Irr показывает: Внутренняя норма доходности (ВНД) — что такое и как рассчитать

Исправление ошибки #ЧИСЛО! — Служба поддержки Майкрософт

Excel

Формулы и функции

Ошибки

Ошибки

Исправление ошибки #ЧИСЛО!

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel Web App Excel для iPhone Excel для планшетов с Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов с Android Excel для Windows Phone 10 Excel Mobile Excel Starter 2010 Еще…Меньше

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

Такое часто происходит, если ввести числовое значение, используя тип данных или числовой формат, который не поддерживается в разделе аргументов данной формулы. Например, нельзя ввести значение $1,000 в формате валюты, так как знаки доллара используются как индикаторы абсолютной ссылки, а запятые — как разделители аргументов.

Чтобы предотвратить появление ошибки #ЧИСЛО!, вводите значения в виде неформатированных чисел, например 1000.

В Excel ошибка #ЧИСЛО! также может возникать, если:

• в формуле используется функция, выполняющая итерацию, например ВСД или СТАВКА, которая не может найти результат.

  Чтобы исправить ошибку, измените число итераций формулы в Excel.

  1. На вкладке Файл выберите пункт Параметры. Если вы используете Excel 2007, нажмите кнопку Microsoft Office и выберите Параметры Excel

     .

  2. На вкладке Формулы в разделе Параметры вычислений установите флажок Включить итеративные вычисления.

  3. В поле Предельное число итераций введите необходимое количество пересчетов в Excel. Чем больше предельное число итераций, тем больше времени потребуется для вычислений.

  4. Для задания максимально допустимой величины разности между результатами вычислений введите ее в поле Относительная погрешность

     . Чем меньше число, тем точнее результат и тем больше времени потребуется Excel для вычислений.

• Результат формулы — число, слишком большое или слишком малое для отображения в Excel.

  Чтобы исправить ошибку, измените формулу таким образом, чтобы результат ее вычисления находился в диапазоне от -1*10307 до 1*10307.

  Совет: Если в Microsoft Excel включена проверка ошибок, нажмите кнопку рядом с ячейкой, в которой показана ошибка. Выберите пункт Показать этапы вычисления, если он отобразится, а затем выберите подходящее решение.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Функции Excel (по алфавиту)

Функции Excel (по категориям)

89.

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

IRR (Internal Rate of Return, IRR) определяет максимальную стоимость привлекаемого капитала, при которой инвестиционный проект остается выгодным. В другой формулировке, это средний доход на вложенный капитал, обеспечиваемый данным инвестиционным проектом, т.е. эффективность вложений капитала в данный проект равна эффективности инвестирования под IRR процентов в какой-либо финансовый инструмент с равномерным доходом.

90.Что показывает pi?

Рентабельность инвестиционного проекта

91.Что показывает dpp?

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

92.Что показывает mirr?

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

93.По двум инвестиционным проектам по одной и той же процентной ставке были посчитаны NPV: NPV1 = 134,85, NPV2 = 510,31. Что можно сказать по этим значениям при данной процентной ставке?

Второй выгоднее

94.По двум инвестиционным проектам были посчитаны PI: PI1 = 1,39, PI2 = 1,23. Что можно сказать по этим значениям при данной процентной ставке?

Индекс доходности по обоим проектам больше 100%, что соответствует положительной величине NPV, однако первый проект выгоднее

95.По инвестиционному проекту irr оказался равен 125%. О чём это говорит?

IRR (Internal Rate of Return, IRR) определяет максимальную стоимость привлекаемого капитала, при которой инвестиционный проект остается выгодным. То есть максимальный процент, под который можно привлечь средства для инвестирования будет 125%. В другой формулировке, это средний доход на вложенный капитал, обеспечиваемый данным инвестиционным проектом, т. е. эффективность вложений капитала в данный проект равна эффективности инвестирования под IRR процентов в какой-либо финансовый инструмент с равномерным доходом.

96.По инвестиционному проекту mirr оказался равен 15%. О чём это говорит?

Максимальный процент, под который можно привлечь средства для инвестирования- 15%

По данному проекту допускается реинвестирование денежных потоков по ставке 15%

97.Какими недостатками обладает irr?

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

 

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

Как рассчитать IRR в Excel? (Функция и формула IRR)

Внутренняя норма доходности (IRR) — это понятие в финансовом моделировании, обычно используемое в сочетании с чистой приведенной стоимостью (NPV). Формула в Excel используется для оценки нормы прибыли, которую дадут инвестиции, на основе ряда денежных потоков, обычно представленных в процентах («3%). С другой стороны, NPV выражается в виде кардинального числа («3»). Оба играют ключевую роль в принятии инвестиционных решений, когда предоставляется более одной возможности, как мы покажем здесь.

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

Получите навсегда бесплатную учетную запись Confluence!

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

ПОЛУЧИТЕ БЕСПЛАТНО

Как использовать функцию IRR в Excel?

Сначала мы объясним, как использовать функцию IRR в Excel для расчета нормы годового дохода для первоначальных инвестиций. Если вам нужно рассчитать доходность за разные периоды времени или будущую стоимость, Excel также предлагает множество финансовых функций, например, XIRR или MIRR.

По какой формуле рассчитывается IRR?

Синтаксис формулы IRR:

 =IRR(значения;[предположение]) 

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

ВАЖНО: Важно, чтобы первоначальные инвестиции и денежные потоки сохраняли одинаковую последовательность в формуле IRR, которая будет интерпретироваться именно в этом порядке. Более того, для правильной работы функции IRR аргумент должен быть числом. Если это логическое значение, текст или пустая ячейка, оно будет проигнорировано.

• [догадка]: Этот аргумент является необязательным. Это оценка числа, наиболее близкого к ожидаемому IRR (поскольку их может быть два). Если он не указан, аргумент будет показывать значение по умолчанию 0,1 (= 10%).

Давайте теперь применим формулу IRR к следующему сценарию:

Вы являетесь владельцем бизнеса, и у вас есть две инвестиционные возможности, A и B. Первая имеет первоначальные инвестиции в размере 2 500 000,00 долларов США, тогда как вторая имеет одну из 2 800 000,00 евро.

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

1. 1. Вариант А имеет первоначальные инвестиции в размере 2 500 000,00 долларов США со следующими ежегодными денежными потоками.

1. 2. Введите «IRR» в ячейку, в которой вы хотите отобразить значение IRR.

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

1. 4. После того, как вы нажмете клавишу возврата, вы увидите процент IRR. Помните, что IRR представлена ​​в процентах, тогда как NPV — это кардинальное число. Здесь мы получаем возврат в размере 5%.

1. 5. Теперь давайте рассчитаем IRR для Варианта B с первоначальными инвестициями в размере 2 800 000,00 долларов США и последующими ежегодными денежными потоками.

1. 6. Мы должны ввести формулу так же, как мы делали ранее в шагах 2-3.

1. 7. Здесь мы получаем внутреннюю норму доходности 8%.

Информация, полученная на данный момент, поможет нам принять решение исключительно на основе IRR; это обычная практика, чтобы пойти на более высокий процент. Учитывая, что вариант А обеспечивает внутреннюю норму доходности 5%, а вариант Б — 8%, мы бы предпочли инвестировать в проект Б. Однако, если бы денежные потоки были ежемесячными, наши решения могли бы быть более точными. Следуя этим шагам, вы легко научитесь рассчитывать IRR в Excel для ежемесячного денежного потока.

Как поделиться файлом Excel для нескольких пользователей?

Существует несколько способов поделиться файлом Excel. Вот как можно поделиться файлом Excel с несколькими пользователями для удобства совместной работы

ЧИТАТЬ ДАЛЕЕ

Как рассчитать IRR с помощью NPV в Excel?

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

 =ЧПС(ставка, значения) 

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

Допустим, мы применяем ставку дисконтирования 5% (это может быть более низкая ставка) к обеим инвестиционным возможностям.

1. 1. Введите «NPV» в ячейку, в которой вы хотите получить формулу NPV.

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

1. 3. Нажмите клавишу возврата, чтобы получить значение NPV (50 196,47 долларов США).

1. 4. Чтобы рассчитать чистую приведенную стоимость для варианта инвестирования B, выполните шаги 1–3.

Как упоминалось ранее, теперь нам нужно рассчитать чистую приведенную стоимость для более высокой ставки дисконтирования (8%) для обеих инвестиционных возможностей. Опять же, давайте начнем с варианта A.

1. 5. Мы включили вторую строку, в которой мы будем вычислять NPV, примененную к тем же значениям, что и раньше, но на этот раз включите ячейку B15 в качестве ставки дисконтирования.

1. 6. Перейдите к применению значений к формуле NPV и нажмите клавишу «Возврат», чтобы получить результат: следующие:

На основании полученных результатов принимаем решение по Варианту Б. Почему? Поскольку и NPV, и IRR выше в обоих сценариях ставок дисконтирования:

Формула IRR может использоваться только в тех случаях, когда распределение денежных потоков происходит в одни и те же периоды времени (годовые, месячные и т. д.). Если вы хотите рассчитать доходность за разные периоды времени, используйте формулу ЧИСТВНР в Excel. Это выбирает диапазон денежных потоков, а затем диапазон дат, в которые производятся денежные потоки.

Как использовать функцию GOOGLEFINANCE в Google Sheets?

Импорт текущих или исторических данных финансового рынка из Google Finance и мониторинг в режиме реального времени. Вот как использовать функцию GOOGLEFINANCE в Google Таблицах

ЧИТАТЬ ДАЛЕЕ

ЧИТАТЬ ДАЛЕЕ

Как рассчитать IRR вручную?

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

1. 1. Оцените два значения ставок дисконтирования (т. е. 4% и 8%), чтобы попытаться установить NPV равным нулю.
2. 2. Рассчитайте чистую приведенную стоимость, используя 4% и 8%, и постарайтесь максимально приблизиться к нулю.
3. 3. После того, как вы определите два значения ставки дисконтирования и две чистой стоимости, вы можете использовать эту формулу для расчета внутренней нормы доходности:

 NPV2) ) 

1. 4. R1 соответствует более низкой учетной ставке (4%), а R2 — более высокой (8%). С другой стороны, NPV1 представляет более высокую NPV, а NPV2 — более низкую.

Откройте для себя Confluence: рабочее пространство Ultimate Team

Измените способ совместной работы вашей команды с помощью Confluence, удобного для удаленного рабочего пространства, предназначенного для объединения знаний и совместной работы. Попрощайтесь с разрозненной информацией и разрозненным общением и воспользуйтесь платформой, которая позволит вашей команде добиться большего вместе.

Основные характеристики и преимущества:

• Централизованное знание:  Удобный доступ к коллективному разуму вашей команды.
• Совместное рабочее пространство:  Поощряйте взаимодействие с помощью гибких инструментов проекта.
• Удобная связь:  Подключите всю свою организацию без особых усилий.
• Сохранить идеи:  Сохраняйте идеи, не теряя их в чатах или уведомлениях.
• Комплексная платформа:  Управляйте всем контентом в одном месте.
• Открытая командная работа: Предоставьте сотрудникам возможность вносить свой вклад, делиться информацией и расти.
• Превосходная интеграция:  Синхронизация с такими инструментами, как Slack, Jira, Trello и другими.

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

Оставьте это поле пустым

руководств и ресурсов по Excel и Google Sheets прямо в вашей почте!

Я разрешаю Layer хранить и обрабатывать эту информацию и использовать ее для связи со мной по поводу соответствующего контента и услуг.

Заключение

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

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

Получите навсегда бесплатную учетную запись Confluence!

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

ПОЛУЧИТЕ БЕСПЛАТНО

Хади ЭльХади

Хэди — руководитель отдела контента в Layer.

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

Первоначально опубликовано 26 января 2022 г., обновлено 22 марта 2023 г.

Расчет 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 основан на традиционной формуле NPV:

Если вы не очень хорошо знакомы с обозначениями суммирования, расширенная форма формулы 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.