Таблица учета инвестиций excel по дивидендным акциям
Таблица для учета инвестиций
Я продал квартиру и вложил деньги в фондовый рынок. Чтобы отслеживать изменения по портфелю, попробовал несколько публичных сервисов — платных и бесплатных, но все они показались неудобными, либо с ежемесячной оплатой. Вернулся к старому доброму «Экселю». На разработку таблицы потратил две недели.
Таблица фиксирует все мои активы: акции, облигации, кэш, фонды. Активы записаны в количестве, рублях и долларах по среднему курсу. Распределены по секторам экономики, доля каждого актива и каждого сектора измеряется в рублях и в процентах от общей стоимости портфеля.
По каждой бумаге просчитана будущая дивидендная/купонная доходность на основе публичных данных и прогнозов. Все в процентах и деньгах. Это удобно: я точно знаю, на какую сумму дивидендов могу рассчитывать в будущем году, и могу контролировать ДД по долларовой и рублевой части портфеля независимо. Мой портфель имеет перекос в сторону дивидендных акций, поэтому мне важно понимать, сколько я заработаю за следующий год, а курсовая стоимость акций меня не интересует совсем, поэтому я ее не отслеживаю (бумаги не продаю, а только покупаю).
На основе данных в таблице построены графики: по типам активов (акции роста, акции дивидендов, защитные активы, бонды), разбивка по секторам экономики (я визуал), по валютам всех активов.
Таблица считает сумму дивидендного дохода в год и средний в месяц, в рублях и долларах отдельно + конвертация долларов по курсу в рублях и общий итог ДД в месяц.
В таблице есть дополнительные вкладки: планы по будущим покупкам (по какой цене планирую какой актив купить с обоснованием), контроль поставлений дивов / купонов (дата, сумма, эмитент), динамика капитала с графиком, подборка коротких бондов, которые я использую для финансовой подушки, портфель сына и план по пассивному доходу на 15 лет вперед, по которому я следую.
Таблицу прикладываю, но все данные по эмитентам, суммам и стоимости акций я изменил, так как мой портфель непубличный.
Действую так: Купил акцию — добавил строчку в соответствующий сектор. Указываю эмитента, сектор, количество купленных бумаг, брокера, валюту акции, сумму покупки и планируемый дивиденд на одну акцию. Формулы просчитывают все остальное.
Если акция уже была — просто изменил количество акций в строчке. Автоматически просчитывается чистая ДД (за вычетом налога) на то количество акций, которое я указал. Чистая ДД прибавляется в итоговую сумму заработка за год. Если это доллары — они конвертируются в рубли по курсу 75 рублей за доллар и добавляются к сумму заработка за год.
В комплекте к таблице идут принципы инвестирования, которым я следую. Например, доля одного эмитента не может быть более 5% от портфеля, а доля одного сектора не может быть более 15% от портфеля. Покупки совершаются в три этапа: 30% + 30% + 40% в зависимости от степени падения бумаги. По некоторым эмитентам использую так называемую «демо покупку»: когда бумага на хаях, и я захожу на одну акцию, чисто чтобы за ней следить и так далее. В совокупности таблица и принципы отлично дисциплинируют.
Благодаря таблице я точно знаю, сколько денег заработаю в следующий год. Могу отследить исторические данные по портфелю: сколько ДД принес, например, октябрь этого года, и могу сравнить его с октябрем прошлого года и оценить прибавку в ДД.
Сделки я совершаю один-два раза в месяц, каждую фиксирую в таблице. Занимает это около 10 минут.
Таблицу постоянно дорабатываю. Сейчас планирую добавить столбец, который бы просчитывал рост дивдоходности эмитента за то время, что я его держу, и средний рост в год.
Как сделать таблицу в Excel для анализа портфеля ИИС.
Следить за изменением стоимости акций и соблюдать пропорции.Я решил завести таблицу для отслеживания результата инвестиций на ИИС в мае 2020 года.На ИИС я придерживаюсь четкого плана в противовес случайному характеру покупок на брокерском счете. Никогда особо не доверял различным сервисам по анализу инвестиций. Могу сравнить их со своей таблицей при надобности, но никогда не отдам анализ в чужие руки. Мне важно досконально понимать, как считается каждое число, и своя таблица в этом плане опережает все подобные сервисы.Сам я работаю в сфере автоматизации, и потому таблица, по моему мнению, также должна быть полностью автоматизирована. Принципиально не хочу ничего вводить руками: знаю, что когда-нибудь забуду об этом или мне просто надоест. Основная часть информации подгружается благодаря OpenAPI Тинькофф Инвестиций. Почти все сделано с помощью скриптов. Формул — минимальное количество. Никаких брокерских отчетов загружать не надо.Главная страница таблицы состоит из нескольких блоков:Состав портфеля. Показывает, сколько у меня сейчас акций, текущую и средние цены, прибыль/убыток, текущие пропорции и пропорции после покупки.Графики трендов и изменения цен за выбранный период.Общая информация по портфелю. Смотрю здесь, обгоняю ли я вообще инфляцию.График отдельно выбранного актива с минимальным техническим анализом.
Особенности таблицы
Киллер-фича моей таблицы — она рассчитывает, что и в каких количествах купить, чтобы соблюсти плановые пропорции. В идеале я вообще не должен тратить время на ее ведение, а только ежемесячно пополнять счет суммой, на которую буду покупать активы. Эта функция помогает придерживаться заданных пропорций автоматически — не нужно каждый раз думать, что покупать.
Для начала я составил план распределения активов в портфеле. У вас должна быть тактика, которой вы будете придерживаться, иначе это все бессмысленно. Для себя решил, что мой портфель будет состоять только из ETF:
Целевые пропорции указаны в столбце Target allocation. Столбец Current allocation показывает текущие пропорции в портфеле. Класс активов не имеет значения.
Моей задачей было написать алгоритм, следуя которому я мог бы покупать акции, максимально приближаясь к заветным пропорциям. Для этого я написал отдельный скрипт — WhatToBuy.js, который считывает текущие цены активов, их пропорции и доступный бюджет; а также ищет наибольшее отклонение от плановой доли актива и присуждает каждому активу приоритет для покупки.
В первую очередь алгоритм будет «покупать» активы с наибольшим отклонением — до тех пор, пока не восстановит заданные пропорции. Затем перейдет к следующему по приоритету активу и так по кругу, пока не кончится бюджет. Детально описывать не буду, при желании можно самому разобраться в коде.
Алгоритм довольно прост, но потребуются небольшие знания JavaScript. Результатом его работы станет точное указание, в каких количествах покупать тот или иной актив, сколько комиссии уйдет брокеру (Fee, строка 10) и сколько бюджета останется (Cash, строка 10).
Как сделать такую же таблицу
Я создавал таблицу на базе готового исходного кода. Низкий поклон этому человеку. Просто скопируйте код с этого сервиса и посмотрите, что и как работает. Если у вас есть навыки программирования, то вам не составит труда быстро адаптировать его под свои задачи.
Если навыков нет, то вкратце этот процесс добавления скрипта выглядит так:
Этот скрипт добавляет в вашу таблицу функции, с помощью которых можно подгрузить ваши операции. Подробнее о функциях можно почитать также на странице исходного кода.
Если вас устроят доступные функции, то можно этим ограничиться и не лезть глубоко в код. Но чтобы вводить новые фишки, придется подтянуть навыки программирования и более основательно изучать документацию. В любом случае таблица будет индивидуальна для каждого человека, поэтому единой инструкции для всех нет.
Я также сделал шаблон своей таблицы, чтобы вы могли настроить его под себя. Чтобы воспользоваться им, сначала нужно получить доступ к своему портфелю при помощи OpenApi Тинькофф Инвестиций. Это самый сложный этап, так как подразумевает совершение нескольких нетривиальных операций.
Для начала придется получить уникальный токен для работы со своим портфелем — программистом тут быть не надо. Весь процесс хорошо описан в документации по ссылке. Если вкратце, нужно зайти в настройки своего инвестиционного счета в браузере и выбрать внизу настроек пункт «Токен для OpenAPI».
В самой таблице порядок такой:
Важно: если в вашем портфеле больше восьми активов, функция выдаст ошибку — ей просто не хватит строк. В этом случае нужно добавить нужное количество пустых строк ниже девятой — как только строк станет достаточно, данные подгрузятся автоматически. Еще в этом случае придется увеличить диапазоны в некоторых формулах ниже — по умолчанию они будут считать только диапазон со второй по девятую строку.
Таблица избавляет меня от случайных ошибок и мук выбора «чего бы купить». В целом помогает докупать подешевевшие активы и не покупать слишком дорого.
Мне, в принципе, скорее интересен сам результат программирования, чем результат инвестиций. Не могу остановиться и постепенно добавляю новые фишки. Побочным эффектом подучил JavaScript и «Эксель», наверное, пока это самая большая польза от этой таблицы.
Считаем доходность инвестиций в портфеле + готовая 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 с уже готовыми формулами, перечисленными в статье. Подставляете свои данные. Считаете прибыль. Радуйтесь (или огорчайтесь) полученной доходности.
Прогноз предстоящих дивидендов по акциям российских компаний
[Бесплатный шаблон Excel]
Пётр Ляпин, наш технический директор, поделился своим методом прогноза дивидендов по акциям российских компаний в Cообществе Т—Ж. Приводим здесь статью с дополнениями.
Как спрогнозировать предстоящие дивиденды сразу на нескольких брокерских счетах
У меня есть несколько брокерских счетов. При покупке акций я в первую очередь руководствуюсь тем, насколько хорошо компания развивается, как стабильно и какие дивиденды платит, увеличиваются ли они от года к году. На текущий момент в портфеле есть акции порядка 35 российских компаний.
Также есть акции американских компаний, но в силу разрозненности источников данных по российским и зарубежным компаниям в этой статье я расскажу только про то, как работать с российскими. Пишите в комментариях, если вам была бы интересна аналогичная статья про американские компании.
Поработав с брокерами ВТБ и Тинькофф, я не нашел удобного способа посмотреть общий прогноз по дивидендам по всему портфелю на горизонте до года. Можно посмотреть только уже утвержденные дивиденды по конкретной компании или по акциям в рамках одного брокерского счета. Возможности увидеть прогноз по всему портфелю (т.е. по всем акциям на нескольких брокерских счетах) и спрогнозировать, какие дивиденды могут быть еще до того, как их утвердили, к сожалению, нет.
Прогнозы по дивидендам нашлись на известном сайте dohod.ru, но каждый раз вручную считать дивиденды по акциям 35 компании совсем не хотелось. На помощь пришел Excel и встроенный в него функционал Power Query.
В итоге получился довольно простой в использовании и полезный шаблон Excel, с которым можно быстро получить нужный результат.
Обзор шаблона Excel для прогноза дивидендов
Этот шаблон для прогноза предстоящих (в горизонте одного года) дивидендов по акциям российских компаний будет полезен тем инвесторам, кто ориентирован на дивидендную стратегию и долгосрочные инвестиции.
В качестве источника прогнозов используется информация с сайта dohod.ru.
Шаг 1. Скачайте шаблон для прогноза дивидендов
Шаблон бесплатный, скачайте его по ссылке ниже. Не забудьте сохранить файл на свой компьютер, чтобы самостоятельно повторить все шаги дальше в этой статье.
Документ состоит из четырёх листов:
Шаг 2. Заполните данные на листе «Портфель»
Значение в поле «Тикер» редактировать не нужно, оно подтягивается автоматически с листа «Компании РФ».
Шаг 3. Обновите лист «Прогноз дивидендов»
Буквально за несколько секунд Excel в фоновом режиме подгрузит прогноз по вашему портфелю и обновит данные в таблице «ПрогнозДивидендов»:
Долгосрочная цель моих инвестиций – это выход на некий уровень пассивного дохода (с дивидендов), который бы меня устраивал. Наличие такого шаблона позволяет прогнозировать сумму дивидендов на год вперед и использовать прогноз для ребалансировки портфеля и новых покупок.
Делаете ли вы прогнозы по своему портфелю, и если да, то как и какие? Пишите в комментариях!
Методы учета доходности портфеля
Достаточно частый вопрос о том, как вести учет доходности своих портфелей в экселе. За 4 года я выделил для себя 2 наиболее удобных способа. Автоматизированный учет на сторонних ресурсах (вроде Интелинвест) сегодня разбирать не будем.
Способ 1. Ежемесячный учет доходности.
Это самый первый метод, к которому я пришел. Здесь все просто, каждый месяц вы учитываете то, сколько денег было в портфеле на начало месяца, сколько вы довнесли или сняли за этот период и сколько осталось на конец месяца.
Пример:
1 ноября в портфеле было активов общей стоимостью 95 000 рублей.
За месяц ничего не снимали и не пополняли.
30 ноября в портфеле активы стоили 100 000 рублей.
Доходность за ноябрь = (100 000 — 95 000) / 95 0000 * 100% = 5,3%
1 декабря сумма активов в портфеле была 100 000 рублей.
10 декабря вы довнесли 50 000 рублей.
31 декабря в портфеле было 153 000 рублей.
Доходность за декабрь = (153 000 — 100 000 — 50 000) / 100 000 * 100% = 3%, таким образом, все довнесения и снятия влияют только на доходность одного месяца.
1 января сумма активов равна 153 000 рублей… и т.д.
В конце года я просто суммирую все месячные доходности и получаю примерную картину динамики доходности за весь год.
Способ 2. Функция Excel ЧИСТВНДОХ()
Эта функция возвращает внутреннюю ставку доходности для графика денежных потоков, которые не обязательно носят периодический характер. Проще говоря, эта функция сама учитывает даты и суммы взносов и выводов средств, а так же считает доходность в зависимости от срока. В отличие от ежемесячного учета, здесь нет необходимости вписывать данные по тем месяцам, когда не было операций ввода/вывода средств.
Главное помнить одно простое правило, все пополнения счета идут со знаком (-) минус, все выводы средств и конечный результат со знаком плюс.
Пример функции выглядит так: =ЧИСТВНДОХ(диапазон сумм; диапазон дат).
Скачать шаблон файла Excel можно по ссылке. В последние годы постепенно перехожу на учет именно по данной формуле. По итогам 2020 года учет по месяцам и учет по формуле дали примерно одинаковый результат с небольшой погрешностью.
Всем успешных инвестиций!
Следить за всеми моими обзорами можете здесь: Telegram, Смартлаб, Вконтакте