Лабораторная работа 10. Финансовые расчеты

 

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

 

Финансовые расчеты, проводимые с помощью встроенных финансовых функций Excel, можно разделить на четыре группы:

- наращение и дисконтирование[10] доходов и затрат (БЗ,ПЗ,КПЕР,НОРМА,ППЛАТ и др.);

- анализ эффективности капитальных вложений (НПЗ,ВНДОХ и др.);

- расчеты по ценным бумагам (ДОХОД,ЦЕНА и др.);

- расчет амортизационных отчислений (АМР,АМГД и др.).

Всего в Excel 2000 встроено более 50 финансовых функций. 16 функций доступны сразу, остальные – после вызова пакета сервисных программ Анализ данных (см. предыдущую работу).

Рассмотрим применение некоторых из них.

 

Задание 1. В банк помещен депозит в размере А = 5000 руб. По этому депозиту в первом году будет начислено р₁ = 10%, во втором – р₂ = 12%, в третьем – р₃ = 15%, в четвертом и пятом – р_4,5 = 16% годовых.

1). Сколько будет на счету в конце пятого года?

2). Сколько будет на счету в конце пятого года при постоянной процентной ставке i = 13%?

3). Сколько надо поместить на счет при постоянной процентной ставке i = 13%, чтобы обеспечить ту же сумму, что была получена при ответе на первый вопрос?

Решить аналогичную задачу, взяв данные из таблицы 10.1.

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

 

Для решения этой задачи можно использовать функции БЗРАСПИС, ПЗ, БЗ.

 

Таблица 10.1

 

Вариант	А	р1	р2	р3	р4	р5	i

 

ФункцияБЗРАСПИС возвращает будущее значение единовременного вложения при переменной процентной ставке.

Ее синтаксис:

БЗРАСПИС (Первичное; План)

Аргументы:

Первичное – числовое значение, представляющее собой исходную сумму средств;

План – массив процентных ставок, используемых за рассматриваемый период.

 

ФункцияБЗ возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки (наращение из настоящего в будущее)[11]. Для расчета функции БЗ используется метод сложных процентов.

Ее синтаксис:

БЗ (Норма; Кпер; Выплата; Нз; Тип)

Аргументы:

Норма – процентная ставка за период (задавать в процентном формате или в долях);

Кпер– общее число платежных периодов, по истечении которых вы хотите определить объем имеющихся средств;

Выплата – величина постоянных периодических платежей;

Нз – начальное значение (текущая стоимость) вклада;

Тип – параметр, определяющий, когда вносятся платежи: в начале (=0) или в конце периода (=1). По умолчанию Тип=0.

Для ответа на третий вопрос эту функцию необходимо использовать совместно с сервисной функцией Excel Подбор параметра, т.к. искомое является аргументом функцииБЗ.

 

ФункцияПЗ возвращает как текущий (сегодняшний) объем вклада для достижения необходимого финансового результата, так и объем будущих постоянных периодических платежей[12] и является обратной по отношению к функции БЗ[13].

Ее синтаксис:

ПЗ (Норма, Кпер, Выплата, Бс, Тип)

Аргументы:

Норма – процентная ставка за период (задавать в процентном формате или в долях);

Кпер– общее число периодов выплат;

Выплата – величина постоянных периодических платежей;

Бс – баланс наличности, который нужно достичь после последней выплаты (если опущен, то 0);

Тип – 0 или 1. Если 0 – оплата производится в конце периода, если 1, то в начале.

На рис 10.1 приведено диалоговое окно функции ПЗ, использованной для решения следующей задачи.

Сколько вы заплатите за холодильник при покупке его в рассрочку на 3 года под неизменную процентную ставку 5% при ежеквартальной выплате 1500 руб.

Ответ: почти 16619 руб.

Задание 2. У вас просят в долг P=10000 руб. и обещают возвращать по A=2000 руб. в течение N=6 лет. У вас есть другой способ использования денег: положить некоторую сумму в банк под 7% годовых и каждый год снимать по 2000 руб.

1). Сколько же надо положить, чтобы обеспечить те же условия, что вам предлагают?

2). Какая финансовая операция будет более выгодна для вас?

3). С помощью Диспетчера сценариев проанализировать ситуацию для нескольких возможных вариантов изменения параметров А, Р и N, взяв их из таблицы 10.2. В качестве выходных данных получить не только числовое значение начального вклада, но и текст-рекомендацию, что нужно делать: нести в банк или давать в долг.

Создание первого сценария производится с помощью последовательности команд Сервис | Сценарии | Диспетчер сценариев | Добавить (рис. 10.2) после того, как на листе получено решение задачи для одного (опорного) варианта.

 

Таблица 10.2

Вариант

N, лет.

P, тыс. р.

A, тыс. Р.

4,0

1,6

В поле Изменяемые ячейки указываются те ячейки, в которых находятся параметры задачи.

После нажатия кнопки ОК в диалоговом окне Значения ячеек сценария вводятся значения параметров для первого сценария и с помощью диалогового окна Диспетчер сценариев (рис. 10.3) добавляется необходимое число сценариев.

С помощью кнопки Отчет открывается диалоговое окно Отчет по сценарию, где определяется тип отчета (Структура или Сводная таблица) и задаются ячейки, где вычисляется результат, т.е. значение функции ПЗ и текст-рекомендация.

Таким образом, на листе Excel будет находиться только одно (опорное) решение; все остальные варианты – в отчете.

Задание 3. У вас есть возможность проинвестировать проект стоимостью A=10000 руб. Через год будет возвращено P₁=2000 руб., через два года - P₂=4000 руб., через - три года P₃=7000 руб. Альтернативный вариант – положить деньги в банк под i процентов годовых.

При какой годовой банковской процентной ставке деньги выгоднее вкладывать в инвестиционный проект? Решить аналогичную задачу, взяв данные из таблицы 10.3.

Указание. Использовать сервисную функцию Excel Подбор параметра и финансовую функцию НПЗ.

 

Таблица 10.3

 

Вариант	N	A	P1	P2	P3	P4	P5

 

ФункцияНПЗ используется в Excel для расчета эффективности планируемых капиталовложений. Она возвращает чистую текущую величину вклада (инвестиции), вычисленного на основе ряда последовательных (неравномерных) поступлений денежных средств[14].

Ее синтаксис:

НПЗ (Ставка; Значение1; Значение 2;…Значение N)

Аргументы:

Ставка– процентная ставка за период;

Значения – до 29 аргументов (могут быть массивы), представляющих поступления (доходы со знаком «+», расходы со знаком «–»).

Рассмотрим применение функции НПЗ для решения следующей задачи.

Пусть в начале первого года вы вкладываете в инвестиционный проект 30000 рублей и предполагаете годовые доходы 8000 руб., 9000 руб., 10000 руб., 12000 руб. в последующие четыре года (начиная со второго). Предположим, что годовая учетная ставка составляет 8 процентов, в таком случае чистый текущий объем инвестиции составит:

–30000+НПЗ(8%; 8000; 9000; 10000; 12000) =1882,14 руб.

Если платежи происходят в конце рассматриваемых периодов, то формула расчета чистого текущего объема инвестиции примет несколько другой вид:

НПЗ(8%; –30000; 8000; 9000; 10000; 12000) =1742,72 руб.,

т.е. первоначальные затраты 30000 руб. были включены в формулу одним из значений.

 

Задание 4. Вычислить N - годичную ссуду покупки квартиры за А рублей с годовой ставкой i процентов и начальным взносом p процентов.

Используя функцию ППЛАТ, сделать расчет отдельно для ежемесячных и ежегодных выплат и сравнить результаты. Какова сумма выплаченных комиссионных в обоих вариантах? Данные взять из таблицы 10.4.

 

Таблица 10.4

 

Вариант

N, лет

А, тыс .р.

р,%.

i, %

 

ФункцияППЛАТ вычисляет величину постоянной периодической выплаты ренты, регулярных платежей по займу и т.п. при постоянной процентной ставке[15]. Она связана с функцией ПЗ: это то, что в ней называется Выплата.

Ее синтаксис:

ППЛАТ (Норма, Кпер, Пз, Бс, Тип)

Аргументы:

Норма – это процентная ставка по ссуде.

Кпер – это общее число выплат по ссуде.

Пз– это общая сумма, которую составят будущие платежи.

Бс – это будущая сумма или баланс наличности, который нужно достичь после последней выплаты.

Тип – это число 0 или 1, обозначающее, когда должна производиться выплата.

 

Задание 5.Используя функцию НОРМА, определить процентную ставку для N - летнего займа в А рублей с ежегодной выплатой в Р рублей. Данные взять из таблицы 10.5. Каковы будут ваши действия, если банк дает заем под более высокий процент? Введите сами значение этого процента и пересчитайте N или Р, выбрав нужную финансовую функцию.

 

Таблица 10.5

 

Вариант
N, лет.
А, млн.р.	1,0	0,8	0,8	0,8	1,7	1,0	7,5	5,9				6,5
Р, млн.р.	0,2	0,15	0,12	0,12	0,19	0,15	0,7	0,7	1,0	1,7	1,8	0,24

 

Функция НОРМА вычисляет процентную ставку за один период, необходимую для получения определенной суммы в течение заданного срока путем постоянных взносов[16].

Ее синтаксис:

НОРМА (Кпер, Выплата, Пз, Бс, Тип, Предположение)

Аргументы:

первые пять аргументов соответствуют аргументам функций ПЗ и ППЛАТ;

Предположение - предполагаемая величина нормы (поиск величины нормы организован итерационным способом и это значение есть начальное приближение); если опущено, то берется значение 10% .

Функция КПЕРвозвращает общее количество периодов выплаты для данного вклада на основе периодических постоянных выплат и постоянной процентной ставки.

Ее синтаксис:

КПЕР(Норма;Выплата;Нз;Бс;Тип)

Аргументы:

Норма – это процентная ставка за период.

Выплата – это выплата, производимая в каждый период.

Нз – это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента.

Бс – это будущая стоимость или баланс наличности, который нужно достичь после последней выплаты.

Тип – это число 0 или 1, обозначающее, когда должна производиться выплата.

 

Задание 6.Используя нужную финансовую функцию, решить следующие задачи.

1. Рассчитайте, какая сумма будет на счете, если вклад размером 50 тыс. руб. положен под 12% годовых на три года, а проценты начисляются каждые полгода.

2. Рассчитайте, через сколько месяцев вклад размером 50 тыс. руб. достигнет 100 тыс. руб. при ежемесячном начислении процентов и ставке процента 20% годовых.

3. Рассчитайте текущую стоимость вклада, который через три года составит 150 тыс. руб. при ставке процента 20% годовых.

4. Определите текущую стоимость обязательных ежемесячных платежей размером 100 тыс. руб. в течение пяти лет, если процентная ставка составляет 12%.

5. Вклад размером 200 тыс. руб. положен под 10% годовых. Рассчитайте, какая сумма будет на банковском счете через пять лет, если проценты начисляются ежемесячно.

6. Определите эффективность инвестиций размером 200 млн. руб., если ежемесячные доходы за первые пять месяцев составят соответственно 20, 30, 50, 80 и 100 млн руб. Издержки привлечения капитала составляют 13, 5% годовых.

7. Рассчитайте будущую стоимость облигации номиналом 50 тыс. руб., выпущенной на пять лет, если предусмотрен следующий порядок начисления процентов: в первые два года – 12% годовых, в следующие два года – 14%, в последний год – 16% годовых.

8. На банковский счет вносятся обязательные ежемесячные платежи по 20 тыс. руб. Рассчитайте, какая сумма окажется на счете через четыре года при ставке процента 12% годовых.

9. Определите текущую стоимость ежемесячных платежей размером 40 тыс. руб. в течение двух лет при ставке процента 15% годовых.

10. Рассчитайте, какую сумму нужно положить на депозит, чтобы через четыре года она выросла до 2 млн. руб. при норме процента 9% годовых.

11. Рассчитайте, через сколько лет произойдет погашение займа размером 50 млн. руб., если выплаты по 400 тыс. руб. производятся в конце каждого квартала, а ставка процента – 15% годовых.

12. Определите текущую стоимость обычных ежеквартальных платежей размером 350 тыс. руб. в течение семи лет, если ставка процента – 11% годовых.

13. Определите ежемесячные выплаты по займу в 10 млн руб., взятому на восемь месяцев под 10% годовых.

14. Рассматривается проект стоимостью 100 млн руб. Ожидается, что ежемесячные доходы по проекту составят 16, 25, 36, 50 млн. руб. за четыре месяца. Определите чистую текущую стоимость проекта, если годовая норма процента 19%.

15. Какую сумму необходимо ежемесячно вносить на счет, чтобы через три года получить 10 млн. руб., если годовая процентная ставка 18%?

16. По сертификату, погашаемому выплатой в 250 тыс. руб. через три года, проценты начисляются раз в полугодие. Определите цену продажи, если номинальная ставка 30% годовых.

17. Капитальные затраты по проекту составляют 470 млн. руб., и ожидается, что его реализация принесет следующие доходы за три года: 170, 230, 190 млн руб. соответственно. Издержки привлечения капитала равны 14%. Определите чистую текущую стоимость проекта.

18. Заем в 900 тыс. руб. погашается равномерными периодическими платежами по 100 тыс. руб. каждые полгода в течение семи лет. Определите годовую ставку процента.

19. Предположим, вам предлагают два варианта оплаты: сразу заплатить 600 тыс. руб. или вносить по 110 тыс. руб. в конце каждого месяца в течение полугода. Вы могли бы обеспечить своим вложениям 9,5% годовых. Какой вариант предпочтительнее?

20. Предполагается, что ссуда размером 5 млн руб. погашается ежемесячными платежами по 140 тыс. руб. Рассчитайте, через сколько лет произойдет погашение, если годовая процентная ставка 16%.

21. Рассчитайте годовую ставку процента по вкладу размером 100 тыс. руб., если за 13 лет эта сумма возросла до 1 млн руб. при ежеквартальном начислении процентов.

22. Рассчитайте будущую стоимость облигации номиналом 100 тыс. руб., выпущенной на семь лет, если в первые три года проценты начисляются по ставке 17%, а в остальные четыре года – по ставке 22% годовых.

23. Какую сумму необходимо положить на депозит под 16% годовых, чтобы получить через три года 44 млн. руб. при полугодовом начислении процентов?

24. Определите, какая сумма окажется на счете, если вклад размером 90 тыс. руб. положен под 9% годовых, а проценты начисляются ежеквартально.

25. Сколько лет потребуется, чтобы платежи размером 3 млн. руб. в конце каждого месяца достигли значения 10 млн. руб., если ставка процента 14,5%?

26. Какая сумма должна быть выплачена, если шесть лет назад была выдана ссуда 1,5 млн руб. под 15% годовых с ежемесячным начислением процентов.

27. Взносы на банковский счет составляют 200 тыс. руб. в начале каждого месяца. Определите, сколько будет на счете через семь лет при ставке процента 10%?

28. Рассчитайте чистую текущую стоимость проекта, затраты по которому составили 400 млн. руб., а доходы за первые два года составили 40 и 75 млн. руб. Процентная ставка 15% годовых.

29. Рассчитайте процентную ставку для трехлетнего займа размером 5 млн. руб. с ежеквартальным погашением по 800 тыс. руб.

30. Рассчитайте, через сколько лет обязательные ежемесячные платежи размером 150 тыс. руб. принесут доход в 10 млн. руб. при ставке процента 13,5% годовых.

31. Рассчитайте, через сколько месяцев вклад размером 50 тыс. руб. достигнет 100 тыс. руб. при ежемесячном начислении процентов и ставке процента 20% годовых.