Работа с функцией плт в excel. Финансовые расчеты в Excel

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

Количественный анализ финансовых данных в Excel при проведении таких операций сводится к исчислению следующих основных характеристик:

· текущей величины (present value – PV) потока платежей;

· будущей величины (future value – FV) потока платежей;

· величины отдельного платежа (payment – P) ;

· нормы доходности (цены) в виде процентной ставки (interest rate – r) ;

· числа периодовпроведения финансовой операции (например, лет, месяцев, и т.д.).

Используемые при этом методы базируются на технике исчисления сложных процентов.

Табличный процессор Excel предоставляет широкие возможности по моделированию подобных расчетов при помощи соответствующих встроенных в Excel финансовых функций: БC(), КПЕР(), СТАВКА(), ПЛТ(), ПС().

Все функции данной группы имеют одинаковый набор базовых аргументов:

ü процентная ставка (норма доходности или цена заемных средств);

ü срок (число периодов) проведения операции;

ü величина периодического платежа;

ü начальная сумма;

ü будущая стоимость (величина) денежных средств;

ü тип начисления процентов (1- начало периода, 0 – конец периода).

Рассмотрим применение вышеперечисленных функций в проведении финансовых расчетов и анализе обыкновенных аннуитетов на конкретном примере.

Предположим, Ваша фирма решила создать специальный фонд для погашения своих долгосрочных обязательств (кредитов, займов), срок погашения которых наступит, например, через 5 лет, путем периодического (ежегодного) пополнения депозита в банке. Начальная сумма депозита составляет 10000 тыс. руб. Размер ежегодных платежей – 1000 тыс. руб. Процентная ставка по банковскому депозиту – 15%.

Необходимо определить величину фонда к концу 5-го года.

Для решения поставленной задачи выполните следующие действия:

1) Введите исходные данные на рабочий лист. Диапазон таблицы С3:С6 содержит входные данные для расчета величины денежного фонда, т.е. будущей стоимости вложений (инвестиций).

2) Установите курсор в ячейку рабочего листа, где будет рассчитана будущая стоимость вложений (депозита), в данном случае в ячейку C7.

3) Выполните команду Формулы / Библиотека функций / Вставить функцию или нажмите кнопку Строки формул . В категорииФинансовые из списка выберите функцию БC() . Нажмите ОК.

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

Функция БС() – позволяет определить будущую величину вклада (Future Value – FV) на основе периодических постоянных платежей при заданных величинах процентной ставки, числа периодов выплат и начальной суммы вклада.

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

=БС (норма; число периодов; выплата; нз; тип),

где: норма – процентная ставка (норма доходности по депозиту);

число периодов – срок (число периодов) проведения операции;

выплата – величина периодического платежа;

нз – начальная стоимость вложений (депозита);

тип – тип начисления процентов, является необязательным аргументом.

(0 – в конце периода; 1 – в начале периода). По умолчанию начисление процентов осуществляется в конце периода.

Следует обратить внимание на особенности задания аргументов:

Если процентная ставка задается как абсолютная величина, она должна иметь вид десятичной дроби, например как в примере: 15% – 0,15. Периодический платеж и начальная сумма задаются со знаком минус, т.к. в данной операции для фирмы они означают выплаты (расходование) денежных средств. Такие правила применимы для всех финансовых функций. В зависимости от условия поставленной задачи, значения периодического платежа и начальной стоимости могут быть введены в функцию как в виде положительных, так и отрицательных величин. Это зависит от того, какой экономический субъект проводит подобные расчеты. Кроме того, можно на этапе ввода аргументов определить конечный результат, возвращаемый функцией, который отображается внизу диалогового окна ввода аргументов.

MS Excel при расчетах характеристик денежных аннуитетов выражает каждый показатель исходя из следующего соотношения:

где: НC – начальная (текущая) стоимость вклада;

БС i – будущая стоимость вклада через число периодов i ;

норма – процентная ставка (норма доходности);

выплата – периодический платеж;

i – порядковый номер периода поведения финансовой операции;

тип – тип начисления процентов.

Таким образом, будущая стоимость вложений определяется по формуле:

Для нашего предприятия будущее значение банковского депозита в конце 5-го года будет следующим:

=БС (0,15;5;-1000;-10000) (Возвращаемый результат: 26855,95 тыс. руб.) .

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

=БС (0,15;5;1000;10000) (Возвращаемый результат: -26855,95 тыс. руб.).

Функция КПЕР() – позволяет определить число выплат (поступлений) денежных средств, если известны процентная ставка, периодический платеж, начальная и будущая величины потоков платежей.

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

=КПЕР (0,15;1000;10000;26855,95)(Возвращаемый результат: 5),

где: 0,15 – процентная ставка по депозиту; 1000 – периодический платеж; 10000 – первоначальная сумма депозита; 26855,95 – будущая величина депозита.

Функция СТАВКА() – вычисляет процентную ставку, которая в зависимости от условий операции может выступать либо в качестве цены, либо в качестве нормы рентабельности данной операции. Имеет аргументы:

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

где: кпер – обязательный. Общее число периодов платежей для ежегодного платежа;

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

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

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

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

прогноз – необязательный. Предполагаемая величина ставки. Если аргумент "прогноз" опущен, предполагается, что его значение равно 10 %.

Пусть в нашем примере будет неизвестна процентная ставка по банковскому депозиту. Тогда для ее расчета воспользуемся функцией СТАВКА() :

=СТАВКА (5;-1000;-10000;26855,95)(Возвращаемый результат: 15%).

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

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

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

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

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

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

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

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

Формула расчета периодического платежа, исходя из вышеприведенного соотношения, будет иметь следующий вид:

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

=ПЛТ (0,15;5;-10000;26855,95)(Возвращаемый результат: -1000).

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

Функция ПС() – позволяет определить текущую (т.е. на момент начала операции – present value ) стоимость аннуитета , если известны 4 обязательных параметра (процентная ставка; число периодов; начальная стоимость; будущая стоимость денежных средств). Имеет следующие аргументы:

ПС(ставка, клер, плт, [бс], [тип])

где: ставка - обязательный. Процентная ставка за период. Например, если получен кредит на автомобиль под 10 процентов годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 10%/12 (0,83%). В качестве значения аргумента "ставка" нужно ввести в формулу 10%/12, 0,83% или 0,0083.

кпер – обязательный. Общее число периодов платежей для ежегодного платежа. Например, если получен кредит на 4 года на покупку автомобиля и платежи производятся ежемесячно, то кредит имеет 4*12 (или 48) периодов. В качестве значения аргумента "кпер" в формулу нужно ввести число 48.

плт – обязательный. Выплата, производимая в каждый период и не меняющаяся на протяжении всего периода ежегодного платежа. Обычно аргумент "плт" состоит из выплат в счет основной суммы и платежей по процентам, но не включает в себя другие сборы или налоги. Например, ежемесячная выплата по кредиту в размере 10 000 р. под 12 процентов годовых на 4 года составит 263,33р. В качестве значения аргумента "плт" нужно ввести в формулу число -263,33.

бс – необязательный. Значение будущей стоимости, т.е. желаемого остатка средств после последнего платежа. Если аргумент "бс" опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0). Предположим, что для определенной цели требуется накопить 50 000 р. за 18 лет: в этом случае будущая стоимость равна 50 000 р. Предположив, что заданная процентная ставка останется без изменений, можно определить, какую сумму необходимо откладывать каждый месяц. Если аргумент "бс" опущен, необходимо использовать аргумент "плт".

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

Для условия нашей задачи применение данной функции позволяет получить ответ на вопрос: «Какую сумму необходимо вложить в банк на депозит, чтобы получить через 5 лет величину вклада 26855,95 тыс. руб. при ежегодном пополнении вклада на 1000 тыс. руб., если годовая банковская ставка составляет 15%?».

Формула для определения текущей (настоящей) стоимости вклада (ПС) :

Для нашего примера синтаксис функции будет следующим:

=ПС (0,15;5;-1000;26855,95)(Возвращаемый результат: -10000).

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

Допустим, при ежеквартальном пополнении вклада и начисления процентов, функция ПС() примет следующий вид:

=ПС (0,15/4;5*4;-1000;26855,95)(Возвращаемый результат: 1035,09).

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 лет.

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

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

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

Синтаксис =ПЛТ(ставка; кпер; пс; [бс]; [тип]) Аргументы

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

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

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

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

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

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

Замечания

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

Примеры

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

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

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

Функция ПЛТ() , английский вариант 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 млн. руб.? (см. файле примера ).

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

Функция ПЛТ

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

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

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

Функция СТАВКА

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

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

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

Функция ЭФФЕКТ

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