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

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

Пример 1.

Решение.

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

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

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

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

Пример 2.

Определить риск портфеля.

Решение.

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

Вначале введем в ячейки исходные данные. В ячейках с А1 по A3 печатаем уд. веса бумаг (см. рис. 1.17), в ячейках Bl, C2 и D3 соответственно - дисперсии доходностей бумаг X (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 выделился жирной рамкой. Печатаем здесь формулу:

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

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

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

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

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

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

Содержание

Далее