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

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

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

В нашем примере будет использован более простой подход — составление портфеля из нескольких американских акций. Для эффекта диверсификации возьмем представителей различных секторов — платежную систему VISA, ритейлера Macy’s, технологичного гиганта Apple и телеком AT&T.

Сразу отмечу, что это лишь пример. Все эмитенты интересны, но для грамотного составления портфеля необходимо учитывать фундаментальные показатели, включая рыночные мультипликаторы, оценивать технические уровни для входа в позицию.
Этап 1. Выкачиваем котировки. Необходимо взять данные минимум за год. В нашем примере были взяты ежемесячные цены закрытия с 31.06.2017 по 31.05.2018.
Этап 2. Считаем доходности по каждой бумаге. Для простоты не будем учитывать эффект дивидендов.

Считаем доходность за каждый месяц по формуле натурального логарифма. К примеру, доходность VISA за май 2018 = LN(C14/C13)

Для расчета ожидаемой доходности берем среднее значение за рассматриваемый период. В нашем случае это год. Ожидаемая доходность VISA = СРЗНАЧ(G3:G14)

Получаем отрицательную доходность AT&T, и убираем бумагу из портфеля. Сразу отмечу, что в этом заключается недостаток модели, ведь просевшие ранее акции в перспективе могут развернуться.
Этап 3. Расчет риска каждой акции. Производится по формуле стандартного отклонения. К примеру, риск VISA =СТАНДОТКЛОН(G3:G14)

Этап 4. Расчет ковариаций между бумагами. Воспользуемся специальной надстройкой в Excel. Для этого выберем в Главном меню → «Данные» → «Анализ данных» → «Ковариация».

Указываем окне входной интервал — ежемесячные доходности акций, а в опции «Группирование» выбираем «по столбцам».

В результате получаем ковариационную матрицу.

Этап 5. Расчет общей доходности портфеля. Для начала установим произвольные доли бумаг в портфеле. Они положительны, их сумма равна 1.

Считаем средневзвешенное значение доходностей отдельных акций. Воспользуемся формулой G15*G23+H15*H23+I15*I23

Этап 6. Расчет общего риска портфеля. Производится по формуле массива КОРЕНЬ(МУМНОЖ(МУМНОЖ(G23:I23;G20:I22); E20:E22))

Этап 7. Портфель минимального риска.

Речь идет о долях отдельных бумаг в портфеле. Для начала необходимо определить минимальный уровень допустимой доходности портфеля (rp). Возьмем rp >= 3,2%.

При оценке долей акций воспользуемся надстройкой в Excel «Поиск решений», для этого выбираем Главное меню → «Данные» → «Поиск решений».

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

В результате имеем портфель с 73% долей VISA и 27% долей Macy’s.

Визуально портфель выглядит так:

Этап 8. Портфель максимальной доходности.

Для начала необходимо определить максимальный уровень допустимого риска портфеля (σp). Возьмем σp <= 4%.

В надстройке «Поиск решений» меняем оптимизируемую ячейку на доходность портфеля, ее максимизируем. Меняем ограничение — теперь ограничиваем риск.

В результате имеем портфель с 15% долей VISA и 85% долей Macy’s.

Визуально портфель выглядит так:

Сильные стороны модели Марковица
• Систематизация подхода к формированию портфеля.

• Относительная простота модели. Расчеты может провести любой инвестор в общедоступной программе Excel.

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

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

• Риск финансового инструмента оценивается с помощью среднеквадратичного отклонения. Однако позитивное изменение доходности выше среднего по факту не является риском.

⇐ Предыдущая 12