Расчет IRR в Excel с помощью функций и графика

IRR (Internal Rate of Return), или ВНД – показатель внутренней нормы доходности инвестиционного проекта. Часто применяется для сопоставления различных предложений по перспективе роста и доходности. Чем выше IRR, тем большие перспективы роста у данного проекта. Рассчитаем процентную ставку ВНД в Excel.

Экономический смысл показателя

Другие наименования: внутренняя норма рентабельности (прибыли, дисконта), внутренний коэффициент окупаемости (эффективности), внутренняя норма.

Коэффициент IRR показывает минимальный уровень доходности инвестиционного проекта. По-другому: это процентная ставка, при которой чистый дисконтированный доход равен нулю.

Формула для расчета показателя вручную:

, где

CFt – денежный поток за определенный промежуток времени t;
IC – вложения в проект на этапе вступления (запуска);
t – временной период.

На практике нередко коэффициент IRR сравнивают со средневзвешенной стоимостью капитала:

ВНД выше – следует внимательно рассмотреть данный проект.
ВНД ниже – нецелесообразно вкладывать средства в развитие проекта.
Показатели равны – минимально допустимый уровень (предприятие нуждается в корректировке движения денежных средств).

Часто IRR сравнивают в процентами по банковскому депозиту. Если проценты по вкладу выше, то лучше поискать другой инвестиционный проект.

Пример расчета IRR в Excel

Быстро рассчитать IRR можно с помощью встроенной функции ВСД. Синтаксис:

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

Возьмем условные цифры:

Первоначальные затраты составили 150 000, поэтому это числовое значение вошло в таблицу со знаком «минус». Теперь найдем IRR. Формула расчета в Excel:

Расчеты показали, что внутренняя норма доходности инвестиционного проекта составляет 11%. Для дальнейшего анализа значение сравнивается с процентной ставкой банковского вклада, или стоимостью капитала данного проекта, или ВНД другого инвестиционного проекта.

Мы рассчитали ВНД для регулярных поступлений денежных средств. При несистематических поступлениях использовать функцию ВСД невозможно, т.к. ставка дисконтирования для каждого денежного потока будет меняться. Решим задачу с помощью функции ЧИСТВНДОХ.

Модифицируем таблицу с исходными данными для примера:

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

значения – денежные потоки;
даты – массив дат в соответствующем формате.

Формула расчета IRR для несистематических платежей:

Существенный недостаток двух предыдущих функций – нереалистичное предположение о ставке реинвестирования. Для корректного учета предположения о реинвестировании рекомендуется использовать функцию МВСД.

Аргументы:

значения – платежи;
ставка финансирования – проценты, выплачиваемые за средства в обороте;
ставка реинвестирования.

Предположим, что норма дисконта – 10%. Имеется возможность реинвестирования получаемых доходов по ставке 7% годовых. Рассчитаем модифицированную внутреннюю норму доходности:

Полученная норма прибыли в три раза меньше предыдущего результата. И ниже ставки финансирования. Поэтому прибыльность данного проекта сомнительна.

Графический метод расчета IRR в Excel

Значение IRR можно найти графическим способом, построив график зависимости чистой приведенной стоимости (NPV) от ставки дисконтирования. NPV – один из методов оценки инвестиционного проекта, который основывается на методологии дисконтирования денежных потоков.

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

Для расчета NPV в Excel можно использовать функцию ЧПС:

Так как первый денежный поток происходил в нулевом периоде, то в массив значений он не должен войти. Первоначальную инвестицию нужно прибавить к значению, рассчитанному функцией ЧПС.

Функция дисконтировала денежные потоки 1-4 периодов по ставке 10% (0,10). При анализе нового инвестиционного проекта точно определить ставку дисконтирования и все денежные потоки невозможно. Имеет смысл посмотреть зависимость NPV от этих показателей. В частности, от стоимости капитала (ставки дисконта).

Рассчитаем NPV для разных ставок дисконтирования:

Посмотрим результаты на графике:

Напомним, что IRR – это ставка дисконтирования, при которой NPV анализируемого проекта равняется нулю. Следовательно, точка пересечения графика NPV с осью абсцисс и есть внутренняя доходность предприятия.