Финансовые расчеты в Excel. Функция ПЛТ (PMT)

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

Одна из самых больших и популярных категорий функций - финансовые. В последней версии Excel есть 55 функций, относящихся к этой группе. Многие из них специфические и узконаправленные, но некоторые могут пригодиться практически каждому. Одна из таких базовых функций - ПЛТ (PMT). 

Как гласит официальная справка, функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянной процентной ставки. Если Вас смущает специфический термин "аннуитет" - не пугайтесь. Иными словами, с помощью функции ПЛТ можно рассчитать сумму, которую нужно будет выплачивать каждый месяц при условии, что процент по кредиту не изменится и платежи вносятся регулярно равными суммами.   

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

Функция имеет следующий синтаксис:

ПЛТ(ставка; кпер; пс; [бс]; [тип]) 

Разберем по очереди все аргументы:

• Ставка. Обязательный аргумент. Представляет процентную ставку за  период. Самое главное здесь - не ошибиться в пересчете размера ставки на нужный период. Если предполагается погашать кредит ежемесячными платежами, а ставка годовая - то ее нужно перевести в месячную, разделив на 12. Если же, например, кредит гасится 1 раз в квартал, то годовую ставку нужно поделить на 4 (и получить таким образом ставку за 1 квартал). Ставку можно указать в процентах или в сотых долях. 
• Кпер. Обязательный. Этот аргумент представляет собой число расчетных периодов (сколько раз будет вноситься платёж в счёт погашения кредита). Как и ставка, этот аргумент зависит от того, какой расчетный период принят для вычислений. Если кредит получен на 5 лет с платежами 1 раз в месяц, то Кпер = 5*12 = 60 периодов. Если же на 3 года, с платежами 1 раз в квартал - то Кпер = 3*4 = 12 периодов.
• Пс. Обязательный. Сумма кредита, то есть объем долга, который нужно будет погасить будущими платежами.
• [бс]. Необязательный. Сумма долга, которая должна остаться неоплаченной после истечения всех расчетных периодов. Обычно этот аргумент равен 0 (кредит должен быть погашен полностью). Так как аргумент необязательный, то его можно не указывать (в таком случае он будет принят равным нулю).
• [тип]. Необязательный. Обозначает момент произведения выплаты - в начале или в конце периода. Для первого случая нужно указать единицу, а для второго ноль (или вообще пропустить этот аргумент). В большинстве случаев используется второй вариант - выплаты в конце периода, а значит чаще всего этот аргумент можно опустить.

Особенностью синтаксиса функции является указание направления денежного потока. Если денежный поток входящий (например, сумма полученного кредита, указанная в аргументе Пс), то необходимо указывать его как положительное число. Исходящие потоки наоборот, указываются как отрицательные числа (например, после вычисления функция ПЛТ вернет отрицательный результат, так как размер платежа по кредиту - это исходящий денежный поток).   

Примеры использования

Задача 1. Расчет суммы выплат по кредиту

Предположим, что в банке получен кредит на сумму 1 000 000 руб. под 17,5% годовых на срок 6 лет. Кредит будет погашаться равными платежами ежемесячно на протяжении всего срока займа. К концу срока будет выплачена вся сумма долга. Первый платеж будет внесен в конце первого периода. Необходимо найти величину ежемесячного платежа. 

Итак, нам известна годовая ставка, а кредит будет погашаться ежемесячно. Значит для расчета нам потребуется перевести годовую ставку в месячную, разделив 17,5% на 12 месяцев. В первый аргумент записываем 17,5%/12.

Кредит получен на 6 лет. Выплачивается ежемесячно. Значит, количество периодов выплат = 6*12. Во второй аргумент записываем 72. 

В третий аргумент пишем сумму кредита. Она равна 1 000 000 руб. (для займополучателя это входящий денежный поток, указываем его как положительное число).

Четвертый аргумент опустим, так как сумма будет полностью погашена к концу срока. 

Пятый аргумент также опустим, так как выплаты производятся в конце периода. 

Формула примет вид:

=ПЛТ(17,5%/12;72;1000000)

Результат вычисления равен -22526,05 руб. Число отрицательное, так как платеж по кредиту для займополучателя является исходящим денежным потоком. Именно такую сумму нужно будет вносить каждый месяц для погашения кредита, описанного в условии. 

Чтобы посчитать сумму итоговой переплаты, нужно умножить ежемесячный платеж на число периодов (Кпер) и вычесть из полученного результата сумму займа (Пс). 

Задача 2. Расчет суммы пополнения депозита для накопления определенного объема средств

В банке открыт пополняемый депозит со ставкой 9% годовых. Вы планируете каждый квартал вносить на депозит одинаковую сумму денег (например, часть полученной квартальной премии) с целью накопить на счете через 4 года ровно 1 000 000 руб. Вопрос: на какую сумму нужно пополнять счёт каждый квартал?

Первый аргумент указываем как 9%/4 (так как годовую ставку нужно перевести в квартальную), второй аргумент = 4*4 (4 года по 4 квартала - итого 16 взносов). Третий аргумент - сумма кредита. Его мы принимаем за 0, так как ничего не брали. Четвертый аргумент - будущая стоимость. Указываем сумму, которую хотим накопить (1 000 000 руб.). Пятый аргумент снова опускаем (выплаты в конце периода, это самая распространенная ситуация). 

Получим формулу:

=ПЛТ(9%/4;4*4;0;1000000). 

Результат вычисления: -52 616,63 руб. Такую сумму нужно вносить на указанный депозит каждый квартал, чтобы через четыре года иметь на счету  миллион рублей. 

Общая сумма внесенных средств = 52616,63 * 16 = 841 866,08 руб. Остальное накоплено за счет процентов. 

 

Особенности функции

При использовании функции обращайте внимание на следующие моменты:

• функция предназначена только для аннуитетных платежей (то есть равных платежей через равные промежутки времени);
• функция работает по классической кредитной модели, что не всегда совпадает с тем, что предлагают современные кредитные организации. Во многих случаях условия кредитования не позволят успешно применить к ним функцию ПЛТ и придется расписывать отдельную модель и искать решение с помощью Подбора параметра или Поиска решения (создание подобной модели можно заказать на нашем сайте - tDots.ru);
• функция учитывает выплату основной части долга и начисленных процентов, но не принимает в расчет различные дополнительные начисления, комиссии, налоги и сборы и т.д.;
• знак числа (положительный или отрицательный) задаёт направление денежного потока. Поток от кредитора к должнику (например, сумма займа) будет иметь один знак, а поток от должника к кредитору (например, сумма ежемесячного погашения) - противоположный (неважно, плюс или минус).

Поддержать наш проект и его дальнейшее развитие можно вот здесь.

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

С уважением, команда tDots.ru