Функция XIRR: лучший способ рассчитать доходность
- 22 Октября 2021
Пару лет назад у меня был счет в ВТБ. У них есть приложение «Мои инвестиции», которым я пользовался. В какой-то момент, после нового обновления появился раздел с аналитикой портфеля. Там можно было узнать доходность за год/месяц и тд.
Поначалу все работало нормально, но стоило мне внести средства и купить новые акции, как вся статистика по доходности полетела. Приложение выдавало совершенно несопоставимые с реальностью цифры.
И тогда я озадачился тем, как посчитать реальную доходность своего портфеля с учетом регулярных пополнений.
Сейчас ВТБ все исправили и доходность считается адекватно. Возможно, у других брокеров проблема осталась, и эта статья поможет вам с ней разобраться. К тому же, если у вас несколько счетов/видов инвестиций, вы сможете посчитать суммарную доходность.
Если вы положили на счет 100 тыс. р., инвестировали их и благополучно забыли на год, то посчитать доходность не составит труда. Предположим, к концу года у вас на счете образовалось 120 тыс. руб., тогда годовая доходность составит:
120/100-1=0,2 или же 20%
Проблемы начинаются, стоит только немного усложнить этот пример. Предположим, прошел не год, а 8 мес. В таком случае, 20% — это прибыль за 8 месяцев. Но общепринято высчитывать именно годовую доходность, чтобы было проще сравнить с тем же банковским вкладом. Для этого нужно провести дополнительные расчеты. Есть два варианта:
А что делать, если инвестор периодически пополняет счет или снимает средства?
На помощь нам приходит функция Excel XIRR или в русской версии ЧИСТВНДОХ.
Функция очень простая в использовании, но сложная для понимания. ЧИСТВНДОХ рассчитывает IRR, внутреннюю норму доходности при нерегулярных денежных потоках. Этот показатель часто используется при оценке привлекательности инвестиционных проектов. IRR — такая ставка дисконтирования, при которой совокупный денежный поток проекта равен нулю. Не буду вдаваться в подробности оценки проектов, сейчас не об этом. Как мы можем применить ЧИСТВНДОХ для расчета доходности нашего портфеля?
Для этого нам понадобятся вводные данные, а именно: сумма на начало периода, сумма на конец периода и суммы ввода/вывода средств с датами. Эти данные можно найти в брокерском отчете или отчете о движении денежных средств.
Ниже приведен пример. Сумма на начало периода — 100 тыс. руб. Сумма на счете на конец периода — 170 тыс. руб. Конечная сумма и вывод средств выписываются со знаком «-», начальная сумма и пополнения счета со знаком «+».
Знаки можно расставить наоборот, итоговый результат не изменится. Тут уже кому как удобнее.
Дата | Поток, руб. |
31.12.2019 | 100000 |
28.02.2020 | 10000 |
02.05.2020 | 15000 |
18.09.2020 | -20000 |
31.12.2020 | 15000 |
15.04.2021 | 30000 |
30.06.2021 | -20000 |
30.09.2021 | 10000 |
20.10.2021 | -170000 |
Далее прописываем функцию. Первый аргумент — наши денежные потоки, второй — соответствующие им даты, третий вводить не нужно.
В итоге мы получили ответ — 13,08%. Функция считает сразу годовую доходность.
Чтобы посчитать доходность за период, нужно воспользоваться формулой, которую упоминали ранее. Только на этот раз в степени мы будем делить период на кол-во дней в году. Количество дней в периоде легко найти: нужно вычесть из конечной дату начальную.
Доходность за период, а именно за 659 дней, составила 24,8%.
Что делает функция ЧИСТВНДОХ?
Это раздел для тех, кто любит копнуть поглубже. Смысл работы функции заключается в следующем:
Входящие потоки (начальная сумма + зачисления) приравниваются к исходящим (конечная сумма + снятия).
По идее две стороны уравнения не будут равны, так как они отличаются как раз на величину доходности, которую мы пытаемся найти.
И функция как раз рассчитывает эту доходность с учётом разного времени зачисления.
Она подбирает такую ставку, при которой разница между входящими и исходящими потоками будет равна 0.
Если бы мы попытались рассчитать доходность простым способом, поделив начальную сумму на конечную, мы бы не учли зачисления и списания.
Можно поделить конечную сумму на сумму начальных средств и всех потоков за период. Но тогда мы не учтем время зачислений.
Единственный альтернативный вариант, который может показать доходность близкую к ЧИСТВНДОХ — это расчет средневзвешенного капитала за период. Но это достаточно муторная тема, особенно если потоков много.
Таким образом, функция ЧИСТВНДОХ или XIRR — оптимальный вариант для расчета доходности вашего портфеля.