Xirr что это такое и как рассчитать
Функция ЧИСТВНДОХ
В этой статье описаны синтаксис формулы и использование функции ЧИСТВНДОХ в Microsoft Excel.
Описание
Возвращает внутреннюю ставку доходности для графика денежных потоков, которые не обязательно носят периодический характер. Чтобы рассчитать внутреннюю ставку доходности для ряда периодических денежных потоков, следует использовать функцию ВСД.
Синтаксис
Аргументы функции ЧИСТВНДОХ описаны ниже.
Значения Обязательный. Ряд денежных потоков, соответствующий графику платежей, приведенному в аргументе «даты». Первый платеж является необязательным и соответствует затратам или выплате в начале инвестиции. Если первое значение является затратами или выплатой, оно должно быть отрицательным. Все последующие выплаты дисконтируются на основе 365-дневного года. Ряд значений должен содержать по крайней мере одно положительное и одно отрицательное значение.
Предп Необязательный. Величина, предположительно близкая к результату ЧИСТВНДОХ.
Замечания
В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 448 дней.
Числа в аргументе «даты» усекаются до целых.
В xiRR ожидается по крайней мере один положительный и один отрицательный денежный поток; в противном случае XIRR возвращает #NUM! значение ошибки #ЗНАЧ!.
Если любое число в датах не является допустимой датой, то xiRR возвращает #VALUE! значение ошибки #ЗНАЧ!.
Если любое число в датах предшествует начальной дате, то xiRR возвращает #NUM! значение ошибки #ЗНАЧ!.
Если значения и даты содержат различное количество значений, то #NUM! значение ошибки #ЗНАЧ!.
В большинстве случаев задавать аргумент «предп» для функции ЧИСТВНДОХ не требуется. Если этот аргумент опущен, то он полагается равным 0,1 (10 процентов).
Функция ЧИСТВНДОХ тесно связана с функцией ЧИСТНЗ. Ставка доходности, вычисляемая функцией ЧИСТВНДОХ — это процентная ставка, соответствующая ЧИСТНЗ = 0.
Excel для вычисления XIRR используется метод итеративный метод. Используя изменяемую ставку (начиная с прогноза), XIRR цикличен по вычислениям, пока результат не будет точным в пределах 0,0000001 процента. Если после 100 попыток xiRR не получается найти результат, #NUM! возвращается значение ошибки. Ставка будет изменена до:
di = дата i-й (последней) выплаты;
d1 = дата 0-й выплаты (начальная дата);
Pi = сумма i-й (последней) выплаты.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Xirr что это такое и как рассчитать
Возвращает внутреннюю норму прибыли для запланированных денежных потоков, которые могут не являться периодичными.
Синтаксис
Параметры
Термин | Определение |
---|---|
table | Таблица, для которой должны быть вычислены выражения значений и дат. |
значения | Выражение, возвращающее значение денежного потока для каждой строки таблицы. |
даты | Выражение, возвращающее дату денежного потока для каждой строки таблицы. |
guess | (необязательно) Начальное предположение для внутренней нормы прибыли. Если этот параметр опущен, используется предположение 0,1 по умолчанию. |
alternateResult | (Необязательно) Значение, возвращаемое вместо ошибки, если не удается определить решение. |
Возвращаемое значение
Внутренняя норма прибыли для заданных входных данных. Если вычисление не возвращает допустимый результат, возвращается ошибка или значение, указанное как alternateResult.
Remarks
Значение вычисляется как норма, удовлетворяющая следующей функции:
Ряд значений денежного потока должен содержать по крайней мере одно положительное число и одно отрицательное число.
Не используйте функции ISERROR или IFERROR для перехвата ошибки, возвращенной XIRR. Если некоторые входные данные функции могут привести к отсутствию решения ошибки, предоставление параметра alternateResult является самым надежным и оптимальным для производительности способом обработки ошибки.
Эта функция не поддерживается для использования в режиме DirectQuery при использовании в вычисляемых столбцах или правилах безопасности на уровне строк (RLS).
Пример
В следующей формуле вычисляется внутренняя норма прибыли для таблицы CashFlows:
Расчет доходности портфеля методом XIRR. Инструкция для инвесторов
В этой статье расскажем об одном из простых и универсальных, но эффективных способов расчета доходности по формуле XIRR.
Зачем рассчитывать доходность?
Что такое XIRR?
В отличие от IRR, XIRR учитывает не только денежные потоки и ставки дисконтирования, но и соответствующие даты. Это позволяет рассчитывать доход, если вы периодически вносите деньги на инвестиционный счет и снимаете их с него.
Как рассчитывать доход по формуле XIRR?
Для расчета вам нужно заполнить Excel или Google таблицу, где указаны все денежные потоки по вашему инвестиционному счету за нужный период, например, год. Необходимо поставить, когда были совершены пополнения и снятия средств.
Расходные операции должны быть со знаком минус. В конце проставляется стоимость инвестиционного портфеля на конец периода. Здесь нужно использовать сумму с минусом, как будто вы снимаете все деньги со счета, иначе функция сработает неверно.
Вот как должна выглядеть итоговая таблица. Лучше вести ее и вписывать операции по мере их совершения, чтобы потом ничего не упустить.
После заполнения таблицы нужно задать функцию XIRR. В Excel она называется ЧИСТВНДОХ. Выбираете «Финансовые функции» и введите в ячейку, где хотите видеть результат расчета доходности, такую формулу:
В нашем примере получается процент доходности 14%.
доходность × количество дней в году / количество дней, когда вы инвестировали средства
Соответственно получаем: 14 × 366 / 180 = 28
Конечно, брокерские компании и предоставляют отчеты, где можно отследить и движения средств по счету, и показатель доходности. Но грамотный инвестор никогда не упускает возможность использовать все инструменты для проверки своей стратегии. Тем более, когда метод расчета простой, универсальный и занимает всего несколько минут.
Описание алгоритма, используемого функцией XIRR() в Excel
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Сводка
В следующих сведениях описывается алгоритм, используемый функцией XIRR() в Microsoft Excel для вычисления внутренней скорости прибыли по расписанию денежных потоков, которые не обязательно являются периодическими. То есть платежи могут быть сделаны с разными интервалами времени.
Дополнительные сведения
Если функция XIRR() недоступна, необходимо установить надстройку Analysis ToolPak.
С помощью IRR() все денежные потоки дисконтирования с помощью нескольких периодов соединения. Например, первый платеж со скидкой один период, второй — два периода и так далее.
Функция XIRR() позволяет платежам происходить в неравные периоды времени. С помощью этой функции вы связываете дату с каждым платежом и тем самым разрешаете дробные периоды (повышение или дисконтирования с дробной мощностью).
Следующий шаг — вычислить правильную ставку дисконтирования. В принципе, чем больше скорость, тем больше уменьшаются значения.
Функция XIRR() устанавливает границы над и ниже правильной ставки, удваив догадки в каждом направлении. С известными верхними и нижними границами функция использует метод Ньютона, чтобы найти соответствующую догадку до нужного уровня точности.
Расчет дисконтирования выполняется после каждой итерации.
Метод Ньютона — это способ приближения к корню уравнения (y=f(x)) с помощью касательной линии к кривой уравнения при последовательных x-значениях. Новое x-value все ближе и ближе к корневому корню уравнения, пока не достигнет определенной заранее определенной точности.
Как быстро посчитать свою доходность от инвестиций | Формула + готовый шаблон (файл) в Гугл
Сможете ответить на простой вопрос.
Сколько вы заработали (или потеряли) денег на инвестициях за прошлый год? А в сумме за 2-3 года? 5 лет. Какой был у вас результат в позапрошлом году?
Все эти данные можно конечно достать из отчетов брокеров. Или примерно прикинуть, сколько было и сколько стало денег на счет. И вывести какой то финансовый результат. Пять, десять, пятьдесят, сто тысяч, миллион (рублей, долларов)
А сколько это в относительной доходности? То есть, годовой прибыли в процентах. Ситуация может осложняться периодическими вводами-выводами денег с брокерского счета.
Закинули сегодня денежку. Через пару месяцев еще, потом еще. Иногда снимали средства. Или дивиденды могут капать сразу на карту (а не на брокерский счет). В итоге получается целая вереница движений денег.
Ну так что, сможете сказать про свои реальные финансовые достижения?
Если ответ положительный, то респект.
Я уже несколько лет самостоятельно веду учет эффективности своего инвестиционного портфеля. И могу с точностью до доли процента ответить.
Зачем вести учет инвестиций
Произошла сильная просадка по позиции. Закрывать с убытком жалко. Оставим (заморозим) до лучших времен. Со временем котировки восстановятся. И можно будет избавиться от позиции.
А что в итоге? Какова результативность?
А давай переведем твой результат за несколько лет в среднегодовую доходность? И поймем, насколько выгодна твоя стратегия.
И тут может выяснится неприятная вещь. Оказывается со всеми сделками, комиссиями брокеру и прочими расходами (включая периодические заморозки убыточных позиций) реальный результат не такой впечатляющий. Вплоть некоторые игроки, за несколько лет получали окололунную или весьма скромную доходность.
Среднегодовая доходность позволяет оценить эффективность используемой стратегии.
Это может быть какой-нибудь фондовый индекс. Или например, безрисковые ставки по банковским вкладам или надежным облигациям.
Допустим, вы все посчитали. Вывели какой то результат. Например, за прошлый год вы заработали 15% годовых. Много это или мало?
Непонятно. Нужно с чем-то сравнивать. Если вы инвестируете в российские акции, то ориентир для вас будет индекс Московской биржи. Вернее финансовые инструменты в лице фондов (БПИФ или ETF).
Покупаете американские акции? Берем соответствующий ETF для сравнения. На тот же индекс S&P 500.
И может получится, у вас годовая прибыль 15%, а соответствующий индекс вырос на 25%.
Или за 3 последних года, путем отбора, анализа отдельный акций инвестор получал среднегодовую доходность 12%. А фонд показал среднегодовую прибыль в 14%.
Как считать доходность?
Многие скажут, финансовый результат можно посмотреть в отчетах или личном кабинете своего брокера.
Действительно. У большинства брокером это есть.
Во-первых. Некоторые брокеры не совсем корректно рассчитывают доходность (не знаю с чем это связано).
Я использую обычные электронные таблицы (EXCEL или Google вам в помощь).
С помощью функции XIRR.
Формула расчета доходности:
= XIRR ( B1:B»x» ; C1:C»x» ) * 100
= XIRR (денежные потоки ; даты выплат )
Потребуются два вида данных:
В итоге получается таблица примерно такого вида.
Обратите внимание на несколько моментов.
Средняя доходность за несколько лет
Аналогично можно рассчитать среднегодовую доходность за несколько прошлых периодов.
Вводим данные: деньги + даты ввода-вывода.
И вуаля. Все готово.
Разное
Как считать доходность по отдельным годам?
Все тоже самое. Только нужно знать дополнительно сколько денег было на счете (или счетах) на начало и конец каждого года.
Как лучше вести учет, если несколько счетов?
Вариант 1. Все ваши инвестиции работают по принципу одной стратегии. Весь капитал составляют единую структуру портфеля, то разбивать учет на несколько составляющих, не вижу смысла. Простой пример, долгосрочное пассивное накопление капитала.
Вариант 2. Когда у вас несколько различных стратегий, то лучше вести по ним отдельный учет. Например, вы копите деньги на пенсию. Для этого у вас есть активы, которые вы просто покупаете и держите всю жизнь. Параллельно вы торгуете на срочном рынке (с кредитным плечом). Возможно еще спекулируете акциями.
В итоге у вас есть 2-3 различных стратегии.
И правильно будет разделить учет доходностей на несколько разные портфелей. Плюс один общий, как суммарный результат.
Готовая таблица
Все вышеперечисленные примеры с расчетами доходностей выложил в онлайн-таблице (нужен аккаунт гугл).