Существуют сотни онлайновых финансовых планировщиков. Все они просты в использовании, но ограничены по функциональности. MS Excel на их фоне - настоящий комбайн. В нём есть 53 финансовые формулы на все случаи жизни, а для контроля и планирования бюджета полезно знать три из них.
Функция ПЛТ
Одна из актуальнейших функций, с помощью которой можно рассчитать сумму платежа по кредиту с аннуитетными платежами, то есть когда кредит выплачивается равными частями. Полное описание функции .
ПЛТ(ставка;кпер;пс;бс;тип)
- Ставка - процентная ставка по ссуде.
- Кпер - общее число выплат по ссуде.
- Пс - приведённая к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.
- Бс - требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент «бс» опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение «бс» равно 0.
Функция СТАВКА
Вычисляет процентную ставку по займу или инвестиции, базируясь на величине будущей стоимости. Полное описание функции .
СТАВКА(кпер;плт;пс;бс;тип;прогноз)
- Кпер - общее число периодов платежей для ежегодного платежа.
- Плт - выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент «плт» состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент «пс» является обязательным.
- Пс - приведённая (текущая) стоимость, т. е. общая сумма, которая на данный момент равноценна ряду будущих платежей.
- Бс (необязательный аргумент) - значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если аргумент «бс» опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0).
- Тип (необязательный аргумент) - число 0 (нуль), если платить нужно в конце периода, или 1, если платить нужно в начале периода.
- Прогноз (необязательный аргумент) - предполагаемая величина ставки. Если аргумент «прогноз» опущен, предполагается, что его значение равно 10%. Если функция СТАВКА не сходится, попробуйте изменить значение аргумента «прогноз». Функция СТАВКА обычно сходится, если значение этого аргумента находится между 0 и 1.
Функция ЭФФЕКТ
Возвращает эффективную (фактическую) годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году, за которые начисляются сложные проценты. Полное описание функции
ЛАБОРАТОРНЫЕ РАБОТЫ
Лабораторная работа №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дставитель банка.
Лабораторная работа №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 Письменные ответы на контрольные вопросы
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
Инструкции
|
|
|
ПРИМЕЧАНИЕ. Годовая процентная ставка делится на 12, поскольку начисление сложных процентов производится ежемесячно.
Функция плт
Применимо к: Microsoft Office Excel 2007
Печать
Показать все
Возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.
Синтаксис
ПЛТ (ставка ;кпер ;пс ;бс;тип)
Более подробное описание аргументов функции ПЛТ см. в описании функции ПС.
Ставка - процентная ставка по ссуде.
Кпер - общее число выплат по ссуде.
Пс - приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.
Бс - требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нолю), т. е. для займа, например, значение бс равно 0.
Тип - число 0 (ноль) или 1, обозначающее, когда должна производиться выплата.
Замечания
Выплаты, возвращаемые функцией ПЛТ, включают основные платежи и платежи по процентам, но не включают налогов, резервных платежей или комиссий, иногда связываемых со ссудой.
Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов «ставка» и «кпер». Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12%/12 для задания аргумента «ставка» и 4*12 для задания аргумента «кпер». Если вы делаете ежегодные платежи по тому же займу, то используйте 12 процентов для задания аргумента «ставка» и 4 для задания аргумента «кпер».
Совет. Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, умножьте возвращаемое функцией ПЛТ значение на «кпер».
Пример 1
Чтобы этот пример проще было понять, скопируйте его на пустой лист.
Копирование примера
|
|
|
В данной статье описана формула 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)
