Как правильно рассчитать доходность инвестиций в процентах годовых

У каждого начинающего инвестора рано или поздно возникает вопрос — а какова доходность моих акций (или других инвестиций) в % годовых? Как понять: обгоняет или проигрывает мой портфель банковскому вкладу или индексу?

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

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

Но представьте следующие ситуации:

Ситуация №1. Вы начали инвестировать в фондовый рынок 1 января 2019 года, вложив 1000 рублей. Ежемесячно вы докупали ценные бумаги на 1000 рублей, но в последний месяц года (декабрь 2019) вы купили акций на 50 000 рублей.

Ситуация №2. Вы начали инвестировать в фондовый рынок 1 января 2019 года, вложив 50000 рублей. Ежемесячно вы докупали ценные бумаги на 1000 рублей до конца года.

Ситуация №3. Вы начали инвестировать в фондовый рынок 1 января 2019 года, вложив 1000 рублей. Ежемесячно в течение года вы докупали ценные бумаги на 1000 рублей, но в середине года, а именно в июле 2020 года, вы вложили 50 000 рублей.

ИТОГО: Во всех 3-х случаях вы вложили в фондовый рынок 61 000 рублей. Через год ваши вложения выросли до 75 000 рублей. Посчитаем доходность по формуле: Общая стоимость портфеля / общая сумма вложений. Я свел все данные расчета в табличную форму для наглядности.

Как видим, во всех трёх случаях доходность портфеля за год составила 22,95% годовых, что, конечно же, неверно. Кстати, приложение от брокера тоже считает неправильно. Ниже я приведу верный расчет и вы все поймете.

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

Дата внесения суммы в 50 000 рублей в данных сценариях влияет на общую доходность, что логично. Если вы год назад внесли 50 000 рублей, а сегодня у вас 75 000, то вам понадобился целый год, чтобы получить прибыль в 15 000 рублей. А вот если вы получили 15 000 рублей всего за месяц, то доходность инвестиций гораздо выше, что и подтверждает таблица выше.

Для расчета доходности своего портфеля я использую связку из двух приложений: Yahoo Finance и Google Таблицы. Из первого можно легко экспортировать данные по всем сделкам, а во втором можно отслеживать доходность портфеля онлайн, подгружая котировки акций через функцию GOOGLEFINANCE. Лучшего решения для себя я пока что не нашел. Будем надеяться, что в будущем брокеры встроят в свои приложения правильный расчет доходности акций и не нужно будем изобретать велосипед. Итак, приступим к расчету.

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

Шаг №1. В Yahoo Finance выбираем свой портфель, в правом верхнем углу нажимаем «Export».

Шаг №2. На компьютер скачается файл «quotes.csv».

Шаг №3. Теперь нам нужно импортировать данные из скачанного файла «quotes.csv». Для этого открываем или создаем новый документ в Excel’е. Выбираем вкладку «Данные» ->> «Получение внешних данных» ->> «Из текста».

Шаг №4. На следующем экране ставим галочки напротив пунктов «с разделителями» и «мои данные содержат заголовки». Нажимаем «Далее».

Шаг №5. Здесь выбираем в качестве символа-разделителя «запятую». Жмем «Далее».

Шаг №6. Формат данных столбца — «дата», а именно формат «ГМД». Помимо этого обязательно нужно выбрать столбец с заголовком «Trade Date». Далее нажимаем на кнопку «Подробнее…».

Шаг №7. Во вкладке «Подробнее» меняем разделитель целой и дробной части: вместо запятой нужно выбрать «.» точку. Жмем «ОК» и «Готово».

Шаг №8. Теперь выбираем любую ячейку на листе для вставки таблицы. Нажимаем «ОК».

Шаг №9. Готово! Мы вставили наши данные из Yahoo Finance в Excel.

Теперь нам нужно удалить столбцы с ненужной нам информацией. Удаляем все кроме: Symbol, Current Price, Trade Date, Purchase Price и Quantity. Данные этих столбцов мы занесем в Google таблицу, которая рассчитает нам правильную доходность наших акций.

Для работы с Google таблицами вам понадобится Google аккаунт. Если у вас его ещё нет, то создайте его. Я сделал себе Google табличку, в которую заношу данные своего портфеля из Yahoo Finance. С помощью формулы «XIRR» (она же «ЧИСТВНДОХ» в Excel) я могу видеть корректную доходность своих акций. Вы также можете её использовать. Для этого открывайте мою Google таблицу, нажимайте «Файл» ->> «Создать копию». Теперь вы можете работать с таблицей и вносить свои данные по инвестициям.

Шаг №10. В таблице на листе «Портфель» вам необходимо внести свои данные (полученные в шаге №9) в соответствующие столбцы «Данные из Yahoo Finance». Если у вас не вмещаются данные по сделкам, то добавьте больше строк. Добавляйте где-нибудь в середине, чтобы формулы автоматически растянулись для вашего диапазона.

Шаг №11. Переходим на вкладку «Индекс». Я сравниваю свой портфель с индексом S&P500, поэтому мне необходимо подгрузить данные по котировкам индекса в те числа, в которые я приобретал акции. Для этого в первой ячейке (А1) нужно отредактировать формулу. Нужно изменить 2 даты. Первая — это дата вашей самой первой сделки в портфеле. Вторая — текущая дата, то есть сегодняшняя.

Шаг №12. Формула должна показать значения индекса S&P500 по дням в заданном промежутке времени. Теперь вам нужно скопировать столбец D и вставить только значения (!) в столбец G.

Шаг №13. Выделяем столбец G и задаем ему формат «Дата».

Шаг №14. Возвращаемся на вкладку «Портфель» и смотрим свой финансовый результат.

Как вы можете видеть, в момент написания этой статьи я имею доходность -59,83% годовых! Очень страшный результат, правда? Развал сделки ОПЕК+ и активно развивающийся коронавирус делают свое дело. Но на самом деле не все так печально, как могло показаться на первый взгляд. И вот почему:

Если в таблице вместо данных по индексу появятся ошибки, то переключитесь на вкладку «Индекс» и измените в формуле даты в первой ячейке (А1). Сам факт изменения дат исправляет ошибку. Если кто-то знает в чем причина — буду рад вашей помощи в комментариях.

Надеюсь эта статья окажется полезной для вас. Я инвестирую в американский фондовый рынок, поэтому в Google таблице я сравниваю свой результат с индексом S&P500. Причем, таблица не просто сравнивает ваш результат с ростом индекса за год. Она моделирует покупки самого индекса на те же суммы и в те же даты, в которые вы покупали акции. Таким образом, вы сможете понять что выгоднее: ваша личная стратегия или покупка индекса.

Если у вас акции российских компаний и вы хотите сравнить свой результат с индексом МосБиржи, то напишите в комментариях и я сделаю таблицу для российского рынка 😉

Вам интересна тема FIRE и инвестиций в фондовый рынок? Подписывайтесь на мой Telegram-канал, где я публикую свой собственный опыт. Буду рад найти единомышленников.