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

Содержание

Внутренняя Норма Доходности 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)

Функция XIRR — формула, примеры, руководство по использованию XIRR

Функция XIRR относится к финансовым функциям Excel. Он рассчитает внутреннюю норму доходности (IRR) Внутренняя норма доходности (IRR) Внутренняя норма доходности (IRR) — это ставка дисконтирования, которая делает чистую приведенную стоимость (NPV) проекта равной нулю. Другими словами, это ожидаемая совокупная годовая норма прибыли, которая будет получена от проекта или инвестиций. для серии денежных потоков, которые могут быть непериодическими. Это достигается путем присвоения конкретных дат каждому отдельному денежному потоку. Основное преимущество использования функции XIRR Excel состоит в том, что такие неравномерные по времени денежные потоки можно точно смоделировать. Чтобы узнать больше, прочтите, почему всегда использовать XIRR вместо IRR XIRR против IRR Зачем использовать XIRR против IRR. XIRR назначает конкретные даты каждому отдельному денежному потоку, что делает его более точным, чем IRR, при построении финансовой модели в Excel. в моделировании Excel.

В финансовом моделировании Что такое финансовое моделирование Финансовое моделирование выполняется в Excel для прогнозирования финансовых показателей компании. Обзор того, что такое финансовое моделирование, как и зачем его создавать. функция XIRR полезна для определения стоимости инвестиций или понимания осуществимости проекта, который не имеет регулярных периодических денежных потоков. Это помогает нам понять норму прибыли от инвестиций. Следовательно, он обычно используется при оценке и выборе между двумя или более инвестициями.

Формула

= XIRR (значения, даты; [предположение])

В формуле используются следующие аргументы:

Значения (обязательный аргумент) — это массив значений, представляющих серию денежных потоков. Вместо массива это может быть ссылка на диапазон ячеек, содержащих значения.
Даты (обязательный аргумент) — это серия дат, соответствующих заданным значениям. Последующие даты должны быть позже первой даты, так как первая дата — это дата начала, а последующие даты — это будущие даты исходящих платежей или доходов.
[предположение] (необязательный аргумент) — это первоначальное предположение или оценка того, какой будет IRR. Если он не указан, Excel принимает значение по умолчанию 10%.

Excel использует итеративный метод расчета XIRR. Используя скорость изменения (начиная с [предположить]), XIRR циклически перебирает вычисления до тех пор, пока результат не будет точным в пределах 0,000001%.

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

Чтобы понять использование функции XIRR, давайте рассмотрим несколько примеров:

Пример XIRR

Предположим, что проект стартовал 1 января 2018 года. Проект дает нам денежные потоки в середине первого года, через 6 месяцев, затем через 1,5 года, 2 года, 3,5 года и затем ежегодно. Приведенные данные показаны ниже:

Используемая формула будет следующей:

Мы оставим предположение пустым, поэтому Excel примет значение по умолчанию 10%.

Получаем результат ниже:

Что нужно помнить о функции XIRR

Числа в датах округляются до целых.
XNPV и XIRR тесно связаны. Ставка доходности, рассчитываемая XIRR, — это процентная ставка, соответствующая XNPV = 0.
Даты следует вводить как ссылки на ячейки, содержащие даты или значения, полученные из формул Excel.
# ЧИСЛО! ошибка — возникает, если:
Массивы значений и дат имеют разную длину;
Данные массивы не содержат хотя бы одного отрицательного и хотя бы одного положительного значения;
Любая из указанных дат предшествует первой указанной дате;
Расчет не сходится после 100 итераций.
#ЦЕННОСТЬ! ошибка — возникает, когда одна из указанных дат не может быть распознана Excel как допустимые даты.
Щелкните здесь, чтобы загрузить образец файла Excel
Дополнительные ресурсы
Спасибо за то, что прочитали финансовое руководство по функции Excel XIRR. Потратив время на изучение и освоение этих функций, вы значительно ускорите свой финансовый анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными финансовыми ресурсами:
Функции Excel для финансов Excel для финансов Это руководство по Excel для финансов расскажет о 10 основных формулах и функциях, которые вы должны знать, чтобы стать отличным финансовым аналитиком в Excel. В этом руководстве есть примеры, скриншоты и пошаговые инструкции. В конце скачайте бесплатный шаблон Excel, который включает в себя все финансовые функции, описанные в руководстве.
Продвинутый курс Excel
Расширенные формулы Excel, которые вы должны знать Расширенные формулы Excel, которые необходимо знать Эти расширенные формулы Excel очень важно знать и выведут ваши навыки финансового анализа на новый уровень. Вы должны знать дополнительные функции Excel. Изучите 10 лучших формул Excel, которые регулярно использует каждый финансовый аналитик мирового уровня. Эти навыки улучшат вашу работу с таблицами в любой карьере.
Ярлыки Excel для ПК и Mac Ярлыки Excel для ПК Mac Ярлыки Excel — список наиболее важных и распространенных ярлыков MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, манипулирование данными, редактирование формул и ячеек и другие краткие сведения.
Как рассчитать внутреннюю норму прибыли (IRR) в Excel
Что такое внутренняя норма прибыли (IRR)?
Внутренняя норма доходности (IRR) — это ставка дисконтирования, обеспечивающая нулевую чистую стоимость будущих денежных потоков. И IRR, и чистая приведенная стоимость (NPV) используются при выборе инвестиций на основе их доходности.
В Excel есть три функции для расчета внутренней нормы доходности, включая внутреннюю норму доходности (IRR), модифицированную внутреннюю норму доходности (MIRR) и внутреннюю норму доходности с периодами времени (XIRR).
Функция IRR вычисляет внутреннюю норму доходности для ряда денежных потоков, функция MIRR работает с процентными ставками для заимствования и инвестирования, а функция XIRR рассчитывает более точную внутреннюю норму доходности, поскольку она учитывает периоды времени.

Ключевые выводы
• Внутренняя норма доходности (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 вручную в Excel
Как рассчитать IRR с помощью формул Excel
Формула IRR для расчета внутренней нормы прибыли
Формула XIRR для определения IRR для денежных потоков с неравным временем
Формула MIRR для получения модифицированного IRR
Калькулятор внутренней нормы прибыли / шаблон
Как сделать 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.
Формула ЧВНРД для определения ВНР для неравномерных денежных потоков
В случае денежных потоков с неравным временем использования функции ВНР может быть рискованно, поскольку предполагается, что все платежи происходят в конце периода и все периоды времени равны. В этом случае XIRR будет более разумным выбором.
С денежными потоками в B2:B7 и их датами в C2:C7 формула будет выглядеть следующим образом: Функция ЧВНПД не обязательно требует даты в хронологическом порядке, дата первого денежного потока (первоначальных инвестиций) должна быть первой в массиве.
Даты должны быть указаны как действительные даты Excel ; предоставление дат в текстовом формате подвергает Excel риску их неправильной интерпретации.
Функция ЧИСТВНДОХ в Excel использует другую формулу для получения результата. Формула ЧИСТВНРД дисконтирует последующие платежи, исходя из 365-дневного года, в результате ЧИСТВНОРД всегда возвращает внутреннюю норму доходности в годовом исчислении .
Для получения дополнительной информации см. функцию ЧССНП в Excel.
Формула MIRR для расчета модифицированной внутренней нормы доходности
Чтобы справиться с более реалистичной ситуацией, когда средства проекта реинвестируются по ставке, близкой к стоимости капитала компании, вы можете рассчитать модифицированную внутреннюю норму прибыли, используя формулу MIRR:
=MIRR(B2:B7,E1,E2)
Где B2:B7 — денежные потоки, E1 — ставка финансирования (стоимость займа денег), а E2 — ставка реинвестирования (проценты, полученные на реинвестирование прибыли).
Примечание. Поскольку функция MIRR Excel вычисляет сложные проценты на прибыль, ее результат может существенно отличаться от результатов функций IRR и XIRR.
IRR, XIRR и MIRR — что лучше?
Я считаю, что никто не может дать общий ответ на этот вопрос, потому что теоретические основы, преимущества и недостатки всех трех методов все еще обсуждаются учеными-финансистами. Пожалуй, лучше всего было бы сделать все три расчета и сравнить результаты:
Обычно считается, что:
ЧИСТВНР обеспечивает лучшую точность расчета, чем ВНР, поскольку учитывает точные даты денежных потоков.
IRR часто дает неоправданно оптимистическую оценку прибыльности проекта, тогда как MIRR дает более реалистичную картину.
Калькулятор IRR — шаблон Excel
Если вам необходимо регулярно выполнять расчет IRR в Excel, настройка шаблона внутренней нормы прибыли может значительно облегчить вашу жизнь.
Наш калькулятор будет включать в себя все три формулы (IRR, XIRR и MIRR), так что вам не придется беспокоиться о том, какой результат является более достоверным, но вы можете рассмотреть их все.
Введите денежные потоки и даты в два столбца (в нашем случае A и B).
Введите ставку финансирования и ставку реинвестирования в 2 отдельные ячейки. При желании назовите эти продажи Finance_rate и Reinvest_rate соответственно.
Создайте два динамически определяемых диапазона с именем 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.
Используйте только что созданные имена в качестве аргументов следующих формул. Обратите внимание, что формулы можно вводить в любой столбец, кроме A и B, которые зарезервированы исключительно для денежных потоков и дат соответственно.
= IRR(Cash_flows)
= ЧИСТВНРД(Денежные_потоки, Даты)
=MIRR(Cash_flows, Finance_rate, Reinvest_rate)
Готово! Теперь вы можете ввести любое количество денежных потоков в столбец A, и ваши формулы динамической внутренней нормы прибыли будут пересчитаны соответствующим образом:
В качестве меры предосторожности против небрежных пользователей, которые могут забыть заполнить все необходимые входные ячейки, вы можете обернуть свои формулы в функции ЕСЛИОШИБКА для предотвращения ошибок:
=ЕСЛИОШИБКА(IRR(Cash_flows), "")
=IFFERROR(XIRR(Cash_flows, Dates), "")
=IFERROR(MIRR(Cash_flows, Finance_rate, Reinvest_rate), "")
Имейте в виду, что если ячейки Finance_rate и/или Reinvest_rate пусты, функция MIRR в Excel предполагает, что они равны нулю.
Как сделать IRR в Excel с поиском цели
Функция Excel IRR выполняет только 20 итераций, чтобы получить скорость, а XIRR выполняет 100 итераций. Если после этого количества итераций результат с точностью до 0,00001% не найден, выдается ошибка #ЧИСЛО! возвращается ошибка.
Если вам требуется более точный расчет внутренней нормы доходности, вы можете заставить Excel выполнить более 32 000 итераций с помощью функции поиска цели, которая является частью анализа «что, если».
Идея состоит в том, чтобы заставить Goal Seek найти процентную ставку, которая делает NPV равным 0. Вот как:
Настройте исходные данные следующим образом:
Введите денежные потоки в столбец (в данном примере B2:B7).
Поместите ожидаемую IRR в какую-нибудь ячейку (B9). Значение, которое вы вводите, на самом деле не имеет значения, вам просто нужно что-то «скормить» формуле NPV, поэтому просто введите любой процент, который придет вам в голову, скажем, 10%.
Введите следующую формулу NPV в другую ячейку (B10):
=ЧПС(B9,B3:B7)+B2
На вкладке Данные в группе Прогноз щелкните Что если анализ > Поиск цели…
В диалоговом окне Goal Seek определите ячейки и значения для проверки:
Установить ячейку — ссылку на ячейку NPV (B10).
В значение введите 0, что является желаемым значением для ячейки Set.
При изменении ячейки — ссылка на ячейку IRR (B9).
Когда закончите, нажмите OK .
Появится диалоговое окно Goal Seek Status , в котором будет указано, найдено ли решение. В случае успеха значение в ячейке IRR будет заменено новым значением, которое сделает NPV нулевым.
Нажмите OK , чтобы принять новое значение, или Cancel , чтобы вернуться к исходному значению.