График амортизации кредита в Excel — пошаговое руководство

Что такое график амортизации и зачем он нужен

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

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

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

Важно: базовый пример в статье использует фиксированную годовую ставку 5%, срок 60 месяцев и исходную цену автомобиля $68,529 с первоначальным взносом $6,853 (величины из примера). Это демонстрационные данные, вы подставляете свои.

Основные термины в одну строку

• Платёж (Payment): сумма, которую вы платите регулярно (ежемесячно).
• Процент (Interest): плата за пользование займом за период.
• Тело кредита (Principal): оставшаяся сумма долга.
• Остаток (Balance): непогашенная часть кредита в текущий момент.

Какие формулы Excel понадобятся (кратко)

• PMT — вычисляет размер периодического платежа при заданной ставке, сроке и сумме кредита.
• RATE — находит процентную ставку при известном платеже, сроке и сумме кредита.
• NPER — определяет количество периодов (например, месяцев) при известных платеже и ставке.
• PV — вычисляет настоящую стоимость (сумму кредита), которую можно получить при заданных условиях.

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

Пример для практики (исходные данные)

В примере мы будем моделировать покупку 2022 Mustang Mach 1 Premium с условиями:

• Оценочная чистая цена: $68,529
• Первоначальный взнос: $6,853
• Сумма кредита (Loan Amount): $61,676 (цена минус взнос)
• Ежемесячный платёж: $1,164
• Срок: 60 месяцев
• Процентная ставка (годовая): 5%

Эти цифры служат базой. Часто у вас будет одна неизвестная — ставка, срок, платёж или сумма. Excel-функции помогут найти любую из них.

Как рассчитывать пропущенные значения в Excel (подробно)

• Чтобы найти ежемесячный платёж при известной сумме кредита, ставке и сроке, используйте PMT:

=-PMT(RATE/12, NPER, PV)

Здесь RATE/12 — месячная ставка, NPER — число месяцев, PV — начальная сумма долга. Минус перед PMT нужен, чтобы получить положительное значение платежа (Excel возвращает отрицательное, если исходные аргументы положительны).

• Чтобы найти годовую процентную ставку при известных платеже, сумме и сроке, используйте RATE:

=RATE(NPER, -PMT, PV) * 12

RATE возвращает месячную ставку, поэтому умножаем на 12, чтобы получить годовую.

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

=NPER(RATE/12, -PMT, PV)

• Чтобы вычислить, сколько вы можете взять в кредит при заданном платеже, ставке и сроке, используйте PV:

=PV(RATE/12, NPER, -PMT)

Совет: всегда следите за знакомыми (плюс/минус) аргументами — Excel использует денежные потоки и ожидает, что входящие и исходящие суммы имеют противоположные знаки.

Построение таблицы амортизации с фиксированной процентной ставкой — подробный шаг за шагом

1. Подготовьте областb с исходными данными (рекомендуем в строках 1–8):

   • B1: Total Amount (Общая цена)
   • B2: Down Payment (Первоначальный взнос)
   • B3: Loan Amount (Сумма кредита) — формула =B1-B2
   • B4: Interest Rate (Годовая ставка) — как десятичная 0.05 или 5%
   • B5: Loan Period (Срок в месяцах) — например, 60
   • B6: Monthly Payment (Ежемесячный платёж) — =-PMT(B4/12,B5,B3)

2. Создайте заголовки таблицы амортизации (рекомендуем в строке 9):

   • Период
   • Начальный остаток
   • Ежемесячный платёж
   • Погашение основного долга
   • Платёж процентов
   • Накопленный основной долг
   • Накопленные проценты
   • Конечный остаток
   • Процентная ставка
   • Единовременные платежи (Lump Sum Payments)

3. Заполните первую строку (месяц 1) — пример для ячеек начиная с A9:

   • A9: «Месяц 1» (или просто 1)
   • B9 (Начальный остаток): =$B$3
   • C9 (Ежемесячный платёж): =$B$6
   • E9 (Проценты): =B9*(I9/12) — I9 содержит годовую ставку в виде 0.05
   • D9 (Погашение основного долга): =C9-E9
   • F9 (Накопленный основной): =D9
   • G9 (Накопленные проценты): =E9
   • H9 (Конечный остаток): =B9-D9
   • I9 (Процентная ставка): =$B$4
   • J9 (Единовременные платежи): оставьте пустым или введите сумму, если есть

Примечание: используйте абсолютные ссылки ($B$3, $B$6, $B$4), чтобы при автозаполнении они не смещались.

Заполнение последующих строк (месяц 2 и далее)

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

• B10 (Начальный остаток) = H9 - J9 — если в предыдущем периоде были единовременные платежи, они уменьшают окончательный остаток; если поле J пустое, то это просто H9
• C10 (Ежемесячный платёж) = $B$6
• E10 (Проценты) = B10*(I10/12)
• D10 (Погашение основного долга) = C10 - E10
• F10 (Накопленный основной) = F9 + D10
• G10 (Накопленные проценты) = G9 + E10
• H10 (Конечный остаток) = B10 - D10
• I10 (Процентная ставка) = I9 (или $B$4 если фиксированная)
• J10 (Единовременные платежи) — вводите вручную, если применимо

После заполнения второй строки выделите её (B10:J10), скопируйте и вставьте вниз до нужного количества месяцев (например, до месяца 60). Также можно использовать автозаполнение, как показано ниже.

Скорректируйте формат чисел и графики

• Отформатируйте валюты через «Формат ячеек» → Валюта.
• Процентную ставку отобразите как процент с двумя знаками.
• Для наглядности добавьте диаграмму: «Линия» для остатка по времени и «Столбцы» для разбиения процентов и тела.

Работа с единовременными и досрочными платежами

• В поле «Единовременные платежи» (Lump Sum Payments) введите сумму дополнительного погашения в нужном периоде (месяц/год). Таблица автоматически уменьшит остаток, и последующие месяцы покажут меньшие проценты и/или более быстрое погашение.
• Чтобы увидеть эффект годового бонуса, введите сумму в соответствующий месяц и сравните суммарные уплаченные проценты.

Пример: если в 12-м месяце вы внесёте единовременный платёж $5,000, то начиная с 13-го месяца начальный остаток будет уменьшен, и проценты по оставшейся сумме будут рассчитываться от меньшего баланса.

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

Чек-лист для проверки:

• Сумма накопленного основного долга в конце равна первоначальной сумме кредита (если нет дополнительных платежей и точного округления).
• Последний конечный остаток должен быть близок к нулю (из-за округлений может быть ±1 цент).
• Если при автозаполнении месяцы не соответствуют ожидаемым, проверьте абсолютные/относительные ссылки ($).
• Если проценты растут вместо снижения — проверьте, не перепутаны ли столбцы «Проценты» и «Погашение основного».

Важно: ошибки обычно происходят из-за неверных ссылок, забытых $ или неверного деления ставки на 12.

Моделирование сценариев и анализ чувствительности

Рекомендации по моделированию:

• Создайте отдельный лист «Сценарии», где меняйте срок, ставку, размер единовременных платежей и сравнивайте суммарные проценты.
• Сделайте таблицу «Что если», используя функцию Excel «Диспетчер сценариев» (What-If Analysis → Scenario Manager).
• Используйте условное форматирование, чтобы подсветить месяц, когда остаток станет ≤ 0.

Пару практических сценариев:

• Сценарий A: базовый (60 месяцев, $1,164 в мес.).
• Сценарий B: досрочное погашение $200 в месяц сверх регулярного платежа. Сравните общее количество месяцев и суммарные проценты.
• Сценарий C: один единовременный платёж $5,000 на 12-м месяце.

Эти сценарии покажут экономию процентов и изменение срока.

Когнитивные модели и правила-эвристики для принятия решения

• Правило 1: Маленькое дополнительное регулярное погашение уменьшает проценты значительно за счёт того, что сокращается база по которой начисляются проценты.
• Правило 2: Единовременные платежи наиболее эффективны, когда делаются раньше в сроке кредита, потому что экономия процентов выше.
• Правило 3: Сравнивайте альтернативный доход: если инвестиции при том же риске дают доход выше ставки по кредиту, возможно, лучше инвестировать, чем гасить кредит преждевременно.

Пара ментальных моделей: «Снежный ком» (выплата сначала маленьких долгов) и «Аврал» (погашение самого дорогого долга по процентной ставке). Для автокредита обычно лучше ориентироваться на экономию процентов.

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

• Онлайн-калькуляторы амортизации: быстро создают графики и позволяют выгружать CSV.
• Google Sheets: те же формулы работают, но интерфейс автозаполнения и ссылки идентичны. Особенность: Sheets активнее синхронизирует и удобен для совместной работы.
• Бухгалтерское ПО: если у вас множество кредитов, используйте специализированные инструменты для учета и сценариев.

Как учитывать переменную ставку

Для кредитов с плавающей ставкой стандартная фиксированная таблица не подходит. Подход:

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

Пример: ставка меняется на 0.5% в середине срока. Обновите столбец I с соответствующей процентной ставкой для каждого месяца и пересчитайте столбцы процентов и окончательного остатка с того момента.

Типы амортизации: равные платежи vs равные погашения основного долга

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

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

Практическое руководство — шаблон столбцов и ключевые формулы (шаблон для копирования)

Колонки (A–J):

Формулы (предполагаем, что начальные данные находятся в B1:B6):

B9 = $B$3
C9 = $B$6
I9 = $B$4
E9 = B9*(I9/12)
D9 = C9 - E9
F9 = D9
G9 = E9
H9 = B9 - D9
J9 = 0 или ваша сумма

B10 = H9 - J9
C10 = $B$6
I10 = I9
E10 = B10*(I10/12)
D10 = C10 - E10
F10 = F9 + D10
G10 = G9 + E10
H10 = B10 - D10
J10 = 0 или ваша сумма

Копируйте строки 10 вниз до конца срока.

SOP — пошаговый рабочий процесс для создания таблицы (короткий план)

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

Роли и контрольные списки

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

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

Для консультанта/бухгалтера:

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

Когда график амортизации не даёт полного ответа (примеры и ограничения)

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

Визуализация и презентация партнёру

• Добавьте диаграмму остатка по времени и стэк-диаграмму «проценты vs тело» по месяцам.
• Экспортируйте лист в PDF для печати или отправки.
• Если используете Google Sheets, включите доступ «Просмотр» или «Редактирование» и дайте ссылку партнёру.

Пример сценария расчёта эффекта досрочной выплаты (качественно)

Допустим, вы платите $200 сверх ежемесячного платежа. Это уменьшит начальный остаток быстрее и снизит сумму начисленных процентов. Чем раньше вы начнёте такие дополнительные платежи, тем больше итоговой экономии.

Правило: дополнительный платёж уменьшает тело кредита, а значит и будущие начисления процентов. Экономия процентов очень чувствительна к времени: $1,000 досрочно заплаченные в начале срока сэкономят больше, чем $1,000, внесённые в последний год.

Часто задаваемые вопросы

Что делать, если в последней строке остаток стал отрицательным?

Это нормальный эффект округления. Уменьшите последний ежемесячный платёж или вручную установите конечный остаток в ноль и скорректируйте последний платёж.

Как учитывать налоги и страхование?

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

Можно ли использовать этот шаблон для ипотеки?

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

Короткое резюме

График амортизации — мощный инструмент планирования: он показывает, как платежи распределяются между процентами и основным долгом, и помогает оценивать эффект досрочных и единовременных платежей. Собрать таблицу в Excel просто: используйте PMT/NPER/RATE/PV, внимательно применяйте абсолютные ссылки и проверяйте итоговые остатки.

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

Руководство к действию — 5 шагов сейчас

1. Введите свои реальные данные в блок B1–B6.
2. Проверьте рассчитанный ежемесячный платёж через PMT.
3. Постройте одну-две строки таблицы амортизации и проверьте их вручную.
4. Автозаполните до конца срока, проверьте суммарные проценты.
5. Смоделируйте хотя бы один сценарий досрочной выплаты.

FAQPage (коротко)

• Вопрос: Можно ли автоматически сокращать срок займа при дополнительных платежах?

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

• Вопрос: Как учитывать комиссии и дополнительные сборы?

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