Таблица для расчета прибыли акций
Таблица для расчета дивидендов по акциям российских компаний
Я хотел бы поделиться готовым для использования Excel-шаблоном, который позволяет сделать прогноз предстоящих дивидендов по акциям российских компаний.
Шаблон будет полезен инвесторам, ориентированным на дивидендную стратегию и долгосрочные инвестиции. Работает только на Windows. В качестве источника прогнозов используется информация с сайта Dohod.ru.
Примечание: стоит сразу обратить внимание, что шаблон показывает именно прогноз и не является гарантией того, что дивиденды будут выплачены в принципе или именно в том объеме, который был спрогнозирован. Тем не менее, эта информация очень полезна при планировании инвестиций на горизонте до года. Более подробно с методикой расчета прогноза вы можете ознакомиться тут
У меня есть несколько брокерских счетов. При покупке акций я в первую очередь руководствуюсь тем, насколько хорошо компания развивается, как стабильно и какие дивиденды платит, увеличиваются ли они от года к году. На текущий момент в портфеле есть акции порядка 35 российских компаний. Так же есть акции американских компаний, но в силу разрозненности источников данных в этом тексте речь пройдет только про то, как работать с российскими.
Поработав с брокерами ВТБ и Тинькофф, я не нашел удобного способа посмотреть общий прогноз по дивидендам по всему портфелю на горизонте до года. Можно посмотреть только уже утвержденные дивиденды по конкретной компании или по акциям в рамках одного брокерского счета. Возможности увидеть прогноз по всему портфелю — то есть по всем акциям на нескольких брокерских счетах — и спрогнозировать, какие дивиденды могут быть еще до того, как их утвердили, к сожалению, нет.
Прогнозы нашлись на сайте Dohod.ru, но каждый раз ручками считать дивиденды по акциям 35 компании совсем не хотелось. На помощь пришел Excel и встроенный в него функционал Power Query. В итоге получился довольно простой в использовании и очень полезный инструмент, позволяющий быстро получить нужный результат.
Документ состоит из трех листов: «Портфель», «Прогноз дивидендов» и «Компании РФ». На листе «Портфель» необходимо растянуть табличку на нужное количество Компании, выбрать сами компании из списка и задать общее число акций этой компании на всех ваших счетах.
Значение в поле «Тикер» редактировать не нужно, оно подтягивается автоматически с листа «Компании РФ». После заполнения данных по портфелю достаточно перейти на вкладку «Прогноз дивидендов», далее открыть вкладку «Данные» в меню Excel и нажать «Обновить все». Буквально за несколько секунд Excel в фоновом режиме подгрузит прогноз по вашему портфелю и обновит данные в таблице «ПрогнозДивидендов».
Долгосрочная цель моих инвестиций — это выход на некий уровень пассивного дохода с дивидендов, который бы меня устраивал. Наличие такого инструмента позволяет прогнозировать сумму дивидендов на год вперед и использовать прогноз для ребалансировки портфеля и новых покупок.
Эксельки. Здесь хвалятся своими наработками в «Гугл-таблицах»
Таблица для учета инвестиций
В первые месяцы инвестирования — начало 2020 года — я просматривал разные сервисы по учёту инвестиций. Некоторые нравились, некоторые — не очень. Одновременно с этим я вёл учёт расходов семьи. И заметил, что довольно много небольших расходов в разных категориях. Добавлять ещё одну категорию — оплата сервиса учёта — совсем не хотелось. Вроде копейки, но на эти «копейки» можно купить какую-нибудь акцию. А за год — 12 акций. Если каждый год такие 12 акций будут приносить прибыль, то почему я должен тратить эту сумму на сделанные кем-то таблички?!
Несмотря на то, что я придерживаюсь долгосрочных инвестиций, в табличку заглядываю несколько раз в день — просто так. Как минимум по утрам я вношу данные по доходности (об этом будет ниже), а в середине дня просто смотрю на цифры.
Разобью функции по вкладкам.
Портфель. У меня открыто два счёта у одного брокера — обычный брокерский и ИИС. Поэтому в первой вкладке два портфеля. Цены подтягиваются с сайта Мосбиржи, раньше пользовался функцией Googlefinance. В самом низу вкладки подсчитаны суммы по категориям эмитентов, чтобы понимать диверсифицированность суммарного портфеля. На момент написания статьи в лидерах категорий у меня «нефть и газ», «химия» и «энергетика». Стоимость активов подтягивается сама из формулы суммы с двух счетов. Я заполняю только среднюю цену входа, беру из приложения, и количество акций.
Во вкладке «Вложения 2021» я ежедневно обновляю данные по доходности портфеля, при вычислении её стандартной формулой «сумма вложений/текущую стоимость портфеля». По этим данным строится график.
«Составной портфель». Поскольку на двух моих счетах некоторые эмитенты дублируются, однажды я решил совместить их в одну вкладку. Здесь суммируется общая стоимость каждого эмитента, его доля в суммарном портфеле и сумма полученных дивидендов. Сюда же добавил столбец со значением, насколько процентов увеличились поступления от каждого эмитента по сравнению с прошлым годом. Понимаю, что каждый год они будут увеличиваться в любом случае, так как регулярно идет увеличение количества акций каждого из эмитентов. Посмотрим, может быть вскоре откажусь от этого столбца. Ниже в той же вкладке обозначена диверсификация активов по странам. У меня нет строгой стратегии, сколько процентов от портфеля должны составлять акции той или иной страны, но со временем более менее я пришёл к действующим цифрам.
«Дивиденды» — моя любимая вкладка. Очень приятно наблюдать за их увеличением, в том числе средним значением за последние 12 месяцев. На момент написания этого текста мой пассивный доход в среднем составляет более 3000 рублей в месяц. Даже подсчитана средняя сумма дивидендов за всё время инвестирования — 22 месяца, включая первые месяцы с полным отсутствием дивидендных выплат.
XIRR — более справедливый, на мой взгляд, способ оценки доходности. В данный момент он практически совпадает с данными доходности по формуле из вкладки «Портфель».
Идеи. Название говорит само за себя, можно и без пояснений.
Сравнение. По этой таблице я выбираю наиболее привлекательные акции, голосуя за них по разным критериям. Для иностранных акций данные нахожу на Finasquare, для российских — на Смартлабе. Наиболее привлекательному показателю ставлю единичку, наименее привлекательному — 0, иногда 0,5. И затем смотрю, какая компания набрала больше баллов.
План. После первого года инвестирования стало интересно, когда при текущих условиях я приду к сумме в 10 000 000 рублей. Тут можно играться с процентной ставкой и суммами ежегодных вложений.
Вложения (все). За неполные 2 года инвестиций были разные месяцы по вложениям. Поэтому здесь просто строится интересный график по сумме всех-всех-всех пополнений. И автоматически появляется линия тренда, как и в дивидендной вкладке, что позволяет оценить динамику вложений и среднюю сумму ежемесячных вложений за весь срок инвестирования.
В идеале было бы интересно из этой таблицы создать более презентабельный вид при помощи какого-нибудь Python или другого языка. Но поскольку таблица создавалась исключительно для себя, да и знаний у меня на это нет, она остаётся такой, какая есть в данный момент. Иногда я что-то в неё добавляю, иногда убираю. Посмотрим, как она будет выглядеть через несколько лет.
Эксельки. Здесь хвалятся своими наработками в «Гугл-таблицах»
Таблица для учета инвестиций
Я продал квартиру и вложил деньги в фондовый рынок. Чтобы отслеживать изменения по портфелю, попробовал несколько публичных сервисов — платных и бесплатных, но все они показались неудобными, либо с ежемесячной оплатой. Вернулся к старому доброму «Экселю». На разработку таблицы потратил две недели.
Таблица фиксирует все мои активы: акции, облигации, кэш, фонды. Активы записаны в количестве, рублях и долларах по среднему курсу. Распределены по секторам экономики, доля каждого актива и каждого сектора измеряется в рублях и в процентах от общей стоимости портфеля.
По каждой бумаге просчитана будущая дивидендная/купонная доходность на основе публичных данных и прогнозов. Все в процентах и деньгах. Это удобно: я точно знаю, на какую сумму дивидендов могу рассчитывать в будущем году, и могу контролировать ДД по долларовой и рублевой части портфеля независимо. Мой портфель имеет перекос в сторону дивидендных акций, поэтому мне важно понимать, сколько я заработаю за следующий год, а курсовая стоимость акций меня не интересует совсем, поэтому я ее не отслеживаю (бумаги не продаю, а только покупаю).
На основе данных в таблице построены графики: по типам активов (акции роста, акции дивидендов, защитные активы, бонды), разбивка по секторам экономики (я визуал), по валютам всех активов.
Таблица считает сумму дивидендного дохода в год и средний в месяц, в рублях и долларах отдельно + конвертация долларов по курсу в рублях и общий итог ДД в месяц.
В таблице есть дополнительные вкладки: планы по будущим покупкам (по какой цене планирую какой актив купить с обоснованием), контроль поставлений дивов / купонов (дата, сумма, эмитент), динамика капитала с графиком, подборка коротких бондов, которые я использую для финансовой подушки, портфель сына и план по пассивному доходу на 15 лет вперед, по которому я следую.
Таблицу прикладываю, но все данные по эмитентам, суммам и стоимости акций я изменил, так как мой портфель непубличный.
Действую так: Купил акцию — добавил строчку в соответствующий сектор. Указываю эмитента, сектор, количество купленных бумаг, брокера, валюту акции, сумму покупки и планируемый дивиденд на одну акцию. Формулы просчитывают все остальное.
Если акция уже была — просто изменил количество акций в строчке. Автоматически просчитывается чистая ДД (за вычетом налога) на то количество акций, которое я указал. Чистая ДД прибавляется в итоговую сумму заработка за год. Если это доллары — они конвертируются в рубли по курсу 75 рублей за доллар и добавляются к сумму заработка за год.
В комплекте к таблице идут принципы инвестирования, которым я следую. Например, доля одного эмитента не может быть более 5% от портфеля, а доля одного сектора не может быть более 15% от портфеля. Покупки совершаются в три этапа: 30% + 30% + 40% в зависимости от степени падения бумаги. По некоторым эмитентам использую так называемую «демо покупку»: когда бумага на хаях, и я захожу на одну акцию, чисто чтобы за ней следить и так далее. В совокупности таблица и принципы отлично дисциплинируют.
Благодаря таблице я точно знаю, сколько денег заработаю в следующий год. Могу отследить исторические данные по портфелю: сколько ДД принес, например, октябрь этого года, и могу сравнить его с октябрем прошлого года и оценить прибавку в ДД.
Сделки я совершаю один-два раза в месяц, каждую фиксирую в таблице. Занимает это около 10 минут.
Таблицу постоянно дорабатываю. Сейчас планирую добавить столбец, который бы просчитывал рост дивдоходности эмитента за то время, что я его держу, и средний рост в год.
Как я создал собственную гугл-таблицу для учета капитала
После того как 10 лет пользовался разными программами
Привет, меня зовут Михаил, и у меня нет кредитов, ипотеки и работы. Инвестировать я начал, когда еще был студентом.
Моя основная финансовая боль всегда была связана с эффективным учетом всех активов — то есть всего, что у меня есть. Я инвестирую через различных брокеров, не только в РФ, но и за ее пределами, а еще вкладываю в недвижимость, депозиты, монеты и страхование юнит-линкед.
Мне было сложно увидеть полную картину активов, потому что у разных финансовых посредников нет единой формы и стандарта отчетов. Ни одна из программ, которыми я пользовался, не подходила мне на сто процентов: в основном приходилось слишком долго возиться с добавлением новых бумаг, подтягиванием нужных котировок.
Поэтому я разработал собственную отчетную форму в «Гугл-таблицах»: туда я импортирую отчеты разных брокеров и записываю активы, чтобы понимать, что происходит с моим капиталом, и видеть достоверный бюджет поступлений на месяц вперед.
Как работает таблица
Изначально мой отчет был табличкой в экселе с использованием упрощенного языка программирования VBA, но сейчас я перенес его в гугл-таблицу без использования скриптов.
Чтобы таблица была не просто очередным шаблоном, я дал ей собственное имя — SilverFir: Investment Report. Название говорит о том, что это инвестиционный отчет, а silver fir отсылает к разновидности вечнозелёных деревьев.
Прежде чем пошагово расписать, как пользоваться шаблоном гугл-таблицы, необходимо сделать несколько важных пояснений.
Форматы данных. В настройках таблицы указаны региональные настройки Соединенных Штатов. Это означает, что разделитель целой и дробной части числа — точка, то есть 105.1 — правильная запись, а 105,1 выдаст ошибку. Это сделано, чтобы не загромождать формулы автоматической заменой точки на запятую. Все американские и многие российские сайты выдают цены именно с точкой в качестве разделителя.
Даты указаны в формате «год-месяц-день», то есть «2020-03-11» — 11 марта 2020 года.
Разделитель в формулах при американских региональных настройках — запятая, в отличие от российского формата — точки с запятой. Если вы будете переносить формулы в какие-то свои таблицы, имейте это в виду.
Как победить выгорание
Основные параметры, используемые в таблице. Чтобы заполнить таблицу и корректно ею пользоваться, необходимо знать следующие параметры:
Знание экселя и регулярных выражений не помешает
Актуальные цены многих активов подтягиваются со сторонних сайтов с помощью функции ImportXML. Для разных активов используются разные сайты. Например, данные по актуальной стоимости квартиры на Арбате я беру с сайта «Домофонд». И тут две проблемы.
Во-первых, если «Домофонд» обновит структуру сайта, формула может слететь, потому что она обращается к конкретной части страницы. На момент публикации статьи все формулы работают, но со временем что-то может поменяться.
Во-вторых, если вы захотите подтягивать актуальную цену квартиры в другом районе или городе, формулу нужно будет переписать.
Если вам нужна будет помощь с этим, я постараюсь отвечать в комментариях к статье.
Пошаговое руководство по заполнению
По ссылке откроется сразу ваша копия таблицы — можно редактировать данные прямо в ней. Никто другой не имеет доступа к данным в вашей копии.
Представим, что у вас есть несколько типов активов: два вклада в разных валютах, ИИС, обычный брокерский счет, арендная квартира в Москве и монета «Георгий Победоносец». Разберемся, как получить полную картину по сбережениям.
Начнем с вкладов. Готовые примеры занесены в строки 7 и 8 таблицы.
Пусть это будет вклад 50 000 Р под 5,8% годовых, открытый 22 марта 2020 года сроком на год — до 22 марта 2021 года. Разнесем данные по столбцам таблицы:
Как следить за бюджетом
Если ваш вклад не в рублях, то таблица автоматически рассчитает начальные затраты в рублях в столбце «Цена покупки, Р » по курсу на дату открытия вклада.
Индивидуальный инвестиционный счет (ИИС). Допустим, что на ИИС куплено 100 облигаций федерального займа ОФЗ-ПД 26225. Код этой ценной бумаги — SU26225RMFS1. Облигации куплены 3 сентября 2018 года по цене 89% от номинала.
Код ценной бумаги можно посмотреть в отчете брокера или на сайте биржи
Разнесем данные по столбцам таблицы, которые надо заполнить вручную:
Брокерский счет. Допустим, на брокерском счете — бумаги двух эмитентов:
Разнесем данные по столбцам таблицы. Для облигаций ГК «Пионер»:
Если в дальнейшем я буду докупать те же бумаги, нужно просто обновить в этой строке количество бумаг и базовую цену. Остальные значения остаются неизменными. Таким образом, «Дата покупки» — это, строго говоря, дата первой покупки актива.
Разнесем данные по столбцам таблицы:
Разнесем данные по столбцам таблицы:
Что делать после заполнения данных
После того как вы внесете исходные данные, сразу можно увидеть работу формул. Данные начнут скачиваться, и таблица автоматически заполнится недостающими параметрами.
Теперь можно узнать следующие показатели по каждому из активов:
Дополнительно вручную можно указать категории и классы активов, если вы хотите смотреть распределение и по ним. Автоматическое скачивание возможно реализовать только на гугл-скриптах.
Анализ сводных показателей портфеля
Перейдем теперь к сводным показателям всего портфеля. Их можно смотреть на разных вкладках.
«Данные» — это главная вкладка, куда вносятся все исходные. Светло-голубым выделены ячейки, которые надо заполнить вручную. Также на этой вкладке рассчитывается прибыль и убыток по позиции, дата и размер ближайшего поступления от актива.
«Валюты» — полностью автоматическая вкладка, которая содержит отчет по используемым валютам. Как только вы редактируете что-либо на вкладке «Данные», этот мини-отчет сразу меняется.
«Посредники» — отчетная вкладка, которая показывает распределение сумм по брокерам и весовое значение процента капитала. Еще она показывает количество бумаг у каждого брокера и расчетный ежемесячный доход, также этот доход отображается в процентах годовых.
На этой вкладке можно оценить, насколько успешен тот или иной счет, потому что отображаются изменения в рублях с момента покупки.
«Классы активов» — здесь вы увидите отчет о диверсификации вашего портфеля. Я формализовал описания классов активов из Quicken и описаний нескольких авторов, в том числе Сергея Спирина, Александра Силаева, Павла Комаровского.
«Покупки» — это мини-отчет об истории покупок по времени. Здесь вы сможете узнать, в каком месяце сколько денег потратили.
«Капитал» — на этой вкладке отображается текущая дата и две совокупных стоимости всех активов: стоимость покупки и текущая рыночная стоимость портфеля в рублях. Эта вкладка реализована с помощью формул, а формулы не могут сами копироваться в другие ячейки — для создания истории придется вручную копировать эти данные на строчку ниже.
«Капитал график» — визуализирует данные с вкладки «Капитал».
«Идентификаторы» — в графическом виде отображает распределение по бумагам в таблице.
«Отчет» — сводный отчет о планируемых поступлениях на три месяца вперед в рублях, то есть сумма купонов, арендных платежей. Также вкладка дает информацию о ближайших выплатах на 30 дней вперед и назад, а еще — о лидерах роста и падения вместе с историей капитала.
Запомнить
Тебе не придётся напрягаться с учётом инвестиций, если у тебя их нет!
(картинка, с умным негром)
Спасибо за материал, взял на заметку пару интересных моментов.
Для себя тоже веду Excel-таблицу, сначала она была такая же сложная, потом постепенно приходил к выводу, что та или иная аналитика избыточна. В итоге пришел к варианту буквально с двумя страницами: на первой веду все операции с активами (покупка, продажа, купоны/дивиденды), на второй автоматически собирается вся необходимая мне сводная информация:
1. Текущая доля каждого класса актива и отклонение от желаемой доли.
2. Текущая доля распределения по валютам и по отраслям.
3. Текущая доля каждого актива относительно всего портфеля.
4. Текущая стоимость каждого актива в рублевом эквиваленте (тоже подтягивается автоматически из разных источников).
5. Количество.
6. Годовая доходность каждого актива.
7. Общая стоимость и годовая доходность всего портфеля.
8. Денежный поток по каждому активу (сколько купонов/дивидендов получено).
9. Общая прибыль в рублевом выражении с учетом изменения цены актива и всех полученных по нему купонов/дивидендов.
А, ну еще есть одна страница, на которой красивый график, показывающий прогнозную стоимость моего портфеля на 20 лет вперед, если я буду продолжать дисциплинированно инвестировать дальше. И на этом же графике вторая кривая, показывающая фактический результат. Очень мотивирует.
Считаем доходность инвестиций в портфеле + готовая Excel таблица с формулами
Например, что более выгодно? Вложить 57 тысяч на 3 месяца и заработать 3 тысячи. Или инвестировать 75 000 на восемь месяцев и получить 5500?
Как узнать процент эффективной доходности портфеля, если в течение года было постоянное снятие и внесение средств?
Считаем прибыль
Самая простая и базовая формула для определения «выгодности» вложений.
Разность между конечной суммой и начальной образует чистую прибыль.
Чтобы вывести в процентном соотношении воспользуйтесь формулой:
Доходность = (чистая прибыль) / сумму вложения * 100%.
Пример.
Купили акции Газпрома на 10 000 рублей. Через год все продали за 13 000 тысяч.
Доходность вложений 30% (3 000 / 10 000) * 100%).
В этой формуле есть один существенный недостаток. Она позволяет рассчитать только абсолютную доходность. Без привязки к периоду, за который она была получена.
Мы могли заработать 30% за 1 год. А могли бы и за 5 лет.
Годовая доходность в процентах
Более правильно оценить прибыль вложений можно с помощью годовой доходности.
Если простыми словами, то годовая доходность показывает, сколько зарабатывает инвестор на каждый вложенный рубль за одинаковый период времени.
Как это выглядит на практике?
Через полгода, после роста котировок Сбербанка, продали все за 36 тысяч рублей.
Газпром вы держали ровно год и скинули бумаги за 65 тысяч.
Итог: На Сбербанке вы заработали за полгода 6 тысяч. На Газпроме 15 тысяч, но за целый год.
Если считать по общей доходности, то чистая прибыль в процентах составила бы:
Для правильной оценки эффективности инвестиций нужно все перевести в годовые проценты:
Доходность (% годовых) = (прибыль в % * 365 дней) / срок инвестиций в днях.
Доходность Сбербанка = 20% х 365 дня / 180 дней = 40% годовых
Доходность Газпрома = 30% х 365 / 365 = 30% годовых.
Более выгодными оказались инвестиции в акции Сбербанка.
Доходность с учетом движения средств
А как вывести общий результат, например за год?
Складывать все доходности не очень удобно и трудоемко.
Пример. На начало года инвестор обладал капиталом в 200 тысяч рублей. За счет выгодных инвестиций, через год его портфель оценивался в 240 тысяч.
Чистая прибыль 40 000 рублей или 20% годовых.
В этой методике расчета доходности есть один существенный минус, который будет искажать реальные цифры. А по простому, делать их совсем не правильными.
За рассматриваемый период не учитываются возможные движения средств по счету или портфелю.
Что это за движения:
Из примера выше. Если за месяц до окончания годового периода инвестор вносил дополнительные 40 тысяч рублей. Как это скажется на результате? В абсолютных цифрах мы также имеем +40 тысяч прибыли или 20% годовых. Но по факту результата ноль.
Другой вариант. Через 1 месяц инвестор не внес, а снял 40 тысяч. В итоге почти целый год он оперировал суммой на 20% меньше первоначальной. И все равно заработал 40 тысяч прибыли.
Или в течение года выплачивались дивиденды, купоны. Были постоянное внесение и вывод средств со счета. Как тогда? Как определить реальную доходность?
Для расчета есть специальная формула расчета процентов в зависимости от даты и суммы движения средств. Но думаю большинство (наверняка все) не будут ее пользоваться. Она слишком сложная и громоздкая. Даже ее приводить здесь не буду.)))
Расчет доходности в Excel
Есть более простой вариант расчета процентов в таблице Эксель. Нам поможет формула ЧИСТВНДОХ.
Как заполнить таблицу?
Нам нужны 2 колонки по движению денежных средств:
Все поступления на счет должны быть со знаком плюс. Снятия и прочие расходы обязательно со знаком минус. Конечная финальная сумма (на момент которой подсчитывается доходность) на счете тоже со знаком минус.
Вот как это выглядит на примере:
Как это сделать в Excel?
Вносим в таблицу собственные значения (по аналогии с примером выше).
Вызываем функцию ЧИСТВНДОХ.
В поля «Значение» и «Даты» вносим наши условия как на картинке ниже. Просто выделяя правой кнопкой мыши необходимый диапазон.
По ссылке, есть файл Excel с уже готовыми формулами, перечисленными в статье. Подставляете свои данные. Считаете прибыль. Радуйтесь (или огорчайтесь) полученной доходности.