Формирование инвестиционного портфеля на основе Quasi-Sharpe model в Excel

Написано admin в Январь 16, 2012. Опубликовано в Рынок ценных бумаг

Бета

Для эффективной работы на нестабильных фондовых рынках была предложена новая модель формирования инвестиционного портфеля, которая получила название модель Quasi-Sharpe. Эта модель основана на взаимосвязи доходности каждой ценной бумаги из всего множества N ценных бумаг с доходностью единичного портфеля их этих бумаг. В общих чертах модель «Квази- Шарпа» сильно походит на модель предложенную У.Шарпом, но есть некоторые отличия. Рассмотрим основные допущения модели «Квази- Шарпа»:

  1. Доходность ценной бумаги рассчитывается как математическое ожидание доходностей. Это допущение есть и в модели Шарпа.
  2. Единичный портфель представляет собой портфель, состоящий из всех рассматриваемых ценных бумаг, взятых в одинаковой пропорции. В модели Шарпа за эталонный портфель(бенчмарк) берется так называемый рыночный портфель, динамику которого часто описывает фондовый индекс. Для российского фондового рынка это индекс РТС (RTSI), для украинского рынка индекс ПФТС, для американского фондового рынка это S&P500.
  3. Доходность ценной бумаги прямо пропорционально доходности единичного портфеля. То же предположение в модели Шарпа для рыночного портфеля.
  4. Риск ценной бумаги рассчитывается как чувствительность изменения доходности ценной бумаги от изменения доходности единичного портфеля. Аналогично для модели Шарпа.
  5. В отличии от модели Шарпа за безрисковую ставку берется средняя доходность единичного портфеля, а не государственные обязательства.

Модель «Квази – Шарпа» соединяет доходность ценной бумаги с доходностью единичного портфеля и риском этой ценной бумаги с помощью функции линейной регрессии. Формула доходности ценной бумаги следующая:
Модель Квази Шарпа
Ri– доходность ценной бумаги;
Rsp– доходность единичного портфеля;
βi– коэффициент чувствительности к изменению доходности ценной бумаги, коэффициент регрессии в уравнении доходности;
Средняя доходность ценной бумаги –средняя доходность ценной бумаги;
Средняя доходность единичного портфеля – средняя доходность единичного портфеля.

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

В модели «Квази-Шарпа » риск ценной бумаги представляет собой совокупность коэффициента бета и остаточного риска (σri). Остаточным риском называют степень разброса значений доходности ценной бумаги относительно линии регрессии.
Доходность в модели «Квази – Шарпа» рассчитывается как:
Доходность в модели Квази Шарпа
Риск же рассчитывается по следующей формуле:
Риск в модели Квази Шарпа
Задача формирования оптимального портфеля по модели «Квази-Шарпа», где мы максимизируем доходность инвестиционного портфеля и устанавливаем допустимый уровень риска, будет выглядеть следующим образом.
Задача формирования инвестиционного портфеля

Обратная задача формирования оптимального портфеля, где мы минимизируем общий риск инвестиционного портфеля с фиксированным уровнем доходности, имеет следующий вид:
Обратная задача формирования порфтеля
Давайте на конкретном примере рассмотрим применение модели «Квази – Шарпа» для российского фондового рынка. Данные по котировкам возьмем с сайта finam.ru. За последний год возьмем котировки таких крупных компаний как Газпром (GAZP), Аэрофлот (AFLT), Якутэнерго (YKEN), Сбербанк (SBER), Лукойл (LKOH) и ГМК Норникель (GMKN). Мы сразу отбросим из рассмотрения акции Газпрома, так как за прошедший год они показали отрицательную прибыль. И так, занесем все данные в таблицу Excel. На рисунке ниже показаны месячные данные по стоимости акций этих компаний с 10.10.2009 по 10.10.2010.

Excel
Следующим этапом рассчитаем доходности этих акций по следующей формуле:
Доходность акции
Где:
Ri– текущая доходность акции;
Pi– текущая стоимость акции;
Pi-1 – стоимость акции в предыдущем периоде.

Формула в Excel будет выглядеть следующим образом:

=(A3-A2)/A2

И аналогично рассчитываем доходности всех акций. На рисунке ниже показан расчет доходности по акциям. Каждый столбец представляет месячные доходности каждой акции.
Следующим этапом, для каждой акции рассчитаем среднее значение доходности за весь год, то есть за все временные отрезки. Формула для расчета следующая:
Средняя доходность
Rit– доходность i-ой акции за период t;
T- рассматриваемое количество временных периодов (в нашем случае 12).
Формула расчета в Excel средней доходности (AFLT) следующая:

=СРЗНАЧ(F3:F13)

Аналогично рассчитываются остальные доходности акций.

Доходность акций российского фондового рынка

Далее рассчитаем доходность единичного портфеля.
Сделав необходимые расчеты, рассчитаем доходности единичного портфеля и его среднюю доходность за все периоды. Доходность единично портфеля представляет собой доходность портфеля составленного из используемых акций, взятых в равных пропорциях.  Доходность единичного портфеля рассчитывается следующим образом:
Доходность единичного портфеля
Где:
Доходность –доходность единичного портфеля;
Средняя доходность единичного портфеля – доходность i-ой ценной бумаги за период t.
Средняя доходность единичного портфеля за все периоды рассчитывается так:
Средняя доходность единичного портфеля
Средняя доходность единичного портфеля – средняя доходность единичного портфеля;
Т –  рассматриваемое количество временных периодов;
Доходность единичного портфеля – доходность единичного портфеля.
В Excel расчет доходностей единичного портфеля (ЕП) будет выглядеть следующим образом:

=СРЗНАЧ(F3:J3)  - для расчета доходности ЕП;
=СРЗНАЧ(K2:K13) – для расчет средней доходности ЕП.

Доходность единичного портфеля excel
Далее рассчитаем чувствительность изменения доходности акции от изменения доходности единичного портфеля. Чувствительность показывает коэффициент бета (β). И формула его вычисления следующая:
Коэффициент бета
Для упрощения расчета  посчитаем сначала знаменатель коэффициента бета, он для всех акций будет одинаков, а  после числитель.
Столбец знаменателя (L) рассчитывается по формуле:

=СТЕПЕНЬ((K3-$K$15);2)

И в ячейке L14 происходит расчет непосредственно знаменателя по формуле:

=СУММ(L3:L13)

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

=(F3-$F$14)*(K3-$K$15) (Для акций Аэрофлота, колонка М)

Аналогично для других акций.
Суммируем полученные результаты за все периоды, то есть непосредственно рассчитываем числителя. Расчеты находятся в ячейках L14-Q14 по формулам:

=СУММ(L3:L13)

Коэффициент бета (β) будет рассчитан как отношение числителей к знаменателю.

=M14/$L$14

Бета
Следующим этапом рассчитаем остаточный риск, который представляет собой степень разброса доходности ценной бумаги относительно линии регрессии. Формула расчета остаточного риска следующая:
Остаточный риск

Сначала рассчитываем остаточный риск на каждый период

=СТЕПЕНЬ((F3-$F$14-$M$15*M3);2)

После рассчитываем остаточный риск за все периоды (R14-V14):

=СРЗНАЧ(R3:R13)

Остаточный риск
При формировании инвестиционного портфеля из этих акций нам еще потребуется рассчитать риск единичного портфеля:
Риск единичного портфеля
Риск единичного портфеля равен (U15):

=КОРЕНЬ(L14/12)

И так, давайте обобщим все полученные данные в таблицу.

Таблица

Для расчета долей в инвестиционном портфеле воспользуемся надстройкой Excel «Поиск решений» или в английском варианте «Solver».

Расчет долей в инвестиционном портфеле на основе модели «Квази – Шарпа»

Создадим новый рабочий лист в Excel и построим следующую таблицу. Используя поиск решений нам необходимо найти доли акций в новом инвестиционном портфеле. На рисунке, они помечены синей колонкой. Перед нами стоит прямая задача максимизации доходность инвестиционного портфеля с ограничением на риск. Максимальный риск установим на отметке 5%. Заполним дополнительные столбцы для расчета доходности и риска.

R*W= B2*G2 – произведение средней доходности и весов;
β*W=G2*C2 – произведение бета акции и веса;
(β*W)^2=I2*I2 – квадрат произведения;
σ^2*W^2=D2*D2*G2*G2 – произведение квадратов;
СУММА W =СУММ(G2:G6) –сумма весов портфеля.

Поиск решений в excel
Формула расчета целевой ячейки с доходностью портфеля (C9) будет следующая.

=СУММ(B2*G2;B3*G3;B4*G4;B5*G5;G6*B6)+F4*СУММ(C2*G2;C3*G3;C4*G4;C5*G5;C6*G6)

Формула расчета риска инвестиционного портфеля:

=КОРЕНЬ(J7*E4*E4+K7)

Для нахождения оптимальной структуры портфеля загрузим надстройку «Поиск решений». Выберем целевую функция – ячейку с доходностью (С9). Ее мы будем максимизировать. Для этого будем изменять доли акций в портфеле – диапазон ячеек C2:G6. Необходимо так же наложить ограничения на риск и веса акций. Веса должны быть положительны, сумма их должна не превышать единицы и риск рассчитанный в ячейке С10 должен быть меньше 5%.

Поиск решений в excel
В итоге мы получаем расчет долей акций в нашем инвестиционном портфеле. В итоге мы получили следующее соотношений весов акций в портфеле. Доля акций Аэрофлота (AFLT) составляет 37.7%, доля акций Якутэнерго (YKEN) составляет 40.5%, доля акций Сбербанка (SBER) 1.3%, доля акций Лукойла (LKOH) 0% и доля акций ГМКНорНикель (GMKN) 20.5%.

Оптимальная структура портфеля

Заключение
И так проведем качественное сравнение трех моделей формирования инвестиционного портфеля: модель Г.Марковица, модель У.Шарпа (CAPM) и модель «Квази – Шарпа».

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

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

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

Теги:, , , , ,

Trackback с вашего сайта.

Комментарии (7)

  • ivan123
    Сентябрь 27, 2013 в 7:39 дп |

    а как скачать этот замечательный файл?

    • admin
      Сентябрь 27, 2013 в 2:48 пп |

      ivan123 тут без файла, а просто пример построения в excel самому. Следуя примеру построить тоже самое не долго – все формулы даны

      • ivan123
        Октябрь 2, 2013 в 1:05 пп |

        Спасибо за ответ. Буду писать на выходных.

  • Женя
    Ноябрь 28, 2013 в 8:01 дп |

    Спасибо, все понял кроме F4 на последнем рисунке? Откуда цифра? Доходность единичного портфеля минус ЧТО? Модуль его же? не пойму

  • zberigach
    Декабрь 16, 2013 в 1:55 дп |

    По-поводу F4 все очень просто…
    Это значение = доходность ЕП в последнем периоде – средняя доходность ЕП. Ячейки (К13-К15)

  • Декабрь 16, 2013 в 12:02 пп |

    Так, все-таки, мне кто-нибудь ответит? Откуда цифра на последнем рисунке F4? Доходность единичного портфеля минус ЧТО? Модуль его же? не пойму.

  • Геннадий
    Январь 10, 2014 в 7:59 дп |

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

Оставить комментарий

Войти

Новости

Изменить "Кол-во"
Ежедневный курс иностранной валюты ЦБ РФ на 15.10.2018
ВалютаКол-воРубль RUB
Продажа (руб.)
Доллар США USD
Евро EUR
Фунт стерлингов Соединенного королевства GBP
Use data from the cache