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

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

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

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

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

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

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

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

комментария 63

  1. Прохожий:

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

    • Сергей:

      А вот это сможете подсчитать?
      01.01.14 100
      01.04.14 500
      30.07.14 1000
      01.01.15 -1300
      0,00%

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

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

    • ActiveInvestor:

      Да, доходность считается в процентах годовых.

  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%.

    • ActiveInvestor:

      Почитайте комментарии выше, там была аналогичная ошибка.

  8. Алексей:

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

    • ActiveInvestor:

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

  9. Алексей:

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

    • ActiveInvestor:

      У моего все попроще. Я считаю доходность сам, так прозрачнее.

  10. Kz:

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

    • ActiveInvestor:

      Да, в принципе можно и с помощью ЧИСТВНДОХ считать.

  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

    • Попробуйте вводить не сделки, а как я писал выше: цифры ввода/вывода денег со счета.

      • Владимир:

        Показал 17.1 % Спасибо. А как проще всего будет перепроверить «руками»?

      • Владимир:

        а это он показывает годовую доходность или за тот период который показывает начальная и конечная дата?

  14. Владимир:

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

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

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

    • Да, спасибо что заметили, я неверно интерпретировал даты операций из примера Арсагеры. Теперь пересчитал правильно, получилось 8,009%.

  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%

    • Сергей:

      Кому интересно могут сами убедиться.
      https://cloud.mail.ru/public/5n5z/yZJNvhgae

  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 лет?

    • Формула довольно сложная, можете ее посмотреть здесь http://fingramota22.ru/materials/base/material.php?ID=71

  27. Никита:

    Здравствуйте Алексей, подскажите пожалуйста, а как считать если например 1 000$ которые были внесены 20 марта 2005 года это прибыль полученная с 10 000$?

  28. Петр:

    Если Знаменатель отрицательный, то расчет идет с ошибкой (бОльгшая сумма числителя усугубляет показатели доходности). Что делать? Про третий параметр описанный выше не понял

    • Сергей:

      Вот так должно быть =ЧИСТВНДОХ(B1:B31;A1:A31;0)

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

    Как-то странно эта функция считает доходность в случае неполного года.
    Например 1 января вложили 100 тыс. руб. Ровно через пол-года стало 200 тыс. ЧИСТВНДОХ показывает годовую доходность 305%! Хотя правильно было бы ожидать доходность 200%