Функция PMT в Excel — расчёт выплат по кредиту

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

Что такое функция PMT

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

Синтаксис:

=PMT(rate, nper, pv, [fv], [type])

Краткие определения:

• rate — процентная ставка за период (например, годовая ставка/12 для месячных платежей).
• nper — общее число периодов платежа (например, лет * 12 для месяцев).
• pv — текущая стоимость (present value), то есть сумма кредита (в Excel обычно вводят отрицательным значением, чтобы платежы отображались положительными числами).
• fv — будущая стоимость; по умолчанию 0 (чтобы в конце кредита остаток был нулевым).
• type — 0 или 1: 0 — платеж в конце периода (по умолчанию), 1 — в начале.

Важно: единицы rate и nper должны соответствовать друг другу. Если вы рассчитываете ежемесячные платежи, используйте месячную ставку и количество месяцев.

Быстрый пример: кредит $15,000 на 5 лет под 10% годовых

Предположим, вы берёте кредит 15,000 $ на 5 лет под 10% годовых. Процент и платежи считаем ежемесячно (платёж в конце месяца).

• PV = -15000
• Годовая ставка = 10% → месячная ставка = 10%/12
• NPER = 5 лет × 12 = 60 месяцев

Формула в Excel (при ссылках на ячейки):

=PMT(B2/12, D2, A2)

Где B2 — годовая ставка (в виде 0.10), D2 — общее количество месяцев (60), A2 — сумма кредита (в виде -15000).

Если в ячейке C2 окажется значение PMT, то общая выплата за весь срок — это C2 * D2:

=C2*D2

А суммарные проценты, уплаченные банку, можно получить так:

=C4-ABS(A2)

Здесь C4 — общая выплата (C2*D2), ABS(A2) берёт абсолютное значение суммы кредита.

Картинка примера:

Как получить дополнительные инсайты: амортизационная таблица

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

Простой шаблон столбцов для амортизации (ежемесячно):

• Номер периода (1..N)
• Остаток в начале периода
• Платёж (PMT)
• Проценты за период = Остаток в начале * месячная ставка
• Погашение тела = Платёж - Проценты
• Остаток в конце = Остаток в начале - Погашение тела

Примеры формул (предположим, месячная ставка в B2, PMT в C2, начальный остаток в A3 = 15000):

Проценты (ячейка D3) = A3*$B$2
Погашение тела (ячейка E3) = $C$2 - D3
Остаток в конце (ячейка F3) = A3 - E3
Остаток в начале следующей строки A4 = F3

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

Использование Goal Seek вместе с PMT

Иногда вы знаете желаемый платёж, а не ставку или срок. Тогда используйте «Подбор параметра» (Goal Seek).

Шаги:

1. Введите формулу PMT в ячейку (например, C2).
2. Вкладка “Данные” → “Анализ сценариев” (Forecast) → “Подбор параметра” (Goal Seek).
3. В диалоге: Set Cell — ячейка с PMT, To value — желаемая сумма платежа, By changing cell — ячейка, которую Excel будет менять (например, ставка B2 или срок D2).
4. Нажмите “OK”; Excel подберёт значение.

Подсказка: нельзя в качестве меняемой ячейки выбирать саму ячейку с формулой PMT, иначе формула будет перезаписана. Goal Seek подходит для одного меняемого параметра; для нескольких используйте “Поиск решения” (Solver) или ручные переборы.

Когда PMT не подойдёт — ограничения и альтернативы

PMT хорош для аннуитетных платежей при фиксированной ставке и равных платежах. Но функция не учтёт:

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

Альтернативы:

• Использовать функцию RATE для вычисления ставки при известных платёжах и сроке.
• Использовать функцию NPER для расчёта числа периодов при известных платёжах и ставке.
• Построить кастомную модель амортизации для плавающей ставки или гибридных схем.
• Применить “Поиск решения” (Solver) для оптимизации по нескольким параметрам.

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

• Всегда держите единицы согласованными: месячный rate ↔ месячный nper.
• Для отображения положительных чисел платежей вводите PV отрицательным: это повышает читаемость отчёта (например, -15000 → платеж 318.05).
• Если платежы в начале периода (аннуитет с платёжами в начале), укажите type=1.
• Добавьте проверку сумм: Total payments = PMT * NPER; Total interest = Total payments - ABS(PV).
• Для сравнения кредитов приводите всё к годовым эквивалентам и показывайте эффективную процентную ставку (APR) отдельно.

Мини-методология: как быстро оценить кредит в Excel

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

Чек-листы по ролям

Заёмщик:

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

Финансовый аналитик / бухгалтер:

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

Критерии приёмки

• Формула PMT возвращает значение и не перезаписывается вручную.
• Общая выплата = PMT × NPER совпадает с суммой по амортизационной таблице (погрешность из-за округления не более нескольких центов).
• Остаток в конце последнего периода ≈ 0 (с учётом округлений).

Примеры формул и приёмы

Стандартный пример (ссылки на ячейки):

=PMT(B2/12, D2, A2)

Расчёт общей суммы выплат:

=C2*D2

Расчёт суммарного процента:

=C4-ABS(A2)

Использование RATE для вычисления процентной ставки при заданных PMT и NPER:

=RATE(nper, pmt, pv, [fv], [type])

Маленькое руководство по досрочному погашению

Если планируете частично гасить кредит раньше срока:

1. Пересчитайте остаток после досрочного погашения.
2. Решите: уменьшаете срок или размер платежа.
3. Обновите NPER или PMT и проверьте новую общую переплату.

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

Ментальные модели и правила принятия решений

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

Decision flow (упрощённая схема выбора подхода)

flowchart TD
  A[Нужен расчёт платежа?] -->|Да| B{Данные известны}
  B -->|Сумма, ставка, срок| C[Использовать PMT]
  B -->|Есть желаемый платёж| D[Использовать Goal Seek или RATE]
  B -->|Переменная ставка| E[Построить модель амортизации по периодам]
  C --> F[Построить амортизацию]
  D --> F
  E --> F
  F --> G[Сравнить варианты и принять решение]

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

Что делать, если платежи ежеквартальные, а ставка годовая?

Разделите годовую ставку на 4 и используйте число кварталов как NPER (годы × 4). Всегда согласуйте периодичность ставки и количества периодов.

Как учитывать единовременные комиссии?

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

Как посчитать эффективную годовую ставку (APR)?

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

Часто встречающиеся ошибки и как их избежать

• Несогласованные единицы (месяцы vs годы): всегда перепроверяйте nper и rate.
• Перезапись ячейки с формулой PMT при использовании Goal Seek: используйте другую ячейку для изменяемой величины.
• Игнорирование комиссий и страховок: включайте их в расчёт, чтобы не недооценивать стоимость кредита.

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

PMT — быстрый и надёжный инструмент для расчёта равных периодических выплат по кредиту при фиксированной ставке. В сочетании с амортизационной таблицей, Goal Seek и дополнительными функциями Excel (RATE, NPER) вы получите полное представление о стоимости кредита, суммарных процентах и сценариях досрочного погашения. Применяйте единообразные единицы измерения, учитывайте комиссии и тестируйте несколько сценариев перед принятием решения.

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

Важное: используйте “Поиск решения” (Solver), когда нужно менять несколько параметров одновременно, а Goal Seek подходит для одного параметра.

FAQ — короткие ответы

• Как быстро сравнить два кредита? Сравните общую выплату и суммарные проценты при одинаковых входных данных; используйте одну и ту же амортизационную структуру.
• Что лучше: уменьшить платёж или срок при досрочном погашении? Математически выгоднее сокращать срок — это уменьшит суммарную переплату по процентам.