Как рассчитать платеж по кредиту с помощью Excel. Часть 1. Дифференцированный платеж.

При получении кредита в банке, каждый задается вопросом о размере платежа по нему. В настоящее время существует два вида платежа: дифференцированный и аннуитетный. В данной статье рассмотрим расчет дифференцированного платежа. (аннуитет см. здесь).

Смысл дифференцированного платежа состоит в том, что основной долг кредита разбивается равными частями на количество периодов погашения, а на остаток основного долга начисляются проценты по установленной процентной ставке.

Рассмотрим пример: предположим, Вам предлагают кредит в сумме
100 000.00 рублей на срок 5 лет по ставке 13% годовых с ежемесячным начислением процентов на остаток. Учитывая, что срок скредита 5 лет и начисление прроцентов происходит ежемесячно, то количество периодов погашения равно шестидесяти. Создадим таблицу, в которой будет виден остаток кредита после каждого платежа, общая сумма платежа в каждом периоде, сумма уплаченных процентов и сумма уплаченного долга:

Разберем подробно. В первой строке все значения равны 0, поскольку это получение кредита, здесь остаток долга равен 100 000.00 руб.

В столбце А разместим формулу с ячейки А3, которая будет увеличивать значение на 1, и растянем формулу до тех пор, пока значение в столбце не достигнет значения 60.

Далее в столбце B разместим формулу, которая определит сумму ежемесячного пдатежа по основному долгу кредита (значение ячейки Е2, в которой указана сумма кредита, разделим на 60, т.е. на количество месяцев, на который кредит взяли):

Протянем формулу также до конца таблицы. Знак $ в формуле служит для фиксации указателя на ячейку, чтобы при растягивании формулы на диапазон, указатель не перемещался.

В столбец Е начиная с ячейки Е2 разместим формулу определения остатка основного долга на конец периода, для этого из ячейки Е1 вычтем сумму погашения основного долга за период:

Также протянем формулу до конца таблицы. в ячейке Е60. должен получиться ноль.

В столбец С поместим формулу определения суммы уплаченных за период процентов, для этого надо остаток основного долга разделить на процентную ставку (13% годовых, как условились ранее) и разделить полученный результат на 12 (процентная ставка годовая, период начисления процентов - месяц, делим на 12) и разделим все на 100, чтобы перевести итог с процентного формата на числовой:

Также проятнем формулу до конца таблицы.

В итоге получены две части платежа на каждый период - сумма погашения основного долга и сумма погашения процентов. Осталось их сложить за каждый период и узнать ежемесячный платеж на весь срок кредита.

И снова протянем формулу в ячейке D2 (суммируются значения основного долга и процентов) до конца таблицы.

Подведем итог, чтобы узнать сумму уплаченного основного долга, сумму уплаченных процентов и общую сумму за весь период кредитования, для чего воспользуемся функцией СУММ():

В ячейке B63 в результате будет 100 000.00, в ячейке С63 - сумма процентных платежей за весь срок кредита, в D3 - общая сумма платежей за весь срок кредитования.

На этом все.

Если Вам интересен Excel, то подписывайтесь на этот канал, ставьте лайк, пишите комментарии, задавайте вопросы.