Хотите разобраться подробнее в том, что такое структурные продукты и как они работают?
Посмотрите наш новый видеокурс "Структурные продукты – новый уровень инвестиций"

Зарегистрироваться

Ваш город?

Москва и область

Расчёт доходности портфеля в Excel

 

Содержание:

Получение исторических котировок

Расчёт доходности портфеля в программе MS Excel

Материалы по теме

 

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

Получение исторических котировок

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

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

 

Рис.1. Список авторизованных распространителей биржевой информации

Рис.1. Список авторизованных распространителей биржевой информации

 

В качестве примера рассмотрим, как в Excel рассчитать доходность портфеля, состоящего из бумаг компаний Сбербанк, НЛМК и «Яндекс». Исторические котировки для примера получим на ресурсе mfd.ru («МФД-ИнфоЦентр Плюс»). Для реализации нашей цели следует выбрать в меню «Котировки» пункт «Экспорт в MetaStock».

 

Рис.2. Выбор в меню «Котировки» пункта «Экспорт в MetaStock»

Рис.2. Выбор в меню «Котировки» пункта «Экспорт в MetaStock»

 

Следующий шаг – выбрать интересующие активы (в нашем примере – «МосБиржа Акции и ПИФы») и поместить их в раздел «Выбранные тикеры» (в примере мы рассмотрим Сбербанк, «НЛМК» АО и Yandex cla). Далее выбираем таймфрейм и временной интервал (для удобства визуализации процесса мы выбрали месячный таймфрейм и интервал, равный одному году), далее в поле «Формат» следует выбрать «Текстовый» и «Все тикеры в одном файле», после чего вводим «Имя файла» - «Портфель.txt», выбираем в разделе «Разделитель полей» пункт «Табуляция», выбираем в поле «Десятичный разделитель» пункт «Запятая», вводим в поле «Формат даты/времени» «dd/MM/yy, hhmmss», отмечаем галочкой пункт «Добавить заголовок файла», вводим «Формат записи» «TICKER, PER, DATE, TIME, CLOSE, VOLUME» и нажимаем на клавишу «Получить данные».

 

Рис.3. Получение исторических данных

Рис.3. Получение исторических данных

 

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

 

Рис.4. Полученные данные в программе «Блокнот»

Рис.4. Полученные данные в программе «Блокнот»

 

Полученные данные следует скопировать в программу MS Excel для проведения дальнейших расчётов.

 

Рис.5. Данные в Excel

Рис.5. Данные в Excel

Расчёт доходности портфеля в программе MS Excel

Далее приступим к расчёту доходности представленных активов за период относительно предыдущего. Для этого вводим в ячейку свободного столбца формулу расчёта доходности, в которой мы из цены закрытия периода вычитаем цену закрытия предыдущего периода, делим данную разность на цену закрытия предыдущего периода и умножаем частное от деления на 100 для приведения к процентной форме. Для этого мы в ячейку G3 вводим формулу «=(E3-E2)/E2*100» для вычисления доходности за месяц по бумаге Yandex cla.

 

Рис.6. Ввод формулы расчёта доходности за период относительно предыдущего периода

Рис.6. Ввод формулы расчёта доходности за период относительно предыдущего периода

 

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

 

Рис.7. Автозаполнение формулы расчёта доходности за период относительно предыдущего периода

Рис.7. Автозаполнение формулы расчёта доходности за период относительно предыдущего периода

 

Следующий этап – вычислить среднюю годовую доходность за выбранный промежуток времени. Для выполнения поставленной задачи следует в свободную ячейку ввести формулу, вычисляющую среднее значение от сформированного диапазона, и умножить на 12 (так как мы анализировали средние доходности за 12 месяцев). В нашем примере мы ввели в ячейку G15 формулу «=СРЗНАЧ(G3:G14)*12» и получили среднюю годовую доходность за анализируемый период.

 

Рис.8. Вычисление средней доходности за годовой период

Рис.8. Вычисление средней доходности за годовой период

 

Далее следует проделать аналогичные действия для оставшихся бумаг.

 

Рис.9. Вычисление средней доходности за годовой период

Рис.9. Вычисление средней доходности за годовой период

 

В результате мы получили среднюю доходность в годовых процентах за анализируемый временной период по искомым бумагам. Она составила 39,68% по Yandex cla, 28,10% по НЛМК и 36% по Сбербанку.

После получения годовой доходности по искомым бумагам можно приступать к вычислению доходности портфеля, состоящего из данных бумаг. Для примера предположим, что мы решили сформировать портфель в следующих процентных долях: 40% – «Яндекс», 20% – НЛМК и 20% – Сбербанк. В общем виде доходность портфеля равна сумме произведений доходностей входящих в него бумаг на их процентные доли. Следовательно, расчёт доходности рассматриваемого портфеля можно представить как 0,4*39,68+0,2*26,10+0,2*36. Для произведения представленных вычислений в Excel в ячейку G18 (или в любую другую) введём формулу «=G15*0,4+N15*0,2+U15*0,2». Таким образом, доходность портфеля составит 28,29% годовых.

 

Рис.10. Вычисление доходности портфеля

Рис.10. Вычисление доходности портфеля

Вывод

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

Материалы по теме:

Видеокурс «Инвестирование в облигации»

Открытый вебинар «Обучение торговле акциями: полное руководство к действию»

 

22.01.2019

Зарегистрируйтесь и оставляйте
свои комментарии

Зарегистрироваться

Войти через соцсети

Комментарии

4

Отзывов нет

Избранные материалы