Построение графика погашения кредита в Excel за 5 минут
Чтобы рассчитать кредит в Excel, используйте функцию ПЛТ (для аннуитетных платежей) или формулу деления основного долга на срок (для дифференцированных). Это позволяет мгновенно узнать ежемесячный взнос, общую переплату и построить детальный график погашения без обращения к банковским калькуляторам. Ниже приведены готовые формулы и структура таблицы для обоих типов кредитов.
Ключевые финансовые функции Excel
Excel содержит встроенный набор финансовых функций, автоматизирующих сложные вычисления. Для работы с кредитами вам понадобятся всего три основные функции.
1. ПЛТ (PMT) — Расчет ежемесячного платежа
Используется для аннуитетной схемы, где сумма платежа неизменна весь срок.
Синтаксис: =ПЛТ(ставка; кпер; пс; [бс]; [тип])
- ставка — процентная ставка за период (годовая ставка / 12).
- кпер — общее количество периодов выплат (срок в месяцах).
- пс — приведенная стоимость, то есть сумма кредита (вводится со знаком минус, чтобы результат был положительным).
Пример расчета: Кредит 1 000 000 руб. на 5 лет под 12% годовых.
Формула: =ПЛТ(12%/12; 5*12; -1000000)
Результат: 22 244,45 руб. — фиксированный платеж каждый месяц.
2. ОСПЛТ (PPMT) — Тело кредита
Показывает, какая часть конкретного платежа идет на погашение основного долга. В начале срока эта доля минимальна, в конце — максимальна.
Формула: =ОСПЛТ(ставка; период; кпер; пс)
Где «период» — номер месяца, для которого делается расчет (от 1 до 60).
3. ПРПЛТ (IPMT) — Проценты
Показывает сумму начисленных процентов за конкретный месяц.
Формула: =ПРПЛТ(ставка; период; кпер; пс)
Сумма значений функций ОСПЛТ и ПРПЛТ для любого периода всегда равна значению функции ПЛТ.
Создание таблицы аннуитетного графика
Аннуитетная схема наиболее распространена в банках. Чтобы создать полный график погашения:
- Создайте шапку таблицы: № месяца, Платеж, Проценты, Основной долг, Остаток долга.
- В ячейки внесите исходные данные: Сумма кредита (например, в ячейку
B1), Ставка (B2), Срок в месяцах (B3). - Заполните строки формулами, используя абсолютные ссылки (знак
$) для параметров кредита.
| № мес | Платеж (формула) | Проценты | Основной долг | Остаток долга |
|---|---|---|---|---|
| 0 | =B1 | |||
| 1 | =ПЛТ($B$2/12; $B$3; -$B$1) | =E2*($B$2/12) | =B3-C3 | =E2-D3 |
| 2 | =B3 | =E3*($B$2/12) | =B4-C4 | =E3-D4 |
| ... | ... | ... | ... | ... |
Как это работает:
- Столбец «Платеж» копирует значение функции ПЛТ для всех месяцев.
- Столбец «Проценты» считается от остатка долга предыдущего месяца.
- Столбец «Основной долг» — разница между полным платежом и процентами.
- «Остаток долга» уменьшается на сумму основного долга.
Протяните формулы вниз до последнего месяца срока. В последней строке остаток долга должен стать равен 0 (или близок к нему с учетом копеек).
Расчет дифференцированного платежа
При дифференцированной схеме сумма основного долга делится на равные части, а проценты начисляются на остаток. Платеж каждый месяц уменьшается.
Здесь функция ПЛТ не нужна. Используйте простую арифметику:
- Ежемесячное тело долга:
=Сумма_кредита / Срок_в_месяцах. Это значение постоянно для всех строк. - Проценты:
=Остаток_долга_на_начало_месяца * (Ставка / 12). - Итоговый платеж:
=Тело_долга + Проценты.
| № мес | Тело долга | Проценты | Итого платеж | Остаток долга |
|---|---|---|---|---|
| 0 | 1 000 000 | |||
| 1 | =1000000/60 | =E2*(12%/12) | =B3+C3 | =E2-B3 |
| 2 | =B3 | =E3*(12%/12) | =B4+C4 | =E3-B4 |
Результат: Первый платеж составит около 30 833 руб., а последний — около 16 800 руб. Общая переплата по такой схеме будет значительно ниже, чем при аннуитете.
Сравнение схем и анализ переплаты
Для принятия решения о том, какой кредит выгоднее, сведите итоговые данные в сводную таблицу.
| Параметр | Аннуитетный платеж | Дифференцированный платеж |
|---|---|---|
| Ежемесячная нагрузка | Равная сумма весь срок | Высокая в начале, снижается к концу |
| Первый платеж | 22 244 руб. | 30 833 руб. |
| Последний платеж | 22 244 руб. | ~16 800 руб. |
| Общая переплата | ~334 647 руб. | ~302 500 руб. |
| Выгода | Удобно планировать бюджет | Экономия ~32 000 руб. за 5 лет |
Банки часто навязывают аннуитетную схему, так как она приносит им больше прибыли за счет ранних процентов. При досрочном погашении в первые годы аннуитетного кредита вы гасите в основном проценты, а не тело долга.
Частые ошибки при расчетах
- Отрицательный результат платежа. Функция ПЛТ возвращает отрицательное число, показывая отток денег. Чтобы получить красивое положительное число в таблице, ставьте знак минус перед суммой кредита в формуле:
=ПЛТ(...; -Сумма). - Неверная ставка. Самая распространенная ошибка — использование годовой ставки (12%) вместо месячной (1%). Всегда делите ставку на 12:
12%/12. - Срок в годах вместо месяцев. Аргумент «кпер» (количество периодов) должен соответствовать частоте платежей. Если платите ежемесячно, умножайте годы на 12.
FAQ
Можно ли учесть страховку в расчете? Да. Добавьте сумму страховки к основному долгу («пс») в формуле, если она выплачивается единовременно за счет кредитных средств. Если страховка оплачивается отдельно ежегодно, ее нужно считать вручную и добавлять к столбцу «Платеж».
Как рассчитать эффективную процентную ставку?
Используйте функцию =ЭФФЕКТ(номинальная_ставка; кол_периодов). Например, =ЭФФЕКТ(12%; 12) покажет реальную годовую ставку с учетом ежемесячной капитализации (около 12,68%).
Что делать, если был платежный отпуск (каникулы)? В таблице графика просто пропустите начисление основного долга за эти месяцы, но оставьте формулу расчета процентов от текущего остатка. Остаток долга уменьшится медленнее, что автоматически увеличит общую переплату в последующих периодах.