Существуют сотни онлайновых финансовых планировщиков. Все они просты в использовании, но ограничены по функциональности. MS Excel на их фоне - настоящий комбайн. В нём есть 53 финансовые формулы на все случаи жизни, а для контроля и планирования бюджета полезно знать три из них.
Одна из актуальнейших функций, с помощью которой можно рассчитать сумму платежа по кредиту с аннуитетными платежами, то есть когда кредит выплачивается равными частями. Полное описание функции .
ПЛТ(ставка;кпер;пс;бс;тип)
Вычисляет процентную ставку по займу или инвестиции, базируясь на величине будущей стоимости. Полное описание функции .
СТАВКА(кпер;плт;пс;бс;тип;прогноз)
Возвращает эффективную (фактическую) годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году, за которые начисляются сложные проценты. Полное описание функции
Функция ПЛТ в Excel входит в категорию «Финансовых». Она возвращает размер периодического платежа для аннуитета с учетом постоянства сумм платежей и процентной ставки. Рассмотрим подробнее.
Синтаксис функции: ставка; кпер; пс; [бс]; [тип].
Расшифровка аргументов:
Особенности функционирования ПЛТ:
Для корректной работы функции необходимо правильно внести исходные данные:
Размер займа указывается со знаком «минус», т.к. эти деньги кредитная организация «дает», «теряет». Для записи значения процентной ставки необходимо использовать процентный формат. Если записывать в числовом, то применяется десятичное число (0,08).
Нажимаем кнопку fx («Вставить функцию»). Откроется окно «Мастер функций». В категории «Финансовые» выбираем функцию ПЛТ. Заполняем аргументы:
Когда курсор стоит в поле того или иного аргумента, внизу показывается «подсказка»: что необходимо вводить. Так как исходные данные введены в таблицу Excel, в качестве аргументов мы использовали ссылки на ячейки с соответствующими значениями. Но можно вводить и числовые значения.
Обратите внимание! В поле «Ставка» значение годовых процентов поделено на 12: платежи по кредиту выполняются ежемесячно.
Ежемесячные выплаты по займу в соответствии с указанными в качестве аргументов условиями составляют 1 037,03 руб.
Чтобы найти общую сумму, которую нужно выплатить за весь период (основной долг плюс проценты), умножим ежемесячный платеж по займу на значение «Кпер»:
Исключим из расчета ежемесячных выплат по займу платеж, произведенный в начале периода:
Для этого в качестве аргумента «Тип» нужно указать значение 1.
Детализируем расчет, используя функции ОСПЛТ и ПРПЛТ. С помощью первой покажем тело кредита, посредством второй – проценты.
Для подробного расчета составим таблицу:
Рассчитаем тело кредита с помощью функции ОСПЛТ. Аргументы заполняются по аналогии с функцией ПЛТ:
В поле «Период» указываем номер периода, для которого рассчитывается основной долг.
Заполняем аргументы функции ПРПЛТ аналогично:
Дублируем формулы вниз до последнего периода. Для расчета общей выплаты суммируем тело кредита и проценты.
Рассчитываем остаток по основному долгу. Получаем таблицу следующего вида:
Общая выплата по займу совпадает с ежемесячным платежом, рассчитанным с помощью функции ПЛТ. Это постоянная величина, т.к. пользователь оформил аннуитетный кредит.
Таким образом, функция ПЛТ может применяться для расчета ежемесячных выплат по вкладу или платежей по кредиту при условии постоянства процентной ставки и сумм.
Excel имеет значительную популярность среди бухгалтеров, экономистов и финансистов не в последнюю очередь благодаря обширному инструментарию по выполнению различных финансовых расчетов. Главным образом выполнение задач данной направленности возложено на группу финансовых функций. Многие из них могут пригодиться не только специалистам, но и работникам смежных отраслей, а также обычным пользователям в их бытовых нуждах. Рассмотрим подробнее данные возможности приложения, а также обратим особое внимание на самые популярные операторы данной группы.
В группу данных операторов входит более 50 формул. Мы отдельно остановимся на десяти самых востребованных из них. Но прежде давайте рассмотрим, как открыть перечень финансового инструментария для перехода к выполнению решения конкретной задачи.
Переход к данному набору инструментов легче всего совершить через Мастер функций.
В Мастер функций также можно перейти через вкладку «Формулы» . Сделав переход в неё, нужно нажать на кнопку на ленте «Вставить функцию» , размещенную в блоке инструментов «Библиотека функций» . Сразу вслед за этим запустится Мастер функций.
Имеется в наличии также способ перехода к нужному финансовому оператору без запуска начального окна Мастера. Для этих целей в той же вкладке «Формулы» в группе настроек «Библиотека функций» на ленте кликаем по кнопке «Финансовые» . После этого откроется выпадающий список всех доступных инструментов данного блока. Выбираем нужный элемент и кликаем по нему. Сразу после этого откроется окно его аргументов.
Одним из наиболее востребованных операторов у финансистов является функция ДОХОД . Она позволяет рассчитать доходность ценных бумаг по дате соглашения, дате вступления в силу (погашения), цене за 100 рублей выкупной стоимости, годовой процентной ставке, сумме погашения за 100 рублей выкупной стоимости и количеству выплат (частота). Именно эти параметры являются аргументами данной формулы. Кроме того, имеется необязательный аргумент «Базис» . Все эти данные могут быть введены с клавиатуры прямо в соответствующие поля окна или храниться в ячейках листах Excel. В последнем случае вместо чисел и дат нужно вводить ссылки на эти ячейки. Также функцию можно ввести в строку формул или область на листе вручную без вызова окна аргументов. При этом нужно придерживаться следующего синтаксиса:
ДОХОД(Дата_сог;Дата_вступ_в_силу;Ставка;Цена;Погашение»Частота;[Базис])
Главной задачей функции БС является определение будущей стоимости инвестиций. Её аргументами является процентная ставка за период («Ставка» ), общее количество периодов («Кол_пер» ) и постоянная выплата за каждый период («Плт» ). К необязательным аргументам относится приведенная стоимость («Пс» ) и установка срока выплаты в начале или в конце периода («Тип» ). Оператор имеет следующий синтаксис:
БС(Ставка;Кол_пер;Плт;[Пс];[Тип])
Оператор ВСД вычисляет внутреннюю ставку доходности для потоков денежных средств. Единственный обязательный аргумент этой функции – это величины денежных потоков, которые на листе Excel можно представить диапазоном данных в ячейках («Значения» ). Причем в первой ячейке диапазона должна быть указана сумма вложения со знаком «-», а в остальных суммы поступлений. Кроме того, есть необязательный аргумент «Предположение» . В нем указывается предполагаемая сумма доходности. Если его не указывать, то по умолчанию данная величина принимается за 10%. Синтаксис формулы следующий:
ВСД(Значения;[Предположения])
Оператор МВСД выполняет расчет модифицированной внутренней ставки доходности, учитывая процент от реинвестирования средств. В данной функции кроме диапазона денежных потоков («Значения» ) аргументами выступают ставка финансирования и ставка реинвестирования. Соответственно, синтаксис имеет такой вид:
МВСД(Значения;Ставка_финансир;Ставка_реинвестир)
Оператор ПРПЛТ рассчитывает сумму процентных платежей за указанный период. Аргументами функции выступает процентная ставка за период («Ставка» ); номер периода («Период» ), величина которого не может превышать общее число периодов; количество периодов («Кол_пер» ); приведенная стоимость («Пс» ). Кроме того, есть необязательный аргумент – будущая стоимость («Бс» ). Данную формулу можно применять только в том случае, если платежи в каждом периоде осуществляются равными частями. Синтаксис её имеет следующую форму:
ПРПЛТ(Ставка;Период;Кол_пер;Пс;[Бс])
Оператор ПЛТ рассчитывает сумму периодического платежа с постоянным процентом. В отличие от предыдущей функции, у этой нет аргумента «Период» . Зато добавлен необязательный аргумент «Тип» , в котором указывается в начале или в конце периода должна производиться выплата. Остальные параметры полностью совпадают с предыдущей формулой. Синтаксис выглядит следующим образом:
ПЛТ(Ставка;Кол_пер;Пс;[Бс];[Тип])
Формула ПС применяется для расчета приведенной стоимости инвестиции. Данная функция обратная оператору ПЛТ . У неё точно такие же аргументы, но только вместо аргумента приведенной стоимости («ПС» ), которая собственно и рассчитывается, указывается сумма периодического платежа («Плт» ). Синтаксис соответственно такой:
ПС(Ставка;Кол_пер;Плт;[Бс];[Тип])
Следующий оператор применяется для вычисления чистой приведенной или дисконтированной стоимости. У данной функции два аргумента: ставка дисконтирования и значение выплат или поступлений. Правда, второй из них может иметь до 254 вариантов, представляющих денежные потоки. Синтаксис этой формулы такой:
ЧПС(Ставка;Значение1;Значение2;…)
Функция СТАВКА рассчитывает ставку процентов по аннуитету. Аргументами этого оператора является количество периодов («Кол_пер» ), величина регулярной выплаты («Плт» ) и сумма платежа («Пс» ). Кроме того, есть дополнительные необязательные аргументы: будущая стоимость («Бс» ) и указание в начале или в конце периода будет производиться платеж («Тип» ). Синтаксис принимает такой вид:
СТАВКА(Кол_пер;Плт;Пс[Бс];[Тип])
Оператор ЭФФЕКТ ведет расчет фактической (или эффективной) процентной ставки. У этой функции всего два аргумента: количество периодов в году, для которых применяется начисление процентов, а также номинальная ставка. Синтаксис её выглядит так:
ЭФФЕКТ(Ном_ставка;Кол_пер)
Нами были рассмотрены только самые востребованные финансовые функции. В общем, количество операторов из данной группы в несколько раз больше. Но и на данных примерах хорошо видна эффективность и простота применения этих инструментов, значительно облегчающих расчеты для пользователей.
Функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.
Возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.
ставка кпер пс бс тип
Обязательный. Процентная ставка по ссуде.
Обязательный. Общее число выплат по ссуде.
Обязательный. Приведенная (текущая) стоимость, т. е. общая сумма, которая на данный момент равноценна ряду будущих платежей, называется также «основной суммой».
Необязательный. Значение будущей стоимости, т. е. желаемого остатка средств после последнего платежа. Если аргумент «бс» опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0).
Необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата.
Пример1 Пример задачи1
Задача
Клиент банка осуществляет заем в размере 5000 руб. под 6% годовых на 6 месяцев. Определить ежемесячные платежи клиента. Платежи осуществляются в конце месяца.
Решение
Введем первоначальные данные.
В финансовой практике часто встречаются операции, характеризующиеся возникновением потоков платежей (денежных поступлений и выплат), распределенных во времени. Потоки платежей, при которых выплаты (поступления) денежных средств осуществляются равными суммами через одинаковые интервалы времени,принято называть обыкновенным или регулярным аннуитетом. Как правило, подобные потоки платежей возникают при проведении кредитно-депозитных операций, формировании различных фондов, долгосрочной аренде и т.п.
Количественный анализ финансовых данных в 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).
