Создание амортизационного графика займа в Excel

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

Именно здесь пригодится амортизационный график. Он визуализирует структуру вашего займа, показывает влияние ежемесячных и дополнительных платежей и помогает планировать досрочные погашения. Ниже — подробная инструкция по созданию такого графика в Microsoft Excel, плюс дополнительные методики, сценарии и шаблоны.

Что такое амортизационный график

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

Определения в одну строку:

• Основной долг: сумма, которую вы заняли.
• Проценты: плата кредитору за пользование заемными средствами.
• Аннуитетный платёж: фиксированная сумма, которую вы платите каждый период.

Пример расчёта в Excel (входные данные)

Возьмём реальный пример из статьи: вы планируете купить 2022 Mustang Mach 1 Premium. Оценочная цена — $68,529. Первоначальный взнос — $6,853. Тогда сумма займа (Loan Amount) составляет $61,676. Условия: 60 месяцев, годовая процентная ставка (APR) 5%, ежемесячный платёж $1,164.

В Excel можно подтвердить или вычислить любой из этих параметров с помощью встроенных финансовых функций:

• Ежемесячный платёж: PMT
• Процентная ставка (если неизвестна): RATE
• Количество периодов (месяцев): NPER
• Текущая стоимость/максимальная сумма займа: PV

Также существуют дополнительные аргументы FV, TYPE, GUESS, но в большинстве задач они не обязательны.

Пример входных ячеек (рекомендуемая структура):

• B1: Общая цена (Estimated net price) = 68529
• B2: Первоначальный взнос (Down payment) = 6853
• B3: Сумма займа (Loan Amount) = =B1-B2 → 61676
• B4: Годовая ставка APR = 5%
• B5: Срок в месяцах = 60
• B6: Ежемесячный платёж = 1164

Из данных B3, B4, B5 вы можете получить B6 через PMT. Если одной из величин не хватает, используйте соответствующую функцию.

Формулы в Excel

Ежемесячная процентная ставка r = B4/12.

Ежемесячный платёж (PMT):

=-PMT(B4/12, B5, B3)

Рассчитать годовую ставку, если известны остальные переменные (функция RATE даёт месячную ставку):

=RATE(B5, -B6, B3) * 12

Число месяцев при известной ставке и платеже (NPER):

=NPER(B4/12, -B6, B3)

Максимальная сумма займа при заданном платеже (PV):

=PV(B4/12, B5, -B6)

Обратите внимание: при использовании отрицательных/положительных знаков Excel интерпретирует направления денежных потоков (платёж = выход средств).

Построение таблицы амортизации с фиксированной ставкой

Сначала внесите в лист базовые параметры (см. раздел выше). Затем создайте таблицу с такими столбцами (рекомендуемый порядок):

• Период
• Начальный остаток
• Ежемесячный платёж
• Погашение основного долга (Principal Payment)
• Платёж по процентам (Interest Payment)
• Накоплено по основному долгу (Cumulative Principal)
• Накоплено по процентам (Cumulative Interest)
• Конечный остаток (Ending Balance)
• Процентная ставка (Interest Rate)
• Единовременные/дополнительные платежи (Lump Sum Payments)

Первая строка таблицы (месяц 1)

1. В столбце «Период» введите “Месяц 1”. Затем используйте автозаполнение для создания списка Месяц 1—Месяц 60.

2. Для «Начального остатка» (первая строка) укажите абсолютную ссылку на сумму займа, например:

=$B$3

3. Для «Ежемесячного платежа» укажите ссылку на ячейку с рассчитанным PMT:

=$B$6

4. «Погашение основного долга» для первой строки:

=C9-E9

(где C9 — ежемесячный платёж, E9 — процент за период).

5. «Платёж по процентам» для первой строки:

=B9*(I9/12)

(где B9 — начальный остаток, I9 — годовая ставка; или напрямую используйте месячную ставку B4/12).

6. «Накоплено по основному долгу» для первой строки:

=D9

7. «Накоплено по процентам» для первой строки:

=E9

8. «Процентная ставка» для первой строки:

=$B$4

9. «Конечный остаток» для первой строки:

=B9-D9

10. Оставьте «Единовременные платежи» пустыми, если их нет.

Заполнение остальных строк

Для второй строки (и далее) используйте формулы, которые ссылаются на предыдущую строку:

• Начальный остаток (строка 10):

=H9-I9

(где H9 — конечный остаток предыдущего периода, I9 — единовременные платежи предыдущего периода).

• Ежемесячный платёж, погашение основного долга, проценты и конечный остаток скопируйте/вставьте из предыдущей строки; Excel автоматически скорректирует ссылки, если вы использовали относительные и абсолютные ссылки правильно.

• Накопленные суммы:

=F9 + D10   (накоплено по основному долгу)
=G9 + E10   (накоплено по процентам)

После того как формулы введены во второй строке, скопируйте всю строку (от Начального остатка до Единовременных платежей) вниз до строки соответствующей последнему месяцу (Месяц 60). Это заполнит таблицу автоматически.

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

Быстрый расчёт экономии при дополнительных платежах (практический пример)

Формально можно показать влияние дополнительного ежемесячного взноса. Возьмём наши числа:

• Сумма займа: $61,676
• Годовая ставка: 5% → месячная ставка r = 0.05/12 = 0.0041666667
• Текущий аннуитетный платёж: $1,164
• Срок: 60 месяцев

Общая сумма выплат при текущем платеже: 1,164 × 60 = $69,840. Переплата по процентам: 69,840 − 61,676 = $8,164.

Если вы добавите $100 к ежемесячному платёжy (итого $1,264), новое число периодов n можно получить явной формулой:

 n = -LN(1 - r * PV / PMT) / LN(1 + r)

Подставляя числа: r*PV/PMT ≈ 257 / 1,264 ≈ 0.2033, 1−…≈0.7967, n ≈ 54.6 месяцев. То есть срок сократится примерно на 5 месяцев. При таком сценарии общая сумма выплат ≈ 1,264 × 54.6 ≈ $69,086, переплата ≈ $7,410. Экономия процентов ≈ $8,164 − $7,410 ≈ $754.

Важно: результат округлён и служит иллюстрацией. В Excel такие вычисления делаются точнее с помощью NPER при заданном PMT.

Что делать при единовременном досрочном платеже

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

Когда амортизационный график не работает или вводит в заблуждение

• Переменная процентная ставка. График, построенный для фиксированной ставки, неверно отразит будущие платежи при переменной ставке. В этом случае нужно периодически обновлять ставку в столбце «Процентная ставка» и пересчитывать последующие строки.
• Комиссии и дополнительные сборы. Если в кредит включены страхование, комиссии, налоги или эскроу, они могут менять реальную нагрузку и не всегда включаются в простой график.
• Платежи с плавающей суммой (например, индексация по доходу) требуют другой модели расчёта.

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

• Google Sheets. Те же функции (PMT, RATE, NPER, PV) доступны в Google Sheets и работают аналогично. Сохраняйте файл в формате .xlsx для совместимости.
• Специализированные калькуляторы и приложения. Банк или сторонние сервисы могут предложить более сложную модель с учётом страховки, комиссий, налогов и графиков платёжных отпусков.
• Моделирование в Python/R. Для автоматизированного анализа множества сценариев и визуализаций удобно использовать скрипты и библиотеки для финансовых расчётов.

Подход для переменной процентной ставки

Если ставка меняется во времени, введите в столбец «Процентная ставка» значение для каждого периода (например, прогноз на год или фактическое изменение по уведомлениям банка). Затем пересчитайте проценты для каждого периода как (Начальный остаток × Процентная ставка / 12). После изменения ставки продолжайте копирование формул вниз. Для сценарного анализа создайте несколько листов с разными траекториями ставок (консервативный/базовый/оптимистичный).

Проверка корректности таблицы — чек‑лист

Для сборщика таблицы:

• Проверил ли я, что начальный остаток в первой строке равен сумме займа?
• Правильно ли указана годовая ставка и разбита ли она на месяцы (B4/12)?
• Совпадает ли итоговая сумма выплат (сумма столбца «Ежемесячный платёж» + единовременные выплаты) с ожидаемой или с расчётом PMT × NPER?
• Закрывается ли остаток в последней строке близко к нулю (учитывая округления)?

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

• Я понимаю, какая часть моего первого платежа идёт на проценты, а какая — на основной долг?
• Учёл ли я возможные комиссии, страховку и налоги?
• Планирую ли я досрочные платежи, и внесены ли они в таблицу?

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

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

Мини‑методика для быстрого создания графика (5 шагов)

1. Внесите входные данные: цена, взнос, сумма займа, ставка, срок, ежемесячный платёж (или вычислите его через PMT).
2. Создайте таблицу с рекомендованными колонками (см. раздел выше).
3. Введите формулы для первой строки: начальный остаток, проценты, основной долг, конец периода.
4. Скопируйте вторую строку вниз до конца периода.
5. Проанализируйте итоговую переплату, добавьте сценарии досрочных платежей.

Модель принятия решения (Mermaid)

flowchart TD
  A[Нужен амортизационный график?] -->|Да| B{Известны все данные}
  B -->|Да| C[Построить таблицу в Excel]
  B -->|Нет| D[Вычислить недостающую величину 'PMT/RATE/NPER/PV']
  D --> C
  C --> E{Фиксированная ставка?}
  E -->|Да| F[Использовать простой график]
  E -->|Нет| G[Разбить по периодам и обновлять ставку]
  F --> H[Добавить сценарии досрочных платежей]
  G --> H
  H --> I[Проанализировать экономию и срок]

Факто‑бокс: ключевые числа примера

• Цена машины: $68,529
• Первоначальный взнос: $6,853
• Сумма займа: $61,676
• Годовая ставка: 5% → месячная 0.4167%
• Срок: 60 месяцев
• Ежемесячный платёж: ≈ $1,164
• Общая переплата по процентам при стандартном графике: ≈ $8,164
• При доплате $100/мес срок ≈ 54–55 месяцев, экономия ≈ $750 в процентах (примерный расчёт)

Уровни зрелости амортизационной модели

• Начальный: Статический график для фиксированной ставки, без учёта комиссий и страховки.
• Средний: График с полем для единовременных выплат и несколькими сценариями (изменение срока/доплаты).
• Продвинутый: Модель с переменной ставкой по периодам, учётом комиссий, налогов, эскроу, динамическими визуализациями и автоматическими отчётами.

Шаблон заголовков столбцов (копируемый)

Период | Начальный остаток | Ежемесячный платёж | Погашение основного долга | Платёж по процентам | Накоплено по основному долгу | Накоплено по процентам | Конечный остаток | Процентная ставка | Единовременные платежи

Контрольные тесты и критерии приёмки

• Таблица должна иметь ровно N строк, где N = число периодов.
• Сумма всех ежемесячных платежей плюс единовременные выплаты минус сумма займа = общая переплата по процентам.
• Конечный остаток в последней строке должен быть ≤ пару единиц валюты (проверка округления).

Практические советы и тонкости

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

Итог и рекомендации

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

Если вы не уверены в своём расчёте, попросите поддержку у специалиста банка или финансового консультанта. Для репликации используйте Google Sheets или экспортируйте файл Excel.

Важно

• Таблица даёт корректные результаты только при правильных исходных данных и правильных формулах.
• Для переменных ставок используйте обновления по каждому периоду.

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