Определяем нужное количество акций с помощью Excel
Составление индексного портфеля — это процесс, который потребует в самом начале большого количества операций. Если в индекс, который вы собираетесь скопировать, входит 40 акций, то вам необходимо совершить 40 сделок. Такое количество операций может привести к множеству ошибок, которые повлияют на инвестиционный результат. Избежать этого можно, если перед совершением сделок вы составите план покупок, в котором укажете, какие акции и в каком количестве покупать.
Процесс составления портфеля осложняется также тем, что акции продаются лотами, состоящими более чем из одной акции. Например, у вас есть 400 тыс. руб., и вам нужно, чтобы доля «Северстали» в портфеле составляла 2,08%. Для этого вам потребуется купить восемь акций (2,08% от 400 тыс. руб. — 8,32 тыс., стоимость одной акции в примере — 1 034 рубля). Но «Северсталь» продаётся лотами по десять штук, соответственно, вы можете купить либо один лот, либо не включать акции этой металлургической компании в портфель. Подобная ситуация будет повторяться для каждой позиции в портфеле. В итоге это приведёт к тому, что после ваших покупок останется значительная сумма, которую необходимо распределить. Процесс составления плана покупок можно существенно ускорить, если воспользоваться Microsoft Excel. Процесс автоматизации будет показан на примере составления портфеля, копирующего Индекс голубых фишек Московской биржи. Предполагается, что вы уже имеете навыки работы с Microsoft Excel.
Перед составлением плана покупок необходимо определиться, какие акции и в каких пропорциях будете покупать. Состав Индекса МосБиржи можно посмотреть на сайте Московской биржи. В нашем случае доли акций в портфеле должны быть следующими.
Теперь запускаем Excel и составляем таблицу.
После заполнения исходных данных необходимо ввести расчётные формулы. В ячейку D2 введите следующее:
=ОКРВНИЗ($J$2*B2/C2;1)
Функция ОКРВНИЗ предназначена для округления значения (указывается в первом аргументе функции) до ближайшего целого кратного второму аргументу. В нашем случае первый аргумент — это выражение $J$2*B2/C2, второй аргумент — единица. В данном контексте знак доллара ($) является служебным символом, который указывает Excel, как обрабатывать формулу при копировании. Это будет показано позднее. Когда вы введёте формулу, нажмите «Enter». Если всё сделано правильно, то в ячейке появится значение 150.
Обратите внимание на небольшой квадратик в правом нижнем углу выделенной ячейки (на рисунке выделен красным). Наведите курсор мыши на него, и он изменит свой вид — станет небольшим чёрным крестиком. Если вы в этот момент зажмёте левую кнопку мыши и поведёте курсор мыши вниз, то формула, сохранённая в ячейке D2, будет тиражироваться на ячейки под указателем мыши.
Если вы кликните по одной из ячеек, то увидите, что Excel соответствующим образом изменил формулу, но адрес ячейки J2 остался неизменным. Это произошло из-за того, что мы вставили знак доллара. Наличие этого символа в адресе указывает Excel, что при тиражировании ячейки часть адреса, которая следует за спецсимволом, изменять нельзя.
Продолжим заполнять таблицу. План покупок уже готов. Но, напомню, акции часто продаются не поштучно. Например, «ВТБ» (VTBR) продаётся лотами по 10 000 штук, а нам надо купить 210 084 акции. Значит, мы можем купить либо 21, либо 22 лота. Количество акций в лоте можно посмотреть на сайте Московской биржи. В верхней части главной страницы биржи найдите окно поиска и введите в него биржевой тикер акции.
После этого кликните мышкой по акции в списке, и откроется страница с информацией по акции. Пролистайте вниз до раздела «Параметры инструмента», там найдите строку «Количество ценных бумаг в одном стандартном лоте». С учётом полученных сведений о размере лота введите желаемое количество акций по каждой позиции в столбце «Фактическое количество».
Теперь нужно рассчитать, какова будет стоимость соответствующей позиции в портфеле. Для этого в ячейку F2 введите следующую формулу:
=E2*C2
Растиражируйте эту формулу по остальным ячейкам в столбце, как это сделали для «Расчётного количества». В итоге получим примерно следующее.
Давайте подсчитаем, сколько денег останется в результате совершения сделок. Для этого в ячейку F17 введите следующую формулу:
=J2-СУММ(F2:F16)
Результат будет примерно следующим.
На этом подготовка к составлению плана покупок завершена. Все расчёты, которые понадобятся при этом — автоматизированы. Вам достаточно лишь изменять значения в столбцах «Фактическое количество» и «Всего денег», Excel автоматически пересчитает стоимость соответствующей позиции и денежный остаток. При желании вы можете усложнить формулу расчёта фактической стоимости позиции, чтобы она учитывала комиссии брокера.
Источник: https://journal.open-broker.ru/investments/kak-samomu-sostavit-indeksnyj-portfel/
Подписывайтесь на Открытый журнал, чтобы не пропустить новые публикации на канале!
