Финансовая функция плт. Описание функции плт. Прогнозирование с помощью анализа "Что-если"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ПЛТ(Ставка;Кол_пер;Пс;[Бс];[Тип])


ПС

Формула ПС применяется для расчета приведенной стоимости инвестиции. Данная функция обратная оператору ПЛТ . У неё точно такие же аргументы, но только вместо аргумента приведенной стоимости («ПС» ), которая собственно и рассчитывается, указывается сумма периодического платежа («Плт» ). Синтаксис соответственно такой:

ПС(Ставка;Кол_пер;Плт;[Бс];[Тип])


ЧПС

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

ЧПС(Ставка;Значение1;Значение2;…)


СТАВКА

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

СТАВКА(Кол_пер;Плт;Пс[Бс];[Тип])


ЭФФЕКТ

Оператор ЭФФЕКТ ведет расчет фактической (или эффективной) процентной ставки. У этой функции всего два аргумента: количество периодов в году, для которых применяется начисление процентов, а также номинальная ставка . Синтаксис её выглядит так:

ЭФФЕКТ(Ном_ставка;Кол_пер)


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

В статье рассмотрены финансовые функции ПЛТ() , ОСПЛТ() , ПРПЛТ() , КПЕР() , СТАВКА() , ПС() , БС() , а также ОБЩДОХОД() и ОБЩПЛАТ() , которые используются для расчетов параметров аннуитетной схемы.

Данная статья входит в цикл статей о расчете параметров аннуитета. Перечень всех статей на нашем сайте об аннуитете.

В этой статье содержится небольшой раздел о теории аннуитета, краткое описание функций аннуитета и их аргументов, а также ссылки на статьи с примерами использования этих функций.

Немного теории

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

Синтаксис ПРПЛТ (ставка; период; кпер; пс; бс, тип). (2.13)

Аргументы функции означают: ставка

период - задает период, для которого надо найти платежи по процентам, значение должно быть в интервале от 1 до "кпер";

кпер - общее число периодов платежей по аннуитету;

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

бс - требуемое значение будущей стоимости или остатка средств после последней выплаты;

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

Если данная функция недоступна или возвращает ошибку # ИМЯ?, то установите и загрузите надстройку «Пакет анализа". Для этого в меню Сервис Выбрать команду Надстройки Excel. В списке надстроек выберите Пакет анализа и нажмите кнопку ОК. Следуйте инструкциям программы установки, если это необходимо.

Решение: ПРПЛТ (10% / 12, 1, 12 * 3; 800) = - 6,667 тыс. Грн.

Пример 2.28. За счет ежегодных отчислений в течение 6 лет был сформирован фонд в 500 тыс. Грн. Надо рассчитать, какой доход приносили вложения владельцу за последний год , если годовая ставка составляла 17,5%.

Решение: Доход за последний год (6 периодов) составил:

ПРПЛТ (17,5%; 6; 6;; 500) = 66,48110268 тыс. Грн.

Ежегодно полагалось ПЛТ (17,5%; 6;; 500) = - 53,627 тыс. Грн.

Сумма основного платежа по займу (выплата задолженности), который погашается равными платежами в конце или в начале каждого расчетного периода, на указанный период рассчитывается с помощью функции Excel ОСПЛТ:

Синтаксис ОСПЛТ (ставка; период; кпер; пс; бс; тип) (2.14)

или находится как разница между фиксированной периодической выплатой и процентам по непогашенной части займа. Аргументы функции означают: ставка - процентная ставка за период;

период - задает период, значение должно быть в интервале от 1 до "кпер";

кпер - общее число периодов выплат годовой ренты;

пс - приведенная стоимость, то есть общая сумма, которая равноценна ряду будущих платежей;

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

Пример 2.29. Определите сумму основного платежа с двухлетней займа 2000 грн. за первый месяц из расчета 10% годовых. Начисление процентов ежемесячно.

Решение: Основной платеж по займу за первый месяц:

ОСПЛТ (10% / 12, 1, 2 * 12; 2000) = - 75,62 грн.

Накопленный доход по займу (сумму платежей по процентам), которая погашается равными платежами в конце или в начале каждого расчетного периода, между двумя периодами выплат рассчитывает в Excel функция ОБЩПЛАТ.

Синтаксис ОБЩПЛАТ (ставка; кпер; пс;

начпериод; конпериод; тип). (2.15)

Аргументы функции означают: ставка - процентная ставка; кпер пс нач_период -

кон_период -

тип - это выбор времени платежа.

Пример 2.30. Изданная заем под покупку недвижимости размером 125 тыс. Грн. сроком на 30 лет под 9% годовых, проценты начисляются ежемесячно. Определить величину процентных платежей а) за второй год, б) за первый месяц.

Решение: Кумулятивная выплата по процентам за второй год (с 13-го периода по 24-й) составит:

ОБЩПЛАТ (9% / 12; 30 * 12; 125 000; 13; 24; 0) = - 11135,23 грн. Одна выплата за первый месяц составит:

ОБЩПЛАТ (9% / 12; 30 * 12; 125 000, 1, 1, 0) = - 937,50 грн. Это же значение будет получено при расчете по формуле:

ПРПЛТ (9% / 12, 1, 30 * 12; 125 000) = - 937,50 грн. В Excel функция ОБЩДОХОД рассчитывает кумулятивную (нарастающим итогом) сумму, выплачиваемую в погашение основной суммы займа в промежутке между двумя периодами:

Синтаксис ОБЩДОХОД (ставка; кпер;

пс; начпериод; конпериод; тип). (2.16)

Аргументы функции означают:

ставка - процентная ставка;

кпер - это общее количество периодов выплат;

пс - это стоимость инвестиции на текущий момент;

нач_период - это номер первого периода, включенного в вычисления. Периоды выплат нумеруются, начиная с 1;

кон_период - это номер последнего периода, включенного в вычисления;

тип - это выбор времени платежа.

Пример 2.31. Изданная заем размером 125 тыс. Грн. сроком на 30 лет под 9% годовых, проценты начисляются ежемесячно. Определить величину основных выплат: а) за первый месяц; б) второй год (платежи с 13-го периода по 24-й).

решение:

а) ОБЩДОХОД (9% / 12; 30 * 12; 125000, 1, 1, 0) = - 68,27827118 грн.;

б) если заем погашается равными платежами в конце каждого расчетного периода, то размер выплаты задолженности за второй год составит:

ОБЩДОХОД (9% / 12; 30 * 12; 125000; 13; 24; 0) = - +934,1071234 грн. Периоды с 13-го по 24-й составляют второй год.

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

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

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

Синтаксис

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

Аргументы

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

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

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

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

Необязательный. Значение будущей стоимости, т. е. желаемого остатка средств после последнего платежа. Если аргумент «бс» опущен, предполагается, что он равен 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)

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

Лабораторная работа №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дставитель банка.

http://ru.excelfunctions.eu/%D0%91%D0%A1/A%D0%BD%D0%B3%D0%BB%D0%B8%D0%B9%D1%81%D0%BA%D0%BE%D0%BC

http://office.microsoft.com/ru-ru/excel-help/HP010069823.aspx

Функция бс

Применимо к: Microsoft Office Excel 2007

Печать

Показать все

Возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки.

Синтаксис

БС (ставка ;кпер ;плт ;пс;тип)

Дополнительные сведения об аргументах функции БС, также о других финансовых функциях см. в описании функции ПС.

Ставка - процентная ставка за период.

Кпер - общее число периодов платежей по аннуитету.

Плт - выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент «плт» состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент «пс» является обязательным.

Пс - приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент «пс» опущен, предполагается значение 0. В этом случае аргумент «плт» является обязательным.

Тип - число 0 или 1, обозначающее срок выплаты. Если аргумент «тип» опущен, предполагается значение 0.

Замечания

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

    Все аргументы, которым соответствуют выплачиваемые денежные средства (например, сберегательные вклады), представляются в виде отрицательных чисел, а получаемые (например, дивиденды) - в виде положительных.

Пример 1

Инструкции

Данные

Описание

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

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

Объем платежей

Стоимость на текущий момент

Платежи осуществляются в начале периода (см. выше)

Формула

Описание (результат)

БС(A2/12; A3; A4; A5; A6)

Будущая стоимость инвестиции в соответствии с приведенными выше условиями (2 581,40)

ПРИМЕЧАНИЕ. Годовая процентная ставка делится на 12, поскольку начисление сложных процентов производится ежемесячно.

Функция плт

Применимо к: Microsoft Office Excel 2007

Печать

Показать все

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

Синтаксис

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

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

Ставка - процентная ставка по ссуде.

Кпер - общее число выплат по ссуде.

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

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

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

Замечания

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

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

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

Пример 1

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Копирование примера

Данные

Описание

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

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

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

Формула

Описание (результат)

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

Месячная сумма платежа по указанному кредиту (-1 037,03)

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

Месячная сумма платежа по указанному кредиту, исключая платежи, производимые в начале периода (-1 030,16)