Расчет результативности инвестиций в EXCEL

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

При учете результатов инвестиций почти всегда необходимо быть уверенным, что на длинных сроках доходность инвестиционного портфеля выше инфляции. Второй важный элемент — это сравнение доходности с «безрисковыми» инструментами. Инвестор, вкладывая деньги в ценные бумаги, берет на себя дополнительные риски. Подразумевается, что вместе с дополнительными рисками он получает возможность более высокой доходности. Если доходность инвестиций (мы всегда говорим о длинных сроках) ниже, скажем, средней ставки депозита, то зачем брать на себя дополнительные риски?

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

Как считать доходность?

Почему большинство инвесторов часто имеют неправильное представление о том, какова настоящая результативность их инвестиций.

Сложность заключается в том, что большинство подходов к расчету доходности подразумевают простую формулу:

$$ R =\frac{ A }{ B }$$  

А – полученный доход

В – стартовые инвестиции

Представим себе жизненную ситуацию, когда человек в январе инвестировал 10 000 р, а в декабре – 90 000 р. К концу года на инвестиционном счете оказалось 110 000 р (ценные бумаги выросли в цене). Какова доходность инвестиций? Что на что делить? Если мы возьмем доход в 10 000 р и разделим на сумму всех инвестиций – 100 000 р, то получим очень сложно интерпретируемый результат – 10%. Ведь большую часть срока на счете находилось всего 10 000 р, а остаток добавлен только за месяц до конца года …

Или еще более интересный пример. В январе инвестор положил на брокерский счет 100 000 р, а в декабре забрал с него 90 000 р. К концу года на брокерском счете фигурировала сумма 15 000 р. Если просто сложить пополнения и изъятия получится что суммарная инвестиция равна 100 000 – 90 000 = 10 000 р. Разделив доход на суммарные инвестиции, получим слишком оптимистичные 50%. Очевидно, что так делать нельзя …

Более подробно о теме расчетов доходности без пополнений и изъятий читайте в статье: Правильный расчет среднегодовой доходности в инвестициях

IRR или Внутренняя норма доходности (ВНД)

Одним из самых простых и распространенных способов измерить результативность инвестиций является расчет IRR (Internal Rate of Return, Внутренняя норма доходности). IRR – это не совсем доходность. Формально IRR или Внутренняя норма доходности (ВНД) – это  процентная ставка, при которой приведённая стоимость денежных поступлений (списаний) равна размеру исходных инвестиций. IRR очень распространен в бизнесе и финансах. При помощи этой величины считается, например, рентабельность проектов в бизнесе. Аналогично считается доходности к погашению для облигаций. IRR можно считать это своего рода стандартом при измерении результативности.

Еще одно важное преимущество – IRR легко считается в EXCEL и других электронных таблицах. 

Если IRR меньше ставки по депозитам в Сбербанке, то надо задуматься, все ли нормально с инвестиционной стратегией.

Шаблон для расчета IRR инвестиций в EXCEL

Для быстрого расчета результативности инвестиций предлагаем простой шаблон в EXCEL.

Расчет доходности инвестиций в EXCEL через IRR

Шаблон считает IRR для каждого из периодов инвестиций, и за последние 6 периодов (колонка «IRR за 6 периодов»). Периоды могут быть произвольными: один месяц, один год. Более того, в калькуляторе используется функция XIRR (ЧИСТВНДОХ), которая умеет считать IRR даже для неравных между собой периодов. Это значит, что в колонке «Дата» можно указывать любую дату, а не только начало месяца или, например, конец года. Удобнее всего вносить новые данные каждый раз, когда пополняется портфель или когда происходит изъятие средств. Для интереса можно вносить новые данные чаще, даже когда нет пополнений портфеля. Например можно указывать даты, когда в размере портфеля происходят какие-то значимые изменения или просто с некоторой заданной регулярностью.

Кроме IRR инвестиционного портфеля в шаблоне можно посмотреть общий прирост портфеля (на сколько размер портфеля отличается от объема инвестированных средств).

Учет результатов инвестиций для сложных портфелей

Важное свойство калькулятора – это возможность измерения результативности инвестиций для широко диверсифицированных портфелей. Часто встречаются ситуации, когда у инвестора несколько брокерских счетов (российский и зарубежный), часть денег размещено в ПИФах через Управляющую компании. Кроме всего, может быть открыт ОМС (Обезличенный металлические счета – используются для покупки драгоценных металлов), куплена недвижимость и тому подобное. В таком случае рассчитать результат инвестиций для итогового портфеля бывает довольно проблематично…  Предлагаемый калькулятор поможет справиться с этой задачей. Достаточно регулярно (например, один раз в год) считать суммарный размер всех активов в портфеле и вносить в таблицу пополнения и изъятия.

Расчет доходности к погашению для облигаций

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

Для вычисления доходности к погашению необходимо внести сумму покупки облигации и планируемые поступления в виде дивидендов.

Расчет доходности к погашению облигации в EXCEL

В примере показан прогноз доходности к погашению для облигации с купоном 40 руб (два раза в год) и текущей стоимостью 98% (980 р) и погашением в 2024 году. Предполагается, что облигация держится до погашения. В данном случае имеет релевантность только последнее значение IRR (в момент погашения), так как изменение цены облигации прогнозировать очень сложно. IRR за 6 периодов тоже большого смысла для облигаций не имеет.

Ограничения калькулятора

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

Другие финансовые калькуляторы для EXCEL можно найти разделе Калькуляторы.

Файлы для скачивания

Калькулятор результативности инвестиций в EXCEL

Размер: 48684 байт


Для скачивания файлов необходимо зарегистрироваться или авторизоваться


Комментарии

  1. Айдар 21 ноября 2019, 10:02 # 0
    Для формулы «R=A/B» нужно поменять местами определения для «A» и «B», и поправить текст идущий ниже.
    1. Сергей 21 ноября 2019, 13:17 # 0
      Айдар, спасибо. Поправил…
    2. Вячеслав 25 ноября 2019, 16:30 # 0
      Подскажите, почему внизу -1820 обозначено красным цветом? Каково значение этого?
      1. Сергей 26 ноября 2019, 13:25 # 0
        Эта ячейка используется для расчета IRR. Поэтому пометили ее красным и написали справа соответствующий комментарий. Просто, чтобы не стерли и не перезаписали.
      2. Константин 01 мая 2020, 10:54 # 0
        Сергей, я не совсем понял, как в калькуляторе я могу увидеть годовую доходность своих инвестиций? IRR показывает только накопленную доходность? Если я инвестирую, например, только 3 мес., где мне посмотреть годовую доходность?
        1. Сергей 03 мая 2020, 06:44 # 0
          Константин, IRR никогда не показывает накопленную доходность. Эта функция всегда дает доходность в промежуток времени. В этом году используется модификация функции XIRR, которая всегда считает доходность в годовых значениях.
          1. Васлилий 11 мая 2020, 03:01(Комментарий был изменён) # 0
            Сергей, подскажите, а в столбце «Пополнения/снятия» нужно вводить суммы, приходящие на счет ввиде дивидентов, и снимаемые со счета ввиде комиссий брокера?
            1. Сергей 11 мая 2020, 14:27 # 0
              Василий, ни дивиденды, ни комиссии в пополнениях/снятиях указывать не нужно. Достаточно просто корректировать размер портфеля в колонке С.
              Вносить нужно только свои снятия и пополнения.
        2. Елена 12 мая 2020, 16:36 # 0
          Я запуталась. Если у меня ИИС и я получаю деньги за купоны и дивиденды на расчетный счет, потом вношу их обратно на ИИС, это деньги надо учитывать как поступления? Или только те, которые без участия дивов и купонов вносишь?
          Например сама лично внесла 100 тыс, а если считать с дивами, то внесла на ИИС 120 тыс. Какую сумму ставить должна?
          И второй вопрос, сумма/размер портфеля это что? Наличка на ИИС+портфель на сегодняшний день? Или без налички?
          1. Сергей 12 мая 2020, 20:29 # 0
            Ваш брокерский счет можно представить себе как «черный ящик», внутрь которого поступают деньги, и из которого вы забираете иногда деньги. То, что происходит внутри «черного ящика» не имеет значения для калькулятора. Важно: сколько поступило внутрь денег, сколько взяли от туда и какова общая стоимость купленного внутри.
            Поэтому… если вы берете деньги со счета в какой-то форме (дивиденды или купоны) и вносите их сразу обратно в том же самом объеме, то эту операцию можно не заносить в калькулятор.
            Не знаю, понятно ли объяснил…
          2. Елена 13 мая 2020, 18:28 # 0
            Спасибо, все поняла. Уложилось в голове.
            1. Сергей 29 мая 2020, 16:15 # 0
              Почему расчет по вашему калькулятору не совпадает с доходностью к погашению, которую я вижу в Квике (транслируется биржей)? В чем ошибка расчета?
              Облигация: RU000A1012R9 Сбербанк ПАО 001Р-SBER14
              Беру стоимость на 29.05.2020 — 101.49, НКД = 1.75
              Купон = 31.91.
              Даты: 20.11.2020, 21.05.2021, 19.11.2021 (последний купон и погашение).

              Квик показывает доходность 5.41%. По вашему файлу получается 5.37% (комиссию и НДФЛ поставил в 0).

              1. Сергей 29 мая 2020, 17:46 # 0
                Довольно трудно ответить на этот вопрос по двум причинам:
                — не известно, как считает QUIK
                — не известно, как считали вы :)
                В целом этот калькулятор не предназначен именно для расчета доходности к погашению. Для этого есть гораздо более подробный шаблон:
                Расчет доходности к погашению для облигаций в EXCEL
                1. Сергей 29 мая 2020, 17:51 # 0
                  Ошибся с темой — не там разместил пост.
                  Я считал именно по вашему файлу «Расчет доходности к погашению для облигаций».
                  Квик транслирует ММВБ.
                  Получается, что расчет по вашему файлу не совпадает с расчетом ММВБ. Почему?
                  1. Сергей 31 мая 2020, 11:12 # 0
                    Сергей, в любом случае ответить на ваш вопрос невозможно, не зная методику расчета доходности к погашению у Мосбиржи. Наши расчеты вы можете посмотреть — они открыты и прописаны в формулах калькулятора. По сути — это расчет IRR методами EXCEL.
                    В каждом случае надо разбираться отдельно. Возможно, вы что-то не так указали при заполнении калькулятора. Если это важно, пришлите свой заполненный шаблон на info@rostsber.ru
                    Посмотрим, все ли в порядке.
              2. Ростислав 15 августа 2020, 00:33 # 0
                Добрый день!
                Спасибо за отличный калькулятор, осваиваю! НО! Осваивать мешает одно странное обстоятельство: после того, как я указываю свои собственные даты, все формулы расчета ВНД сразу уходят в ошибку #VALUE. И дело явно в ячейках с датами. Не могу понять, в чем дело. Подскажите, пожалуйста.
                1. Сергей 16 августа 2020, 08:16 # 0
                  Ростислав, такого не должно происходить. Скорее всего, ошиблись где-то с форматом ввода данных. Например, вместо точки поставили дефис в дате: 20-12-2019.
                  Проще всего заново загрузить с сайта калькулятор и попробовать изменить одну дату. Убедитесь, что всё работает. Потом заново ввести все свои даты и цифры.
                2. Анатолий 11 октября 2020, 13:56(Комментарий был изменён) # 0
                  Добрый день. Я открыл это в microsoft ofis excel, мне открывается график, но с пометкой: «Удаленный компонент: часть /xl/comments1.xml с ошибкой XML. (Примечания) Ошибка загрузки. Строка 2, столбец 459.»
                  Что это значит? Я так-же открыл вашу таблицу в новом Excel, там всё открылось, но этот новый Excel пишет, что моя версия ограничена и предлагает её активировать за 79 $.! Подскажите, в чём проблема у меня и как мне быть.? Спасибо…
                  1. Сергей 11 октября 2020, 16:39 # 0
                    Могу только предположить, что некоторые из функций не поддерживаются в старых версиях EXCEL.
                  2. Роман 04 декабря 2020, 10:52 # 0
                    Добрый день!
                    Калькулятор показывает IRR 33%, все остальные цифры (Инвестировано, Размер портфеля, Доход %) совпадают с тинькофф инвестиции.
                    Не слишком большая цифра при доходе 6,8%?

                    1. Сергей 08 декабря 2020, 13:36 # 0
                      Роман, дело в том, что IRR — это доходность, взвешенная по деньгам. Если вы в начале года вложили 1000 рублей и 11 месяцев получали доходность 10%, а в конце года добавили к портфелю 100 000 руб и на них получили доход 20% за последний месяц года, то IRR покажет доходность за 12 месяц более 400%… Потому что за декабрь была доходность 20% (эквивалентно почти 800% годовых) и приходилась она на крупную сумму денег.
                      Обычно управляющие компании считают не взвешенную по деньгам доходность а доходность, взвешенную по времени. В это случае предполагается, что промежуточных инвестиций не было. Как считает Тинькофф в инвестициях, я не знаю. Но скорее всего, причина расхождения в этом.
                      1. Роман 09 декабря 2020, 08:33 # 0
                        Спасибо, Сергей
                        Понаблюдаю

                    Оставьте комментарий