Существуют сотни онлайновых финансовых планировщиков. Все они просты в использовании, но ограничены по функциональности. MS Excel на их фоне - настоящий комбайн. В нём есть 53 финансовые формулы на все случаи жизни, а для контроля и планирования бюджета полезно знать три из них.
Одна из актуальнейших функций, с помощью которой можно рассчитать сумму платежа по кредиту с аннуитетными платежами, то есть когда кредит выплачивается равными частями. Полное описание функции .
ПЛТ(ставка;кпер;пс;бс;тип)
- Ставка - процентная ставка по ссуде.
- Кпер - общее число выплат по ссуде.
- Пс - приведённая к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.
- Бс - требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент «бс» опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение «бс» равно 0.
Вычисляет процентную ставку по займу или инвестиции, базируясь на величине будущей стоимости. Полное описание функции .
СТАВКА(кпер;плт;пс;бс;тип;прогноз)
- Кпер - общее число периодов платежей для ежегодного платежа.
- Плт - выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент «плт» состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент «пс» является обязательным.
- Пс - приведённая (текущая) стоимость, т. е. общая сумма, которая на данный момент равноценна ряду будущих платежей.
- Бс (необязательный аргумент) - значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если аргумент «бс» опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0).
- Тип (необязательный аргумент) - число 0 (нуль), если платить нужно в конце периода, или 1, если платить нужно в начале периода.
- Прогноз (необязательный аргумент) - предполагаемая величина ставки. Если аргумент «прогноз» опущен, предполагается, что его значение равно 10%. Если функция СТАВКА не сходится, попробуйте изменить значение аргумента «прогноз». Функция СТАВКА обычно сходится, если значение этого аргумента находится между 0 и 1.
Возвращает эффективную (фактическую) годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году, за которые начисляются сложные проценты. Полное описание функции
Excel имеет значительную популярность среди бухгалтеров, экономистов и финансистов не в последнюю очередь благодаря обширному инструментарию по выполнению различных финансовых расчетов. Главным образом выполнение задач данной направленности возложено на группу финансовых функций. Многие из них могут пригодиться не только специалистам, но и работникам смежных отраслей, а также обычным пользователям в их бытовых нуждах. Рассмотрим подробнее данные возможности приложения, а также обратим особое внимание на самые популярные операторы данной группы.
В группу данных операторов входит более 50 формул. Мы отдельно остановимся на десяти самых востребованных из них. Но прежде давайте рассмотрим, как открыть перечень финансового инструментария для перехода к выполнению решения конкретной задачи.
Переход к данному набору инструментов легче всего совершить через Мастер функций.
В Мастер функций также можно перейти через вкладку «Формулы» . Сделав переход в неё, нужно нажать на кнопку на ленте «Вставить функцию» , размещенную в блоке инструментов «Библиотека функций» . Сразу вслед за этим запустится Мастер функций.
Имеется в наличии также способ перехода к нужному финансовому оператору без запуска начального окна Мастера. Для этих целей в той же вкладке «Формулы» в группе настроек «Библиотека функций» на ленте кликаем по кнопке «Финансовые» . После этого откроется выпадающий список всех доступных инструментов данного блока. Выбираем нужный элемент и кликаем по нему. Сразу после этого откроется окно его аргументов.
Одним из наиболее востребованных операторов у финансистов является функция ДОХОД . Она позволяет рассчитать доходность ценных бумаг по дате соглашения, дате вступления в силу (погашения), цене за 100 рублей выкупной стоимости, годовой процентной ставке, сумме погашения за 100 рублей выкупной стоимости и количеству выплат (частота). Именно эти параметры являются аргументами данной формулы. Кроме того, имеется необязательный аргумент «Базис» . Все эти данные могут быть введены с клавиатуры прямо в соответствующие поля окна или храниться в ячейках листах Excel. В последнем случае вместо чисел и дат нужно вводить ссылки на эти ячейки. Также функцию можно ввести в строку формул или область на листе вручную без вызова окна аргументов. При этом нужно придерживаться следующего синтаксиса:
ДОХОД(Дата_сог;Дата_вступ_в_силу;Ставка;Цена;Погашение»Частота;[Базис])
Главной задачей функции БС является определение будущей стоимости инвестиций. Её аргументами является процентная ставка за период («Ставка» ), общее количество периодов («Кол_пер» ) и постоянная выплата за каждый период («Плт» ). К необязательным аргументам относится приведенная стоимость («Пс» ) и установка срока выплаты в начале или в конце периода («Тип» ). Оператор имеет следующий синтаксис:
БС(Ставка;Кол_пер;Плт;[Пс];[Тип])
Оператор ВСД вычисляет внутреннюю ставку доходности для потоков денежных средств. Единственный обязательный аргумент этой функции – это величины денежных потоков, которые на листе Excel можно представить диапазоном данных в ячейках («Значения» ). Причем в первой ячейке диапазона должна быть указана сумма вложения со знаком «-», а в остальных суммы поступлений. Кроме того, есть необязательный аргумент «Предположение» . В нем указывается предполагаемая сумма доходности. Если его не указывать, то по умолчанию данная величина принимается за 10%. Синтаксис формулы следующий:
ВСД(Значения;[Предположения])
Оператор МВСД выполняет расчет модифицированной внутренней ставки доходности, учитывая процент от реинвестирования средств. В данной функции кроме диапазона денежных потоков («Значения» ) аргументами выступают ставка финансирования и ставка реинвестирования. Соответственно, синтаксис имеет такой вид:
МВСД(Значения;Ставка_финансир;Ставка_реинвестир)
Оператор ПРПЛТ рассчитывает сумму процентных платежей за указанный период. Аргументами функции выступает процентная ставка за период («Ставка» ); номер периода («Период» ), величина которого не может превышать общее число периодов; количество периодов («Кол_пер» ); приведенная стоимость («Пс» ). Кроме того, есть необязательный аргумент – будущая стоимость («Бс» ). Данную формулу можно применять только в том случае, если платежи в каждом периоде осуществляются равными частями. Синтаксис её имеет следующую форму:
ПРПЛТ(Ставка;Период;Кол_пер;Пс;[Бс])
Оператор ПЛТ рассчитывает сумму периодического платежа с постоянным процентом. В отличие от предыдущей функции, у этой нет аргумента «Период» . Зато добавлен необязательный аргумент «Тип» , в котором указывается в начале или в конце периода должна производиться выплата. Остальные параметры полностью совпадают с предыдущей формулой. Синтаксис выглядит следующим образом:
ПЛТ(Ставка;Кол_пер;Пс;[Бс];[Тип])
Формула ПС применяется для расчета приведенной стоимости инвестиции. Данная функция обратная оператору ПЛТ . У неё точно такие же аргументы, но только вместо аргумента приведенной стоимости («ПС» ), которая собственно и рассчитывается, указывается сумма периодического платежа («Плт» ). Синтаксис соответственно такой:
ПС(Ставка;Кол_пер;Плт;[Бс];[Тип])
Следующий оператор применяется для вычисления чистой приведенной или дисконтированной стоимости. У данной функции два аргумента: ставка дисконтирования и значение выплат или поступлений. Правда, второй из них может иметь до 254 вариантов, представляющих денежные потоки. Синтаксис этой формулы такой:
ЧПС(Ставка;Значение1;Значение2;…)
Функция СТАВКА рассчитывает ставку процентов по аннуитету. Аргументами этого оператора является количество периодов («Кол_пер» ), величина регулярной выплаты («Плт» ) и сумма платежа («Пс» ). Кроме того, есть дополнительные необязательные аргументы: будущая стоимость («Бс» ) и указание в начале или в конце периода будет производиться платеж («Тип» ). Синтаксис принимает такой вид:
СТАВКА(Кол_пер;Плт;Пс[Бс];[Тип])
Оператор ЭФФЕКТ ведет расчет фактической (или эффективной) процентной ставки. У этой функции всего два аргумента: количество периодов в году, для которых применяется начисление процентов, а также номинальная ставка. Синтаксис её выглядит так:
ЭФФЕКТ(Ном_ставка;Кол_пер)
Нами были рассмотрены только самые востребованные финансовые функции. В общем, количество операторов из данной группы в несколько раз больше. Но и на данных примерах хорошо видна эффективность и простота применения этих инструментов, значительно облегчающих расчеты для пользователей.
Существуют сотни онлайновых финансовых планировщиков. Все они просты в использовании, но ограничены по функциональности. 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 лет. |
Лабораторная работа №2
Работа с финансовыми функциями.
Анализ «Что-если»
Цель работы : научиться работать с финансовыми функциями Excel
и выполнять анализ "Что-если"
1 Финансовые функции при экономических расчётах
2 Прогнозирование с помощью анализа "Что-если"
Финансовые функции при экономических расчётах
Функция ПЛТ. Расчёт величины ежемесячной выплаты кредита
Функция ПЛТ определяет сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.
Пример 1 Определить ежемесячный платёж, если банк предоставляет кредит в 140000р. с рассрочкой в 5 лет под 8,5% годовых с ежемесячной выплатой. Последний платёж должен составить 10000р.
Введём данные в таблицу Excel согласно рис. 1)
1 Выделить ячейку В6 и щелкнуть по кнопке Вставка функции (знак f x слева от строки формул). Появится окно Мастера функций, выбрать категорию Финансовые.
2 Щелкнуть мышью по функции ПЛТ, перетащить окно ПЛТ на свободное место экрана, чтобы освободить таблицу и
Рисунок 1 Расчёт аннуитета заполнить его поля:
▪ Поле Ставка – это процент в месяц,
вводим 0,085,
▪ Кпер – количество периодов выплат, т.е. 5лет*12мес, вводим 5*12
▪ Нз – общая сумма всех платежей с текущего момента, вводим 140000,
▪ Бс – будущая стоимость, вводится 130000 со знаком "-", т.к. платим мы, а не банк,
§ Тип – выплата в конце месяца, поэтому вводим 0 или ничего.
3 Нажать ОК .
Результат : около 2738 р. ежемесячно нужно выплачивать, чтобы погасить 130000 р. за 5 лет (в конце срока последним платежом ещё 10000р.)
2 Прогнозирование с помощью анализа "Что-если"
Анализ «Что-если» позволяет прогнозировать значение какой-либо функции (математической, финансовой, статистической и др.) при изменении её аргументов. Существует три способа прогнозирования значений: с помощью таблиц подстановки данных, с помощью сценариев и с помощью подбора параметров и поиска решения.
1 способ. Таблица подстановки данных – это диапазон ячеек, показывающий, как изменение значений подстановки влияет на возвращаемый формулой результат. Если в какой-либо ячейке записана формула, содержащая элементы из других ячеек, то при изменении значения в какой-нибудь или нескольких ячейках изменится результат в ячейке, содержащей формулу.
Пример 2 Компания сделала заём на 80 000 руб. сроком на 3 года. Определить:
Ежемесячные выплаты при процентных ставках 7%, 8% и 9% годовых,
Ежемесячные выплаты при процентной ставке 5%, сроке заема 5 лет и сумме заема 100 000р.
1 Введем таблицу подстановок в виде (рис. 2):
Рисунок 2 Таблица подстановок
2 Введём в ячейку D2 формулу платежа ПЛТ (В3/12;В4*12;В5) вручную или через окно ПЛТ из Мастера функций (см. пример 1), в D2 появится рассчитанное значение функции -2470,17р.
3 Изменим значение ячейки В3 на 8%, получим в D2 cумму платежа –2506,91р.
4 Изменим значение ячейки В3 на 9%, получим в D2 cумму платежа –2543,98р.
5 Изменим одновременно значения ячеек: В3на 5%, В4на 5 и В5 на 100000, получим в D2 cумму платежа –1887,12р.
Таблица подстановок должна обязательно в одной из ячеек содержать формулу.
2 способ. Сценарий – это набор значений подстановки, используемый для прогнозирования поведения модели. На одном листе Excel можно создать и сохранить несколько различных сценариев и переключаться на любой из них для просмотра результатов и выбора наилучшего.
Пример 3 Оформим в виде сценариев варианты подстановки данных из пунктов 2 и 3 примера 2.
Для создания сценария необходимо выполнить следующие действия:
1 Из меню Сервис выберете команду Сценарии.
2 В открывшемся окне Диспетчер сценариев нажмите кнопку Добавить.
3 Введите имя сценария., например "Ставка 7%"".
4 В поле Изменяемые ячейки задайте те ячейки (через двоеточие), которые Вы собираетесь изменить, в данном случае – ячейку В3.
5 Нажмите кнопку ОК.
6 В открывшемся диалоговом окне Значения сценария для каждой изменяемой ячейки введите новое значение или формулу, в данном случае вводим в В3число 0,07. Нажмите кнопку ОК . Исходную модель " что-если " желательно сохранить в виде сценария, присвоив ему, например, имя «Стартовые значения». В противном случае при задании новых изменяемых ячеек исходные данные будут потеряны.
Для просмотра сценария необходимо воспользоваться кнопкой Вывести в окне Диспетчер сценариев. Щелкнув кнопку Итоги в диалоговом окне Диспетчер сценариев, можно получить итоговый отчет на отдельном рабочем листе с названием "Структура сценариев", показывающий влияние разных сценариев на одну или несколько результирующих ячеек. Знаки "+"("-") слева и сверху позволяют разворачивать (сворачивать) отдельные разделы отчёта. Серым выделены изменяемые поля.
3 способ. Подбор параметра. При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки не возвратит заданное значение.
Пример 4 Условие примера 1. Компания может ежемесячно выплачивать не более 2500р. Определить, каким должен для этого быть последний платёж.
1.Выделим ячейку.В6:
2.В меню Сервис выбрать команду Подбор параметра.
В окне Подбор параметра:
В поле Установить в ячейке – введено В6,
В поле Значение - ввести -2500
В поле Изменяя значение ячейки – ввести В3 (ячейка последнего платежа),
Нажать ОК.
Результат: последний платёж = -27716 р.
При подборе параметра одна из ячеек также обязательно должна содержать формулу , поскольку таблица является таблицей подстановок.
Команда Поиск решения из меню Сервис используется для подбора одновременно нескольких параметров с целью максимизации или минимизации содержимого целевой ячейки и подробно рассматривается в лабораторной работе №7 (excel-7).
Контрольные вопросы
1 Как вывести на экран приложение Мастер функций?
2 Какую операцию выполняет функция ПЛТ, что вводится в её поля Норма, Кпер, Нз, Бс, Тип?
3 Назначение и способы анализа «Что если»?
4 Что такое «Таблица подстановок», каков состав её ячеек?
5 Что такое сценарий, как его создать, просмотреть, получить итоговый отчет на отдельном листе?
6 Сущность операции Подбор параметра, как она выполняется?
Задания
1 Выполнить задание примера 1, изменив сумму кредита на 140000·n , где n - номер студента в журнале преподавателя. Выполнить то же для новой суммы кредита, изменив годовой процент с 8,5% на 5%, а срок кредита с 5 на 10 лет.
2 Выполнить анализ "Что-если" по заданию таблицы подстановки примера 2, изменив сумму заёма на 80000·n, где n- номер студента в журнале преподавателя.
3 Оформить в виде сценариев все операции из п.1 (два сценария) и п.2 (четыре сценария) данного задания к лабораторной работе.
4 Выполнить задание примера 4, изменив сумму ежемесячной выплаты на n·100 .
1Название, цель, содержание работы
2 Письменные ответы на контрольные вопросы
