При получении кредита в банке, каждый задается вопросом о размере платежа по нему. В настоящее время существует два вида платежа: дифференцированный и аннуитетный. В данной статье рассмотрим расчет аннуитетного платежа. (дифференцированный метод рассмотрен см. здесь).
Смысл аннуитетного платежа состоит в том, что размер платежа на всем сроке кредита остается неизменным, при этом сумма погашения основного долга растет внутри этого платежа растет, а сумма процентов снижается вместе со снижением остатка долга по кредиту.
Рассмотрим пример: предположим, Вам предлагают кредит в сумме
100 000.00 рублей на срок 5 лет по ставке 13% годовых с ежемесячным начислением процентов на остаток. Учитывая, что срок скредита 5 лет и начисление прроцентов происходит ежемесячно, то количество периодов погашения равно шестидесяти. Создадим таблицу, в которой будет виден остаток кредита после каждого платежа, общая сумма платежа в каждом периоде, сумма уплаченных процентов и сумма уплаченного долга:
Разберем подробно. В первой строке все значения равны 0, поскольку это получение кредита, здесь остаток долга равен 100 000.00 руб.
В столбце А разместим формулу с ячейки А3, которая будет увеличивать значение на 1, и растянем формулу до тех пор, пока значение в столбце не достигнет значения 60.
Далее необходимо рассчитать сумму ежемесячного платежа. Формула расчета аннуитетного платежа выглядит следующим образом:
В столбце D разместим формулу, которая определит общую сумму ежемесячного платежа, включающую в с себя платеж по основному долгу и платеж по процентам:
здесь в качестве К (первоначальная сумма кредита) выступает ссылка на ячейку $E$2, в качестве s (процентная ставка) 13/12/100, в качестве (1+s) в степени n - выражение СТЕПЕНЬ(1+13/12/100;60).
В столбец С поместим формулу определения суммы уплаченных за период процентов, для этого надо остаток основного долга разделить на процентную ставку (13% годовых, как условились ранее) и разделить полученный результат на 12 (процентная ставка годовая, период начисления процентов - месяц, делим на 12) и разделим все на 100, чтобы перевести итог с процентного формата на числовой:
В итоге получены Общая сумма платежа и Сумма процентов за первый месяц. Осталось из суммы платежа вычесть сумму процентов, чтобы получить сумму погашения основного долга:
Теперь протянем столбцы B, C, D до конца таблицы (до ячеек B62, C62, D62).
Подведем итог, чтобы узнать сумму уплаченного основного долга, сумму уплаченных процентов и общую сумму за весь период кредитования, для чего воспользуемся функцией СУММ():
В ячейке B63 в результате будет 100 000.00, в ячейке С63 - сумма процентных платежей за весь срок кредита, в D3 - общая сумма платежей за весь срок кредитования.
На этом все.
Если Вам интересен Excel, то подписывайтесь на этот канал, ставьте лайк, пишите комментарии, задавайте вопросы.