Лаб раб 4 Фин функции

13

3. Расчет периодических платежей

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

ПЛТ() − возвращает периодические платежи, осуществляемые на основе постоянной процентной ставки и не меняющиеся за все время расчета;

ПРПЛТ() − платежи по процентам за конкретный период на основе постоянства сумм периодических платежей и постоянства ставки;

ПРОЦПЛАТ() − вычисляет проценты, выплаченные за определенный период;

ОСПЛТ() − вычисляет основные платежи по займу (за вычетом процентов) за конкретный период;

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

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

Все эти величины вычисляются при расчете по схеме равномерного погашения займа.

Рассмотрим функции ПЛТ() и ПРПЛТ().

ПЛТ() вычисляет величину выплаты за один период на основе фиксированных периодических выплат и постоянной процентной ставки. Выплаты, рассчитанные функцией ПЛТ(), включают основные платежи и платежи по процентам.

Синтаксис: ПЛТ (ставка; kпер; пс; бс; тип), где ставка − процентная ставка за период;

kпер − общее число периодов выплат; пс − приведенная (текущая) стоимость;

бс − баланс, который нужно достичь после последней выплаты, по умолчанию значение равно 0;

тип − значение равно 1, если платежи вносятся в начале периода погашения и равно 0, если в конце периода.

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

14

Пример 1. Необходимо накопить 4000 тыс. руб. за 3 года, откладывая постоянную сумму в конце каждого месяца. Какой должна быть эта сумма, если норма процента по вкладу − 12% годовых.

ПЛТ (12%/12; 12 3; 4000) = -92,86.

Пример 2. Банк выдал ссуду 200 млн. руб. на 4 года под 18% годовых. Ссуда выдана в начале года, а погашение начинается в конце года одинаковыми платежами. Определить размер ежегодного погашения ссуды.

ПЛТ (18%; 4; -200) = 74,35 млн. руб.

Функция ПРПЛТ() вычисляет сумму платежей процентов за заданный период на основе периодических постоянных выплат и постоянной процентной ставки.

Синтаксис: ПРПЛТ (ставка; период; kпер; пс; бс; тип), где период − конкретный период платежа, остальные аргументы имеют тот же смысл.

Пример 3. Вычислить платежи по процентам за первый месяц от 3-х годичного займа в 800 тыс. руб. из расчета 10% годовых.

ПРПЛТ (10%/12; 1; 12 3; 800) = -6,667 тыс. руб.

Пример 4. Предположим, что за счет ежегодных отчислений в течение 6 лет был сформирован фонд в 5000 тыс. руб. Определим, какой доход приносили вложения владельцу за последний год, если годовая ставка составляла 17,5%. Доход за последний год (6 период) составил ПРПЛТ (17,5%; 6; 6; 5000) = 664,81 тыс. руб.

Ежегодно отчислялось ПРПЛТ (17,5%; 6; 5000) = -536,27 тыс. руб.

Знак žминус¤ указывает на то, что сумму надо вернуть. Дополнительные примеры использования функций ПЛТ() и

ПРОЦПЛАТ() приведены в табл. 2. и табл. 3.

Таблица 2 – Функция ПЛТ()

17

Рисунок 9 – Команда Таблица данных

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

4.1. Использование Таблицы данных (подстановки) для одной переменной

Пример 5. Необходимо определить какие ежемесячные выплаты надо вносить для выплаты ссуды размером 300 тыс. руб., выданной на 5 лет, при разных процентных ставках. Затем рассчитать платежи по процентам за каждый период, считая его равным 1году, т.е. за 1, 2, 3, 4 и 5 годы. Используем инструмент MS Exсel Таблица данных. Результат расчета представлен на рис. А.1. (Приложение А).

Последовательность

решения:

1.В ячейку D15 внести формулу для расчета периодических посто-

янных выплат по займу при условии, что он по- Рисунок 10 – ДО Таблица данных

гашается полностью в течение срока займа.

18

2.Выделить диапазон ячеек, содержащий исходные значения процентных ставок и формулу для расчета − C15:D20. Исходные данные − в диапазоне C16:C20. Результаты подстановки будут расположены в столбце D16:D20.

3.На вкладке Данные в группе Анализ "что если" выбрать команду Таблица данных. Появляется диалоговое окно (ДО) Таблица данных (рис. 10). Ввести ссылку на переменную в ячейку $C$12 в поле žПодставлять значения по строкам в¤.

4.Нажать Ok. Получим результаты подстановки в столбце D16:D20. Полученные значения отрицательны, так как заем

нужно отдать (рис. А.1., Приложение А).

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

Например, для расчета платежей по процентам за первый год для каждого значения процентной ставки в ячейку E15 надо ввести формулу: =ПРОЦПЛАТ (C12; 1; C11; C10) и выполнить подстановку. Для расчета выплат по процентам для остальных периодов (со 2-го по 5-ый) необходимо подставить формулы в следующие ячейки справа от последней, указав в качестве 2-го аргумента − 2 и т. д.

4.2. Использование Таблицы данных (подстановки) для двух переменных

Пример 6. Необходимо найти ежемесячные выплаты по

центные ставки) в столбец B34:B38.

19

2.Ввести второе множество входных значений (сроки погашения) в строку C33:G33.

3.Ввести на пересечении строки и столбца, содержащих два множества входных значений, т.е. в ячейку B33, формулу расчета: = ПЛТ(B28/12; B27*12;B26).

4.Далее необходимо выделить диапазон таблицы данных B33:G38.

5.Выполнить команду Таблица данных и заполнить диалоговое окно (рис. 11).

6.Нажать Ok. Результаты появятся в таблице (рис. А.2., Приложение А).

5. Задание для выполнения лабораторной работы

Задание №1.

1.Используя инструмент MS Excel Таблица данных (подстановки), определить, какие ежемесячные выплаты необходимо вносить для погашения ссуды, выданной на определенный срок при различных процентных ставках. Использовать инструмент

Таблица данных (подстановки) для одной переменной.

2.Рассчитать платежи по процентам за каждый период, считая один период равным одному году. Использовать инструмент Таблица данных (подстановки) для одной переменной. Результаты 1-го и 2-го задания представить в одной Таблице данных (подстановки) (рис. А.1., Приложение А) на отдельном рабочем листе.

Задание №2.

Найти ежемесячные выплаты по займу для различных сроков погашения (3, 4, 5) лет и различных процентных ставок. Использовать инструмент Таблица данных (подстановки) для двух переменных. Результаты представить на отдельном рабочем листе (рис. А.2., Приложение А).

Варианты заданий приведены в табл. 4., номер варианта – номер логина.

Подготовить отчет о выполненной работе в виде электронного документа MS Word.

20

Таблица 4 – Варианты заданий

6. Требования к содержанию и оформлению отчета

Отчет должен включать:

–название работы;

–фамилию, имя, отчество, группу студента;

–формулировку заданий;

–анализ результата;

–рабочий лист с исходными и выходными данными (Приложение А);

–ответы на контрольные вопросы.

7.Контрольные вопросы

1.Какие модели финансово–экономических расчетов знае-

те?

2.Назовите методы финансово–экономических расчетов.