IRR для проектов с терминальной стоимостью
Тренды рынка клинических исследований
12 сентября, 2021
IRR (внутрення норма доходности) – это ключевой показатель доходности инвестиционного проекта. Обычно, чтобы посчитать IRR для любого денежного потока, достаточно воспользоваться одной из встроенных в Excel функций:
- ВСД (или IRR для англоязычной версии) для денежных потоков, поступающих в конце периода
- ЧИСТВНДОХ (XIRR) – для нерегулярных денежных потоков
Однако, если нужно посчитать IRR для денежного потока с терминальной стоимостью (Terminal Value, TV), рассчитанной по модели Гордона, то есть путем деления пост-прогнозного денежного потока на разницу между ставкой дисконтирования и темпом роста в пост-прогнозном периоде, встроенные функции Excel будут считать этот показатель некорректно
Модель Гордона для расчета терминальной стоимости предполагает, что денежные потоки от реализации проекта будут поступать бесконечно долго, поэтому корректный расчет IRR с терминальной стоимостью необходим в первую очередь стратегическим долгосрочным инвесторам. Также IRR, учитывающий терминальную стоимость, просят считать некоторые Российские банки, например, ВЭБ
Почему встроенные функции Excel считают IRR неправильно?
Чтобы разобраться с этим, нужно понять, как именно Excel считает IRR. Математически, чтобы найти IRR денежного потока, нужно найти корни алгебраического уровня, степень которого определяется числом периодов денежного потока. Как известно из алгебры, решить уравнения в радикалах, т.е. через какую-то общую формулу, включающую арифметические операции и извлечение корней, в общем случае невозможно уже для уравнений степени n > 4. Поэтому на практике, чтобы отыскать IRR нужно использовать численные методы – алгоритмы, которые пытаются найти корень уровнения, перербирая разные варианты
Именно таким образом и устроены функции IRR в Excel. Они перебирают различные значения ставки дисконтирования, пока не найдут то, которое приравняет сумму дисконтированных денежных потоков (NPV) к нулю. Но в методе Гордона ставка дисконтирования используется еще и для нахождения терминального денежного потока. Функция IRR в Excel не знает о том, что на каждой итерации алгоритма терминальный денежный поток нужно сначала рассчитать с помощью возможного кандидата на роль IRR, а только потом дисконтировать
Способы расчета IRR для денежных потоков с терминальной стоимостью
Для иллюстрации разных методов расчета IRR мы воспользуемся денежным потоком гипотетического инвестиционного проекта. Первоначальные инвестиции составят 900 млн. руб в 1 год его реализации. Во второй год реализации проект начнет генерировать положительный FCFF, растущий на 5% в год. Скачать демонстрационный файл в Excel можно здесь. Обратите внимание, что некоторые из изложенных методов требуют разрешения Excel на запуск макросов
Goal Seek («Подбор параметра»)
Для данного метода нужно сначала подготовить расчет NPV денежного потока с терминальной стоимостью, причем сделать это «вручную», а не с помощью функции ЧПС, затем, нужно воспользоваться инструментом Goal Seek для решения задач с помощью численных методов. В Goal Seek нужно задать ставку дисконтирования в качестве изменяемой ячейки, а в качестве целевой функции - NPV, и установить целевое значение «0». Если расчет терминальной стоимостью связан со ставкой дисконтирования, которую мы меняем, то полученный результат и будет искомым IRR
Если сравнить IRR, который мы посчитали с помощью Goal Seek для нашего гипотетического денежного потока, с тем, что посчитает встроенная функция, то мы увидим весьма существенную разницу: 38% (встроенная функция) против 25% (Goal Seek)
Честно говоря, плюсов у этого метода нет никаких. Наверняка есть более приятный способ провести время, чем подбирать ставку дисконтирования при любом изменении в финансовой модели. Естественно, провести анализ чувствительности IRR или оптимизировать какие-то параметры для достижения целевого значения IRR можно только утомительной ручной вставкой и копированием
Чтобы упростить себе жизнь, можно автоматизировать работу Goal Seek или Solver с помощью VBA. За исключением объявлений переменных сам код занимает одну строчку и им легко можно дополнить анализ чувствительности, если последний работает с помощью макросов, а не с помощью «Таблиц данных»
Sub GS_automate()
Dim DR As Range, NPV_t As Range
Set DR = Range("DR")
Set NPV_t = Range("NPV_t")
NPV_t.GoalSeek Goal:=0, ChangingCell:=DR
End Sub
Пользовательские функции (UDF)
Это наиболее гибкий и элегантный метод корректно считать IRR для потоков с TV – мы просто напишем свою функцию, которая исправит недостатки встроенных в Excel
Прежде чем писать функцию непосредственно для расчета IRR, мы сначала напишем отдельную функцию для расчета NPV. По сравнению во встроенной ЧПС, наша функция дополнительно посчитает и продисконтирует терминальную стоимость, исходя из заданного темпа роста денежного потока в пост-прогнозный период. Кроме того, в отличие от встроенной функции, она не будет дисконтировать первый период, чтобы соответствовать стандартам, принятым в корпоративных финансах («нулевой» период не дисконтируется)
Function NPVTV(DR As Double, CF As Range, GR As Double) As Double
Dim lenCF As Integer
lenCF = CF.Count
For i = 1 To lenCF
NPVTV = NPVTV + CF(i) / (1 + DR) ^ (i - 1)
Next
NPVTV = NPVTV + CF(lenCF) * (1 + GR) / (DR - GR) / (1 + DR) ^ lenCF
End Function
На входе наша модифицированная функция NPV принимает ставку дисконтирования, денежный поток и темп роста, затем в цикле дисконтирует и суммирует все денежные потоки, а в конце рассчитывает терминальную стоимость, дисконтирует ее и добавляет к ранее полученному результату
Теперь вернемся к самой функции IRR. С помощью нее мы будем искать ставку дисконтирования, которая приведет NPV проекта к нулю. Для этого можно воспользоваться любым численным алгоритмом нахождения корней уравнения. Мы используем методом хорд, так это один из наиболее простых методов, который не требует считать производные в отличие от, например, алгоритма Ньютона
В качестве опциональных параметров мы добавили eps (погрешность между полученным NPV и нулем) и число итераций n_iter, чтобы наша функция не застряла в цикле, например в случае отсутствия вещественных решений. Для того, чтобы функция начала работать, нужно задать два исходных значения для IRR в период n и n – 1. Нужно, чтобы эти значения были выше, чем темп роста пост-прогнозного периода, иначе алгоритм может прийти к точке, в которой темп роста равен ставке дисконтирования и при расчете TV денежный поток будет делится на ноль. Дальше функция просто проводит заданное число итераций по методу хорд, пока количество итераций не превысит максимального значения или не будет найдет корень с заданной погрешностью
Function IRRTV(CF As Range, GR As Double, Optional eps = 0.001, _
Optional n_iter = 100) As Double
Dim IRR_n As Double, IRR_n1 As Double, IRR_nless1 As Double
Dim NPV_n As Double, NPV_nless1 As Double, TV As Double
IRR_nless1 = GR + eps * 2
IRR_n = IRR_nless1 * 2
NPV_n_less1 = NPVTV(IRR_nless1, CF, GR)
NPV_n = NPVTV(IRR_n, CF, GR)
For i = 1 To n_iter
If Abs(NPV_n) < eps Then Exit For
IRRTV = IRR_n - NPV_n * ((IRR_n - IRR_nless1) / (NPV_n - NPV_nless1))
IRR_nless1 = IRR_n
IRR_n = IRRTV
NPV_nless1 = NPV_n
NPV_n = NPVTV(IRRTV, CF, GR)
Next
End Function
Итерации на листе Excel
Мы придумали этот метод, чтобы полностью избавиться от макросов при расчете IRR и для ряда проектов проектов Alpha Venture, где нужно было считать IRR для денежных потоков с терминальной стоимостью. Сам расчет формируется из блоков, каждый из которых соответствует очередной итерации. В каждом из них мы считаем фактор дисконтирования и полученный NPV, а в последнем столбце блока – терминальную стоимость. В качестве ставки дисконтирования используется новое значение кандидата на IRR, который подбирается на основе ранее описанного метода хорд. Как и в случае с UDF, в первых двух блоках нужно выбрать произвольные значения исходных IRR, чтобы алгоритм мог двигаться дальше. Здесь также можно задать погрешность, хотя это и не имеет большого смысла, так как excel все равно посчитает количество итераций соответствующих числу блоков
Из минусов этого метода можно отметить, что для крупных проектов с большим числом прогнозных периодов он может достаточно сильно тормозить финансовую модель и в этом случае лучше воспользоваться UDF
