1.2.4. Использование программы Excel для расчета ковариации и коэффициента корреляции доходностей ценных бумаг
Рассмотрим технику расчета ковариации и корреляции доходностей бумаг на примере.
Пример 1.
Доходность бумаги X за пять лет составила соответственно 20%, 25%, 22%, 28%, 24%.
Доходность бумаги F: 24%, 28%, 25%, 27%, 23%. Определить кова-риацию доходностей бумаг.Решение.
Приведем решение задачи двумя способами.
а) Печатаем в хронологическом порядке в ячейках с Al по А5 значения доходности бумаги X, а в ячейках с Bl по В5 - доходности бумаги F. Решение получим в ячейке С1, поэтому наводим на нее курсор и щелкаем мышью. Печатаем в ячейке С1 формулу:
=КОВАР(А1:А5;В1:В5)
и нажимаем клавишу Enter. В ячейке С1 появилось решение задачи - цифра 3,08, т.е. выборочная ковариация для нашего примера.
б) Ковариацию можно рассчитать с помощью программы "Мастер функций". Для этого наводим курсор на значок А на панели инструментов и щелкаем мышью. Появилось окно "Мастер функций". В левом поле ("Категория") наводим курсор на строку "Статистические" и щелкаем мышью.15 Строка высветилась синим цветом, а в правом поле окна ("Функция") появился перечень статистических функций. Наводим курсор на строку "КОВАР" и щелкаем левой клавишей мыши. Строка высветилась синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно "КОВАР". В окне две строки, которые называются "Массив 1" и "Массив 2". В первую строку заносим номера ячеек с Al по А5. Для этого наводим курсор на знак 3, расположенный с правой стороны первой строки и щелкаем мышью. Окно "КОВАР" превратилось в поле первой строки. Наводим курсор на ячейку А1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, доводим курсор вниз до ячейки А5 и отпускаем клавишу. В поле строки появилась запись А1:А5. Вновь наводим курсор на знак 3 и щелкаем мышью. Появилось развернутое окно "КОВАР". Зано
Вводные пояснения и приемы работы с программой Excel приведены в параграфе 1.1.6.
15 См. сноску 7.31
Глава 1. Ожидаемая доходность и риск портфеля
сим номера ячеек с Bl по В5 во вторую строку. Для этого наводим курсор на знак 5J во второй строке и щелкаем мышью. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, доводим курсор вниз до ячейки В5, отпускаем клавишу. В поле строки появилась запись В1:В5. Наводим курсор на кнопку 3| и щелкаем мышью. Появилось развернутое окно "КОВАР". Наводим курсор на кнопку ОК и щелкаем мышью. В ячейке С1 появилась цифра 3,08.
Пример 2.
Определить коэффициент корреляции доходностей бумаг для условий примера 1. Решение.
Приведем решение задачи двумя способами.
а) Печатаем в хронологическом порядке в ячейках с Al по А5 значения доходности бумаги X, а в ячейках с Bl по В5 - доходности бумаги F. Решение получим в ячейке С1, поэтому наводим на нее курсор и щелкаем мышью. Печатаем в ячейке С1 формулу:
=КОРРЕЛ(А1 :А5;В1 :В5)
и нажимаем клавишу Enter. В ячейке С1 появилось решение задачи - цифра 0,612114.
б) Корреляцию можно рассчитать с помощью программы "Мастер функций". Для этого выбираем курсором на панели инструментов значок л» и щелкаем мышью. Появилось окно "Мастер функций". В левом поле ("Категория") выбираем курсором строку "Статистические" и щелкаем мышью.16 В правом поле окна ("Функция") появился перечень статистических функций. Выбираем курсором строку "КОРРЕЛ" и щелкаем мышью. Строка высветилась синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно "КОРРЕЛ". В окне две строки, которые называются "Массив 1" и "Массив 2". В первую строку заносим номера ячеек с Al по А5. Для этого наводим курсор на знак 3 справа от первой строки и щелкаем мышью. Окно "КОРРЕЛ" превратилось в поле первой строки. Наводим курсор на ячейку А1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки А5 и отпускаем клавишу. В поле строки появилась запись А1:А5. Вновь наводим курсор на знак Ш и щелкаем мышью.
Появилось развернутое окно "КОРРЕЛ". Заносим номера ячеек с Bl по В5 во вторую строку. Для этого наводим курсор на знак Ш во второй строке и щелкаем мышью. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, доводим курсор вниз до ячейки В5, отпускаем клавишу. В поле строки появилась запись В1:В5. Наводим курсор на кнопку Щ и щелкаем мышью. Появилось развернутое окно "КОРРЕЛ". Наводим курсор на кнопку ОК и щелкаем мышью. В ячейке С1 появилась цифра 0,612114.16 См. сноску 7.
32
Глава 1. Ожидаемая доходность и риск портфеля
В примерах 1 и 2 мы рассчитали ковариацию и корреляцию доходностей двух бумаг в портфеле. Если в портфель входит большее количество бумаг, то ковариации и корреляции их доходностей можно рассчитывать попарно изложенным выше способом, однако это трудоемкий вариант решения задачи. В Excel имеется специальный пакет "Анализ данных", который позволяет быстро решить такую задачу для большого количества бумаг. Рассмотрим расчет ковариации и корреляций с его помощью.
"Пакет анализа" может быть не установлен. Тогда его необходимо установить. Для этого наводим курсор на меню "Сервис" и щелкаем левой клавишей мыши. Появилось выпадающее меню. Курсором выбираем в нем команду "Надстройки" и щелкаем левой клавишей мыши. Появилось окно диалога "Надстройки". Наводим курсор на окошко слева от строки "Пакет анализа" и щелкаем левой клавишей мыши. В окошке появился флажок (галочка). Наводим курсор на кнопку ОК и щелкаем мышью. "Пакет анализа" установлен. Рассмотрим определение ковариации и корреляций для нескольких бумаг на примере.
Пример 3. Расчет ковариации
Имеется выборка данных по доходностям бумаг В, С и D за десять периодов. Печатаем значения доходности для бумаги В в ячейки от В1 до В10, бумаги С от С1 до СЮ и бумаги D от D1 до D10, как показано на рис. 1.8. Наводим курсор на меню "Сервис" и щелкаем левой клавишей мыши. Появилось выпадающее меню. Наводим курсор на строку "Анализ данных" и щелкаем левой клавишей мыши.
Появилось окно" Анализ данных". Наводим курсор на строку "Ковариация" и щелкаем левой клавишей мыши. Строка высвечивается синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно "Ковариация". (см. рис. 1.10).J файл ПРАВКА Bcrajeica Формат Сервис Данные &сна СПРАВКА
.........
j AnalCyr ~10*|жХ31КХ Ш
Ш % №
Е21
*Э Книга! 10 5 15 15 10 16 14 5 5 13 3 2 5 10 -5 -10 -5 -15 -5 -10 15 3 5 20 10 8 18 15 10 15 Рис. 1.9. Расчет ковариации с помощью "Анализа данных'
33
Глава 1. Ожидаемая доходность и риск портфеля
Входные данные J Входной интервал:
\ Группирование;
\
Г" Метки в первой строке
Параметры вывода | f Выгодной интервал: J С Новый рабочий щкт: Г Новая рабочая книга
<•* по столицей, С по строкам
31
Оравка
Рис. 1.10. Окно "Ковариация"
Наводим курсор на знак 3 справа от поля строки "Входной интервал" и щелкаем мышью. Окно "Ковариация" свернулось в поле строки. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим до ячейки D10. В строке появилась запись $B$1:$D$10. Вновь наводим курсор на знак 5Г и щелкаем мышью. Появилось развернутое окно "Ковариация". Группировку данных проводим по столбцам. Поэтому, если в круглом окне слева от надписи "по столбцам" не стоит точка, то наводим на нее курсор и щелкаем левой клавишей мыши. В окне появится точка. Ниже расположена строчка "Выходной интервал". В круглом окне слева от надписи должна стоять точка. Если ее нет, то наводим курсор на данную строчку и щелкаем левой клавишей мыши. В окне появится точка. Наводим курсор на знак 3 справа от поля строки "Выходной интервал" и щелкаем мышью. Окно "Ковариация" превратилось в поле строки. В качестве начала выходного интервала возьмем ячейку А12. Поэтому наводим на нее курсор и нажимаем левую клавишу мыши. В поле строки появилась запись $А$12. Вновь наводим курсор на знак 3 и щелкаем мышью. Окно "Ковариация" развернулось. Наводим курсор на кнопку ОК и щелкаем мышью.
На листе появилось решение задачи как показано на рис. 1.11. В блоке от В13 до D15 представлена ковариационная матрица. По ее диагонали, т.е. в ячейках В13,С14иВ15 расположены дисперсии соответственно бумаг 2?, С и Д в остальных ячейках - ковариации доходностей бумаг: в ячейке В14 ковариация доходностей бумаг В и С, в В15 - бумаг BHD,B С15 - бумаг CnD. El Книга! А ! в Г С Г D 1 10 5 15 2 15 10 16 3 14 5 5 4 13 3 2 5 5 10 -5 6 -10 -5 -15 7 -5 -10 15 8 3 5 20 9 10 8 18 10 15 10 15 11 12 13 Столбец 1 тл .'? ' : " ' ? 14 Столбец 2 41.7 40.49 15 СтолбецЭ ? 39,9 .? 1624 119.44 16 17 Рис. 1.11. Определение ковариации доходностей трех бумаг34
Глава 1. Ожидаемая доходность и риск портфеля
Пример 4. Расчет корреляций
Имеется выборка данных по доходностям трех бумаг - В9 С и D - за десять периодов. Как и в задаче 3, печатаем значения доходности для бумаги В в ячейки от В1 до В10, бумаги С от С1 до СЮ и бумаги D от D1 до D10 (рис. 1.9). Наводим курсор на меню "Сервис" и щелкаем левой клавишей мыши. Появилось выпадающее меню. Наводим курсор на строку "Анализ данных" и щелкаем левой клавишей мыши. Появилось окно" Анализ данных". Наводим курсор на строку "Корреляция" и щелкаем левой клавишей мыши. Строка высвечивается синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно корреляция (по структуре оно аналогично окну "ковариация)". Наводим курсор на знак 3 справа от поля строки "Входной интервал" и щелкаем мышью. Окно "Корреляция" свернулось в поле строки. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, доводим курсор до ячейки D10. В строке появилась запись $B$1:$D$10. Вновь наводим курсор на знак 3 и щелкаем мышью. Появилось развернутое окно "Корреляция". Группировку данных проводим по столбцам. Поэтому, если в круглом окне слева от надписи "по столбцам" не стоит точка, то наводим на нее курсор и щелкаем левой клавишей мыши. В окне появится точка. Ниже расположена строчка "Выходной интервал".
В круглом окне слева от надписи должна стоять точка. Если ее нет, то наводим курсор на данную строчку и щелкаем левой клавишей мыши. В окне появится точка. Наводим курсор на знак 3 справа от поля строки "Выходной интервал" и щелкаем мышью. Окно "Корреляция" превратилось в поле строки. В качестве начала выходного интервала возьмем ячейку А12. Поэтому наводим на нее курсор и нажимаем левую клавишу мыши. В поле строки появилась запись $А$12. Вновь наводим курсор на знак 3 и щелкаем мышью. Окно "Корреляция" развернулось. Наводим курсор на кнопку ОК и щелкаем мышью. На листе появилось решение задачи как показано на рис 1.12. В блоке от В13 до D15 представлена корреляционная матрица. По ее диагонали, т.е. в ячейках В13, С14 и D15 расположены единицы, в остальных ячейках - корреляции доходностей бумаг: в ячейке В14 корреляция доходностей бумаг В и С, в В15 - бумаг 5иДв С15 - бумаг Си/).?Щ Книга!. Ковариация.к!*
А 1 В |С I D | Е 1 1 10 5 15 2 15 10 16 З" 14 5 5 4 13 3 2 5 5 10 -5 6 -10 -5 -15 7 -5 -10 15 8 3 5 20 <Г 10 8 18 10 15 10 15 11 12 СтолбецШтп&бц 2 Столбец 1 13 Столбец 1 .-. ШШШШШ 14 Столбец 2 Ш^ШШШШ 15 1 16 Рис. 1.12. Определение корреляций доходностей трех бумаг
35
Глава 1. Ожидаемая доходность и риск портфеля
Еще по теме 1.2.4. Использование программы Excel для расчета ковариации и коэффициента корреляции доходностей ценных бумаг:
- 1.2.7. Использование программы Excel для расчета риска портфеля ценных бумаг
- 1.1.6. Использование программы Excel для расчета ожидаемой доходности портфеля
- 10.2. Использование программы Excel для исторического моделирования
- Ожидаемая доходность и стандартное отклонение доходности для инвестиционного портфеля, сформированного из более чем двух ценных бумаг
- 5.1.4. Использование программы Excel для определения вероятности наступления события
- 10.3.2. Использование программы Excel для получения значений курса акции методом Монте-Карло
- Приложение 7. Использование программы Excel для построения графика границы Марковца портфелей из двух активов
- Пример расчета риска и ожидаемой ДОХОДНОСТИ портфеля из двух ценных бумаг
- 3.5. Определение уравнения линии характеристики и коэффициента детерминации с помощью программы Excel
- 1.2.2. Определение дисперсии и стандартного отклонения доходности актива с помощью программы Excel
- 5.2.1.3. Оценка величины не хеджируемого риска портфеля. Определение коэффициента детерминации портфеля с помощью программы Excel
- § 16.3. КОЭФФИЦИЕНТ КОРРЕЛЯЦИИ ПИРСОНА. КОЭФФИЦИЕНТ ДЕТЕРМИНАЦИИ