Finansovaya_matematika_v_Excel_Levin_L_A

81

Рис. 4-2 Фрагмент рабочего листа MS Excel c вычислением величины чистого дисконтированного потока в соответствии с формулой (4-1)

Рис. 4-3 Фрагмент рабочего листа MS Excel в режиме отображения формул c вычислением величины чистого дисконтированного потока в соответствии с формулой (6-1)

Вячейках столбца :

"А" размещены периоды поступления (оттока) денежных средств;

"В" размещаются величины денежных потоков в соответствующие периоды;

"С" размещены аккумулированные значения денежных потоков в данном

периоде. Например, в ячейке "С4" может быть записано: =C3+B4;

"D" размещаются формулы расчета величины коэффициента дисконтирования денежных потоков. Например, в ячейке "D3" записывается: =(B3/(1+0)^A3)/B3; в ячейке "D4" записывается: =((B4/(1+0.1)^A4))/B4 и т.д.;

"E" значения дисконтированных денежных потоков. Например, в ячейке "E4" записывается: =B4*D4

"F" записываются формулы расчеты аккумулированных дисконтированных денежных потоков в соответствующий период времени. Например, в ячейке "F3" записывается величина денежного потока в начальный период (начальные инвестиции): =D3; в ячейке "F4" записывается: =F3+E4 и т.д.

“G” записывается логическая функция анализа окупаемости проекта. Например, в ячейке “G3” записывается формула: =ЕСЛИ(C3>0;"Проект

82

окупается";"Проект не окупается"), которая копируется в ячейки G4:G11

таблицы.

Таким образом, в результате выполненных вычислений получаем: Чистый дисконтированный доход = 32,01 Дисконтированный доход = -(-150)+32,01 = 182,01

Готовый результат 182,01 в одной клетке дает табличная формула =NPV(10%;C4:C11), вызывающая специальную финансовую функцию со ссылкой на норму дисконтирования _("Ставка") и табличные координаты блока значений ("Значения1".,..)элементов денежного потока, расположенных в хронологическом порядке.

В русских версиях MS Excel функция NPV имеет название ЧПС или в НПЗ в младших версиях.

Рис. 4-4 Диалоговое окно функции ЧПС()

Необходимо

заметить, что, несмотря на название, функция NPV (ЧПС, НПЗ) вычисляет не весь чистый, а только дисконтированный доход, то есть Present Value (PV) денежного потока (на один период назад от первого поступления/выплаты).

Пояснения.

Функция ЧПС() (НПЗ) возвращает величину чистой приведенной стоимости инвестиции, используя ставку дисконтирования, а также стоимости будущих выплат (отрицательные значения) и поступлений (положительные значения).

Синтаксис функции: ЧПС(ставка;значение1;значение2; ...) Ставка — ставка дисконтирования за один период.

Значение1, значение2, ... — от 1 до 29 аргументов, представляющих расходы и доходы.

Значение1, значение2, ... должны быть равномерно распределены во времени, выплаты должны осуществляться в конце каждого периода.

Значение1, значение2, ... могут вводится либо в отдельные окна либо списком ( при этом, порядок ввода значений (либо значений в списке) определяется порядком поступлений и платежей)

Для вычисления чистого дисконтированного дохода к выражению

=NPV(10%;C4:C11) необходимо добавить отрицательную величину инвестиционных затрат нулевого периода, записанное в таблице в ячейке В3

=ЧПС(10%;B4:B11)+B3

85

Вячейках:

C1:G1 размещены номера периодов поступления денежных средств;

C2:G2 размещены величины поступления денежных средств (величины CFk);

C3:G3 размещены формулы дисконтирования поступающих денежных средств. Например, в ячейке С3 записана формула =C2/((1+15%)^C1), соответствующая левой части формулы 6-3;

C4:G4 записаны формулы вычисления накопленного в данный период дисконтированного денежного потока. Например, в ячейке С4 записана формула =B4+C3 (сумма величины инвестиции и поступивших в этот период (1) денежных средств), а в ячейке D4 записывается формула =C4+D3 (сумма величины накопленного дисконтированного потока и поступивших в этот период (2) денежных средств) и т.д.

Анализируя построенную таблицу легко видеть, что инвестиции полностью окупаются в интервале между 2 и 3 периодами. Тогда в соответствии с 7-4 период окупаемости может быть найден как:

=D1+(-D4/E3) =2+480,15/526,01 =2,91 года

Таким образом, период, реально необходимый для возмещения инвестированной сумы, составит 2,91 года или 2 года и 332 дня.

Период окупаемости может быть также определен, если в ячейку С5 записать формулу: =ЕСЛИ(C4>0;C1-(B4+C3)/C3;0)42 и скопировать ее в остальные ячейки строки.

4.3.ИНДЕКС РЕНТАБЕЛЬНОСТИ

Индекс рентабельности (benefit-cost ratio, profitability index - PI) показывает, сколько единиц современной величины денежного потока приходится на единицу предполагаемых первоначальных затрат. Для расчета показателя используется следующая формула:

( 4-5)

Если величина критерия PI > 1, то современная стоимость денежного потока проекта превышает первоначальные инвестиции, обеспечивая тем самым наличие положительной величины NPV. При этом норма рентабельности превышает заданную и проект следует принять.

При PI = 1 величина NPV= 0 и инвестиции не приносят дохода.

В случае, если PI < 1, проект не обеспечивает заданного уровня рентабельности и его следует отклонить

42 Формула реализует условие: если величина накопленного дисконтированного дохода больше нуля, то вычисляется период окупаемости как разность величины накопленного дисконтированного дохода в этот период и разности суммы накопленного дисконтированного дохода в предыдущий период и дисконтированного денежного потока, отнесенной к величине дисконтированного денежного потока.

86

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

Пример 4-4

Фирма рассматривает возможность участия в финансировании шести проектов, предполагаемые условия реализации которых приведены в таблице рис.1. Инвестиционный бюджет фирмы равен 250000,00.

Рис. 4-9 Выбор проектов для инвестирования

Как следует из таблицы ( столбец "Е"), чистая приведенная стоимость всех проектов (NPV) больше нуля, а индекс рентабельность (PI) больше 1. И, если бы инвестиционный бюджет фирмы не был ограничен суммой в 250000,00, то все проекты следовало бы принять. Однако из-за ограниченности бюджета может быть реализован только тот набор (портфель) проектов, при котором суммарные инвестиции не превышают 250000,00.

Для выбора наиболее привлекательных проектов воспользуемся операцией "Поиск решения".

В ячейке "Е8"запишем целевую функцию: =СУММПРОИЗВ(B2:B7;E2:E7); Примечание: в ячейках столбца "В" размещаются результаты выбора проекта: "1" –

проект выбран; "0" – проект отклонен.

вячейке "В9" запишем формулу ограничений:

=СУММПРОИЗВ(B2:B7;C2:C7);

вдиалоговом окне "Поиск решения" выполним необходимые установки:

Рис. 4-10 Диалоговое окно "Поиск решения"

В результате выполнения процедуры "Поиск решения" оказывается оптимальным инвестирование четырех

проектов:"А", "И", "D" и "Е", при этом суммарная величина NPV составит 121000.

87

Замечание: При оценке привлекательности проекта для инвестиций следует в большей степени руководствоваться величиной NPV, а не PI.

4.3.1.Внутренняя норма доходности. Функция ЧИСТВНДОХ()43

Внутренняя норма доходности44 (internal rate of return - IRR) является наиболее широко используемым критерием эффективности инвестиций.

Под внутренней нормой доходности понимают процентную ставку, при которой чистая современная стоимость инвестиционного проекта равна нулю.

Внутренняя норма доходности определяется путем решения следующего уравнения:

i=0 (1+ставка)

Нетрудно заметить, что при NPV = 0, современная стоимость проекта (PV) равна по абсолютной величине первоначальным инвестициям I0, следовательно они окупаются. В общем случае, чем выше величина IRR, тем больше эффективность инвестиций. На практике величина IRR сравнивается с заданной нормой дисконта r. При этом если IRR >

r, проект обеспечивает положительную NPV, и доходность, равную IRR – r. Если IRR < r, то затраты превышают доходы и проект будет убыточным.

Величина IRR может быть найдена:

из формулы ЧИСТНЗ() с использование процедуры "Подбор параметра…" (подбирается величина ставки, при которой NPV=0;

использованием функции ЧИСТВНДОХ()45

Рис. 4-11 Диалоговое окно функции ЧИСТВНДОХ()

Пример 4-5

Фирма намерена 1 января 2005г. инвестировать 200 млн. руб. в проект, ожидаемые доходы по которому в последующие 5 лет составят 40, 60, 80, 90 и 100 млн. руб.

Определите внутреннюю норму дохода по проекту, если поступление доходов будет производится 1 января каждого года

Оцените экономическую эффективность проекта, если рыночная норма дохода составляет 10%

43 В том случае, если платежи имеют регулярный характер и даты их поступления не определены, для вычисления величины IRR может быть использована функция ВНДОХ() (либо функция ВСД в версии Excel 2002,2003, XP

44В литературе эта величина иногда носит название – средневзвешенная доходность 45 В том случае, если платежи имеют регулярный характер и даты их поступления не определены, для

вычисления величины IRR может быть использована функция ВНДОХ() (либо функция ВСД в версии Excel 2002,2003, XP

88

Решение

Вариант 1. Использование операции "Подбор параметра…" для определения величины IRR

В ячейках "В3" и "С3" размещается формула вычисления чистого дисконтированного потока =ЧИСТНЗ(10%;B2:G2;B1:G1)т и =ЧИСТНЗ(C4;B2:G2;B1:G1), соответственно

установите курсор в ячейку "С3" и выполните команду СЕРВИС Подбор параметра...;

Рис. 4-12 Диалоговое окно «Подбор параметра»

в открывшемся диалоговом окне сделайте необходимые установки:

"Установить в ячейке" - $C$3;

"Значение" - 0;

"Изменяя значение ячейки" $C$5.

После щелчка на кнопке "ОК" в ячейку С5 будет возвращено найденное значение IRR =20.94%

Вариант 2. Использование функции ЧИСТВНДОХ()

В ячейку "С6" введите формулу =ЧИСТВНДОХ(B2:G2;B1:G1), возвращающую значение IRR=20,94%

Таким образом, при рыночной ставке дисконта менее 24% инвестирование проекта – целесообразно.

4.3.2.Модифицированная внутренняя норма доходности. Функция МСВД()

Вотличие от NPV, критерий внутренней нормы доходности (IRR) неявно предполагает реинвестирование получаемых доходов по ставке IRR. Если финансирование проекта в рассмотренной выше задаче осуществляется за счет банковской ссуды под 10% годовых, то получаемые в процессе его реализации доходы должны быть реинвестированы по ставке 24,94% годовых, т.е. в 2,5 раза превышающей ставку по долгосрочным кредитам! Очевидно, что это вряд ли осуществимо в реальной практике.

89

Для корректного учета предположения о реинвестировании в ППП EXCEL реализована функция МВСД().

Функция МВСД() вычисляет вычислить модифицированную внутреннюю норму доходности (modified internal rate of return - MIRR) при заданной ставке реинвестирования .

Пример 4-6

Предположим, что в задаче 4.5 имеется возможность реинвестирования получаемых доходов по ставке 8% годовых. Определить модифицированную норму доходности.

Решение Модифицированная внутренняя норма доходности составит:

=МВСД(B2:G2;10%;8%) =16,02%

Рис. 4-13 Диалоговое окно функции МСВД (MIRR)

Нетрудно заметить, что полученная модифицированная

норма рентабельности ниже предыдущей, однако выше заданной, поэтому даже при пессимистичной оценке реальных условий проект можно считать прибыльным.

4.4.ДЕНЕЖНЫЙ ПОТОК ИНВЕСТИЦИОННОГО ПРОЕКТА С ПРОИЗВОЛЬНЫМИ ПЕРИОДАМИ ПОСТУПЛЕНИЯ ПЛАТЕЖЕЙ

В том случае, если поступления (оттоки) денежных средств происходят в произвольные периоды времени), то для расчета величины чистого дисконтированного дохода может быть использована функция Excel ЧИСТНЗ().

Рис. 4-14 Диалоговое окно функции ЧИСТНЗ()

Пример 4-7 1 июля 2003 года была сделана инвестиция в проект в размере 10000 тыс. рублей

Врезультате реализации проекта ожидается получение прибыли:15 сентября 2003 г.

–2750 тыс. руб.; 1 ноября 2003г. -4250 тыс. руб. и 1 января 2004г. – 5250 тыс. руб.

Норма дисконтирования 9% Необходимо определить чистую текущую стоимость инвестиции на 1 июля 2003г..и

на 1 июля 2002г Решение.

На листе Excel создайте таблицу, подобную приведенной на рисунке.

Рис. 4-15 Исходные данные примера

Вызовите функцию ЧИСТНЗ(), расположенную в категории функций «Финансовые» и введите в диалоговое окно этой функции необходимые аргументы:

Рис. 4-16 Диалоговое окно функции ЧИСТНЗ()