Функция ПЛТ. Расчёт величины ежемесячной выплаты кредита
Лабораторная работа №5
Работа с финансовыми функциями. Анализ «Что-если»
Цель работы: научиться работать с финансовыми функциями Excel и выполнять анализ "Что-если" Содержание работы: 1 Финансовые функции при экономических расчётах 2 Прогнозирование с помощью анализа "Что-если"
Финансовые функции при экономических расчётах Функция ПЛТ. Расчёт величины ежемесячной выплаты кредита Функция ПЛТ определяет сумму периодического платежа для аннуитета[1] на основе постоянства сумм платежей и постоянства процентной ставки.
Введём данные в таблицу Excel согласно рис. 1) 1 Выделить ячейку В6 и щелкнуть по кнопке Вставка функции (знак fx слева от строки формул). Появится окно Мастера функций, выбрать категорию Финансовые. 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 Письменные ответы на контрольные вопросы 3 Выводы по работе
На дискете должны быть сохранены результаты работы
[1] Аннуитет – периодический платёж по займу, когда кредитор получает доход (ренту), включающий погашение суммы долга и выплату процентов по нему.
Читайте также: G) ограниченная функция снизу и сверху Воспользуйтесь поиском по сайту: ![]() ©2015 - 2025 megalektsii.ru Все авторские права принадлежат авторам лекционных материалов. Обратная связь с нами...
|