Как рассчитать аннуитет в Excel: ПЛТ, ПС, БС

Аннуитет в Excel считают пятью функциями, и каждая отвечает на свой вопрос: ПЛТ (PMT) находит размер платежа, ПС (PV) показывает, какая сумма нужна сейчас, БС (FV) считает накопления, КПЕР (NPER) определяет срок, а СТАВКА (RATE) возвращает ставку за период. Ниже по шагам разобрано, как вводить месячную ставку и срок, чем отличается платёж в начале и в конце месяца и почему результат часто получается со знаком минус.

Что такое аннуитет и какой вопрос вы решаете в Excel

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

Представьте простую ситуацию: вы хотите, чтобы после накопления вам ежемесячно выплачивалась фиксированная сумма в течение 20 лет. Нужно понять, какая начальная сумма для этого потребуется. Это задача для функции ПС. А если начальная сумма уже есть и вы хотите узнать, сколько можно получать каждый месяц, вам нужна функция ПЛТ.

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

Какая функция для какой задачи: сводная таблица

Названия функций в русской версии Excel отличаются от английских, поэтому ниже они приведены парами. Если у вас английская версия программы, ищите функцию по правому названию.

Функция	На какой вопрос отвечает	Что вводим	Что получаем
ПЛТ / PMT	Какой платёж нужен каждый период?	Ставку, число периодов, начальную сумму	Размер регулярного платежа
ПС / PV	Какая сумма нужна сейчас?	Ставку, число периодов, регулярную выплату	Текущую стоимость серии платежей
БС / FV	Сколько накопится к концу срока?	Ставку, число периодов, регулярный взнос	Будущую стоимость
КПЕР / NPER	Сколько периодов потребуется?	Ставку, платёж, начальную или целевую сумму	Число периодов
СТАВКА / RATE	Какая ставка за период подразумевается?	Число периодов, платёж, начальную сумму	Ставку за один период

Назначение первых четырёх функций описано в справке Microsoft (ссылки в конце статьи). По функции СТАВКА опорой служит руководство Spreadsheet Planet.

Подготовка данных: месячная ставка и срок в месяцах

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

Заведите отдельные ячейки для исходных данных:

• B1 — годовая ставка (например, 5%);
• B2 — срок в годах (например, 20);
• B3 — число платежей в год (12);
• B4 — ставка за период: =B1/B3, это около 0,4167%;
• B5 — число периодов: =B2*B3, это 240.

Проверка условия. Деление годовой ставки на 12 корректно, когда проценты начисляются ежемесячно. Если условия вашего вклада или договора другие, сначала уточните, как именно начисляются проценты.

Во всех примерах ниже используется один сквозной сценарий: начальная сумма 100 000 условных денежных единиц, 5% годовых, 20 лет, платёж в конце месяца. В источнике этот пример приведён в долларах, здесь валюта не привязана ни к какой стране.

ПЛТ: сколько платить или получать каждый месяц

Вопрос. Какой равный платёж нужен каждый период при заданных ставке, сроке и начальной сумме?

Синтаксис. =ПЛТ(ставка; кпер; пс; [бс]; [тип]). В английской версии Excel вместо точки с запятой используется запятая: =PMT(rate, nper, pv, [fv], [type]).

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

Входные ячейки. B4 содержит месячную ставку, B5 — число периодов, B6 — начальную сумму 100 000.

Формула. =ПЛТ(B4; B5; B6)

Как читать результат. Для сквозного примера получается около 659,96 в месяц (так в руководстве Spreadsheet Planet; это значение при указанных условиях, а не универсальное число). Excel покажет его со знаком минус, если начальная сумма введена положительной. Причина в правиле знаков денежных потоков: платёж и начальная сумма считаются потоками в разные стороны. Чтобы получить положительное число, поставьте перед функцией минус: =-ПЛТ(B4; B5; B6).

Обратите внимание: по данным справки Microsoft, в платёж, который возвращает ПЛТ, могут не входить налоги, резервы и комиссии.

ПС: какая сумма нужна сейчас, чтобы получать выплаты каждый месяц

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

Синтаксис. =ПС(ставка; кпер; плт; [бс]; [тип]), в английской версии =PV(rate, nper, pmt, [fv], [type]).

Условный пример. Вы хотите получать 500 в месяц в течение 20 лет при 5% годовых, выплата в конце месяца. Ячейка B7 содержит 500.

Формула. =ПС(B4; B5; B7)

Как читать результат. Excel вернёт примерно −75 760. Минус означает сумму, которую нужно вложить (исходящий поток), а ежемесячные выплаты по 500 — входящий поток. Число округлено, это условный пример, пересчитайте его в своём файле.

Связь с ПЛТ. Это обратная задача. В сквозном примере начальной сумме 100 000 соответствует платёж около 659,96. Платёж 500 меньше примерно в 1,32 раза, поэтому и нужная сумма меньше: около 75 760. Так можно проверить один расчёт другим.

БС: сколько накопится при регулярных взносах

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

Синтаксис. =БС(ставка; кпер; плт; [пс]; [тип]), в английской версии =FV(rate, nper, pmt, [pv], [type]).

Условный пример. Взнос 300 в месяц, 5% годовых, 20 лет, начальной суммы нет. Ячейка B8 содержит −300.

Формула. =БС(B4; B5; B8)

Как читать результат. Получится около 123 300 (округлённо, условный пример, сверьте результат в своём файле: в последних разрядах возможны расхождения из-за округления).

Начальная сумма. Если у вас уже есть накопления, укажите их в аргументе «пс». Исходящие потоки, то есть деньги, которые вы откладываете, вводятся отрицательными числами. Поэтому взнос записан как −300, а начальные накопления, если они есть, вводятся тоже со знаком минус.

Почему взнос отрицательный. Откладывая деньги, вы отдаёте их, это исходящий поток. Накопленная сумма в конце вернётся к вам как входящий поток, поэтому результат положительный.

КПЕР и СТАВКА: срок и ставка по известным данным

КПЕР: сколько периодов потребуется. Синтаксис: =КПЕР(ставка; плт; пс; [бс]; [тип]), в английской версии =NPER(rate, pmt, pv, [fv], [type]). Функция возвращает число периодов при постоянных ставке и платеже. Если платежи ежемесячные, результат получится в месяцах: чтобы перевести его в годы, разделите на 12, например =КПЕР(B4; B8; 0; B9)/12, где B9 — цель накопления. Здесь взнос в B8 введён отрицательным, а цель в B9 положительной.

СТАВКА: какая ставка подразумевается. Синтаксис: =СТАВКА(кпер; плт; пс; [бс]; [тип]), в английской версии =RATE(nper, pmt, pv, [fv], [type]). Функция возвращает ставку за один период (по описанию в руководстве Spreadsheet Planet). Для помесячных платежей результат нужно перевести в годовую ставку: умножьте его на 12. Это номинальная годовая ставка, эффективная ставка в статье не рассматривается.

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

Платёж в начале или в конце периода: аргумент «тип»

Аргумент «тип» определяет, когда происходит платёж. Значение 0 или пропуск означает конец периода, значение 1 — начало периода (так сказано в справке Microsoft и в примере Excel Office).

Сравним на сквозном примере (100 000, 5% годовых, 20 лет):

• платёж в конце месяца: =ПЛТ(B4; B5; B6; 0; 0), около 659,96;
• платёж в начале месяца: =ПЛТ(B4; B5; B6; 0; 1), около 657,22 (расчёт автора как иллюстрация: 659,96 ÷ (1 + 0,05/12); пересчитайте формулой с типом 1 в своей таблице).

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

Что выбрать. Ориентируйтесь на условия вашей ситуации: если взнос вносится или выплата приходит в начале месяца, ставьте 1; если в конце — 0 или оставьте аргумент пустым.

Знаки чисел и частые ошибки

Правило денежных потоков. В финансовых функциях Excel исходящие платежи записываются отрицательными числами, входящие — положительными. Поэтому результат часто получается со знаком минус. Это не поломка формулы. Если нужно положительное число, поставьте минус перед функцией.

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

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

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

Вопросы и ответы

Какой функцией считать ежемесячный платёж?

Функцией ПЛТ (PMT). Ей нужны ставка за период, число периодов и начальная сумма. При месячных платежах годовую ставку делят на 12, а срок в годах умножают на 12.

Как получить каждый месяц нужную сумму и сколько для этого нужно накопить?

Используйте функцию ПС (PV): укажите желаемую ежемесячную выплату, ставку за период и число периодов. Результат покажет сумму, которая нужна в начале. В условном примере выплата 500 в месяц на 20 лет при 5% годовых даёт около 75 760.

Почему результат отрицательный?

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

Как ввести ставку и срок при помесячных платежах?

Ставка за период равна годовой ставке, делённой на 12 (если проценты начисляются ежемесячно), число периодов равно числу лет, умноженному на 12. Например, 5% годовых на 20 лет — это 0,05/12 и 240 периодов.

Подходит ли расчёт, если ставка или платёж меняются?

Не напрямую. Функции описаны для постоянных ставки и платежа, поэтому при изменяющихся условиях результат будет лишь приблизительным ориентиром.

Присоединяйтесь к нашему сообществу