Как посчитать IRR в Excel

Способ 1: Функция ВСД для регулярных денежных потоков

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

Представим инвестиционный проект с начальными вложениями 100 000 рублей и последующими поступлениями: 30 000, 35 000, 40 000 и 25 000 рублей за четыре периода. Разместите эти данные в столбце, где первое значение (инвестиции) обязательно должно быть отрицательным, поскольку оно представляет расход средств.

1. Выделите ячейку, в которой должен отобразиться результат расчета IRR, и начните вводить формулу. Наберите =ВСД( и укажите диапазон ячеек с денежными потоками, например =ВСД(A1:A5).

2. Функция ВСД принимает два аргумента: диапазон значений (обязательный) и предположение (необязательный). Диапазон должен содержать как минимум одно отрицательное и одно положительное значение. Аргумент «Предположение» можно опустить — Excel автоматически начнет подбор с 10% (0,1).

3. После нажатия клавиши Enter функция выполнит итеративные вычисления и вернет значение в десятичном формате, например 0,115425. Чтобы преобразовать его в проценты, примените процентный формат ячейки через контекстное меню или вкладку «Главная» — «Число» — «Процентный формат».

4. Если функция возвращает ошибку #ЧИСЛО!, это означает, что Excel не смог найти решение за 20 попыток. В таком случае добавьте второй аргумент — предположительное значение, близкое к ожидаемому результату: =ВСД(A1:A5; 0,15).

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

Способ 2: ЧИСТВНДОХ для нерегулярных платежей

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

Предположим, вы сделали начальные инвестиции 15 марта 2024 года в размере 80 000 рублей, затем получили доход 25 000 рублей 20 июня 2024 года, 30 000 рублей 15 октября 2024 года и 35 000 рублей 10 февраля 2025 года. Расположите суммы в одном столбце, а соответствующие даты — в соседнем.

1. Выделите ячейку для результата и введите формулу =ЧИСТВНДОХ(A1:A4; B1:B4), где первый диапазон содержит денежные суммы, а второй — соответствующие даты платежей.

2. Функция принимает три аргумента: значения (обязательный), даты (обязательный) и предположение (необязательный). Убедитесь, что первая дата соответствует отрицательному денежному потоку (начальным инвестициям), а последующие даты расположены в хронологическом порядке.

3. Важно правильно отформатировать ячейки с датами. Если Excel не распознает даты, функция вернет ошибку #ЗНАЧ!. Установите для столбца с датами формат «Дата» через вкладку «Главная» — «Число».

4. ЧИСТВНДОХ рассчитывает годовую ставку доходности с учетом фактического количества дней между платежами. Для нашего примера результат составит примерно 19-22% годовых, что точнее отражает реальную доходность проекта по сравнению с упрощенным расчетом через ВСД.

5. При работе с длинными периодами или большим количеством нерегулярных платежей рекомендуется указывать аргумент «Предположение», например =ЧИСТВНДОХ(A1:A4; B1:B4; 0,1), чтобы ускорить вычисления и избежать ошибок подбора.

Способ 3: Расчет модифицированной внутренней нормы доходности через МВСД

Функция МВСД вычисляет модифицированную внутреннюю норму доходности, учитывая разные процентные ставки для финансирования проекта и реинвестирования получаемых доходов. Такой подход дает более реалистичную оценку эффективности инвестиций, поскольку на практике стоимость привлечения капитала и доходность реинвестирования редко совпадают с IRR проекта.

Используем те же данные: начальные инвестиции 100 000 рублей (отрицательное значение) и четыре поступления 30 000, 35 000, 40 000 и 25 000 рублей. Дополнительно потребуется определить две ставки: для финансирования проекта (например, 8% — стоимость кредита) и для реинвестирования доходов (например, 6% — доходность банковского вклада).

1. Выделите ячейку для результата и введите формулу =МВСД(A1:A5; 0,08; 0,06), где первый аргумент — диапазон денежных потоков, второй — ставка финансирования, третий — ставка реинвестирования.

2. Функция требует наличия как минимум одного положительного и одного отрицательного значения в диапазоне. Все три аргумента являются обязательными, в отличие от ВСД, где второй аргумент опционален.
3. МВСД автоматически определяет знак денежных потоков: отрицательные дисконтируются по ставке финансирования, положительные — по ставке реинвестирования. Такое разделение обеспечивает более точную оценку проекта.

4. Обратите внимание, что результат МВСД обычно отличается от значения ВСД, причем может быть как выше, так и ниже. Для нашего примера МВСД составит около 9-11%, что ниже классического IRR, поскольку учитывается консервативная ставка реинвестирования 6%.

5. Полученное значение сравните с требуемой нормой доходности инвестора. Если МВСД превышает этот показатель, проект можно считать привлекательным с учетом реальных условий финансирования и реинвестирования.

Способ 4: Графический метод определения IRR через построение зависимости NPV

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

Для построения графика возьмем знакомые данные: инвестиции 100 000 рублей и поступления 30 000, 35 000, 40 000 и 25 000 рублей. Создадим таблицу с различными ставками дисконтирования и соответствующими значениями NPV.

1. В отдельном столбце разместите ряд процентных ставок с шагом 2-5%, например: 0%, 5%, 10%, 15%, 20%, 25%. Эти значения послужат основой для расчета NPV при разных условиях дисконтирования.

2. В соседнем столбце для каждой ставки рассчитайте NPV, используя функцию =ЧПС(). Формула будет иметь вид =ЧПС(B1;A$2:A$5)+A$1, где B1 — ставка дисконтирования, A2:A5 — будущие денежные потоки, A1 — начальные инвестиции (добавляются отдельно, так как ЧПС дисконтирует все значения).

3. Выделите оба столбца с данными (B и C) и перейдите на вкладку «Вставка». Выберите точечную диаграмму с гладкими кривыми из группы «Диаграммы», чтобы построить график зависимости NPV от ставки дисконтирования.

4. На получившемся графике найдите точку пересечения кривой с горизонтальной осью (где NPV = 0). Соответствующее значение на оси X и будет приблизительным значением IRR. Для нашего примера эта точка окажется в районе 11-12%.

5. Для повышения точности графического метода увеличьте количество расчетных точек или уменьшите шаг между процентными ставками в области, близкой к пересечению с осью. Можно добавить дополнительные ставки: 11%, 11,5%, 12%, 12,5% и пересчитать NPV для них.

Способ 5: «Подбор параметра» для точного нахождения IRR

Инструмент «Подбор параметра» в Excel представляет альтернативный способ вычисления IRR без использования специализированных финансовых функций. Метод основан на автоматическом подборе значения ставки дисконтирования, при котором NPV проекта становится равной нулю, что соответствует определению внутренней нормы доходности.

Подготовьте таблицу с денежными потоками: 100 000 рублей (инвестиции), 30 000, 35 000, 40 000 и 25 000 рублей (доходы). Создайте отдельную ячейку для ставки дисконтирования и ячейку с формулой NPV, которая будет пересчитываться при изменении ставки.

1. В отдельной ячейке введите начальное предположительное значение ставки, например 10% (0,1). Ниже создайте формулу для расчета NPV: =ЧПС(B1; A2:A5) + A1, где B1 — ячейка со ставкой, A2:A5 — будущие потоки, A1 — инвестиции.

2. Откройте вкладку «Данные» на ленте инструментов и в группе «Работа с данными» или «Прогноз» найдите команду «Анализ «что если»». Разверните ее и выберите пункт «Подбор параметра».

3. В появившемся диалоговом окне заполните три поля: в «Установить в ячейке» укажите ячейку с формулой NPV, в «Значение» введите 0 (нулевую NPV), в «Изменяя значение ячейки» укажите ячейку со ставкой дисконтирования.

4. Нажмите «OK» для запуска подбора. Excel выполнит серию итераций и автоматически подберет такое значение ставки, при котором NPV максимально приблизится к нулю. Результат появится в ячейке со ставкой дисконтирования.
5. После завершения подбора появится информационное окно с сообщением о найденном решении. Полученное значение ставки и есть IRR проекта — для нашего примера это будет примерно 11,54%. Можете проверить результат, убедившись, что NPV действительно близка к нулю.

Решение типичных проблем при расчете IRR

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

• Ошибка #ЧИСЛО! при использовании ВСД. Возникает, когда функция не может найти решение за 20 итераций, что характерно для проектов с необычной структурой денежных потоков. Добавьте второй аргумент «Предположение» с ориентировочным значением IRR: =ВСД(A1:A10; 0,15). Если ошибка сохраняется, попробуйте разные начальные значения: 0,05, 0,2, 0,3.

• Множественные значения IRR. Проекты с чередующимися положительными и отрицательными денежными потоками могут иметь несколько математически корректных значений IRR. Используйте графический метод для визуализации всех возможных решений или примените МВСД, которая всегда дает единственное значение за счет разделения ставок финансирования и реинвестирования.
• Неправильный порядок денежных потоков. Функции ВСД и МВСД интерпретируют значения строго в порядке их размещения в диапазоне. Убедитесь, что первым идет начальное вложение (отрицательное), затем доходы в хронологической последовательности. Перестановка значений приведет к некорректному результату.

• Отсутствие отрицательных или положительных значений. Для корректного расчета IRR диапазон обязательно должен содержать как минимум одно отрицательное и одно положительное значение. Проверьте, что начальные инвестиции указаны с минусом, а доходы — с плюсом. Если все значения одного знака, функция вернет ошибку.
• Проблемы с форматом дат в ЧИСТВНДОХ. Функция требует, чтобы второй аргумент содержал корректные даты в формате Excel. Если ячейки с датами отформатированы как текст, примените формат «Дата» или используйте функцию ДАТА для явного создания дат: =ЧИСТВНДОХ(B1:B5; ДАТА(2024;3;15):ДАТА(2025;2;10)).