Пример расчета irr: Внутренняя норма рентабельности (IRR): формула расчета показателя

A

B

C

D

E

1

Размер кредита в процентах

10 %

2

Уровень реинвестирования

12%

3

Период (год) Т

Первоначальные затраты

Денежный доход

Денежный расход

Денежный поток

4

0

200000 р.

— р.

200000 р.

200000 р.

5

1

— р.

50000 р.

30000 р.

20000 р.

6

2

— р.

60000 р.

33000 р.

27000 р.

7

3

— р.

45000 р.

28000 р.

17000 р.

8

4

— р.

50000 р.

15000 р.

35000 р.

9

5

— р.

53000 р.

20000 р.

33000 р.

10

6

— р.

47000 р.

18000 р.

29000 р.

11

7

— р.

62000 р.

25000 р.

37000 р.

12

8

— р.

70000 р.

30000 р.

40000 р.

13

9

— р.

64000 р.

33000 р.

31000 р.

14

MIRR

9%

A

B

C

D

E

1

Период (год) Т

Первоначальные затраты

Денежный доход

Денежный расход

Денежный поток

2

0

200 000 р.

— р.

200000 р.

200000 р.

3

1

— р.

50000 р.

30000 р.

20000 р.

4

2

— р.

60000 р.

33000 р.

27000 р.

5

3

— р.

45000 р.

28000 р.

17000 р.

6

4

— р.

50000 р.

15000 р.

35000 р.

7

5

— р.

53000 р.

20000 р.

33000 р.

8

6

— р.

47000 р.

18000 р.

29000 р.

9

7

— р.

62000 р.

25000 р.

37000 р.

10

8

— р.

70000 р.

30 000 р.

40000 р.

11

9

— р.

64000 р.

33000 р.

31000 р.

12

IRR

6%

A

B

C

D

E

F

1

Период (год) Т

Первоначальные затраты

Денежный доход

Денежный расход

Денежный поток

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

2

0

200000 р.

— р.

200000 р.

200000 р.

3

1

— р.

50000 р.

20 000 р.

20000 р.

20000 р.

4

2

— р.

60000 р.

20000 р.

27000 р.

27000 р.

5

3

— р.

45000 р.

20000 р.

17000 р.

17000 р.

6

4

— р.

50000 р.

20000 р.

35000 р.

35000 р.

7

5

— р.

53000 р.

20000 р.

33000 р.

33 000 р.

8

6

— р.

47000 р.

20000 р.

29000 р.

29000 р.

9

7

— р.

62000 р.

20000 р.

37000 р.

37000 р.

10

8

— р.

70000 р.A5».

Тогда для NPV получается формула «=СУММ(F5:F13)-B7».

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

В появившемся окне заполняют строки «Установить целевую ячейку», указав адрес формулы расчета NPV, т. е. +$F$16. Затем:

• выбирают значение для данной ячейки «0»;
• в окно «Изменения ячейки» вносят параметр +$F$17, т. е. значение внутренней нормы доходности.

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

MIRR

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

где:

• MIRR — внутренняя норма доходности инвестпроекта;
• COFt — отток из проекта денежных средств во временные периоды t;
• CIFt – приток финансов;
• r – ставка дисконтирования, которая равна средневзвешенной стоимости вкладываемого капитала WACC;
• d – %-ая ставка реинвестирования;
• n –

216 Внутренняя норма рентабельности Internal Rate of Return (IRR), Управление спецпроектами (конспект лекций НУГНСУ)

216 Внутренняя норма рентабельности Internal Rate of Return (IRR)

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

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

Расчет IRR проводится методом последовательных приближений величины NPV к нулю при различных ставках дисконта Расчеты производятся по формуле:

На практике определения IRR производится с помощью следующей формулы:

,

гдеА — величина ставки дисконта, при которой NPV положительна;В — величина ставки дисконта, при которой NPV отрицательная;а — величина положительного NPV, при величине ставки дисконтаА;b — величина отрицательной NPV, при величине ставки дисконтаВ.

Таблица 23

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

IRR = 10% ((820 / (820 488))? (15-10))% = 13,1%

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

Но конечно показатель IRR рассчитывают или с помощью финансового калькулятора, или с использованием компьютерной программы В финансовом калькуляторе есть встроенная функция для расчета IRR, поэтому дос статньо ввести все значения CF в регистр денежных потоков и нажать клавишу \»IRR\» В том случае, когда денежные потоки поступают равными долями каждый год, тогда мы аннуитет, и для определения IR R достаточно воспользоваться финансовой таблице анюитетних фактореів.

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

При применении IRR возникают следующие трудности:

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

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

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

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

IRR = r, при которой NPV=f(r)=0 (6)

Если обозначить IC=CF₀, где CF₀ – инвестиционный капитал, то

(7)

y = f(r) = (8)

Эта функция (8) обладает рядом свойств:

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

2) в определенных ситуациях проявляются определенные свойства, которые характерны для специфических потоков.

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

Из последней формулы (8) видно, что для проекта, денежный поток которого можно назвать классическим или ординарным в том смысле, что отток сменяется притоками в сумме, превосходящей этот отток, и соответствующая функция f(r) является убывающей. То есть с ростом значения ставки r график функции стремится к оси абсцисс и пересекается в точке IRR.Ввиду нелинейности функции, а также возможных различных комбинаций знаков элементов денежного потока, функция может иметь несколько пересечений. Так как функция нелинейная, то критерий IRR не обладает свойством аддитивности.Смысл расчета IRR при анализе эффективности планируемых инвестиций заключается в следующем: показатель IRR показывает ожидаемую доходность проекта и, следовательно, максимально допустимый относительный уровень доходов, которые могут быть ассоциированы с данным проектом.Любое предприятие финансирует свою деятельность, в том числе и инвестиционную деятельность, и в качестве платы за пользование авансированными финансовыми ресурсами предприятие выплачивает проценты, дивиденды и т.п., то есть несет обоснованные расходы на поддержание своего экономического потенциала. Показатель, характеризующий относительный уровень этих расходов в отношении долгосрочных источников средств, можно назвать средневзвешенной ценой капитала WACC.WACC отражает сложившийся на предприятии минимум возврата на вложенный в его деятельность капитал. WACC рассчитывается по формуле средней арифметической взвешенной. Экономический смысл IRR заключается в том, что предприятие может принимать любые решения инвестиционного характера, уровень рентабельности которых не ниже текущего значения показателя цены капитала.Под показателем цены капитала понимается либо средневзвешенная цена капитала, если источник средств полностью не идентифицирован, либо

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

1) если IRR> цены капитала, проект нужно принять

2) если IRR < цены капитала, проект нужно отвергнуть

3) если IRR = цене капитала, проект не прибыльный, не убыточный.

В случае, когда IRR = цене капитала (СС) возможны следующие ситуации:

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

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

3. если фирма использует свои собственные средства и заемный капитал и при этом IRR = WACC, то в конце периода n она останется при прежней сумме капитала, выплатив основную сумму долга и процент за кредит + дивиденды по акциям.Независимо от того с чем сравнивается IRR, проект принимается, если его IRR больше некоторой пороговой величины. Всегда выбирается проект с большим IRR (при прочих равных условиях).На практике применяется метод последовательных итераций (или операций) с использованием табулированных значений коэффициентов дисконтирования. Для этого с помощью таблиц, в которых есть ставки дисконтирования, выбирается 2 значения ставки дисконтирования r1 и r2, таким образом, чтобы в интервале от r1 до r2 функция NPV = f(r) меняла свое значение с «+» на « — » или наоборот.Далее применяется формула:

IRR = + (9), где

r1 – значение табулированного коэффициента дисконтирования, при котором f(r1)>0 или f(r) < 0

r2 – значение табулированного коэффициента дисконтирования, при котором f(r2)<0 или f(r2)>0

Точность вычислений обратно пропорциональна длине интервала (r1, r2).

Наилучшая аппроксимация с использованием табулированных значений достигается, когда длина интервала минимальна, то есть когда r1 и r2 ближайшие друг к другу коэффициенты, которые удовлетворяют условиям:

1. r1 – значение табулированного коэффициента дисконтирования, минимизирующее положительное значение NPV, то есть

f(r1) = min {f(r1)>0}

2. r2 – значение табулированного коэффициента дисконтирования, максимизирующее отрицательное значение NPV, то есть

f(r2) = max {f(r2)<0}

Путем взаимной замены коэффициентов r1 и r2 аналогичные условия записываются для ситуации, когда функция меняет знак с «+» на «-».

Для определения IRR графическим методом необходимы 3 условия:

· задать некоторую ставку дисконтирования и определить NPV проекта, после этого отметить соответствующую точку на графике;

· задать гораздо большую ставку дисконтирования, в этом случае NPV резко уменьшается. Вычислить NPV и отметить соответствующую точку на графике;

· соединить полученные две точки и если необходимо продлить график до пересечения с осью х. В точке пересечения кривой с осью х чистая настоящая стоимость проекта = 0. Значение ставки r в этой точке равно значению IRR.Модифицированная внутренняя норма прибыли (MIRR)Основной недостаток, присущий критерию IRR, относится к оценке инвестиционных проектов с неординарными денежными потоками. Соответствующий аналог IRR, который может применяться для любых проектов, называется модифицированной внутренней нормой прибыли (MIRR).

, где r – цена источника финансирования n – продолжительность проекта IF – приток денежных средств в i-ом периоде OF – отток денежных средств в i-ом периоде. Эта формула имеет смысл, если терминальная стоимость превышает сумму дисконтированных оттоков. Критерий MIRR всегда имеет единственное значение. Проект принимается в том случае, если MIRR больше цены источника финансирования проекта. Логика критерия:

· если MIRR больше WACC, то проект прибыльный и обеспечит доходность, равную MIRR – WACC

· если MIRR меньше WACC, то проект убыточный

· если MIRR больше WACC, то решение о принятии проекта на рассмотрение менеджера.

Основное отличие IRR от MIRR заключается в том, что IRR показывает максимальный уровень ставок, а MIRR – реальный уровень с учетом реинвестирования.

Финансовый калькулятор для расчета NPV / IRR онлайн

Расчет чистого дисконтированного дохода NPV и внутренней нормы доходности IRR теперь не проблема!

Почему представленные программы на нашем портале заслужили хорошие оценки поисковых систем ? Потому что: все представление программное обеспечение прошло тщательную проверку на совместимость с современными операционными системами и проверку на вирусы. Мы делаем ваш поиск безопасным. Цените бесплатное и качественное.

IRR — Internal Rate of Return  показатель представляет собой разницу между всеми денежными притоками и оттоками, приведенными к текущему моменту времени (моменту оценки инвестиционного проекта). Он показывает ту величину денежных средств, которую инвесторы ожидают получить от своего проекта (портфеля), после того, как денежные притоки окупят его первоначальные инвестиционные затраты и последующие денежные оттоки, связанные с реализацией инвестиционного проекта. Так как денежные платежи оцениваются с учетом их временной стоимости и рисков, IRR — Internal Rate of Return  можно выразить, как стоимость, добавляемую проектом. Ее также можно охарактеризовать как общую прибыль инвестора.

Формула расчета чистого дисконтированного дохода или чистой приведенной стоимости NPV (Net Present Value). Поступления денежных потоков группируются и суммируются внутри определенных временных периодов. Например, помесячно, поквартально или годично. Тогда, для денежного потока, состоящего из  периодов N (шагов), можно представить в виде следующей формулы: 

CF = CF1 + CF2 + … + CFN,

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

Где D — ставка дисконтирования. Она показывает скорость изменения стоимости денег со временем, чем выше ставка дисконтирования, тем выше скорость.

npv формула пример: В случае оценки инвестиций формула расчета записывается в виде:
                             CF1              CF2                CFN
NPV = -CF0 + ———— + ——— +…+ ————-,
                           (1+r)          (1+r)²            (1+r)^N

Где CF0 — инвестиции сделанные на начальном этапе.
Величина CFK/(1+r)^N называется дисконтированным денежным потоком на шаге N.
Множитель 1/(1+r)^N, используемый в формуле расчета NPV, уменьшается с ростом N, что отражает уменьшение стоимости денег со временем.

Формула расчета NVP может быть использована для оценки уже сделанных в прошлом инвестиций и полученных при этом доходов. В этом случае ставка дисконтирования будет отрицательна, а множитель 1/(1+r)^N будет расти с ростом N.

Внутренняя норма доходности, её так же называют внутренней нормой прибыли, либо внутренним коэффициентом окупаемости  (IRR — Internal Rate of Return ) — есть норма полученной прибыли, от вложенной инвестиции. Это та норма прибыли (барьерная ставка, ставка дисконтирования), при которой чистая приведенная (текущая) стоимость инвестиции будет нулевой, т.е. равна 0, или это та ставка дисконта, при которой дисконтированные доходы от проекта равны инвестиционным затратам. IRR — Internal Rate of Return выявляет наилучшую ставку дисконта, при котором можно вкладывать капитал без каких-либо потерь  и с минимальным риском для собственников, то есть :

IRR = r, при котором NPV = f(r) = 0

Приведем пример:

Ставка дисконта r = 10%

Расчет NPV:

NPV = -7500 +2200/(1+10/100)¹+2500/(1+10/100)²+2900/(1+10/100)³= -1257.3

Расчет IRR:

NPV = 0

-7500 +2200/(1+r/100)¹+2500/(1+r/100)²+2900/(1+r/100)³= 0

IRR = r =0.64%

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

Excel таблица-калькулятор Расчета инвестиций Финансовые функции и таблицы подстановки

Скачать бесплатно без рекламы и ожиданий по прямой ссылке 

 skachat-excel-tablicu-kalkulyator-rascheta-investiciy.zip

Функция Excel XIRR для расчета IRR для непериодических денежных потоков

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

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

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

Функция XIRR в Excel

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

Эта функция была представлена ​​в Excel 2007 и доступна во всех более поздних версиях Excel 2010, Excel 2013, Excel 2016, Excel 2019 и Excel для Office 365.

Синтаксис функции XIRR следующий:

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

Где:

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

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

= XIRR (A2: A5, B2: B5)

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

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

Следующие примечания помогут вам лучше понять внутреннюю механику функции XIRR и наиболее эффективно использовать ее на рабочих листах.

1. XIRR в Excel предназначен для расчета внутренней нормы доходности для денежных потоков с неравными сроками. Для периодических денежных потоков с неизвестными точными датами платежей можно использовать функцию IRR.
2. Диапазон значений должен содержать как минимум одно положительное (доход) и одно отрицательное (исходящий платеж) значение.
3. Если первое значение — это затраты (начальные вложения), оно должно быть представлено отрицательным числом. Первоначальные вложения не дисконтируются; последующие платежи возвращаются к дате первого денежного потока и дисконтируются на основе 365-дневного года.
4. Все даты округлены до целых чисел, что означает, что дробная часть даты, представляющая время, удаляется.
5. Даты должны быть действительными датами Excel, введенными как ссылки на ячейки, содержащие даты или результаты формул, таких как функция ДАТА. Если даты вводятся в текстовом формате, могут возникнуть проблемы.
6. XIRR в Excel всегда возвращает IRR в годовом выражении даже при расчете ежемесячных или недельных денежных потоков.

Расчет XIRR в Excel

Функция XIRR в Excel использует метод проб и ошибок, чтобы найти скорость, которая удовлетворяет этому уравнению:

Где:

• П — денежный поток (платеж)
• d — дата
• i — номер периода
• n — всего периодов

Начиная с предположения, если оно предусмотрено, или с 10% по умолчанию, если нет, Excel выполняет итерации, чтобы получить результат с 0.((B2: B5- $ 2 млрд) / 365)))

Где:

• A2: A5 — денежные потоки
• B2: B5 — даты
• E1 — ставка, возвращаемая XIRR

Как показано на скриншоте ниже, результат очень близок к нулю. Q.E.D. 🙂

Как рассчитать XIRR в Excel — примеры формул

Ниже приведены несколько примеров, демонстрирующих распространенное использование функции XIRR в Excel.

Базовая формула XIRR в Excel

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

= XIRR (A2: A8, B2: B8)

Где A2: A8 — денежные потоки, а B2: B8 — даты, соответствующие денежным потокам:

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

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

Полная форма функции Excel XIRR

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

Для ввода данных, показанного ниже, формула XIRR без предположения возвращает ошибку:

= XIRR (A2: A7, B2: B7)

Ожидаемая норма возврата (-20%), указанная в аргументе guess , помогает Excel прийти к результату:

= XIRR (A2: A7, B2: B7, -20%)

Как рассчитать XIRR для ежемесячных денежных потоков

Для начала запомните: какие бы денежные потоки вы ни рассчитывали, функция Excel XIRR дает годовую норму прибыли .

Чтобы убедиться в этом, давайте найдем IRR для той же серии денежных потоков (A2: A8), которые происходят ежемесячно и ежегодно (даты указаны в B2: B8):

= XIRR (A2: A8, B2: B8)

Как вы можете видеть на скриншоте ниже, IRR увеличивается с 7,68% в случае годовых денежных потоков до примерно 145% для ежемесячных денежных потоков! Разница кажется слишком большой, чтобы ее можно было оправдать только фактором временной стоимости денег:

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

= (1 + E1) ^ (1/12) -1

Или вы можете встроить XIRR непосредственно в уравнение:

= (1 + XIRR (A2: A8, B2: B8)) ^ (1/12) -1

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

= IRR (A2: A8)

В результате этих расчетов мы получаем ежемесячный XIRR в размере 7,77%, что очень близко к 7,68%, полученным по формуле IRR:

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

Шаблон Excel XIRR

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

1. Введите денежные потоки и даты в два отдельных столбца (A и B в этом примере).
2. Создайте два динамически определенных диапазона с именами Cash_flows и Dates . Технически это будут называться формулы:

   Денежные_потоки:

   = СМЕЩЕНИЕ (Лист1! $ A $ 2,0,0, СЧЁТ (Лист1! $ A: $ A), 1)

   Даты:

   = СМЕЩЕНИЕ (Лист1! $ B $ 2,0,0, СЧЁТ (Лист1! $ B: $ B), 1)

   Где Sheet1 — это имя вашего рабочего листа, A2 — это первый денежный поток, а B2 — первая дата.

   Подробные пошаговые инструкции см. В разделе «Как создать динамический именованный диапазон в Excel».

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

= XIRR (денежные_потоки, даты)

Готово! Теперь вы можете добавлять или удалять столько денежных потоков, сколько хотите, и ваша динамическая формула XIRR будет соответственно пересчитана:

Скачать шаблон XIRR Excel

XIRR против IRR в Excel

Основное различие между функциями Excel XIRR и IRR заключается в следующем:

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

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

В качестве примера сравним результаты IRR и XIRR для одних и тех же денежных потоков:

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

Если сроки денежных потоков неравны , разница между результатами весьма значительна:

XIRR и XNPV в Excel

XIRR тесно связан с функцией XNPV, поскольку результатом XIRR является ставка дисконтирования, которая приводит к нулевой чистой приведенной стоимости.Другими словами, XIRR — это XNPV = 0. Следующий пример демонстрирует взаимосвязь между XIRR и XNPV в Excel.

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

С денежными потоками в A2: A5, датами в B2: B5 и ставкой дисконтирования в E1 следующая формула XNPV даст вам чистую приведенную стоимость будущих денежных потоков:

= XNPV (E1, A2: A5, B2: B5)

Положительное значение NPV указывает на прибыльность проекта:

Теперь давайте посмотрим, какая ставка дисконтирования сделает чистую приведенную стоимость равной нулю.Для этого используем функцию XIRR:

= XIRR (A2: A5, B2: B5)

Чтобы проверить, действительно ли ставка, полученная XIRR, приводит к нулевому NPV, поместите ее в аргумент rate вашей формулы XNPV:

= XNPV (E4, A2: A5, B2: B5)

Или встроить всю функцию XIRR:

= XNPV (XIRR (A2: A5, B2: B5), A2: A5, B2: B5)

Да, XNPV, округленное до двух десятичных знаков, действительно равно нулю:

Чтобы отобразить точное значение NPV, выберите отображение большего количества десятичных знаков или примените научный формат к ячейке XNPV.-5 = 0,0000111

Excel XIRR функция не работает

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

# ЧИСЛО! ошибка

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

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

#VALUE! ошибка

Ошибка #VALUE может быть вызвана следующими причинами:

• Любое из предоставленных значений не является числовым.
• Некоторые из предоставленных дат не могут быть идентифицированы как действительные даты Excel.

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

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

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

IRR — это аббревиатура от Internal Rate of Return , которая определяется в терминах NPV или чистой приведенной стоимости. IRR можно указать как ставку дисконтирования, которая делает NPV всех денежных потоков (как положительных, так и отрицательных) от проекта или инвестиций равной нулю. Это один из важнейших финансовых инструментов для оценки желательности потенциального проекта или инвестиций.Более высокая IRR, чем собственная приемлемая ставка компании, указывает на то, что проект или инвестиции, скорее всего, принесут хорошую прибыль в будущем. Принимая во внимание, что более низкая IRR указывает на низкую рентабельность инвестиций.

IRR можно легко рассчитать с помощью нашего онлайн-калькулятора IRR , который является отличным инструментом для быстрого получения результата. Однако для автономного использования вы все равно можете предпочесть использование программ электронных таблиц, таких как Excel, Libreoffice Calc, OpenOffice Calc и т. Д. Давайте узнаем, как рассчитать IRR в электронной таблице Excel.Метод расчета IRR в других таблицах почти такой же.

Формула IRR в Excel

Формула или функция для расчета IRR в Excel рассматривает первоначальные инвестиции как первый денежный поток (отрицательный денежный поток). Итак, формула (или функция) для расчета IRR в Excel:

IRR (значение1, значение2, …)

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

IRR в Excel на примере

Используя вышеупомянутую формулу, давайте узнаем, как рассчитать IRR в электронной таблице Excel на реальном примере. Ниже представлена ​​таблица с данными о движении денежных средств за разные периоды. Первоначальные инвестиции в проект составляют 5000 долларов США. Таким образом, денежный поток за период 0 будет -5000. Поскольку в течение периода 0 денежные средства уходят наружу, это считается отрицательным денежным потоком. Все остальные денежные потоки приведены в таблице.На основе этих данных мы рассчитали бы внутреннюю норму прибыли в Excel.

Шаг 1. Получение всех значений в Excel

Сначала вы должны заполнить данные внутри ячеек Excel.Вы можете начать с любой строки или столбца, но в этом случае мы начнем с крайнего левого столбца A и столбца B. Пусть столбец A должен содержать период движения денежных средств, а столбец B должен иметь соответствующие значения (положительные или отрицательные. ) в зависимости от данных, приведенных в приведенном выше примере. Начальные инвестиции или период 0 будут в первой строке под столбцом B, поэтому это ячейка B1. Точно так же другие значения записываются в столбце B. Подсчет денежного потока начинается с ячейки B1 и заканчивается до ячейки B7, как показано на рисунке ниже.

Шаг 2. Применение формулы Excel внутри ячейки

Так как, значения денежных потоков начинаются с B1 и доходят до ячейки B7. Теперь у нас есть все значения, готовые к применению внутри функции для получения результата IRR. Выберем новую ячейку под последним денежным потоком и назовем ее «внутренняя норма прибыли». Соответствующая ячейка B8 должна иметь значение IRR. На основании вышеупомянутой формулы введите «= IRR (B1: B7)» . Здесь (B1: B7) эквивалентно (B1, B2, B3 ,…., B7).

Шаг 3: Наконец, получение результата

Наконец, после ввода указанной выше функции внутри ячейки B8 нажмите Enter. Это автоматически вернет IRR в той же ячейке (B8) в процентах. Следовательно, IRR для этого примера будет 7,59%

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

Подробнее: как рассчитать NPV в Excel.

Подробнее: как рассчитать CAGR в Excel.

— Расчет внутренней нормы прибыли онлайн

Рассчитать IRR (внутренняя норма доходности) онлайн

Что такое IRR

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

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

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

Где в приведенной выше формуле:

N = общее количество периодов

n = положительное целое число

C = денежный поток

r = внутренняя норма прибыли

NPV = чистая приведенная стоимость

Подробнее: IRR Formula

Как рассчитать IRR на примере

Предположим, компания планирует инвестировать в проект с начальной суммой инвестиций 10000 долларов США.Ожидаемый чистый денежный поток за три года должен составить 4500, 4000 и 5500 долларов соответственно. Рассчитаем внутреннюю норму доходности за этот период.

Итак, формула будет:

0 = C ₁ / (1 + r) + C ₂ / (1 + r) ^₂ + C ₃ / (1 + r) ^₃ — начальные инвестиции

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

0 = 4500 / (1 + r) + 4000 / (1 + r) ^₂ + 5500 / (1 + r) ^₃ — 10000

В этом случае r равно 0.182, что составляет IRR 18,2%

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

Подробнее: Расчет IRR в Excel

IRR критерий принятия решения

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

IRR использует

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

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

Ограничения IRR

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

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

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

О калькуляторе IRR

Расчет IRR иногда может оказаться слишком сложным. Вы можете использовать специальные финансовые калькуляторы (например, калькулятор Ti-83, Ti-84 и HP 12c) или программы, такие как Excel. Калькулятор IRR — один из таких бесплатных онлайн-инструментов для расчета внутренней нормы прибыли от инвестиций.Чтобы рассчитать IRR, просто введите начальную сумму инвестиций, за которой следует чистый денежный поток (отрицательный или положительный) за каждый период, а затем нажмите кнопку «Рассчитать», чтобы получить результат IRR.

Вам могут понравиться наши другие финансовые калькуляторы: