Лабораторная работа №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) вычисляются проценты, выплачиваемые именно в этом месяце, они добавляются к погашаемой части кредита. В итоге мы получаем сумму, которую необходимо выплачивать в конкретном месяце.
Использование условий и применение в вычислениях номера строки помогает создавать достаточно сложные документы, поэтому хорошо разберитесь с принципами работы описанной таблицы. Они пригодятся при выполнении следующих лабораторных работ.
Упражнение №2
Содержание задачи: оформить и заполнить бланк «Платежное поручение».
Откройте новый рабочий лист, создайте бланк, структура которого представлена на рисунке 1.13. Обратите внимание на то, что формат ячейки L1 должен быть «текстовый», иначе запись «0401060» будет восприниматься табличным процессором как число, и первый символ «0» будет автоматически удаляться.
Рисунок 1.13 - Структура бланка «Платежное поручение»