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

Содержание

Внутренняя норма доходности (IRR, internal rate of return). Формула и пример расчета в Excel

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

Внутренняя норма доходности инвестиционного проекта (IRR, Internal Rate of Return). Определение

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

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

★ Инвестиционная оценка в Excel. Расчет NPV, IRR, DPP, PI за 5 минут

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

где:

CF

t (Cash Flow) – денежный поток в период времени t;

IC (Invest Capital) – инвестиционные затраты на проект в первоначальном периоде (тоже являются денежным потоком CF0 = IC).

t – период времени.

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

Показатель используется для оценки привлекательности инвестиционного проекта или для сопоставительного анализа с другими проектами. Для этого IRR сравнивают  с эффективной ставкой дисконтирования, то есть с требуемым уровнем доходности проекта (r). За такой уровень на практике зачастую используют средневзвешенную стоимость капитала (Weight Average Cost of Capital, WACC).

Значение IRR Комментарии
IRR>WACC Инвестиционный проект имеет внутреннюю норму доходности выше чем затраты на собственный и заемный капитал. Данный проект следует принять для дальнейшего анализа
IRR<WACC Инвестиционный проект имеет норму доходности ниже чем затраты на капитал, это свидетельствует о нецелесообразности вложения в него
IRR=WACC Внутренняя доходность проекта равна стоимости капитала, проект находится на минимально допустимом уровне и следует произвести корректировки движения денежных средств и увеличить денежные потоки
IRR1>IRR2 Инвестиционный проект (1) имеет больший потенциал для вложения чем (2)

Следует заметить, что вместо критерия сравнения WACC может быть любой другой барьерный уровень инвестиционных затрат, который может быть рассчитан по методам оценки ставки дисконтирования. Данные методы подробно рассмотрены в статье «Ставка дисконтирования. 10 современных методов расчета». Простым практическим примером, может быть сравнение IRR с безрисковой процентной ставкой по банковскому вкладу. Так если инвестиционный проект имеет IRR=10%, а процент по вкладу=16%, то данный проект следует отклонить.

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

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

Изменение чистого дисконтированного дохода в зависимости от внутренней нормы доходности

Внутренняя норма доходности занимает второе место в инвестиционном анализе проектов, другие показатели оценки проектов более подробно рассмотрены в статье: “6 методов оценки эффективности инвестиций в Excel. Пример расчета NPV, PP, DPP, IRR, ARR, PI“.

Мастер-класс: “Как рассчитать внутреннюю норму доходности бизнес плана”

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

Рассмотрим пример расчета внутренней нормы доходности на примере с помощью Excel, разберем два способа построения с помощью функции и с помощью надстройки «Поиск решений». A7

Чистый дисконтированный доход (NPV) =СУММ(F7:F15)-B6

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

Внутренняя норма доходности (IRR) и NPV. Расчет в Excel в помощью надстройки

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

При нажатии в появившемся окне заполняем строки «Установить целевую ячейку» – это формула расчета NPV, далее выбираем значение данной ячейки равной 0. Изменяемый параметр будет ячейка со значением внутренней нормы доходности (IRR). На рисунке ниже показан пример расчета с помощью надстройки «Поиск решений».

Поиск значения IRR для NPV=0

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

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

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

На практике часто случается, что денежные средства поступают не периодично. В результате ставка дисконтирования для каждого денежного потока будет меняться, это делает невозможным использовать формулу ВСД в Excel. Для решения данной задачи используется другая финансовая формула ЧИСТВНДОХ (). Данная формула включает в себя массив дат и денежные потоки. Формула расчета будет иметь следующий вид:

=ЧИСТВНДОХ(E6:E15;A6:A15;0)

Расчет внутренней нормы доходности в Excel для несистематических платежей

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

В инвестиционном анализе также используется модифицированная внутренняя норма доходности (Modified Internal Rate of Return, MIRR)­ – данный показатель отражает минимальный внутренний уровень доходности проекта при осуществлении реинвестиций в проект. Данный проект использует процентные ставки, полученные от реинвестирования капитала. Формула расчета модифицированной внутренней нормы доходности следующая:

где:

MIRR – внутренняя норма доходности инвестиционного проекта;

COFt – отток денежных средств в периоды времени t;

CIFt – приток денежных средств;

r – ставка дисконтирования, которая может рассчитываться как средневзвешенная стоимость капитала WACC;

d – процентная ставка реинвестирования капитала;

n – количество временных периодов.

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

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

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

MIRR =МВСД(E8:E17;C4;C5)

Преимущества и недостатки внутренней нормы доходности (IRR)

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

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

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

К недостаткам показателя относят:

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

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

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

★ Инвестиционная оценка в Excel. Расчет NPV, IRR, DPP, PI за 5 минут

Резюме

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

Автор: к.э.н. Жданов Иван Юрьевич

Функция ВСД - Служба поддержки Office

В этой статье описаны синтаксис формулы и использование функции ВСД в Microsoft Excel.

Описание

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

Синтаксис

ВСД(значения; [предположения])

Аргументы функции ВСД описаны ниже.

  • Значения    — обязательный аргумент. Массив или ссылка на ячейки, содержащие числа, для которых требуется подсчитать внутреннюю ставку доходности.

    • Значения должны содержать по крайней мере одно положительное и одно отрицательное значение.

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

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

  • Предположение    — необязательный аргумент. Величина, предположительно близкая к результату ВСД.

    • В Microsoft Excel для вычисления IRR используется метод итеративных вычислений. Начиная со прогноза, IRR циклит вычисления, пока результат не будет точным в пределах 0,00001 процента. Если не получается найти результат, который работает после 20 попыток, #NUM! возвращается значение ошибки.

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

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

Замечания

Функция ВСД тесно связана с функцией ЧПС. Ставка доходности, вычисляемая функцией ВСД, связана с нулевой чистой текущей стоимостью. Взаимосвязь функций ЧПС и ВСД отражена в следующей формуле:

ЧПС(ВСД(A2:A7),A2:A7) равняется 1.79E-09 [Учитывая точность расчета для функции ВСД, значение можно считать нулем).]

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные

Описание

-70 000 ₽

Начальная стоимость бизнеса

12 000 ₽

Чистый доход за первый год

15 000 ₽

Чистый доход за второй год

18 000 ₽

Чистый доход за третий год

21 000 ₽

Чистый доход за четвертый год

26 000 ₽

Чистый доход за пятый год

Формула

Описание

Результат

=ВСД(A2:A6)

Внутренняя ставка доходности по инвестициям после четырех лет

-2,1 %

=ВСД(A2:A7)

Внутренняя ставка доходности после пяти лет

8,7 %

=ВСД(A2:A4;-10%)

Для подсчета внутренней ставки доходности после двух лет следует включить предположение (в данном примере — -10 %)

-44,4 %

Анализ эффективности инвестиций на базе платформы QlikView

Любой бизнес так или иначе связан с инвестициями. Инвестирование — одна из форм вложения капитала.

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

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

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

Расчет показателей эффективности инвестиционного проекта

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

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

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

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

Использование Excel для расчета показателей инвестпроекта

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

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

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

Бизнес-аналитика для оценки инвестиционных проектов

Для анализа деятельности различных отделов организации широко используются специализированные системы бизнес аналитики. Одним из лидеров на рынке BI является аналитическая платформа QlikView.

Рассмотрим, каким образом QlikView может применяться для финансового анализа, на примере типового отраслевого решения для оценки эффективности инвестиционных проектов БИТ.BI.FinExpert.

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

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

В случае БИТ.BI.FinExpert источником данных выступает Excel-форма, в которой аналитикам или руководителям удобно и привычно вести бизнес-планы. Данная форма загружается в модель, и все основные показатели рассчитываются автоматически.

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

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

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

Эти алгоритмы дают возможность сразу исключить из рассмотрения проекты, которые алгоритм сочтет невыгодными. В БИТ.BI.FinExpert предложена реализация подобного алгоритма.

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

Тогда аналитику интересны только проекты, имеющие PI больше 100% и период окупаемости меньше определенного значения, которое аналитик может задать самостоятельно.

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


Определение ставки дисконтирования часто является трудной задачей для аналитиков.

Системы бизнес-аналитики позволяют проводить моделирование проекта при различных значениях ставки дисконтирования. Это дает возможность при разработке и анализе бизнес-планов «отследить», насколько чувствительны показатели проекта к изменению ставки дисконтирования, и выбирать проекты с наименьшей чувствительностью.

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



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

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

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

Итак, для анализа любого инвестиционного проекта составляется бизнес-план этого проекта. На основе бизнес-плана рассчитываются основные показатели эффективности: NPV, IRR, PI, PP, DPP, MIRR.

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

В качестве примера возможностей BI-систем описан функционал решения БИТ.BI.FinExpert, разработанного на платформе QlikView. Применение современных инструментов для анализа позволяет принимать обоснованные управленческие решения и значительно сократить ежедневные трудозатраты.


Подготовила Елена Суетина,
разработчик QlikView,
Проектный офис "Спортивная" компании Первый БИТ


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

 

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

Год
Приток платежей
Отток платежей
0

7500
1
2200

2
2500

3
2900

 

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

Расчет NPV:

NPV = -7500 +2200/(1+10/100)1+2500/(1+10/100)2+2900/(1+10/100)3= -1257.3

 

 

Расчет IRR:

NPV = 0

-7500 +2200/(1+r/100)1+2500/(1+r/100)2+2900/(1+r/100)3= 0

IRR = r =0.64%

 

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

 

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

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

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


Что такое IRR | inflexio

Этой статьёй мы открываем аж целую трилогию о похождениях внутренней ставки (нормы) доходности IRR (internal rate of return) по бескрайним финансовым просторам. Эта трилогия получила право на жизнь благодаря часто встречающимся заблуждениям об IRR. При достаточно простой и понятной природе этого показателя ходят слухи, что «IRR – это какая-то там ставка, при которой NPV равен нулю». И на это есть основания: даже википедия нас пытается в этом убедить.

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

А ведь IRR важен, к примеру, для первичного отбора проектов фондами прямых инвестиций (private equity funds). Например, Российский фонд прямых инвестиций заявляет целевую IRR по проектам в 15-25% в долларах США. Фонды прямых инвестиций вообще очень любят этот показатель. Как они зарабатывают 15-25% годовых в долларах США я обязательно расскажу в наших следующих обзорах, но сейчас о насущном.

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

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

Так давайте же попробуем разобраться! Сразу договоримся: природа IRR – отдельно, техника расчета – отдельно.

 

Природа IRR

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

 

 

Вложив $100 сегодня, мы ожидаем получить $110 чистыми через год. Какова годовая доходность такого вложения? Верно – 10% годовых. Вложив $100 сейчас, через год получаешь $110 ($100 + $100 × 10% = $110). Эти 10% и есть IRR нашего проекта!

Просто? Да. И что же нам потребовалось для определения IRR? Исключительно график денежных потоков!

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

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

 

 

Как считать IRR в жизни

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

 

 

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

Вложили $100, а заработали за два года $60 + $55 = $115. Это 15% за два года или 15%/2 = 7.5% в год.

Такой подход неверен. В современных быстро меняющихся экономических условиях важна не только сумма денег, но и момент возникновения потоков денег. Чистые поступления в $60 и $55 имеют разный возраст относительно сегодняшнего дня и, следовательно, разную экономическую ценность. Это не только из-за инфляции. Если мы получаем деньги быстрее, то больше возможностей сделать с этими деньгами что-то путное.

Выходит, что складывать $60 и $55 для целей расчета годовой доходности это все равно что складывать коров с телятами.

 

Так как же найти IRR, если срок проекта более одного года?

Допустим, что «проект» представляет собой выданный заём на сумму $100, который будет погашен в течение двух лет ежегодными платежами в $60 и $55. Тот процент годовых, под который фактически вложены $100 и есть IRR проекта (кстати, эта IRR по займу ни что иное, как эффективная процентная ставка, которая используется для учета финансовых активов по амортизированной стоимости – это полезно тем, кто изучает ДипИФР и ACCA FR.

Общие заработанные проценты за два года равны разнице между вложенными и полученными деньгами $60 + $55 — $100 = $15. Это во-первых. Во-вторых, остаток выданного займа на конец второго года должен быть равен нулю, ведь его погасили. Ставка IRR должна обеспечить одновременное выполнение этих двух условий:

 

 

Точно рассчитывать IRR умеет Excel. В зависимости от целей использовать можно одну из двух функций: ВСД и ЧИСТВНДОХ.

 

Функция ВСД (внутренняя ставка доходности)

Перед применением функции ВСД требуется ввести денежные потоки по проекту без пропусков. Важно соблюдать знаки: оттоки показываем с «-», притоки с «+». Функция ВСД основана на одном важном допущении, о котором следует помнить: она считает, что первый денежный поток осуществляется сегодня, а все последующие – с одинаковым интервалом. В примере с займом нам необходима годовая ставка и интервал между денежными потоками – ровно год. Тогда расчёт IRR с помощью функции ВСД выглядит так:

 

 

Не забывайте, что «сегодня» — это первое число первого года.

Excel говорит, что IRR проекта составляет 10% годовых. Это значит, что вложить $100 и получить в конце каждого года в течение двух лет $60 и $55 — это равносильно размещению $100 на двухлетнем депозите под 10% годовых. Со ставкой 10% таблица учёта нашего займа примет следующий вид:

 

 

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

Функцию ВСД (IRR) мы настоятельно рекомендуем использовать для расчета IRR в задачах раздела С экзамена по «Финанснвому менеджменту» ACCA FM. С её помощью операция расчёта IRR при рассчитанных денежных потоках займет считанные секунды. Я думаю, вы не сильно расстроитесь, узнав о том, что на момент написания настоящей статьи компьютерный арсенал экзаменов ACCA включал только английские варианты финансовых функций. Это означает, что на экзамене вместо =ВСД() придется вводить привычно знакомые английские символы =IRR().

У функции ВСД есть один минус: она подходит только для проектов, у которых денежные потоки случаются с одинаковыми интервалами (год, квартал, месяц). Если же денежные потоки неравномерны, ВСД не в силах решить задачу нахождения IRR.

Как быть, если в реальной жизни денежные потоки происходят в разные месяцы и даже дни?

 

Функция ЧИСТВНДОХ

Если требуется посчитать IRR для графика неравномерных денежных потоков, то следует использовать функцию ЧИСТВНДОХ (XIRR). ЧИСТВНДОХ — функция более совершенная. Она умеет учитывать время возникновения денежных потоков с точностью до дня.

Перед применением функции нужно построить график денежных потоков с датами их возникновения. Первая дата в этом графике – дата осуществление первых инвестиций, т.е. начало проекта («сегодня» в нашем примере). Если интервал денежных потоков ровно год, то результат функции ЧИСТВНДОХ будет такой же, как и результат применения ВСД:

 

 

Теперь допустим, что первый чистый денежный приток в $60 мы получаем 31 августа 2021 года, то есть на четыре месяца раньше. Тогда выгода от проекта больше, так как получить деньги раньше всегда финансово предпочтительнее. ЧИСТВНДОХ подтверждает, что доходность такого проекта составляет уже 11.5% годовых:

 

 

Все практические прелести применения функции ЧИСТВНДОХ мы разбираем на наших тренингах по финансовому моделированию и трансформации отчётности из РСБУ в МСФО. А пока итогом наших умозаключений будем считать три важных тезиса про IRR:

  1. IRR – это доходность в процентах годовых, которую проект обещает своим инвесторам
  2. Чтобы рассчитать IRR требуется исключительно график денежных потоков проекта
  3. На практике для поиска IRR используем функции MS EXCEL ВСД (IRR) и ЧИСТВНДОХ (XIRR)

 

Второй частью нашей трилогии мы раскроем как считать IRR многолетних проектов без помощи MS Excel, когда под рукой только ручка и листок бумаги. Обсудим техническую часть IRR и технику расчёта, которой вам придётся блеснуть на экзаменах квалификации АССА FM «Финансовый менеджмент» и ACCA IFB «Введение в финансы и управление бизнесом».

Третья часть будет посвящена раскрытию основного недостатка IRR. Вы наверняка уже что-то слышали про IRR и реинвестирование денежных потоков? Да? Нет? В любом случае – следите за обновлениями! Обещаем подойти к раскрытию вопроса по-взрослому!

Продолжение здесь.

Расчет доходности инвестиций в EXCEL

20 ноя 2019  Сергей  Кикевич  Все авторы

Приложение к статье:
файл для скачивания

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

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

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

Как считать доходность?

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

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

$$ R =\frac{A}{B}$$ 

А – полученный доход

В – стартовые инвестиции

Представим себе жизненную ситуацию, когда человек в январе инвестировал 10 000 р, а в декабре – 90 000 р. К концу года на инвестиционном счете оказалось 110 000 р (ценные бумаги выросли в цене). Какова доходность инвестиций? Что на что делить? Если мы возьмем доход в 10 000 р и разделим на сумму всех инвестиций – 100 000 р, то получим очень сложно интерпретируемый результат – 10%. Ведь большую часть срока на счете находилось всего 10 000 р, а остаток добавлен только за месяц до конца года …

Или еще более интересный пример. В январе инвестор положил на брокерский счет 100 000 р, а в декабре забрал с него 90 000 р. К концу года на брокерском счете фигурировала сумма 15 000 р. Если просто сложить пополнения и изъятия получится что суммарная инвестиция равна 100 000 – 90 000 = 10 000 р. Разделив доход на суммарные инвестиции, получим слишком оптимистичные 50%. Очевидно, что так делать нельзя …

IRR или Внутренняя норма доходности (ВНД)

Одним из самых простых и распространенных способов измерить результативность инвестиций является расчет IRR (Internal Rate of Return, Внутренняя норма доходности). IRR – это не совсем доходность. Формально IRR или Внутренняя норма доходности (ВНД) – это  процентная ставка, при которой приведённая стоимость денежных поступлений (списаний) равна размеру исходных инвестиций. IRR очень распространен в бизнесе и финансах. При помощи этой величины считается, например, рентабельность проектов в бизнесе. Аналогично считается доходности к погашению для облигаций. IRR можно считать это своего рода стандартом при измерении результативности.

Еще одно важное преимущество – IRR легко считается в EXCEL и других электронных таблицах. 

Если IRR меньше ставки по депозитам в Сбербанке, то надо задуматься, все ли нормально с инвестиционной стратегией.

Шаблон для расчета IRR инвестиций в EXCEL

Для быстрого расчета результативности инвестиций предлагаем простой шаблон в EXCEL.

Шаблон считает IRR для каждого из периодов инвестиций, и за последние 6 периодов (колонка «IRR за 6 периодов»). Периоды могут быть произвольными: один месяц, один год. Более того, в калькуляторе используется функция XIRR (ЧИСТВНДОХ), которая умеет считать IRR даже для неравных между собой периодов. Это значит, что в колонке «Дата» можно указывать любую дату, а не только начало месяца или, например, конец года. Удобнее всего вносить новые данные каждый раз, когда пополняется портфель или когда происходит изъятие средств. Для интереса можно вносить новые данные чаще, даже когда нет пополнений портфеля. Например можно указывать даты, когда в размере портфеля происходят какие-то значимые изменения или просто с некоторой заданной регулярностью.

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

Учет результатов инвестиций для сложных портфелей

Важное свойство калькулятора – это возможность измерения результативности инвестиций для широко диверсифицированных портфелей. Часто встречаются ситуации, когда у инвестора несколько брокерских счетов (российский и зарубежный), часть денег размещено в ПИФах через Управляющую компании. Кроме всего, может быть открыт ОМС (Обезличенный металлические счета – используются для покупки драгоценных металлов), куплена недвижимость и тому подобное. В таком случае рассчитать результат инвестиций для итогового портфеля бывает довольно проблематично…  Предлагаемый калькулятор поможет справиться с этой задачей. Достаточно регулярно (например, один раз в год) считать суммарный размер всех активов в портфеле и вносить в таблицу пополнения и изъятия.

Расчет доходности к погашению для облигаций

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

Для вычисления доходности к погашению необходимо внести сумму покупки облигации и планируемые поступления в виде дивидендов.

В примере показан прогноз доходности к погашению для облигации с купоном 40 руб (два раза в год) и текущей стоимостью 98% (980 р) и погашением в 2024 году. Предполагается, что облигация держится до погашения. В данном случае имеет релевантность только последнее значение IRR (в момент погашения), так как изменение цены облигации прогнозировать очень сложно. IRR за 6 периодов тоже большого смысла для облигаций не имеет.

Ограничения калькулятора

Калькулятор будет показывать, в том числе, нереализованный доход. Например, если ценная бумага выросла в цене, но еще не продана, то такой доход инвестора называется нереализованным. Поэтому предлагаемый шаблон не может быть использован для расчета налогов (НДФЛ). t}-C_0\\ &\textbf{where:}\\ &C_t=\text{Net cash inflow during the period t}\\ &C_0=\text{Total initial investment costs}\\ &IRR=\text{The internal rate of return}\\ &t=\text{The number of time periods}\\ \end{aligned}​0=NPV=t=1∑T​(1+IRR)t

To calculate IRR using the formula, one would set NPV equal to zero and solve for the discount rate, which is the IRR. However, because of the nature of the formula, IRR cannot be easily calculated analytically and therefore must instead be calculated either through trial-and-error or by using software programmed to calculate IRR. This can be done in Excel.1

Generally speaking, the higher an internal rate of return, the more desirable an investment is to undertake. IRR is uniform for investments of varying types and, as such, IRR can be used to rank multiple prospective investments or projects on a relatively even basis. In general, when comparing investment options whose other characteristics are similar, the investment with the highest IRR would probably be considered the best.

How to Calculate IRR in Excel

Using the IRR function in Excel makes calculating the IRR easy. Excel does all the necessary work for you, arriving at the discount rate you are seeking to find. All you need to do is combine your cash flows, including the initial outlay as well as subsequent inflows, with the IRR function.1 The IRR function can be found by clicking on the Formulas Insert (fx) icon.2

Here is a simple example of an IRR analysis with cash flows that are known and annually periodic (one year apart). Assume a company is assessing the profitability of Project X. Project X requires $250,000 in funding and is expected to generate $100,000 in after-tax cash flows the first year and grow by $50,000 for each of the next four years.

The initial investment is always negative because it represents an outflow. Each subsequent cash flow could be positive or negative, depending on the estimates of what the project delivers or requires as capital injection in the future. In this case, the IRR is 56.72%, which is quite high.

Keep in mind that the IRR is not the actual dollar value of the project. It is the annual return that makes the net present value equal to zero.

Excel also offers two other functions that can be used in IRR calculations, the XIRR and the MIRR. XIRR is used when the cash flow model does not exactly have annual periodic cash flows.3 The MIRR is a rate of return measure that also includes the integration of cost of capital as well as the risk-free rate.4

Краткая справка

5

When to Use IRR

There are several formulas and concepts that can be used when seeking to identify an expected return. The IRR is generally most ideal for analyzing the potential return of a new project that a company is considering undertaking.

You can think of the internal rate of return as the rate of growth an investment is expected to generate annually. Thus, it can be most similar to a compound annual growth rate (CAGR). In reality, an investment will usually not have the same rate of return each year. Usually, the actual rate of return that a given investment ends up generating will differ from its estimated IRR.

In capital planning, one popular scenario for IRR is comparing the profitability of establishing new operations with that of expanding existing ones. For example, an energy company may use IRR in deciding whether to open a new power plant or to renovate and expand a previously existing one. While both projects could add value to the company, it is likely that one will be the more logical decision as prescribed by IRR.

What IRR Tells You

Most IRR analysis will be done in conjunction with a view of a company’s weighted average cost of capital (WACC) and net present value calculations. IRR is typically a relatively high value, which allows it to arrive at a NPV of zero. Most companies will require an IRR calculation to be above the WACC. Analysis will also typically involve NPV calculations at different assumed discount rates.

In theory, any project with an IRR greater than its cost of capital should be a profitable one. In planning investment projects, firms will often establish a  required rate of return (RRR) to determine the minimum acceptable return percentage that the investment in question must earn in order to be worthwhile. The RRR will be higher than the WACC.

Any project with an IRR that exceeds the RRR will likely be deemed a profitable one, although companies will not necessarily pursue a project on this basis alone. Rather, they will likely pursue projects with the highest difference between IRR and RRR, as these likely will be the most profitable.

IRR may also be compared against prevailing rates of return in the  securities market. If a firm can’t find any projects with IRR greater than the returns that can be generated in the financial markets, it may simply choose to invest money into the market. Market returns can also be a factor in setting a required rate of return.

IRR vs. Compound Annual Growth Rate

The CAGR measures the annual return on an investment over a period of time. The IRR is also an annual  rate of return. However, CAGR typically uses only a beginning and ending value to provide an estimated annual rate of return. IRR differs in that it involves multiple periodic cash flows–reflecting the fact that cash inflows and outflows often constantly occur when it comes to investments. Another distinction is that CAGR is simple enough that it can be calculated easily.

IRR vs. Return on Investment (ROI)

Companies and analysts may also look at the return on investment when making capital budgeting decisions. ROI tells an investor about the total growth, start to finish, of the investment. It is not an annual rate of return. IRR tells the investor what the annual growth rate is. The two numbers would normally be the same over the course of one year, but they won’t be the same for longer periods of time.

Return on investment is the percentage increase or decrease of an investment from beginning to end. It is calculated by taking the difference between the current or expected future value and the original, beginning value, divided by the original value and multiplied by 100.

ROI figures can be calculated for nearly any activity into which an investment has been made and an outcome can be measured. However, ROI is not necessarily the most helpful for long time frames. It also has limitations in capital budgeting, where the focus is often on periodic cash flows and returns.

Limitations of the IRR

IRR is generally most ideal for use in analyzing capital budgeting projects. It can be misconstrued or misinterpreted if used outside of appropriate scenarios. In the case of positive cash flows followed by negative ones and then by positive ones, the IRR may have multiple values. Moreover, if all cash flows have the same sign (i.e., the project never turns a profit), then no discount rate will produce a zero NPV.

Within its realm of uses, IRR is a very popular metric for estimating a project’s annual return. However, it is not necessarily intended to be used alone. IRR is typically a relatively high value, which allows it to arrive at a NPV of zero. The IRR itself is only a single estimated figure that provides an annual return value based on estimates. Since estimates in both IRR and NPV can differ drastically from actual results, most analysts will choose to combine IRR analysis with scenarios analysis. Scenarios can show different possible NPVs based on varying assumptions.

As mentioned, most companies do not rely on IRR and NPV analysis alone. These calculations are usually also studied in conjunction with a company’s WACC and a RRR, which provides for further consideration.

Companies usually compare IRR analysis to other tradeoffs. If another project has a similar IRR with less upfront capital or simpler extraneous considerations then a simpler investment may be chosen despite IRRs.

In some cases, issues can also arise when using IRR to compare projects of different lengths. For example, a project of short duration may have a high IRR, making it appear to be an excellent investment. Conversely, a longer project may have a low IRR, earning returns slowly and steadily. The ROI metric can provide some more clarity in these cases. Though some managers may not want to wait out the longer time frame.

Investing Based on IRR

The internal rate of return rule is a guideline for evaluating whether to proceed with a project or investment. The  IRR rule states that if the internal rate of return on a project or investment is greater than the minimum required rate of return, typically the cost of capital, then the project or investment can be pursued. Conversely, if the IRR on a project or investment is lower than the cost of capital, then the best course of action may be to reject it. Overall, while there are some limitations to IRR, it is an industry standard for analyzing capital budgeting projects.

Frequently Asked Questions

What does internal rate of return mean?

The internal rate of return (IRR) is a financial metric used to assess the attractiveness of a particular investment opportunity. When you calculate the IRR for an investment, you are effectively estimating the rate of return of that investment after accounting for all its projected cashflows together with the time value of money. When selecting among several alternative investments, the investor would then select the investment with the highest IRR, provided it is above the investor’s minimum threshold. The main drawback of IRR is that it is heavily reliant on projections of future cashflows, which are notoriously difficult to predict.

Is IRR the same as ROI?

Although IRR is sometimes referred to informally as a project’s “return on investment”, it is different from the way most people use that phrase. Often, when people refer to ROI they are simply referring to the percentage return generated from an investment in a given year, or across a stretch of time. But that type of ROI does not capture the same nuances as IRR, and for that reason IRR is generally preferred by investment professionals. Another advantage of IRR is that its definition is mathematically precise, whereas the term ROI can mean different things depending on the context or the speaker.

What is a good internal rate of return?

Whether an IRR is good or bad will depend on the cost of capital and opportunity cost of the investor. For instance, a real estate investor might pursue a project with a 25% IRR if comparable alternative real estate investments offer a return of, say, 20% or lower. This comparison assumes that the riskiness and effort involved in making these difficult investments is roughly the same, however. If the investor can obtain a slightly lower IRR from a project that is considerably less risky or time-consuming, then they might happily accept that lower-IRR project.


Расчет 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 инвестиций, как в предыдущем примере, но с учетом того, что компания будет занимать деньги, чтобы вложить их обратно (отрицательные денежные потоки) по ставке, отличной от ставки, по которой она будет реинвестировать. заработанные деньги (положительный денежный поток). Диапазон от C5 до E5 представляет собой диапазон денежных потоков инвестиций, а ячейки E10 и E11 представляют ставку по корпоративным облигациям и ставку по инвестициям.

На изображении ниже показана формула Excel MIRR.{\ frac {1} {n-1}} - 1 \ end {выровнено} (NPV (частота, значения [отрицательные]) × (1 + frate) −NPV (rrate, значения [положительные]) × (1 + rrate) n) n − 11 −1

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

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

.

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

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


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

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

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

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

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

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

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

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

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

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

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


Об авторе

Дж. Карлтон Коллинз ([email protected]) - консультант по технологиям, инструктор по CPE и редактор JofA .

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

Задать вопрос

У вас есть вопросы о технологиях для этой колонки? Или, прочитав ответ, у вас есть лучшее решение? Отправьте их на адрес jofatech @ aicpa.орг. Сожалеем, что не смогли индивидуально ответить на все заданные вопросы.

Как использовать функцию 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, указывает на то, что проект генерирует норму прибыли, равную ставке дисконтирования. Другими словами, оба варианта - инвестирование денег в проект B или размещение денег на высокодоходном сберегательном счете под процентную ставку 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 (функция) - служба поддержки Office

В этой статье описаны синтаксис формулы и использование функции IRR в Microsoft Excel.

Описание

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

Синтаксис

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

Аргументы функции ВСД следующие:

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

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

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

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

  • Guess Необязательно. Число, которое вы догадались, близко к результату IRR.

    • Microsoft Excel использует итерационный метод расчета IRR.Начиная с предположения, IRR выполняет циклическое вычисление до тех пор, пока результат не станет точным в пределах 0,00001 процента. Если IRR не может найти результат, который работает после 20 попыток, #NUM! возвращается значение ошибки.

    • В большинстве случаев вам не нужно давать предположение для расчета IRR. Если предположение опущено, предполагается, что оно равно 0,1 (10 процентов).

    • Если IRR дает #NUM! значение ошибки, или, если результат не близок к ожидаемому, попробуйте еще раз с другим значением для предположения.

Примечания

IRR тесно связана с NPV, функцией чистой приведенной стоимости. Норма прибыли, рассчитываемая IRR, представляет собой процентную ставку, соответствующую нулевой (нулевой) чистой приведенной стоимости. Следующая формула показывает, как связаны NPV и IRR:

NPV (IRR (A2: A7), A2: A7) равняется 1,79E-09 [В пределах точности расчета IRR значение фактически равно 0 (нулю).]

Пример

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

Данные

Описание

–70000 долларов США

Начальная стоимость бизнеса

12 000 долларов США

Чистая прибыль за первый год

15 000 долл. США

Чистая прибыль за второй год

18 000 долл. США

Чистая прибыль за третий год

21 000 долл. США

Чистая прибыль за четвертый год

26 000 долл. США

Чистая прибыль за пятый год

Формула

Описание

Результат

= IRR (A2: A6)

Внутренняя норма доходности инвестиций через четыре года

-2.1%

= IRR (A2: A7)

Внутренняя норма прибыли через пять лет

8,7%

= IRR (A2: A4, -10%)

Чтобы рассчитать внутреннюю норму доходности через два года, необходимо включить предположение (в этом примере -10%).

-44,4%

Функция IRR - формула, примеры, как использовать IRR в Excel

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

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

Эта функция очень полезна при финансовом моделировании Что такое финансовое моделирование Финансовое моделирование выполняется в Excel для прогнозирования финансовых показателей компании. Обзор того, что такое финансовое моделирование, как и зачем строить модель, поскольку оно помогает рассчитать доходность инвестиций на основе ряда денежных потоков. Бесплатные руководства по оценке, позволяющие изучать наиболее важные концепции в удобном для вас темпе.Эти статьи научат вас передовым методам оценки бизнеса и научат оценивать компанию с помощью сопоставимого анализа компании, моделирования дисконтированного денежного потока (DCF) и прецедентных транзакций, используемых в инвестиционно-банковском деле, исследовании капитала и т. Д. Он часто используется предприятиями для сравнения капитальных проектов и принятия решения о них. Один пример - когда компании представлены две возможности: одна - инвестировать в новый завод, а вторая - расширять существующий завод. Используя функцию IRR, мы можем оценить IRR для обоих сценариев и проверить, какой из них выше, чем средневзвешенная стоимость капитала (WACCWACCWACC - это средневзвешенная стоимость капитала фирмы и представляет собой смешанную стоимость капитала, включая собственный капитал и заемные средства.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 и др. Дж.П. Морган и Феррари

Функция 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 (значения; [предположить])

Где:

  • Значения (обязательно) - массив или ссылка на диапазон ячеек, представляющих ряд денежных потоков, для которых вы хотите найти внутреннюю норму прибыли.
  • Угадайте (необязательно) - ваше предположение, какой может быть внутренняя норма прибыли.Он должен быть указан в процентах или в виде соответствующего десятичного числа. Если не указано, используется значение по умолчанию 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 IRR циклически перебирает вычисления, пока не найдет результат с точностью до 0,00001%. Если после 20 итераций точный результат не найден, # ЧИСЛО! возвращается ошибка.

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

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

= СУММ (C2: C5)

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

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

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

Использование функции IRR в Excel - примеры формул

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

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

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

Найти IRR в Excel очень просто:

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

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

= IRR (B2: B8)

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

Пример 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 следует отклонить, поскольку его IRR составляет всего 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 и финансовом калькуляторе

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

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

    Хотя это непростой расчет, формула IRR в Excel упрощает вычисление IRR для мгновенных инвестиционных решений.

    Формула Excel для IRR

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

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

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

    Калькулятор IRR в Excel Пример

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

    • Первоначальные инвестиции: 50 000 долларов США

    • Последующие денежные потоки: 20 000 долларов США каждый год в течение 5 лет; 40 000 долларов в год 6.

    Как найти IRR на финансовом калькуляторе

    Если вы не используете Excel, вы все равно можете рассчитать IRR с помощью финансового калькулятора (например, Texas Instrument BA II Plus).Вам понадобятся те же цифры, которые вы использовали для определения IRR в Excel: первоначальные инвестиции и последующие денежные потоки.

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

    • Нажмите кнопку CF (денежный поток), чтобы запустить регистр денежных потоков.

    • Введите начальные инвестиции (отрицательное число).

    • Нажмите Enter.

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

    • Введите сумму за год 1.

    • Дважды нажмите стрелку вниз, чтобы ввести денежный поток за 2-й год.

    • Повторяйте процесс, пока не введете прогнозируемый денежный поток за каждый год.

    • Нажмите кнопку IRR.

    • Нажмите кнопку CPT для вашего IRR.

    Расчет IRR с помощью финансового калькулятора Пример

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

    • Первоначальные инвестиции: 150 000 долларов США

    • Последующие денежные потоки: 50 000 долларов США в год для 5 лет.

    Шаг 1 : Нажмите кнопку движения денежных средств (CF)

    При этом запускается регистр движения денежных средств, когда вы вводите свои первоначальные инвестиции. Поскольку выплачиваются наличные, это отрицательное число. В этом примере введите - 150 000 долларов и нажмите Enter.

Опубликовано в категории: Разное

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

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