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

Функция MIRR (Visual Basic для приложений)

Twitter LinkedIn Facebook Адрес электронной почты

• Статья
• 04/07/2023

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

Синтаксис

MIRR(values( ), finance_rate, reinvest_rate)

Функция MIRR использует следующие именованные аргументы:

Part	Описание
values( )	Обязательно. Массив с типом Double, определяющий значения денежного потока. Этот массив должен содержать по крайней мере одно отрицательное значение (платеж) и одно положительное значение (получение).
finance_rate	Обязательно. Значение типа Double, определяющее процентную ставку, представляющую собой стоимость финансирования.
reinvest_rate	Обязательно. Значение типа Double, определяющее процентную ставку прибыли, получаемую благодаря реинвестированию денежных средств.

Измененная внутренняя норма прибыли — это внутренняя норма прибыли для случая, когда для финансирования платежей и поступлений используются различные ставки. Функция MIRR учитывает как стоимость инвестиций (finance_rate), так и процентную ставку, получаемую от реинвестирования денежных средств (reinvest_rate).

Аргументыfinance_rate и reinvest_rate — это проценты, выраженные в виде десятичных значений. Например, 12 процентов представляется как 0,12.

Функция MIRR использует порядок значений в массиве для интерпретации порядка платежей и поступлений. Следите, чтобы значения платежей и поступлений указывались в правильном порядке.

Пример

В этом примере функция MIRR используется для возврата измененной внутренней нормы доходности для ряда денежных потоков, содержащихся в массиве

Values(). LoanAPR представляет проценты по финансированию и InvAPR процентную ставку, полученную при реинвестировании.

Dim LoanAPR, InvAPR, Fmt, RetRate, Msg
Static Values(5) As Double    ' Set up array.
LoanAPR = . 1    ' Loan rate.
InvAPR = .12    ' Reinvestment rate.
Fmt = "#0.00"    ' Define money format.
Values(0) = -70000    ' Business start-up costs.
' Positive cash flows reflecting income for four successive years.
Values(1) = 22000 : Values(2) = 25000
Values(3) = 28000 : Values(4) = 31000
RetRate = MIRR(Values(), LoanAPR, InvAPR)    ' Calculate internal rate.
Msg = "The modified internal rate of return for these five cash flows is"
Msg = Msg & Format(Abs(RetRate) * 100, Fmt) & "%."
MsgBox Msg    ' Display internal return rate.
        

См. также

• Функции (Visual Basic для приложений)

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Внутренняя Норма Доходности Irr, Internal Rate Of Return Формула И Пример Расчета В Excel

Содержание

• Пример Расчета Irr В Excel C Помощью Встроенной Функции
• Модифицированная Внутренняя Норма Доходности Mirr
• Пример Расчета Irr В Excel
• Расчет Irr В Excel С Помощью Функций И Графика
• Примеры Функции Всд В Excel
• Как Пользоваться Показателем Всд:
• Расчет Внутренней Нормы Доходности Irr На Примере В Excel
• Функция Всд В Excel И Пример Как Посчитать Irr

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

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

Пример Расчета Irr В Excel C Помощью Встроенной Функции

Допустим, для запуска проекта брался кредит в банке под 15% годовых. Расчет показал, что внутренняя норма доходности составила 20,9%. Диапазон ячеек, в которых содержатся числовые выражения денежных средств. Для данных сумм нужно посчитать внутреннюю норму доходности.

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

Модифицированная Внутренняя Норма Доходности Mirr

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

Во-вторых, возможность сравнения различных инвестиционных проектов с разным горизонтом инвестирования. В нашем примере расчет ВСД произведен для ежегодных потоков. Если нужно найти IRR для ежемесячных потоков сразу за несколько лет, лучше ввести аргумент «Предположение». Программа может не справиться с расчетом за 20 попыток – появится ошибка #ЧИСЛО!. В программе Excel для подсчета внутренней ставки доходности используется метод итераций (подбора). Формула производит циклические вычисления с того значения, которое указано в аргументе «Предположение».

Пример Расчета Irr В Excel

Один из методов оценки инвестиционных проектов – внутренняя норма доходности. Расчет в автоматическом режиме можно произвести с помощью функции ВСД в Excel. Она находит внутреннюю ставку доходности для ряда потоков денежных средств. Финансовые показатели должны быть представлены числовыми значениями.

Значения – диапазон с суммами денежных потоков, по которым необходимо рассчитать внутреннюю норму рентабельности. Полученная норма прибыли в три раза меньше предыдущего результата. Поэтому прибыльность данного проекта сомнительна. ВНД выше – следует внимательно рассмотреть данный проект. В-третьих, не способность отразить абсолютный размер полученных денежных средств от инвестиции.

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

• Один из методов оценки инвестиционных проектов – внутренняя норма доходности.
• После оптимизации программа заполнит нашу пустую ячейку значением ставки дисконтирования, при которой чистый дисконтированный доход равен нулю.
• Функция дисконтировала денежные потоки 1-4 периодов по ставке 10% .
• То есть денежные потоки должны вводится в таблицу в соответствии со временем их возникновения.
• Расчеты показали, что внутренняя норма доходности инвестиционного проекта составляет 11%.
• ВНД выше – следует внимательно рассмотреть данный проект.
• В программе Excel для подсчета внутренней ставки доходности используется метод итераций (подбора).

При данной ставке инвестор вернет вложенные первоначально средства. Инвестиции состоят из платежей (суммы со знаком «–») и доходов (со знаком «+»), которые происходят в одинаковые уроки форекса знания форекс по продолжительности временные промежутки. Внутренняя норма доходности равна ставке дисконтирования, при которой чистый дисконтированный доход отсутствует, то есть равен нулю.

Расчет Irr В Excel С Помощью Функций И Графика

Во-вторых, показатель IRR не отражает размер реинвестирования в проект (данный недостаток решен в модифицированной внутренней норме доходности MIRR). На рисунке ниже показан первоначальный вид для расчета IRR. Можно заметить, форекс брокер что ставка дисконтирования, используемая для расчета NPV, ссылается на ячейку, в которой нет данных (она принимается равной 0). Внутренняя норма доходности тесно связана с чистым дисконтированным доходном .

В нашем случае получилось 6%, результат полностью совпадает с расчетом по строенной формуле в Excel. Показатель используется для оценки привлекательности инвестиционного проекта или для сопоставительного анализа с другими проектами. Для этого IRR сравнивают с эффективной ставкой что такое форекс рынок дисконтирования, то есть с требуемым уровнем доходности проекта . За такой уровень на практике зачастую используют средневзвешенную стоимость капитала . Если значение IRR проекта выше стоимости капитала для предприятия, то данный инвестиционный проект нужно принять.

Примеры Функции Всд В Excel

Расчеты показали, что внутренняя норма доходности инвестиционного проекта составляет 11%. Для дальнейшего анализа значение сравнивается с процентной ставкой банковского вклада, или стоимостью капитала данного проекта, или ВНД другого инвестиционного проекта. Разберем такой показатель как внутренняя норма доходности инвестиционного проекта, определим экономический смысл и рассмотрим подробно пример его расчета с помощью Excel. Значение IRR можно найти графическим способом, построив график зависимости чистой приведенной стоимости от ставки дисконтирования. NPV – один из методов оценки инвестиционного проекта, который основывается на методологии дисконтирования денежных потоков. При нажатии в появившемся окне заполняем строки «Установить целевую ячейку» – это формула расчета NPV, далее выбираем значение данной ячейки равной 0.

Как Пользоваться Показателем Всд:

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

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

Функция Всд В Excel И Пример Как Посчитать Irr

В частности, от стоимости капитала (ставки дисконта). IRR , или ВНД – показатель внутренней нормы доходности инвестиционного проекта. Часто применяется для сопоставления различных предложений по перспективе роста и доходности. Чем выше IRR, тем большие перспективы роста у данного проекта. Для этого открываем в главном меню раздел «Данные» и в нем «Поиск решений». После оптимизации программа заполнит нашу пустую ячейку значением ставки дисконтирования, при которой чистый дисконтированный доход равен нулю.

Изменяемый параметр будет ячейка со значением внутренней нормы доходности . На рисунке ниже показан пример расчета с помощью надстройки «Поиск решений». Функция дисконтировала денежные потоки 1-4 периодов по ставке 10% . При анализе нового инвестиционного проекта точно определить ставку дисконтирования и все денежные потоки невозможно. Имеет смысл посмотреть зависимость NPV от этих показателей.

0/5

(0 Reviews)

Как рассчитать внутреннюю норму прибыли (IRR) в Excel

Что такое внутренняя норма прибыли (IRR)?

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

В Excel есть три функции для расчета внутренней нормы доходности, включая внутреннюю норму доходности (IRR), модифицированную внутреннюю норму доходности (MIRR) и внутреннюю норму доходности с периодами времени (XIRR).

Функция IRR вычисляет внутреннюю норму доходности для ряда денежных потоков, функция MIRR работает с процентными ставками для заимствования и инвестирования, а функция ЧВНП рассчитывает более точную внутреннюю норму доходности, поскольку она учитывает периоды времени.

Ключевые выводы

• Внутренняя норма доходности (IRR) – это ставка дисконтирования, обеспечивающая нулевую чистую стоимость будущих денежных потоков.

• Excel имеет три функции для расчета внутренней нормы прибыли.

• При использовании различных ставок реинвестирования заемных средств применяется модифицированная внутренняя норма доходности (MIRR).

• Функция ЧИСТВНДОХ учитывает разные периоды времени.

Как рассчитать IRR в Excel

Что такое чистая приведенная стоимость?

NPV – это разница между приведенной стоимостью притока денежных средств и приведенной стоимостью оттока денежных средств с течением времени.

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

В приведенном ниже примере при использовании ставки дисконтирования 20% инвестиция № 2 показывает более высокую доходность, чем инвестиция № 1. Если вместо этого выбрать ставку дисконтирования в размере 1 %, доходность инвестиции № 1 будет выше, чем у инвестиции № 2. Прибыльность часто зависит от последовательности и важности денежных потоков проекта и ставки дисконтирования, применяемой к этим денежным потокам.

Чем отличаются IRR и NPV

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

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

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

Расчет внутренней нормы доходности в Excel

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

В нашем примере внутренняя норма доходности инвестиции №1 составляет 48%, а для инвестиции №2 внутренняя норма доходности составляет 80%. Это означает, что в случае инвестиции № 1 с инвестицией в размере 2 000 долларов США в 2013 году инвестиции принесут годовой доход в размере 48 %. В случае инвестиции № 2 при вложении 1000 долларов США в 2013 году доход принесет годовой доход в размере 80%.

Если никакие параметры не введены, Excel начинает по-разному проверять значения IRR для введенного ряда денежных потоков и останавливается, как только выбрана ставка, которая сводит NPV к нулю. Если Excel не находит коэффициента, уменьшающего чистую приведенную стоимость до нуля, отображается ошибка «#ЧИСЛО».

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

На изображении ниже для инвестиции № 1 Excel не находит, что ставка NPV уменьшена до нуля, поэтому у нас нет внутренней нормы доходности.

На изображении ниже также показана инвестиция №2. Если второй параметр не используется в функции, Excel найдет IRR, равную -10%. С другой стороны, если используется второй параметр (т. е. = IRR ($ C $ 6: $ F $ 6, C12)), для этой инвестиции отображаются две IRR: -10% и 216%.

Если последовательность денежных потоков имеет только один денежный компонент с одним изменением знака (с + на — или — на +), инвестиции будут иметь уникальную внутреннюю норму доходности. Тем не менее, большинство инвестиций начинаются с отрицательного потока и серии положительных потоков по мере поступления первых инвестиций. Затем прибыль, как мы надеемся, спадает, как это было в нашем первом примере.

На изображении ниже мы вычисляем IRR. Для этого мы просто используем функцию Excel IRR:

Расчет MIRR в Excel

Когда компания использует разные ставки реинвестирования заемных средств, применяется модифицированная внутренняя норма доходности (MIRR).

На изображении ниже мы рассчитываем IRR инвестиции, как в предыдущем примере, но принимая во внимание, что компания займет деньги, чтобы вложить их в инвестиции (отрицательные денежные потоки) по ставке, отличной от ставки, по которой она будет реинвестировать. заработанные деньги (положительный денежный поток). Диапазон от C5 до E5 представляет диапазон денежных потоков инвестиции, а ячейки D10 и D11 представляют ставку по корпоративным облигациям и ставку по инвестициям. 9{\ гидроразрыва {1} {n-1}} -1 \ конец {выровнено} (NPV(частота, значения[отрицательные])×(1+частота)−NPV(rставка, значения[положительные])×(1+rставка)n​)n−11​−1​

Вычисление XIRR в Excel

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

В приведенном ниже примере денежные потоки не выплачиваются в одно и то же время каждый год, как в приведенных выше примерах. Скорее, они происходят в разные периоды. Мы используем функцию ЧИСТВНР ниже, чтобы решить этот расчет. Сначала мы выбираем диапазон денежных потоков (от C5 до E5), а затем выбираем диапазон дат реализации денежных потоков (от C32 до E32).

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

Расчет IRR (внутренняя норма доходности) в Excel

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

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

Что такое IRR?

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

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

Термин «внутренний» означает, что IRR учитывает только внутренние факторы; внешние факторы, такие как инфляция, стоимость капитала и различные финансовые риски, исключаются из расчета.

Что показывает IRR?

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

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

Ограничения IRR

Хотя IRR является очень популярным методом оценки капитальных проектов, он имеет ряд недостатков, которые могут привести к неоптимальным решениям. Основные проблемы с IRR:

• Относительная мера . IRR учитывает процентное, а не абсолютное значение, в результате он может отдать предпочтение проекту с высокой нормой доходности, но очень небольшой стоимостью в долларах. На практике компании могут предпочесть крупный проект с более низкой IRR небольшому проекту с более высокой IRR. В этом отношении NPV является лучшим показателем, поскольку он учитывает фактическую сумму, полученную или потерянную в результате реализации проекта.
• Та же ставка реинвестирования . IRR предполагает, что все денежные потоки, генерируемые проектом, реинвестируются по ставке, равной самой IRR, что является очень нереалистичным сценарием. Эту проблему решает MIRR, который позволяет задавать разные ставки финансирования и реинвестирования.
• Несколько результатов . Для проектов с чередующимися положительными и отрицательными денежными потоками можно найти более одного IRR. Проблема также решена в MIRR, который предназначен для получения только одной ставки.

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

Расчет IRR в Excel

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

Если вы не очень хорошо знакомы с обозначениями суммирования, расширенная форма формулы IRR может быть проще для понимания:

Где:

• CF ₀ ​ — начальные инвестиции (представлены отрицательным числом)
• CF ₁ , CF ₂ … CF _n — денежные потоки
• i — номер периода
• n — всего периодов
• IRR — внутренняя норма доходности

Характер формулы таков, что не существует аналитического способа расчета внутренней нормы доходности. Мы должны использовать подход «угадай и проверь», чтобы найти его. Чтобы лучше понять концепцию внутренней нормы доходности, давайте выполним расчет IRR на очень простом примере.

Пример : Вы инвестируете 1000 долларов сейчас и получаете 500 долларов и 660 долларов в следующие 2 года. При какой ставке дисконтирования чистая текущая стоимость равна нулю?

В качестве нашего первого предположения, давайте попробуем ставку 8%:

• Сейчас: PV = -1000 долларов США
• Год 1: PV = 500 долл. США / (1+0,08) ¹ = 462,96 долл. США
• Год 2: PV = 660 долл. США / (1 + 0,08) ² = 565,84 долл. США

Суммируя их, мы получаем NPV , равную $28,81:

О, даже близко не к 0. Может быть, лучшее предположение, скажем, 10%, может что-то изменить?

• Сейчас: PV = -1000 долларов США
• Год 1: PV = 500 долл. США / (1+0,1) ¹ = 454,55 долл. США
• Год 2: PV = 660 долл. США / (1+0,1) ² = 545,45 долл. США
• NPV: -1000 + 454,55 долл. США + 545,45 долл. США = 0,00 долл. США

Вот оно! При ставке дисконтирования 10% чистая приведенная стоимость равна ровно 0. Таким образом, внутренняя норма доходности для этой инвестиции составляет 10%:

.

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

Как рассчитать IRR в Excel с помощью формул

Microsoft Excel предоставляет 3 функции для определения внутренней нормы доходности:

• IRR — наиболее часто используемая функция для расчета внутренней нормы доходности для ряда денежных потоков, которые происходят через регулярных интервалов .
• XIRR – находит IRR для ряда денежных потоков, которые происходят через нерегулярных интервалов . Поскольку она учитывает точные даты платежей, эта функция обеспечивает более высокую точность расчета.
• MIRR – возвращает модифицированную внутреннюю норму доходности , которая представляет собой вариант IRR, учитывающий как стоимость заимствования, так и сложные проценты, полученные от реинвестирования положительных денежных потоков.

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

Формула IRR для расчета внутренней нормы прибыли

Предположим, вы рассматриваете 5-летнюю инвестицию с денежными потоками в B2:B7. Чтобы рассчитать IRR, используйте эту простую формулу:

= IRR(B2:B7)

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

Для получения дополнительной информации см. функцию Excel IRR.

Формула ЧВВНР для определения ВНР для нерегулярных денежных потоков

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

С денежными потоками в B2:B7 и их датами в C2:C7 формула будет выглядеть следующим образом:

=ЧИСТВНИЗ(B2:B7,C2:C7)

Примечания:

• Хотя функция ЧИСТВНДОХ не обязательно требует дат в хронологическом порядке, дата первого денежного потока (первоначальных инвестиций) должна быть первой в массиве.
• Даты должны быть указаны как действительные даты Excel ; предоставление дат в текстовом формате подвергает Excel риску их неправильной интерпретации.
• Функция Excel ЧИСТРОБД использует другую формулу для получения результата. Формула ЧИСТВНРД дисконтирует последующие платежи, исходя из 365-дневного года, в результате ЧИСТВНОРД всегда возвращает внутреннюю норму доходности в годовом исчислении .

Дополнительные сведения см. в статье о функции ЧССНП в Excel.

Формула MIRR для расчета модифицированной IRR

Чтобы справиться с более реалистичной ситуацией, когда средства проекта реинвестируются по ставке, близкой к стоимости капитала компании, вы можете рассчитать модифицированную внутреннюю норму доходности, используя формулу MIRR:

=MIRR(B2:B7,E1,E2)

Где B2:B7 — денежные потоки, E1 — ставка финансирования (стоимость заимствования денег), а E2 — ставка реинвестирования (проценты, полученные от реинвестирования прибыли).

Примечание. Поскольку функция MIRR Excel вычисляет сложные проценты на прибыль, ее результат может существенно отличаться от результатов функций IRR и XIRR.

IRR, XIRR и MIRR — что лучше?

Я считаю, что никто не может дать общий ответ на этот вопрос, потому что теоретическая основа, преимущества и недостатки всех трех методов все еще обсуждаются учеными-финансистами. Пожалуй, лучше всего было бы сделать все три расчета и сравнить результаты:

Обычно считается, что:

• XIRR обеспечивает лучшую точность расчета, чем IRR, поскольку учитывает точные даты денежных потоков.
• IRR часто дает неоправданно оптимистическую оценку прибыльности проекта, тогда как MIRR дает более реалистичную картину.

Калькулятор IRR — шаблон Excel

Если вам необходимо регулярно рассчитывать IRR в Excel, настройка шаблона внутренней нормы прибыли может значительно облегчить вашу жизнь.

Наш калькулятор будет включать в себя все три формулы (IRR, XIRR и MIRR), так что вам не придется беспокоиться о том, какой результат является более достоверным, но вы можете рассмотреть их все.

1. Введите денежные потоки и даты в два столбца (в нашем случае A и B).
2. Введите ставку финансирования и ставку реинвестирования в 2 отдельные ячейки. При желании назовите эти продажи Finance_rate и Reinvest_rate соответственно.
3. Создайте два динамически определяемых диапазона с именем Cash_flows и Даты .

   Предполагая, что ваш рабочий лист называется Sheet1 , первый денежный поток (начальные инвестиции) находится в ячейке A2, а дата первого денежного потока — в ячейке B2, создайте именованные диапазоны на основе следующих формул:

   Денежные_потоки:

   =СМЕЩЕНИЕ(Лист1!$A$2,0,0,COUNT(Лист1!$A:$A),1)

   Даты:

   =СМЕЩЕНИЕ(Лист1!$B$2,0,0,COUNT(Лист1!$B:$B),1)

   Подробные инструкции можно найти в разделе Как создать динамический именованный диапазон в Excel.

4. Используйте только что созданные имена в качестве аргументов следующих формул. Обратите внимание, что формулы можно вводить в любой столбец, кроме A и B, которые зарезервированы исключительно для денежных потоков и дат соответственно.

   = IRR(Cash_flows)

   = ЧИСТРРР(Денежные_потоки, Даты)

   =MIRR(Cash_flows, Finance_rate, Reinvest_rate)

Готово! Теперь вы можете ввести любое количество денежных потоков в столбец A, и ваши формулы динамической внутренней нормы прибыли будут соответствующим образом пересчитаны:

В качестве меры предосторожности против небрежных пользователей, которые могут забыть заполнить все необходимые входные ячейки, вы можете заключить свои формулы в функцию ЕСЛИОШИБКА, чтобы предотвратить ошибки:

=ЕСЛИОШИБКА(IRR(Cash_flows), "")

=ЕСЛИОШИБКА(ЧИСТРОД(ЧИСТЫЙ_ДОХОД(Денежные_потоки, Даты), "")

=ЕСЛИОШИБКА(MIRR(Cash_flows, Finance_rate, Reinvest_rate), "")

Имейте в виду, что если ячейки Finance_rate и/или Reinvest_rate пусты, функция MIRR Excel предполагает, что они равны нулю.

Как сделать IRR в Excel с поиском цели

Функция Excel IRR выполняет только 20 итераций, чтобы получить скорость, а функция XIRR выполняет 100 итераций. Если после этого количества итераций результат с точностью до 0,00001% не найден, выдается ошибка #ЧИСЛО! возвращается ошибка.

Если вам требуется более точный расчет IRR, вы можете заставить Excel выполнить более 32 000 итераций с помощью функции поиска цели, которая является частью анализа «что, если».

Идея состоит в том, чтобы заставить Goal Seek найти процентную ставку, которая делает NPV равным 0. Вот как:

1. Настройте исходные данные следующим образом:
   • Введите денежные потоки в столбец (в данном примере B2:B7).
   • Поместите ожидаемую IRR в какую-нибудь ячейку (B9). Значение, которое вы вводите, на самом деле не имеет значения, вам просто нужно что-то «скормить» формуле NPV, так что просто поставьте любой процент, который придет вам на ум, скажем, 10%.
   • Введите следующую формулу NPV в другую ячейку (B10):
     =ЧПС(B9,B3:B7)+B2

2. На вкладке Данные в группе Прогноз щелкните Что если анализ > Поиск цели…
3. В диалоговом окне Goal Seek определите ячейки и значения для проверки:
   • Установить ячейку — ссылку на ячейку NPV (B10).
   • В значение введите 0, что является желаемым значением для ячейки Set.
   • При изменении ячейки — ссылка на ячейку IRR (B9).

   По завершении нажмите OK .
   

4. Появится диалоговое окно Goal Seek Status , в котором будет указано, найдено ли решение. В случае успеха значение в ячейке IRR будет заменено новым значением, которое сделает NPV нулевым.

   Нажмите OK , чтобы принять новое значение, или Cancel , чтобы вернуться к исходному значению.
   

Аналогичным образом вы можете использовать функцию поиска цели, чтобы найти XIRR. Единственное отличие состоит в том, что вам нужно будет использовать формулу XNPV вместо NPV.

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