1.2.7. Использование программы Excel для расчета риска портфеля ценных бумаг

Рассмотрим использование программы для расчета риска портфеля на примерах.

Пример 1.

Определить риск портфеля, состоящего из двух бумаг X и F, если вх = 0,3 ; ву = 0,7; ох = 20,8%; Вводные пояснения и приемы работы с программой Excel приведены в параграфе 1.1.6.

48

Глава 1.

Решение.

Печатаем в ячейке А1 уд. вес бумаги X (0,3), в ячейке А2 - бумаги Y (0,7), в ячейках В1 и В2 соответственно - стандартные отклонения доходностей бумаг Х(20,8) и F(25,4). В ячейке С1 печатаем ковариацию доходностей бумаг (3,08). Решение получим в ячейке С2, поэтому наводим на нее курсор и щелкаем мышью. Печатаем в ячейке С2 формулу риска портфеля, представленную дисперсией:

=А1Л2*В1Л2+А2Л2*В2Л2+2*А1*А2*С1

и нажимаем клавишу Enter. В ячейке С2 появилось решение задачи - цифра 356,3596. Данный ответ является дисперсией портфеля. Найдем стандартное отклонение доходности портфеля в ячейке СЗ. Это можно сделать двумя способами, а) Если ячейка СЗ не выделена, то наводим на нее курсор и щелкаем мышью. После этого печатаем в ней формулу:

=КОРЕНЬ(С2)

и нажимаем клавишу Enter. В ячейке СЗ появилась цифра 18,8749. Таким образом, стандартное отклонение портфеля составляет 18,8749%.

б) Извлечь квадратный корень из числа можно с помощью программы "Мастер функций". Для этого выбираем курсором на панели инструментов значок Щ и щелкаем мышью. Появилось окно "Мастер функций". В левом поле ("Категория") выбираем курсором строку "Математические" и щелкаем мышью.22 В правом поле окна ("Функция") курсором выбираем строку "КОРЕНЬ" и щелкаем мышью. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно "КОРЕНЬ". В строку "Число" заносим номер ячейки С2. Для этого наводим курсор на знак Щ справа от строки и щелкаем мышью. Окно "КОРЕНЬ" превратилось в поле строки. Наводим курсор на ячейку С2 и щелкаем мышью.

Пример 2.

Портфель состоит из трех бумаг - X, F, Z. Уд. вес бумаги X равен 0,2, бумаги Y- 0,3, бумаги Z - 0,5; Определить риск портфеля.

Решение.

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

См. сноску 7.

49

Глава 1. Ожидаемая доходность и риск портфеля

Вначале введем в ячейки исходные данные. В ячейках с А1 по A3 печатаем уд. веса бумаг (см. рис. 1.17), в ячейках Bl, С2 и D3 соответственно - дисперсии доходностей бумаг А" (900), F(400) и Z(100). В ячейках В2 и С1 - ковариации доходностей бумаг X и К, в ячейках ВЗ и D1 - ковариации доходностей бумаг X и Z, в ячейках СЗ и D2 - ковариации доходностей бумаг Y и Z. Цифры, которые расположены в три столбца в ячейках от В1 до ВЗ, С1 до СЗ и D1 до D3 представляют собой не что иное как ковариационную матрицу. По ее диагонали стоят дисперсии доходностей бумаг, на остальных местах - ковариации бумаг. Матрицу, как единый блок, для целей вычислений обозначают с помощью адресов ее угловых ячеек (верхней левой и нижней правой), разделяя их двоеточием. Поэтому ковариационная матрица в примере обозначается как B1:D3.

Уд. веса бумаг в столбце А1:АЗ представляют собой матрицу столбец. Согласно формуле (1.39) необходимо также получить матрицу строку уд. весов, т.е. транспонировать матрицу А1:АЗ. Получим транспонированную матрицу в ячейках А5:С5. Это делается следующим образом. Наводим курсор на ячейку А5, нажимаем левую клавишу мыши и, удерживая ее, проводим мышью до ячейки С5 и отпускаем клавишу.

=ТРАНСП(А1:АЗ)

После этого одновременно нажимаем клавиши Ctrl, Shift и Enter (удобно вначале одновременно нажать клавиши Ctrl и Shift и после этого Enter). В ячейках А5, В5 и С5 соответственно появятся цифры 0,2, 0,3 и 0,5. На рис. 1.17 представлен лист Excel с подготовленными данными для вычисления риска портфеля.

|ЦКнига!,Риск портфеля.к!» А ! 8 С ] о 1..........г 900 3,8 2,5 2 0,3 3,8 400 5,5 3 0,5 2,5 5,5 100 4 s! 0,2 0,3 0,5 \ б ;

Рис. 1.17. Данные для расчета риска портфеля

Осуществим перемножение матриц последовательно. Вначале перемножим транспонированную и ковариационную матрицы. В результате умножения матриц получим матрицу строку из трех ячеек. Поэтому выделим для получения ответа интервал А7:С7. Для этого наводим курсор на ячейку А7, нажимаем левую клавишу мыши и, удерживая ее, доводим мышь до ячейки С7 и отпускаем клавишу. В выделенной строке печатаем формулу:

=МУМНОЖ(А5:С5;В1 :D3)

и одновременно нажимаем клавиши Ctrl, Shift и Enter. В ячейках получаем цифры как показано на рис 1.18.

50

Глава 1. Ожидаемая доходность и риск портфеля

?I Книга 1 .Риск портфеля.к15

А 1 В 1 С 1 D ! Е F и

2 ' 0.2 900 3,8 2,5

0,3 3,8 400 5.5 3 1 0,5 2,5 5,5 100 4 5; 0,2 0,3 0,5 6 iL 182,39 ^ 8 4

Рис. 1.18. Расчет риска портфеля

Теперь перемножим полученную в ячейках А7:С7 матрицу строку на матрицу столбец в ячейках А1:АЗ. Умножение дает одну цифру, поэтому для ответа уже известным способом выделяем ячейку Е7 и печатаем в ней формулу:

=МУМНОЖ(А7:С7;А1 :АЗ)

и нажимаем Enter. Получаем ответ 99,606.

Решить данную задачу, т.е. транспонировать и перемножить матрицы, можно также с помощью программы "Мастер функций". После того как мы ввели исходные данные по уд. весам бумаг и ковариационную матрицу, алгоритм решения является следующим. Получим ответ в диапазоне ячеек А5:С5. Поэтому наводим курсор на ячейку А5, нажимаем левую клавишу мыши и, удерживая ее, доводим до ячейки С5, отпускаем клавишу.

Теперь перемножим транспонированную матрицу строку диапазона А5:С5 на ковариационную матрицу. Для этого выделяем интервал А7:С7 и открываем окно "Мастер функций". В поле "Категория" мышью выбираем строку "Математические". В поле окна "Функция" мышью выбираем строку "МУМНОЖ" и щелкаем мышью. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно "МУМНОЖ" с двумя строками "Массив 1" и "Массив 2". Наводим курсор на значок Я справа от строки "Массив 1" и щелкаем мышью. Окно "МУМНОЖ" превратилось в поле строки. Наводим курсор на ячейку А5, нажимаем левую клавишу мыши и, удерживая ее, доводим до ячейки С5, отпускаем клавишу. В поле строки появилась запись А5:С5. Вновь наводим курсор на значок 3 и щелкаем мышью. Появилось окно "МУМНОЖ". Наводим курсор на значок !9 справа от строки "Массив 2" и щелкаем мышью. Окно "МУМНОЖ" превра

51

Глава 1. Ожидаемая доходность и риск портфеля

тилось в поле строки. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее, доводим курсор до ячейки D3, отпускаем клавишу. В поле строки появилась запись B1:D3. Вновь наводим курсор на значок Ш и щелкаем мышью. Появилось окно "МУМНОЖ". Одновременно нажимаем клавиши Ctrl, Shift и Enter. В ячейках А7:С7 получили ответ.

Теперь перемножаем матрицу строку А7:С7 и матрицу столбец А1:АЗ. Для этого выделяем мышью ячейку Е7 и открываем окно "Мастер функций". В поле "Категория" выбираем строку "Математические", в поле окна "Функция" -строку "МУМНОЖ". Щелкаем мышью кнопку ОК. В строке "Массив 1" окна "МУМНОЖ" уже известным способом записываем А7:С7, а в строке "Массив 2" - А1:АЗ. Возвращаемся к окну "МУМНОЖ", курсором выбираем кнопку ОК и щелкаем мышью. В ячейке Е7 появился ответ. По результатам решения задачи лист Excel имеет вид как показано на рис. 1.19.

_„. ^ - шГ=МУМНОЩА7:С7;А1:/Й) |П| Книга1.Риск портфеля.к Is ........-........_._......„_.....__________J I А " в Г с Г D | Е I F I "1 0,2 900 3,8 2,5 0,3 3,8 400 5,5 0,5 2,5 5,5 100 _ 0.2 0,3 0.5 6 7 182,39 123,51 52,15 1 99.606j 8

[9 Рис. 1.19. Расчет риска портфеля