ПЛТ — одна из финансовых функций, возвращающая сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянной процентной ставки.
Воспользуйтесь средством Excel Formula Coach для расчета ежемесячных выплат по ссуде. При этом вы узнаете, как использовать функцию ПЛТ в формуле.
Синтаксис
ПЛТ(ставка; кпер; пс; [бс]; [тип])
Примечание: Более подробное описание аргументов функции ПЛТ см. в описании функции ПС.
Аргументы функции ПЛТ описаны ниже.
Ставка Обязательный аргумент. Процентная ставка по ссуде.
Кпер Обязательный аргумент. Общее число выплат по ссуде.
Пс Обязательный аргумент. Приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.
Fv Необязательный. Будущая стоимость или баланс, который вы хотите достичь после последнего платежа. Если значение «ок» опущено, предполагается значение 0 (ноль), то есть будущая стоимость займа составляет 0.
Тип Необязательный аргумент. Число 0 (нуль) или 1, обозначающее, когда должна производиться выплата.
Когда нужно платить
Замечания
Выплаты, возвращаемые функцией ПЛТ, включают основные платежи и платежи по процентам, но не включают налогов, резервных платежей или комиссий, иногда связываемых со ссудой.
Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов «ставка» и «кпер». Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте значения 12%/12 для задания аргумента «ставка» и 4*12 для задания аргумента «кпер». Если вы делаете ежегодные платежи по тому же займу, то используйте 12 процентов для задания аргумента «ставка» и 4 для задания аргумента «кпер».
Совет Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, умножьте возвращаемое функцией ПЛТ значение на «кпер».
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Пример функции ПЛТ для расчета аннуитетного платежа в Excel
Функция ПЛТ в Excel используется для расчета фиксированного значения суммы периодических взносов для выплат задолженностей при условии, что процентная ставка является постоянной величиной, и возвращает соответствующее значение.
Примеры использования функции ПЛТ в Excel
С помощью функции ПЛТ можно рассчитать фиксированную сумму ежемесячного платежа по кредиту в банке, если известны тело кредита, значение годовой процентной ставки и число периодов выплат (либо срок действия договора кредитования). Также функция удобна для расчета суммы ежемесячных выплат по депозитам с дополнительными взносами.
Расчет ежемесячного платежа по кредиту в Excel
Пример 1. Определить размер ежемесячного платежа по кредиту с процентной ставкой 23% на сумму 25000 рублей, который должен быть выплачен на протяжении 3 лет.
Вид таблицы данных:
Для получения искомого значения введем следующую формулу:
Результат выполнения формулы:
Полученное значение является отрицательным числом, поскольку ежемесячные платежи по кредиту являются расходными операциями для заемщика.
Пример расчета суммы переплаты по кредиту в Excel
Пример 2. Определить сумму, которую переплатит заемщик, взявший кредит на сумму 50000 с годовой процентной ставкой 27% и 12 периодами выплат в год. Срок кредитования составляет 5 лет.
Вид таблицы данных:
Для расчета суммы переплат необходимо из общей суммы выплат по кредиту за период действия договора вычесть тело кредита. Для этого используем следующую формулу:
Произведение результата, возвращаемого функцией ПЛТ и количества периодов выплат (B4*B5) соответствует общей сумме выплат за 5 лет. Поскольку функция ПЛТ возвращает отрицательное значение, используем функцию ABS для получения абсолютного значения. В результате вычислений получим:
Клиент банка выплатит 50000 рублей тела кредита и еще около 42000 рублей процентов.
Формула вычисления оптимального ежемесячного платежа по кредиту в Excel
Пример 3. В банке был открыт депозитный счет с начальной суммой 200 000 рублей. Условия договора позволяют выполнять ежемесячное пополнение данного счета. Определить, какую сумму необходимо вносить ежемесячно, чтобы спустя 4 года получить 2000000 рублей. Процентная ставка составляет 11% годовых.
Вид таблицы данных:
Искомое значение может быть определено с помощью следующей формулы:
Примечание: для получения корректного результата аргумент пс должен принимать отрицательное значение суммы первоначального взноса.
В результате расчетов получим следующее значение:
Для накопления 2 млн. рублей клиенту банка потребуется ежемесячно вносить на депозитный счет примерно 28000 рублей.
Аннуитет — график погашения кредита, предполагающий выплату основного долга и процентов по кредиту равными суммами через равные промежутки времени. Это один из самых простых способов для расчета графика платежей, позволяющий точно определить сумму ежемесячных выплат и спланировать бюджет.
Для заемщика он удобен:
Как рассчитать аннуитетный платеж
Есть несколько способов самостоятельно выполнить расчет аннуитетного платежа: вручную или в с помощью кредитного калькулятора. Для расчета надо знать всего три параметра: сумму, процентную ставку и срок займа.
При ручном подсчете с помощью обычного калькулятора или программы MS Excel вам придется самостоятельно выводить формулы и подставлять в них значения. на сайтах банков — простой и наглядный инструмент, который мгновенно пересчитывает сумму ежемесячного платежа при изменении суммы, ставки или срока кредита. Он позволяет быстро сравнить условия разных банков и продуктов и выбрать наиболее выгодное предложение.
Формула расчета аннуитета вручную
Формула выглядит так:
Для расчета возьмем кредит на сумму 20000 рублей под 12% годовых, оформленный на 36 месяцев.
Рассчитаем процентную ставку в месяц (ПС) = 12*100/12 = 0,01
После выполнения расчетов получим:
Теперь можно определить переплату за весь период займа. Для этого размер ежемесячного взноса умножается на количество месяцев, а затем из полученного значения вычитают тело кредита — сумму основного долга, который вы получаете наличными.
Получается 3 904 руб.
Расчет графика платежей на компьютере
В Excel также можно выполнить расчет аннуитетного графика через финансовую функцию ПЛТ. Для этого нужно заполнить необходимые значения в мастере аргументов функции — окно появляется при нажатии кнопки fx.
Кпер — это срок кредита в месяцах, Пс — тело кредита.
Итоговым значением будет 664 руб., что аналогично результату ручного подсчета.
Как выглядит кредитный калькулятор
Выполнить действия гораздо быстрее и получить итоги в развернутом виде позволяют кредитные калькуляторы на банковских сайтах и финансовых ресурсах.
Стандартный калькулятор состоит из 4 ячеек, у каждой могут быть выпадающие списки:
Многие калькуляторы могут учитывать дату выдачи средств, досрочное погашение и другие дополнительные параметры.
Алгоритм действий простой:
Сервис рассчитает сумму ежемесячного взноса с разбивкой по основному долгу и процентами переплату. Итоги будут предложены в виде таблицы с графиком платежей.
Для наглядности вставим в ячейки калькулятора те же данные из предыдущего примера.
Калькулятор выдал сумму аннуитетного взноса в размере 664,29 руб. (второй столбец «Всего»).
Аннуитетом называется поток платежей одинакового размера, поступающих через равные промежутки времени. Период времени между двумя последовательными платежами является расчетным при начислении процентов.
Рис. 49. Тип аннуитета задает распределение n платежей одинакового размера по границам процентных периодов внутри срока аннуитета.
В зависимости от момента поступления первого платежа различают два типа потоков платежей – пренумерандо (первый платеж в начале первого периода) и постнумерандо (в конце). За счет более раннего поступления денежных средств и удлиненного на один период срока начисления процентов в случае пренумерандо можно достигнуть больших финансовых результатов по сравнению с потоком платежей, вносимых в конце периода.
Пример. Пять платежей по три рубля каждый нужно внести по схеме пренумерандо. Получатель аннуитета использует эти средства с доходностью R = 8% за период между платежами.
Какова будущая стоимость FV этого срочного аннуитета (срок n = 5) в конце пятого периода в результате начисления процентов на все поступившие платежи? Обозначим размер одного платежа буквой A. Тогда
Рис. 50. Вычисление будущей стоимости каждого платежа и аннуитета пренумерандо в конце срока.
Какую сумму достаточно вложить на 5 периодов с начислением 8% сложных, чтобы в конце срока снять 19,01 руб.?
Текущая стоимость бессрочного аннуитета ( вечной ренты при бесконечно большом сроке n ) есть сумма всех членов бесконечно убывающей геометрической прогрессии со знаменателем 1/(1+ R ), которая при R R > 0 сходится.
Эквивалентная ей в конце срока будущая стоимость срочного аннуитета постумерандо есть
Процентный множитель будущей стоимости аннуитета FVIFA(R,n) – Future Value Interest Factor of Annuity является основным финансовым коэффициентом, который показывает, какую сумму можно накопить, постоянно получая выплаты единичного размера в течение срока n при начислении R % сложных за каждый период на уже аккумулированные денежные средства.
Процентный множитель текущей стоимости аннуитета PVIFA(R,n) – Present Value Interest Factor of Annuity также является финансовым коэффициентом, и показывает, какую сумму достаточно инвестировать в начальный момент времени, чтобы потом регулярно в течении срока, состоящего из n процентных периодов получать платежи единичного размера с учетом начисления на оставшиеся денежные средства R % сложных за период.
Знакомство с условностями автоматизации финансовых расчетов в среде процессора электронных таблиц начнем со встроенной функции =FV(rate; nper; pmt; pv; type)
=БЗ(норма; число_периодов; выплата; нз; тип) в исходной русификации
=БС(ставка; кпер; плт; пс; тип) в новейшей русификации.
Пример. Господин Иванов в конце каждого месяца переводит 1000р. за счет в банк, начисляющий ежемесячно сложные проценты по номинальной ставке 9% годовых. Какая сумма накопится на счете за два года, при сохранении на это время всех указанных условий без изменения?
Рис. 51. Применение функции БЗ=FV для расчета будущей стоимости аннуитета.
Таблица 13 Аннуитетные финансовые функции в исходной русификации
Встроенная функция Excel
Таблица 1 4 Аннуитетные финансовые функции в новейшей русификации
Встроенная функция Excel 2002
Выполним расчет будущей стоимости аннуитета поэтапно. Ниже, на рис. 52, в восьмой строке таблицы рабочего листа дан формат вызова функции =БЗ, возвращающий то же самое числовое значение, которое в ячейке седьмой строки найдено по рекуррентным формулам.
Рис. 52. «Аннуитетный треугольник» постнумерандо.
В зависимости от выбора пользователем из полного списка аргументов встроенной функции =БЗ(норма; число_периодов; выплата; нз; тип) подмножества тех аргументов, значения которых известны в задаче, можно с помощью одной и той же функции посчитать и наращенную сумму вклада, и будущую стоимость аннуитета, причем с переключением формул между типами потоков платежей постнумерандо и пренумерандо.
Рассмотрим полностью возможные варианты.
1,46 р. = FV(0,1;4;0;-1;0) =FV(0,1;4;0;-1;0) =FV(0,1;4;;-1) – будущая стоимость одного вложенного рубля ( нз=-1 ) после четырех раз ( число_периодов=4 ) присоединения к нему процентных денег, начисляемых в конце периода по ставке сложных процентов 10% ( норма=0,1) без дополнительных поступлений и выплат. В связи с полным отсутствием в течение срока промежуточного потока платежей нет смысла уточнять и момент их поступления в нулевом размере ( тип=0, значение используется по умолчанию).
1,61 р. =FV(0,1;5;0;-1;0) =FV(0,1;5;0;-1;0) =FV(0,1;5;;-1) – будущая стоимость одного вложенного рубля ( нз=-1 ) после пяти раз ( число_периодов=5 ) присоединения к нему процентных денег, начисляемых в конце периода по ставке сложных процентов 10% ( норма=0,1) без дополнительных поступлений и выплат ( выплата=0, тип=0 ).
6,11 р. = FV(0,1;5;-1;0;0) = FV(0,1;5;-1;0;0) =FV(0,1;5;-1) – будущая стоимость потока пяти периодических платежей ( число_периодов=5 ) единичного размера, вносимых ( выплата=-1 ) регулярно в конце периода (потоку постнумерандо соответствует тип=0, значение используется по умолчанию) при начислении 10% сложных ( норма=0,1) за период между моментами внесения платежей на поступившие ранее средства.
Пример. Молодой человек c пятнадцатилетнего возраста в конце каждого месяца регулярно вносит по 15 долл. на сберегательный счет в банк, начисляющий на всю растущую сумму сложные проценты по номинальной ставке 15% годовых. В каком возрасте этот человек может стать миллионером?
Выразим срок (число периодических платежей) из формулы будущей стоимости аннуитета:
Используя определение и свойства логарифма, самостоятельно продолжите вывод формулы срока накопления миллиона в условиях задачи и найдите ответ на поставленный вопрос.
Рис. 53. Применение функции КПЕР=NPER для определения срока аннуитета.
Найденный срок выражен в месяцах. 542/12=45 полных лет, так что сумма 15+45 дает искомый в задаче возраст 60 лет.
Какую сумму достаточно вложить на такой же срок единовременно, чтобы при той же доходности при ежемесячном начислении сложных процентов накопить 1 млн.долл.?
При какой годовой процентной ставке удастся накопить миллион к 55 годам?
Ответ: 17,3% =RATE((55-15)*12;-15;;1000000)*12.
При каком размере ежемесячного платежа удастся накопить миллион к 50 годам без изменения ставки 15%?
Варьировать параметры задачи можно и неявно, подгоняя влияющие исходные данные, например, размер ежемесячного платежа, под искомую будущую стоимость 1 млн.долл. (см. рис. 54).
Рис. 54. Подбор значения будущей стоимости аннуитета изменением размера платежа.
Неявное уравнение, используемое всеми финансовыми калькуляторами и электронными таблицами для расчета неизвестных показателей аннуитета по известным можно обнаружить в Справочной системе Excel в разделе, посвященном функции =ПЗ. Для преодоления проблем с терминологией здесь оно приводится в обозначениях оригинала:
Таблица 15 Реакция неявного уравнения на нулевые значения финансовых параметров
Наращение однократно вложенной суммы
Накопление будущей суммы потоком вносимых через равные периоды времени платежей одинакового размера
Если же решается аннуитетная задача, в которой известен размер платежа, а дополнительные единовременные начальные вложения отсутствуют, то в силу условия PV=0 элиминируется первое слагаемое, и остается зависящая от размера платежа формула будущей стоимости аннуитета с начислением процентов за период между платежами.
Получается балансовая модель роста сложных процентов, учитывающая направление движения средств: то, что дали в долг – положительно, а то, что будет потом возвращаться кредитору с процентами обратно, с точки зрения должника, отрицательно
Второй сомножитель по умолчанию равен единице (случаю постнумерандо соответствует тип=0), а если оценивается аннуитет пренумерандо (тип=1), то получается процентный множитель (1+RATE), отражающий дополнительный период начисления сложных процентов за счет более раннего начала поступления потока платежей
Для существования ненулевых корней этого соотношения знаки величин затрат и поступлений должны быть друг другу противоположны
Пример. Инвестор выдает должнику кредит в объеме 300 тыс. руб. Возврат долга планируется в виде квартального аннуитета с выплатой 75 тыс. руб. постнумерандо (обыкновенной финансовой ренты) на протяжении 5 кварталов.
Оценим процентную ставку R за один квартал. Подставляя исходные данные в формулу текущей стоимости аннуитета, получаем следующее уравнение относительно новой переменной x = (1 + R) – процентного множителя за один квартал:
Рис. 55. Поведение заданного многочлена шестой степени от ставки R на интервале [0%; 10%].
Рис.56 Применение функции НОРМА=RATE для нахождения доходности аннуитета.
Используя процедуру Excel Подбор параметра, подгоните к 300 тыс. руб. значение суммы строки нулевого периода в «верхнем аннуитетном треугольнике» (см. рис. 57).
Какая процентная ставка R за период доставляет эту текущую стоимость?
Повторите подбор, используя в качестве зависимой от исходного значения ставки R формулы обращение ко встроенной функции PV=ПЗ.
Рис. 57. «Верхний аннуитетный треугольник».
Каким должен быть размер периодического платежа, чтобы внесение пяти одинаковых платежей такого размера по схеме постнумерандо позволило погасить долг 300 тыс. руб. по ставке 8% за период?
Проценты начисляются на невыплаченную часть долга («правило США 4 «). При соблюдении равенства периодических платежей друг другу изменяется пропорция между двумя составными частями платежа (см. рис. 58).
Рис.58. Сравнение графиков погашения долга.
Сначала по аннуитетной формуле (здесь это сделано при помощи функции PMT) определяется сумма платежа – 75 137 тыс. руб. Затем каждый платеж разбивается на части следующим образом: PMT = PPMT + IPMT.
Сумма всех частей платежа PPMT, погашающих долг, равна 300 тыс.руб. Дисконтированная же по ставке кредитования (процент ная ставка в данном примере R = 8%) сумма платежей PMT также равна исходной сумме долга. Для расчета частей периодического платежа, размер которых зависит от текущего периода k, в Excel также имеются встроенные функции PPMT и IPMT (см. табл. 16).
Таблица 16 Функции для расчета двух переменных составляющих частей постоянной суммы платежа
Встроенная функция Excel
Часть платежа, идущая в зачет погашения основного долга
ОСНПЛАТ(норма;период;кпер;тс;бс;тип) в исходной русификации ОС ПЛТ( ставка ;период;кпер; п с;бс;тип) в новейшей русификации
PPMT(rate;k;nper;pv;fv;type) в оригинальной версии
Часть платежа, равная процентной плате за остаток долга в данном периоде
ПЛПРОЦ(норма;период;кпер;тс;бс;тип) в исходной русификации ПЛПРОЦ( ставка ;период;кпер; п с;бс;тип) в новейшей русификации
IPMT(rate;k;nper;pv;fv;type) в оригинальной версии
Так, например, можно получить разбиение второго платежа на погашение основного долга –55,228=PPMT(0,08;2;5;300) и процентную часть –19,909=IPMT(0,08;2;5;300).
Рис.59. Эквивалентность потоков платежей погашения долга по разным схемам.
Обе рассмотренные схемы погашения долга: и равными платежами, и неравными, эквивалентны друг другу по начальной стоимости кредита. Это обстоятельство иногда используют в анализе инвестиционных проектов, вычисляя аннуитет (размер годового платежа), эквивалентный исходному денежному потоку в смысле равенства чистого дисконтированного дохода. При простом арифметическом суммировании всех платежей без дисконтирования эти потоки друг от друга отличаются, но с точки зрения экономической теории процента, такое «измерение дохода» за несколько периодов не имеет смысла, поскольку полагает цену денег во времени равной нулю, что на финансовом рынке невозможно. 3 См., напр.: Уотшем Т. Дж., Паррамоу. Количественные методы в финансах. М., 1999. 4 См., например, статью «United States rule» в Федоров Б.Г. «Англо-русский толковый словарь валютно-кредитных терминов». – М., 1992.
Примеры функции ПЛТ в Excel: расчет выплат по аннуитетному кредиту
Функция ПЛТ в Excel входит в категорию «Финансовых». Она возвращает размер периодического платежа для аннуитета с учетом постоянства сумм платежей и процентной ставки. Рассмотрим подробнее.
Синтаксис и особенности функции ПЛТ
Синтаксис функции: ставка; кпер; пс; [бс]; [тип].
Особенности функционирования ПЛТ:
Примеры функции ПЛТ в Excel
Для корректной работы функции необходимо правильно внести исходные данные:
Размер займа указывается со знаком «минус», т.к. эти деньги кредитная организация «дает», «теряет». Для записи значения процентной ставки необходимо использовать процентный формат. Если записывать в числовом, то применяется десятичное число (0,08).
Нажимаем кнопку fx («Вставить функцию»). Откроется окно «Мастер функций». В категории «Финансовые» выбираем функцию ПЛТ. Заполняем аргументы:
Когда курсор стоит в поле того или иного аргумента, внизу показывается «подсказка»: что необходимо вводить. Так как исходные данные введены в таблицу Excel, в качестве аргументов мы использовали ссылки на ячейки с соответствующими значениями. Но можно вводить и числовые значения.
Обратите внимание! В поле «Ставка» значение годовых процентов поделено на 12: платежи по кредиту выполняются ежемесячно.
Ежемесячные выплаты по займу в соответствии с указанными в качестве аргументов условиями составляют 1 037,03 руб.
Чтобы найти общую сумму, которую нужно выплатить за весь период (основной долг плюс проценты), умножим ежемесячный платеж по займу на значение «Кпер»:
Исключим из расчета ежемесячных выплат по займу платеж, произведенный в начале периода:
Для этого в качестве аргумента «Тип» нужно указать значение 1.
Детализируем расчет, используя функции ОСПЛТ и ПРПЛТ. С помощью первой покажем тело кредита, посредством второй – проценты.
Для подробного расчета составим таблицу:
Рассчитаем тело кредита с помощью функции ОСПЛТ. Аргументы заполняются по аналогии с функцией ПЛТ:
В поле «Период» указываем номер периода, для которого рассчитывается основной долг.
Заполняем аргументы функции ПРПЛТ аналогично:
Дублируем формулы вниз до последнего периода. Для расчета общей выплаты суммируем тело кредита и проценты.
Рассчитываем остаток по основному долгу. Получаем таблицу следующего вида:
Общая выплата по займу совпадает с ежемесячным платежом, рассчитанным с помощью функции ПЛТ. Это постоянная величина, т.к. пользователь оформил аннуитетный кредит.
Таким образом, функция ПЛТ может применяться для расчета ежемесячных выплат по вкладу или платежей по кредиту при условии постоянства процентной ставки и сумм.
