Расчёт платежей по кредиту в Excel с помощью функции PMT

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

Зачем использовать PMT и краткие определения

PMT — простая финансовая функция Excel для расчёта фиксированного периодического платежа по кредиту или аннуитету. Она полезна владельцам бизнеса, бухгалтерам, студентам и тем, кто планирует покупку в кредит.

Краткие определения (1 строка каждый):

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

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

Синтаксис функции PMT

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

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

Пример: если ставка годовая 10% и платежи ежемесячные, используйте rate = 10%/12 и nper = число лет * 12.

Рекомендуемая структура листа

Совет: вводите параметры в отдельные ячейки и ссылайтесь на них в формуле. Это упрощает чтение и тестирование.

Пример расположения ячеек:

• A2 — Сумма займа (PV), например -15000
• B2 — Годовая ставка (в виде десятичной), например 0.10
• C2 — Срок в годах, например 5
• D2 — Число периодов (NPER) = C2*12
• E2 — Месячная ставка = B2/12
• F2 — Платёж = =PMT(E2, D2, A2)

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

Входные данные:

• PV = -15000 (заём поступил вам, поэтому отрицательное значение в модели платежей);
• Annual rate = 10% (0.10);
• Years = 5;
• Monthly rate = 0.10 / 12;
• NPER = 5 * 12 = 60.

Формула для расчёта месячного платежа:

=PMT(B2/12, D2, A2)

Где B2 — годовая ставка, D2 — общее число периодов, A2 — сумма займа (с отрицательным знаком).

Как интерпретировать знак результата

Excel возвращает платёж со знаком, противоположным pv: если pv отрицательный, PMT обычно будет положительным числом (денежный отток при выплате). Это чисто бухгалтерская конвенция: важно понимать, какие ячейки вы используете при дальнейших расчётах.

Подсчёт общей суммы платежей и общей суммы процентов

Общая сумма платежей за весь срок:

=PMT(Ежемесячная_ставка, NPER, PV)*NPER

В нашем примере, если PMT в C2 и NPER в D2:

=C2*D2

Общая сумма уплаченных процентов:

=Общая_сумма_платежей - ABS(PV)

Если общая сумма платежей в C4 и PV в A2:

=C4-ABS(A2)

Таблица амортизации: как построить и зачем

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

Пример колонок и формул (предположим, строка 10 — заголовки, строка 11 — первый платёж):

• A11 — Номер платежа = 1, в A12 = A11+1 и т.д.
• B11 — Дата платежа (например, =EDATE(StartDate, A11-1))
• C11 — Платёж = $F$2 (фиксированная ссылка на PMT)
• D11 — Процентная часть = BalancePrev * MonthlyRate
• E11 — Погашение основного долга = C11 - D11
• F11 — Остаток = BalancePrev - E11

Начальный остаток (BalancePrev для первой строки) равен ABS(PV).

Пример формул (предположения: начальный баланс в F10):

A11 = 1
B11 = EDATE(StartDate, A11-1)
C11 = $F$2
D11 = F10 * $E$2
E11 = C11 - D11
F11 = F10 - E11

Скопируйте вниз на NPER строк, чтобы получить полный график платежей.

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

Использование Goal Seek для подбора параметров

Если вы хотите получить конкретный платёж (например, $500 в месяц) и найти, при каких условиях это возможно, используйте инструмент Поиск решения (Goal Seek).

Шаги:

1. Подготовьте лист с функцией PMT в отдельной ячейке (например, F2).
2. Перейдите на вкладку Данные → Анализ «Что–если» → Поиск решения.
3. В поле «Установить ячейку» укажите ячейку с PMT (F2).
4. В поле «Значение» введите желаемый платёж (например, -500 или 500 в зависимости от конвенции знаков).
5. В поле «Изменяя ячейку» укажите ту ячейку, которую хотите менять (например, годовая ставка B2 или срок C2).
6. Нажмите OK и дождитесь результата.

Советы:

• Не просите Goal Seek менять ячейку с формулой PMT — он должен менять входной параметр.
• Можно запускать поэтапно для нескольких переменных, но стандартный Goal Seek меняет только одну ячейку. Для нескольких используйте Поиск решения (Solver) или скрипт.

Функция RATE: как быстро найти процентную ставку

Если известны PMT, NPER и PV, можно найти RATE:

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

Пример: если вы хотите, чтобы платёж был $500 при pv = -15000 и nper = 60, используйте:

=RATE(60, -500, -15000)

Замечание: параметры знаков должны быть согласованы — PMT и PV обычно имеют противоположные знаки.

Альтернативные подходы и функции Excel

• PPMT — вычисляет часть платежа, идущую на погашение основного долга за конкретный период.
• IPMT — вычисляет процентную часть платежа за период.
• NPER — возвращает количество периодов при известных ставке, платеже и PV.
• CUMIPMT и CUMPRINC — суммарные проценты и погашение основного долга за диапазон периодов.
• Использование шаблонов «Кредит» в Excel или готовых онлайн-калькуляторов для быстрой оценки.

Когда PMT не подходит (ограничения)

• Плавающая ставка: PMT предполагает фиксированную ставку на весь срок.
• Нерегулярные платежи: PMT предполагает равные по сумме периодические платежи.
• Сложные схемы с бонусными или отсроченными платежами, крупными баллонными выплатами — требуется индивидуальная модель.

Типичные ошибки и как их избежать

• Неправильная конверсия ставки и периодов: используйте месячную ставку с месячными NPER.
• Неверные знаки: pv отрицательное и pmт положительный (или наоборот) — согласуйте.
• Забыли учесть тип (0/1): платеж в начале периода изменит расчёт процентов.
• Ставка = 0: PMT вернёт ошибку деления; используйте простую формулу PV/NPER.

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

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

• PMT совпадает с известным примером (ручный расчёт).
• Общая сумма платежей = PMT * NPER.
• Общая выплата процентов = Общая сумма платежей - ABS(PV).
• Амортизация сходится в ноль (остаток после NPER = 0, если fv = 0).

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

1. Нулевой процент: PV=10000, rate=0, nper=10 → PMT = 1000.
2. Один период: nper=1 → PMT = -PV*(1+rate).
3. Проверка даты: EDATE(StartDate, NPER) соответствует ожидаемому месяцу.

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

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

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

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

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

Для разработчика, автоматизирующего расчеты:

• Валидация входных данных (rate >= 0, nper > 0, pv <> 0).
• Логирование исходных параметров и результатов.
• Тесты погрешности при малых и больших значениях.

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

• Разложение платежа: платеж = проценты (остаток*ставка) + погашение основного долга.
• Чем длиннее срок, тем ниже платёж, но выше суммарные проценты.
• Небольшое снижение ставки даёт заметный эффект на многолетних кредитах.

Практическое руководство (короткий SOP)

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

Безопасность и конфиденциальность

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

Глоссарий в одну строку

• PMT: Ежемесячный/периодический платёж; PV: сумма займа; NPER: число периодов; FV: остаток; RATE: ставка за период.

Что ещё учитывать при принятии решения

• Комиссии и страхование часто не включаются в PMT — добавляйте их в бюджет отдельно.
• Если кредит с переменной ставкой, моделируйте сценарии (best/worst/base) по ставке.

Важно

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

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

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

Дополнительные материалы и шаблоны

• Используйте шаблон амортизации: создайте таблицу с колонками Номер, Дата, Платёж, Проценты, Погашение, Остаток и заполните формулами, приведёнными выше.
• Для многокомпонентных сценариев рассмотрите использование Power Query или VBA для автоматизации расчётов.

Спасибо за внимание — используйте PMT как часть проверки бюджета и планирования, а не как единственный источник решений.