Xirr что это такое и как рассчитать

How to calculate annualized return (XIRR) from a stock investment

In this post, I discuss how to calculate the annualized return from a stock investment after accounting for corporate actions like dividends, stock splits, bonuses, buybacks and rights issues. I have made a calculator that will calculate the annualized return (CAGR for single investments, IRR for periodic investments and XIRR or investments on random dates) and would like to use this post for its beta release.

It also gives me a chance to discuss the methods used for the calculation with readers. If you do not know what is XIRR and how it is calculated, I would suggest that you start here: CAGR vs. IRR: Understanding investment growth measures and then go here: What is XIRR?

I am indebted to discussions with Captain Navneet Bindra in the development of the calculator. Thanks are also due to Vignesh Bhaskar, Ashwin Sheoroy for testing the sheet.

Let us discuss the calculation via examples.

Stock XIRR Calculation: buying and selling

1. you buy 200 stocks of a company at Rs. 1000 a share on 1st Jan 2010
2. You sell 25 of those stocks at Rs. 6000 a share on 7th July 2017 and
3. calculate the XIRR as on 8th October 2017 (current market price is Rs. 6100) what is the annualized return?

To do this in Excel

Stock XIRR Calculation: bonus shares

Now suppose the company announces a 1:1 bonus on 2nd Feb 2011.

Since you hold 200 shares, you will be given 200 more shares and the price will fall by half to Rs. 500. This is an internal adjustment will not be used as a cash flow entry for XIRR calculation. The price from this point on will reflect this bonus.

The redemption and valuation entries will be shown as above for all illustrations below.

Stock XIRR Calculation: Stock split

Now on 3rd March a 3:2 split is announced. That is, since you hold 400 shares, half that number or 200 shares will be given to you. This is because

400 x (3/2) = 400 x (1.5) = 400 + 400 * 0.5 = 400 + 200.

So now, you will hold 600 shares. The price will drop by a factor of 1/.5. For example, if the price before the split is Rs. 500, it will drop to 500/1.5 = 333.33.

This will again not be reflected in the cash flow.

Stock XIRR Calculation: Dividends

Now a dividend of Rs. 2 per share is announced on 4th April 2013. Regardless of what you do with the dividend, in order to find the XIRR of the instrument, it will be assumed to be reinvested at the ex-dividend market price.

So the dividend amount of 2 x 600 = 1200 is used to imaginary stocks for the XIRR calculation at the ex-div market price of Rs. 200.

So 1200/500 gives 2.4 stocks. Making the total stocks held as 602.4.

Now a second dividend is announced on 9th Sep 2013 for Rs. 5 per share. The actual dividend received is 5 x 600 = 3000. However, due to the first dividend there 602.4 stocks assumed to be held for the XIRR calculation. Therefore the dividend that will be assumed to be reinvested is

5 x 602.4 = 3012. This amount will be used to buy 5.48 imaginary stocks at the ex-dividend price of Rs. 550. Taking the XIRR share tally to 607.88

Many investors are confused by this and say that this wrong. It is, however, important to recognise that a dividend is an action by the instrument and not by us. Reinvesting stock or mutual fund dividends at ex-price is the standard procedure adopted to calculate instrument XIRR. Investor XIRR can be different. Will discuss that in the post when the final calculator is released.

Stock XIRR Calculation: Rights Issue

On 5th May 2014, the company announces a rights issue. This allows existing investors to buy some shares at a discount.

Suppose you buy 10 stocks at Rs. 950 a share against the then market price of Rs. 1000, the no of actual stocks = 610. The no of XIRR stocks (imaginary) = 617.88

Since we spend money to participate in the rights issue, the 10 x 950 = 9500 wil be shown in the cash flow.

Stock XIRR Calculation: Buyback

Now a buyback is announced on 6th June 2015. The price is Rs. 1600 a premium over the then market price of Rs. 1500. Suppose, you decide to sell 200 shares to the company.

The total no of actual stocks held will become 410.

A buyback is similar to a dividend (I am referring to accounting aspects only. There are other differences). In a dividend, the no of stocks remains the same, the price falls to the extent of the dividend.

In a buyback, the no of shares decreases, the market price is not directly affected by the buyback and like a dividend, the investor gets money in hand. Therefore we assume that the money received will be reinvested back at the then market price.

The buyback amount received = 200 x 1600 = 320000. This is reinvested at the market price of Rs. 1500 to buy 213.33 imaginary shares, taking the XIRR tally to 831.21 shares.

Finally, there is a redemption of 25 shares 7th July 2107 and the XIRR calculation is done as on 8th Oct 2017 (as in the above cases).

The XIRR or the annualized return of the stock instrument can be seen in each of the above figures.

This is the beta version of the stock XIRR calculator. Use it ONLY if you wish to help me test it.

Как рассчитать доходность инвестиций с учетом ввода/вывода средств?

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

Для этого рассчитывают результат инвестирования с учетом вводов/выводов средств и делят его на средневзвешенную по времени величину вложенных средств. Данный метод расчета очень подробно описан на сайте УК Арсагера, поэтому здесь я его описывать не буду. Те, кто читал их статью, знают, что этот метод очень трудоемкий, все приходится считать вручную. Если вы много раз вводили и выводили деньги, то формула расчета доходности портфеля будет ооочень длинной, легко запутаться и сделать ошибку. Поэтому я объясню, как очень просто посчитать доходность инвестиций в Excel.

Как считать доходность инвестиций в Excel

В Excel для расчета доходности инвестиций с учетом ввода/вывода денег используется функция ЧИСТВНДОХ (XIRR) — это функция, которая возвращает внутреннюю ставку доходности для графика денежных потоков, которые не обязательно носят периодический характер. Как ей пользоваться? Возьмем пример из статьи Арсагеры:

1. Инвестор купил акций на сумму 1000 рублей.
2. Через 3 месяца он купил еще акций на 500 рублей.
3. Еще через 4 месяца он продал часть акций на сумму 300 рублей.
4. Через год после первоначального приобретения, стоимость акций составила 1300 рублей.

Доходность портфеля составила 8,004% годовых.

Введем эти данные в Excel. В первой колонке указываем суммы, во второй даты.

• В первой строчке указываем начальную сумму инвестиций 1000 рублей и дату инвестирования, к примеру 01.01.2014.
• Во второй строчке указываем ввод средств 500 рублей и дату 01.03.2014.
• В третьей строчке указываем вывод средств со знаком минус -300 и дату 01.04.2014.
• В четвертой строчке указываем стоимость портфеля на конец года со знаком минус -1300 и дату конец года 31.12.2014.

Теперь выбираем какую-нибудь пустую ячейку и жмем кнопку fx (вставить функцию). Находим функцию ЧИСТВНДОХ. Вводим значения ячеек. В строке «Значения» выбираем ячейки с суммами, в строке «Даты» — ячейки с датами.

Жмем ОК, получаем доходность — 8,009% годовых.

Если бы мы считали по простой формуле, то получили бы результат (1300-1200)/1200=8,3%. Вроде бы разница небольшая, но в других примерах разница может составить несколько процентов.

Функцию в ячейку так же можно вписать руками. Для этого в пустой ячейке впишите текст: =ЧИСТВНДОХ(A1:A4;B1:B4), номера ячеек укажите свои.

Расчет доходности инвестиционного портфеля за год

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

Рыночная стоимость портфеля на 31 декабря 2004 года: 10000$
20 марта 2005 года: внесение 1000$
25 июня 2005 года: изъятие 500$
1 октября 2005 года: внесение 1000$
Рыночная стоимость портфеля на 31 декабря 2005 года: 12000$

Вносим данные в Excel:

Формулы расчетов ниже:

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

Функция ЧИСТВНДОХ (XIRR)

• В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 г. соответствует число 1, а 1 января 2008 г. — число 39 448, поскольку интервал между ними составляет 39 448 дней.
• Числа в аргументе «даты» усекаются до целых.
• В функции ЧИСТВНДОХ предполагаются по крайней мере один положительный и один отрицательный денежный поток; в противном случае эта функция возвращает значение ошибки #ЧИСЛО!.
• Если хотя бы одно из чисел в аргументе «даты» не является допустимой датой, то функция ЧИСТВНДОХ возвращает значение ошибки #ЧИСЛО!.
• Если хотя бы одно из чисел в аргументе «даты» предшествует начальной дате, то функция ЧИСТВНДОХ возвращает значение ошибки #ЧИСЛО!.
• Если количество значений в аргументах «значения» и «даты» не совпадает, функция ЧИСТВНДОХ возвращает значение ошибки #ЧИСЛО!.
• В большинстве случаев задавать аргумент «предп» для функции ЧИСТВНДОХ не требуется. Если этот аргумент опущен, то он полагается равным 0,1 (10 процентов).
• Функция ЧИСТВНДОХ тесно связана с функцией ЧИСТНЗ. Ставка доходности, вычисляемая функцией ЧИСТВНДОХ — это процентная ставка, соответствующая ЧИСТНЗ = 0.
• В Microsoft Excel функция ЧИСТВНДОХ вычисляется с помощью итеративного метода. Используя меняющуюся ставку (начиная со значения аргумента «предп»), функция ЧИСТВНДОХ выполняет циклические вычисления, пока не получит результат с точностью до 0,000001%. Если функции ЧИСТВНДОХ не удается найти результат за 100 попыток, возвращается значение ошибки #ЧИСЛО!. Ставка меняется до тех пор, пока не будет получено следующее равенство:
  

XIRR в Excel (формула, примеры) — Как использовать функцию XIRR?

Функция XIRR относится к категории финансовых функций и будет рассчитывать внутреннюю норму прибыли (XIRR) для ряда потоков денежных средств, которые могут быть непериодическими, путем назначения конкретных дат каждому отдельному потоку денежных средств. Основное преимущество использования функции XIRR заключается в том, что эти неравномерно распределенные денежные потоки можно точно смоделировать.
В финансовом отношении функция XIRR полезна для определения стоимости инвестиций или понимания осуществимости проекта без периодических денежных потоков. Это помогает нам понять норму прибыли на инвестиции. Следовательно, это обычно используется в финансах, особенно при выборе между инвестициями.

Возвращает

XIRR популярен в анализе электронных таблиц, потому что, в отличие от функции XIRR, он допускает неравные промежутки времени между датами составления. Функция XIRR особенно полезна в финансовых структурах, поскольку сроки первоначальных инвестиций в течение месяца могут оказать существенное влияние на XIRR. Например, крупные инвестиции в акционерный капитал 1-го числа месяца дают значительно меньшую XIRR, чем инвестиции в акционерный капитал того же размера в последний день того же месяца. Принимая во внимание переменные периоды составления, функция XIRR учитывает точное количество дней в первом периоде и, следовательно, может помочь оценить эффект задержки в дате формирования партнерства в том же месяце.
Функция XIRR также имеет несколько своих уникальных недостатков. Поскольку в Excel используется внутренняя логика фактических дат, невозможно использовать функцию XIRR даже с ежемесячными периодами, соответствующими расчетам процентов по кредиту 30/360. Кроме того, хорошо известная ошибка с функцией XIRR на момент написания этой статьи заключается в том, что она не может обрабатывать набор денежных потоков, которые начинаются с нуля в качестве его начального значения.

XIRR Формула в Excel

Ниже приведена формула XIRR.

Как открыть функцию XIRR

Нажмите на вкладку «Формула»> «Финансовый»> «Нажмите на XIRR».

> Мы получаем новую функцию Windows, показанную ниже.

> Затем мы должны ввести данные о денежной стоимости и датах.

Ярлык использования формулы XIRR

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

= XIRR (диапазон значений денежного потока, диапазон значений дат)> Enter

Формула XIRR имеет следующие аргументы:

• Значения (обязательный аргумент) — это массив значений, представляющих серию денежных потоков. Вместо массива это может быть ссылка на диапазон ячеек, содержащих значения.
• Даты (обязательный аргумент) — это серия дат, соответствующих заданным значениям. Последующие даты должны быть позже первой, поскольку первая дата является начальной датой, а последующие даты являются будущими датами исходящих платежей или доходов.
• Угадай (необязательный аргумент) — это первоначальное предположение о том, каким будет IRR. Если не указано, Excel принимает значение по умолчанию, которое составляет 10%.

Формат результата

Это внутренняя процентная ставка, поэтому мы рассматриваем формат числа в процентах (%)

Как использовать функцию XIRR в Excel?

Эта функция XIRR очень проста и удобна в использовании. Давайте теперь посмотрим, как использовать XIRR в Excel с помощью нескольких примеров.

Вы можете скачать эту XIRR функцию Excel Template здесь — XIRR функцию Excel Template

Пример № 1

Расчет значения XIRR с использованием формулы XIRR = XIRR (H3: H9, G3: G9)

Ответ будет 4, 89%

В приведенной выше таблице приток процентов является нерегулярным. Следовательно, вы можете использовать функцию XIRR в Excel для вычисления
Внутренняя процентная ставка по этим денежным потокам, сумма инвестиций показывает знак минус. Когда вы получите результат, измените формат на%.

Пример № 2

Предположим, вы получили ссуду, подробности указаны в таблице «+», ссуде в рупиях 6000, которая показывает знак «минус» (-), а дата получения — 2–18 февраля, после этой даты отдыха и суммы EMI. Тогда мы можем использовать функцию XIRR для внутренней ставки возврата этой суммы.

Расчет значения XIRR с использованием формулы XIRR = XIRR (A3: A15, B3: B15)

Ответ будет 16.60%

Сначала в листе Excel введите первоначальную сумму инвестиций. Вложенная сумма должна быть представлена ​​знаком «минус». В следующих ячейках введите возвраты, полученные в течение каждого периода. «Не забудьте включить знак« минус », когда вы вкладываете деньги.

Теперь найдите XIRR, используя значения, относящиеся к серии денежных потоков, которые соответствуют графику платежей в датах. Первый платеж относится к инвестициям, сделанным в начале инвестиционного периода, и должен иметь отрицательное значение. Все последующие платежи дисконтируются на основе 365-дневного года. Ряд значений должен содержать как минимум одно положительное и одно отрицательное значение.

Дата означает день, когда были сделаны первые инвестиции и когда были получены доходы. Каждая дата должна соответствовать сделанным инвестициям или полученному доходу, как показано в таблице выше. Даты следует вводить в формате «ДД-ММ-ГГ (дата-месяц-год)», поскольку при несоблюдении этого формата могут возникнуть ошибки. Если какое-либо число в датах является недопустимым или формат даты несовместим, XIRR будет отображать ошибку «#value!».

Если мы обсуждаем проблемы с ошибками, это тоже важная часть. Если наши данные неверны, мы столкнемся с такой проблемой, как упомянуто ниже.

• #Num! Происходит, если либо:
• Предоставленные массивы значений и дат имеют разную длину.
• Массив предоставленных значений не содержит хотя бы одного отрицательного и хотя бы одного положительного значения;
• Любая из предоставленных дат переходит к первой предоставленной дате
• Расчет не сходится после 100 итераций.
• #значение! : — происходит, если ни одна из предоставленных дат не может быть признана действительными датами Excel.
• Если вы попытаетесь ввести даты в текстовом формате, существует риск того, что Excel может неправильно их интерпретировать, в зависимости от системы дат или настроек интерпретации даты на вашем компьютере.

Рекомендуемые статьи

Это было руководство к функции XIRR. Здесь мы обсуждаем формулу XIRR и как использовать функцию XIRR вместе с практическими примерами и загружаемыми шаблонами Excel. Вы также можете просмотреть наши другие предлагаемые статьи —