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

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 – число временных периодов.

Вычисление MIRR в табличном процессоре

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

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

В ячейку Е14 вводится формула для MIRR «=МВСД(E3:E13;C1;C2)».

Преимущества и недостатки использования показателя внутренней нормы доходности

Метод оценивания перспективности проектов, посредством вычисления IRR и сравнения с величиной стоимости капитала не является совершенным. Однако у него есть определенные преимущества. К ним относятся:

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

В то же время очевидны недостатки этого показателя. К ним относятся:

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

Обратите внимание! Последний недостаток был разрешен путем ведения показателя MIRR, о котором было подробно рассказано выше.

Как умение рассчитывать IRR может пригодиться заемщикам

По требованиям российского ЦБ, все банки, действующие на территории РФ, обязаны указывать эффективную процентную ставку (ЭПС). Ее может самостоятельно рассчитать любой заемщик. Для этого ему придется воспользоваться табличным процессором, например, Microsoft Excel и выбрать встроенную функцию ВСД. Для этого результат в той же ячейке следует умножить на период платежей Т (если они ежемесячные, то Т = 12, если дневные, то Т = 365) без округления.

Теперь, если вы знаете, что такое внутренняя норма доходности, поэтому, если вам скажут: «Для каждого из нижеперечисленных проектов рассчитайте IRR», у вас не возникнет каких-либо затруднений.

Посчитать IRR без Excel? Такое возможно!

В первой части нашей трилогии мы размышляли о природе IRR и механике её расчёта с помощью Microsoft Excel.

Теперь обсудим, как быть, если у вас под рукой не оказалось экселя, а руки так и чешутся посчитать IRR? Выход есть: существует техника расчёта IRR «на бумаге», которой, например, необходимо уверенно владеть для успешной сдачи экзаменов по программе АССА «Введение в Финансы и управление бизнесом» и «Финансовый менеджмент».

Чтобы раскрыть все тонкости расчёта IRR «на бумаге», нам придётся напомнить себе про основу основ оценки инвестиционных проектов – чистую приведённую стоимость (NPV).

Взаимосвязь NPV и IRR

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

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

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

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

Если минимальная требуемая инвесторами доходность равна 8% годовых (т.е. они готовы инвестировать в наш проект при ожидаемой доходности на уровне 8% годовых и выше), то идеально правильный расчёт NPV будет таким:

или с помощью доступных на экзамене ACCA коэффициентов дисконтирования:

NPV_8% = $60 × 0.926 + $55 × 0.857 — $100 = $55.6 + $47.1 – $100 = $2.7

Разница в значениях ($2.7 против $2.8) вызвана исключительно округлением, и я предлагаю остановиться на $2.7. Вы же не забыли, что экселя у нас по-прежнему под рукой нет?

В любом случае, NPV нашего проекта положительна, а, значит, он финансово привлекателен и обеспечивает требуемую инвесторами доходность в 8% годовых. Если бы инвесторы требовали не менее 14% (например, из-за возросших валютных рисков) то NPV нашего проекта составила бы:

или с помощью доступных на экзамене ACCA коэффициентов дисконтирования:

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

Как мы уже выяснили в первой части нашей трилогии: IRR – это годовая доходность проекта. Если NPV проекта положительна, то он финансово приемлем. Каковы были требования инвесторов по нашему проекту? Инвесторы требовали доходность не менее 8% годовых. Значит, IRR нашего проекта явно больше 8%, раз NPV получилась положительной. А вот если бы инвесторы требовали не менее 14% годовых, то наш проект для них уже невыгоден, так как NPV отрицательная. Выходит, IRR нашего проекта явно больше 8%, но меньше 14%.

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

Теперь давайте сделаем несложное умозаключение о связи между NPV c одной стороны, и IRR и стоимости капитала с другой:

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

Собственно техника расчёта, или «поляции»

Расчёт IRR «на бумаге» сводится к поиску ставки дисконтирования, при которой NPV равна нулю. Это поиск именуется как метод интер- или экстраполяции.  Не стоит ругаться этими словами, если вас попросили посчитать IRR проекта на работе: помните, что для практики есть как минимум Excel.

Расчёт IRR «на бумаге» можно разделить на два последовательных шага:

Шаг 1. Определить связь между NPV проекта и ставкой дисконтирования

Шаг 2 Используя связь из Шага 1, «подобрать» ставку, при которой NPV равна нулю.

Схематично это выглядит так:

Итак, нам нужны две ставки и два соответствующих значения NPV. Мы уже знаем, что при ставке 8% NPV нашего проекта $2.7, а при 14% — ($5.1). Обратите внимание: как мы уже отмечали в нашей первой части: IRR определяется исключительно денежными потоками. Ставки, которые мы использовали (8% и 14%) произвольны. Мы с тем же успехом могли бы использовать, скажем, 2% и 10%. Правда, результат будет немного иным – но об этом я пока умолчу.

Теперь есть всё, чтобы найти IRR. Классическая формула нахождения IRR «на бумаге» выглядит так:

r_м – мéньшая из двух используемых ставок дисконтирования;

r_б – бóльшая из двух используемых ставок дисконтирования;

NPV_м – значение NPV при мéньшей ставке дисконтирования;

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

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

Поиск IRR расчётным путем выглядит так:

Обобщить это видео можно такой таблицей:

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

Если бы  мы выбрали ставки в 4% (NPV была бы равна $8.5) и 6% (NPV была бы равна $5.6), то IRR нашего проекта оказалась бы такой:

Смотрите-ка, что вышло: при использовании одной произвольной пары ставок (8% и 14%) в первом расчёте IRR получилась равной 10.08%, а при использовании другой произвольной пары (4% и 6%) во втором – 9.86%. А ведь мы-то знаем, что точное значение IRR – ровно 10% годовых. Знаем, потому что с помощью функции ВСД посчитали его в первой части нашей трилогии!

То есть рассчитать IRR «на бумаге» реально, однако результат будет зависеть от используемых ставок. Почему? Потому, что этот метод наивно полагает, что между NPV и ставкой дисконтирования линейная зависимость, то есть при каждом изменении ставки на 1% NPV каждый раз меняется на одну и ту же величину.

В действительности же никакой линейной зависимости нет. Вот оно, реальное поведение NPV при изменении ставки:

Вы можете пожать плечами — мол, какое это имеет значение? Всё равно почти 10%.

Ну, представьте, если в жизни ставка растёт с 30% до 31%. Многие инвесторы могут такое событие и вовсе пропустить мимо.

На низких же уровнях, напротив, изменения ставки могут быть очень значимы для результата. Представьте, что ставка меняется с 2% до 3%. Это уже в целых 1.5 раза! Сегодня, когда процентные ставки на рекордно низких уровнях, инвесторы как никогда пристально следят за заседаниями финансовых регуляторов, на которых принимаются решения об изменении ключевых ставок. Изменение ставок с низких уровней всего на десятые проценты способно сильно взбудоражить финансовые рынки.

Увы, этот недостаток бумажного метода расчёта IRR ограничивает его применение только финансовыми экзаменами ACCA, где требуется продемонстрировать, что вы понимаете логику IRR. А в жизни повода использовать метод «поляции» у вас не представится. Ну, может, только если только вы клавиатуру ноутбука зальёте чаем.

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

Продолжение следует…

вычисление показателей ЧПС и ВСД в Excel

Вы теряете спящий режим и знаете, как максимально повысить прибыльность и свести к минимуму риски, связанные с бизнес-инвестициями? Остановить переключение и поворот. Расслабьтесь и перейти к потоку.

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

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

Вопросы о проектов по capital investment

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

• Будет ли новый долгосрочный проект прибыльным? Когда?

• Лучше ли вкладывать деньги в другой проект?

• Следует ли вкладывать средства в текущий проект еще больше или стоит ли сократить потери?

Теперь рассмотрим каждый из этих проектов подробнее и спросите:

• Каковы отрицательные и положительные денежные потоки для этого проекта?

• Какое влияние оказывают крупные первоначальные инвестиции и какой объем будет слишком велик?

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

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

Ответы на вопросы с помощью ЧПС и IRR

Существует два финансовых способа, которые помогут вам ответить на все эти вопросы: чистая стоимость (ЧПС) и внутренняя ставка прибыли (IRR). Как ЧПС, так и IRR называются дисконтируемыми методами движения денежных средств, так как они применяют значение времени к оценке проектов по capital investment. Как ЧПС, так и IRR основаны на рядах будущих платежей (отрицательный денежный поток), доходов (положительный денежный поток), потерь (отрицательный денежный поток) или «нулевых денежных потоков».

Npv

ЧПС возвращает чистую стоимость денежных потоков, представленных в современных рублях. Из-за денежной стоимости каждый доллар сегодня стоит больше, чем завтра. ЧПС вычисляет обтекаемую стоимость для каждого ряда денежных потоков и объединяет их, чтобы получить чистую стоимость.

Формула ЧПС:

Где n — количество денежных потоков, а i — процентная или скидка.

Irr

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

NPV(IRR(values),values) = 0

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

Сравнение проектов

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

Выбор соответствующей Excel функции

hich Office Excel функции, которые можно использовать для вычисления ЧПС и IRR? Существует пять: функция ЧПС,функция ЧПС,функция IRR,ФУНКЦИЯ XIRRи МВСД. Выбор зависит от предпочитаемого финансового метода, от того, происходят ли денежные потоки через определенные интервалы времени и являются ли они периодическими.

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

Дополнительные сведения

Дополнительные сведения об использовании ЧПС и IRR см. в разделах Главы 8 «Оценка инвестиций с использованием критериев чистой стоимости» и Глава 9 ,»Внутренняя ставка прибыли» в Microsoft Excel Анализ данных и бизнес-моделирование Уин Л. Уинстон. чтобы узнать больше об этой книге.

К началу страницы

?нвестиционный проект | Excel для финансиста

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

Показатели инвестиционного проекта

Для ответа на вышеприведённые вопросы используют следующие показатели эффективности инвестиционного проекта:

• срок окупаемости проекта (обычно в месяцах)
• чистая приведённая стоимость (net present value, NPV)
• внутренняя норма доходности (IRR).

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

Недостаток этого показателя – игнорирование факта изменения стоимости денег во времени (дисконтирования). Дисконтирование — это приведение будущих денежных потоков к текущему периоду с учетом изменения стоимости денег с течением времени.Номер периода

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

• стоимостью привлекаемого капитала инвестора;
• прогнозной инфляцией;
• премией за риск проекта.

Коэффициент дисконтирования используется для расчёта показателя Чистая приведённая стоимость (net present value -NPV), который по сути является совокупным дисконтированным денежным потоком. Проект считается экономически выгодным, если его NPV не отрицательна. Нулевое значение NPV говорит о том, что проект принесет прибыль, достаточную для выплаты процентов по привлечённому капиталу с учётом инфляции. Чем выше NPV проекта, тем он привлекательнее (при учете рисков).

Для того чтобы получить более универсальную оценку привлекательности инвестиционного проекта, можно рассчитать третий показатель: внутреннюю норму доходности (IRR) – значение ставки дисконтирования, при которой NPV равен нулю (то есть проект отобъёт вложенные в него средства). Считается, что проект приемлем, если расчётное значение IRR больше ставки дисконтирования. Кроме того, этот показатель удобно использовать при сравнении альтернативных инвестиционных проектов: для каждого рассчитывается показатель IRR и предпочтение отдаётся проекту с наибольшим IRR.

Пример расчёта инвестиционного проекта в Excel

Скачайте файл с примером pokazateli-investproekta, ознакомьтесь с заданием. Первый шаг инвестиционного планирования – составление прогноза денежных потоков.

Прогнозирование денежного потока в Excel

Заполните таблицу «Денежные потоки»:

• в ячейку В9 введите значение первоначальных инвестиций,
• в ячейку В10 — формулу «=B8-B9»
• в ячейку С8 введите сумму поступлений в первый год,
• в D8 – формулу «=C8*1,3»,
• в С9 — «=C8*0,8»,
• протяните формулу из ячейки D8 вправо до 2019 года, рассчитайте итоговое значение;
• протяните вправо формулы из ячеек С9 и В10,
• протяните формулу из ячейки G8 на две ячейки вниз.
• В ячейку В11 формулу «=B10», в ячейку С11 формулу =B11+C10, протяните ячейку С11 вправо до F11, сверьте значение в ячейке F11 cо значением в G10.

Теперь рассчитаны денежные потоки, в том числе нарастающим итогом.

Срок окупаемости в Excel: пример расчёта

Для расчёта срока окупаемости в примере Excel введите в ячейку В17 формулу «=СЧЁТЕСЛ?(B11:G11;»<1″)+1». Смысл этой формулы:  подсчёт всех отрицательных значений и прибавление единицы даст номер года, в котором совокупный денежный поток станет неотрицательным.

Дисконтирование в Excel денежного потока: продолжение примера

На листе «инвестиционный проект» заполните строчку Дисконтированный денежный поток: в ячейку В13 введите «=B10/СТЕПЕНЬ(1+$B$5;B12)», протяните вправо. Обратите внимание на нумерацию периодов инвестирования: текущий год имеет номер 0, так как для него денежный поток не дисконтируется. Второй год (2016) имеет номер 1, денежный поток дисконтируется с коэффициентом 0,82 (=1/(1+22%)), с каждым следующим годом коэффициент дисконтирования уменьшается. Рассчитайте суммарный дисконтированный денежный поток в ячейке G13. Как видим, значение NPV существенно отличается от значения совокупного недисконтированного денежного потока (ячейка F11) за счёт достаточно большой ставки дисконтирования. Поскольку значение положительное, проект может быть признан привлекательным для инвестирования.

Расчёт чистой приведённой стоимости (NPV) в Excel: продолжение примера

В Excel есть встроенная функция для расчёта NPV — ЧПС(ставка.дисконтирования; диапазон_денежных_потоков). Этой функцией пользоваться удобнее (не нужно рассчитывать дисконтированный поток), но у неё есть особенность: первый год проекта считается как будущий (с номером 1) и его денежный поток уже дисконтируется. Если проект планируется, начиная с текущего года, необходимо до применения функции ЧПС скорректировать значения денежных потоков каждого года, умножив их на коэффициент (1+ставка дисконтирования), покажем на примере.

На листе «инвестиционный проект» заполните строчку «Скорректированный денежный поток»: в ячейку В15 введите формулу «=B10*(1+$B$5)» и протяните вправо. Теперь в ячейку В18 введите формулу «=ЧПС(B5;B15:F15)», сравните с рассчитанным «вручную» значением в ячейке G13.

Расчёт внутренней нормы доходности (IRR) в Excel: окончание примера

Сделаем расчёт IRR в Excel двумя способами: вручную и автоматически.

На листе «инвестиционный проект», вручную изменяя ставку дисконтирования, примерно подберите такое значение ставки, чтобы значение в ячейке G13 было близко к нулю.

В Excel есть функция для расчёта IRR — ВСД, которая работает с той же особенностью дисконтирования с первого года. В ячейке В19 введите функцию «=ВСД(B15:F15)», сравните с подобранным вручную.

Таким образом, рассмотрен пример расчёта инвестиционного проекта в Excel, включающий составление таблицы дисконтированного денежного потока  с расчётом показателей инвестиционного проекта.

Скачать файл с примером инвестиционного проекта Excel можно по ссылке: pokazateli-investproekta.

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

Финансовый калькулятор для расчета 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

Расчет формулы IRR в Excel (со снимками экрана)

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

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

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

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

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

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

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

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

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

В приведенном ниже примере при использовании ставки дисконтирования 20% инвестиция №2 показывает более высокую прибыльность, чем инвестиция №1. Если вместо этого выбрать ставку дисконтирования 1%, инвестиция №1 показывает доходность больше, чем инвестиция №2. Прибыльность часто зависит от последовательности и важности денежных потоков проекта и ставки дисконтирования, применяемой к этим денежным потокам.

Что такое внутренняя норма прибыли?

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

В нашем примере IRR инвестиции №1 составляет 48%, а для инвестиции №2 — 80%. Это означает, что в случае инвестиции №1 с вложением 2000 долларов в 2013 году вложение принесет годовой доход в размере 48%. В случае инвестиции №2 с вложением 1000 долларов в 2013 году доходность принесет 80% годовых.

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

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

На изображении ниже для инвестиции №1 Excel не обнаружил, что показатель NPV уменьшился до нуля, поэтому у нас нет IRR.

На изображении ниже также показаны инвестиции №2. Если второй параметр не используется в функции, Excel обнаружит, что IRR составляет 10%.С другой стороны, если используется второй параметр (т. Е. = IRR ($ C $ 6: $ F $ 6, C12)), для этих инвестиций будут предоставлены две IRR, которые составляют 10% и 216%.

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

Расчет IRR в Excel

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

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

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

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

На изображении ниже мы рассчитываем IRR инвестиции, как в предыдущем примере, но с учетом того, что компания будет занимать деньги, чтобы вложить их обратно (отрицательные денежные потоки) по ставке, отличной от ставки, по которой она будет реинвестировать. {\ frac {1} {n-1}} — 1 \ конец {выровнен} (NPV (частота, значения [отрицательные]) × (1 + frate) −NPV (rrate, значения [положительные]) × (1 + rrate) n) n − 11 −1

Внутренняя норма прибыли в разные моменты времени (XIRR)

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

.

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

Страница не найдена

• Образование

  • Общий

    • Словарь
    • Экономика
    • Корпоративные финансы
    • Рот ИРА
    • Акции
    • Паевые инвестиционные фонды
    • ETFs
    • 401 (к)

  • Инвестирование / Торговля

    • Основы инвестирования
    • Фундаментальный анализ
    • Управление портфелем
    • Основы трейдинга
    • Технический анализ
    • Управление рисками
• Рынки

  • Новости

    • Новости компании
    • Новости рынков
    • Торговые новости
    • Политические новости
    • Тенденции

  • Популярные акции

    • Яблоко (AAPL)
    • Тесла (TSLA)
    • Amazon (AMZN)
    • AMD (AMD)
    • Facebook (FB)
    • Netflix (NFLX)
• Симулятор
• Твои деньги

  • Личные финансы

    • Управление благосостоянием
    • Бюджетирование / экономия
    • Банковское дело
    • Кредитные карты
    • Домовладение
    • Пенсионное планирование
    • Налоги
    • Страхование

  • Обзоры и рейтинги

    • Лучшие онлайн-брокеры
    • Лучшие сберегательные счета
    • Лучшие домашние гарантии
    • Лучшие кредитные карты
    • Лучшие личные займы
    • Лучшие студенческие ссуды
    • Лучшее страхование жизни
    • Лучшее автострахование
• Советники

  • Ваша практика

    • Управление практикой
    • Продолжая образование
    • Карьера финансового консультанта
    • Инвестопедия 100

  • Управление благосостоянием

    • Портфолио Строительство
    • Финансовое планирование
• Академия

  • Популярные курсы

    • Инвестирование для начинающих
    • Станьте дневным трейдером
    • Торговля для начинающих
    • Технический анализ

  • Курсы по темам

    • Все курсы
    • Торговые курсы
    • Курсы инвестирования
    • Финансовые профессиональные курсы

Представлять на рассмотрение

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

• О нас
• Условия эксплуатации
• Словарь
• Редакционная политика
• Рекламировать
• Новости
• Политика конфиденциальности
• Связаться с нами
• Карьера
• Уведомление о конфиденциальности для Калифорнии

• #
• А
• B
• C
• D
• E
• F
• грамм
• ЧАС
• я
• J
• K
• L
• M
• N
• О
• п
• Q
• р
• S
• Т
• U
• V
• W
• Икс
• Y
• Z

Страница не найдена

• Образование

  • Общий

    • Словарь
    • Экономика
    • Корпоративные финансы
    • Рот ИРА
    • Акции
    • Паевые инвестиционные фонды
    • ETFs
    • 401 (к)

  • Инвестирование / Торговля

    • Основы инвестирования
    • Фундаментальный анализ
    • Управление портфелем
    • Основы трейдинга
    • Технический анализ
    • Управление рисками
• Рынки

  • Новости

    • Новости компании
    • Новости рынков
    • Торговые новости
    • Политические новости
    • Тенденции

  • Популярные акции

    • Яблоко (AAPL)
    • Тесла (TSLA)
    • Amazon (AMZN)
    • AMD (AMD)
    • Facebook (FB)
    • Netflix (NFLX)
• Симулятор
• Твои деньги

  • Личные финансы

    • Управление благосостоянием
    • Бюджетирование / экономия
    • Банковское дело
    • Кредитные карты
    • Домовладение
    • Пенсионное планирование
    • Налоги
    • Страхование

  • Обзоры и рейтинги

    • Лучшие онлайн-брокеры
    • Лучшие сберегательные счета
    • Лучшие домашние гарантии
    • Лучшие кредитные карты
    • Лучшие личные займы
    • Лучшие студенческие ссуды
    • Лучшее страхование жизни
    • Лучшее автострахование
• Советники

  • Ваша практика

    • Управление практикой
    • Продолжая образование
    • Карьера финансового консультанта
    • Инвестопедия 100

  • Управление благосостоянием

    • Портфолио Строительство
    • Финансовое планирование
• Академия

  • Популярные курсы

    • Инвестирование для начинающих
    • Станьте дневным трейдером
    • Торговля для начинающих
    • Технический анализ

  • Курсы по темам

    • Все курсы
    • Торговые курсы
    • Курсы инвестирования
    • Финансовые профессиональные курсы

Представлять на рассмотрение

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

• О нас
• Условия эксплуатации
• Словарь
• Редакционная политика
• Рекламировать
• Новости
• Политика конфиденциальности
• Связаться с нами
• Карьера
• Уведомление о конфиденциальности для Калифорнии

• #
• А
• B
• C
• D
• E
• F
• грамм
• ЧАС
• я
• J
• K
• L
• M
• N
• О
• п
• Q
• р
• S
• Т
• U
• V
• W
• Икс
• Y
• Z

Как использовать функцию IRR

Простой пример IRR | Настоящие ценности | Правило IRR

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

Простой пример IRR

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

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

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

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

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

3. Функция IRR ниже вычисляет внутреннюю норму прибыли проекта A.

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

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

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

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

Настоящие значения

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

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

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

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

3. Мы можем это проверить. Сначала мы рассчитываем приведенную стоимость (pv) каждого денежного потока. Затем мы суммируем эти значения.

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

Правило IRR

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

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

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

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

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

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

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

— формула, примеры, как использовать IRR в Excel

Что такое функция IRR?

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

Эта функция очень полезна при финансовом моделировании Что такое финансовое моделирование Финансовое моделирование выполняется в Excel для прогнозирования финансовых показателей компании.Обзор того, что такое финансовое моделирование, как и зачем строить модель, поскольку оно помогает рассчитать доходность инвестиций на основе ряда денежных потоков. Эти статьи научат вас передовым методам оценки бизнеса и научат оценивать компанию с помощью сопоставимого анализа компании, моделирования дисконтированного денежного потока (DCF) и прецедентных транзакций, используемых в инвестиционно-банковском деле, исследовании капитала и т. Д. Он часто используется предприятиями для сравнения капитальных проектов и выбора между ними.Один из примеров — когда компании представлены две возможности: одна — инвестировать в новый завод, а вторая — расширять существующий завод. Используя функцию IRR, мы можем оценить IRR для обоих сценариев и проверить, какой из них выше, чем средневзвешенная стоимость капитала (WACCWACCWACC — это средневзвешенная стоимость капитала фирмы и представляет собой смешанную стоимость капитала, включая собственный капитал и заемные средства.) бизнес (также известный как минимальная ставка). Если оба показателя превышают стоимость капитала, мы выберем тот, который показывает более высокую внутреннюю норму доходности и / или чистую приведенную стоимость (формула NPVNPV — справочник по формуле NPV в Excel при выполнении финансового анализа.n], где PV = текущая стоимость, F = будущий платеж (денежный поток), r = ставка дисконтирования, n = количество периодов в будущем), чем другой.

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

Формула IRR

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

IRR Функция использует следующие аргументы:

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

Примечания

1. Значение аргумента должно содержать по крайней мере одно положительное и одно отрицательное значение для расчета внутренней нормы прибыли.
2. Функция IRR использует порядок значений для интерпретации денежных потоков. Следовательно, необходимо последовательно вводить значения выплат и доходов.
3. Если аргумент массива или ссылки содержит логические значения, пустые ячейки или текст, эти значения игнорируются.

Чтобы узнать больше, запустите наш бесплатный ускоренный курс по Excel прямо сейчас!

Как использовать функцию IRR в Excel?

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

Пример 1

Для начальных инвестиций в размере 1000000 денежные потоки приведены ниже:

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

Внутренняя норма прибыли, которую мы получаем, составляет 14%.

Пример 2

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

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

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

Теперь мы просто применим функцию IRR для расчета CAGR, как показано ниже:

CAGR, который мы получаем:

Давайте проведем обратный инжиниринг, чтобы проверить, полученное значение верное или нет.(1 / Периоды) -1.

Используемая формула:

Результат такой же, как и при использовании функции IRR:

Что нужно помнить о функции IRR

1. # ЧИСЛО! error — Происходит, когда:
   1. Если заданный массив значений не содержит хотя бы одного отрицательного и одного положительного значения
   2. Расчет не удается сойтись после 20 итераций.Если внутренняя норма прибыли не может найти результат, который работает после 20 попыток, то #NUM! возвращается значение ошибки.
2. IRR тесно связана с функцией NPV (чистой приведенной стоимости). Норма прибыли, рассчитываемая IRR, — это ставка дисконтирования, соответствующая чистой приведенной стоимости в $ 0 (нулевой).

Бесплатный курс Excel

Если вы хотите узнать больше о функциях и стать экспертом по ним, ознакомьтесь с бесплатным ускоренным курсом CFI по Excel! Ознакомьтесь с нашими пошаговыми инструкциями и демонстрациями того, как стать опытным пользователем Excel.

Дополнительные ресурсы

Спасибо за чтение руководства CFI по важным формулам Excel! Потратив время на изучение и освоение этих функций, вы значительно ускорите свое финансовое моделирование и анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

• XIRR против IRR XIRR против IRR Зачем использовать XIRR против IRR. XIRR назначает конкретные даты каждому отдельному денежному потоку, что делает его более точным, чем IRR, при построении финансовой модели в Excel.
• Модифицированная внутренняя норма прибыли (MIRR) Руководство по MIRR Модифицированная внутренняя норма прибыли (MIRR) — это функция в Excel, которая учитывает стоимость финансирования (стоимость капитала) и ставку реинвестирования для денежных потоков от проекта или компании сверх временной горизонт инвестиций.
• Руководство по финансовому моделированию Бесплатное руководство по финансовому моделированию Это руководство по финансовому моделированию содержит советы и передовые методы работы с Excel в отношении предположений, драйверов, прогнозирования, связывания трех отчетов, анализа DCF и др.
• Программа финансового аналитика Стать сертифицированным аналитиком финансового моделирования и оценки (FMVA) ®CFI’s Сертификация «Финансовое моделирование и оценка аналитика» (FMVA) ® поможет вам обрести уверенность в своей финансовой карьере.Запишитесь сегодня!

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

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

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

Функция IRR в Excel

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

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

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

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

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

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

Где:

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

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

= IRR (B2: B5)

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

Как показано на скриншоте выше, наша формула Excel IRR возвращает 8.9%. Хорошая это оценка или плохая? Что ж, это зависит от нескольких факторов.

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

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

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

5 фактов о функции IRR в Excel

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

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

Общие сведения о формуле IRR в Excel

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

Где:

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

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

Чтобы увидеть, как это работает на практике, давайте выполним этот расчет IRR на выборке данных. Для начала мы попытаемся угадать, какой может быть внутренняя норма доходности (скажем, 7%), а затем вычислим чистую приведенную стоимость.A3

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

= СУММ (C2: C5)

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

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

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

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

= IRR (B2: B8)

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

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

= IRR (B2: B8, 10%)

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

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

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

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

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

= IRR (B2: B7)

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

= IRR (C2: C7)

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

= IRR (D2: D7)

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

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

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

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

Пример 4. Вычислить среднегодовой темп роста (CAGR)

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

• Оставьте первое значение начальных инвестиций как отрицательное число, а конечное значение как положительное число.(1 / кол-во периодов) -1

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

  Для получения дополнительной информации см. Как рассчитать CAGR в Excel.

  IRR и NPV в Excel

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

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

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

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

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

  = ЧПС (E1, B3: B7) + B2

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

  Какая ставка дисконтирования сделает NPV равной нулю? Следующая формула IRR дает ответ:

  = IRR (B2: B7)

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

  = ЧПС (E4, B3: B7) + B2

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

  = ЧПС (IRR (B2: B7), B3: B7) + B2

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

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

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

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

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

  Формула

  IRR возвращает # ЧИСЛО! ошибка

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

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

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

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

  Несколько IRR

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

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

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

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

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

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

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

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

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

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

  Расчет IRR

  в Excel с формулами, шаблоном и Goal Seek

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

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

  Что такое IRR?

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

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

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

  Что показывает IRR?

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

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

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

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

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

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

  Расчет IRR

  в Excel

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

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

  Где:

  • CF ₀ — начальные вложения (представлены отрицательным числом)
  • CF ₁ , CF ₂ … CF _n — денежные потоки
  • i — номер периода
  • n — всего периодов
  • IRR — внутренняя норма доходности

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

  Пример : Вы инвестируете 1000 долларов сейчас и получаете обратно 500 и 660 долларов в следующие 2 года. Какая ставка дисконтирования делает чистую приведенную стоимость равной нулю?

  В качестве нашего первого предположения, давайте попробуем 8% ставку:

  • Сейчас: PV = — 1000 $
  • Год 1: ЛС = 500 долл. США / (1 + 0,08) ¹ = 462,96 долл. США
  • Год 2: PV = 660 долларов США / (1 + 0.08) ² = 565,84 долл. США

  Сложив их, мы получим NPV , равную 28,81 доллара:
  

  О, даже близко не к 0. Может, лучшая догадка, скажем, 10%, может что-то изменить?

  • Сейчас: PV = — 1000 $
  • Год 1: ЛС = 500 долл. США / (1 + 0,1) ¹ = 454,55 долл. США
  • , год 2: ЛС = 660 долл. США / (1 + 0,1) ² = 545,45 долл. США
  • NPV: -1000 + 454,55 USD + 545,45 USD = 0,00 USD

  Вот и все! При ставке дисконтирования 10% NPV ровно 0.Итак, IRR для этой инвестиции составляет 10%:
  

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

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

  Microsoft Excel предоставляет 3 функции для определения внутренней нормы прибыли:

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

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

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

  Предположим, вы рассматриваете 5-летнее вложение с денежными потоками в B2: B7. Чтобы рассчитать IRR, используйте эту простую формулу:

  = IRR (B2: B7)

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

  Для получения дополнительной информации см. Функцию Excel IRR.

  Формула XIRR для определения IRR для нерегулярных денежных потоков

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

  С денежными потоками в B2: B7 и их датами в C2: C7 формула будет выглядеть следующим образом:

  = XIRR (B2: B7, C2: C7)

  Примечания:
  • Хотя функция XIRR не обязательно требует дат в хронологическом порядке, дата первого денежного потока (начальных инвестиций) должна быть первой в массиве.
  • Даты должны быть представлены как действительные даты Excel ; предоставление дат в текстовом формате подвергает Excel риску их неверной интерпретации.
  • Функция Excel XIRR использует другую формулу для получения результата. Формула XIRR дисконтирует последующие платежи на основе 365-дневного года, в результате XIRR всегда возвращает внутреннюю норму доходности в годовом исчислении .

  Для получения дополнительных сведений см. Функцию Excel XIRR.

  Формула MIRR для расчета модифицированной IRR

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

  = MIRR (B2: B7, E1, E2)

  Где B2: B7 — денежные потоки, E1 — ставка финансирования (стоимость заимствования денег), а E2 — ставка реинвестирования (проценты, полученные от реинвестирования прибыли).
  

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

  IRR, XIRR и MIRR — что лучше?

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

  Обычно считается, что:

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

  Калькулятор IRR — шаблон Excel

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

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

  1. Введите денежные потоки и даты в два столбца (в нашем случае A и B).
  2. Введите ставку финансирования и ставку реинвестирования в 2 отдельные ячейки. При желании назовите эти продажи Финансовая_рейт и Реинвест , соответственно.
  3. Создайте два динамически определенных диапазона с именами Cash_flows и Даты .

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

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

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

     Даты:

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

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

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

     = IRR (Cash_flows)

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

     = MIRR (денежные_потоки, финансовая_рейт ,_рейт_ реинвестирования)

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

  В качестве меры предосторожности против неосторожных пользователей, которые могут забыть заполнить все необходимые ячейки ввода, вы можете заключить свои формулы в функцию ЕСЛИОШИБКА, чтобы предотвратить ошибки:

  = ЕСЛИОШИБКА (IRR (Cash_flows); "")

  = ЕСЛИОШИБКА (XIRR (денежные_потоки, даты), "")

  = ЕСЛИОШИБКА (MIRR (денежные_потоки, финансовая_ ставка, реинвестирование), "")

  Имейте в виду, что если ячейки Finance_rate и / или Reinvest_rate пусты, функция Excel MIRR предполагает, что они равны нулю.

  Скачать калькулятор IRR в Excel

  Как сделать IRR в Excel с помощью Goal Seek

  Функция Excel IRR выполняет только 20 итераций, чтобы получить скорость, а XIRR выполняет 100 итераций. Если после этого большого количества итераций результат с точностью до 0,00001% не будет найден, # ЧИСЛО! ошибка возвращается.

  Если вам нужна более высокая точность расчета IRR, вы можете заставить Excel выполнить более 32 000 итераций с помощью функции поиска цели, которая является частью анализа «что, если».

  Идея состоит в том, чтобы получить Goal Seek, чтобы найти процентную ставку, которая делает NPV равной 0. Вот как это сделать:

  1. Настройте исходные данные следующим образом:
     • Введите денежные потоки в столбец (B2: B7 в этом примере).
     • Поместите ожидаемую внутреннюю норму дохода в некоторую ячейку (B9). Значение, которое вы вводите, на самом деле не имеет значения, вам просто нужно что-то «скормить» формуле NPV, поэтому просто укажите любой процент, который придет в голову, скажем, 10%.
     • Введите следующую формулу ЧПС в другую ячейку (B10):
       = ЧПС (B9, B3: B7) + B2

  2. На вкладке Data в группе Forecast щелкните What if Analysis > Goal Seek…
  3. В диалоговом окне Goal Seek определите ячейки и значения для проверки:
     • Установить ячейку — ссылка на ячейку NPV (B10).
     • To value — введите 0, которое является желаемым значением для ячейки Set.
     • Изменяя ячейку — ссылка на ячейку IRR (B9).

     Когда закончите, нажмите ОК .
     

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

     Нажмите ОК, , чтобы принять новое значение, или Отмена, , чтобы вернуть исходное.
     

  Аналогичным образом вы можете использовать функцию Goal Seek для поиска XIRR. Единственная разница в том, что вам нужно будет использовать формулу XNPV вместо NPV.

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

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

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

  .

  Об авторе

  

  alexxlab

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

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

  Как помочь ребенку научиться правильно произносить звук «Р»: советы логопеда

  Уход за пупочной ранкой новорожденного: правила обработки, средства и советы

  Добавить комментарий Отменить ответ

  Ваш адрес email не будет опубликован. Обязательные поля помечены *

  Комментарий *

  Имя *

  Email *

  Сайт