Google-таблица для учета и анализа инвестиционного портфеля
Google-таблица для учета и анализа инвестиционного портфеля.
Всем привет, друзья. Сегодня расскажу об одном из инструментов для ведения, учета и анализа своего инвестиционного портфеля. Данный инструмент подойдет для любителей вести учет самостоятельно и индивидуально подстраивать под себя отчетные формы и диаграммы.
Ценность моей разработанной таблицы заключается в том, что она максимально автоматизирована. Вам необходимо будет только вводить свои сделки!
Примерно вот так. При чем столбцы белого цвета считаются автоматически. А если вы совершаете сделку с ценной бумагой не в первые, то таблица также уже автоматически заполнит сектор, вид бумаги, валюту и категорию (категория зависит от капитализации
После заполнения сделок во вкладке «портфель» АВТОМАТИЧЕСКИ» сформируется ваш портфель:
В данный портфель автоматически подтягивается название акции, их количество, цена, общая стоимость, сектор прибыль (убыток) в % и в абсолюте и т.п.
Для наглядности таблица подсвечивает сектора разными цветами. Вы сможете установить желаемую долю от всего портфеля на одного эмитента. В моем случае 5%. И в случае ее превышения таблица будет вам это подсказывать подсвечивая долю красным цветом. Конечно же вы сможете увидеть результат вашей позиции (см. 3 последних столбца).
Для визуального анализа во вкладке «Аналитика» построятся различные графики и диаграммы.
Автоматическая визуальная аналитика
ВНИМАНИЕ: таблица НЕ распознает расписки иностранных акций, которые торгуются на Московской бирже с тикерами, заканчивающимися на «XR». например «AMX-RM». Но для меня это не проблема я просто покупаю эти акции листингованые на СПБ бирже в долларах.
Подробную инструкцию по использованию данной таблицы и условия ее получения вы можете прочитать здесь: https://docs.google.com/spreadsheets/d/12B_MXeyv-ZX_CvjOuMovRmV9RHBD5V3Y2oKRIwxeiGQ/edit?usp=sharing
В будущем у меня есть желания доработать таблицу, чтобы она учитывала облигации, считала доходность портфеля в % годовых, каждый день будет фиксировать стоимость портфеля и строить график и еще многое другое.
Как вести учет облигаций в Excel. Расчет доходности
Рада, что предыдущий пост про НКД понравился. По поводу всевозможных сторонних сервисов для учета ценных бумаг и анализа портфеля. Я, конечно, ничего не имею против. Лишь бы этот учет был. Я все же предпочитаю вести портфели сама, чтобы не зависеть особо от сторонних ресурсов и чтобы, это самое главное, видеть те характеристики портфелей, которые необходимы для принятия тактических и стратегических решений.
Ну, а сейчас, к расчету доходности.
Самая простая и понятная по смыслу доходность – это простая доходность к погашению. Рассмотрим все ту же облигацию Уральской стали.
Для расчета доходности нам потребуется график купонных выплат. Где его взять? Он есть, например, на Smart-lab. Есть он и на сайте биржи.
По бумаге предстоит получить еще 10 купонов по 26.43 руб. Итого 264.3 руб.
Где В3 – это ячейка с ISIN.
Сейчас цена облигации ниже номинала. Значит при погашении мы получим прибыль от прироста стоимости.
Теперь у нас есть все, чтобы рассчитать простую доходность к погашению.
Простая доходность к погашению, в отличие от эффективной доходности к погашению, не учитывает тот факт, что купоны можно реинвестировать.
Если рассматривать российский рынок, а он у нас очень «короткий» по мировым меркам, то простая доходность нам куда полезнее.
Но общепринятое правило, сравнить облигации, используя эффективную доходность к погашению. То есть с учетом возможного реинвестирования купона.
Не хотелось бы перегружать пост лишними формулами, но, одну использовать придется.
Для того, чтобы определить эффективную доходность к погашению, начинать надо с того, как формируется цена облигации. Цена облигации — это сумма всех денежных потоков, приведенных (дисконтированных) к текущему моменту.
C – размер купона в год (Номинал облигации * ставку купона)
n – количество купонных выплат в год
N – номинал облигации
T – количество периодов до погашения
t – текущий период
Значение r в этой формуле – эта та самая доходность к погашению, которая нам нужна. А цена у нас есть. Это рыночная стоимость.
Посчитаем эффективную доходность для Уральской стали. Поток платежей уже есть. Просто немного модифицируем его. Помним, что в конце вместе с купоном мы получим и номинал. А покупая облигацию сегодня, заплатим текущую стоимость и НКД (накопленный купонный доход).
Когда известны даты выплат купонов, идеальной функцией для поиска эффективной доходности к погашению является ЧИСВНДОХ.
Это функция привязана к датам и подходит для расчета внутренней ставки доходности, чем и является эффективная доходность.
В этой функции всего два аргумента: поток платежей по бумаге (С2: С12) и даты выплат (В2: В12). То, что мы платим вбиваем со знаком «минус».
И да, доходность к погашению можно посчитать только в том случае если известен поток платежей. А это постоянный и фиксированные купоны. При переменном купоне доходность считают к оферте. Если купон плавающий, то этот показатель не имеет смысла.
- спецраздел:
- облигации
- Ключевые слова:
- Облигации,
- доходности облигаций,
- расчет
Таблица для учета инвестиций
В первые месяцы инвестирования — начало 2020 года — я просматривал разные сервисы по учёту инвестиций. Некоторые нравились, некоторые — не очень. Одновременно с этим я вёл учёт расходов семьи. И заметил, что довольно много небольших расходов в разных категориях. Добавлять ещё одну категорию — оплата сервиса учёта — совсем не хотелось. Вроде копейки, но на эти «копейки» можно купить какую-нибудь акцию. А за год — 12 акций. Если каждый год такие 12 акций будут приносить прибыль, то почему я должен тратить эту сумму на сделанные кем-то таблички?!
Несмотря на то, что я придерживаюсь долгосрочных инвестиций, в табличку заглядываю несколько раз в день — просто так. Как минимум по утрам я вношу данные по доходности (об этом будет ниже), а в середине дня просто смотрю на цифры.
Разобью функции по вкладкам.
Портфель. У меня открыто два счёта у одного брокера — обычный брокерский и ИИС. Поэтому в первой вкладке два портфеля. Цены подтягиваются с сайта Мосбиржи, раньше пользовался функцией Googlefinance. В самом низу вкладки подсчитаны суммы по категориям эмитентов, чтобы понимать диверсифицированность суммарного портфеля. На момент написания статьи в лидерах категорий у меня «нефть и газ», «химия» и «энергетика». Стоимость активов подтягивается сама из формулы суммы с двух счетов. Я заполняю только среднюю цену входа, беру из приложения, и количество акций.
Во вкладке «Вложения 2021» я ежедневно обновляю данные по доходности портфеля, при вычислении её стандартной формулой «сумма вложений/текущую стоимость портфеля». По этим данным строится график.
«Составной портфель». Поскольку на двух моих счетах некоторые эмитенты дублируются, однажды я решил совместить их в одну вкладку. Здесь суммируется общая стоимость каждого эмитента, его доля в суммарном портфеле и сумма полученных дивидендов. Сюда же добавил столбец со значением, насколько процентов увеличились поступления от каждого эмитента по сравнению с прошлым годом. Понимаю, что каждый год они будут увеличиваться в любом случае, так как регулярно идет увеличение количества акций каждого из эмитентов. Посмотрим, может быть вскоре откажусь от этого столбца. Ниже в той же вкладке обозначена диверсификация активов по странам. У меня нет строгой стратегии, сколько процентов от портфеля должны составлять акции той или иной страны, но со временем более менее я пришёл к действующим цифрам.
«Дивиденды» — моя любимая вкладка. Очень приятно наблюдать за их увеличением, в том числе средним значением за последние 12 месяцев. На момент написания этого текста мой пассивный доход в среднем составляет более 3000 рублей в месяц. Даже подсчитана средняя сумма дивидендов за всё время инвестирования — 22 месяца, включая первые месяцы с полным отсутствием дивидендных выплат.
XIRR — более справедливый, на мой взгляд, способ оценки доходности. В данный момент он практически совпадает с данными доходности по формуле из вкладки «Портфель».
Идеи. Название говорит само за себя, можно и без пояснений.
Сравнение. По этой таблице я выбираю наиболее привлекательные акции, голосуя за них по разным критериям. Для иностранных акций данные нахожу на Finasquare, для российских — на Смартлабе. Наиболее привлекательному показателю ставлю единичку, наименее привлекательному — 0, иногда 0,5. И затем смотрю, какая компания набрала больше баллов.
План. После первого года инвестирования стало интересно, когда при текущих условиях я приду к сумме в 10 000 000 рублей. Тут можно играться с процентной ставкой и суммами ежегодных вложений.
Вложения (все). За неполные 2 года инвестиций были разные месяцы по вложениям. Поэтому здесь просто строится интересный график по сумме всех-всех-всех пополнений. И автоматически появляется линия тренда, как и в дивидендной вкладке, что позволяет оценить динамику вложений и среднюю сумму ежемесячных вложений за весь срок инвестирования.
В идеале было бы интересно из этой таблицы создать более презентабельный вид при помощи какого-нибудь Python или другого языка. Но поскольку таблица создавалась исключительно для себя, да и знаний у меня на это нет, она остаётся такой, какая есть в данный момент. Иногда я что-то в неё добавляю, иногда убираю. Посмотрим, как она будет выглядеть через несколько лет.
Портфель взвешенный по капитализации или в равных долях (часть 5) | Готовый шаблон для учета инвестиций
Сделал шаблон для учета инвестиций по стратегии равно взвешенного портфеля. Расскажу вкратце что умеет делать таблица.
Таблица состоит из нескольких блоков. Для удобства и наглядности блоки выделены разными цветами. Вот как это выглядит у меня на начальном этапе.
Начало — веса, котировки и названия
Перед началом пользования таблицей нужно указать сколько акций в портфеле вы хотите иметь. Это нужно для вычисления доли на одну акцию (5, 10 или 20%).
В первом блоке накидываем для себя список акций, который вы хотите иметь в портфеле. Для примера я добавил в файл 20 компаний из индекса Мосбиржи.
Котировки подтягиваются с биржи автоматически (прописана формула). Если будете менять бумагу на другую (или добавлять новые имена), в формуле нужно прописать новый тикер.
На примере формулы для Сбера. Тикер выделил красным. Его и нужно менять на другой.
= IMPORTXML ( «http://iss.moex.com/iss/engines/stock/markets/shares/securities/ SBER .xml» , «/document/data[@id=»»marketdata»»]/rows/row[@BOARDID=»»TQBR»»]/@MARKETPRICE» )
Твой портфель
Второй сектор показывает текущее состояние вашего портфеля. Сколько и каких акций куплено и на какую сумму. А также пропорции этих акций в портфеле.
Заполнять количество акций можно в колонке «Акций куплено«. Но бывает ситуации, что бумаги могут быть раскиданы по разным брокерам. И даже акции одного эмитента могут находиться по разным счетам. К примеру у меня так. Часть у одного брокер, часть у другого. Есть даже бумаги, лежащие у одного брокера, но по разным счетам (ИИС и обычный брокерский счет).
Это доставляет определенные неудобства при заполнении таблицы. Нужно постоянно складывать данные в уме. «у брокера А у меня лежит 100 акций Сбера, у брокера Б — еще 250. По брокеру В — сегодня купил 60 и было до этого на счете 40. Сколько итого нужно записать?» Или бывает случайно удалил данные по количеству акций, к примеру того же Сбера. Типа рука дрогнула и ты не заметил сразу (и не можешь сделать отмену действий). И что нужно сделать, чтобы восстановить данные? Пройтись по всем своим брокерам, посмотреть нет ли у них акций Сбера. А если удалил не одну, а несколько ячеек? У меня так было несколько раз. Приходилось не только восстанавливать, но делать сверку по всем брокерам — вдруг я что-то еще удалил случайно.
Второй минус — ты не видишь полной картины, какие бумаги и у какого брокера у тебя находятся.
Для подобных случаев я добавил разделение по брокерам. Заполняем количество по каждому брокеру отдельно, а потом все автоматом суммируется в отдельную колонку (акций куплено) При необходимости можно нажимать на «плюсик» (или «минус») и колонки с брокерами будут скрыты.
Помощь в ребалансировке
Для наглядности я сделал колонку «Расхождение весов«. Она показывает на сколько отклоняются текущие пропорции акций от первоначально заданных. В зависимости от цвета колонки инвестор понимает, что ему нужно сделать с акциями:
- красный цвет — вес акции в портфеле превышен. Нужно продать часть.
- зеленый цвет — доля акций меньше заданного. Нужно докупать.
Если портфель постоянно пополняется, то продавать необязательно. Можно выравнивать пропорции покупкой отстающих, доля которых на данный момент минимальна, а расхождение максимально (зеленый цвет).
Новые пополнения
В таблице можно заполнить поле «Сумма для инвестиций (кэш)» и система сама посчитает каких акций и в каком количестве нужно купить. Причем учитывается уже купленные акции.
По сути — это подсказка куда направить новые поступления денег. Даже думать не надо. ��
Дивиденды
Таблица выдергивает данные по дивидендам за 12 месяцев с сайта Доход (можете сравнить правильность). В итоге мы наглядно видим не только данные по каждой бумаге, но и сколько может денег приносить наш портфель в целом и какая у него дивидендная доходность. Можно поиграть с наименованием бумаг, чтобы увеличить дивидендную доходность портфеля.
Сектора
Необязательный столбец. Показывает к какому сектору относятся ваши акции. Я использую его для наглядности.
В шаблоне выводится две диаграммы — сколько веса занимает в вашем портфеле каждый сектор. Одна диаграмма показывает запланированный веса портфеля (бенчмарк). Вторая — реальные.
Во-первых, когда вы выбираете эмитентов в свой портфель, сразу видно распределение по секторам. Это помогает избежать сильного доминирования одного сектора в портфеле. К примеру, большинство крупных компаний на Мосбирже относятся к нефтегазовому сектору. И если вы захотите собрать портфель из 10 акций голубых фишек, то, скорее всего, больше половины веса будет приходиться на нефтегаз. А это с точки зрения диверсификации — не есть гуд. И желательно такой портфель разбавить акциями из других отраслей.
Этот же принцип работает и в обратную сторону. Мы можем посмотреть на шаблонный вариант портфеля и заметить, что нам не хватает какого-нибудь сектора (доля которого в портфеле презрительно мала или вообще отсутствует). Это будет нам сигнал к размышлению.
Во-вторых, на диаграмме по реальному распределению по секторам мы сразу можем увидеть сильно ли наш портфель «разъехался», по сравнению с шаблонным вариантом.
К примеру, глядя на диаграммы ниже, я сразу вижу, что доля сектора «Металл и добыча» у меня намного больше запланированного. А вот сектор «Нефтегаз» сильно отстает. Следовательно, мне нужно направлять в него все новые деньги в первую очередь. И пока не вкладываться в Металлы.
Файл-шаблон
Файл для учета равно взвешенного портфеля доступен по ссылке. Напоминаю, чтобы утащить к себе, нужно в меню «Файл» выбрать «Создать копию». В этом случае вам будет доступно редактирование документа.
Буду рад услышать обратную связь.
- Благодарность автору — здесь. ��
- Есть вопросы? �� Задайте их в комментариях. Все читаю, по возможности отвечаю! ��
- Чтобы не пропускать новые статьи, подпишись на мой �� Telegram-канал �� или VK