Функция Excel XIRR для расчета IRR для непериодических денежных потоков
В этом учебном пособии показано, как использовать ЧСДНР в Excel для расчета внутренней нормы прибыли (ВНР) для денежных потоков с нерегулярным распределением по времени, а также как создать собственный калькулятор ЧСДНР.
Когда вы сталкиваетесь с капиталоемким решением, желателен расчет внутренней нормы прибыли, поскольку он позволяет сравнить прогнозируемую доходность для различных инвестиций и дает количественную основу для принятия решения.
В нашем предыдущем уроке мы рассмотрели, как рассчитать внутреннюю норму доходности с помощью функции Excel IRR. Этот метод быстрый и простой, но у него есть существенное ограничение — функция IRR предполагает, что все денежные потоки происходят через равные промежутки времени, например, ежемесячно или ежегодно. Однако в реальных жизненных ситуациях приток и отток денежных средств часто происходят через нерегулярные промежутки времени. К счастью, в Microsoft Excel есть еще одна функция для определения IRR в таких случаях, и это руководство научит вас ее использовать.
ЧИСТВНДОХ в Excel
Функция Excel ЧИСТВНР возвращает внутреннюю норму доходности для ряда денежных потоков, которые могут быть или не быть периодическими.
Эта функция была представлена в Excel 2007 и доступна во всех более поздних версиях Excel 2010, Excel 2013, Excel 2016, Excel 2019 и Excel для Office 365.
Синтаксис функции ЧИСТНДОХД следующий:
ЧИСТВНДОХ(значения, даты, [guess])
Где:
- Значения (обязательно) — массив или диапазон ячеек, которые представляют ряд притоков и оттоков.
- Даты (обязательно) – даты, соответствующие денежным потокам. Даты могут располагаться в любом порядке, но дата первоначальных инвестиций должна быть первой в массиве.
- Guess (необязательно) — ожидаемая IRR, представленная в процентах или десятичном числе. Если этот параметр опущен, Excel использует коэффициент по умолчанию 0,1 (10%).
Например, чтобы рассчитать IRR для ряда денежных потоков в A2:A5 и дат в B2:B5, вы должны использовать эту формулу:
=ЧИСТРЯД(A2:A5, B2:B5)
Кончик. Чтобы результат отображался корректно, убедитесь, что для ячейки с формулой установлен процентный формат.
6 вещей, которые вы должны знать о функции ЧИСТВНДОХ
Следующие примечания помогут вам лучше понять внутреннюю механику функции ЧССНП и наиболее эффективно использовать ее в своих рабочих листах.
- XIRR в Excel предназначен для расчета внутренней нормы доходности денежных потоков с неодинаковыми временными характеристиками. Для периодических денежных потоков с неизвестными точными датами платежей можно использовать функцию IRR.
- Диапазон значений должен содержать как минимум одно положительное (доход) и одно отрицательное (исходящий платеж) значение.
- Если первое значение представляет собой затраты (первоначальные инвестиции), оно должно быть представлено отрицательным числом. Первоначальные инвестиции не дисконтируются; последующие платежи возвращаются к дате первого денежного потока и дисконтируются на основе 365-дневного года.
- Все даты усекаются до целых чисел, что означает удаление дробной части даты, представляющей время.
- Даты должны быть действительными датами Excel, введенными как ссылки на ячейки, содержащие даты или результаты формул, таких как функция ДАТА. Если даты вводятся в текстовом формате, могут возникнуть проблемы.
- XIRR в Excel всегда возвращает IRR в годовом исчислении, даже при расчете ежемесячных или еженедельных денежных потоков.
ЧИСТВНДОХ вычисление в Excel
Функция ЧВНПД в Excel использует метод проб и ошибок, чтобы найти скорость, которая удовлетворяет этому уравнению:
Где:
- P – денежный поток (оплата)
- д – дата
- я – номер периода
- n – всего периодов
Начиная с предположения, если оно предоставлено, или с 10% по умолчанию, если нет, Excel выполняет итерации, чтобы получить результат с точностью 0,000001%. Если после 100 попыток точная ставка не найдена, то #ЧИСЛО! возвращается ошибка.
Чтобы проверить правильность этого уравнения, давайте сравним его с результатом формулы ЧИСТВНР. Чтобы упростить наш расчет, мы будем использовать следующую формулу массива (помните, что любая формула массива должна быть завершена нажатием Ctrl + Shift + Enter):
=СУММ(A2:A5/((1+$E$1)^((B2:B5-$B$2)/365)))
Где:
- A2:A5 – денежные потоки
- B2:B5 – даты
- E1 — это курс, возвращаемый ЧИСТВНДОХ
Как показано на скриншоте ниже, результат очень близок к нулю. КЭД 🙂
Как посчитать ЧИСТВНР в Excel – примеры формул
Ниже приведены несколько примеров, демонстрирующих распространенное использование функции ЧССНП в Excel.
Базовая формула ЧИСТВНР в Excel
Предположим, вы инвестировали 1000 долларов в 2017 году и рассчитываете получить некоторую прибыль в следующие 6 лет. Чтобы найти внутреннюю норму прибыли для этой инвестиции, используйте эту формулу:
=ЧИСТВНР(A2:A8, B2:B8)
Где A2:A8 — денежные потоки, а B2:B8 — даты, соответствующие денежным потокам:
Чтобы оценить рентабельность этих инвестиций, сравните выпуск ЧВВНР с доходом вашей компании. Средневзвешенная стоимость капитала или же барьерная ставка. Если доходность выше стоимости капитала, проект можно считать хорошей инвестицией.
При сравнении нескольких вариантов инвестирования помните, что прогнозируемая доходность — это лишь один из факторов, который следует оценить перед принятием решения. Для получения дополнительной информации см. Что такое внутренняя норма доходности (IRR)?
Полная форма функции Excel ЧИСТНДОХ
Если вы знаете, какой доход вы ожидаете от той или иной инвестиции, вы можете использовать свое ожидание в качестве предположения. Это особенно полезно, когда явно верная формула ЧИСТНРР выдает ошибку #ЧИСЛО! ошибка.
Для входных данных, показанных ниже, формула ЧИСТВНРД без предположения возвращает ошибку:
=ЧИСТВНР(A2:A7, B2:B7)
Ожидаемая доходность (-20%), указанная в предполагать аргумент помогает Excel получить результат:
=ЧИСТВНР(A2:A7, B2:B7, -20%)
Как рассчитать XIRR для ежемесячных денежных потоков
Для начала, пожалуйста, запомните следующее: какие бы денежные потоки вы ни рассчитывали, функция Excel ЧИСТВНР рассчитывает годовую норму прибыли.
Чтобы убедиться в этом, давайте найдем IRR для той же серии денежных потоков (A2:A8), которые происходят ежемесячно и ежегодно (даты указаны в B2:B8):
=ЧИСТВНР(A2:A8, B2:B8)
Как вы можете видеть на скриншоте ниже, IRR увеличивается с 7,68% в случае годовых денежных потоков до примерно 145% в случае ежемесячных денежных потоков! Разница кажется слишком большой, чтобы ее можно было оправдать одной лишь временной стоимостью денег:
Чтобы найти приблизительный месячный ЧВНП, вы можете использовать приведенный ниже расчет, где E1 — это результат обычной формулы ЧВНП:
=(1+Е1)^(1/12)-1
Или вы можете вставить XIRR прямо в уравнение:
=(1+ЧИСТВНДОХ(A2:A8,B2:B8))^(1/12)-1
В качестве дополнительной проверки воспользуемся функцией IRR для тех же денежных потоков. Пожалуйста, имейте в виду, что IRR также будет рассчитывать приблизительную ставку, потому что предполагается, что все периоды времени равны:
=ВНР(A2:A8)
В результате этих расчетов мы получаем месячный ЧВНП в размере 7,77%, что очень близко к 7,68%, полученному по формуле ВНД:
Вывод: если вы ищете IRR в годовом исчислении для ежемесячных денежных потоков, используйте функцию XIRR в чистом виде; чтобы получить месячную внутреннюю норму доходности, примените корректировку, описанную выше.
Шаблон Excel XIRR
Чтобы быстро получить внутреннюю норму доходности для разных проектов, вы можете создать универсальный калькулятор ЧССНП для Excel. Вот как:
- Введите денежные потоки и даты в два отдельных столбца (A и B в этом примере).
- Создайте два динамически определяемых диапазона с именами Денежный поток а также Даты. Технически это будет называться формулами:
Денежный поток:
=СМЕЩЕНИЕ(Лист1!$A$2,0,0,COUNT(Лист1!$A:$A),1)
Даты:
=СМЕЩЕНИЕ(Лист1!$B$2,0,0,COUNT(Лист1!$B:$B),1)
Где Лист1 — это название вашего рабочего листа, A2 — первый денежный поток, а B2 — первая дата.
Подробные пошаговые инструкции см. в разделе Как создать динамический именованный диапазон в Excel.
- Укажите динамически определенные имена, которые вы создали, в формулу ЧИСТВНРД:
= XIRR(Денежные_потоки, Даты)
Сделанный! Теперь вы можете добавлять или удалять столько денежных потоков, сколько хотите, и ваша динамическая формула XIRR будет соответствующим образом пересчитана:
XIRR против IRR в Excel
Основное различие между функциями ЧССДД и ВСД в Excel заключается в следующем:
- IRR предполагает, что все периоды в серии денежных потоков равны. Вы используете эту функцию, чтобы найти внутреннюю норму прибыли для периодических денежных потоков, таких как ежемесячные, ежеквартальные или годовые.
- XIRR позволяет назначить дату каждому отдельному денежному потоку. Итак, используйте эту функцию для расчета IRR для денежных потоков, которые не обязательно являются периодическими.
Как правило, если вы знаете точные даты платежей, рекомендуется использовать ЧИСТВНДОХД, поскольку это обеспечивает лучшую точность расчета.
В качестве примера сравним результаты IRR и XIRR для одних и тех же денежных потоков:
Если все платежи происходят через равные промежутки времени, функции возвращают очень близкие результаты:
Если сроки денежных потоков неодинаковы, разница между результатами довольно значительна:
XIRR и XNPV в Excel
ЧИСТВНДОХ тесно связан с функцией ЧПСД, поскольку результатом ЧПСД является ставка дисконтирования, которая приводит к нулевой чистой приведенной стоимости. Другими словами, ЧИСТВНР равен XЧПС = 0. В следующем примере демонстрируется взаимосвязь между ЧИСНП и ЧПСД в Excel.
Допустим, вы рассматриваете какую-то инвестиционную возможность и хотите изучить как чистую текущую стоимость, так и внутреннюю норму прибыли на эту инвестицию.
С денежными потоками в A2:A5, датами в B2:B5 и ставкой дисконтирования в E1 следующая формула XNPV даст вам чистую приведенную стоимость будущих денежных потоков:
=XNPV(E1, A2:A5, B2:B5)
Положительный NPV указывает на прибыльность проекта:
Теперь давайте найдем, какая ставка дисконтирования сделает чистую приведенную стоимость равной нулю. Для этого воспользуемся функцией ЧИСТВНДОХ:
=ЧИСТРЯД(A2:A5, B2:B5)
Чтобы проверить, действительно ли ставка, полученная с помощью ЧССНП, приводит к нулевой чистой приведенной стоимости, поместите ее в поле оценивать аргумент вашей формулы XNPV:
=XNPV(E4, A2:A5, B2:B5)
Или вставьте всю функцию ЧИСТВНДОХ:
=XNPV(ЧИСТВНДОХ(A2:A5, B2:B5), A2:A5, B2:B5)
Да, XNPV, округленный до 2 знаков после запятой, равен нулю:
Чтобы отобразить точное значение NPV, выберите отображение большего количества знаков после запятой или примените научный формат к ячейке XNPV. Это даст результат, подобный этому:
Если вы не знакомы с экспоненциальной записью, выполните следующие вычисления, чтобы преобразовать ее в десятичное число:
1,11E-05 = 1,11*10^-5 = 0,0000111
Не работает функция Excel XIRR
Если у вас возникла проблема с функцией XNPV в Excel, ниже приведены основные моменты, которые необходимо проверить.
# ЧИСЛО! ошибка
Ошибка #ЧИСЛО может возникнуть по следующим причинам:
- ценности а также даты диапазоны имеют разную длину (разное количество столбцов или строк).
- ценности массив не содержит хотя бы одного положительного и одного отрицательного значения.
- Любая из последующих дат предшествует первой дате.
- Результат не найден после 100 итераций. В этом случае попробуйте другое предположение.
#ЦЕННОСТЬ! ошибка
Ошибка #VALUE может быть вызвана следующими причинами:
- Любой из поставляемых ценности являются нечисловыми.
- Некоторые из предоставленных дат не могут быть идентифицированы как действительные даты Excel.
Вот как вы вычисляете XIRR в Excel. Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить наш образец книги ниже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
ЧИСТРРР шаблон Excel (файл .xlsx)
