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

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

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

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

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

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

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

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

Поделиться в соцсетях
Top-100 блогов инвесторов, 
трейдеров и аналитиков

Алексей Мартынов

Частный инвестор, независимый финансовый советник.

You may also like...

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

  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. Александр:

    😉

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

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