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

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

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

Разъяснение внутренней нормы прибыли (IRR)

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

Позвольте мне объяснить IRR на простом примере:

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

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

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

Или, если вы планируете взять ссуду или привлечь капитал и купить этот проект за 50 000 долларов, убедитесь, что стоимость капитала составляет менее 15% (иначе вы заплатите больше как стоимость капитала, чем вы зарабатываете из проект).

Функция IRR в Excel — синтаксис

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

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

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

Вот некоторые важные предпосылки для использования функции

• Функция IRR будет рассматривать только числа в указанном диапазоне ячеек. Любые логические значения или текстовые строки в массиве или ссылочном аргументе будут проигнорированы.
• Суммы в параметре значений должны быть отформатированы как числа
• Параметр «guess» должен быть в процентах в десятичном формате (если он предусмотрен).
• Ячейка, в которой отображается результат функции, должна быть отформатирована как процент
• Суммы происходят в регулярные временные интервалы (месяцы, кварталы, годы)
• Одна сумма должна быть отрицательный денежный поток (представляющий первоначальные инвестиции), а другие суммы должны быть положительный денежные потоки, представляющие периодические доходы
• Все суммы должны быть в хронологический порядок потому что функция вычисляет результат на основе порядка сумм

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

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

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

Предположим, у вас есть набор данных, как показано ниже, где у нас есть начальные инвестиции в размере 30 000 долларов США, а затем варьируемый денежный поток / доход от них в течение следующих шести лет.

Для этих данных нам нужно рассчитать IRR, что можно сделать, используя следующую формулу:
= IRR (D2: D8)

Результат функции: 8.22%, которая представляет собой IRR денежного потока через шесть лет.

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

Узнайте, когда инвестиции принесут положительную внутреннюю норму доходности

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

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

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

Для этого вместо расчета IRR для всего проекта мы найдем IRR для каждого года.

Это можно сделать, используя приведенную ниже формулу в ячейке D3, а затем скопировав ее для всех ячеек в столбце.
= IRR ($ C $ 2: C3)

Как видите, IRR после года 1 (значения D2: D3) составляет -80%, после года 2 (D2: D4) -52% и т. Д.

Этот обзор показывает нам, что инвестиции в размере 30 000 долларов США с заданным денежным потоком имеют положительную внутреннюю норму доходности после пятого года.

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

Обратите внимание, что в приведенной выше формуле ссылка на диапазон смешана, то есть первая ссылка на ячейку ($ C $ 2) заблокирована знаками доллара перед номером строки и буквой столбца, а вторая ссылка (C3) не является заблокирован.

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

Использование функции IRR для сравнения нескольких проектов

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

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

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

Как вы видете:

• Проект 1 имеет IRR в размере 5.60%
• Проект 2 имеет IRR в размере 1.75%
• Проект 3 имеет IRR в размере 14.71%.

Если мы предположим, что стоимость капитала составляет 4,50%, мы можем сделать вывод, что вложение 2 неприемлемо (так как приведет к убыткам), в то время как вложение 3 является наиболее прибыльным с самой высокой внутренней нормой доходности.

Поэтому, если вам нужно принять решение об инвестировании только в один проект, вам следует выбрать проект 3, а если вы можете инвестировать более чем в один проект, вы можете инвестировать в проекты 1 и 3.

Определение: Если вам интересно, какова стоимость капитала, это деньги, которые вам придется заплатить, чтобы получить доступ к деньгам. Например, если вы берете в долг 100 тысяч долларов под 4,5% годовых, ваша стоимость капитала составляет 4,5%. Аналогичным образом, если вы выпускаете привилегированные акции, обещая Возврат 5%, чтобы получить 100K, ваша стоимость капитала будет 5%. В реальных сценариях компания обычно привлекает деньги из различных источников, и ее Стоимость капитала — это средневзвешенная величина всех этих источников капитала.

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

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

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

Например, ниже представлен набор данных, в котором денежные потоки происходят с нерегулярными интервалами (см. Даты в столбце A).

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

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

В этом примере IRR можно рассчитать по следующей формуле:
= XIRR (B2: B8; A2: A8)

В приведенной выше формуле денежные потоки указаны как первый аргумент, а даты указаны как второй аргумент.

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

IRR против NPV — что лучше?

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

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

Если это значение окажется выше вашего первоначального оттока, значит, проект прибыльный, иначе проект не прибыльный.

С другой стороны, когда вы рассчитываете IRR для проекта, он сообщает вам, какой будет норма прибыли для всего будущего денежного потока, чтобы вы получили сумму, эквивалентную текущему оттоку. Например, если вы сегодня тратите 100 тысяч долларов на проект с IRR или 10%, это означает, что если вы дисконтируете все будущие денежные потоки по ставке дисконтирования 10%, вы получите 100 тысяч долларов.

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

В таком случае лучше всего воспользоваться рекомендацией, полученной с помощью метода NPV.

В целом метод IRR имеет ряд недостатков, которые делают метод NPV более надежным:

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

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

В этом уроке я показал вам, как использовать Функция ВНД в Excel. Я также рассказал, как рассчитать IRR в случае нерегулярных денежных потоков с помощью функции XIRR.

Надеюсь, вы нашли этот урок полезным!