Как создать таблицу амортизации кредита с переменной ставкой в Excel

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

Ниже — подробная инструкция с рекомендациями, примерами формул и дополнительными материалами: контрольными проверками, списками и альтернативными подходами.

Что нужно подготовить

Перед началом соберите эти данные:

• Общая сумма кредита (Total Loan Amount). В примерах ниже используется 5 000 000 руб.
• Срок кредита в годах (Loan term), например 20 лет.
• Количество платежей в году (Payments per year), для ежемесячных — 12.
• График изменения годовой ставки по периодам (можно заполнить предварительными значениями).
• Любые условия досрочных платежей и комиссий (если есть).

Краткое определение ключевой формулы:

• PMT — встроенная функция Excel для расчёта размера периодического платежа по аннуитетной схеме. В формате: -PMT(RATE, NPER, PV), где RATE — ставка за период, NPER — число оставшихся периодов, PV — текущая задолженность.

Пример начальных данных (пример в Excel)

В примере возьмём 20 лет, 5 000 000 руб., ежемесячные платежи (12 в год). В ячейках обычно располагают: общая сумма, платежей в году, итоговый срок в месяцах/летах и начальная годовая ставка.

Фактовая подсказка: в примере 20 лет × 12 = 240 месяцев.

Структура таблицы амортизации (колонки)

Создайте следующие столбцы (минимум):

• Период платежа (Payment Period) — нумерация периодов, обычно от 0.
• Сумма платежа (Payment Amount) — полный ежемесячный платёж.
• Выплаченные проценты (Interest Paid).
• Выплаченный основной долг (Principal Paid).
• Остаток кредита (Loan Balance).
• Годовая ставка для периода (Annual Interest Rate).
• Досрочный платёж (Lump-Sum Payment).

Вы также можете добавить вспомогательные колонки: Примечания, Тип периода (фикс/перем), Комиссия, Номер платежа в году.

Заполнение фиксированных данных (номера периодов и начальный баланс)

1. В столбце «Период» поставьте 0 в строке с начальными данными (тот самый Period 0). В следующей строке — 1, далее 2.
2. Выделите три первых ячейки (0,1,2) и растяните маркером заполнения до нужного последнего периода (в нашем примере до 240). Excel покажет подсказку с текущим номером строки — отпустите, когда дойдёте до 240.
3. В строке Period 0 в столбце «Остаток кредита» свяжите ячейку с полем Total Loan Amount (например, =C1 или =B2 в зависимости от расположения). Это первый остаток задолженности.
4. Оставьте остальные ячейки «Остатка» пустыми — они будут вычисляться формулами.

Подсказка: используйте форматирование ячеек (Числовой, Денежный) для удобства чтения.

Как задавать годовые ставки по периодам

В столбце «Годовая ставка» укажите годовую процентную ставку для каждого платежного периода. Для ARM (например, 5/1 ARM) первые 5 лет будет фиксированная ставка, затем ставка меняется ежегодно. Вы можете заполнить столбец историческими значениями или прогнозными значениями от банка.

Пример: первые 60 периодов (5 лет × 12) — 3.57%, затем — переменные значения.

Формулы, которые нужно добавить (подробно)

Ниже — набор формул, которые вы добавите в первую вычисляемую строку (обычно строка 1 или 6 в примере) и протянете вниз.

1. Формула для суммы платежа (Payment Amount).

=-PMT(RATE,NPER,PV)

• RATE: свяжите с ячейкой годовой ставки текущего периода и разделите на число платежей в году (например, F7/$C$3). Если ставка задана в годовом виде, то для ежемесячных расчётов используйте F7/12.
• NPER: число оставшихся периодов; можно использовать COUNT или простую арифметику (TotalPeriods - CurrentPeriod). В примере используется COUNT для подсчёта оставшихся строк: COUNT(firstPeriodCell:lastPeriodCell) с абсолютной ссылкой на конец диапазона (например, $A$245).
• PV: текущий остаток задолженности до платежа (ячейка Period 0 для первого платежа). В примере это E6.

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

2. Формула для уплаченных процентов (Interest Paid).

=Balance Remaining*(Annual Interest Rate/Payments Per Period)

Пример для строки 7:

=E6*(F7/$C$3)

Где E6 — остаток перед платежом (Loan Balance предыдущего периода), F7 — годовая ставка для текущего периода, $C$3 — число платежей в году с абсолютной ссылкой.

3. Формула для погашения основного долга (Principal Paid).

Отнимите Уплаченные проценты от Суммы платежа:

=B7-C7

4. Формула для нового остатка кредита (Loan Balance).

Остаток = предыдущий остаток − Погашение основного долга − Досрочная выплата (если есть):

=E6-B7-G7

В этом примере G7 — столбец «Lump-Sum Payment».

Совет: проверяйте относительные и абсолютные ссылки. Когда нужно «заморозить» ссылку на ячейку с общим значением (например, количество платежей в году), используйте $C$3. При протягивании формул это предотвратит сдвиг ссылки.

Автоматическое заполнение для всех периодов

1. Выделите ячейки первого периода с формулами (Payment Amount, Interest Paid, Principal Paid, Loan Balance и т.д.).
2. Потяните маркер заполнения вниз до последнего периода (или используйте Copy → Paste для диапазона).
3. После заполнения отформатируйте ячейки с формулами цветом, чтобы избежать случайного изменения.
4. Для визуального контроля можно применить условное форматирование: когда Loan Balance = 0, покрасить строку в другой цвет.

Полезные дополнения и альтернативные формулы

• Функции IPMT и PPMT. Если хотите получить точную часть процентов или основного долга для заданного номера платежа, используйте IPMT (проценты) и PPMT (основной долг). Формат: =IPMT(rate, per, nper, pv) и =PPMT(rate, per, nper, pv). Период per — порядковый номер платежа.

• Для ситуаций с переменной ставкой можно заносить в формулу PMT текущую ставку и число оставшихся периодов: таким образом значение платежа будет пересчитываться при смене ставки.

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

Где метод может дать неточные результаты (ограничения и когда он не годится)

• Банковские комиссии, страховые премии и округления: банки часто применяют дополнительные комиссии, которые не учитываются в простой амортизационной таблице.
• Различия в компаундинге: если банк начисляет проценты ежедневно, а вы моделируете месячные периоды, возможны расхождения.
• Плавающая маржа: если в ARM есть маржа плюс индекс (например, LIBOR/EURIBOR + маржа), вам нужно применять реальные значения индекса.
• Отсутствие учёта периодов льгот или «interest-only» периодов: для таких схем требуются отдельные условия и формулы.

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

Практические тесты и критерии приёмки

Критерии приёмки для корректной таблицы:

• Первый остаток равен сумме кредита (Period 0).
• Для каждой строки: Payment Amount = Interest Paid + Principal Paid (с допустимым небольшим округлением).
• Остаток на последнем периоде близок к нулю (с учётом округлений) и не отрицательный.
• Сумма всех Principal Paid = первоначальная сумма кредита − итоговый остаток.
• При введении досрочного платежа соответствующая строка уменьшает следующий остаток на эту сумму.

Тестовые сценарии:

1. Без досрочных выплат: заполните столбец Lump-Sum = 0 для всех строк. Проверьте, что последний остаток = 0.
2. С одной досрочной выплатой в середине срока: в строке 120 введите G120 = 100000. Проверьте, что будущие остатки уменьшились, а сумма процентов сокращается.
3. Изменение ставки: в строке 61 задайте новую ставку 4.5% и протяните дальше; проверьте изменение суммы платежа/процентов в соответствии с формулой PMT.

Чеклист — перед публикацией / экспортом

• Все формулы заполнены для всех периодов.
• Абсолютные ссылки ($) используются корректно.
• Округления выполнены — установите формат DECIMAL (2 знака) для валют.
• Проверены контрольные суммы: сумма Principal Paid + сумма Interest Paid = сумма всех платежей.
• Присутствуют комментарии/примечания, объясняющие источники ставок.

Альтернативные подходы

• Google Sheets — эквивалент Excel; формулы PMT, IPMT, PPMT работают аналогично.
• VBA / макросы — автоматизация изменения ставок и уведомлений; удобно, если вы часто обновляете данные.
• Маленький скрипт на Python (pandas) — для сложных расчётов, бэкапирования и сравнения сценариев (используйте openpyxl/xlsxwriter при экспорте результатов обратно в Excel).
• Финансовый калькулятор или специализированные программы (для юристов/аудиторов).

Ментальные модели и эвристики

• “Процент сначала, основной позже”: в аннуитетном платеже в начале срока большая часть идёт на проценты.
• “Досрочные платежи ускоряют падение процентов”: каждая единица досрочной выплаты уменьшает основной баланс и тем самым будущие начисления процентов.
• “Смена ставки = пересчёт NPER или платежа”: при переменной ставке можно выбрать пересчитать текущую сумму платежа (PMT) при каждой смене ставки или оставить платёж неизменным и изменять срок — зависит от условий банка.

Пример маленького справочника формул (cheat sheet)

Предположим: первые вычисления находятся в строке 7, предыдущий остаток — E6, годовая ставка для строки 7 — F7, число платежей в году — $C$3, общий последний номер периода — $A$245.

• Payment Amount (B7):

=-PMT(F7/$C$3,COUNT($A$7:$A$245),E6)

• Interest Paid (C7):

=E6*(F7/$C$3)

• Principal Paid (D7):

=B7-C7

• Loan Balance (E7):

=E6-D7-G7

(где G7 — досрочный платёж в этом периоде)

Рекомендации по визуализации и отчётности

• Добавьте линейный график остатка кредита по времени.
• Отдельная диаграмма «проценты против основного долга» показывает, как меняется структура платежа.
• Используйте срезы и фильтры, если у вас несколько кредитов в одной книге Excel.

Почему это важно: влияние на финансовое планирование

Понимание амортизационной структуры помогает:

• Оценить, насколько досрочные платежи сокращают переплату по процентам.
• Сравнивать сценарии с разными будущими ставками.
• Планировать бюджет и видеть «точку пересчёта», когда изменение ставки станет критичным.

Риски и меры смягчения

• Риск: пропуск комиссий или платежей банка в модели. Митигирование: учитывайте все сборы в отдельных строках.
• Риск: неверная модель компаундинга. Митигирование: спросите в банке, как начисляются проценты (ежедневно/ежемесячно) и скорректируйте ставку.
• Риск: округления могут давать небольшой остаток на последнем периоде. Митигирование: вручную скорректируйте последний платёж или используйте функцию ROUND для промежуточных сумм.

Рольовые чеклисты

Для заёмщика:

• Проверьте начальную сумму кредита и комиссии.
• Введите фактические ставки от банка по мере их получения.
• Моделируйте несколько сценариев досрочных платежей.

Для финансового консультанта:

• Подтвердите способ начисления процентов у кредитора.
• Проверьте соответствие итоговых сумм договору и амортизации.
• Сохраните версию с исходными данными и версию со сценариями.

Мини-методология для повторного процесса (SOP)

1. Обновить входные данные (начальная сумма, срок, число платежей в году).
2. Внести реальные ставки в соответствующие периоды.
3. Ввести/обновить досрочные платежи.
4. Пересчитать таблицу (протянуть формулы).
5. Проверить критерии приёмки и графики.
6. Сохранить отдельной версией (например, “snapshot_YYYYMMDD”).

Decision flowchart

flowchart TD
  A[Начальные данные готовы?] -->|да| B[Ввести в Excel]
  A -->|нет| Z[Собрать данные]
  B --> C{Есть переменные ставки?}
  C -->|да| D[Заполнить колонки ставок по периодам]
  C -->|нет| E[Заполнить одну ставку на весь срок]
  D --> F[Добавить формулы PMT, IPMT, PPMT]
  E --> F
  F --> G{Есть досрочные платежи?}
  G -->|да| H[Добавить Lump-Sum и учесть в остатке]
  G -->|нет| I[Протянуть формулы]
  H --> I
  I --> J[Проверить критерии приёмки]
  J --> K{Ошибки?}
  K -->|да| L[Отладить формулы]
  K -->|нет| M[Сохранить и проанализировать сценарии]

Часто встречающиеся ошибки и устранение

• Неправильно зафиксированные адреса ячеек: используйте $ для фиксирования.
• Использование COUNT вместо COUNTIF/COUNTA в таблицах с пустыми ячейками: убедитесь, что диапазон корректен.
• Забыт отрицательный знак перед PMT: тогда получаете отрицательные значения платежей.

Краткое итоговое резюме

• Соберите входные данные: сумма, срок, частота платежей, график ставок.
• Создайте столбцы: период, платеж, проценты, основной долг, остаток, ставка, досрочные.
• Введите формулы: PMT для платежа, IPMT/PPMT при необходимости, вычисление процентов и остатка.
• Проверьте таблицу контрольными суммами и тестовыми сценариями.

Важно: эта модель пригодна для планирования и оценки — для официальных расчётов используйте документы банка.

Summary:

• Модель в Excel позволяет гибко анализировать кредиты с переменной ставкой.
• Досрочные выплаты сокращают как баланс, так и итоговую переплату процентов.
• Учитывайте компаундинг и комиссии для точных результатов.