Меню
Меню

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

Пошаговая инструкция для Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

В результате мы получили среднюю доходность в годовых процентах за анализируемый временной период по искомым бумагам. Она составила 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% годовых.

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

Больше интересных материалов