Эффективная граница марковица в excel

Обновлено: 08.07.2024

Модели формирования инвестиционного портфеля, такие как модель Г.Марковица и У.Шарпа (Capital Asset Price Model) хорошо работают в периоды стабильного роста национальной экономики.

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

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

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

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

Формула в Excel будет выглядеть следующим образом:
=(A3-A2)/A2

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

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

Для упрощения расчета посчитаем сначала знаменатель коэффициента бета, он для всех акций будет одинаков, а после числитель.
Столбец знаменателя (L) рассчитывается по формуле:
=СТЕПЕНЬ((K3-$K$15);2)

И в ячейке L14 происходит расчет непосредственно знаменателя по формуле:
=СУММ(L3:L13)
Для вычисления числителя по периодам коэффициента бета сначала воспользуемся формулой.
=(F3-$F$14)*(K3-$K$15) (Для акций Аэрофлота, колонка М)

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

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

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

В Excel формула будет следующая:
Сначала рассчитываем остаточный риск на каждый период:
=СТЕПЕНЬ((F3-$F$14-$M$15*M3);2)

После рассчитываем остаточный риск за все периоды (R14-V14):
=СРЗНАЧ(R3:R13)

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

Риск единично портфеля равен (U15):
=КОРЕНЬ(L14/12)

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

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

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

Формула расчета целевой ячейки с доходностью портфеля (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%.

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

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

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

Представляем вашему вниманию статистический метод расчета справедливой стоимости акций. Речь идет о регрессионном анализе. Незаменимую помощь в процессе исследования окажет обычный Excel.

Что такое регрессия

Регрессионный анализ является статистическим методом исследования. Он позволяет оценить зависимость одной (зависимой) переменной от других (независимых) переменных. Самой простой является линейная регрессия. Ее формула такова:

Y = a0 + a1x1 + … + anxn

где Y — зависимая переменная,
x — независимые переменные, влияющие на нее,
a — коэффициенты регрессии.

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

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

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

Пример расчетов в Excel и выводы

В качестве примера возьмем акции американского нефтегазового гиганта Exxon Mobil (XOM). Модель будет упрощенной и учебной и не является рекомендацией для осуществления операций с бумагами, ситуацию нужно смотреть в комплексе.

Независимыми переменными у нас выступят фьючерсы на американскую нефть WTI (склеенные фронтальные контракты) и индекс S&P 500. Логика проста — бизнес компании зависит от цен на нефть, а поведение акций в теории должно быть связано в общерыночной ситуацией.

Шаг 1. Выкачиваем в Excel котировки XOM, SPX и CL1. Данные возьмем за пять лет. Так как на более длительных периодах наблюдалась разная структурная ситуация на нефтяном рынке. Возьмем статистику в недельной разбивке, будет 262 наблюдения.

Шаг 2. Активируем настройку регрессионного анализа. Открываем раздел Файл. Переходим на вкладку Параметры Excel — Надстройки. Внизу появившегося окна будет вкладка Управление, где стоит параметр Надстройки Excel, жмем — Перейти.

Выбираем опцию Пакет анализа.

Готово. Результат появится в разделе Данные — Анализ данных.

Шаг 3. Строим регрессию. При клике на Анализ данных появится меню с опциями функционала для анализа. Выбираем Регрессия.

Заполняем окна по аналогии со схемой, используя ранее выгруженные данные по активам.

На выходе получаем вот такие данные.

Шаг 4. Интерпретация. Статистических показателей много. Не вдаваясь в теорию, наиболее интересными являются значения коэффициентов регрессии и показатель R2.

Наша модель будет иметь следующий вид:

Цена акций Exxon Mobil = $96,2 + 0,28*WTI — 0,01*S&P 500

R — квадрат равен 0,61. Показатель показывает, насколько значение зависимой переменной определяется значениями независимых переменных. Речь идет о статистической значимости модели. Модель является очень хорошей, если R2 превышает 0,8, и при этом сама модель имеет экономическое обоснование. В нашем случае все не настолько идеально, но все же выше 0,5, поэтому модель можно использовать.

Отмечу, что в процессе подготовки материала делались расчеты не только за пять лет, но и за 10, и за три года, также WTI заменялась на Brent. Итоговый вариант был выбран в связи с наибольшим значением R2.

Шаг 5. Применение. Рассчитаем в Excel теоретические значения акций Exxon за весь использовавшийся для построения модели период (5 лет).

Построим линейную диаграмму, на которой будут представлены динамика фактической цены и расчетной цены акций. Заметно, что расхождения между двумя величинами редко носили слишком серьезный характер. По состоянию на 06.06.2019 фактическая цена акций составила $74,2, а теоретическая — $76,7. Исходя из этого, критерия бумаги вполне справедливо оценены рынком. Однако это только один, причем упрощенный подход. Ситуацию нужно рассматривать в комплексе. К примеру, медианный таргет аналитиков на 12 месяцев равен $84. Это усредненный показатель результатов моделей фундаментальной оценки, предполагающий заметный потенциал роста.

Корреляционный анализ

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

Строим корреляционную матрицу. В том же разделе Анализ данных выбираем опцию Корреляция. Заполняем окно, как показано ниже, с учетом котировок наших активов.

На выходе получаем корреляционную матрицу. На ней видно, что цена Exxon положительно связана с WTI (коэффициент корреляции = 0,55) и отрицательно зависит от динамики индекса S&P 500 (коэффициент корреляции = -0,48).

Так что Exxon — это преимущественно нефтяная история, зачастую не совпадающая по динамике с широким рынком. Это можно заметить на графике трех активов с 2010 г. Ситуация стала такой с 2014 г., когда рынок нефти обвалился из-за структурных сдвигов. На нашей выборке за 5 лет корреляция между WTI и S&P 500 равна 0,13, то есть несущественна.

Построение графика простой регрессии

Расскажем об еще одном регрессионном функционале Excel. Программа позволяет построить график линейной регрессии. Правда доступно это лишь при наличии одной независимой переменной. В нашем случае ею будет нефть, так как она в большей мере объясняет движения акций Exxon — коэффициент регрессии равен 0,28 против (-0,01) у S&P 500.

Строим точечную диаграмму по XOM и WTI за 5 лет. Получаем поле корреляции. Щелкаем по любой из точек на диаграмме и меню левой кнопки мыши выбираем Добавить линию тренда.

В окне выбираем линейную линию тренда, ставим галочки напротив Показывать уравнение и Поместить на диаграмму R2.

В итоге получим такую схему зависимости Exxon (y) от WTI (x). В нашем случае модель не является статистически значимой — R-квадрат равен лишь 0,3.

Как еще использовать корреляционно-регрессионный анализ

В архивах раздела Обучение БКС Экспресс есть материалы на эту тему.

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

БКС Брокер

Зарегистрируйтесь, чтобы иметь возможность оставлять комментарии. Уже есть аккаунт? Войти Покупайте ценные бумаги любимых брендов в один клик Зарегистрируйтесь, чтобы управлять панелью котировок. Уже есть аккаунт? Войти Покупайте ценные бумаги любимых брендов в один клик Совет директоров Qiwi дал рекомендации по дивидендам за III квартал 2021

Все, что нужно знать об оценке REITs

Доллар достиг ключевой цели

«Префы» Сургутнефтегаз упали на панике. Можно ли на этом заработать

Ход торгов. Рынок акций растет после мощного падения

4 акции. Игра в ротацию

3 лидера падения. Какие перспективы

Главный защитный актив на фоне общего краха

NVIDIA уходит вниз более чем на 8%. Что делать с акциями

* Материалы, представленные в данном разделе, не являются индивидуальными инвестиционными рекомендациями. Финансовые инструменты либо операции, упомянутые в данном разделе, могут не подходить Вам, не соответствовать Вашему инвестиционному профилю, финансовому положению, опыту инвестиций, знаниям, инвестиционным целям, отношению к риску и доходности. Определение соответствия финансового инструмента либо операции инвестиционным целям, инвестиционному горизонту и толерантности к риску является задачей инвестора. ООО «Компания БКС» не несет ответственности за возможные убытки инвестора в случае совершения операций, либо инвестирования в финансовые инструменты, упомянутые в данном разделе.

Информация не может рассматриваться как публичная оферта, предложение или приглашение приобрести, или продать какие-либо ценные бумаги, иные финансовые инструменты, совершить с ними сделки. Информация не может рассматриваться в качестве гарантий или обещаний в будущем доходности вложений, уровня риска, размера издержек, безубыточности инвестиций. Результат инвестирования в прошлом не определяет дохода в будущем. Не является рекламой ценных бумаг. Перед принятием инвестиционного решения Инвестору необходимо самостоятельно оценить экономические риски и выгоды, налоговые, юридические, бухгалтерские последствия заключения сделки, свою готовность и возможность принять такие риски. Клиент также несет расходы на оплату брокерских и депозитарных услуг, подачи поручений по телефону, иные расходы, подлежащие оплате клиентом. Полный список тарифов ООО «Компания БКС» приведен в приложении № 11 к Регламенту оказания услуг на рынке ценных бумаг ООО «Компания БКС». Перед совершением сделок вам также необходимо ознакомиться с: уведомлением о рисках, связанных с осуществлением операций на рынке ценных бумаг; информацией о рисках клиента, связанных с совершением сделок с неполным покрытием, возникновением непокрытых позиций, временно непокрытых позиций; заявлением, раскрывающим риски, связанные с проведением операций на рынке фьючерсных контрактов, форвардных контрактов и опционов; декларацией о рисках, связанных с приобретением иностранных ценных бумаг.

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

В данной статье при помощи средств Ecxel создается оптимальный по Г. Марковицу портфель для акций российского фондового рынка.

(1)

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

(2)

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

(3)

(4)

Экономико-математическая модель задачи формирования оптимального портфеля акций максимальной эффективности при которой риск портфеля не превышает заданного значения δ_р , и при учете всех ограничений на портфель, примет следующий вид (5):

(5)

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

(6)

Пример составления портфеля Марковица для российского фондового рынка

Для примера возьмем акции 4-х российских компаний Газпром (GAZP), Дальсвязь(DLSV), Сургутнефтегаз (SNGS) и Роснефть (ROSN). Построим на основе котировок оптимальный портфель. Для нахождения оптимального портфеля по Марковицу воспользуемся средствами Excel и компонентой Solver(Поиск решений).
Для начала рассчитаем дневную доходность по каждой акции за один год с 20.05.2009 по 20.05.2010.
Формула расчета дневной доходности (m_j) представлена (7): ;

(7)

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

(8)

Посчитав, среднедневная доходность за весь период составила для GAZP = -0,02%, DLSV =0,28%, SNGS =0,05% и ROSN= 0,08%. Так как средняя доходность Газпрома отрицательная то эта акция не будет включена в портфель. Помимо доходности необходимо рассчитать риск этих акций, для этого рассчитаем среднеквадратическое отклонение дневных доходностей акций по формуле (9).

(9)

Для Дальсвязи (DLSV) δ₁ =1,73% , Сургут нефтегаза (SNGS) δ₂ = 1,98% и Роснефти (ROSN) δ₃ = 2,05%. Составим уравнение для нахождения оптимального портфеля. Так же зададим допустимый максимальный уровень риска в 0,15%.

(10)

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

После запуска надстройки «Поиск решений» установим целевую функцию, это доходность всего портфеля. После этого поставим флажок на максимизации значения этой целевой функции. Ячейки для изменения будут соответствовать доли акций, которые необходимо найти. Так же необходимо наложить ограничения на то что бы сумма всех долей была равна 1, и что бы каждая доля была не отрицательна и общий риск портфеля был бы меньше 0,15%.
В ячейке с общим риском (D7) прописывается следующая формула:
=КОРЕНЬ(0,0301*D2*D2+0,0393*D3*D3+0,0421*D4*D4)
Целевая функция в ячейке (F3) равняется:
=D2*B2+D3*B3+D4*B4
В ячейке (D6) прописывается ограничение для портфеля:
=СУММ(D2:D4)
После проделанной работы определятся доли в инвестиционном портфеле для каждой акции.

Расчет оптимального портфеля по Г. Марковицу представлен на рисунке ниже. Оптимальный портфель будет состоять из 85,38% акций Дальсвязи (DLSV), 3,66% акций Сургутнефтегаза (SNGS) и 10,96% акций Роснефти (ROSN). Доходность всего портфеля будет составлять 0,24% при общем установленном заранее риске портфеля в 0,15%.

Модель эффективного портфеля Дж Тобина предназначена для создания оптимальных портфелей активов c использованием безрисковых финансовых инструментов.

В модели Тобина в отличие от модели Г. Марковица предполагается наличие так называемых безрисковых активов, доходность которых не зависит от состояния рынка и имеет постоянное значение. Кроме того, в модели Дж. Тобина допустимыми являются любые портфели, это значит, что допустимы не только покупки акций, но и продажи. Поэтому доли акций (x_i) могут принимать и отрицательные значения. Единственное ограничение на портфеля – сумма всех долей должна равняться 1, включая и долю безрискового актива (x₀).

(1)

Доходность портфеля по Тобину будет выглядеть как сумма доходностей отдельных акций (mi) с выбранными весовыми коэффициентами, а также доходность безрискового актива(m₀). Каждый инвестор пытается максимизировать получаемую доходность, что представлено формулой (2).

(2)

(3)

Экономико-математическая модель задачи формирования оптимального портфеля по Тобину, максимальной доходности, при которой риск портфеля не превышает заданного значения δ_р , и при учете всех ограничений на портфель, примет следующий вид (4):

(4)

Экономико-математическая модель задачи формирования портфеля минимального риска имеет вид, где: mp- требуемая норма доходности (5).

(5)

(6)

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

(7)

Посчитав, среднедневная доходность акций за весь период составляет SBER = 0,28%, AFLT =0,23%. Среднедневная доходность краткосрочных государственных облигаций = 0,004%. Зададим необходимую дневную доходность для портфеля m_p = 0,28% . Помимо доходности необходимо рассчитать риск этих акций, для этого рассчитаем среднеквадратическое отклонение дневных доходностей акций по формуле (8).

(8)

Для Сбербанка(SBER) δ₁ =2,50% , Аэрофлота (AFLT ) δ₂ = 1,88% . Для краткосрочных государственных облигаций риск равен δ₀ =0. Составим уравнение для нахождения оптимального портфеля. Корреляция (r_ij) между акциями Сбербанка и Аэрофлота составляет 0,458.

(9)

После запуска надстройки «Поиск решений» установим целевую функцию, это риск всего портфеля. После этого поставим флажок на минимизации значения этой целевой функции. Ячейки для изменения будут соответствовать доли акций, которые необходимо найти. Так же необходимо наложить ограничения на то что бы сумма всех долей была равна 1, и что бы дневная доходность портфеля составляла 0,23% .
В ячейке целевой функции общего риска портфеля(F7) прописывается следующая формула:
==КОРЕНЬ(2,5*2,5*D2*D2+1,88*1,88*D3*D3+2*0,45*D2*D3*C2*C3)
Функция, определяющая необходимую доходность, прописана в ячейке D7.
=D2*B2+D3*B3+D4*B4
В ячейке (D6) прописывается ограничение для портфеля:
=СУММ(D2:D4)
После проделанной работы определятся доли в инвестиционном портфеле для каждой акции.

Расчет оптимального портфеля по Тобину представлен на рисунке ниже. Оптимальный портфель будет состоять из 34,64% акций Сбербанка, 57,70% Аэрофлота и 7,66% безрисковых государственных ценных бумаг. Риск всего портфеля будет составлять 1,66%.

Читайте также: