<<
>>

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

14

Рассмотрим технику расчета ковариации и корреляции доходностей бумаг на примере.

Пример 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. Ожидаемая доходность и риск портфеля

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

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

  1. 1.2.7. Использование программы Excel для расчета риска портфеля ценных бумаг
  2. 1.1.6. Использование программы Excel для расчета ожидаемой доходности портфеля
  3. 10.2. Использование программы Excel для исторического моделирования
  4. Ожидаемая доходность и стандартное отклонение доходности для инвестиционного портфеля, сформированного из более чем двух ценных бумаг
  5. 5.1.4. Использование программы Excel для определения вероятности наступления события
  6. 10.3.2. Использование программы Excel для получения значений курса акции методом Монте-Карло
  7. Приложение 7. Использование программы Excel для построения графика границы Марковца портфелей из двух активов
  8. Пример расчета риска и ожидаемой ДОХОДНОСТИ портфеля из двух ценных бумаг
  9. 3.5. Определение уравнения линии характеристики и коэффициента детерминации с помощью программы Excel
  10. 1.2.2. Определение дисперсии и стандартного отклонения доходности актива с помощью программы Excel
  11. 5.2.1.3. Оценка величины не хеджируемого риска портфеля. Определение коэффициента детерминации портфеля с помощью программы Excel
  12. § 16.3. КОЭФФИЦИЕНТ КОРРЕЛЯЦИИ ПИРСОНА. КОЭФФИЦИЕНТ ДЕТЕРМИНАЦИИ