Главная / Деньги и государство / Полезные формулы Excel для контроля финансов. Примеры функции ПЛТ в Excel: расчет выплат по аннуитетному кредиту Прогнозирование с помощью анализа "Что-если"

Полезные формулы Excel для контроля финансов. Примеры функции ПЛТ в Excel: расчет выплат по аннуитетному кредиту Прогнозирование с помощью анализа "Что-если"

По-настоящему мощным инструментом 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:

Функция ПЛТ в Excel входит в категорию «Финансовых». Она возвращает размер периодического платежа для аннуитета с учетом постоянства сумм платежей и процентной ставки. Рассмотрим подробнее.

Синтаксис и особенности функции ПЛТ

Синтаксис функции: ставка; кпер; пс; [бс]; [тип].

Расшифровка аргументов:

Ставка – это проценты по займу.
Кпер – общее количество платежей по ссуде.
Пс – приведенная стоимость, равноценная ряду будущих платежей (величина ссуды).
Бс – будущая стоимость займа после последнего платежа (если аргумент опущен, будущая стоимость принимается равной 0).
Тип – необязательный аргумент, который указывает, выплата производится в конце периода (значение 0 или отсутствует) или в начале (значение 1).

Особенности функционирования ПЛТ:

В расчете периодического платежа участвуют только выплаты по основному долгу и платежи по процентам. Не учитываются налоги, комиссии, дополнительные взносы, резервные платежи, иногда связываемые с займом.
При задании аргумента «Ставка» необходимо учесть периодичность начисления процентов. При ссуде под 6% для квартальной ставки используется значение 6%/4; для ежемесячной ставки – 6%/12.
Аргумент «Кпер» указывает общее количество выплат по кредиту. Если человек совершает ежемесячные платежи по трехгодичному займу, то для задания аргумента используется значение 3*12.

Примеры функции ПЛТ в Excel

Для корректной работы функции необходимо правильно внести исходные данные:

Размер займа указывается со знаком «минус», т.к. эти деньги кредитная организация «дает», «теряет». Для записи значения процентной ставки необходимо использовать процентный формат. Если записывать в числовом, то применяется десятичное число (0,08).

Нажимаем кнопку fx («Вставить функцию»). Откроется окно «Мастер функций». В категории «Финансовые» выбираем функцию ПЛТ. Заполняем аргументы:

Когда курсор стоит в поле того или иного аргумента, внизу показывается «подсказка»: что необходимо вводить. Так как исходные данные введены в таблицу Excel, в качестве аргументов мы использовали ссылки на ячейки с соответствующими значениями. Но можно вводить и числовые значения.

Обратите внимание! В поле «Ставка» значение годовых процентов поделено на 12: платежи по кредиту выполняются ежемесячно.

Ежемесячные выплаты по займу в соответствии с указанными в качестве аргументов условиями составляют 1 037,03 руб.

Чтобы найти общую сумму, которую нужно выплатить за весь период (основной долг плюс проценты), умножим ежемесячный платеж по займу на значение «Кпер»:

Исключим из расчета ежемесячных выплат по займу платеж, произведенный в начале периода:

Для этого в качестве аргумента «Тип» нужно указать значение 1.

Детализируем расчет, используя функции ОСПЛТ и ПРПЛТ. С помощью первой покажем тело кредита, посредством второй – проценты.

Для подробного расчета составим таблицу:

Рассчитаем тело кредита с помощью функции ОСПЛТ. Аргументы заполняются по аналогии с функцией ПЛТ:

В поле «Период» указываем номер периода, для которого рассчитывается основной долг.

Заполняем аргументы функции ПРПЛТ аналогично:

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

Рассчитываем остаток по основному долгу. Получаем таблицу следующего вида:

Общая выплата по займу совпадает с ежемесячным платежом, рассчитанным с помощью функции ПЛТ. Это постоянная величина, т.к. пользователь оформил аннуитетный кредит.

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

Функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.

Описание функции ПЛТ

Возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.

Синтаксис

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

Аргументы

ставка кпер пс бс тип

Обязательный. Процентная ставка по ссуде.

Обязательный. Общее число выплат по ссуде.

Обязательный. Приведенная (текущая) стоимость, т. е. общая сумма, которая на данный момент равноценна ряду будущих платежей, называется также «основной суммой».

Необязательный. Значение будущей стоимости, т. е. желаемого остатка средств после последнего платежа. Если аргумент «бс» опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0).

Необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата.

Замечания

Выплаты, возвращаемые функцией ПЛТ, включают основные платежи и платежи по процентам, но не включают налогов, резервных платежей или комиссий, иногда связываемых со ссудой.
Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов «ставка» и «кпер». Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте значения 12%/12 для задания аргумента «ставка» и 4*12 для задания аргумента «кпер». Если вы делаете ежегодные платежи по тому же займу, то используйте 12 процентов для задания аргумента «ставка» и 4 для задания аргумента «кпер».

Примеры

Пример1 Пример задачи1

Задача
Клиент банка осуществляет заем в размере 5000 руб. под 6% годовых на 6 месяцев. Определить ежемесячные платежи клиента. Платежи осуществляются в конце месяца.

Решение
Введем первоначальные данные.

В данной статье описана формула Excel, которую можно использовать для расчета величины аннуитетного платежа в Excel.

Для расчета величины аннуитетного платежа необходимо знать:

1. Сумму кредита.

2. Срок кредита.

3. Величину процента по кредиту.

4. Периодичность начисления процентов по кредиту (ежемесячно, еженедельно и так далее), а вернее количество платежных периодов для вида процентов.

Рассмотрим пример: нам необходимо рассчитать величину ежемесячного аннуитетного платежа по кредиту на сумму 100 000.00 рублей, сроком на 2 года, по ставке 18 процентов годовых . В данном случае количество платежных периодов будет равно 12 так как в году 12 месяцев (по условию задачи у нас рассматривается годовая процентная ставка).

Расчет величины аннуитетного платежа при помощи формулы

Для расчета аннуитетного платежа в Excel необходимо использовать функцию ПЛТ .

В английской версии Excel функция называется PMT .

Она имеет 5 параметров, из которых нам интересны первые 3 (ставка, кпер, пс ), остальные параметры не являются обязательными, поэтому их мы указывать не будем.

Описание параметров:

ставка – процентная ставка, приведенная к одному платежному периоду. Для нашего примера она будет равна / =. Обратите внимание, что процентная ставка должна быть указана в долях от 1. Итого получаем 0,015.

кпер – количество выплат по ссуде. В нашем примере выплаты по кредиту осуществляются ежемесячно, поэтому в качестве значения этого параметра указываем срок кредита, то есть 24 месяца.

пс – сумма кредита.

В результате получаем следующую формулу: =ПЛТ(0,015;24;100000) .

Полученное через формулу значение будет отрицательным, вносим небольшое исправление =-ПЛТ(0,015;24;100000) .

В итоге получаем величину аннуитетного платежа равной 4 992,41 рубль .

Расчет величины аннуитетного платежа на VBA

Для расчета величины аннуитета необходимо использовать функцию WorksheetFunction.Pmt , параметры этой функции аналогичны тем, что используются в функции для формул.

Расчет аннуитетного платежа на VBA по вышеуказанному примеру:

Annuitet = -WorksheetFunction.Pmt(0.015, 24, 100000)

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

Блок статей, посвященных теории и расчетам параметров аннуитета . В этой статье рассмотрены только синтаксис и примеры использования функции ПЛТ() .

Синтаксис функции ПЛТ()

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

Ставка. Процентная ставка по кредиту (ссуде).
Кпер. Общее число выплат по кредиту.
пс. Сумма кредита.
Бс. Необязательный аргумент. Требуемое значение остатка по кредиту после последнего платежа. Если этот аргумент опущен, предполагается, что он равен 0 (кредит будет полностью возвращен).
Тип. Необязательный аргумент. Принимает значение 0 (нуль) или 1. Если =0 (или опущен), то принимается, что регулярный платеж осуществляется в конце периода, если 1, то в начале периода (сумма регулярного платежа будет несколько меньше).

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

Пример 1

Предположим, человек планирует взять кредит в размере 50 000 руб. (ячейка В8 ) в банке под 14% годовых (B6 ) на 24 месяца (В7 ) (см. файле примера ).

Расчет Месячной суммы платежа по такому кредиту с помощью функции ПЛТ()

ПЛТ(B6/12;B7;B8)

СОВЕТ :
Убедитесь, что Вы последовательны в выборе временных единиц измерения для задания аргументов "ставка" и "кпер". В нашем случае рассчитываются ежемесячные выплаты по двухгодичному займу (24 месяца ) из расчета 14 процентов годовых (14% / 12 месяцев ).

Расчет Месячной суммы платежа по такому кредиту с помощью БЕЗ функции ПЛТ()

B8*(B6/12*(1+B6/12)^B7)/((1+B6/12)^B7-1)

Для нахождения суммы переплаты, умножьте возвращаемое функцией ПЛТ() значение на "кпер" (получите число со знаком минус) и прибавьте сумму кредита. В нашем случае переплата составит 7 615,46 руб. (за 2 года).

Пример 2

Предположим, человек планирует ежемесячно откладывать деньги, чтобы скопить через 5 лет (ячейка E7 ) 1 млн. рублей (E8 ). Деньги ежемесячно он планирует относить в банк и пополнять свой вклад. В банке действует процентная ставка 10% (E6 ) и человек полагает, что она будет действовать без изменений в течение 5 лет. Какую сумму человек должен ежемесячно относить в банк, чтобы таким образом через 5 лет скопить 1 млн. руб.? (см. файле примера ).