Внутренняя норма доходности (irr). формула и пример расчета в excel

Пример расчета IRR

В рамках примера, используем аппроксимирующую формулу для расчета IRR. Рассмотрим следующие данные.

Период	Исходные данные (0%)	Дисконт 10%	Дисконт 15%	Дисконт 20%
-1000	-1000	-1000	-1000
1	550	500	478,26	458,33
2	-600	-495,87	-453,69	-416,67
3	1550	1164,54	1019,15	896,99
NPV	500	168,67	43,72	-61,34

Стоит отметить, что 5% это достаточно большой интервал и в примере он приведен лишь для простоты вычислений.

Как видите, IRR находится между 15% и 20%. Используем аппроксимирующую формулу.

IRR = 0,15 + (0,2 — 0,15) * 43,72 / (43,72 — (-61,34)) = 0,17 (17%). Если же использовать функцию IRR из OpenOffice Calc, то результат будет 16,98%, что достаточно близко.

Анализ полученных результатов доходности

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

Часто этот показатель путают с процентной ставкой по кредиту. В этом случае можно узнать эффективность привлечения займа. Целесообразность инвестиций определяется из соотношения прибыльности и нормы ВСД:

• ВСД = П — предельно допустимый уровень вложений, для которого нужно предварительно пересмотреть первоначальную сумму инвестиций, откорректировать сроки и потоки;
• ВСД > П — вложения полностью покроют затраты на проект, инвестирование возможно после проведения подробного финансового анализа;
• ВСД < П — прибыль будет меньше расходов, предпринимателю стоит отказаться от финансирования плана;
• ВСД1 < ВСД2 — сравнение нескольких возможных инвестиций в пользу одной из них.

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

Определение

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

Для потока платежей CF, где CFt{\displaystyle CF_{t}} — платёж через t{\displaystyle t} лет (t=1,…,N{\displaystyle t=1,…,N}) и начальной инвестиции в размере IC=−CF{\displaystyle IC=-CF_{0}} внутренняя норма доходности IRR{\displaystyle IRR} рассчитывается из выражения:

NPV=−IC+∑t=1NCFt(1+IRR)t={\displaystyle NPV=-IC+\sum _{t=1}^{N}{\frac {CF_{t}}{(1+IRR)^{t}}}=0}

или

IC=∑t=1NCFt(1+IRR)t{\displaystyle IC=\sum _{t=1}^{N}{\frac {CF_{t}}{(1+IRR)^{t}}}}

В табличных процессорах в состав финансовых функций входит функция для вычисления внутренней нормы доходности. В OpenOffice.org Calc для вычисления внутренней нормы доходности применяется функция IRR. Можно определить IRR с помощью опции «Подбор параметров» Microsoft Excel или OpenOffice.org Calc.

 Пример:
 Год     Поток платежей
  0        -100
  1        +120

 Расчёт NPV:
 i = процентная ставка
 NPV = -100 +120/[(1+i/100)^1]

 Расчёт IRR (в процентах):
 NPV = 0
 -100 +120/[(1+IRR/100)^1] = 0
 IRR = 20

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

Формула внутренней нормы доходности

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

где IRR — это внутренняя норма доходности,

NPV — чистый дисконтированный доход,

IC — это первоначальные инвестиции (по сути, нулевой денежный поток),

СУММА — это стандартная функция суммирования всех элементов,

CF_i — денежный поток за период i,

i — периоды от 1 до n (количество прогнозируемых периодов).

Стоит знать, что IRR не всегда может быть найдена. К примеру, если инвестиционный проект изначально подразумевает одни убытки, то, как бы вы не уменьшали или увеличивали ставку дисконтирования, NPV (ЧДД) никогда не будет равно 0.

Рассчитывать данный коэффициент лучше через Excel или OpenOffice (в них встроены формулы). Так же можно использовать графический метод.

Или аппроксимирующую формулу:

где IRR — внутренняя норма доходности,

NPV — это функция расчета чистого дисконтированного дохода,

r₁ — ставка дисконтирования, при которой NPV > 0,

r₂ — ставка дисконтирования, при которой NPV < 0.

Важно отметить, что чем ближе к нулю находятся значения NPV(r1) и NPV(r2), тем более точным получается значение

Внутренняя норма доходности финансовых инструментов

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

Для определения внутренней нормы доходности облигации часто используют приближённую «купеческую» формулу:
r=f+A−PTA+P2{\displaystyle r={\frac {f+{\frac {A-P}{T}}}{\frac {A+P}{2}}}},
где

• A{\displaystyle A} — номинал облигации;
• P{\displaystyle P} — текущая рыночная цена облигации;
• f{\displaystyle f} — годовой купонный доход;
• T{\displaystyle T} (в годах) — срок до погашения облигации.

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

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

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

Пример расчета MIRR

Чтобы понять, в чем смысл MIRR, рассчитаем его для тех же данных, что и в примере для IRR. Единственно, необходимо добавить ставки дисконтирования. Будем считать, что исходные суммы можно было бы вложить под 12% (но, только если полностью). А вот при реинвестировании можно вложить только под 8%.

Период	Исходные данные (0%)	Дисконт 12% (отр.)	Дисконт 8% (пол.)
-1000	1000
1	550	641,52
2	-600	478,32
3	1550	1550
SUM	500	1478,32	2191,52
MIRR			14,02%

Как видите, прирост с учетом дисконтов получается 14,02% = ( (2191,52/1478,32)1/3 — 1 ) * 100%. Кстати, если ставки считать одинаковыми, то для 8% было бы 13,11%, а для 12% было бы 14,86%. В любых из трех вариантов, MIRR был бы больше, поэтому инвестиционный проект можно было бы рассматривать.

Применение в ссудном проценте и использование эффективной процентной ставки потребителем

По требованиям ЦБ, банки обязаны указывать ЭПС — эффективную процентную ставку (рассчитанную по формуле, приведённой в статье первой). Данная ставка может быть самостоятельно рассчитана заёмщиком с помощью редактора электронных таблиц и формулы IRR (в Microsoft Excel ВСД). Из-за особенности формулы нужно результат в той же ячейке умножить на период платежей (при ежемесячных платежах — 12, при дневных платежах или нестандартном графике — 365) без округления. Например, по кредиту «10-10-10» записывается
−9000 (то есть −10000+1000 стоимость товара минус сумма, уплаченная в кассу, на которую не предоставляется кредит)
1000 (в ячейках A2-A11, то есть 10 раз)

Для большей точности, используя формулу XIRR (openoffice)
−9000 15.01.2011
1000 15.02.2011
1000 15.03.2011
… (10 раз)
XIRR(A1:A11;B1:B11)=26.44 %

То есть использование аннуитетного платежа (в каждое число периода) повысило эффективную ставку в 1,13 раз.

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

Внутренняя норма доходности: основная формула

В науке о финансах существует формула, по которой вычисляется ВНД. Для примерной цифры внутренней нормы доходности следует использовать уравнение вида NPV = -IC + CF/(1 + IRR)^t = 0, где:

• NPV — размер чистой прибыли;
• IC — начальный размер инвестиций;
• CF — приток средств за определенное время;
• IRR — ВНД.

Чтобы разобраться детальнее, нужно изучить вычисление внутренней нормы доходности, то есть, IRR на примере. Сумма вложений в план составляет 1 млн рублей. Если верить прогнозу, то в первый год прибыль дойдет до 100 000 р., во второй — до 150 тысяч, в третий — до 200 000, в четвертый — до 270 000 р.

Сначала необходимо использовать методику итерации для расчета минимального показателя ВНД. Понадобится знать количество лет, изначальную инвестсумму и доход за каждый год, поделенный на 1000. В этом примере формула будет выглядеть так: (((0,1 + 0,15 + 0,2 + 0,27) / 0,5)^4) -1 = 0,10 или 10%. То есть минимальная ставка ВНД означает 10%. Затем нужно посчитать максимальный показатель: 0,72/0,5 — 1 = 0,44 или 44%.

Чистую прибыль определяют для каждого предельного значения отдельно. Для первого коэффициента она составляет: -1 + 0,1/1,1 + 0,15/1,21 + 0,2/1,331 + 0,27/1,4641 = -0,45. Для второго формула другая: -1 + 0,1/0,44 + 0,15/2,07 + 0,2/2,985 + 0,27/4,299 = 0,73. После этого рассчитывают величину ВНД для примера: (0,1 + (-0,45)/(-0,45 — 0,73)) х (0,44 — 0,1) = 0,23 или 23%.

Примеры расчета IRR

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

Таблица 2. Сведения об инвестициях и денежных потоках по проектам

	Проект №1	Проект №2
Годы	Денежный поток (CF)
-1 500 000	-1 500 000
1	65 700	150 900
2	235 800	238 400
3	478 900	670 900
4	601 290	450 900
5	778 200	238 650
6	800 500	629 001

На первый взгляд сложно определить:

• какой из них выгоден инвестору;
• под какой процент привлекать заемное финансирование.

Для того чтобы дать ответы на эти вопросы, необходимо перенести информацию в табличный редактор Excel, после чего выполнить такие действия:

• поместить курсор в свободную ячейку;
• выбрать финансовую функцию ВСД (внутренняя ставка доходности);
• в поле «Значения» указать массив данных от первоначальных инвестиций до последнего денежного поступления.

Рисунок 2. Порядок заполнения полей финансовой функции ВСД (Проект №1)

В примере функцию ВСД необходимо использовать дважды – так можно вычислить IRR по каждому проекту.

Рисунок 3. Итоги расчета внутренней нормы доходности в Excel

Вывод! Проведенный расчет показал, что более выгодным представляется Проект №1, поскольку он сулит доходность, равную 17%. Кроме того, диапазон выбора заемных средств в этом случае шире: кредиты можно привлекать по ставке не более 17% (для сравнения, по Проекту №2 – до 13%).

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

Применение инструментария Excel для определения величины целесообразно:

• погрешность вычислений может составлять 0,00001%, если это значение указать в поле «Предположение» функции ВСД (в стандартном варианте погрешность составляет 0,1%);
• функция применима к наиболее удобной форме отображения денежных потоков – в хронологическом порядке (по годам, месяцам и др.);
• денежные потоки могут быть положительными или отрицательными.

Важный момент! Среди денежных потоков по проекту хотя бы один должен быть положительным, иначе система сообщит об ошибке вычислений.

Определение и формула IRR.

Определение IRR можно изобразить математически следующим образом:

\( \mathbf { NPV = CF_0 + {CF_1 \over (1+IRR)^1}+{CF_2 \over (1+IRR)^2}+\cdots+ {CF_N \over (1+IRR)^N}=0 } \)   (формула 2)

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

Для некоторых простых проектов денежный поток CF при t = 0 (в текущий момент времени) отражает единственный разовый отток капитала или первоначальные инвестиции; а денежные потоки после t = 0 являются положительными денежными притоками от инвестиций.

В таких случаях мы можем сказать, что:CF = -Инвестиции (отрицательный знак указывает на отток).

Таким образом, мы можем преобразовать уравнение формулы 2 в форму, которая полезна в таких случаях:

Инвестиции = \( \mathbf { {CF_1 \over (1+IRR)^1}+{CF_2 \over (1+IRR)^2}+\cdots+ {CF_N \over (1+IRR)^N} } \)

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

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

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

What is Internal Rate of Return?

The Internal Rate of Return is the discount rate that makes the net present value (NPV) of all cash flows from a particular project equal to zero. It is also known as «economic rate of return» and «discounted cash flow rate of return». «Internal» in the name refers to the omission of external factors like capital cost, currency inflation, etc. By definition it gives more weight to earlier cash flows than to later cash flows reflecting the time preference of investors.

IRR is used to estimate the profitability of potential investments: the higher it is, the more desirable it is to undertake the project, while the lower it is, the more risky and overall undesirable. It is often used to rank prospective projects on a relatively even basis.

In general, it is best to combine IRR with other metrics such as NPV. It should be used with caution since comparing it across projects of widely different expected lengths can be misleading. Another issue to be taken into account is that IRR does not incorporate reinvestment rates which are more directly tied to the capital cost. Some propose the modified internal rate of return (MIRR) for cases when cost of capital is to be accounted for.

Формула расчета IRR

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

В приведенной формуле присутствуют такие показатели, как:

• CF – суммарный денежный поток за период t;
• t – порядковый номер периода;
• i – ставка дисконтирования денежного потока (ставка приведения);
• IC – сумма первоначальных инвестиций.

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

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

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

Рисунок 1. Пример использования графического метода при расчете IRR

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

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

Таблица 1. Методы расчета IRR

№	Метод
1	Ручной (финансовый калькулятор)	Громоздкие расчеты, необходимость решения уравнения с корнем n-ой степени
2	Графический	Наглядный, но представляет неточные результаты
3	Логарифмический	Точный, но сложный. Требует знания свойств логарифмов и действий с ними
4	Автоматизированный (Excel)	Точный (погрешность 0,00001%), простой, оперативный

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

Модифицированная внутренняя норма доходности (Modified Internal Rate of Return, MIRR) — это минимальный уровень доходности проекта, при осуществлении реинвестирования, выраженный как ставка дисконтирования, при которой суммарная приведенная стоимость доходов от инвестиций равна стоимости этих инвестиций. Звучит «громоздко», но далее станет более понятно.

Прежде, чем приводить формулу, приведу несколько пояснений:

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

2

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

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

3. У двух схожих проектов легко может наблюдаться ситуация, когда IRR1 > IRR2, но в одном диапазоне NPV1 < NPV2, а в ином NPV1 > NPV2. Это означает, что у первого проекта больший запас «прочности» (уменьшение денежных потоков или увеличение ставки дисконта меньше сказывается), однако реальная эффективность зависит от процента дисконта. Иными словами, IRR не всегда отражает действительную привлекательность проекта (так как это расчетное теоретическое значение).

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

Логика происходит из следующих моментов:

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

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

3. Для отрицательных и положительных денежных потоков определяется своя ставка дисконтирования.

4. Пересчитанные доходы и вложения должны соответствовать формуле сложных процентов. Вот этот процент и является MIRR.

Формула

где MIRR — модифицированная внутренняя норма доходности,

SCOF — это сумма всех расходов, пересчитанная к исходному периоду с dOut,

SCIF — это сумма всех доходов, пересчитанная к последнему периоду с учетом реинвестирования по ставке dIn,

COF_i — это отрицательный денежный поток (расходы, инвестиции) в период i,

CIF_i — это положительный денежный поток (доход) в период i,

dOut — это ставка дисконтирования для оттоков,

dIn — это ставка реинвестирования,

i — это периоды от 0 до n (для положительных потоков это 1, так как в исходном периоде доходов нет).

Если ставки дисконтирования одинаковы, то критерий принятия решения: MIRR > d. Если же ставки дисконтирования различаются, то критерий: MIRR > dOut (иначе рост меньше, чем обесценивание капитала).

Способы расчета ЧПС

При помощи чистого дисконт дохода проекта можно дать оценку инвестиционному КПД. Формула подсчёта чистой текущей стоимости бизнеса выглядит следующим образом:

, где:

N — число временных промежутков, за которые необходимо рассчитать инвестпроект;
t — время, за которое принимается чистый финпоток;
CF — ∑ чистых финпоступлений;
Программа Excel даёт возможность посчитать чистую текущую инвестиционную стоимость несколькими способами. Формула расчёта NPV будет работать только при корректно заданной ставке дисконта и выделенном «коридоре» NPV или ЧДП. Для начала работы в Excel выбирается вкладка «формула», после этого «финансовые» в заключении «ЧПС».В ячейке «аргументы функции» выбирают строку «Ставка» и указывают величину дисконтставки. В строчке «Значение 1» вписывают приведённые финансовые потоки NPV

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

С использованием калькулятора

Дисконтная ставка проекта для NPV может быть рассчитана несколькими способами. К примеру, взять её эквивалентной рентабельности по минимальной инвестиционной доходности консервативных вложений средств. Или же принять в виде дисконта для NPV процентную ставку кредита либо индекс процентного преобразования величины цен за конкретный временной отрезок. «Ручной» мониторинг осуществляется поэтапно:

1. Считается сумма первоначальных капиталовложений (к ним относятся все денежные средства, которые инвестор планирует вложить на начальной стадии, в том числе непрямые затраты).
2. Выбирается временной отрезок (t) для инвестиционного анализирования.
3. Определяется финансовый поток проекта за все временные отрезки. Если необходимо найти чистый дисконтированный доход на 6 лет, то и инвестиционных финансовых потоков будет также 6. Между ними может быть разница по объёму, но они могут быть и одинаковыми.
4. Устанавливается дисконт ставка.
5. Уменьшаются финпотоки за каждый год на дисконтставку (дисконтирование финпоступлений).
6. Выполняется суммирование всех полученных инвестиционных дисконтпотоков. Из результата вычитается ∑ стартовых капиталовложений.

Произвести математический расчёт чистой текущей стоимости проекта довольно просто. Достаточно знать все переменные величины для определения чистого дисконтированного дохода. Но именно на этом этапе у большинства инвесторов возникают трудности.

IRR formula

If you wonder how to calculate the Internal Rate of Return by yourself or using an Excel spreadsheet, you would be surprised to discover that there is no analytical solution to the issue and the only way to calculate it is programmatically or by using tools such as our IRR calculator above. It is still based on the NPV formula:

where r is the discount rate and t is the number of cash flow periods, C is the initial investment while C_t is the return during period t. One needs to replace NPV with zero and solve for r, for which there is no analytical solution since one can’t single out r in one side of the equation. For this reason, our calculator performs a recursive search until it finds a value of r which results in an NPV close to zero.