Ошибка при расчете irr

Одна из проблем с использованием критерия IRR — это «проблема множественной IRR» (англ ‘multiple IRR problem’).

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

*Этот пример адаптирован из Hirschleifer.

IRR для этих денежных потоков удовлетворяет следующему уравнению:

\( \dst -1,000 + {5,000 \over (1+\IRR)^1}+ {-6,000 \over (1+\IRR)^2} = 0 \)

Оказывается, что есть два значения IRR, которые удовлетворяют уравнению:

IRR = 1 = 100% и IRR = 2 = 200%.

Чтобы еще лучше понять эту проблему, рассмотрите профиль NPV для этой инвестиции, который показан в иллюстрациях 14 и 15.

Как вы можете видеть в профиле NPV, NPV равна нулю при IRR = 100% и IRR = 200%. NPV отрицательна для ставок дисконтирования ниже 100%, затем положительна для ставок от 100% до 200%, а затем опять отрицательна для ставок выше 200%. NPV достигает наивысшего значения, когда ставка дисконтирования составляет 140%.

Также бывают инвестиционные проекты без IRR.

«Проблема отсутствия IRR» (англ. ‘no-IRR problem’) возникает при такой модели денежных потоков:

IRR для этих денежных потоков удовлетворяет следующему уравнению:

\( \dst 100 + {-300 \over (1+\IRR)^1}+ {250 \over (1+\IRR)^2} = 0 \)

Для этих денежных потоков не существует ставки дисконтирования, которая приводит к нулевой NPV.

Означает ли это, что проект не подходит для инвестиции?

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

Самый низкий NPV, со значением 10, соответствует ставке дисконтирования 66.67%, и NPV всегда выше нуля. Следовательно, IRR не существует.

Иллюстрация 14. Профиль NPV при множественной IRR.

Иллюстрация 15. График профиля NPV при множественной IRR.

График профиля NPV при множественной IRR.

Иллюстрация 16. Профиль NPV для инвестиционного проекта без IRR.

Иллюстрация 17. График профиля NPV для инвестиционного проекта без IRR.

График профиля NPV для инвестиционного проекта без IRR.

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

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

См. также: об отличиях моделей денежных потоков.

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

Например, проект с двумя изменениями знака может иметь 0, 1 или 2 IRR. Наличие двух изменений знака не означает, что у вас будет несколько IRR; это просто означает, что такое возможно.

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

@mah2023  wrote:  «why does my Excel not show the negative number and gives an error?«

@mah2023wrote:  «Is there a problem with my Excel settings?«

It seems that you still have not learned from past mistakes, namely:  «with great details come great answers», to paraphrase Uncle Ben’s advice to Spider-Man. (wink)

You say that Excel does «not show the negative number», and it still «gives an error».

Again, attach an Excel that demonstrates those problems.

What is your formula(s)?  What is the data, if not the same as before?

Exactly what does Excel display?  Show us a screenshot, or attach a JPG file with a screenshot.

Using your previous Excel file attachment, when I enter the formula =IRR(O4:O38/1000000) into O40 and =IRR(O4:O38/1000000, -10%) into O41, as I suggested, this is what I see:

I changed the format for O4:O38 to display 2 decimal places and to separate 1000s with commas.

(Aside…. Many of the values in O4:O38 have more than 2 decimal places, up to 5 decimal places.  And all of the values have what I call «floating-point residuals»:  additional precision beyond 15 significant digits, which Excel does not format.)

The point is:  I do not see any Excel error.

Instead, O40 displays red 0.00, and O41 displays red (0), even though the cell values are negative.

That is because of the cell formats:  0.00;[Red]0.00 for O40, and 0_);[Red](0) for O41.

Change the formats to Percentage with 2 decimal places to see the negative cell values (rounded), namely -0.49% in O40 and -5.17% in O41.

Increase the number of decimal places (up to 15) to see the greater precision of the cell values.

——

If that does not answer your question, follow all of the instructions above to improve our understanding of the problem(s).

If you need instruction on how to set the cell format, I’m afraid that varies for different versions of Excel.

So, let us know what version of Excel you are using, and on what device (desktop, laptop, tablet, smartphone, etc).

For my version of Excel, I can select the cell(s), right-click, click Format Cells and the Number tab, then select the desired format.

Alternatively, I can select a limited set of format options from the Home «ribbon».

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

Особенности и синтаксис функции ВСД

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

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

Внутренняя ставка доходности (IRR, внутренняя норма доходности) – процентная ставка инвестиционного проекта, при которой приведенная стоимость денежных потоков равняется нулю. При данной ставке инвестор вернет вложенные первоначально средства. Инвестиции состоят из платежей (суммы со знаком «–») и доходов (со знаком «+»), которые происходят в одинаковые по продолжительности временные промежутки.

Аргументы функции ВСД в Excel:

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

Секреты работы функции ВСД (IRR):

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

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

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



Примеры функции ВСД в Excel

Расчет внутренней нормы рентабельности рассмотрим на элементарном примере. Имеются следующие входные данные:

Сумма первоначальной инвестиции – 7000. В течение анализируемого периода было еще две инвестиции – 5040 и 10.

Заходим на вкладку «Формулы». В категории «Финансовые» находим функцию ВСД. Заполняем аргументы.

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

Искомая IRR (внутренняя норма доходности) анализируемого проекта – значение 0,209040417. Если перевести десятичное выражение величины в проценты, то получим ставку 20,90%.

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

Еще один показатель эффективности инвестиционного проекта – NPV (чистый дисконтированный доход). NPV и IRR связаны: IRR определяет ставку дисконтирования, при которой NPV = 0 (то есть затраты на проект равны доходам).

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

На основании полученных данных построим график изменения NPV.

Пересечение графика с осью Х (когда чистый дисконтированный доход проекта равняется нулю) дает показатель IRR для данного проекта. Графический метод показал результат ВСД, аналогичный найденному в Excel.

Как пользоваться показателем ВСД:

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

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

Скачать пример функций ВСД IRR и ЧПС NPV в Excel.

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