IRR (Internal Rate of Return), или ВНД – показатель внутренней нормы доходности инвестиционного проекта. Часто применяется для сопоставления различных предложений по перспективе роста и доходности. Чем выше IRR, тем большие перспективы роста у данного проекта. Рассчитаем процентную ставку ВНД в Excel.
Экономический смысл показателя
Другие наименования: внутренняя норма рентабельности (прибыли, дисконта), внутренний коэффициент окупаемости (эффективности), внутренняя норма.
Коэффициент IRR показывает минимальный уровень доходности инвестиционного проекта. По-другому: это процентная ставка, при которой чистый дисконтированный доход равен нулю.
Формула для расчета показателя вручную:
, где
- CFt – денежный поток за определенный промежуток времени t;
- IC – вложения в проект на этапе вступления (запуска);
- t – временной период.
На практике нередко коэффициент IRR сравнивают со средневзвешенной стоимостью капитала:
- ВНД выше – следует внимательно рассмотреть данный проект.
- ВНД ниже – нецелесообразно вкладывать средства в развитие проекта.
- Показатели равны – минимально допустимый уровень (предприятие нуждается в корректировке движения денежных средств).
Часто 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 с осью абсцисс и есть внутренняя доходность предприятия.
КУРС
EXCEL ACADEMY
Научитесь использовать все прикладные инструменты из функционала MS Excel.
В статье будет приведен расчет показателей эффективности проекта, с учетом дисконтирования.
- ЧДД или чистый дисконтированный доход от инвестиционного проекта;
- Внутренняя норма доходности.
Рассмотрим эти два показателя подробнее и рассчитаем пример работы с ними в Excel. Еще больше о возможностях Excel можно узнать на нашем открытом курсе «Аналитика в Excel».
Net Present Value (NPV, чистый дисконтированный доход) — один из самых распространенных показателей эффективности инвестиционного проекта.
Это разность между дисконтированными по времени поступлениями от проекта и инвестиционными затратами на него.
Метод расчета NPV:
- Определяем текущую стоимость затрат (инвестиции в проект)
- Производим расчет текущей стоимости денежных поступлений от проекта, для этого доходы за каждый отчетный период приводятся к текущей дате
CF – денежный поток;
r – ставка дисконта.
3. Сравниваем текущую стоимость инвестиций (наши затраты) в проект (Io) с текущей стоимостью доходов (PV). Разница между ними будет чистый дисконтированный доход — NPV.
NPV показывает инвестору доход или убыток от вложений средств в проект по сравнению с доходом от хранения денег в банке.
Если NPV больше 0, то инвестиции принесут больше дохода, нежели чем аналогичный вклад в банке.
Формула 1 модифицируется если инвестиционные вложения в проект осуществляются в несколько этапов (периодов).
CF – денежный поток;
I – сумма инвестиционных вложений в проект в t-ом периоде;
r – ставка дисконтирования;
n – количество периодов.
Internal Rate of Return (Внутренняя норма доходности, IRR) определяет ставку дисконтирования при которой инвестиции равны 0 (NPV=0), или другими словами затраты на проект равны его доходам.
IRR = r, при которой NPV = f(r) = 0, находим из формулы:
CF – денежный поток;
I – сумма инвестиционных вложений в проект в t-ом периоде;
n – количество периодов.
Этот показатель показывает норму доходности или возможные затраты при вложении денежных средств в проект (в процентах).
Пример расчета NPV в Excel
В MS Excel 2010 для расчета NPV используется функция =ЧПС().
Найдем чистый дисконтированный доход (NPV) проекта, требующего вложений инвестиций на 90 тыс. руб., и денежный поток которого распределен по времени рис 1. , и ставка дисконта равна 10%.
Рассчитаем показатель NPV по формуле Excel:
=ЧПС(D3;C3;C4:C11)
D3 – ставка дисконта;
C3 – вложения в 0 периоде (наши инвестиционные затраты в проект);
C4:C11 – денежный поток проекта за 8 периодов.
В итоге показатель чистого дисконтированного дохода равен 51,07 >0, это говорит о том, что в проект стоит инвестировать.
Расчет IRR в Excel
Для определения IRR в Excel используется встроенная функция
=ЧИСТВНДОХ()
Но так как у нас в примере данные поступали в равные интервалы времени можно использовать функцию
=ВСД(C3:C11)
Доходность вложения в проект равна 38%.
В завершение картинка финансового анализа проекта целиком.
КУРС
EXCEL ACADEMY
Научитесь использовать все прикладные инструменты из функционала MS Excel.
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More…Less
This article describes the formula syntax and usage of the IRR function in Microsoft Excel.
Description
Returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.
Syntax
IRR(values, [guess])
The IRR function syntax has the following arguments:
-
Values Required. An array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.
-
Values must contain at least one positive value and one negative value to calculate the internal rate of return.
-
IRR uses the order of values to interpret the order of cash flows. Be sure to enter your payment and income values in the sequence you want.
-
If an array or reference argument contains text, logical values, or empty cells, those values are ignored.
-
-
Guess Optional. A number that you guess is close to the result of IRR.
-
Microsoft Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can’t find a result that works after 20 tries, the #NUM! error value is returned.
-
In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent).
-
If IRR gives the #NUM! error value, or if the result is not close to what you expected, try again with a different value for guess.
-
Remarks
IRR is closely related to NPV, the net present value function. The rate of return calculated by IRR is the interest rate corresponding to a 0 (zero) net present value. The following formula demonstrates how NPV and IRR are related:
NPV(IRR(A2:A7),A2:A7) equals 1.79E-09 [Within the accuracy of the IRR calculation, the value is effectively 0 (zero).]
Example
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
|
Data |
Description |
|
|
-$70,000 |
Initial cost of a business |
|
|
$12,000 |
Net income for the first year |
|
|
$15,000 |
Net income for the second year |
|
|
$18,000 |
Net income for the third year |
|
|
$21,000 |
Net income for the fourth year |
|
|
$26,000 |
Net income for the fifth year |
|
|
Formula |
Description |
Result |
|
=IRR(A2:A6) |
Investment’s internal rate of return after four years |
-2.1% |
|
=IRR(A2:A7) |
Internal rate of return after five years |
8.7% |
|
=IRR(A2:A4,-10%) |
To calculate the internal rate of return after two years, you need to include a guess (in this example, -10%). |
-44.4% |
Need more help?
How to Calculate IRR in Excel
Using the IRR formula in Excel is quick and easy
Updated on December 30, 2021
What to Know
- Select the cells you want to check and right-click. Select Format Cells > Number > Number or Accounting.
- Change the formatting and select OK. Select a cell where you want to place the IRR and press Enter.
- The IRR syntax is =IRR(values,[guess]). Values must have one positive and one negative number and be in the desired order.
Knowing the internal rate of return (IRR) of an investment allows you to plan for future growth and expansion of that investment. To calculate those numbers, use the IRR formula in Microsoft Excel 2019, 2016, 2013, 2010, 2007, Excel for Mac, Excel for Microsoft 365, and Excel Online.
Understanding the IRR Function
The syntax for the IRR function is as follows: =IRR(values,[guess]) where «values» are the list of values representing a series of cash flows in equal increments like a particular date every month, or monthly. Values can also be cell references or ranges of references. For example, A2:A15 would be the values in the range of cells A2 through A15.
The «guess» is an optional argument that you guess is close to your IRR result. If you don’t use this argument, Excel defaults to the value of 0.1 (10%). When using the Guess value, you receive a #NUM error, or the end result isn’t what you expected. But you can always change this value.
Your values must contain at least 1 positive number and 1 negative number for the internal rate of return formula to work correctly. Your first negative number will most likely be the initial investment, but could have other negative values in the array.
Additionally, you have to make sure you enter your values in the order you want. IRR uses the order of the values to calculate. You must also make sure your array of numbers is formatted as numbers. Text, logical values, and empty cells will be ignored by Excel’s IRR formula.
How to Calculate IRR in Excel
You’ll first want to ensure that your sequence of values for net cash flow in all your entries is in the Number format. To accomplish this, do the following:
-
Select the cells you want to check or change the format to.
-
Right-click and select Format Cells.
-
Under the Number tab, select Number or Accounting.
You might use the Accounting format if you want to use parenthesis around negative values.
-
Make any formatting adjustments to the left of the Category field, then select OK.
For example, if using Accounting, and you want to set the Symbol to $, use the drop-down menu to select $. You can also select how many Decimal places your values have.
-
Select a cell you want to place the IRR value in and enter the following:
=IRR(values,[guess])
In the example, the formula would read:
=IRR(B4:B16)
Here, a cell range is being used instead of actual values. This is especially helpful if working in financial spreadsheets where values can change, but the cell location doesn’t. Also, note that the formula used is using Excel’s default value of 0.1. If you want to use another value, such as 20%, enter the formula as: =IRR(B4:B16,20%).
-
Once you’ve entered and formatted the formula to your needs, press Enter to see the value.
If you want to use a cell value, use the cell reference instead of the number. In this example, a list of Guess values has been entered into the E column. To calculate a 20% Guess, use this formula: =IRR(B4:B16,E5). This makes it easier to figure different values by entering in a reference cell where you can keep changing the number and not the formula.
Thanks for letting us know!
Get the Latest Tech News Delivered Every Day
Subscribe
Загрузить PDF
Загрузить PDF
Внутренняя норма доходности (ВНД или IRR от англ. Internal Rate of Return) часто используется в бизнесе для сравнения различных проектов по доходности и перспективах роста. Этот показатель иногда называют методом дисконтированных денежных потоков, так как, фактически, это — процентная ставка, при которой чистый дисконтированный денежный поток равен нулю. Чем выше ставка IRR, тем большую перспективу роста имеет проект. Возможность просчитать IRR в Excel полезна менеджерам, которые для этих целей могут обойтись без бухгалтерского или экономического отдела.
Шаги
-
1
Запустите Microsoft Excel.
-
2
Создайте новую книгу и сохраните файл с соответствующим названием.
-
3
Выберите проекты, денежные потоки которых необходимо проанализировать. Определите срок.
- Предположим, вам необходимо посчитать IRR для трех проектов на 5 лет.
-
4
Подготовьте таблицу на 4 столбца, озаглавив каждый.
- Первый столбец отведите под описание.
- Каждому проекту отведите отдельный столбец, вы затем легко их сравните и проанализируете. Озаглавьте столбцы соответственно: «Данные проекта 1», «Данные проекта 2» и т.д.
-
5
В первой колонке введите описание финансовых потоков отдельно в каждой строке: Первоначальные инвестиции, Чистый доход 1, Чистый доход 2, Чистый доход 3, Чистый доход 4, Чистый доход 5 и IRR (ВНД).
-
6
По каждому проекту введите данные, включая первоначальные инвестиции и прогнозируемый чистый доход отдельно по каждому году.
-
7
В ячейке напротив IRR (ВНД) в столбце «Данные проекта 1» считаем функцию IRR (если интерфейс Excel русский, тогда функция ВСД). Для этого рядом со строкой формулы нажимаем кнопку “fx” и в сплывающем окне «Мастер функций» ищем необходимую функцию (ВСД или IRR) в категории «Финансовые».
- В окне «Аргументы функций» активируйте поле «Значение», затем выберите диапазон цифровых значений первого проекта. В нашем случае B2:B7.
- Поле «Предположение» оставьте пустым. Нажмите «OK».
-
8
Убедитесь, что число выводится в формате процентов.
- Если это не так, в выпадающем списке ячейки (нажав на правую кнопку мыши) выберите «Формат ячеек», а затем – «Процентный».
- Поставьте для округления 2 десятичных знака.
-
9
Скопируйте ячейку с формулой в ячейки C8 и D8.
-
10
Найдите проект, у которого значение IRR (ВНД) максимальное. Это проект с максимальной перспективой роста и рентабельностью капиталовложений.
Реклама
Советы
- Помните, первоначальные инвестиции вводятся с отрицательным знаком, так как это отток капитала; а чистые доходы — с положительным. Со знаком «-» чистые доходы ставятся в том случае, если в указанном периоде расходы превышают поступления.
- Если при расчете IRR (ВНД) возникла ошибка #ЧИСЛО!, введите число в поле «Предположение».
- Функция ВСД (IRR) работает только в том случае, если у вас как минимум одно негативное значение и одно позитивное значение в проекте.
Реклама
Что вам понадобится
- Данные проекта
- Компьютер
- Программа Microsoft Excel
Об этой статье
Эту страницу просматривали 59 499 раз.
Была ли эта статья полезной?
Это формулы, которые позволят рассчитать:
— NPV (Net Present Value) — чистую приведенную стоимость.
— IRR (Internal Rate of Return) — внутреннюю ставку доходности.
— Аннуитеты – равномерные платежи.
Также рассмотрим некоторые нюансы использования этих формул. Все расчеты можно найти в приложенном файле. Основной акцент сделан на функции Excel, поэтому саму финансовую математику разбирать особо не будем.
Скачать пример
Оценка целесообразности проекта с помощью NPV
Есть проект, который ежегодно в течении 5 лет будет приносить 250 000 руб. Нужно потратить 1 000 000 руб. Предположим, что ставка дисконтирования равна 10%.
Оцениваем NPV проекта. Напомню формулу этого показателя:
Если денежные потоки, приведенные к текущему периоду, больше инвестированных денег (NPV > 0), то проект выгодный. В противном случае – нет. Другими словами, нам потребуется сделать в Excel следующее:
Добавить порядковые номера лет: 0 – стартовый год, к нему приводятся потоки. 1, 2, 3 и т.д. – это годы реализации проекта. В формуле на рисунке выполнены действия, которые прописаны выше после знака суммы (Σ): денежный поток за период делится на сумму 1 и ставки дисконтирования, возведенную в степень соответствующего года.
Рассчитанная строка представляет собой дисконтированный денежный поток. Чтобы получить значение NPV, достаточно найти общую сумму всей строки.
Получается «-52 303». Проект невыгоден.
Чтобы определить NPV, на самом деле необязательно готовить такую таблицу. Достаточно воспользоваться формулой Excel ЧПС. Синтаксис формулы такой (здесь и далее будет написано не как в справке Excel, а в переводе на понятный язык):
ЧПС(Ставка дисконтирования; Диапазон дисконтируемых значений)
То есть достаточно указать ячейку с процентом и с денежными потоками. Но при использовании этой формулы с непривычки финансисты часто допускают ошибку:
Вообще-то дисконтированный поток и расчет по ЧПС должны совпадать. Почему же здесь разные значения? Дело в том, что ЧПС начинает дисконтировать с первого же значения. Т.е. она на самом деле ищет приведенную стоимость. А стартовые инвестиции нужно отнимать после. Правильная запись формулы в нашем случае будет иметь следующий вид:
Стартовые инвестиции «выведены» за пределы дисконтируемого диапазона и вычтены: т.к. стартовые инвестиции уже идут с минусом, то D8 нужно прибавлять. Теперь результаты одинаковые.
Оценка целесообразности проекта с помощью IRR
Как еще можно оценить проект? Можно посмотреть на него с точки зрения ставки дисконтирования. Задать вопрос: а какая должна быть ставка, чтобы NPV стала = 0? Вот этой ставкой как раз и является IRR. Если Ставка дисконтирования < IRR, то проект стоит принять, если нет – отказаться. Рассчитать IRR с помощью Excel очень просто: подставляем в функцию ВСД итоговый денежный поток.
IRR оказался меньше ставки доходности. Проект невыгодный (тот же вывод, что и при NPV).
NPV и IRR по праву считаются главными экономическими критериями. Их используют и для инвестиционной оценки проектов, и для оценки стоимости существующего бизнеса. В том числе, показатель EVA (Economic Value Added) считается хорошим критерием в том числе потому, что при правильном расчете он равен NPV.
Но кроме всего прочего, NPV и IRR могут быть использованы финансистами в более прикладных вопросах, например, при общении с банками на тему реальной кредитной ставки. Как – давайте посмотрим.
Аннуитеты – любимая банковская цифра
Сначала поговорим о волнующем вопросе – как банки рассчитывают сумму равномерного платежа, как их проверить и как это понимать. Допустим, вы собираетесь взять кредит 1 000 000 руб. на 5 лет под 10% годовых. Платить будете раз в год равными платежами. Формулу из учебника по финансовому менеджменту здесь приводить не будем. Приведем формулу Excel:
ПЛТ(Ставка дисконтир; Количество периодов; Сумма кредита которую вы берете)
В формуле есть еще два необязательных пункта: сумма, которая должна остаться (по умолчанию ноль), и как высчитывать сумму – на начало месяца, и тогда ставят 1, или на конец – ставят ноль. В 90% случаев эти пункты не нужны, поэтому их можно не ставить вообще. Итого аннуитет определяется так:
Сумма ежегодного платежа получается сразу с минусом. Эту сумму нужно каждый год платить банку.
В ней содержатся две части: 1) платеж по кредиту, 2) тело кредита.
Ниже они показаны. Платеж по кредиту берется как 10% (процент по кредиту) от суммы задолженности на начало периода. Тело – как разность между ежегодным платежом и платежом по процентам (в Excel можно найти формулы, которые рассчитают вам и эти платежи). Задолженность на конец рассчитывается как разность между Задолженностью на начало и платежом по телу кредита.
Если платежи не ежегодные, а ежемесячные или ежеквартальные, то нужно ставку и период приводить к этим значениям. Так если бы у нас платеж был каждый месяц, формула выглядела бы так:
Мы бы годовую ставку разделили на 12 (привели к ежемесячному), и взяли не 5 периодов, а 5 • 12 = 60 месяцев. И получили ежемесячный платеж в 21 247 руб.
Нюансы и тонкости
А теперь обсудим, как проверять банки на честность. Любой поток платежей по кредиту подразумевает под собой, что все выбытия денег приведены к поступлениям на ставку кредитования. Теперь по-русски: если мы построим денежный поток из полученного нами кредита и последующих наших аннуитетных платежей, то затем мы можем посчитать по ним NPV и IRR. NPV при этом должно принять нулевое значение, а IRR, что интереснее, — показать нам реальную процентную ставку.
Когда кредит и платежи по нему рассчитаны правильно, то NPV, взятый по той же процентной ставке, равен нулю. А IRR показывает ставку. Когда банк делает предложение, от которого невозможно отказаться и которое увеличит кредитную ставку «всего» на несколько процентов – не верьте и пересчитывайте! Например, в нашем случае банк предложил страховку «всего» 2 % от суммы кредита в год. Думаете это прирост всего в 2%? Нет! Дело в том, что настоящий кредит в начале каждого года уменьшается:
В результате видно, что NPV не равен нулю. А реальный процент не 10, а 12,9%! Обратите внимание: здесь же выросла сумма переплаты. Если вас это смутит, вам могут предложить «еще более выгодные условия» — заплатить переплату сейчас, а остальное потом, меньшими платежами, или в нашем примере просто заплатить больше, а потом меньше. Сумма переплаты не изменится, а вот процент…
Что здесь сделано? Из каждого последующего платежа взята сумма 43 797 руб. и добавлена к первому же платежу (а бывает выкручивают сумму в момент выдачи кредита). Если для реального сектора финансовая математика «деньги вчера – деньги завтра» кажется несколько отдаленной от жизни, для банков это реальная прибыль. Поэтому всеми силами нагружают первый платеж. А вы с помощью простых формул сможете подготовить основу для дальнейших переговоров.
Да, не забудьте, если речь идет про ежемесячные платежи, умножать на 12.
На чтение 8 мин Просмотров 138к.
Разберем такой показатель как внутренняя норма доходности инвестиционного проекта, определим экономический смысл и рассмотрим подробно пример его расчета с помощью Excel.
Содержание
- Внутренняя норма доходности инвестиционного проекта (IRR, Internal Rate of Return). Определение
- Внутренняя норма доходности формула расчета
- Применение внутренней нормы доходности
- Мастер-класс: «Как рассчитать внутреннюю норму доходности бизнес плана»
- Расчет внутренней нормы доходности (IRR) на примере в Excel
- Пример расчета IRR в Excel c помощью встроенной функции
- Пример расчета IRR через надстройку «Поиск решений»
- Расчет внутренней нормы доходности в Excel для несистематических поступлений
- Модифицированная внутренняя норма доходности (MIRR)
- Расчет модифицированной внутренней нормы доходности в Excel
- Преимущества и недостатки внутренней нормы доходности (IRR)
Внутренняя норма доходности инвестиционного проекта (IRR, Internal Rate of Return). Определение
Внутренняя норма доходности (англ. Internal Rate of Return, IRR, внутренняя норма прибыли, внутренняя норма, внутренняя норма рентабельности, внутренняя норма дисконта, внутренний коэффициент эффективности, внутренний коэффициент окупаемости) – коэффициент, показывающий максимально допустимый риск по инвестиционному проекту или минимальный приемлемый уровень доходности. Внутренняя норма доходности равна ставке дисконтирования, при которой чистый дисконтированный доход отсутствует, то есть равен нулю.
★ Инвестиционная оценка в Excel. Расчет NPV, IRR, DPP, PI за 5 минут
Внутренняя норма доходности формула расчета
где:
CFt (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, разберем два способа построения с помощью функции и с помощью надстройки «Поиск решений».
Пример расчета IRR в Excel c помощью встроенной функции
В программе есть встроенная финансовая функция, позволяющая быстро произвести расчет данного показателя – ВСД (внутренняя ставка дисконта). Следует заметить, что данная формула будет работать только тогда, когда есть хотя бы один положительный и один отрицательный денежный поток. Формула расчета в Excel будет иметь следующий вид:
Внутренняя норма доходности (E16) =ВСД(E6:E15)
В результате мы получили, что внутренняя норма доходности равняется 6%, далее для проведения инвестиционного анализа, полученное значение необходимо сопоставить со стоимостью капитала (WACC) данного проекта.
Пример расчета IRR через надстройку «Поиск решений»
Второй вариант расчета подразумевает использование надстройки «Поиск решений» для поиска оптимального значения ставки дисконтирования для NPV=0. Для этого необходимо рассчитать чистый дисконтированный доход (NPV).
На рисунке ниже показаны формулы расчета дисконтированного денежного потока по годам, сумма которых дает чистый дисконтированный доход. Формула расчета дисконтированного денежного потока (DCF) следующая:
Дисконтированный денежный поток (F) =E7/(1+$F$17)^A7
Чистый дисконтированный доход (NPV) =СУММ(F7:F15)-B6
На рисунке ниже показан первоначальный вид для расчета IRR. Можно заметить, что ставка дисконтирования, используемая для расчета NPV, ссылается на ячейку, в которой нет данных (она принимается равной 0).
Сейчас наша задача состоит в том, чтобы отыскать на основе оптимизации с помощью надстройки «Поиск решений», то значение ставки дисконтирования (IRR) при котором NPV проекта будет равен нулю. Для этого открываем в главном меню раздел «Данные» и в нем «Поиск решений».
При нажатии в появившемся окне заполняем строки «Установить целевую ячейку» – это формула расчета NPV, далее выбираем значение данной ячейки равной 0. Изменяемый параметр будет ячейка со значением внутренней нормы доходности (IRR). На рисунке ниже показан пример расчета с помощью надстройки «Поиск решений».
После оптимизации программа заполнит нашу пустую ячейку (F17) значением ставки дисконтирования, при которой чистый дисконтированный доход равен нулю. В нашем случае получилось 6%, результат полностью совпадает с расчетом по строенной формуле в Excel.
Расчет внутренней нормы доходности в Excel для несистематических поступлений
На практике часто случается, что денежные средства поступают не периодично. В результате ставка дисконтирования для каждого денежного потока будет меняться, это делает невозможным использовать формулу ВСД в Excel. Для решения данной задачи используется другая финансовая формула ЧИСТВНДОХ (). Данная формула включает в себя массив дат и денежные потоки. Формула расчета будет иметь следующий вид:
=ЧИСТВНДОХ(E6:E15;A6:A15;0)
Модифицированная внутренняя норма доходности (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, которая учитывает также доходность от реинвестирования капитала.
Автор: к.э.н. Жданов Иван Юрьевич
IRR, или внутренняя норма доходности, используется для расчета внутренней прибыли для некоторых инвестиций в финансы. IRR — это встроенная функция в Excel, используемая для расчета того же самого. Это также финансовая формула. Эта функция принимает диапазон значений в качестве входных данных, для которых нам нужно рассчитать внутреннюю норму доходности и предполагаемое значение в качестве второго входного значения.
Например, предположим, что ваша компания сделала первоначальные инвестиции в размере 50 000 долларов и рассчитывает получать 10 000 долларов в год в течение следующих 10 лет. Мы можем использовать эти данные для расчета IRR этой инвестиции, которая представляет собой норму прибыли, которую вы можете получить на свои инвестиции в размере 100 долларов США. В результате IRR получается равным 15%. Это эквивалентно деньгам, вложенным под 15% на 10 лет.
IRRIRRВнутренняя норма доходности (IRR) — это ставка дисконтирования, которая устанавливает нулевую чистую текущую стоимость всех будущих денежных потоков от проекта. Он сравнивает и выбирает лучший проект, при этом выбирается проект с IRR, превышающим минимально допустимую доходность (барьерную ставку). Читать далее в Excel означает явнутренний рсъел из рвозвращение. Это одна из встроенных функций, доступных в Microsoft Excel. Кроме того, он подпадает под категорию финансовых функций.
Внутренняя норма доходности – это процентная ставка, полученная за сделанные инвестиции. Он состоит из произведенных платежей, представленных отрицательными значениями, и полученного дохода, обозначенного положительными значениями, которые происходят регулярно.
Функция IRR в Excel возвращает внутреннюю норму доходности для ряда денежных потоков. Денежные потоки Денежный поток — это сумма денежных средств или эквивалента денежных средств, созданная и потребленная компанией за определенный период. Это оказывается предпосылкой для анализа силы бизнеса, прибыльности и возможностей для улучшения. читать далее представлены положительные и отрицательные числа. Эти денежные потоки не обязательно должны быть постоянными, как в случае с аннуитетом. Однако денежные потоки должен происходят через регулярные промежутки времени, например, ежемесячно, ежеквартально или ежегодно. Сумма наличных может варьироваться для каждого интервала.
Оглавление
- Расчет IRR в Excel
- Формула, используемая для расчета IRR в Excel
- Как рассчитать IRR в Excel? (с примерами)
- Пример № 1. Расчет внутренней нормы доходности в таблице Excel
- Пример #2
- Пример №3
- Пример №4
- Пример № 5. Расчет IRR в Excel VBA
- IRR в примере Excel VBA
- То, что нужно запомнить
- IRR в видео Excel
- Рекомендуемые статьи
Формула, используемая для расчета IRR в Excel
Формула IRR в Excel выглядит следующим образом:
Где,
- диапазон = Это обязательный параметр. Диапазон ячеек обозначает ряд денежных потоков, для которых мы рассчитываем внутреннюю норму доходности.
- предполагать = Это необязательный параметр. Он обозначает число, которое, как вы предполагаете, близко к результату внутренней нормы доходности. Если значение не указано, принимается значение по умолчанию 0,1 или 10%.
- “[ ]” обозначает необязательные параметры.
Функция возвращает числовое значение. Он может быть положительным или отрицательным.
Как рассчитать IRR в Excel? (с примерами)
.free_excel_div{фон:#d9d9d9;размер шрифта:16px;радиус границы:7px;позиция:относительная;margin:30px;padding:25px 25px 25px 45px}.free_excel_div:before{content:»»;фон:url(центр центр без повтора #207245;ширина:70px;высота:70px;позиция:абсолютная;верх:50%;margin-top:-35px;слева:-35px;граница:5px сплошная #fff;граница-радиус:50%}
Вы можете скачать этот шаблон Excel функции IRR здесь — Шаблон Excel функции IRR
Мы можем выполнять расчеты IRR в Excel с помощью функции рабочего листа (WS) или функции VBA. Его можно ввести как часть формулы в ячейку рабочего листа как функцию WS. С другой стороны, его можно использовать в макрокоде как функцию VBA. Функция VBA Функции VBA служат основной цели для выполнения определенных вычислений и возврата значения. Поэтому в VBA мы используем синтаксис для указания параметров и типа данных при определении функции. Такие функции называются пользовательскими функциями. Подробнее, вводимыми через редактор Microsoft Visual Basic Editor, интегрированный в MS Excel. Обратитесь к расчету IRR в примере Excel ниже, чтобы лучше понять. Пример № 1. Расчет внутренней нормы доходности в таблице Excel
В этом примере расчета IRR -3000 обозначает сумму инвестированного капитала. Следующие числа, 2000 и 5000, указывают на доход. Функция IRR в Excel рассчитывается для значений -3000, 2000 и 5000. Результат 67%.
Пример #2
В этом примере расчета IRR #ЧИСЛО! Он отображается как результат, поскольку в наборе значений нет ни одного отрицательного значения. Итак, нет значения, указывающего на оплату, но все являются доходом.
Пример №3
В этом примере расчета IRR все значения отрицательные. Таким образом, это указывает на отсутствие дохода. Следовательно, сгенерированный результат равен #ЧИСЛО!
Пример №4
В этом примере расчета IRR общее количество значений равно пяти. -10 000 обозначает выполненный платеж. Следующие значения приведены за год, т. е. доход, полученный в 1-м году, равен 2000, во 2-м году — 5000, в 3-м году — 4000 и в 5-м году — 6000.
Чтобы рассчитать доход, полученный в конце трех лет, примените формулу IRR к ячейкам в диапазоне от B2:B4. Результат составляет -19%, так как сумма платежа в начальный год больше, чем доход предыдущего года.
Чтобы рассчитать доход, полученный в конце пяти лет, примените формулу IRR к ячейкам в диапазоне от B2: B6. Результат 22%. Результат положительный, поскольку доход, полученный в последующие годы, превышает сумму платежа, вложенную в первый год.
Пример № 5. Расчет IRR в Excel VBA
Функцию IRR можно использовать в коде VBA Код VBA Код VBA относится к набору инструкций, написанных пользователем на языке программирования приложений Visual Basic в редакторе Visual Basic (VBE) для выполнения определенной задачи. Подробнее в Microsoft Excel следующим образом:
Формула IRR в Excel VBA:
Формула Irr в VBA выглядит следующим образом.
Где,
Ценности = Денежный поток
IRRval = Внутренняя норма доходности
IRR в примере Excel VBA
Давайте рассмотрим пример для Irr в VBA.
В этом примере вычисления IRR IRRal является переменной типа double. Он содержит результат, возвращаемый функцией IRR. Значения представлены в массиве типа данных double, где начальное значение равно -10000, что указывает на платеж, сделанный в первый год. Например, следующие значения — 2 000, 5 000, 4 000 и 6 000 — показывают годовой доход, полученный в течение следующих четырех лет.
То, что нужно запомнить
- Массив значений (диапазон) должен содержать как минимум одно положительное и одно отрицательное число.
- Значения должны быть в последовательном порядке. IRR использует порядок значений для интерпретации порядка денежных потоков. Пожалуйста, введите значения платежа и дохода в той последовательности, в которой они происходят, или если вы хотите, чтобы это произошло.
- Эти значения игнорируются, если аргумент массива или ссылки содержит текст, логические значения или пустые ячейки.
- MS Excel использует итеративный метод для расчета IRR с помощью Excel. Начиная с предположения, IRR циклически повторяет расчет, пока результат не будет точным в пределах 0,00001 процента; если IRR не может найти результат, который работает после 20 попыток, #ЧИСЛО! Возвращается значение ошибки.
- Если функция IRR в Excel возвращает значение #ЧИСЛО! или результат не такой, как вы ожидали, попробуйте с другим значением.
- В большинстве случаев вам не нужно указывать предполагаемое значение для расчета IRR в Excel. Однако, если параметр угадывания пропущен, программное обеспечение принимает значение 0,1, т. е. 10%.
- IRR тесно связана с NPV, функцией чистой приведенной стоимости. Норма прибыли, рассчитанная с помощью IRR с помощью Excel, представляет собой процентную ставку, соответствующую нулевой чистой текущей стоимости. оттоки за период реализации проекта. Если разница положительна, проект прибыльный; в противном случае это не так.Подробнее.
IRR в видео Excel
Рекомендуемые статьи
Эта статья представляет собой руководство по расчету IRR в Excel. Мы обсуждаем формулу и функцию IRR в Excel, расчеты, примеры и загружаемые шаблоны. Вы также можете посмотреть на эти другие функции поиска и ссылок в Excel:
- Примеры внутренней нормы доходности
- IRR против ROI
- Математическая функция Excel
- Пользовательские функции в Excel