Рассмотрим пример портфеля Шарпа. Введем в ячейку А5-А11 значения 1,2,3,4,5,6,7
Рис. 20.1 Ввод данных
Введем в ячейки В5-В11 2,0;3,0;2,0;3,0;4,0;3,0;2,0
Рис. 20.2 Ввод данных
Введем в ячейки:
С5-С11 = 5,0; 5,5; 6,0;5,0;4,0;6,0;4,0
D5-D11 = 4,0;5,0;5,0;4,0;6,0;4,0;5,0
E5-E11 = 7,0;5,0;8,0;5,0;9,0;6,0;7,0
F5-F11 = 5,0;7,0;4,0;6,0;8,0;5,0;4,0
Рис 20.3. Ввод данных
Рассчитаем среднее значение. Для этого открываем Мастер функций, определяем курсор в ячейку G5 щелкаем мышью на кнопке в строке состояния и открывается окно Мастер функций. В категории выбираем Срзнач и нажимаем ОК
Рис 20.4. Выбор функции
В появившемся диалоговом окне Аргументы функции определяем
массив В5:F5 и нажимаем ОК (Рис. 20.5). Копируем на остальные ячейки G6-G11
Рис. 20.5 Выбор массива
Рис. 20.6 Выбор массива
Рассчитаем в ячейке G12 среднее значение ячеек G5-G11(аналогично как и для B5-F5).
Рис. 20.7 Выбор массива
В ячейке B12 аналогично посчитаем среднее значение для B5-B11 и т.д.
Рис. 20.8 Расчет среднего значения
В ячейке B13 рассчитаем дисперсию. Для этого открываем Мастер функций, определяем курсор в ячейку B13 щелкаем мышью на кнопке в строке состояния и открывается окно Мастер функций. В категории выбираем Диспр. и нажимаем ОК. Аргументы функции определяем массив B5-B11 и нажимаем ОК (Рис. 20.9). Копируем на остальные ячейки C13-F13
Рис. 20.9 Расчет дисперсии
В ячейке B14 рассчитаем ковариацию. Для этого открываем Мастер функций, определяем курсор в ячейку B14 щелкаем мышью на кнопке в строке состояния и открывается окно Мастер функций. В категории выбираем ковар. и нажимаем ОК. Аргументы функции определяем массив B5-B11 и нажимаем ОК (Рис. 20.10). Копируем на остальные ячейки C14-F14
Рис. 20.10 Расчет ковариации
В ячейке B16 рассчитаем значение бета. Для этого ячейку B14/ G13 (Рис. 20.11). Копируем на остальные ячейки C16-F16.
Рис. 20.11 Расчет беты
В ячейке B15 рассчитаем значение альфа. Для этого ячейку B12- B16*G12 (Рис. 20.12). Копируем на остальные ячейки C15-F15.
Рис. 20.12 Расчет альфы
В ячейке B2 рассчитаем correl T. Для этого введем в ячейку B2=КОРРЕЛ($A$5:$A$11;B$5:B$11) (Рис. 20.13). Копируем на остальные ячейки B2-F2.
Рис. 20.13 Расчет correl T
В ячейке B3 рассчитаем correl M. Для этого введем в ячейку B3= =КОРРЕЛ(B$5:B$11;$G$5:$G$11). (Рис. 20.14). Копируем на остальные ячейки B3-F3.
Рис. 20.14 Расчет correl M
Произведем расчеты, которые будут необходимы для расчета дисперсии остаточной и сигмы. Для этого введем в ячейку J5 =(B$15+B$16*$G5-B5)^2. Копируем остальные ячейки.
Рис. 20.15 Предварительные расчеты
Произведем расчет дисперсии остаточной. Для этого открываем Мастер функций, определяем курсор в ячейку J12 щелкаем мышью на кнопке в строке состояния и открывается окно Мастер функций. В категории выбираем СУММ(J5:J11)/5 и нажимаем ОК. Копируем на остальные ячейки J12-N12.
Рис 20.16. Расчет дисперсии
Произведем расчет сигмы. Для этого нужно вычислить корень квадратный из дисперсии остаточной. Копируем на остальные ячейки J13-N13.
Рис. 20.17 Расчеты сигмы
Введем ячейки А18-А19 и ячейки B18-J18
Рис. 20.18 Ввод ячеек
Заполним матрицу. В ячейку B19 введем формулу 2*J12, а в остальные (B20-B24) вводим «0» и в ячейку B25 вводим «1».
Рис. 20.19. Заполняем матрицу
В ячейку С20 введем формулу 2*К12, а в остальные (С19, С21-С24) вводим «0» и в ячейку С25 вводим «1».
Рис. 20.20 Заполнение матрицы
Продолжаем заполнять матрицу для остальных столбцов W3-W5. В ячейку W3= 2*L12; W4= 2*M12; W5=2*N12.
Рис. 20.21 Заполнение матрицы
Рис. 20.22 Заполнение матрицы А
Рассчитаем матрицу А^-1. Произведем расчет диапазона ячеек В19:J27с помощью массива. Для этого выбираем выбрать функцию МОБР.
Рис.20.23 Матрица А^-1.
Введем значение матрицы В. Для этого введем вводим значения 0;0;0;0;0;0;1;5;0
Рис. 20.24 Ввод значений
Произведем расчет матрицы Х. Для этого в строку функции введем =МУМНОЖ(B29:J37;O19:O27). Выделим ячейки L19:L27, затем нажимаем F2 и клавиши одновременно Ctrl+Shift+Enter. Продолжим заполнять матрицу для остальных строк. В итоге рассчитываем сумму ячеек L19-L23.
Рис. 20.25 Расчет матрицы Х
Произведем расчет матрицы А1 единичной. Для этого в строке ввода функции введем =МУМНОЖ(B19:J27;B29:J37). Выделим ячейку В39, затем нажимаем F2 и клавиши одновременно Ctrl+Shift+Enter. Продолжим заполнять матрицу для остальных строк.
Рис. 20.26 Расчет матрицы А1 единичной
Введем данные нужные для расчета дисперсий
Рис.20.27 Ввод данных
Произведем расчет дисперсии П. Для этого в строку ввода функции произведем ввод функции =МУМНОЖ(J12:N12;N30:N34)
Рис. 20.28 Расчет дисперсии П
Произведем расчет дисперсии средней. Для этого в строку ввода функции произведем ввод функции =МУМНОЖ(B13:F13;N30:N34)
Рис. 20.29 Расчет дисперсии средней
Произведем расчет дисперсии общей. Для этого в строку ввода функции введем значения =Q35+Q36
Рис. 20.30 Расчет дисперсии общей
Расчет сигмы. Для расчета сигмы необходимо вычесть корень из значения Q37. Введем формулу в строку ввода формул
Рис. 20.31 Расчет сигмы
Произведем расчет 3 сигм. Для этого ячейку Q39*3
Рис. 20.32 Расчет 3 сигм
Вопросы и задачи к теме
Вопросы для изучения
1. Методы оценки и снижения рисков Шарпа.
2. Метод Шарпа в управлении инвестициями.
3. Измерение доходности портфеля
4. Безрисковый уровень доходности