Функция ПЛТ в Excel и ещё восемь финансовых: КПЕР, СТАВКА, ЧПС, ВСД

Ставка, срок и сумма кредита связаны одной формулой, и банковский платёж по ней получается за секунды. Справка Microsoft требует, чтобы единицы ставки и числа периодов совпадали: для ежемесячных выплат по займу на 4 года под 12% годовых берут 12%/12 и 4*12^[1].

Порядок работы с функцией ПЛТ в Excel такой: найти в договоре ставку, срок и сумму, ввести формулу, проверить результат умножением платежа на число периодов, исправить знак или единицы и протянуть график на свои данные. Остальные финансовые функции Excel — КПЕР, СТАВКА, ПС, БС, ЧПС и ВСД — описывают тот же расчёт с других сторон.

Ключевые факты: функция ПЛТ в Excel

• ПЛТ возвращает регулярный платёж по аннуитету, а аргументы «бс» и «тип» по умолчанию равны 0^[1].
• Для ежемесячных выплат по займу на 4 года под 12% годовых справка берёт ставку 12%/12 и число периодов 4*12^[1].
• ВСД требует хотя бы одного положительного и одного отрицательного значения и делает не больше 20 попыток^[3].
• Блок «Применяется к» в справке по ПЛТ начинается с Microsoft 365 и заканчивается Excel 2016, включая версии для Mac^[1].
• ЧПС в отличие от ВСД принимает до 254 значений перечнем, а ВСД — массив или ссылку на ячейки^[4][3].

Как работает функция ПЛТ в Excel: аргументы и единицы ставки

Функция ПЛТ в Excel возвращает равный периодический платёж по кредиту или накоплению по ставке, сроку и сумме. Синтаксис по справке Microsoft: =ПЛТ(ставка;кпер;пс;[бс];[тип])^[1]; в английском интерфейсе функция называется PMT.

ПЛТ не знает, в каких единицах задан срок, — она лишь сопоставляет ставку и число периодов. Если написать =ПЛТ(12%;36;-1000000), формула посчитает 36 периодов под 12% каждый и вернёт 122 064 вместо 33 214,31. Годовую ставку делят на 12, срок в годах умножают на 12^[1].

Знак — это направление денег. Сумма, которую вы получаете, положительная, а выплаты отрицательные: так знаки расставлены и в примерах справки^[5]. Перед суммой займа ставят минус, если платёж должен выйти положительным.

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

Кредитный калькулятор по шагам: график с ОСПЛТ и ПРПЛТ

Кредитный калькулятор на функции ПЛТ в Excel раскладывает каждый платёж на проценты и основной долг. Рядом работают ПРПЛТ и ОСПЛТ: по справочнику Microsoft первая возвращает проценты за данный период, вторая — платёж с основного капитала^[2].

1. Внесите условия: в B1 — сумму 1000000, в B2 — ставку 12%, в B3 — срок 36.
2. Пронумеруйте месяцы от 1 до 36 в A6:A41.
3. В B6 введите =ПЛТ($B$2/12;$B$3;-$B$1), в C6 — =ПРПЛТ($B$2/12;A6;$B$3;-$B$1), в D6 — =ОСПЛТ($B$2/12;A6;$B$3;-$B$1).
4. В E6 посчитайте остаток =$B$1-D6, а в E7 — =E6-D7.
5. Протяните строку 6 до 36-го месяца и проверьте, что остаток в последней строке равен нулю.

ПЛТ в столбце B даёт одну и ту же сумму, а её состав меняется: в первый месяц 10 000 уходит на проценты и только 23 214,31 — на долг, в последний наоборот. Проверка графика простая: сумма столбца «Долг» равна сумме кредита, а сумма «Проценты» — переплате 195 715,15.

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

Однако банк не всегда называет платёж: иногда он известен, а неизвестны срок, ставка или сумма.

КПЕР, СТАВКА, ПС или БС: какую функцию взять для обратной задачи

КПЕР, СТАВКА, ПС и БС решают обратные задачи к функции ПЛТ в Excel: по известным величинам находят недостающую. Справочник Microsoft описывает их так: КПЕР — число периодов выплаты, СТАВКА — ставку по аннуитету за период, ПС — приведённую, а БС — будущую стоимость^[2].

ПЛТ остаётся точкой отсчёта: в каждой из этих функций те же ставка, число периодов и суммы, только неизвестное другое. Примеры КПЕР, ПС, БС и накоплений взяты из справки Microsoft о платежах и сбережениях^[5].

КПЕР отвечает на вопрос «за сколько месяцев я закрою долг, если буду платить больше». При платеже 40 000 вместо 33 214,31 миллионный кредит уйдёт за 28,9 месяца — =КПЕР(12%/12;-40000;1000000). Дробный ответ — норма: для целых месяцев результат округляют вверх.

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

Пример оценки проекта: ЧПС и ВСД

ЧПС и ВСД оценивают проект с неравными денежными потоками, где функция ПЛТ в Excel бессильна. Финансовые функции этой пары работают с любыми суммами: ЧПС приводит потоки к сегодняшнему дню по ставке, а ВСД находит ставку, при которой ЧПС равна нулю^[3].

ВСД требует хотя бы одного положительного и одного отрицательного значения, а порядок чисел функция читает как очерёдность выплат и поступлений^[3]. Предположение по умолчанию равно 0,1, то есть 10%. Расчёт идёт итерациями: если за 20 попыток точность 0,00001% не достигнута, появится #ЧИСЛО!^[3].

ЧПС принимает от 1 до 254 значений, и они должны приходиться на конец каждого периода через равные промежутки^[4]. Вложение в начале первого периода в аргументы не включают, а прибавляют к результату: =ЧПС(10%;3000;4200;6800)-10000 вернёт 1 307,29, тогда как пример справки =ЧПС(10%;-10000;3000;4200;6800) с вложением через год даёт 1 188,44^[4].

В Google Таблицах ВСД устроена так же: денежные потоки — массив или диапазон хотя бы с одним отрицательным и одним положительным значением, а примерная ставка по умолчанию равна 0,1^[6]. Остаётся понять, где функция ПЛТ и её соседи дают неверный ответ.

Когда функция ПЛТ не подойдёт и какие ошибки встречаются

Функция ПЛТ не подойдёт, если платежи неравные или в платёж нужно включить комиссии: она считает регулярный платёж, куда входят только основная сумма и проценты^[1]. Ниже — сценарии, где ответ окажется неверным.

• Если банк начисляет дифференцированные платежи, ПЛТ не подходит: долг там гасят равными частями, а проценты считают на остаток, поэтому платёж каждый месяц разный.
• Если в договор входят страховка и комиссии, их прибавляют отдельно: по справке Microsoft результат ПЛТ их не включает^[1].
• При потоках с неравными интервалами ЧПС и ВСД дадут искажённую оценку: для нерегулярных дат справка Google советует ЧИСТВНДОХ^[6], а справочник Microsoft — ЧИСТВНДОХ и ЧИСТНЗ^[2].
• Когда ставка и число периодов заданы в разных единицах, ответ выглядит правдоподобно, но неверен — проверьте деление ставки на 12 и умножение срока на 12^[1].

По блоку «Применяется к» справка Microsoft перечисляет для ПЛТ Excel 2016, Excel 2019, Excel 2021, Excel 2024 и Microsoft 365, причём три новейшие — вместе с версиями для Mac^[1]. Процентные расчёты попроще — доли, наценки, проценты от суммы — разобраны на странице о процентах от числа и долях, а остальные функции собраны в справочнике формул Excel для расчётов и анализа.