Как избежать ошибок при расчете чистого дисконтированного дохода в Excel

9 апреля 2015

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

Как автоматически вычислить чистый дисконтированный доход в Excel

Чтобы определить чистый дисконтированный доход с помощью Excel, сначала нужно подготовить специальную модель – в ней отразить планируемое движение денежных средств по основной, финансовой и инвестиционной деятельности (подробнее об этом см. Как составить бюджет движения денежных средств инвестиционного проекта). Далее определить ставку дисконтирования (о том, как это сделать, см. Как определить ставку дисконтирования для оценки инвестиционного проекта).

Теперь можно воспользоваться стандартной функцией Excel ЧПС(ставка, значение1, значение2,…), где:

Например, инвестиционный проект компании «Альфа» представлен таблицей на рисунке 1 – начинается в начале I квартала 2014 года, а заканчивается в I квартале 2017-го. Исходная информация детализирована поквартально без учета инфляции. Известно, что ставка дисконтирования составляет 21 процент годовых, то есть 4,88 процента поквартально (рассчитывается по формуле).

К этой таблице необходимо добавить еще две строки (см. пример на рисунке 2). В диапазоне ячеек B24:N24 указать квартальную ставку дисконтирования, а в ячейку N25 ввести формулу: =ЧПС(B24;B22:N22), где:

Согласно расчету функцией ЧПС() чистый дисконтированный доход инвестиционного проекта компании «Альфа» равен 25 233 082 руб. Чтобы убедиться, что эти данные верны, смоделируем формулу вручную.

Как вручную рассчитать чистый дисконтированный доход в Excel

Помимо использования стандартной функции Excel ЧПС(), чистый дисконтированный доход можно вычислить, задав расчетную формулу вручную (подробнее о его расчете см. Как определить чистый дисконтированный доход инвестиционного проекта). В качестве исходных данных потребуются цифры исходной прогнозной модели движения денежных средств по проекту, пример которой представлен в таблице на рисунке 1.

Формула. Расчет чистого дисконтированного дохода

В исходных данных поступления и платежи были представлены без учета инфляции. Функция ЧПС() не может включить ее в свои вычисления, но при ручном счете ставку дисконтирования можно скорректировать на инфляцию без труда. Для этого необходимо перевести номинальную ставку дисконтирования, которая использовалась ранее, в реальную, применив формулу расчета реальной ставки дисконтирования. К модели исходной таблицы добавляем строчки, где указываем инфляцию по кварталам и результаты корректировки ставки дисконтирования (см. пример на рисунке 3).

Теперь, чтобы воспроизвести в модели инвестиционного проекта компании «Альфа» чистый дисконтированный доход, к модели проекта, представленной на рисунке 1, нужно добавить еще три строчки и назвать их:

В каждой строчке будет стоять своя формула (см. пример на рисунке 4).

В строке с коэффициентом дисконтирования рассчитывается знаменатель формулы для чистого дисконтированного дохода. В ячейке В31 будет стоять единица – это значение соответствует первому периоду, ведь, как правило, большая часть инвестиций осуществляется вначале. Далее в ячейке С31 и последующих нужно записать формулу: =B31*(1+B30), где:

Как видно из рисунка 2, полученное значение равно 31 288 574 руб. и отличается от предыдущего, найденного по стандартной функции ЧПС(). Эта разница возникает из-за того, что стандартная функция дисконтирует в том числе и денежный поток первого периода, а также не учитывает инфляцию (т. е. требует источника данных с «зашитой» внутри инфляцией). Поэтому для расчета чистого денежного дохода стандартную функцию лучше не применять, однако ее можно использовать, например, при определении внутренней нормы рентабельности.

Елена Исаева , независимый консультант

Если предполагается, что периоды между выплатами и поступления по инвестиционному проекту могут быть не равными (например, один период равен кварталу, следующий – месяцу, далее – двум месяцам и т. д.), для точного расчета чистого дисконтированного дохода (NPV) стоит использовать функцию ЧИСТНЗ(ставка;значения;даты). Все аргументы этой функции – обязательные:

Для расчета внутренней нормы доходности (IRR) проекта с неравными периодами между выплатами (поступлениями) стоит применять функцию ЧИСТВНДОХ (значения; даты; [предп]). Ее аргументы «значения» и «даты» соответствуют аргументам функции ЧИСТНЗ. Аргумент «предп» необязательный, он обозначает величину, предположительно близкую к результату ЧИСТВНДОХ.

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

Иллюстрирует рекомендации следующий пример.

В таблице представлены денежные потоки проекта в детализации по кварталам. Ставка дисконтирования – 21 процент.

Если рассчитывать показатели NPV и IRR c использованием функций ЧПС и ВСД соответственно, то первый из них равен 225 544 198 руб., второй – 83,2 процента.

Если применять функции ЧИСТНЗ и ЧИСТВНДОХ (денежные потоки – в детализации по кварталам), NPV составляет 236 117 969 руб., IRR – 82,8 процента.

А если рассчитывать эти показатели с помощью функции ЧИСТНЗ и ЧИСТВНДОХ, но денежные потоки указать в детализации по датам, получим, что NPV равен 238 488 296 руб., IRR – 84,2 процента.

Школа

Школа

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

Записаться

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

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

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

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

А еще...




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

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

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


  • Мы в соцсетях