Расчет кредитных выплат в Excel

Расчет кредитных выплат в ExcelРасчет кредитных выплат с помощью Excel.

Многие из нас брали кредит в банке.

Не знаю, как вам, но мне расчеты ежемесячных выплат доставляют головную боль.

Впрочем, поэтому я за них и не берусь.

Хотя было бы правильнее, конечно, заблаговременно произвести расчеты и выбрать наиболее подходящий вариант кредита.

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

  • сумму ежемесячных выплат (аннуитетный платеж);
  • часть из этой суммы, которая идет на погашение основного долга;
  • оставшуюся часть, которая идет на погашение процентов.

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

Построение таблицы

Построить такую таблицу совсем несложно. использовать мы будем три функции:

  • ПЛТ — рассчитывает сумму регулярного платежа при той или иной процентной ставке;
  • ОСПЛТ — рассчитывает часть суммы регулярного платежа, которая уходит на погашение основного долга;
  • ПРПЛТ — рассчитывает часть суммы регулярного платежа, которая уходит на погашение процентов.

Для начала, внесем основные данные, а именно — сумму кредита, срок кредита в годах, и процентную ставку. Например, это будет 200 тыс. руб, на 2 года под 18%.

Составим колонки таблицы расчета:

  1. Месяц — порядковый номер месяца очередной выплаты;
  2. Аннуитетный платеж — общая сумма ежемесячных выплат;
  3. В т.ч. основная сумма — часть общей суммы, уходящей на погашение основного долга;
  4. В т.ч. проценты — оставшаяся часть суммы, уходящей на погашение процентов;
  5. Остаток долга на конец месяца.

За два года пройдет 24 месяца, поэтому в колонке месяц проставляем порядковые номера от 1 до 24. Для облегчения процедуры можно воспользоваться автонумерацией. В первую ячейку проставляем 1. Подводим курсор мыши к правому нижнему углу выделенной ячейки (там должен быть виден маленький черный квадратик), зажимаем левую клавишу мыши и, одновременно, клавишу Ctrl на клавиатуре, и протягиваем курсор вниз до тех пор, пока справа от курсора мыши не появиться значение «24».

Внесение формул

В верхнюю ячейку колонки «Аннуитетный платеж» вносим такую формулу: =ПЛТ($B$4/12;$B$3*12;-$B$2). Знак $ перед адресом колонки и номером строки обозначает, что при протягивании этой формулы на другие ячейки, этот адрес не будет изменяться.

  • Первый аргумент функции ПЛТ обозначает процентную ставку. Мы делаем ссылку на B4. Но, поскольку ставка указывается годовая, а нам необходимо вычислить ставку за месяц, мы это значение делим на 12.
  • Второй аргумент должен указывать период, на который взят кредит. В нашем случае это 5 лет, однако, поскольку мы уже заложили ежемесячный расчет, то и период необходимо указать в количестве месяцев. Поэтому ссылку на B3 мы умножаем на 12.
  • Третий аргумент — сумма кредита. Ссылаемся на B2. Однако, если мы просто сошлемся на сумму кредита, то будет выпадать отрицательное значение. И это логично, ведь каждая выплата, это — убыток. Поэтому, если мы не хотим видеть отрицательные значения, то перед ссылкой поставим минус.

В верхнюю строку колонки «в т.ч. основная сумма» вносим формулу: =ОСПЛТ($B$4/12;A7;$B$3*12;-$B$2). Здесь все аналогично, кроме второго аргумента, который указывает на порядковый номер периода (в нашем случае, это порядковый номер месяца).

В верхнюю строку колонки «в т.ч. проценты» вносим формулу: =ПРПЛТ($B$4/12;A7;$B$3*12;-$B$2). Здесь все аргументы соответствуют предыдущей формуле.

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

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

При расчете долга на конец месяца, мы должны учитывать и выплату на погашение процента. Поэтому это не просто остаток долга минус текущий аннуитетный платеж, а остаток долга плюс сумма на погашение процента минус текущий аннуитетный платеж (уж простите меня за сложность). Поэтому формула, которую мы внесем, будет такой: =B2+D7-B7.

  • B2 — это ссылка на сумму кредита (остаток на прошлый месяц);
  • D7 — ссылка на сумму погашения процента, набежавшего к концу первого месяца;
  • B7 — ссылка на сумму первой выплаты по кредиту.

Протянуть на все ячейки ниже для автозаполнения эту формулу мы не можем, т.к. адрес с остатком долга поменялся. Теперь это будет не В2, а значение в ячейке выше. Поэтому формула теперь будет следующей: =E7+D8-B8.

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

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

Анализ «что-если»

Но это еще не все. Было бы интересно сравнить различные условия по кредиту. А каждый раз менять эти условия в таблице с данными не совсем наглядно. Для этого лучше использовать анализ «что если», ссылка на который находится в закладке меню «Данные«.

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

На пересечении наименований строк и колонок необходимо сделать ссылку на зависимую ячейку. В нашем случае это любая ячейка из колонки «Аннуитетный платеж». Поставим ссылку на В7 (появиться значение с этой ячейки — 9986). Теперь выделяем эту нашу таблицу, и нажимаем на кнопку меню «Анализ «что-если«. В выпавшем меню выбираем строку «Таблица данных«.

Появилось окошечко, для определения критериев. Значения по столбцам у нас указывают на срок кредита в годах, поэтому в верхней строке окошка делаем ссылку на ячейку В3. Значения по строкам — это процентная ставка. Значит ссылаемся на В4. Нажимаем ОК.

Теперь видно, сколько бы мы платили на разных условиях и при разных сроках по кредиту.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *