Бухвалов А.В. Финансовые вычисления для профессионалов. Настольная книга финансиста

Подождите немного. Документ загружается.

252 10. Инвестиции

10.8. Используем Excel

В Excel имеется группа из 5 функций для расчета критериев эффектив-

ности инвестиционных проектов, ранее описанных в этом разделе. При-

ведем, как и раньше, сначала таблицу, в которой перечислены имена ар-

гументов, используемых этими функциями, и их назначение.

Аргумент Назначение

норма процентная ставка

(норма прибыли или цена капитала)

платежи поток платежей

(произвольной величины)

ставка ставка реинвестирования

полученных средств

даты массив дат платежей

Приведем теперь таблицу, в которой для каждой функции указаны ее

имя (в русифицированной версии и в англоязычной), формат обращения

и вычисляемая величина.

Функция Формат обращения Значение

НПЗ (NPV) НПЗ(норма;платежи) современная

стоимость потока

ВНДОХ (IRR) ВНДОХ(платежи[;прогноз]) внутренняя норма

доходности

МВСД (MIRR) МВСД (платежи;норма;ставка) модифицированная

ВНДОХ

ЧИСТПЗ (XNPV) ЧИСТПЗ(норма;платежи;даты) современная

стоимость потока

ЧИСТВНДОХ (XIRR) ЧИСТВНДОХ(платежи;даты внутренняя норма

[;прогноз]) доходности

Сделаем ряд важных замечаний, касающихся применения этих функ-

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

ми произвольной величины, осущ ествляемыми через равные промежутки

времени.

10.8. Используем Excel 253

Функция НПЗ вычисляет значение современной стоимости потока.

Для того чтобы вычислить значение чист о й современной стоимости по-

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

мо сделать две вещи: не включать первоначальные инвестиции (сделан-

ные в момент 0) в поток платежей, и вычесть величину первоначальных

инвестиций из полученного значения функции НПЗ.

Полный аналог функции НПЗ, функция ЧИСТПЗ, вычисляет чистую

современную стоимость потока платежей, осуществляемых через про-

извольные промежутки времени.

Функции ВНДОХ и ЧИСТВНДОХ имеют необязательный аргумент

прогноз, который может использоваться для указания предполагаемого

значения вычисляемой процентной ставки. По у молчанию значение этого

аргумента полагается равным 10%. Вычисление значений рассматривае-

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

Если после 20 итераций не достигнута требуемая точность результата, то в

ячейке появляется сообщение об ошибке: # ЧИСЛО!. В этом случае стоит

попытаться найти решение при другом значении аргумента прогноз.

Применение функций ЧИСТНЗ и ЧИСТВНДОХ требует указания пред-

полагаемых дат платежей (аргумент даты). Информация о способах за-

писи дат в Excel приведена в Приложении А.

Чтобы расширить знания читателя об Excel, в этом пункте будет рас-

сказано о трех новых средствах, которые можно использовать при реше-

нии задач из данного и предыдущих разделов: параметрические таблицы,

диаграммы, команда Подбор параметра.

10.8.1. Используем параметрическую таблицу

Опишем решение двух ранее разобранных примеров из этого разде-

ла с помощью Excel. Начнем с примера 10.6. Фрагмент рабочего листа с

решением этого примера приведен на рис. 16. Его оформление и запись

данных выполняем, как в ранее разобранных примерах.

Для получения результирующей таблицы, в которой отражена зави-

симость чистой современной ценности NP V от ставки дисконтирования

r, можно полностью (с точностью до используемой функции) повторить

действия, выполненные при решении примера 4.2. Используем при фор-

мировании таблицы результатов специальный механизм, кото рый назы-

вается параметрическая таблица.

Выполним сначала подготовительные действия. Скопируем интервал

B5:B9 (значения ставки процента) в интервал A23:A27. Введем в ячейку

254 10. Инвестиции

Рис. 16. Решение примера 10.6

10.8. Используем Excel 255

Рис. 17. Решение примера 10.7

256 10. Инвестиции

B23 формулу:

= $B$10+НПЗ(A23;$B$11;$B$12;$B$13;$B$14;$B$15;)

Обратите внимание, что формула ссылается на ячейку A23, в которой за-

писано первое значение параметра r. Далее выполняем следующие дей-

ствия:

1. Образуем интервал A23:B27.

2. Выбираем меню Данные.

3. Выбираем команду Таблица подстановки. . .

4. В диалоговом окне Таблица подстановки заполняем поле:

Подставлять значения по строкам в: $A$23

5. Нажимаем кнопку OK.

Еще раз обращаем ваше внимание на то, что названия меню, команд и

полей приведены для Excel 97. Например, в Excel 5.0 использованная выше

команда называлась не Таблица подстановки, а Таблица, и заполнять надо

было поле Ячейка ввода столбца. Нет никакой гарантии, что в следующей

версии Excel не произойдут подобные изменения, поэтому полезно понять

и запомнить логику построения параметрических таблиц.

Сделайте активной любую ячейку из интервала A 23:B27, и вы увидите,

что в каждой из них записана формула:

{=ТАБЛИЦА(;A23)}

Она заключена в фигурные скобки, так как является формулой массива.

Используемая в формуле функция ТА БЛИЦА имеет два аргумента, но

первый аргумент (ссылка_строки) опущен, на что указывает знак <;>.

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

столбца.

Построив таблицу, вы можете вносить изменения в данные и в таблич-

ную формулу. Например, можно исключить из набора значений парамет-

ра r = 40% и добавить r = 25%.

Перейдем теперь к примеру 10.7, решение которого приведено на рис. 17.

Оно выполнено с применением тех же средств, что и решение примера

10.6, поэтому мы не будем его комментировать.

Записанные в итоговых таблицах результаты решения примеров 10.6

и 10.7 будут намного нагляднее, если их изобразить в виде графиков.

10.8. Используем Excel 257

В программе Excel имеется мощный механизм под названием Диаграммы

(Chart), который позволяет представлять данные с рабочих листов в гра-

фическом виде.

10.8.2. Строим диаграммы

Данные из рабочих листов можно представлять в виде самых разно-

образных диаграмм. Диаграммы могут строиться как на рабочих листах

с данными (внедренные диаграммы), так и на отдельных листах (листы

диаграмм). В этом подразделе мы разберем примеры получения только

внедренных диаграмм

В Excel почти весь процесс построения диаграммы автоматизирован.

Однако, необходимо потратить определенное время и проявить терпение,

чтобы ваши диаграммы стали выглядеть именно так, как вы задумали.

Продолжим решение примера 10.6 и представим зависимость NP V (r)

в графическом виде (рис. 18). Перед построением диаграммы выделим ин-

тервал ячеек, в котором расположены данные для диаграммы (категория

и хотя бы один ряд данных с их названиями), A3:B8. Далее запустим Ма-

стер диаграмм, нажав одноименную кнопку на панели инструментов, или,

выполнив следующие действия:

1. Выбираем меню Вставка.

2. Выбираем подменю Диаграмма. . .

3. Нажимаем кнопку OK.

После этого на экране появляется диалоговое окно первого шага Ма-

стер диаграмм. Процесс создания диаграммы состоит из четырех шагов.

После каждого шага можно перейти к следующему (кнопка Далее>) или

вернуться к предыдущему шагу (кнопка <Назад ). Кнопка Готово поз-

воляет пропустить оставшиеся шаги, а кнопка Отмена — отказаться от

построения диаграммы.

На первом шаге Мастер диаграмм предлагает выбрать тип диаграм-

мы. В случае построения графика функции, надо выбрать тип Точечная.

Именно этот тип обеспечивает построение графиков функций в тради-

ционном математическом понимании. На этом же шаге дополнительно

требуется указать вид графика.

Листы диаграмм имеют некоторые преимущества перед внедренными диаграмма-

ми, в частности, их легче редактировать.

258 10. Инвестиции

Рис. 18. Построение диаграммы для примера 10.6

10.8. Используем Excel 259

На втором шаге устанавливается источник данных. Так как мы за-

ранее выделили область с данными, и данные расположены в столбцах,

то в рассматриваемом примере нужно просто нажать кнопку Далее>. Вы-

деленная область с данными содержит две ячейки с текстом (названия

столбцов), которые используются для оформления диаграммы. Название,

связанное со значением функции, считается именем это ряда данных и по

умолчанию используется в легенде диаграммы.

Третий шаг служит для задания параметров диаграммы. Диалоговое

окно этого шага имеет несколько вкладок. С их помощью мы задали на-

звание диаграммы (График NP V (r)), заголовки осей (r, NP V ), убрали

легенду.

В рассматриваемом примере четвертый шаг (Размещение диаграммы)

можно пропустить, так как по умолчанию размещение происходит на име-

ющемся листе. Диаграмма для примера 10.6 построена.

После выполнения четвертого шага на экране появляется диаграмма,

размеры и место размещения которой определяются программой. Однако,

вы можете разместить ее, где хотите, даже поверх данных или другой

диаграммы. Для этого поместите на контур диаграммы указатель мыши,

нажмите левую кнопку и перетащите диаграмму в ну жное место. Чтобы

изменить размеры диаграммы, сначала выделите ее, а затем перетащите

один из размещенных по ее периметру маркеров в нужное место.

Начиная со второго шага, в диалоговом окне показано, как будет вы-

глядеть ваша диаграмма при выбранных параметрах. Если вас что-то не

устраивает, всегда есть возможность вернуться назад (кнопка <Назад) и

поменять параметры. Можно также заняться редактированием диаграм-

мы после того, как она построена.

Построение диаграммы для примера 10.7 (рис. 19) выполнено по той

же схеме. Так как на диаграмме изображены два графика (ряда данных),

мы оставили и разместили под диаграммой ее легенду, в которой для каж-

дого ряда указан его маркер.

Из подраздела 10.5 мы уже знаем, что важной характеристикой ин-

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

Далее мы покажем, как организовать ее вычисление, используя команду

Подбор параметра.

10.8.3. Объясняем команду Подбор параметра

В меню Сервис имеется команда Подбор параметра, с помощью которой

можно находить корни, вообще говоря, сколь угодно сложных уравнений.

260 10. Инвестиции

Рис. 19. Построение диаграммы для примера 10.7

10.8. Используем Excel 261

Существенным моментом при применении этой команды является то, что

требуется знать приближенное значение корня, который требуется найти.

Эта команда, как следует из ее названия, предназначена для полу-

чения заданного значения в целевой ячейке путем подбора значения в

ячейке-параметре. Таким образом, требуется указать целевую ячейку, ее

желаемое значение и изменяемую ячейку-параметр, влияющую на содер-

жимое целевой ячейки. При этом ячейка-параметр должна содержать зна-

чение, а не формулу.

Пример 10.13. В условиях примера 10.6 найти значение параметра r,

при котором проект принесет доход, современная ценность которого равна

100 000 руб.

Решение . Рабочий лист с решением этого примера приведен на рис. 20.

Подробно опишем процесс его получения.

Сначала выполним подготовительные действия. Во-первых, в ячейку

A18 запишем значение, являющееся первым приближением к тому зна-

чению r, которое необходимо определить. Если посмотреть на рис. 16, то

становится ясно (из монотонности функции NP V (r)), что искомое зна-

чение параметра удовлетворяет неравенству 0% < r < 10%. Поэтому в

ячейку A18 было записано число 5%. Этого уже не увидеть на рабочем

листе, так как потом в эту ячейку было программой записано найденное

значение параметра r.

Во-вторых, в ячейку B18 записываем формулу вычисления NP V :

= $B$5+НПЗ(A18;$B$6;$B$7;$B$8;$B$9;$B$10)

После ввода этой формулы в ячейке B18 появится число 105623.22р. Под-

готовка к основным вычислениям закончена.

Далее выполняем следующую последовательность действий:

1. Выбираем меню Сервис.

2. Выбираем команду Подбор параметра. . .

3. В диалоговом окне Подбор параметра заполняем поля:

Установить в ячейке: B18

Значение: 100000

Изменяя значение в ячейке: A18

4. Нажимаем кнопку OK.