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


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

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

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

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

доходность инвестиционного портфеля excel

Если бы мы считали по простой формуле, то получили бы результат (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:

доходность инвестиционного портфеля формула

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

доходность инфестиционного портфеля

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

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

Поделиться в соцсетях

комментариев 59

  1. Хорошая метода. Тоже так делаю. Правда, пользуюсь не экселем, а опенофисовским «кальком», оно как-то привычнее.

  2. Александр:

    А если срок владения акциями больше одного года, то эта функция будет считать годовую доходность?

  3. Александр:

    Спасибо.

  4. Александр:

    А еще вопрос есть по этой теме?
    Доходность по этой формуле может быть отрицательность?
    Есть эмитент в портфеле. Ниже даты покупки и последняя строка, стоимость продажи на сегодня.
    Сумма покупки 20963, сумма продажи 13689, т.е. по идее доходность должна отрицательной, а в формуле получается 0,0000002980232%.
    Можете пояснить данную ситуацию?
    Заранее спасибо.

    25.09.2013 1 745
    25.09.2013 2 618
    25.09.2013 6 108
    29.10.2013 813
    11.11.2013 1 540
    15.11.2013 722
    16.12.2013 718
    16.12.2013 718
    28.02.2014 711
    28.02.2014 712
    28.02.2014 2 846
    03.06.2014 1 712 20 963 сумма покупки
    10.10.2015 -13 689

    • ActiveInvestor:

      Вводите так и будет вам счастье)
      25.09.2013 10471
      29.10.2013 813
      11.11.2013 1540
      15.11.2013 722
      16.12.2013 1436
      28.02.2014 4269
      03.06.2014 1712
      10.10.2015 -13689

      • Олег:

        Извините, а я не понял как считать доходность инвестиций в Excel. Я взял Ваш пример и незначительно изменил цифры
        1112 1.01.2014; 525 1.03.2014; — 312 1.04.2015; -1303 31.12.2014
        Результат должен быть около 8 %
        Получилось — 1, 6 %.

        • ActiveInvestor:

          Обе формулы из этой статьи дают один и тот же результат -1,67%. Почему вы решили, что результат должен быть около 8%? Даже если посчитать по простой формуле получится -1,66%.

          • Олег:

            Добрый день! В Вашей статье, в разделе «Как считать доходность инвестиций в Excel» Вы рекомендуете использовать функцию ЧИСТВНДОХ и приводите пример из статьи АРСАГЕРЫ: 1000 1.01.2014; 500 1.03.2014; — 300 1.04.2015; — 1300 31.12.2014. Формула выдает ответ 8 %. Я незначительно изменил вводимые денежные суммы 1112 (вместо 1 000) 1.01.2014; 525 (вместо 500) 1.03.2014; — 312 (вместо 300) 1.04.2014; -1303 (вместо 1 000) 31.12.2015. Отличия незначительные, значит и ответ должен быть около 8 %. Но получается 1, 67 %.

            • ActiveInvestor:

              В том то и дело, что ваши изменения значительны. Можно проверить по простой формуле:
              1300/(1000+500-300)-1*100%=8,3%
              1303/(1112+525-312)-1*100%=-1,66%

  5. Александр:

    Ага, понял. Теперь все получилось. Спасибо.

  6. Александр:

    А как Вы считаете доходность всего портфеля? Есть среднегодовая доходность по нескольким эмитентам, у одного больше, у другого меньше, у какого то вообще отрицательная. Вывести среднеарифметическое из этих доходностей, как мне кажется, не совсем верно?

    • ActiveInvestor:

      Я не считаю доходность по каждой акции в отдельности. Считаю по всей стоимости портфеля.

  7. Олег:

    Спасибо за ответ. Я ввел реальные данные своей торговли с 2012 по 2015 год в функцию ЧИСТВНДОХ. По всем эмитентам формула показывает 0,0000002980232%.

  8. Алексей:

    А расчет в отчетах брокера правильный? Или надо проверять?

    • ActiveInvestor:

      Мой брокер не считает доходность, а как считает ваш — не знаю) Лучше уточнить этот вопрос у него.

  9. Алексей:

    В отчетах есть анализ операций. Там довольно большая «портянка». Капитал (взвешенный во времени),начальный,дополнительный,изъятый. Даже коэффициент Шарпа)).

  10. Kz:

    А зачем во втором случае (где 10000$) надо считать по отдельным формулам? отчего бы не применить ту же функцию ЧИСТВНДОХ?

  11. Владимир:

    а в чем может быть проблема, процент не выводит.

    1000 18.01.2015
    500 25.11.2015
    -300 05.02.2016
    1300 10.02.2016

    2,98023E-09

  12. Владимир:

    Спасибо.

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

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

  13. Владимир:

    где я ошибся?

    57100 18.01.2015
    14789 05.02.2016
    6947 10.02.2016
    14515 15.02.2016
    5095 24.02.2016
    7400 01.03.2016
    5040 02.03.2016
    -3063 03.03.2016
    -2165 09.03.2016
    5808 10.03.2016
    35900 11.03.2016
    7860 21.03.2016
    7800 12.04.2016
    -1599 04.05.2016
    198 13.05.2016
    60 23.05.2016
    7510 26.05.2016
    65000 02.06.2016
    -6474 03.06.2016
    46483 22.06.2016
    35713 23.06.2016
    2344 28.06.2016
    45000 04.07.2016
    21018 08.07.2016
    -3297 11.07.2016
    13600 12.07.2016
    10140 13.07.2016
    4000 15.07.2016
    712 25.07.2016
    1509 26.07.2016
    922 27.07.2016
    1674 01.08.2016
    25000 02.08.2016
    -237855 03.08.2016

    2,98023E-09

  14. Владимир:

    Отличная статья, спасибо вам!

  15. Александр:

    Мне кажется, расчёт не верный (в excel не перепроверял):
    — 1 ввод средств 01.01.2014
    — 2 ввод средств «через 3 месяца» — почему 01.03? Разве не 01.04?
    — частичная продажа «ещё через 4 месяца»… Не 01.08?

  16. Александр:

    😉

  17. Сергей:

    Алексей добрый день!

    А по какой формуле будет корректно считать общую доходность за произвольный период?
    Я пробую забивать данные за 721 день — в ЧИСТВНДОХ считает 10,41%, в ручном ексель 21,27%

    Хотя если разбить по годам — получается одинаково в обоих: 26,25% (1-й год) и 2,05% (2-й год)

    Спасибо.

    • ЧИСТВНДОХ может рассчитать доходность и за произвольный период. Почему у вас цифры отличаются, я не знаю, возможно вы не перевели в проценты годовые.

  18. Сергей:

    1. ЧИСТВНДОХ за произвольный период покажет среднюю годовую доходность? Т.е. если за пять лет прирост инвестиций будет +50%, то он покажет доходность 5% ?

    2. Извините, но не понял как можно переводить результаты в проценты годовые? Я просто выбираю формат ячейки «процентный»

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

  19. Сергей:

    Здравствуйте. А как посчитать среднегодовые % (убытки)? Т.е. есть даты и суммы которые вносились на ИИС(!) в течение 3-х лет. И есть текущая сумма инвестиций (убытки). XIRR (ЧИСТВНДОХ) если прибыль, то нормально считает, а если убытки, то до какого-то минусового % считает, дальше 0,0000000029802322387695300 и все.

  20. Сергей:

    Внесения, например
    01.01.17 100
    01.01.18 100
    05.05.18 200
    05.08.19 500
    И т.д.
    По итогам например на счете 200. (в ячейке 200 будет с минусом) Так XIRR не считает. Помогите пожалуйста разобраться. Выводит 0,000000298023223876953000000000%

    • А попробуйте посчитать вторым способом из этой статьи.

      • Сергей:

        Я спросил потому я хотел просто среднегодовую доходность рассчитать, а не годовую.

      • Сергей:

        А как можно подсчитать вторым способом при начале инвестирования? Или рыночная стоимость это будет первая покупка акций? Т.е. купили акции на 100. Это и есть рыночная стоимость на начало периода?

      • Сергей:

        Вторым методом нельзя считать вообще.
        Сравните результат:
        01.07.14 1000
        02.07.14 1
        31.12.14 1
        01.01.15 -1400
        Результат 94,26624% (XIRR)

        По второму варианту будет 39,760240%

  21. Сергей:

    А Вот так уже не считается.
    01.01.14 100
    01.04.14 500
    30.07.14 1000
    01.01.15 -1300
    0,00%
    Ваше мнение, почему?

    • Сергей:

      У меня вот так получилось по второму методу.
      Числитель -300
      Знаменатель 901,369863
      Результат -33,28%

  22. Сергей:

    По двум методам результаты отличаются.
    01.01.14 300
    01.04.14 500
    30.07.14 1000
    01.01.15 -1300
    -41,94%

    Числитель -500
    Знаменатель 1101,369863
    Результат -45,40%

  23. Сергей:

    Удивительно XIRR работает.
    01.01.14 100
    01.04.14 500
    30.07.14 899
    01.01.15 -1300
    Результат -22,17193%

    01.01.14 100
    01.04.14 500
    30.07.14 900
    01.01.15 -1300
    Результат 0,00000%

  24. Сергей:

    Иногда, при отрицательных значениях, функция может неправильно работать или выдавать ошибку. Чтобы её починить, надо использовать 3-ий параметр с подсказкой. Если туда написать -0,1(т.е. даже отдаленно напоминающую доходность), то будет показывать правильно отрицательную доходность.

  25. Сергей:

    Вторым методом нельзя считать вообще.
    Сравните результат:
    01.07.14 1000
    02.07.14 1
    31.12.14 1
    01.01.15 -1400
    Результат 94,26624% (XIRR)

    По второму варианту будет 39,760240%

    • Анон:

      Второй метод даёт доходность за период владения, если перевести в годовые то цифры сойдутся.
      (1 + 0,3976)^(365/184) — 1 = 0,9427

  26. PH:

    А по какой формуле рассчитать ежемесячную сумму инвестиций?
    Допустим, я посчитал размер желаемого капитала, который хочу получить через 30 лет. Я знаю среднегодовую доходность. Как мне высчитать, сколько мне надо ежемесячно инвестировать при такой доходности, чтобы получить этот капитал через 30 лет?

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *