Аннуитет. Выплата ссуды (кредита, займа) с досрочным погашением. Сокращаем регулярный платеж

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

В статье Аннуитет. Расчет периодического платежа в MS EXCEL. Погашение ссуды (кредита, займа) показано как рассчитать график погашения кредита или ссуды в случае аннуитетной схемы погашения. В данной статье-продолжении научимся рассчитывать график с учетом частичного досрочного погашения.

Задача

Пусть был взят кредит в размере 100 000руб. на 5 лет под ставку 12%. Кредит должен гаситься ежемесячными равными платежами (в конце периода).

Первоначальный ежемесячный платеж рассчитывается по формуле =ПЛТ(12%/12;12*5;-100000)

Совет: для тех, кто первый раз сталкивается с функцией ПЛТ() советую все же прочитать сначала статью  Аннуитет. Расчет периодического платежа в MS EXCEL. Погашение ссуды (кредита, займа) . Там подробно написано, почему в формуле годовую ставку процента нужно разделить на 12, а перед суммой кредита поставить минус.

Для каждого периода нам требуется рассчитать (привожу алгоритм):

• ежемесячный платеж (он будет меняться после каждого частично досрочного погашения (далее - ДП)). Используем функцию ПЛТ(). Вместо начальной суммы кредита (аргумент ПС) нужно взять остаток кредита на конец периода (тело кредита на конец периода), в аргумент кпер введите оставшееся количество периодов выплат; 
• величину процента к уплате (она меняется в зависимости от остатка тела кредита; формула та же, что и для случая без ДП);
• величину, которая идет на погашение тела кредита (она вычисляется как разница между первыми двумя величинами, вычисленными ранее);
• тело кредита на конец периода (из суммы остатка тела кредита на начало периода нужно вычесть величину, которая идет на погашение тела кредита, рассчитанную на предыдущем шаге );
• тело кредита на начало следующего периода (от случая без досрочных погашений расчет отличается лишь дополнительным вычитанием суммы ДС).

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

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

Предполагается, что досрочное погашение делается в конце периода (за этот период % платится полностью), а размер регулярного платежа меняется только начиная со следующего периода.

Досрочное погашение возможно с 1 месяца, хотя фактически все изменения в расчетах происходят со следующего месяца.

Вообще, в случае ДП в банках предлагают сократить либо платеж либо срок кредита. Переплата по кредиту ниже, если вы сокращаете срок. Оно и понятно, не уменьшая платеж вы раньше выплачиваете кредит. Однако, если сумма ежемесячного платежа для вас высока или есть риск снижения доходов, то выбор снижения платежа также может быть оправдан.

В файле примера на листе Погашение (варианты) сделана форма для сравнения нескольких вариантов ДП. Выбор нужного варианта реализован с помощью выпадающего списка .

В столбцы J:L можно ввести 3 варианта досрочного погашения и последовательно сравнить переплату %.

Примечание: Для того, чтобы рассчитать сумму необходимую для окончательного погашения кредита из тела кредита на конец предыдущего периода вычтите сумму погашения тела кредита в текущем периоде. После этого периода значения во всех столбца обнулятся (кроме столбца Всего погашено тело кредита, конечно).