Урок 8. Считаем доходность своих вложений

Чтобы пройти этот урок, вам понадобится 20—25 минут.

Мы предлагаем вам пройти этот урок, даже если вы пока не инвестируете. Вы увидите, что можно контролировать свои инвестиции и держать все под рукой, — и, возможно, вам будет проще попробовать.

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

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

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

Разберем на примере. Вы вложили 100 000 Р и через год получили 110 000 Р — чистый доход составил 10 000 Р. Этих данных уже достаточно, чтобы посчитать доходность:

Доходность = Доход / Вложенные средства × 100%

В нашем примере доходность составляет 10%: 10 000 Р / 100 000 Р × 100%.

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

К примеру, вы вложили 100 000 Р и за 6 месяцев заработали 2500 Р. Считаем доходность за полгода:

2500 Р / 100 000 Р × 100% = 2,5%

Мы знаем доходность за полгода, а хотим получить годовую — умножаем на 2:

2,5% × 2 = 5% годовых

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

Итак, если бы мы просто увидели, что банк предлагает 5% годовых, и прикинули на глазок, получилось бы 1 050 000 Р: мы бы просто прибавили 5% к нашему миллиону. Но в реальности денег будет чуть больше.

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

К примеру, вы хотите открыть вклад на два года и рассматриваете предложения двух банков. Один банк предлагает вклад под 5% годовых, но выплата процентов — только в конце срока, без капитализации. А другой банк предлагает 4,8% годовых, но с капитализацией — то есть проценты будут выплачивать каждый месяц и на них тоже будут начислять проценты.

С первого взгляда может показаться, что первый вклад выгоднее, но в реальности все не так. Посчитаем, сколько вы заработаете на предложении каждого банка, если вложите 1 000 000 Р.

Хоть ставка по второму вкладу и была ниже, доходность получилась выше: 100 548 Р чистого дохода делим на вложенные 1 000 000 Р — получаем 10,05% за два года, или 5,025% годовых.

В результате вклад, ставка по которому меньше, принесет больше денег — пусть и всего на 548 Р. Но вы, как истинный Человек Т⁠—⁠Ж, должны понимать, что копейка рубль бережет.

Как считать доходность с пополнениями

Самое интересное начинается, если вы инвестируете. В итоге легко может получиться что-нибудь такое:

• 01.04.2020 вложили 40 000 Р и купили какие-нибудь ценные бумаги;
• 01.06.2020 вложили еще 60 000 Р и снова что-то купили;
• 01.09.2020 продали часть бумаг и сняли 30 000 Р;
• 01.04.2021 остаток на счете составил, например, 79 000 Р — 70 000 Р своих и еще 9000 Р дохода по инвестициям.

Устроена функция так: она анализирует, в какие даты и сколько денег вы внесли или вывели, и считает, какая годовая доходность у вас получилась. Потом можно сравнить эту доходность, например, с предложениями по банковским вкладам — и оценить, стоила ли игра свеч. Прелесть в том, что нам не нужно записывать котировки каждой ценной бумаги и считать доходность по ним отдельно. Доходность каждой отдельной бумаги можно посмотреть и у брокера. А вот общий результат каждый брокер считает по-разному — полезно привести данные к одному знаменателю в процентах годовых и сравнить с другими инструментами, например с теми же вкладами.

Рассмотрим на примере: возьмем сценарий, о котором писали выше, и занесем его в таблицу. Если мы вносили деньги на счет, указываем с минусом, если выводили — с плюсом. Финальный баланс — тоже с плюсом:

Формула в нашем случае будет выглядеть так: =XIRR(B1:B4;A1:A4). Разберемся:

• B1:B4 — наши денежные потоки. То есть диапазон, в котором мы указывали снятия и пополнения;
• A1:A4 — даты, когда происходили соответствующие действия. Диапазоны с потоками и датами должны быть одинаковыми по размеру — в нашем случае и в том и в другом по четыре ячейки.

Результат — 12,42%. Интерпретировать его можно так: если бы вы открыли вклад и таким же образом пополняли его и частично снимали деньги, для такого же финансового результата понадобилась бы ставка 12,42% годовых. Если у вас есть возможность открыть вклад под 12,42% или выше, то проще не инвестировать, а открыть вклад. Но вряд ли можно найти такую ставку.

Посчитать доходность таким образом можно за любой период — хоть за полгода, хоть за 10 лет. XIRR() пересчитает результат в проценты годовых — так его можно будет сравнить с вкладами или другими инструментами.

Запомним

1. Простой способ посчитать доходность — поделить полученный доход на вложенную сумму.
2. Даже если ставка по одному вкладу меньше, чем по другому, его реальная доходность может быть выше за счет капитализации. Поэтому лучше проверить доходность формулой.
3. Чтобы посчитать доходность инвестиций или вклада с учетом пополнений и снятий, используйте функцию XIRR() — она выведет результат в процентах годовых, его можно будет сравнить с доходностью других инструментов.
4. Когда вы рассчитываете доходность с помощью функции XIRR(), в таблице следует указывать с минусом суммы, которые вы тратите на покупку ценных бумаг или просто кладете на вклад, и положительное значение, если вы выводите деньги.

Как автоматически подтягивать курсы валют и котировки акций

Эта часть урока — о функциях, которые есть только в «Гугл-таблицах». В «Экселе» их, к сожалению, нет, но они слишком полезны, чтобы о них молчать.

Подтянуть курс валюты. Допустим, вы ведете в таблице учет всех ваших денег. И у вас есть вклад в евро. Чтобы посчитать, сколько он сейчас стоит в рублях, можно найти в интернете текущий курс евро, обновить его в таблице и перемножить — и делать так каждый раз, когда вы заходите в таблицу. А еще можно одной формулой подтягивать в гугл-таблицу курсы любых интересующих вас валют с сайта Центробанка:

=IMPORTXML("http://www.cbr.ru/scripts/XML_daily.asp"; "//ValCurs/Valute[CharCode=""EUR""]/Value")

Эта формула показывает текущий курс. Чтобы подтянуть курс доллара, а не евро, в формуле нужно заменить EUR на USD. Можно подтягивать и другие валюты — достаточно знать их обозначение. Список есть, например, в Википедии.

Предположим, вам нужен курс на определенную дату, а не текущий: например, у вас накоплено 500 000 Р и вы хотите сравнить, сколько это в долларах сейчас и сколько было полгода назад. Может, выгоднее держать накопления в валюте, чтобы не обесценивались?

Можно модернизировать ту же формулу и запросить у Центробанка курс на определенную дату. Выглядеть это будет так:

=IMPORTXML("http://www.cbr.ru/scripts/XML_daily.asp?date_req=01.05.2020"; "//ValCurs/Valute[CharCode=""USD""]/Value")

Тут у нас добавилась переменная с датой — она выделена желтым. Дата вводится в формате ДД.ММ.ГГГГ, менять ее можно прямо в формуле — курс обновится автоматически.

Подтянуть текущие котировки активов. Предположим, у вас есть инвестиционный портфель: вы записывали, какие акции купили, когда и по какой цене. Пример такой таблицы — на листе «Портфель» вашей рабочей тетради:

Можно в автоматическом режиме подтянуть текущую стоимость каждого актива, зная его тикер — условное обозначение на бирже. Мы указали тикеры в столбце B.

Понадобится функция GOOGLEFINANCE() — она автоматически подтягивает стоимость активов из сервисов Гугла. Чтобы получить актуальную цену актива, достаточно написать формулу такого вида:

=GOOGLEFINANCE(B2)

Здесь B2 — адрес ячейки, в которой указан тикер. В нашем случае — ADBE, компания Adobe.

Добавьте формулу в столбец H и протяните на все строки:

Теперь мы можем посчитать, сколько наши акции стоят сейчас. Достаточно умножить текущую цену на количество акций в портфеле:

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

К сожалению, с помощью GOOGLEFINANCE() подтягиваются не все котировки — если у вас не получается найти свой актив, почитайте нашу статью про альтернативные методы.

Как посмотреть котировки на определенную дату. С помощью GOOGLEFINANCE() можно подтягивать не только текущие котировки — как и в случае с валютой. Чтобы посмотреть котировки на определенную дату, понадобится еще несколько действий. Предположим, нам нужна стоимость акций Apple (AAPL) на 1 мая 2020 года:

Подтянем котировки на эту дату — формула в ячейке D2 будет выглядеть так:

=GOOGLEFINANCE(B2;"price";C2)

Вот что есть что:

• B2 — тикер акции, которую мы хотим подтянуть;
• "price" — параметр, который будем подтягивать. В нашем случае — цена. Этот параметр можно не указывать, если мы хотим подтянуть актуальную котировку: по умолчанию подтягивается именно цена. А вот если нужна котировка на определенную дату, придется указать;
• C2 — дата, на которую нужна цена.

В результате появится таблица из четырех ячеек:

Функция GOOGLEFINANCE() выдала таблицу два на два — она расположилась в диапазоне D2:E3. Из нее нам нужна только одна ячейка, с ценой, — E3. Но мы не можем оставить только ее, а остальные удалить, потому что все эти четыре ячейки — результат действия одной формулы и будут прописаны вместе.

Обернем нашу GOOGLEFINANCE() в функцию INDEX() вот с такими параметрами:

=INDEX(GOOGLEFINANCE(B2;"price";C2);2;2)

Разберемся, что есть что. Тут три параметра через точку с запятой:

• GOOGLEFINANCE(B2;"price";C2) — наша исходная формула, она выступает первым параметром, который указывает функции INDEX(), какой диапазон нужно анализировать;
• 2 — номер строки внутри этого диапазона, которую нужно вытянуть. Обратите внимание, что указывается номер строки не относительно всей нашей таблицы, а относительно выбранного диапазона. Если у нас диапазон две на две ячейки и нам нужна вторая по счету, укажем 2;
• 2 — номер столбца, аналогично.

В результате формула выдаст только стоимость акции на конкретную дату:

Запомним

1. С помощью функции IMPORTXML() вы можете не только подтянуть актуальный курс валют, но и посмотреть курс на любой прошедший день. Для этого в формуле указывается конкретная дата.
2. одтянуть актуальные котировки активов можно с помощью функции GOOGLEFINANCE(). Чтобы узнать стоимость активов в конкретный день, тоже нужно указать дату.

Функции, которые вы освоили

1. XIRR() считает доходность ваших активов.
2. IMPORTXML() подтягивает данные с внешних сайтов, в нашем случае — актуальный курс валют Центробанка.
3. GOOGLEFINANCE() подтягивает стоимость активов из сервисов Гугла.
4. INDEX() выбирает в диапазоне ячейку, которую нужно показать, скрывая остальные.