Как вести инвестиционный портфель в excel
Перейти к содержимому

Как вести инвестиционный портфель в 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, Смартлаб, Вконтакте

Сделал план по инвестициям на 15 лет вперед в Excel !

Хотел бы поделиться с вами своей таблицей по планированию долгосрочных инвестиций. Мой путь в инвестировании начался именно с этой таблицы и привел к полумиллиона активов.

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

У вас наверняка возник вопрос: Если все так просто, то зачем же она нужна?

А я вам отвечу: Эта таблица может дать вам гораздо больше, чем вы думаете. Она превращает ваши мечты в реальность, позволяет объективно взглянуть на конкретно вашу финансовую ситуацию, продумать шаги для достижения именно ваших целей и наконец начать предпринимать что-то для получения результата. И если вы как и я всегда хотели стать как Уоррен Баффет, как Скрудж Макдак , как сын маминой подруги и не испытывать проблем с деньгами, то рекомендую вам дочитать статью до конца.

Эта таблица поспособствует тому, что вы наглядно пропишите и учтете:

  • период инвестиций;
  • ваш планируемый доход;
  • планируемые расходы;
  • крупные цели;
  • обязательства;
  • расходы на отпуск;
  • доходность вашего портфеля;
  • распределение долей взносов по активам.

В результате вы сможете увидеть сумму, которую реально будет получить при заданных условиях. В таблице учтено все кроме случайности.

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

Теперь предлагаю перейти к заполнению. В таблице указан не мой план, которому я следую, а просто пример. Вы можете изменять исходные данные. Жми на ссылку и следуй шаг за шагом:

Заполняем доходы и расходы (последние столбцы). Здесь все просто. Там где у вас указаны месяца вы пишите ваш планируемый доход и расход в месяц, там где начинаются года, то умножаете эти данные на 12. У меня для примера указан доход на начальном этапе 35 тысяч, расход 25 тысяч, а после я их увеличиваю с течением времени. Чем выше у вас доход и меньше расход, тем больше вы сможете выделить под инвестиции и соответственно получить в результате капитал либо больше, либо быстрее (в зависимости от целей). Но тут главное не перестараться, в свой первый раз я заложил слишком маленькие расходы и потом корректировал.

После того, как вы внесли доходы и расходы таблица рассчитает вам прибыль(самый последний столбец), то есть средства, которые вы можете пустить на инвестиции, либо оставить на счету. Исходя из этой суммы необходимо решить, какую долю вы хотели бы выделить для создания инвест портфеля. У меня указано значение 80% от прибыли, но оно может быть абсолютно любым. Чем выше доля вложений, которую вы готовы инвестировать, тем лучше будет результат. Это значение необходимо внести в ячейку с названием «Капитал» над графой «ВЗНОСЫ».

Заполняем распределение капитала между различными видами активов. В таблице учитываются низкорисковые активы, среднерисковые и высокорисковые. В зависимости от уровня доходности (чем она выше, тем выше риски) необходимо распределить доли, согласно которым будет распределяться «Капитал» по этим активам. Чтобы было понятнее приведу пример. У меня в таблице, в ячейке «Консервативный», над графой «ВЗНОСЫ» указана цифра 5%, это значит, что одна двадцатая от выделенного мной капитала(на предыдущем шаге) пойдет на низкорисковые активы. Аналогичным образом, нужно будет выделить долю для ячейки «Умеренный»(среднерисковые) и «Агрессивный»(высокорисковые). В сумме эти доли должны составлять 100%. В этом плане это 5%+30%+65%.Стоит понимать, что риск влияет на доходность. Не нужно бояться этого слова ,но и пренебрегать им не стоит. Чем выше будет агрессивная доля в портфеле тем, быстрее вы получите нужный результат. Обращаю внимание, что заполнить нужно только ячейку с процентом, в самой таблице расчет идет автоматически.

Переходим к соседней графе-«НАКОПЛЕНИЯ». Это и есть наш портфель. Сюда подтягиваются значения из колонки «ВЗНОСЫ» и учитывается доходность портфеля. По умолчанию у меня указаны актуальные проценты доходности для долгосрочного инвестирования, поэтому стоит их менять только если ситуация на рынке меняется, сейчас можно этого не делать. В этой части таблицы находится самое интересное-столбец, который выделен голубым под названием «Капитал». В нем указана сумма активов на вашем портфеле накопленная к определенному временному периоду. Следует понимать, что на протяжении всего периода инвестирования вы реинвестируете средства и не снимаете их с брокерского счета, таким образом работает сложный процент и вы получаете десятикратный результат. Для того чтобы у вас не было соблазна воспользоваться средствами на брокерском счету, понадобится финансовая подушка.

Столбец, который выделен розовым, под названием «Остаток на счете», тоже достаточно интересен. Это те средства, которые остаются у вас на счету, после того как вы учли все свои расходы, цели, отпуск, взносы на инвестиции , выплаты по обязательствам, поэтому я рекомендую сначала заполнить только вложения по инвестициям (как мы это сделали в предыдущих шагах), после этого посмотреть сколько свободных средств у вас есть(в графе «Остаток на счете») и на какой момент времени, а потом уже планировать остальные траты( цели, отпуск, кредиты) отталкиваясь от свободных средств на счету.

Все что нужно было заполнить на этом этапе мы с вами сделали, поэтому сейчас можно оценить результат в голубой колонке «Капитал» и анализируем цели. Если вас не удовлетворяет результат, то можете поиграть с уровнем расходов, доходов, распределением взносов в портфель и т.д.

В итоге вы сможете прийти к плану, который отражает лично вашу финансовую стратегию, финансовое состояние и цели. По результатам данных которые я внес, мы можем увидеть капитал в размере 11,8 млн за 15 лет с относительно умеренной доходностью (среднерисковый портфель). С покупкой машины без кредита и отпуском раз в год на сумму около 30 тысяч. Стоит понимать, что это лишь пример, и эта таблица является инструментом, а не готовым инвест планом.

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

Оценка эффективности инвестиций, инвестиционного портфеля, акций на примере в Excel

Crypto Tyan

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

Показатели эффективности инвестиций на фондовом рынке

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

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

Доходность инвестиций. Оценка и анализ акций

Первый один из самых важных показателей инвестиции (акции, облигации, фьючерса и т.д.) является ее доходность. Она отражает привлекательность финансового инструмента для инвесторов. Для примера мы будем оценивать доходность акции. Так чем выше привлекательность акции, тем выше ее доходность и стоимость на фондовом рынке. Для того чтобы оценить доходность акций воспользуемся сервисом сайта finam.ru, который позволяет получить текущие котировки акций в режиме реального времени.

Пример оценки доходности акций ОАО «Газпром» в Excel

Рассмотрим оценку доходности акции ОАО «Газпром» (GAZP) в Excel. Были загружены недельные котировки за 31.01.2014–31.01.2015 г. Далее необходимо вставить котировки акций в таблицу, как представлено на рисунке ниже.

Котировки акции ОАО «Газпром»

Для расчета доходности акции можно воспользоваться следующей формулой:

Доходность ОАО «Газпром» = (B6-B5)/B5

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

Доходность ОАО «Газпром» =LN(B6/B5)

Расчет доходности акции ОАО «Газпром» в Excel

Прогнозирование доходности акции

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

На рисунке ниже показан результат расчета будущей доходности ОАО «Газпром» по данной модели. Формула оценки доходности будет следующая:

Доходность акции ОАО «Газпром» =СРЗНАЧ(C6:C56)

Оценка будущей доходности акции ОАО «Газпром»

Оценка риска акции в Excel

Под риском акции подразумевается его волатильность или изменчивость (данную трактовку ввел Г.Марковиц). То есть чем больше чувствительность изменения котировок, тем выше риск акции. Для расчета риска необходимо рассчитать стандартное отклонение доходностей акции от среднего. На рисунке ниже представлена формула расчета стандартного отклонения доходностей акции ОАО «Газпром».

Оценка риска акции ОАО «Газпром» =СТАНДОТКЛОН(C6:C56)

Оценка эффективности инвестиций. Коэффициент Шарпа

Коэффициент Шарпа (англ. Sharp ratio) — самый распространенный коэффициент оценки эффективности инвестиций на фондовом рынке, был введен экономистом У. Шарпом в 1966г. Данный коэффициент используют для анализа акций, фьючерсов, инвестиционных портфелей, стратегий. Коэффициент Шарпа показывает отношение доходности к риску инвестиции.

Формула расчета коэффициента Шарпа

Формула расчета коэффициента Шарпа следующая:

rp — средняя доходность инвестиционного портфеля;

rf — средняя доходность безрискового актива;

σp — стандартное отклонение доходностей инвестиционного портфеля (риск портфеля).

Как видно, коэффициент Шарпа показывает отношение избыточной доходности инвестиционного портфеля к риску. За безрисковую доходность по активу, на практике, берут:

  • Доходность по банковскому вкладу наиболее надежных банков РФ;
  • Доходность государственных ценных бумаг (ГКО, ОФЗ);
  • Размер ключевой ставки ЦБ РФ;

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

Оценка эффективности инвестиций по коэффициенту Шарпа

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

Значение коэффициента Шарпа
Оценка эффективности инвестиции

Sharp ratio >1 Высокая степень эффективности управления инвестиционным портфелем, инвестициями

1>Sharp ratio >0 Уровень риска вложения в данную инвестицию выше, чем ожидаемый уровень доходности

Sharp ratio <0 Вложение не целесообразно, так как доходность безрискового актива выше

Пример расчета коэффициента Шарпа для оценки эффективности инвестиционного портфеля в Excel

Рассчитаем показатель Шарпа для инвестиционного портфеля. Для этого необходимо знать доходность по инвестиционному портфеля за определенный интервал времени. В данном примере рассматривался расчет для инвестиционного портфеля за период с 10.02.2014 по 17.03.2014 года по неделям. Формулы расчета следующие:

Средняя доходность инвестиционного портфеля =СРЗНАЧ(B5:B10)

Риск инвестиционного портфеля =СТАНДОТКЛОН(B5:B10)

Доходность безрискового актива = 15%/50. Данное значение соответствует средней доходности по банковскому вкладу (15%), поделенное на количество недель в году.

Коэффициент Шарпа =(C5-E5)/D5

На рисунке ниже представлены результаты расчета.

Расчет коэффициента Шарпа в Excel.

Коэффициент Шарпа составил 0,12 — это говорит о том, что данный портфель управляется довольно рискованно, то есть уровень риска выше, чем уровень доходности.

Коэффициент бета для оценки эффективности инвестиций

Коэффициент бета (англ. beta coefficient) ­– показывает чувствительность изменения доходности инвестиционного портфеля от доходности рынка (рыночного индекса). Данный коэффициент используется как самостоятельно, так и в модели оценки капитальных активов CAPM (Capital Assets Price Model). Коэффициент бета отражает систематический риск инвестиции.

Формула расчета коэффициента бета

β — коэффициент бета;

rp — доходность инвестиционного портфеля;

rm — рыночная доходность;

σ2m — дисперсия рыночной доходности.

Оценка эффективности инвестиций по коэффициенту бета

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

Оценка риска акций. Пример расчета коэффициента Бета в Excel

Для расчета коэффициента бета необходимо рассчитать ковариацию между доходностями инвестиционного портфеля и доходностями рыночного индекса (индекса РТС) и соотнести с дисперсией доходности рынка. Формула в Excel расчета следующая:

Коэффициент Бета =КОВАР(B5:B10;C5:C10)/ДИСП(C5:C10)

Расчет коэффициента бета в Excel

Коэффициент бета равен 0,22 — что показывает умеренный уровень рыночного риска для данного инвестиционного портфеля.

Коэффициент Трейнора для оценки эффективности инвестиций

Коэффициент Трейнора (англ. Treynor ratio) — показатель эффективности инвестиции (инвестиционного портфеля), который показывает отношение превышение доходности инвестиции над систематическим риском портфеля. В качестве систематического риска используют значение рыночного риска, который рассчитывается как коэффициент бета. Коэффициент бета отражает чувствительность изменения доходности инвестиций и доходности рынка (рыночного индекса). Показатель Трейнора используется многими инвестиционными компаниями для осуществления ранжирования качества управления финансовыми продуктами.

Коэффициент Трейнора. Формула расчета

rp — средняя доходность инвестиционного портфеля;

rf — средняя доходность безрискового актива;

βp — рыночный риск инвестиционного портфеля.

Формула расчета коэффициента бета следующая:

σpm — ковариация между доходностью инвестиционного портфеля и доходностью рынка;

σ2m — дисперсия доходности рынка.

Оценка эффективности инвестиций на основе коэффициента Трейнора

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

Пример расчета коэффициента Трейнора для оценки эффективности инвестиционного портфеля в Excel

Рассмотрим на примере расчет коэффициента Трейнора для оценки результативности инвестиционного портфеля. Данные использовались аналогичные как для расчета коэффициента Шарпа, отличие заключается в использовании доходности рыночного индекса (доходность рынка). На практике, доходность рынка соответствует доходность фьючерса на индекс (RTSI). Формулы расчета коэффициента Трейнора следующие:

Средняя доходность инвестиционного портфеля =СРЗНАЧ(B5:B10)

Коэффициент бета =КОВАР(B5:B10;C5:C10)/ДИСП(C5:C10)

Доходность безрискового актива =0,03% (Расчет аналогичен как при расчете коэффициента Шарпа)

На рисунке ниже показан итоговый результат оценки эффективности управления портфелем. Коэффициент Трейнора составил 0,02, что рыночный риск инвестиции довольно высок, но доходность была показана выше, чем по безрисковому активу.

Расчет коэффициента Трейнора в Excel

Коэффициент Альфа Йенсена для оценки эффективности инвестиций

Коэффициент альфа Йенсена (англ. Jensen index) — размер превышения доходности инвестиционного портфеля над среднерыночной. Данный показатель был разработан Йенсеном в 1968 году и используется для оценки эффективности управления инвестиционными портфелями. Формула расчета коэффициента Альфы Йенсена следующая:

rp — средняя доходность инвестиционного портфеля;

rf — средняя доходность безрискового актива;

rf — среднерыночная доходность;

βp — рыночный риск инвестиционного портфеля.

Под среднерыночной доходностью подразумевают доходность рынка, которая равна доходности индекса РТС.

Оценка инвестиционного портфеля на основе Альфы Йенсена

Чем выше значение коэффициента Альфы Йенсена, тем эффективнее активная стратегия управления на пассивной (вложение в рыночный индекс). В таблице ниже показана оценка данного показателя.

Пример расчета коэффициента Альфа Йенсена для оценки эффективности инвестиционного портфеля в Excel

Рассмотрим на примере расчет показателя Йенсена для инвестиционного портфеля. Данные по уровню доходности и уровням риска у нас уже есть. Формула в Excel будет иметь следующий вид:

Коэффициент Альфа Йенсена = D5-(F5+E5*(G5-F5))

Расчет Альфы Йенсена в Excel

Коэффициент Йенсена составил 0,01 (1%). Так как он имеет положительное значение, это говорит об эффективном управлении портфелем и получение доходности выше среднерыночной в среднем на 1%. Активная стратегия управления инвестициями оправдывает себя.

Коэффициент Модильяни для оценки эффективности инвестиций

Коэффициент Модильяни (аналог: индекс Модильяни, коэффициент М2) — показатель отражающий эффективность управления инвестиционным портфелем. Данный показатель был предложен Франко Модильяни в 1997 г. и позволяет сравнивать между собой различные инвестиционные портфели.

Индекс Модильяни. Формула расчета

rp — средняя доходность инвестиционного портфеля;

rf — средняя доходность безрискового актива;

βp — рыночный риск инвестиционного портфеля;

σm — стандартное отклонение доходности рынка;

σp — стандартное отклонение доходностей инвестиционного портфеля (риск портфеля).

Оценка коэффициента Модильяни

Чем выше значение коэффициента Модильяни, тем более результативно управлялся инвестиционный портфель по отношению к бенчмарку (безрисковому активу) при соответствующем уровне риска.

Пример расчета коэффициента Модильяни для оценки эффективности инвестиций в Excel

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

Расчет коэффициента Модильяни в Excel

Резюме

В данной статье мы рассмотрели наиболее часто используемые показатели оценки эффективности инвестиций (инвестиционных портфелей, ПИФов и т.д.). На практическом примере рассчитали данные коэффициенты в Excel. Использование методов оценки позволяет выделить наиболее эффективные и результативные стратегии управления на фондовом рынке или пересмотреть и модифицировать уже существующие. Расчет коэффициентов позволяет минимизировать риски вложения в финансовые инструменты, применяйте их в своей инвестиционной практике.

Портфель взвешенный по капитализации или в равных долях (часть 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 акций голубых фишек, то, скорее всего, больше половины веса будет приходиться на нефтегаз. А это с точки зрения диверсификации — не есть гуд. И желательно такой портфель разбавить акциями из других отраслей.

Этот же принцип работает и в обратную сторону. Мы можем посмотреть на шаблонный вариант портфеля и заметить, что нам не хватает какого-нибудь сектора (доля которого в портфеле презрительно мала или вообще отсутствует). Это будет нам сигнал к размышлению.

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

К примеру, глядя на диаграммы ниже, я сразу вижу, что доля сектора «Металл и добыча» у меня намного больше запланированного. А вот сектор «Нефтегаз» сильно отстает. Следовательно, мне нужно направлять в него все новые деньги в первую очередь. И пока не вкладываться в Металлы.

Диаграммы портфелей

Файл-шаблон

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *