Левин Л.А. Финансовая математика в EXCEL
Подождите немного. Документ загружается.
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
83
Метод определения чистой текущей стоимости часто используется при оценке
эффективности инвестиций. Он позволяет определить нижнюю границу прибыльности и
использовать ее в качестве критерия при выборе наиболее эффективного проекта.
Положительное значение NPV является показателем того, что проект приносит
чистую прибыль, после покрытия всех связанных с ним расходов
Пример 4-2
Сравним два проекта, денежные потоки которых представлены на рис.6-4 , при
значениях ставки дисконтирования
15%
Рис. 4-5 Исходные данные и
решение примера 4-2
В ячейке
“B8”
Разместим
формулу
ЧПС()
, которую
скопируем в ячейку
“С8”
Рис. 4-6 Диалоговое окно
функции ЧПС() для Проекта 1
В ячейках
“В9
” и “
С9”
вычисляется значения чистого
дисконтированного дохода для
Проектов 1 и 2.
Выполненные расчеты
показывают целесообразность принятия Проекта 2, не смотря на то, что величины
денежных потоков обоих проектов различаются несущественно.
4.2.
СРОК ОКУПАЕМОСТИ
Для анализа эффективности инвестиций часто используется такой показатель, как
срок окупаемости
(payback period method) – продолжительность времени, в течение
которого дисконтированные на момент завершения инвестиций прогнозируемые денежные
84
поступления равны сумме инвестиций. Иными словами – это количество лет, необходимых
для компенсации стартовых инвестиций
41
:
(
((
( )
))
)
0
1 1
1
n m
k
k
k j
CF / r Z
= =
= == =
= =
+ =
+ =+ =
+ =
∑ ∑
∑ ∑∑ ∑
∑ ∑
( 4-3)
Наиболее просто период окупаемости может быть определен как:
( 4-4)
Пример 4-3
Рассчитать срок окупаемости проекта, для которого размер инвестиций составляет 1
млн. руб., а денежные поступления в течение 5 лет будут составлять: 250; 400; 800; 900; 900
тыс. руб. соответственно. Ставка дисконтирования 15%.
Решение.
На листе Excel создадим таблицу, подобную приведенной на рис.4-7
Рис. 4-7 Фрагмент
рабочего листа Excel с
исходными данными и
решением примера 4-3
Рис. 4-8 Фрагмент рабочего листа Excel в режиме отображения формул с
исходными данными и решением примера 6-3
41
По сути дела этот показатель определяет срок, в течение которого инвестиции будут "заморожены",
так как реальный доход от инвестиционного проекта начнет поступать только по истечении периода
окупаемости
n
ок
= Число лет до года окупаемости + (Не
возмещенная стоимость на начало года
окупаемости / Приток наличности в течение
года окупаемости)
85
В ячейках:
C1:G1
размещены номера периодов поступления денежных средств;
C2:G2
размещены величины поступления денежных средств (величины
CF
k
);
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)
является наиболее
широко используемым критерием эффективности инвестиций.
Под внутренней нормой доходности понимают процентную ставку, при которой
чистая современная стоимость инвестиционного проекта равна нулю.
Внутренняя норма доходности определяется путем решения следующего уравнения:
( 4-6)
Нетрудно заметить, что при NPV =
0
, современная стоимость проекта
(
PV
)
равна по
абсолютной величине первоначальным инвестициям
I
0
,
следовательно они окупаются. В
общем случае, чем выше величина 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
0
0
0
1
n
i
i
i
поток
NPV
ЧПС() ЧИСТНЗ()
I
( ставка)
=
==
=
+
++
+
= = − = =
= = − = == = − = =
= = − = =
∑
∑∑
∑
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 Диалоговое окно
функции ЧИСТНЗ()
90
Ставка это ставка
дисконтирования, применяемая к
денежным потокам.
Значения
это ряд денежных потоков, соответствующий
графику платежей приведенной в аргументе даты.
Первый платеж является необязательным и
соответствует выплате в начале инвестиции.
Если первое значение яв
ляется выплатой, оно
должно быть отрицательным.
Все последующие выплаты дисконтируются на
основе 365-дневного года.
Ряд значений должен содержать по крайней
мере одно положительное и одно отрицательное
значения.
Даты
это расписание дат платежей, которое
соответствует ряду денежных потоков.
Первая дата означает начальную величину в
графике платежей. Все другие даты должны быть
позже этой даты, но могут идти в произвольном
порядке..
Пример 4-7
1 июля 2003
года была сделана инвестиция в проект в размере
10000
тыс. рублей
В результате реализации проекта ожидается получение прибыли:15 сентября 2003 г.
– 2750 тыс. руб.; 1 ноября 2003г. -4250 тыс. руб. и 1 января 2004г. – 5250 тыс. руб.
Норма дисконтирования
9%
Необходимо определить чистую текущую стоимость инвестиции на
1 июля 2003г.
.и
на
1 июля 2002г
Решение
.
На листе Excel создайте таблицу, подобную приведенной на рисунке.
Рис. 4-15 Исходные данные примера
Вызовите функцию
ЧИСТНЗ()
, расположенную в категории функций
«Финансовые» и введите в диалоговое окно этой функции необходимые аргументы:
Рис. 4-16 Диалоговое
окно функции ЧИСТНЗ()