<<
>>

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

^1

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

Пример 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 и щелкаем мышью.

В поле строки появился номер ячейки. Вновь наводим курсор на знак Щ и щелкаем мышью. Появилось окно "КОРЕНЬ". Наводим курсор на кнопку ОК и щелкаем мышью. В ячейке СЗ появилась цифра 18,8749.

Пример 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 и отпускаем клавишу.

Диапазон А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, отпускаем клавишу.

Выбираем курсором на панели инструментов значок * и щелкаем мышью. Появилось окно "Мастер функций". В левом поле ("Категория") выбираем курсором строку "Ссылки и массивы" и щелкаем мышью. В правом поле окна ("Функция") курсором выбираем строку "ТРАНСП" и щелкаем мышью. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно "ТРАНСП" со строкой "Массив". Наводим курсор на значок 3 справа от строки "Массив" и щелкаем мышью. Окно "ТРАНСП" превратилось в поле строки. Наводим курсор на ячейку А1, нажимаем левую клавишу мыши и, удерживая ее, доводим до ячейки A3, отпускаем клавишу. В поле строки появилась запись А1:АЗ. Вновь наводим курсор на значок 3 и щелкаем мышью. Появилось окно "ТРАНСП". Одновременно нажимаем клавиши Ctrl, Shift и Enter. В диапазоне ячеек А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. Расчет риска портфеля

<< | >>
Источник: А.Н. Буренин. Управление портфелем ценных бумаг. 2-е издание, исправленное и дополненное. Научно-техническое общество имени академика СИ. Вавилова . 2008

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

  1. 1.2.4. Использование программы Excel для расчета ковариации и коэффициента корреляции доходностей ценных бумаг
  2. 1.1.6. Использование программы Excel для расчета ожидаемой доходности портфеля
  3. Приложение 7. Использование программы Excel для построения графика границы Марковца портфелей из двух активов
  4. 5.2.1.3. Оценка величины не хеджируемого риска портфеля. Определение коэффициента детерминации портфеля с помощью программы Excel
  5. Пример расчета риска и ожидаемой ДОХОДНОСТИ портфеля из двух ценных бумаг
  6. 10.2. Использование программы Excel для исторического моделирования
  7. 5.1.4. Использование программы Excel для определения вероятности наступления события
  8. 10.3.2. Использование программы Excel для получения значений курса акции методом Монте-Карло
  9. 5.4.4. Анализ риска портфеля ценных бумаг
  10. 4.6. Оценка доходности и риска портфеля ценных бумаг
  11. 5.1.1.2. Определение оптимального портфеля при копировании индекса с помощью программы Excel
  12. 4.3. Определение удельных весов активов в оптимальных портфелях и эффективной границы с помощью программы Excel
  13. 5.3.1. Хеджирование портфеля ценных бумаг, стоимость которого выражена в рублях. Страхование валютного риска по хеджируемой позиции
  14. 2.2. Использование маркетинговой и производственной программ для менеджмента валютно-экономического риска
  15. Методология использования методов оценки стоимости при управлении портфелем ценных бумаг
  16. 4.5. Определение удельных весов активов в рыночном портфеле при возможности заимствования и кредитования с помощью программы Excel