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

Рассчитать IRR в Excel — voxt

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

Например, предположим, что ваша компания сделала первоначальные инвестиции в размере 50 000 долларов и рассчитывает получать 10 000 долларов в год в течение следующих 10 лет. Мы можем использовать эти данные для расчета IRR этой инвестиции, которая представляет собой норму прибыли, которую вы можете получить на свои инвестиции в размере 100 долларов США. В результате IRR получается равным 15%. Это эквивалентно деньгам, вложенным под 15% на 10 лет.

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

Он сравнивает и выбирает лучший проект, при этом выбирается проект с IRR, превышающим минимально допустимую доходность (барьерную ставку). Читать далее в Excel означает явнутренний рсъел из рвозвращение. Это одна из встроенных функций, доступных в Microsoft Excel. Кроме того, он подпадает под категорию финансовых функций.

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

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

Функция IRR в Excel возвращает внутреннюю норму доходности для ряда денежных потоков. Денежные потоки Денежный поток — это сумма денежных средств или эквивалента денежных средств, созданная и потребленная компанией за определенный период.

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

Оглавление

• Расчет IRR в Excel
  • Формула, используемая для расчета IRR в Excel
  • Как рассчитать IRR в Excel? (с примерами)
    • Пример № 1. Расчет внутренней нормы доходности в таблице Excel
    • Пример #2
    • Пример №3
    • Пример №4
    • Пример № 5. Расчет IRR в Excel VBA
  • IRR в примере Excel VBA
  • То, что нужно запомнить
  • IRR в видео Excel
  • Рекомендуемые статьи

Формула, используемая для расчета IRR в Excel

Формула IRR в Excel выглядит следующим образом:

Где,

• диапазон = Это обязательный параметр. Диапазон ячеек обозначает ряд денежных потоков, для которых мы рассчитываем внутреннюю норму доходности.
• предполагать = Это необязательный параметр. Он обозначает число, которое, как вы предполагаете, близко к результату внутренней нормы доходности. Если значение не указано, принимается значение по умолчанию 0,1 или 10%.
• “[ ]” обозначает необязательные параметры.

Функция возвращает числовое значение. Он может быть положительным или отрицательным.

Как рассчитать IRR в Excel? (с примерами)

.free_excel_div{фон:#d9d9d9;размер шрифта:16px;радиус границы:7px;позиция:относительная;margin:30px;padding:25px 25px 25px 45px}.free_excel_div:before{content:»»;фон:url(центр центр без повтора #207245;ширина:70px;высота:70px;позиция:абсолютная;верх:50%;margin-top:-35px;слева:-35px;граница:5px сплошная #fff;граница-радиус:50%}

Вы можете скачать этот шаблон Excel функции IRR здесь — Шаблон Excel функции IRR

Мы можем выполнять расчеты IRR в Excel с помощью функции рабочего листа (WS) или функции VBA. Его можно ввести как часть формулы в ячейку рабочего листа как функцию WS. С другой стороны, его можно использовать в макрокоде как функцию VBA. Функция VBA Функции VBA служат основной цели для выполнения определенных вычислений и возврата значения. Поэтому в VBA мы используем синтаксис для указания параметров и типа данных при определении функции. Такие функции называются пользовательскими функциями. Подробнее, вводимыми через редактор Microsoft Visual Basic Editor, интегрированный в MS Excel. Обратитесь к расчету IRR в примере Excel ниже, чтобы лучше понять. Пример № 1. Расчет внутренней нормы доходности в таблице Excel

В этом примере расчета IRR -3000 обозначает сумму инвестированного капитала. Следующие числа, 2000 и 5000, указывают на доход. Функция IRR в Excel рассчитывается для значений -3000, 2000 и 5000. Результат 67%.

Пример #2

В этом примере расчета IRR #ЧИСЛО! Он отображается как результат, поскольку в наборе значений нет ни одного отрицательного значения. Итак, нет значения, указывающего на оплату, но все являются доходом.

Пример №3

В этом примере расчета IRR все значения отрицательные. Таким образом, это указывает на отсутствие дохода. Следовательно, сгенерированный результат равен #ЧИСЛО!

Пример №4

В этом примере расчета IRR общее количество значений равно пяти. -10 000 обозначает выполненный платеж. Следующие значения приведены за год, т. е. доход, полученный в 1-м году, равен 2000, во 2-м году — 5000, в 3-м году — 4000 и в 5-м году — 6000.

Чтобы рассчитать доход, полученный в конце трех лет, примените формулу IRR к ячейкам в диапазоне от B2:B4. Результат составляет -19%, так как сумма платежа в начальный год больше, чем доход предыдущего года.

Чтобы рассчитать доход, полученный в конце пяти лет, примените формулу IRR к ячейкам в диапазоне от B2: B6. Результат 22%. Результат положительный, поскольку доход, полученный в последующие годы, превышает сумму платежа, вложенную в первый год.

Пример № 5. Расчет IRR в Excel VBA

Функцию IRR можно использовать в коде VBA Код VBA Код VBA относится к набору инструкций, написанных пользователем на языке программирования приложений Visual Basic в редакторе Visual Basic (VBE) для выполнения определенной задачи. Подробнее в Microsoft Excel следующим образом:

Формула IRR в Excel VBA:

Формула Irr в VBA выглядит следующим образом.

Где,

Ценности = Денежный поток

IRRval = Внутренняя норма доходности

IRR в примере Excel VBA

Давайте рассмотрим пример для Irr в VBA.

В этом примере вычисления IRR IRRal является переменной типа double. Он содержит результат, возвращаемый функцией IRR. Значения представлены в массиве типа данных double, где начальное значение равно -10000, что указывает на платеж, сделанный в первый год. Например, следующие значения — 2 000, 5 000, 4 000 и 6 000 — показывают годовой доход, полученный в течение следующих четырех лет.

То, что нужно запомнить

1. Массив значений (диапазон
   ) должен содержать как минимум одно положительное и одно отрицательное число.
2. Значения должны быть в последовательном порядке. IRR использует порядок значений для интерпретации порядка денежных потоков. Пожалуйста, введите значения платежа и дохода в той последовательности, в которой они происходят, или если вы хотите, чтобы это произошло.
3. Эти значения игнорируются, если аргумент массива или ссылки содержит текст, логические значения или пустые ячейки.
4. MS Excel использует итеративный метод для расчета IRR с помощью Excel. Начиная с предположения, IRR циклически повторяет расчет, пока результат не будет точным в пределах 0,00001 процента; если IRR не может найти результат, который работает после 20 попыток, #ЧИСЛО! Возвращается значение ошибки.
5. Если функция IRR в Excel возвращает значение #ЧИСЛО! или результат не такой, как вы ожидали, попробуйте с другим значением.
6. В большинстве случаев вам не нужно указывать предполагаемое значение для расчета IRR в Excel. Однако, если параметр угадывания пропущен, программное обеспечение принимает значение 0,1, т. е. 10%.
7. IRR тесно связана с NPV, функцией чистой приведенной стоимости. Норма прибыли, рассчитанная с помощью IRR с помощью Excel, представляет собой процентную ставку, соответствующую нулевой чистой текущей стоимости. оттоки за период реализации проекта. Если разница положительна, проект прибыльный; в противном случае это не так.Подробнее.

IRR в видео Excel

Рекомендуемые статьи

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

• Примеры внутренней нормы доходности
• IRR против ROI
• Математическая функция Excel
• Пользовательские функции в Excel

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Внутренняя Норма Доходности Excel — Furkan News — Güncel İslami Habeler

Содержание

• Внутренняя норма доходности (IRR). Формула и пример расчета в Excel
• Примеры функции ВСД в Excel
• Мастер-класс: «Как рассчитать внутреннюю норму доходности бизнес плана»

NPV – один из методов оценки инвестиционного проекта, который основывается на методологии дисконтирования денежных потоков. IRR , или ВНД – показатель внутренней нормы доходности инвестиционного проекта. Часто применяется для сопоставления различных предложений по перспективе роста и доходности.

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

Внутренняя норма доходности (IRR). Формула и пример расчета в Excel

Предположение – величина, которая предположительно близка к значению https://goforex. info/ (аргумент необязательный; но если функция выдает ошибку, аргумент нужно задать).

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

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

Примеры функции ВСД в Excel

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

Что такое Всд в Excel?

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

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

Мастер-класс: «Как рассчитать внутреннюю норму доходности бизнес плана»

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

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

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

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

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

Пример расчета IRR через надстройку «Поиск решений»

Для этого IRR сравнивают с эффективной ставкой дисконтирования, то есть с требуемым уровнем доходности проекта . За такой уровень на практике зачастую используют средневзвешенную стоимость капитала . Напомним, что IRR – это ставка дисконтирования, при которой NPV анализируемого проекта равняется нулю.

Во-вторых, показатель IRR не отражает размер реинвестирования в проект (данный недостаток решен в модифицированной внутренней норме доходности MIRR). Допустим, для запуска проекта брался кредит в банке под 15% годовых. Расчет показал, что внутренняя норма доходности составила 20,9%.

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

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

— Оба показателя во многих случаях показывают одно и то же решение. В финансовом менеджменте зачастую предпочтение отдаётся методу NPV, потому что NPV дает оценку прибыли инвестиционного проекта, расчёт IRR может выдавать несколько значений при чередовании положительных и отрицательных денежных потоков».

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

Значения – диапазон с суммами денежных потоков, по которым необходимо рассчитать внутреннюю норму рентабельности. Диапазон ячеек, в которых содержатся числовые выражения денежных средств. Для данных сумм нужно посчитать внутреннюю норму доходности. На рисунке ниже показан первоначальный вид для расчета IRR. Можно заметить, что ставка дисконтирования, используемая для расчета NPV, ссылается на ячейку, в которой нет данных (она принимается равной 0).

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

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

Функция

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

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

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

Функция IRR в Excel

Функция Excel IRR возвращает внутреннюю норму доходности для ряда периодических денежных потоков, представленных положительными и отрицательными числами.

Во всех расчетах неявно предполагается, что:

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

Функция доступна во всех версиях Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 и Excel 2007.

Синтаксис функции IRR в Excel:

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

Где:

• Значения (обязательно) — массив или ссылка на диапазон ячеек, представляющих серию денежных потоков, для которых вы хотите найти внутреннюю норму доходности.
• Guess (необязательно) — ваше предположение о том, какой может быть внутренняя норма доходности. Он должен быть представлен в виде процента или соответствующего десятичного числа. Если этот параметр опущен, используется значение по умолчанию 0,1 (10%).

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

= IRR(B2:B5)

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

Как показано на снимке экрана выше, наша формула IRR в Excel возвращает 8,9%. Эта ставка хороша или плоха? Ну, это зависит от нескольких факторов.

Как правило, рассчитанная внутренняя норма прибыли сравнивается с

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

В нашем примере, если вы обходится вам в 7%, чтобы занять деньги, то IRR около 9% является довольно хорошим. Но если стоимость средств составляет, скажем, 12%, то IRR в 9% недостаточно.

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

5 вещей, которые вы должны знать о функции Excel IRR

Чтобы расчет IRR в Excel был выполнен правильно, запомните следующие простые факты:

1. Аргумент values ​​ должен содержать как минимум одно положительное значение (представляющее доход) и одно отрицательное значение (представляющее расходы).
2. Обрабатываются только чисел в аргументе значений ; текст, логические значения или пустые ячейки игнорируются.
3. Денежные потоки не обязательно должны быть четными, но они должны происходить с регулярными интервалами , например ежемесячно, ежеквартально или ежегодно.
4. Поскольку IRR в Excel интерпретирует порядок денежных потоков на основе порядка значений, значения должны быть в хронологическом порядке .
5. В большинстве ситуаций аргумент предположения на самом деле не нужен. Однако, если уравнение IRR имеет более одного решения, возвращается ставка, ближайшая к предположению. Итак, ваша формула дает неожиданный результат или #ЧИСЛО! ошибка, попробуйте другое предположение.

Понимание формулы IRR в Excel

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

Где:

• CF — денежный поток
• я — номер периода
• n — всего периодов
• IRR — внутренняя норма доходности

Из-за специфики этой формулы нет другого способа рассчитать IRR, кроме как методом проб и ошибок. Microsoft Excel также использует этот метод, но очень быстро выполняет несколько итераций. Начиная с предположения (если указано) или 10% по умолчанию, функция Excel IRR циклически выполняет расчет, пока не найдет результат с точностью до 0,00001%. Если после 20 итераций точный результат не найден, функция #ЧИСЛО! возвращается ошибка. 9А3

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

=СУММ(С2:С5)

И узнаем, что при 7% мы получаем NPV в размере 37,90 доллара:

Очевидно, наша догадка неверна. Теперь давайте проведем тот же расчет на основе ставки, рассчитанной функцией IRR (около 8,9%). Да, это приводит к нулевой NPV:

Совет. Чтобы отобразить точное значение NPV, выберите отображение большего количества знаков после запятой или примените научный формат. В этом примере чистая приведенная стоимость равна нулю, что является очень редким случаем!

Использование функции IRR в Excel – примеры формул

Теперь, когда вы знаете теоретические основы расчета IRR в Excel, давайте составим пару формул, чтобы увидеть, как это работает на практике.

Пример 1.

Расчет IRR для ежемесячных денежных потоков

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

Найти IRR в Excel очень просто:

1. Введите первоначальные инвестиции в какую-то ячейку (в нашем случае B2). Поскольку это исходящий платеж, он должен быть отрицательным номером .
2. Введите последующие денежные потоки в ячейки под первоначальными инвестициями или справа от них (в данном примере B2:B8). Эти деньги поступали за счет продаж, поэтому мы вводим их как положительных чисел.

Теперь вы готовы рассчитать IRR для проекта:

= IRR(B2:B8)

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

Пример 2. Использование предположения в формуле IRR Excel

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

= IRR(B2:B8, 10%)

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

Пример 3. Найдите IRR для сравнения инвестиций

При составлении бюджета капиталовложений значения IRR часто используются для сравнения инвестиций и ранжирования проектов с точки зрения их потенциальной рентабельности. Этот пример демонстрирует технику в ее простейшей форме.

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

Формула для проекта 1:

= IRR(B2:B7)

Формула для проекта 2:

= IRR(C2:C7)

Формула для проекта 3:

= IRR(D2:D7)

Учитывая, что требуемая норма прибыли компании составляет, скажем, 9%, проект 1 следует отклонить, поскольку его IRR составляет всего 7%.

Две другие инвестиции приемлемы, потому что обе могут генерировать IRR выше пороговой ставки компании. Какой из них вы бы выбрали?

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

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

Пример 4. Расчет совокупного годового темпа роста (CAGR)

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

• Сохраните первое значение начальных инвестиций как отрицательное число, а конечное значение — как положительное число.
• Замените значения промежуточных денежных потоков нулями.
9(1/число периодов) -1

Как показано на снимке экрана ниже, обе формулы дают одинаковый результат:


Дополнительные сведения см. в разделе Как рассчитать CAGR в Excel.


IRR и NPV в Excel

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

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

Из-за разной природы IRR и NPV могут «конфликтовать» друг с другом — один проект может иметь более высокую NPV, а другой — более высокую IRR. Всякий раз, когда возникает такой конфликт, финансовые эксперты советуют отдавать предпочтение проекту с более высокой чистой приведенной стоимостью.

Чтобы лучше понять взаимосвязь между IRR и NPV, рассмотрим следующий пример. Допустим, у вас есть проект, который требует первоначальных инвестиций в размере 1000 долларов США (ячейка B2) и ставки дисконтирования 10% (ячейка E1). Срок жизни проекта составляет пять лет, а ожидаемые поступления денежных средств за каждый год указаны в ячейках B3:B7.

Чтобы узнать, сколько сейчас стоят будущие денежные потоки, нам нужно вычислить чистую текущую стоимость проекта. Для этого воспользуемся функцией NPV и вычтем из нее начальные инвестиции (поскольку первоначальные инвестиции — отрицательное число, используется операция сложения):

=ЧПС(Е1,В3:В7)+В2

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


При какой ставке дисконтирования чистая приведенная стоимость будет равна нулю? Следующая формула IRR дает ответ:

= IRR(B2:B7)

Чтобы проверить это, возьмите приведенную выше формулу NPV и замените ставку дисконтирования (E1) на IRR (E4):

=ЧПС(Е4,В3:В7)+В2

Или вы можете встроить функцию IRR прямо в ставка аргумент NPV:

= NPV(IRR(B2:B7),B3:B7)+B2


На снимке экрана выше видно, что значение NPV, округленное до 2 знаков после запятой, действительно равно нулю. Если вам интересно узнать точное число, установите научный формат в ячейку NPV или выберите отображение большего количества знаков после запятой:


.

Как видите, результат находится в пределах заявленной точности 0,00001 процента, и мы можем сказать, что чистая приведенная стоимость фактически равна 0,

.

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

Функция Excel IRR не работает

Если вы столкнулись с какой-либо проблемой с IRR в Excel, следующие советы могут дать вам ключ к ее устранению.

Формула IRR возвращает значение #ЧИСЛО! ошибка

А #ЧИСЛО! ошибка может быть возвращена по следующим причинам:

• Функция IRR не может найти результат с точностью до 0,000001% с 20-й попытки.
• Предоставленный диапазон значений не содержит хотя бы одного отрицательного и хотя бы одного положительного денежного потока.

Пустые ячейки в массиве значений

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



Множественные IRR

В ситуации, когда ряд денежного потока меняется с отрицательного на положительный или наоборот более одного раза, может быть найдено несколько IRR.

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


Возможные решения:

• Предполагая, что вы знаете, какой доход вы ожидаете от определенных инвестиций, используйте это ожидание в качестве предположения.
• Если вы получаете более одного IRR для одного и того же денежного потока, выберите тот, который ближе всего к стоимости капитала вашей компании, в качестве «истинного» IRR.
• Используйте функцию MIRR, чтобы избежать проблемы множественных IRR.

Нерегулярные интервалы денежных потоков

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



Различные ставки заимствования и реинвестирования

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


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


Вас также может заинтересовать

Функция IRR Excel и другие способы расчета IRR в Excel

Функция IRR Excel и другие способы расчета IRR в Excel

Функция IRR Excel и другие способы расчета IRR в Excel 8 февраля 2020 г.

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

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

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

IRR представляет собой годовую эффективную совокупную ставку доходности, обозначаемую буквой «r». Математически это можно сформулировать так:

Как лучше всего рассчитать IRR набора денежных потоков?

В этом руководстве мы обсудим возможные методы расчета IRR:

• Использование метода проб и ошибок (для облегчения понимания)
• Использование одномерной таблицы данных
• Расчет IRR с использованием функции Excel NPV()
• Расчет IRR с использованием XIRR

Загружаемая выше рабочая книга Excel была подготовлена ​​для демонстрации расчета IRR. Для удобства мы рекомендуем вам загрузить книгу во время чтения этого руководства.

 Метод проб и ошибок для расчета IRR в Excel

IRR — это ставка дисконтирования, для которой чистая приведенная стоимость равна нулю, и ее можно рассчитать методом проб и ошибок.

• NPV(IRR(…), …) = 0
• XNPV(XIRR(…), …) = 0

Метод проб и ошибок выглядит следующим образом:

• Начните с предположения ставки дисконтирования «r»
• Рассчитайте чистую приведенную стоимость, используя «r» — обратитесь к нашим учебным пособиям о том, как рассчитать чистую приведенную стоимость с формулами Excel или без них
• Если чистая приведенная стоимость близка к нулю, то ‘r’ — это IRR
• Если чистая приведенная стоимость положительная, увеличьте ‘r’
• Если чистая приведенная стоимость отрицательна, уменьшите ‘r’
• Продолжайте процесс, пока чистая приведенная стоимость не достигнет нуля

Используя пример в рабочей тетради:

• См. снимок экрана 1: чистая приведенная стоимость, рассчитанная при ставке дисконтирования 10%, составляет 19 долларов США.0,31 млн, следовательно, мы знаем, что IRR должен быть больше 10%
• Обратитесь к скриншоту 2: Давайте теперь попробуем «r» равным 18%. Чистая приведенная стоимость составляет -0,87 миллиона долларов. Он отрицательный, но на этот раз чистая приведенная стоимость ближе к нулю.
• Таким образом, можно предположить, что IRR должен быть чуть ниже 18%.
• Процесс проб и ошибок может быть более утомительным, чем сам расчет NPV.
• Далее мы покажем вам подход к угадыванию IRR с помощью одномерной таблицы данных.

Использование одномерной таблицы данных для определения корня

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

Давайте нанесите приведенную выше таблицу данных на график – см. скриншот 4. Становится ясно, что IRR находится между 17,50% и 18,00%. Если быть точным, IRR составляет 17,53%, что мы могли бы получить с помощью функции Excel.

Использование IRR() в Excel

IRR() синтаксис:

IRR(CF1, CF2, …)

Мы могли бы рассчитать IRR, используя функцию Excel IRR(), но она, как и NPV(), имеет некоторые ограничения:

• Денежные потоки (CFi) должны быть равномерно распределены по времени и встречаться в конце каждого периода
• CFi необходимо вводить в правильной последовательности

Использование функции ЧИСТВНДОХ() В Excel

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

ЧИСТВНДОХ() — это встроенная функция в Excel, синтаксис которой следующий: ЧИСТВНДОХ(CFi, даты)

Как показано на снимке экрана 5, рассчитанное значение внутренней нормы доходности составляет 17,53%. Мы могли бы перепроверить расчет, вернув внутреннюю норму доходности обратно в расчет чистой приведенной стоимости в качестве ставки дисконтирования, для которой чистая приведенная стоимость равна нулю.

Общие области применения IRR

Составление бюджета капиталовложений: инструмент принятия инвестиционных решений

IRR — это метрика, позволяющая решить, стоит ли инвестировать в отдельный проект. стоимость капитала и отклоняется, если эта IRR меньше стоимости капитала.

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

Оптимизация тарифов

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