Лабораторная работа №2. Расчет покупки товара в кредит

Экономическое содержание задачи: пусть вы хотите приобрести какой-то товар в кредит, или предлагаете товары населению с рассрочкой платежа. Важно быстро рассчитать, какие суммы должны ежемесячно выплачиваться и какова общая стоимость товара с учетом процентов. Исходными данными для расчета являются стоимость товара, процент первоначального взноса, годовая ставка кредита, дата оформления кредита и срок кредитования в месяцах, а нужно рассчитать дату последней выплаты, сумму выплаченных процентов и общую сумму покупки.

Введите в ячейки первого столбца таблицы названия ячеек, а также заполните произвольными значениями ячейки с первой до пятой строки второго столбца. В столбце С будут выводиться даты погашения частей кредита. Для того, чтобы дата отображалась правильно, необходимо задать формат ячейки «Дата». В столбце D – выплачиваемые суммы. Структура таблицы представлена на рисунке 1.12.

Рисунок 1.12 – Заголовки и исходные данные

В ячейки со знаком «?» вписываем формулы, приведенные в таблице.

Вначале введем самые простые формулы. Чтобы узнать дату последней выплаты, нужно добавить к дате оформления кредита количество месяцев, на которые выдан кредит. В таблице 1.7 приведены формулы для расчета данной задачи.

Таблица 1.7 – Формулы для реализации задачи

Ячейки	Формулы	Область копирования
В7	=ДАТАМЕС(В4;В5)	-
В8	=СУММ(D2:D19*)-B1*(1-B2)	-
B9	=B1+B8	-
С2	=ЕСЛИ(СТРОКА()-1<=$B$5;ДАТАМЕС($B$4;СТРОКА()-1);"")	С3 – С19
D2	=ЕСЛИ(СТРОКА()-1<=$B$5;$B$1*(1-$B$2)/$B$5*(1+(СТРОКА()-1)*$B$3/12);"")	D3-D19

В данной задаче мы будем использовать функцию ДАТАМЕС, для работы которой необходимо, чтобы был установлен пакет анализа. Для его установки нажмите на кнопку Office и нажмите на клавишу Параметры Excel, выберете пункт Надстройки и нажмите клавишу Перейти. В открывшемся окне поставьте галочку напротив пункта Пакет анализа и нажмите OK. Если пакет анализа не установлен, то можно ввести в ячейку В7 следующую формулу:

=ДАТА(ГОД(В4);МЕСЯЦ(В4)+В5;1)

Значение ячейки В8 представляет собой сумму всех выплат, которые будут рассчитаны и помещены в столбец D из которой следует вычесть сумму кредита. А эта сумма вычисляется как разность между стоимостью товара и первоначальным взносом.

Общая сумма складывается из стоимости товара и выплаченных процентов.

Далее нужно заполнить ячейки в столбцах C и D. Но вначале поясним используемый при расчетах алгоритм. Считается, что кредит возвращается в течение всего срока равными долями. Каждый месяц погашается часть кредита, и вместе с нею выплачиваются проценты за пользование этой частью. В первый месяц возвращается часть суммы с процентами за пользование кредитом в течение одного месяца. Во втором месяце процент больше, так как пользование кредитом уже два месяца.

Рассмотрим, какие вычисления выполняются при расчете даты. Функция СТРОКА() возвращает номер текущей строки, то есть 2 для ячейки С2, и выражение СТРОКА()-1 позволяет определить порядковый номер месяца. Если номер месяца меньше или равен сроку кредитования, выводится дата, а в противном случае ячейка будет пуста.

Если вы работаете без установленного пакета анализа, в ячейку С2 введите формулу, а затем скопируйте ее в диапазон С3:С10

=ЕСЛИ(СТРОКА()-1<=$B$5;ДАТА(ГОД($B$4);МЕСЯЦ($B$4)+ СТРОКА()-1;1);””)

Как и для даты, сумма выводится только для месяцев погашения кредита, а для следующих месяцев выводятся пустые значения. Выражение $B$1*(1-$B$2) вычисляет сумму кредита, а разделив ее на содержимое ячейки В5, можно получить месячную долю погашения. Выражение $B$3/12 определяет месячный процент кредита, а с помощью выражения 1+(СТРОКА()-1)*$B$3/12) вычисляются проценты, выплачиваемые именно в этом месяце, они добавляются к погашаемой части кредита. В итоге мы получаем сумму, которую необходимо выплачивать в конкретном месяце.

Использование условий и применение в вычислениях номера строки помогает создавать достаточно сложные документы, поэтому хорошо разберитесь с принципами работы описанной таблицы. Они пригодятся при выполнении следующих лабораторных работ.