Как посчитать irr в excel

Обновлено: 05.07.2024

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

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

Это руководство охватывает:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Для этих данных нам нужно рассчитать IRR, что можно сделать по следующей формуле:

Результат функции – 8,22 %, что соответствует внутренней норме доходности денежного потока через шесть лет.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Приведенная выше формула дает внутреннюю норму доходности для проекта 1. Точно так же вы можете рассчитать внутреннюю норму доходности для двух других проектов.

В проекте 1 внутренняя норма доходности составляет 5,60 %.
В проекте 2 внутренняя норма доходности составляет 1,75 %.
Проект 3 имеет внутреннюю норму доходности 14,71%.

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

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

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

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

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

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

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

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

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

В этом примере IRR можно рассчитать по следующей формуле:

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

IRR vs NPV – что лучше?

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

ЧПС — это метод чистой приведенной стоимости, при котором вы оцениваете все будущие денежные потоки и рассчитываете чистую приведенную стоимость всех этих денежных потоков.

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

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

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

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

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

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

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

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

Кэролайн Бэнтон более 6 лет работает внештатным автором статей о бизнесе и финансах. Она также пишет биографии для Story Terrace.

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

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

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

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

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

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

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

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

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

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

Что такое внутренняя норма доходности?

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

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

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

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

На изображении ниже мы рассчитываем IRR.

Для этого мы просто используем функцию Excel IRR:

Модифицированная внутренняя норма доходности (MIRR)

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

На изображении ниже мы рассчитываем IRR инвестиции, как и в предыдущем примере, но принимая во внимание, что компания займет деньги, чтобы вложить их в инвестиции (отрицательные денежные потоки) по ставке, отличной от ставки, по которой он будет реинвестировать заработанные деньги (положительный денежный поток). Диапазон от C5 до E5 представляет диапазон денежных потоков инвестиции, а ячейки D10 и D11 представляют ставку по корпоративным облигациям и ставку по инвестициям.

На изображении ниже показана формула MIRR в Excel. Мы вычисляем MIRR, найденный в предыдущем примере, с фактическим определением MIRR. Это дает тот же результат: 56,98%.

Внутренняя норма доходности в разные моменты времени (XIRR)

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

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

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

А. Excel предлагает три функции для расчета внутренней нормы прибыли, и я рекомендую вам использовать все три. Проблема с использованием математики для расчета внутренней нормы прибыли заключается в том, что необходимые расчеты сложны и требуют много времени. По сути, математическое решение включает в себя расчет чистой приведенной стоимости (NPV) для каждой суммы денежного потока (в серии денежных потоков) с использованием различных предполагаемых процентных ставок на основе проб и ошибок, а затем эти NPV складываются вместе. . Этот процесс повторяется с использованием различных процентных ставок, пока вы не найдете/не наткнетесь на точную процентную ставку, при которой сумма чистой приведенной стоимости равна нулю. Процентная ставка, которая дает нулевую сумму NPV, затем объявляется внутренней нормой доходности.

<р>
1. Функция IRR Excel. Функция IRR Excel вычисляет внутреннюю норму доходности для ряда денежных потоков, предполагая периоды платежей одинакового размера. Используя пример данных, показанный выше, формула IRR будет =IRR(D2:D14,.1)*12, что дает внутреннюю норму доходности 12,22%. Однако, поскольку в одних месяцах 31 день, а в других – 30 или меньше, продолжительность месячных периодов не одинакова, поэтому IRR всегда будет возвращать немного ошибочный результат, если задействовано несколько месячных периодов.

<р>2. Функция ЧИСТНДОХ в Excel. Функция ЧИСТВНР в Excel вычисляет более точную внутреннюю норму доходности, поскольку она учитывает периоды времени разного размера. Чтобы использовать эту функцию, вы должны указать как суммы денежных потоков, так и конкретные даты, когда эти денежные потоки выплачиваются. В приведенном ниже примере формула ЧИСТВНРД будет =ЧИСТВНРД(D2:D14,B2:B14,.1) , что дает внутреннюю норму доходности 12,97%.

<р>3. Функция Excel MIRR. Функция Excel MIRR (модифицированная внутренняя норма доходности) работает аналогично функции IRR, за исключением того, что она также учитывает стоимость заимствования первоначальных инвестиционных средств, а также сложные проценты, полученные путем реинвестирования каждого денежного потока. Функция MIRR достаточно гибкая, чтобы учитывать отдельные процентные ставки для заимствования и инвестирования денежных средств. Поскольку функция MIRR рассчитывает сложные проценты на доходы проекта или дефицит денежных средств, результирующая внутренняя норма доходности обычно значительно отличается от внутренней нормы доходности, полученной с помощью функций IRR или XIRR.В примере слева формула MIRR будет =MIRR(D2:D14,D16,D17)*12, что дает внутреннюю норму доходности 17,68%.

Примечание. Некоторые CPA утверждают, что результаты функции MIRR менее достоверны, поскольку денежные потоки проекта редко полностью реинвестируются. Однако умные CPA могут компенсировать частичные уровни инвестиций, просто регулируя процентную ставку в соответствии с ожидаемыми уровнями реинвестирования. Например, если предполагается, что реинвестированные денежные потоки принесут 3,0%, но ожидается, что будет реинвестирована только половина денежных потоков, то CPA может использовать процентную ставку 1,5% (половину от 3,0%) в качестве процентной ставки для компенсировать частичное инвестирование денежных потоков.

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

<р>1. Требуются отрицательные и положительные значения денежного потока. Для выполнения всех трех функций требуется как минимум один отрицательный и хотя бы один положительный денежный поток. Первым числом в ряду денежных потоков обычно является отрицательное число, которое считается первоначальным вложением в проект.

<р>2. Месячная и годовая доходность. При расчете IRR или MIRR ежемесячных денежных потоков результаты необходимо умножить на 12, чтобы получить годовой доход; однако функция ЧВНПГ автоматически дает годовой результат, который не нужно умножать. При расчете IRR, XIRR или MIRR годовых денежных потоков результаты не нужно умножать. (Поскольку функция ЧИСТВНДОХ включает диапазоны дат, она автоматически пересчитывает результаты в годовом исчислении.)

<р>5. Если вы не введете предположение. Если вы не введете предположение для функции IRR или XIRR, Excel примет 0,1 или 10 % в качестве начального предположения.

<р>6. Даты. Даты, которые вы вводите, должны быть введены как значения дат, а не как текст, чтобы функция ЧССНПГ точно использовала эти даты.

Об авторе

Примечание. Инструкции для Microsoft Office в разделе «Технологические вопросы и ответы» относятся к версиям с 2007 по 2016 год, если не указано иное.

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

Простой пример внутренней нормы доходности

Например, проект A требует первоначальных инвестиций в размере 100 долларов США (ячейка B5).

<р>1. Мы ожидаем прибыль в размере 0 долларов США в конце первого периода, прибыль в размере 0 долларов США в конце второго периода и прибыль в размере 152,09 доллара США в конце третьего периода.

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

<р>2. Правильная формула NPV в Excel использует функцию NPV для расчета приведенной стоимости ряда будущих денежных потоков и вычитает первоначальные инвестиции.

Объяснение: положительная чистая приведенная стоимость указывает на то, что норма прибыли проекта превышает ставку дисконтирования. Другими словами, лучше вложить деньги в проект А, чем положить деньги на сберегательный счет с процентной ставкой 10%.

<р>3. Приведенная ниже функция IRR вычисляет внутреннюю норму доходности проекта А.

<р>4. Внутренняя норма доходности – это ставка дисконтирования, при которой чистая текущая стоимость равна нулю. Чтобы наглядно это увидеть, замените ставку дисконтирования 10% в ячейке B2 на 15%.

Объяснение: чистая приведенная стоимость, равная 0, указывает на то, что проект обеспечивает норму прибыли, равную ставке дисконта. Другими словами, оба варианта — инвестирование денег в проект А или размещение денег на высокодоходном сберегательном счете с процентной ставкой 15 % — приносят одинаковую прибыль.

<р>5. Мы можем это проверить. Предположим, вы положили 100 долларов в банк. Сколько будут стоить ваши инвестиции через 3 года при годовой процентной ставке 15%? Ответ: 152,09 доллара США.

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

Настоящие ценности

Например, проект Б требует первоначальных инвестиций в размере 100 долларов США (ячейка B5). Мы ожидаем прибыль в размере 25 долларов США в конце первого периода, прибыль в размере 50 долларов США в конце второго периода и прибыль в размере 152,09 доллара США в конце третьего периода.

<р>1. Приведенная ниже функция IRR вычисляет внутреннюю норму доходности проекта B.

<р>2. Опять же, внутренняя норма доходности — это ставка дисконтирования, которая делает чистую приведенную стоимость равной нулю. Чтобы наглядно это увидеть, замените ставку дисконтирования 15% в ячейке B2 на 39%.

Объяснение: чистая приведенная стоимость, равная 0, указывает на то, что проект обеспечивает норму прибыли, равную ставке дисконта. Другими словами, оба варианта — инвестирование денег в проект Б или размещение денег на высокодоходном сберегательном счете с процентной ставкой 39 % — приносят одинаковую прибыль.

<р>3. Мы можем это проверить. Во-первых, мы рассчитываем текущую стоимость (pv) каждого денежного потока. Затем мы суммируем эти значения.

Объяснение: вместо того, чтобы инвестировать 100 долларов США в проект Б, вы также можете положить 17,95 долларов США на сберегательный счет на 1 год, 25,77 долларов США на сберегательный счет на 2 года и 56,28 долларов США на сберегательный счет на три года с годовой процентной ставкой, равной к IRR (39%).

Правило IRR

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

<р>1. Приведенная ниже функция IRR вычисляет внутреннюю норму доходности проекта X.

Вывод: если требуемая норма доходности равна 15 %, вы должны принять этот проект, поскольку IRR этого проекта составляет 29 %.

<р>2. Приведенная ниже функция IRR вычисляет внутреннюю норму доходности проекта Y.

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

<р>3. Приведенная ниже функция IRR рассчитывает внутреннюю норму доходности проекта Z.

Вывод: чем выше IRR, тем лучше. У проекта Z более высокая IRR, чем у проекта Y, но денежные потоки намного ниже.

Читайте также: