Функция Excel MIRR для расчета модифицированной внутренней нормы доходности
В учебнике объясняются основы модифицированной внутренней нормы доходности, чем она отличается от внутренней нормы доходности и как рассчитать MIRR в Excel.
В течение многих лет финансовые эксперты и учебники предупреждали об изъянах и недостатках внутренней нормы прибыли, но многие руководители продолжают использовать ее для оценки капитальных проектов. Им нравится жить на грани или они просто не знают о существовании MIRR? Хотя это и не идеально, измененная внутренняя норма доходности решает две основные проблемы с IRR и обеспечивает более реалистичную оценку проекта. Итак, встречайте функцию Excel MIRR, которая сегодня у нас в гостях!
Что такое МИРР?
Модифицированная внутренняя норма доходности (MIRR) — это финансовый показатель для оценки прибыльности проекта и ранжирования инвестиций одинакового размера. Как следует из названия, MIRR представляет собой модифицированную версию традиционной внутренней нормы доходности, которая направлена на преодоление некоторых недостатков IRR.
Технически MIRR представляет собой норму прибыли, при которой чистая текущая стоимость (NPV) притока терминала равна инвестициям (т.е. оттоку); тогда как IRR – это ставка, которая делает NPV нулевой.
IRR подразумевает, что все положительные денежные потоки реинвестируются по собственной норме доходности проекта, в то время как MIRR позволяет указать другую ставку реинвестирования для будущих денежных потоков. Для получения дополнительной информации см. МИРР против. IRR.
Как вы интерпретируете курс, возвращаемый MIRR? Как и в случае с IRR, чем больше, тем лучше 🙂 В ситуации, когда единственным критерием является модифицированная внутренняя норма доходности, правило принятия решения очень простое: проект может быть принят, если его MIRR больше стоимости капитала (барьерная ставка). и отклоняется, если ставка ниже стоимости капитала.
Функция Excel MIRR
Функция MIRR в Excel вычисляет модифицированную внутреннюю норму доходности для ряда денежных потоков, происходящих через равные промежутки времени.
Синтаксис функции MIRR следующий:
MIRR(значения, ставка_финансирования, ставка_реинвестирования)
Где:
- Значения (обязательно) — массив или диапазон ячеек, содержащий денежные потоки.
- Finance_rate (обязательно) — процентная ставка, которая выплачивается для финансирования инвестиции. Другими словами, это стоимость заимствования в случае отрицательного денежного потока. Должен быть указан в процентах или соответствующем десятичном числе.
- Reinvest_rate (обязательно) — совокупная норма прибыли, при которой положительные денежные потоки реинвестируются. Он предоставляется в процентах или десятичном числе.
Функция MIRR доступна в Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 и Excel 2007.
5 вещей, которые вы должны знать о MIRR в Excel
Прежде чем приступить к расчету измененной IRR в таблицах Excel, вот список полезных моментов, которые следует запомнить:
- Значения должны содержать как минимум одно положительное (представляющее доход) и одно отрицательное (представляющее расходы) число; иначе #DIV/0! возникает ошибка.
- Функция Excel MIRR предполагает, что все денежные потоки происходят через равные промежутки времени, и использует порядок значений для определения порядка денежных потоков. Поэтому обязательно вводите значения в хронологическом порядке.
- Неявно подразумевается, что все денежные потоки происходят в конце периода.
- Обрабатываются только числовые значения. Текст, логические значения и пустые ячейки игнорируются; однако обрабатываются нулевые значения.
- Обычный подход заключается в использовании средневзвешенной стоимости капитала в качестве реинвест_ставкано вы можете ввести любую ставку реинвестирования, которую считаете подходящей.
Как рассчитать MIRR в Excel – пример формулы
Расчет MIRR в Excel очень прост — вы просто указываете денежные потоки, стоимость займа и ставку реинвестирования в соответствующие аргументы.
В качестве примера найдем модифицированную внутреннюю норму доходности для ряда денежных потоков в A2:A8, ставку финансирования в D1 и ставку реинвестирования в D2. Формула проста:
=ЗЕРК.(A2:A8,D1,D2)
Кончик. Если результат отображается в виде десятичного числа, установите процентный формат для ячейки формулы.
Шаблон Excel МИРР
Чтобы быстро оценить разные проекты разного размера, давайте создадим шаблон MIRR. Вот как:
- Для значений денежного потока создайте динамически определенный диапазон на основе этой формулы:
=СМЕЩЕНИЕ(Лист1!$A$2,0,0,COUNT(Лист1!$A:$A),1)
Где Лист1 — это название вашего рабочего листа, а A2 — это первоначальные инвестиции (первый денежный поток).
Назовите приведенную выше формулу как хотите, скажем Ценности.
Подробные инструкции см. в разделе Как создать динамический именованный диапазон в Excel.
- При желании назовите ячейки, содержащие ставки финансирования и реинвестирования. Чтобы назвать ячейку, вы можете использовать любой из методов, описанных в разделе Как определить имя в Excel. Обратите внимание, что давать имена этим ячейкам необязательно, подойдут и обычные ссылки.
- Введите определенные имена, которые вы создали, в формулу MIRR.
Для этого примера я создал следующие имена:
- Ценности – формула OFFSET, описанная выше
- Финанс_ставка – ячейка D1
- Реинвест_ставка – ячейка D2
Итак, наша формула MIRR принимает следующий вид:
=MIRR(Значения, Финансовая_ставка, Реинвест_ставка)
И теперь вы можете ввести любое количество значений в столбец А, начиная с ячейки А2, и ваш калькулятор MIRR с динамической формулой сразу выдаст результат:
Заметки:
- Для корректной работы шаблона Excel MIRR значения должны быть введены в соседние ячейки без пробелов.
- Если ячейки финансовой ставки и ставки реинвестирования пусты, Excel предполагает, что они равны нулю.
MIRR против IRR: что лучше?
Хотя теоретическая основа MIRR все еще оспаривается учеными-финансистами, в целом она считается более действенной альтернативой IRR. Если вы не уверены, какой метод дает более точные результаты, в качестве компромисса вы можете вычислить оба, учитывая следующие ограничения.
Ограничения внутренней нормы доходности
Хотя IRR является общепринятой мерой инвестиционной привлекательности, она имеет несколько неотъемлемых проблем. И MIRR решает две из них:
1. Ставка реинвестирования
Функция Excel IRR работает в предположении, что промежуточные денежные потоки реинвестируются с доходностью, равной самой IRR. Загвоздка в том, что в реальной жизни, во-первых, ставка реинвестирования, как правило, ниже ставки финансирования и ближе к стоимости капитала компании, а, во-вторых, ставка дисконтирования может существенно меняться с течением времени. В результате IRR часто дает чрезмерно оптимистичный взгляд на потенциал проекта.
MIRR более точно отражает прибыльность инвестиций, поскольку учитывает как ставку финансирования, так и ставку реинвестирования и позволяет изменять ожидаемую норму прибыли от этапа к этапу в долгосрочном проекте.
2. Несколько решений
В случае чередования положительных и отрицательных значений (т.е. если ряд денежных потоков меняет знак более одного раза), IRR может давать несколько решений для одного и того же проекта, что приводит к неопределенности и путанице. MIRR предназначен для поиска только одного значения, устраняя проблему с несколькими IRR.
Ограничения MIRR
Некоторые финансовые эксперты считают показатель доходности, полученный с помощью MIRR, менее надежным, поскольку прибыль проекта не всегда полностью реинвестируется. Однако вы можете легко компенсировать частичные инвестиции, регулируя ставку реинвестирования. Например, если вы ожидаете, что реинвестирование принесет 6%, но, вероятно, будет реинвестирована только половина денежных потоков, используйте реинвест_ставка 3%.
Функция MIRR не работает
Если ваша формула MIRR в Excel приводит к ошибке, необходимо проверить два основных момента:
- #ДЕЛ/0! ошибка. Возникает, если ценности аргумент не содержит хотя бы одного отрицательного и одного положительного значения.
- #ЦЕННОСТЬ! ошибка. Возникает, если финанс_ставка или же реинвест_ставка аргумент не числовой.
Вот как можно использовать MIRR в Excel, чтобы найти модифицированную норму прибыли. Для практики вы можете скачать наш образец рабочей тетради на Расчет MIRR в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!