Функция XIRR: лучший способ рассчитать доходность

Пару лет назад у меня был счет в ВТБ. У них есть приложение «Мои инвестиции», которым я пользовался. В какой-то момент, после нового обновления появился раздел с аналитикой портфеля. Там можно было узнать доходность за год/месяц и тд.

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

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

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

Если вы положили на счет 100 тыс. р., инвестировали их и благополучно забыли на год, то посчитать доходность не составит труда. Предположим, к концу года у вас на счете образовалось 120 тыс. руб., тогда годовая доходность составит:

120/100-1=0,2 или же 20%

Проблемы начинаются, стоит только немного усложнить этот пример. Предположим, прошел не год, а 8 мес. В таком случае, 20% — это прибыль за 8 месяцев. Но общепринято высчитывать именно годовую доходность, чтобы было проще сравнить с тем же банковским вкладом. Для этого нужно провести дополнительные расчеты. Есть два варианта:

А что делать, если инвестор периодически пополняет счет или снимает средства?

На помощь нам приходит функция Excel XIRR или в русской версии ЧИСТВНДОХ.

Функция очень простая в использовании, но сложная для понимания. ЧИСТВНДОХ рассчитывает IRR, внутреннюю норму доходности при нерегулярных денежных потоках. Этот показатель часто используется при оценке привлекательности инвестиционных проектов. IRR — такая ставка дисконтирования, при которой совокупный денежный поток проекта равен нулю. Не буду вдаваться в подробности оценки проектов, сейчас не об этом. Как мы можем применить ЧИСТВНДОХ для расчета доходности нашего портфеля?

Для этого нам понадобятся вводные данные, а именно: сумма на начало периода, сумма на конец периода и суммы ввода/вывода средств с датами. Эти данные можно найти в брокерском отчете или отчете о движении денежных средств.

Ниже приведен пример. Сумма на начало периода — 100 тыс. руб. Сумма на счете на конец периода — 170 тыс. руб. Конечная сумма и вывод средств выписываются со знаком «-», начальная сумма и пополнения счета со знаком «+».

Знаки можно расставить наоборот, итоговый результат не изменится. Тут уже кому как удобнее.

Далее прописываем функцию. Первый аргумент — наши денежные потоки, второй — соответствующие им даты, третий вводить не нужно.

В итоге мы получили ответ — 13,08%. Функция считает сразу годовую доходность.

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

Доходность за период, а именно за 659 дней, составила 24,8%.

Что делает функция ЧИСТВНДОХ?

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

1. Входящие потоки (начальная сумма + зачисления) приравниваются к исходящим (конечная сумма + снятия).

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

3. И функция как раз рассчитывает эту доходность с учётом разного времени зачисления.

4. Она подбирает такую ставку, при которой разница между входящими и исходящими потоками будет равна 0.

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

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

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

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