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

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

Функция ПЛТ

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

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

  • Ставка - процентная ставка по ссуде.
  • Кпер - общее число выплат по ссуде.
  • Пс - приведённая к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.
  • Бс - требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент «бс» опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение «бс» равно 0.
Функция СТАВКА

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

СТАВКА(кпер;плт;пс;бс;тип;прогноз)

  • Кпер - общее число периодов платежей для ежегодного платежа.
  • Плт - выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент «плт» состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент «пс» является обязательным.
  • Пс - приведённая (текущая) стоимость, т. е. общая сумма, которая на данный момент равноценна ряду будущих платежей.
  • Бс (необязательный аргумент) - значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если аргумент «бс» опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0).
  • Тип (необязательный аргумент) - число 0 (нуль), если платить нужно в конце периода, или 1, если платить нужно в начале периода.
  • Прогноз (необязательный аргумент) - предполагаемая величина ставки. Если аргумент «прогноз» опущен, предполагается, что его значение равно 10%. Если функция СТАВКА не сходится, попробуйте изменить значение аргумента «прогноз». Функция СТАВКА обычно сходится, если значение этого аргумента находится между 0 и 1.
Функция ЭФФЕКТ

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

Excel для Office 365 Excel для Office 365 для Mac Excel Online Excel 2019 Excel 2016 Excel 2019 для Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 для Mac Excel для Mac 2011 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Excel Mobile Excel Starter 2010 Меньше

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

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

Синтаксис

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

Примечание: Более подробное описание аргументов функции ПЛТ см. в описании функции ПС.

Аргументы функции ПЛТ описаны ниже.

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

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

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

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

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

Замечания

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

    Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов "ставка" и "кпер". Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте значения 12%/12 для задания аргумента "ставка" и 4*12 для задания аргумента "кпер". Если вы делаете ежегодные платежи по тому же займу, то используйте 12 процентов для задания аргумента "ставка" и 4 для задания аргумента "кпер".

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

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем - клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные

Описание

Годовая процентная ставка

Количество месяцев платежей

Сумма займа

Формула

Описание

Результат

ПЛТ(A2/12;A3;A4)

Ежемесячный платеж по займу в соответствии с условиями, указанными в качестве аргументов в диапазоне A2:A4.

ПЛТ(A2/12;A3;A4)

Ежемесячный платеж по займу в соответствии с условиями, указанными в качестве аргументов в диапазоне A2:A4, за исключением платежей, подлежащих оплате в начале периода.

Данные

Описание

Годовая процентная ставка

Количество месяцев платежей

Сумма займа

Формула

Описание

Оперативный результат

ПЛТ(A12/12;A13*12;0;A14)

Необходимая сумма ежемесячных платежей для выплаты 50 000р. за 18 лет.

ЛАБОРАТОРНЫЕ РАБОТЫ

Лабораторная работа №1

Тема: Финансовая функция ПЛТ

Время выполнения - 3 часа.

Цель работы: научиться использовать финансовую функцию ПЛТ табличного процессора Microsoft Excel для решения экономических задач, с использованием представленных примеров.

Последовательность выполнения:

1.Решить все описанные упражнения самостоятельно, руководствуясь методическими указаниями;

2. Выполнить задание;

3. Проверить свои знания по контрольным вопросам и сдать лабораторную работу.

Основные сведения по тее:

Финансовая функция ПЛТ

Лист1 в книге ФИНАНСОВЫЙ АНАЛИЗ переименуйте в ПЛТ. Все упражнения в данной лабораторной работе выполняйте на листе ПЛТ.

Рассмотрим пример расчета 30-летней ипотечной ссуды со ставкой 8% годовых при начальном взносе 20% и ежемесячной (ежегодной) выплате с помощью функции ПЛТ.

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

Рис. 4.1.1 Расчет ипотечной ссуды

Введите представленные на рис. 4.1.2. данные на лист ПЛТ и сравните полученный результат с данными на рис. 4.1.1.

Рис. 4.1.2 Формулы для расчета ипотечной ссуды

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

Синтаксис: ПЛТ(ставка; кпер; пс; бс; тип).

Аргументы:

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

Если бс = 0 и тип = 0, то функция ПЛТ вычисляет по формуле (1):

где Р - пс;

i - ставка;

n - кпер.

Отметим, что очень важно быть последовательным в выборе единиц измерения для задания аргументов ставка и КПЕР. Например, если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12% годовых, то для задания аргумента ставка используйте 12%/12, а для задания аргумента КПЕР - 4*12. Если вы делаете ежегодные платежи по тому же займу, то для задания аргумента ставка используйте 12%, а для задания аргумента КПЕР - 4.

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

В данной статье описана формула 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)