Расчет NPV и IRR в Excel

18440
Дерягина Ирина
финансовый директор Natural Resource Strategies Ltd.
Знать и понимать формулы NPV и IRR нужно, чтобы разобраться, как работают одноименные функции в Excel. Ведь с некорректной работой функций IRR и NPV при проведении расчетов в Excel могли столкнуться многие финансовые специалисты. Но дело не в том, что функции Excel IRR и NPV работают некорректно. Условия могли быть заданы неверно. Чтобы получить идеальный расчет, важно задать правильные условия и точно знать, в каких случаях и как функцию можно скорректировать, когда ее необходимо дополнить графической интерпретацией или вообще отказаться от ее использования, заменив другими инструментами. Ниже приведен пошаговый алгоритм работы этих функций в Excel, а в конце статьи приложение с расчетами.

NPV. ЧИСТАЯ ПРИВЕДЕННАЯ СТОИМОСТЬ

Чистая приведённая стоимость NPV (чистая текущая стоимость или чистый дисконтированный доход – название, принятое в международной практике для анализа инвестиционных проектов) - это сумма дисконтированных значений потока платежей, приведённых к сегодняшнему дню.

Показатель NPV представляет собой разницу между всеми денежными притоками и оттоками, приведёнными к текущему моменту времени (моменту оценки инвестиционного проекта). Он показывает величину денежных средств, которую инвестор ожидает получить от проекта, после того, как денежные притоки окупят его первоначальные инвестиционные затраты и периодические денежные оттоки, связанные с осуществлением проекта. Поскольку денежные платежи оцениваются с учётом их временной стоимости и рисков, NPV можно интерпретировать как стоимость, добавляемую проектом. Её также можно интерпретировать как общую прибыль инвестора.

Критерий принятия решения на основании этого показателя таков

  • Если NPV>0 проект принимается
  • Если NPV<0 проект отвергается
  • Если NPV=0 проект откладывается

Расчет NPV и IRR в Excel

На практике принято пользоваться классической формулой NPV.

формула NPV:

Расчет NPV и IRR в Excel

Формула расчета NPV (Excel) выглядит так:

Расчет NPV и IRR в Excel

Из формулы NPV очевидна логика расчета чистой приведенной стоимости:

1. Аргументы функции (ставка дисконтирования; потоки);

Расчет NPV и IRR в Excel

2. При использовании функции NPV Excel дисконтируются все потоки, включая первый (первоначальные инвестиции), относящийся, как правило, к текущей дате – по состоянию на которую принимается инвестиционное решение;

3. Временные интервалы денежных потоков CF должны быть равными (год, квартал, месяц и т.д.);

4. Ставка дисконтирования должна соответствовать периоду времени: годовая, полугодовая, квартальная.

Зная механизм расчета gNPV, через функцию Excel разберем схематично три варианта формирования потоков:

Вариант № 1. Все денежные потоки, начиная с первого, поступают в конце периода

Расчет NPV и IRR в Excel

Вариант № 2. Все денежные потоки поступают в середине периода

Расчет NPV и IRR в Excel

Вариант № 3. Первый денежный поток (первоначальные инвестиции) поступает в начале первого периода, а остальные формируются в конце периода

Расчет NPV и IRR в Excel

IRR. ВНУТРЕННЯЯ НОРМА РЕНТАБЕЛЬНОСТИ

IRR вычисляется через поиск такого значения этой ставки, при которой NPV в заданном периоде равен нулю. NPV рассчитывается на основании потока платежей, дисконтированного к сегодняшнему дню.

Расчет NPV и IRR в Excel

Особенности применения IRR

1. Аргументы функции (потоки, предполагаемое значение).

Расчет NPV и IRR в Excel

2. В результате вычисления будет получена ставка, соответствующая временному периоду, которую следует пересчитать в годовую величину;

3. Второй аргумент «Предположение» - не обязателен.

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

Расчет NPV и IRR в Excel

Сценарий 1

Расчет NPV и IRR в Excel

NPV, вычисленный «вручную» с применением классической формулы составляет 107 (t=0,1,2….10).

NPV, вычисленный через функцию NPV Excel, где в качестве аргументов фигурируют денежные потоки всего рассматриваемого периода (участвуют и инвестиции и выручка), отличается и составляет 97.

NPV, вычисленный с выделением первоначальной суммы инвестиций за рамки примененной ко всем остальным денежным потокам, функции NPV Excel, также, как и при расчете «вручную», равен 107.

Соответственно, NPV Excel работает корректно (при условии осуществления первоначальных инвестиций на старте проекта) только в том случае, когда сумма первоначальных инвестиций выносится за рамки рассматриваемой функции.

Внутренняя норма доходности (IRR), рассчитанная с применением IRR-Excel  составила 25,88% (ячейка С21).

Легко проверить полученное значение с помощью встроенного макроса Excel – «поиск решения» (Goal seek).

Расчет NPV и IRR в Excel

После нажатия кнопки «ОК» Excel запустит процесс итеративных вычислений, в результате чего ячейка С10 будет содержать найденное решение. Это значение совпадает с результатом, полученным путем применения функции IRR Excel.

Расчет NPV и IRR в Excel

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

Расчет NPV и IRR в Excel

NPV, вычисленный «вручную» с применением классической формулы составляет 146 (t=0,1,2….10).

NPV, вычисленный через функцию NPV Excel, где в качестве аргументов фигурируют денежные потоки всего рассматриваемого периода (участвуют и инвестиции и выручка), отличается и составляет 133.

NPV, вычисленный с выделением первоначальной суммы инвестиций за рамки примененной ко всем остальным денежным потокам (знакопеременного характера!), функции NPV Excel, равен значению, рассчитанному «вручную» - аналогично рассмотренному выше примеру, и составляет 146.

Итак, механизм работы функции NPV Excel полностью соответствует теории временной стоимости денег и все расчеты производятся исходя из того, что дисконтироваться должны все потоки (как положительные, так и отрицательные). О чем, собственно, и говорит help:

Расчет NPV и IRR в Excel

Анализируем IRR

Внутренняя норма доходности (IRR), рассчитанная с применением IRR-Excel  составила 25,88% (ячейка С21).

Аналогично рассмотренному выше сценарию 1, проверяем полученное значение с помощью встроенного макроса Excel – «поиск решения» (Goal seek) и одинаковые значения.

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

На основании полученных прогнозных значений показателей IRR и NPV, логично сделать вывод о том, что, при равной сумме первоначальных инвестиций (100), первый сценарий (NPV 107 и IRR 25,88%) намного уступает второму (NPV 146 и IRR 32,50%) по доходности. НО! Продолжим наш анализ рассматриваемых сценариев, обратившись к графической интерпретации наших сценариев, которая будет выглядеть следующим образом:

Расчет NPV и IRR в Excel

Итак, графики значений NPV при разных ставках дисконтирования подтвердили ранее сделанные выводы о превосходстве второго сценария.

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

Расчет NPV и IRR в Excel

Значение NPV по первому сценарию очевидно демонстрирует тот факт, что он более привлекателен.

Тот же самый вывод можно сделать, сравнив значения IRR сценариев (вычисленную с помощью функции IRR Excel или «подбора параметров»). Но! После построения графиков NPV мы видим, что, из-за чередования знаков денежных потоков (оттоки чередуются с притоками), функция по сценарию 2 ведет себя таким образом, что пересекает ось абсцисс в двух точках. NPV проекта в этих точках равен 0, соответственно делаем вывод о том, что функция IRR, в рассматриваемом сценарии, имеет два значения IRR₁ 5, 67% и IRR₂ 25%.

Вернувшись к задаче сравнения двух сценариев на основании  второго показателя – внутренней ставки доходности (IRR),  приходим к выводу, что такое сравнение, из-за поведения функции, некорректно.

Выводы:

  1. Функции Excel IRR и NPV работают корректно. В зависимости от временных особенностей формирования денежных потоков, в формулы вычисления этих показателей необходимо вносить изменения. Например, в случае осуществления первоначальных инвестиций на старте проекта, их сумму, при вычислении NPV, необходимо выносить за рамки NPV Excel, применимой ко всем остальным потокам – как положительным, так и отрицательным.
  2. Для анализа показателей IRR и NPV в финансовых моделях желательно строить графики зависимости чистой приведенной стоимости (NPV) и ставки дисконтирования. Этот способ позволяет устранить один из недостатков применения расчетов IRR для денежных потоков, которые меняют знак не один, а несколько раз.
  3. Использовать IRR желательно только как вспомогательный инструмент, но больше ориентироваться на NPV.

Вложенные файлы

Доступно только подписчикам
  • Расчет IRR и NPV.xlsx


Подписка на статьи

Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

Школа

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

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

Живое общение с редакцией

А еще...


Опрос

Вы планируете менять работу в новом году?

  • Да, планирую 36%
  • Подумываю об этом 26.4%
  • Нет, пока никаких перемен 28%
  • Это секрет! 9.6%
Другие опросы

Рассылка



© 2007–2016 ООО «Актион управление и финансы»

«Финансовый директор» — практический журнал по управлению финансами компании

Зарегистрировано Федеральной службой по надзору в сфере связи,
информационных технологий и массовых коммуникаций (Роскомнадзор)
Свидетельство о регистрации Эл №ФС77-43625 от 18.01.2011
Все права защищены. email: fd@fd.ru


  • Мы в соцсетях
×
Чтобы скачать файл, пожалуйста, зарегистрируйтесь

Сайт журнала «Финансовый директор» - это профессиональный ресурс для сотрудников финансовых служб и профессиональных управленцев.

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

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Зарегистрируйтесь на сайте,
чтобы продолжить чтение статьи

Еще Вы сможете бесплатно:
Скачать надстройку для Excel. Узнайте риск налоговой проверки в вашей компании
Прочитать книгу «Я – финансовый директор. Секреты профессии» (раздел «Книги»)

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль