Некоторое время назад я уже писала о том как бывает полезно вести журнал инвестиции. Тогда в комментариях меня попросили показать мой журнал и я решила, что стоит отдельно рассказать о том, как можно устроить учет своих ценных бумаг. Сразу оговорюсь, в этой статье я покажу только один из 4 моих брокерских счетов, который я открыла совсем недавно. Я использую его в основном для операций с американскими акциями, которые выплачивают дивиденды. Такое распределение появилось, поскольку мой основной брокер не позволяет подписать форму W-8BEN и уменьшить налог на дивиденды. Но у этого брокера большая комиссия за сделку, поэтому я провожу через него не все свои иностранные ценные бумаги. Поэтому по этому отдельному счету не будет видна диверсификация. Также по этому счету на самом деле у меня нет операций продажи, я вписала одну только чтобы показать как работает функционал. А самое приятное, что я открыла счет по акции и первый месяц у меня не было комиссий за операции с ценными бумагами.
Структура документа
У меня в моем экселе есть 5 листов, на которых распределена вся информация, которая меня интересует:
- Операции покупки и продажи;
- Пополнения счета;
- Баланс счета на некоторые даты;
- Справочник по акциям;
- Сводные данные.
Лист "Справочник"
Этот лист содержит техническую информацию, которую надо вбить один раз. В дальнейшем данные из этого листа будут подставляться в мою основную таблицу. На данном листе у меня указаны:
- тип ценной бумаги (акция, облигация, фонд);
- валюта за которую приобретается актив (это поле необходимо для того, чтобы эксель понимал переводить цену бумаги в рубли или нет и для того, чтобы считать процентное соотношение рублевых и долларовых активов в портфеле);
- сегмент в котором работает эмитент, чтобы оценивать диверсификацию.
Таблица "Операции"
Это моя основная таблица, где и сосредоточена основная масса информации. Она может показаться переизбыточной, но я так построила свой учет. Буду рада, если кто-то в комментариях предложит мне что-то альтернативное.
Графы валюта, тип и сегмент заполняются автоматически. В них подставлена функция ВПР, которая находит в справочнике заданное значение - наименование эмитента и выдает наименование из заданного столбца справочника. Функция эта выглядит вот так ВПР(A3;Справочник!$A$2:D10;3).
Для тех, кто-то не знает, если задать формулу для ячейки F1, затем потянуть ее вниз, она перенесется на F2, F3 и т.д. При этом если формула ссылалась на ячейку A1, ссылка перейдет на A2, A3 и т.д. Это очень удобно. Записываешь формулу для одной ячейки и копируешь для следующих.
Переоценка цены операции в рубли производится тоже автоматически по следующей формуле =ЕСЛИ(F3="RUB";C3;C3*$T$15). Для этого в ячейке T15 указан сегодняшний курс валюты.
Таблица "Пополнения"
Сразу оговорюсь, что таблица "Пополнения" выглядит немного странно. Я участвовала в акции Тинькофф "Подарок за обучение". Подробнее о ней я уже писала тут. Операции по продаже подарков от брокера выглядят как пополнения.
Для расчета настоящей доходности с учетом того, что не все денежные средства были положены на счет единовременно необходимо рассчитывать среднюю сумму вложений. Рассчитать ее можно по следующей формуле:
Собственно ее я и рассчитала в колонке Е по формуле D3*B3/(СЕГОДНЯ()-$A$2) .
Количество дней на счете рассчитывается автоматически по формуле СЕГОДНЯ()-A2 . Формула СЕГОДНЯ() вставляет сегодняшнюю дату. Если в экселе из даты вычесть даты, он посчитает количество дней между датами. Если в коронке появится белиберда, надо поменять тип ячейки на "Числовой".
Значения в столбце F - это сумма нарастающим итогом:
F7=F6+E7
F8=F7+E8
и так далее.
Таблица "Баланс"
Таблица используется чтобы сохранять данные себе на память. Баланс счета я вписываю с экрана мобильного приложения брокера, среднюю сумму инвестиций подставляю сюда сама с листа "Пополнения". Таблица "Баланс" выглядит следующим образом.
Доходность рассчитывается по формуле D2*100/F2 . После того как изменится текущая дата или курс доллара, значения доходности в таблице пересчитаются. Если нужна динамика, доходность можно скопировать в соседний столбец.
Таблица "Сводка"
И наконец последняя таблица, та, на которой сосредоточена самая интересная информация.
Расчет диверсификации по отрасли производится по формуле СУММЕСЛИ(Операции!I:I;B8;Операции!G:G)-СУММЕСЛИ(Операции!I:I;B8;Операции!L2:L72) . Тут я вычитаю из того, что было куплено, то, что было продано. То что было куплено рассчитывается почти также как то, что было продано, с использованием формулы СУММЕСЛИ.
Точно таким же способом можно рассчитать диверсификацию по классам активов и валюте. Не буду перегружать текст картинками, их тут и так получилось очень много.
По моим другим счетам мне очень важно видеть зафиксированный доход, ведь пока акция просто растет в цене, это еще не доход. Доход получается тогда, когда сделка совершена и ценная бумага продана. Этот показатель позволяет мне оценить эффективность. Зафиксированный доход я суммирую по простой формуле =СУММ(Операции!Q:Q)
В этом тексте я попробовала нарисовать в общих чертах как можно устроить учет своих операций с ценными бумагами. Если расписывать каждую функцию эксель и ее аргументы, то получится слишком большой лонгрид. Если возникнут вопросы, пишите, с удовольствием отвечу.
Журнал инвестиций - это очень интересный документ, который иногда позволяет взглянуть на свою стратегию по-новому.
Приглашаю вас подписываться на мой телеграмм-канал.
