Irr в эксель

ВСД (функция ВСД)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

IRR (Internal Rate of Return), или ВНД – показатель внутренней нормы доходности инвестиционного проекта. Часто применяется для сопоставления различных предложений по перспективе роста и доходности. Чем выше IRR, тем большие перспективы роста у данного проекта. Рассчитаем процентную ставку ВНД в Excel.

Экономический смысл показателя

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

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

Формула для расчета показателя вручную:

• CFt – денежный поток за определенный промежуток времени t;
• IC – вложения в проект на этапе вступления (запуска);
• t – временной период.

На практике нередко коэффициент IRR сравнивают со средневзвешенной стоимостью капитала:

1. ВНД выше – следует внимательно рассмотреть данный проект.
2. ВНД ниже – нецелесообразно вкладывать средства в развитие проекта.
3. Показатели равны – минимально допустимый уровень (предприятие нуждается в корректировке движения денежных средств).

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

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

Быстро рассчитать IRR можно с помощью встроенной функции ВСД. Синтаксис:

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

Возьмем условные цифры:

Первоначальные затраты составили 150 000, поэтому это числовое значение вошло в таблицу со знаком «минус». Теперь найдем IRR. Формула расчета в Excel:

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

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

Модифицируем таблицу с исходными данными для примера:

Обязательные аргументы функции ЧИСТВНДОХ:

• значения – денежные потоки;
• даты – массив дат в соответствующем формате.

Формула расчета IRR для несистематических платежей:

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

• значения – платежи;
• ставка финансирования – проценты, выплачиваемые за средства в обороте;
• ставка реинвестирования.

Предположим, что норма дисконта – 10%. Имеется возможность реинвестирования получаемых доходов по ставке 7% годовых. Рассчитаем модифицированную внутреннюю норму доходности:

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

Графический метод расчета IRR в Excel

Значение IRR можно найти графическим способом, построив график зависимости чистой приведенной стоимости (NPV) от ставки дисконтирования. NPV – один из методов оценки инвестиционного проекта, который основывается на методологии дисконтирования денежных потоков.

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

Для расчета NPV в Excel можно использовать функцию ЧПС:

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

Функция дисконтировала денежные потоки 1-4 периодов по ставке 10% (0,10). При анализе нового инвестиционного проекта точно определить ставку дисконтирования и все денежные потоки невозможно. Имеет смысл посмотреть зависимость NPV от этих показателей. В частности, от стоимости капитала (ставки дисконта).

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

Посмотрим результаты на графике:

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

Как рассчитывается показатель IRR

IRR, ВНД, ВСД, или внутренняя норма доходности – показатель инвестиционного анализа, который позволяет определить доходность вложения средств и максимально возможную ставку по привлекаемым заемным средствам. Он помогает сравнить несколько проектов и выбрать наиболее удачный вариант для вложений. Поскольку он рассчитывается через NPV, то из 4-х методов удобнее всего использовать автоматизированный – через функцию ВНД табличного редактора Excel.

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

IRR (Internal Rate of Return – внутренняя норма доходности) – это ставка дисконтирования, при которой значение NPV (Net Present Value – чистая приведенная стоимость) равно нулю.

Справка! IRR можно рассматривать двояко:

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

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

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

В литературе встречаются и иные названия IRR: внутренняя норма доходности (аббревиатура – ВНД), внутренняя ставка доходности, внутренняя норма рентабельности и др.

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

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

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

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

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

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

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

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

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

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

Внутренняя норма доходности (IRR) инвестиционного проекта – что это такое и как рассчитать

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

Статья посвящена экономическому смыслу понятия IRR и тому, как рассчитать этот показатель.

Что такое IRR инвестиционного проекта и зачем он нужен

Объяснить, что это такое – внутренняя норма доходности (IRR), можно легко и простыми словами. Отечественная и мировая экономика давно применяет этот показатель, хотя называется он по-разному: внутренним коэффициентом окупаемости (ВКО), внутренней ставкой дохода предприятия (ВСДП), внутренней нормой доходности (ВНД) и т. д.

С английского языка термин переводится как «внутренняя ставка возврата» (Internal Rate of Return, сокращенно IRR), что, вероятно, наиболее точно характеризует смысл и сущность понятия.

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

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

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

Слово «дисконтированная» означает, что каждый чистый денежный поток необходимо привести к разным процентным ставкам, действующим на протяжении периода инвестирования. Имеются в виду банковские проценты, индекс инфляции, уровень девальвации (в случае валютных вложений) и т. д.

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

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

Первый способ применения показателя IRR – оценка прибыльности инвестиции. Чем выше значение, тем предпочтительней данный проект.

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

Как рассчитать внутреннюю норму доходности

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

Уравнение с нулевой суммой будет приведено ниже.

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

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

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

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

Исходной формулой для вычисления внутренней нормы доходности служит следующее уравнение:

• NPV – чистая приведенная стоимость проекта;
• N – количество расчетных периодов (обычно лет);
• T – номер расчетного периода;
• IS – затраты на проект в первоначальном периоде (стартовый размер инвестиции) и последующие вложения;
• IRR – внутренняя норма прибыльности.

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

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

• IRRmin – минимальная внутренняя норма прибыльности;
• N – количество расчетных периодов;
• IS T – размеры инвестиций по каждому периоду;
• IS – общая сумма инвестиции.

Для наглядности применения этой формулы имеет смысл рассмотреть пример расчета.

Объект инвестирования – недвижимость – квартира, сдаваемая в аренду. На ее приобретение должна быть потрачена сумма 1,5 млн руб. Прогнозируется поступление арендной платы по следующему графику:

• 1-й год – 620 тыс. руб.
• 2-й год – 632 тыс. руб.
• 3-й год – 790 тыс. руб.

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

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

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

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

В программе Эксель есть встроенная функция ВСД – ею и надлежит пользоваться. При этом следует придерживаться простых правил и выполнить несложную последовательность действий.

Для расчета IRR в Excel необходимо:

1. Войти в программу.
2. Создать книгу с таблицей денежных потоков и их датами. Одно из значений обязательно должно иметь отрицательное значение – это сумма инвестиции, то есть затраты на реализацию. В таблице могут содержаться данные нескольких проектов для сравнения.
3. Выбрать функцию IRR в мастере функций (для русского интерфейса ВНД или ВСД) нажатием кнопки fx.
4. Отметить участок нужного столбца с данными, подлежащими анализу. В строке появится что-то вроде «IRR(B4:B:12, 7,2%)».
5. Нажать кнопку «OK».

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

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

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

Онлайн-калькуляторы внутренней нормы доходности

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

Анализ полученных данных

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

Может ли быть внутренняя норма окупаемости больше 100 процентов? Теоретически да, но на практике такое случается крайне редко. Каково же в таком случае нормальное значение IRR?

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

• IRR меньше RT – проект будет явно убыточным для инвестора;
• IRR равен RT – вложения только окупятся, но дохода не принесут;
• IRR больше RT – ожидается прибыль.

Сравнение возможно также с уровнем минимальной ожидаемой доходности компании-инвестора, а она в каждой фирме своя.

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

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

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

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

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

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

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

Ставка дисконтирования может не браться в учет, так как в формулах она не фигурирует.

Отличие модифицированной внутренней нормы прибыли MIRR от IRR

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

Методология расчета модифицированной внутренней нормы доходности MIRR основана на следующих положениях:

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

В конечном виде формула модифицированной внутренней нормы прибыли выглядит так:

• MIRR – модифицированная внутренняя норма прибыли;
• N – инвестиционный период в годах;
• DF – прибыли от инвестиции;
• DC – суммы инвестиций;
• WACC – сумма средневзвешенной стоимости капитала;
• R – ставка дисконта;
• i — номер периода.

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

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

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

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

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

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

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

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

где:

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

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

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

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

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

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

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

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

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

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

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

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

Пример расчета IRR в Excel c помощью встроенной функции

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

Внутренняя норма доходности (E16) =ВСД(E6:E15)

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

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

Пример расчета IRR через надстройку «Поиск решений»

Второй вариант расчета подразумевает использование надстройки «Поиск решений» для поиска оптимального значения ставки дисконтирования для NPV=0. Для этого необходимо рассчитать чистый дисконтированный доход (NPV).

На рисунке ниже показаны формулы расчета дисконтированного денежного потока по годам, сумма которых дает чистый дисконтированный доход. Формула расчета дисконтированного денежного потока (DCF) следующая:

Дисконтированный денежный поток (F) =E7/(1+$F$17)^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. Для решения данной задачи используется другая финансовая формула ЧИСТВНДОХ (). Данная формула включает в себя массив дат и денежные потоки. Формула расчета будет иметь следующий вид:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Резюме

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

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