Рассчитать погашение кредита тремя способами

Таблица 1. Условия кредита.

Решение.

Способ 1. Дифференцированный платеж с равными долями погашения основного долга. Погашения основного долга — не меняется от месяца к месяцу.

Таблица 2.

Заполним таблицу 2 в Excel (ячейки А6: F21) по формулам. Для этого последовательно программируем формулы 7 и 8 строки таблицы Excel. Так «Сумма на начало» в ячейке В7 равна сумме в ячейке С2. Кроме того делаем ее постоянной ссылкой (кнопка F4). Поэтому в ячейке В7 записано: =$C$ 2.

«% по кредиту за месяц» равны: = «сумма на начало» *"Годовая ставка кредита"/12/100. При этом «сумма на начало» делаем переменной ссылкой т.к. она будет меняться от месяца к месяцу, а «Годовая ставка кредита» делаем постоянной ссылкой. Кроме того, т.к. мы считаем проценты за месяц, то делим на 12 и на 100. Поэтому в ячейке С7 записано =B7*$C$ 3/12/100.

Погашения основного долга — не меняется от месяца к месяцу. Поэтому в ячейке D7 записано = «Начальная сумма кредита"/ «срок кредита в месяцах».

(делаем их постоянной ссылкой). А в ячейке D8 записано = D7. Тем самым мы задаем постоянство погашения основного долга. «Всего платежей» равны: ="% по кредиту за месяц" + «Погашения основного долга». Поэтому в ячейке Е7 записано: =C7+D7. «Сумма на конец» = «Сумма на начало» — «Погашения основного долга». Поэтому в ячейке F7 записано: =B7-D7.

Далее программируем восьмую строку таблицы. «Сумма на начало» второго месяца равна «Сумма на конец» первого месяца. Отсюда следует, что в ячейке B8 записано: = F7. Далее копируем формулы из строки 7 в строку 8 в столбцах C, D, E, F.

После этого копируем формулы из строки 8 во все строки ниже в столбцах A, B, C, D, E, F.

Рис. 1. График дифференцированного платежа.

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

Способ 2. Второй кредит — аннуитетный — с постоянными суммарными выплатами = % по кредиту за месяц + погашения основного долга = константа.

Формулы аннуитетного кредита:

Пусть S — сумма кредита; а — % годовая ставка; n — срок кредита.

Погашения основного долга 1 месяца = S*а/12/100/((1+а/12/100)n — 1).

Погашения основного долга 2 месяца = погашения основного долга 1 месяца * (1+а/12/100) и т. д.

Погашения основного долга i+1-го месяца=погашения основного долга «i го» месяца *(1+а/12/100).

В результате столбик «Всего платежей» — константа.

Рассчитать кредит при тех же начальных условиях, что и в способе 1.

Таблица 3.

Разместим расчет в ячейках A6: F21. Для этого полностью скопируем всю таблицу расчетов из первого способа.

Поменяем формулу для расчета основного долга в первом и во втором месяце в соответствии с формулой аннуитетного кредита:

Погашения основного долга 1 месяца = S*а/100/12/((1+а/100/12)n — 1).

Где: S — сумма кредита; а — % годовая ставка; n — срок кредита.

Погашения основного долга 2 месяца = погашения основного долга 1 месяца * (1+а/100/12).

Далее копируем формулы второй строки ниже. Для этого выделяем всю вторую строку (массив А7: F7) и тянем вниз за маркер заполнения (точка в правом нижнем углу выделенного массива).

Рис. 2. График аннуитетного платежа

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

Способ 3.

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

Разместим расчет в ячейках A6: F21. Для этого полностью скопируем всю таблицу расчетов из первого способа.

Поменяем формулу для расчета основного долга в первом и во втором месяце. =-ОСПЛТ ($C$ 3/100/12;A51;$C$ 4;$C$ 2).

Далее копируем формулы второй строки ниже.

Таблица 4.

Как видно из таблицы, ее значения полностью совпадаю с расчетами предыдущим способом.