1.2.7. Использование программы Excel для расчета риска портфеля ценных бумаг
Рассмотрим использование программы для расчета риска портфеля на примерах.
Пример 1.
Определить риск портфеля, состоящего из двух бумаг X и F, если вх = 0,3 ; ву = 0,7; ох = 20,8%; 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.
Портфель состоит из трех бумаг - 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. Это делается следующим образом. =ТРАНСП(А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 на ковариационную матрицу. Для этого выделяем интервал А7:С7 и открываем окно "Мастер функций". В поле "Категория" мышью выбираем строку "Математические". В поле окна "Функция" мышью выбираем строку "МУМНОЖ" и щелкаем мышью. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно "МУМНОЖ" с двумя строками "Массив 1" и "Массив 2". Наводим курсор на значок Я справа от строки "Массив 1" и щелкаем мышью. Окно "МУМНОЖ" превратилось в поле строки. Наводим курсор на ячейку А5, нажимаем левую клавишу мыши и, удерживая ее, доводим до ячейки С5, отпускаем клавишу. В поле строки появилась запись А5:С5. Вновь наводим курсор на значок 3 и щелкаем мышью. Появилось окно "МУМНОЖ". Наводим курсор на значок !9 справа от строки "Массив 2" и щелкаем мышью. Окно "МУМНОЖ" превра
51
Глава 1. Ожидаемая доходность и риск портфеля
тилось в поле строки. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее, доводим курсор до ячейки D3, отпускаем клавишу. Теперь перемножаем матрицу строку А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. Расчет риска портфеля
Еще по теме 1.2.7. Использование программы Excel для расчета риска портфеля ценных бумаг:
- 1.2.4. Использование программы Excel для расчета ковариации и коэффициента корреляции доходностей ценных бумаг
- 1.1.6. Использование программы Excel для расчета ожидаемой доходности портфеля
- Приложение 7. Использование программы Excel для построения графика границы Марковца портфелей из двух активов
- 5.2.1.3. Оценка величины не хеджируемого риска портфеля. Определение коэффициента детерминации портфеля с помощью программы Excel
- Пример расчета риска и ожидаемой ДОХОДНОСТИ портфеля из двух ценных бумаг
- 10.2. Использование программы Excel для исторического моделирования
- 5.1.4. Использование программы Excel для определения вероятности наступления события
- 10.3.2. Использование программы Excel для получения значений курса акции методом Монте-Карло
- 5.4.4. Анализ риска портфеля ценных бумаг
- 4.6. Оценка доходности и риска портфеля ценных бумаг
- 5.1.1.2. Определение оптимального портфеля при копировании индекса с помощью программы Excel
- 4.3. Определение удельных весов активов в оптимальных портфелях и эффективной границы с помощью программы Excel
- 5.3.1. Хеджирование портфеля ценных бумаг, стоимость которого выражена в рублях. Страхование валютного риска по хеджируемой позиции
- 2.2. Использование маркетинговой и производственной программ для менеджмента валютно-экономического риска
- Методология использования методов оценки стоимости при управлении портфелем ценных бумаг
- 4.5. Определение удельных весов активов в рыночном портфеле при возможности заимствования и кредитования с помощью программы Excel