<<
>>

3.5. Определение уравнения линии характеристики и коэффициента детерминации с помощью программы Excel

Линия характеристики представляет собой уравнение регрессии. Поэтому для ее построения необходимо оценить значения коэффициентов у. и Д. в

уравнении (3.21). Найти данные коэффициенты можно несколькими способами.

Рассмотрим их на примерах.

Пример 1.

Имеется выборка наблюдений доходности актива А и рыночного индекса (для примера ограничимся десятью значениями). Печатаем значения доходности актива в ячейках от А1 до А10, а индекса - от В1 до В10, как показано на рис. 3.15.

ПО

Глава 3. Модели оценки доходности активов С| Книг а 1 .Ковариация .к1ч А | В ! с 1 10 5 ......Т 15 10 3 14 5 4 13 3 5 5 10 6 -10 -5 7 -5 -10 8 3 5 9 10 8 10 15 10 11 Рис. 3.15. Выборка данных по доходности актива А и рыночного индекса (%)

Определим вначале коэффициент /3 актива. Решение получим в ячейке А12, поэтому выделяем ее, т.е. наводим на нее курсор и щелкаем мышью. Открываем окно "Мастер функций", т.е. наводим курсор на значок на панели инструментов и щелкаем мышью. В поле "Категория" выбираем курсором строку "Статистические" и щелкаем мышью. В поле окна "Функция" выбираем курсором строку "НАКЛОН" и щелкаем мышью. Строка высветилась синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно "НАКЛОН". В окне две строки: "Изв_знач_у" и "Извзначх". В первую строку заносим значения доходности актива А. Для этого наводим курсор на знак 51 с правой стороны первой строки и щелкаем мышью. Окно "НАКЛОН" свернулось в поле первой строки. Наводим курсор на ячейку А1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки А10 и отпускаем клавишу. Вновь наводим курсор на знак 53 и щелкаем мышью. Появилось развернутое окно "НАКЛОН". Заносим доходности рыночного индекса во вторую строку. Для этого наводим курсор на знак 3 во второй строке и щелкаем мышью. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки В10, отпускаем клавишу.

Наводим курсор на кнопку 3 и щелкаем мышью. Появилось развернутое окно "НАКЛОН". Наводим курсор на кнопку ОК и щелкаем мышью. В ячейке А12 появилась цифра 1,029884.

(Получить значение коэффициента /3 можно другим способом: выбираем курсором ячейку А12 и печатаем в ней формулу:

=НАКЛОН(А1: Al 0;В 1 :В 10)

и нажимаем клавишу Enter.)

Рассчитаем теперь коэффициент у. Решение получим в ячейке В12, поэтому наводим на нее курсор и щелкаем мышью. Открываем окно "Мастер функций", т.е. наводим курсор на значок Ъ на панели инструментов и щелкаем мышью. В поле "Категория" выбираем курсором строку "Статистические" и щелкаем мышью. В поле окна "Функция" выбираем курсором строку "ОТРЕЗОК" и щелкаем мышью. Строка высветилась синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно "ОТРЕЗОК". В окне две

111

Глава 3. Модели оценки доходности активов

строки: "Изв_знач_у" и "Извзначх". В первую строку заносим значения доходности актива А. Для этого наводим курсор на знак Э с правой стороны первой строки и щелкаем мышью. Окно "ОТРЕЗОК" свернулось в поле первой строки. Наводим курсор на ячейку А1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки А10 и отпускаем клавишу. Вновь наводим курсор на знак !Щ и щелкаем мышью. Появилось развернутое окно "ОТРЕЗОК". Заносим доходности рыночного индекса во вторую строку. Для этого наводим курсор на знак Щ во второй строке и щелкаем мышью. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки В10, отпускаем клавишу. Наводим курсор на кнопку Э и щелкаем мышью. Появилось развернутое окно "ОТРЕЗОК". Наводим курсор на кнопку ОК и щелкаем мышью. В ячейке В12 появилась цифра 2,777476.

(Получить значение коэффициента р можно другим способом: выбираем курсором ячейку В12 и печатаем в ней формулу:

=ОТРЕЗОК(А 1: А10;В 1 :В 10) и нажимаем клавишу Enter.)

Пример 2.

Рассчитать коэффициенты у и /? для данных примера 1 можно с помощью

функции "ЛИНЕЙН" "Мастера функций".

Решение получим в блоке ячеек А12 и В12. Поэтому выделяем их, т.е. наводим курсор на ячейку А12, нажимаем левую клавишу мыши и, удерживая ее, проводим курсор до ячейки В12, отпускаем клавишу. Открываем окно "Мастер функций", т.е. наводим курсор на значок на панели инструментов и щелкаем мышью. В поле "Категория" выбираем курсором строку "Статистические" и щелкаем мышью. В поле окна "Функция" выбираем курсором строку "ЛИНЕЙН" и щелкаем мышью. Строка высветилась синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно "ЛИНЕЙН". Оно представлено на рис. 3.16.

г ЛИНЕИН-

Константа { ^ ?

Стат| 3 я

Возврлджт параметры линейного приближения по методу наименьших квадратов.

Изп_знач_у множество значений у; для которых уже известно соотношение у * mx + b.

J3j

Рис. 3.16. Окно функции "ЛИНЕЙН"

В строку "Изв_знач_у" заносим значения доходности актива А. Для этого наводим курсор на знак 3 в первой строке и щелкаем мышью. Окно "ЛИНЕЙН" свернулось в поле первой строки. Наводим курсор на ячейку А1, нажи

112

Глава 3. Модели оценки доходности активов

маем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки А10 и отпускаем клавишу. Вновь наводим курсор на знак Щ и щелкаем мышью. Появилось развернутое окно "ЛИНЕЙН". Заносим доходности рыночного индекса в строку "Извзначх". Для этого наводим курсор на знак Щ во второй строке и щелкаем мышью. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки В10, отпускаем клавишу. Наводим курсор на кнопку I2f и щелкаем мышью. Появилось развернутое окно "ЛИНЕИН". Одновременно нажимаем на клавиши Ctrl, Shift и Enter (удобно вначале нажать Ctrl и Shift и, удерживая их в нажатом положении, нажать Enter). В блоке ячеек А12 и В12 появились соответственно цифры 1,029884 и 2,777476 .

С помощью функции "ЛИНЕЙН" можно получить значение коэффициента детерминации. Для этого выделим блок из ячеек А12:В14, т.е.

наводим курсор на ячейку А12, нажимаем левую клавишу мыши и, удерживая ее, доводим курсор до ячейки В14, отпускаем клавишу. После этого вводим значения доходностей актива А и рыночного индекса в строки "Изв_знач_у" и "Изв_знач_х" окна "ЛИНЕЙН" как было сказано выше. Далее в открытом окне "ЛИНЕЙН" в строке "Стат" печатаем цифру 1 (Вместо цифры 1 также можно напечатать слово ИСТИНА) и одновременно нажимаем клавиши Ctrl, Shift и Enter. На экране получаем результат как показано на рис. 3.17.

П| Книга! .Ковариация.KIS

В

10 15 14 13

5 -10 -5

3 10 15

5 10

5 3 10 -5 -10 5 8 10

1,029884 Щ?Ш

Рис. 3.17. Расчет коэффициента детерминации

В ячейках А12 и В12 представлены значения коэффициентов /3 и у. Значение коэффициента детерминации расположено в ячейке А14, это 0,627868.

113

Глава 3. Модели оценки доходности активов

Пример 3.

Рассчитать коэффициенты у и J3 и коэффициент детерминации для данных примера 1 можно с помощью пакета "Анализ данных"8. Для этого выбираем курсором меню "Сервис" и щелкаем мышью. Появилось выпадающее меню. Выбираем курсором строку "Анализ данных" и щелкаем мышью. Появилось окно" Анализ данных". Выбираем курсором строку "Регрессия" и щелкаем мышью. Строка высвечивается синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно "Регрессия" (см. рис. 3.18).

Рис. 3.18. Расчет коэффициентов линии характеристики и коэффициента детерминации

Наводим курсор на знак 3 справа от поля строки "Входной интервал Y" и щелкаем мышью. Окно "Регрессия" свернулось в поле строки. Наводим курсор на ячейку А1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки А10 и отпускаем клавишу. Вновь наводим курсор на знак 31 и щелкаем мышью. Появилось развернутое окно "Регрессия". Наводим курсор на знак 3i справа от поля строки "Входной интервал X" и щелкаем мышью. Окно "Регрессия" свернулось в поле строки. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки В10 и отпускаем клавишу.

Вновь наводим курсор на знак 3 и щелкаем мышью. Появилось развернутое окно "Регрессия". Если в круглом окне слева от надписи "Выходной интервал" не стоит точка, то надо навести курсор на данную строку и щелкнуть мышью: в окне появится точка. После этого наводим курсор на знак 3 в правой части этой строки и щелкаем мышью. Окно "Регрессия" свернулось в поле строки.

Об установке пакета "Анализ данных" см. параграф 1.2.2.

114

Глава 3. Модели оценки доходности активов 11

13 ВЫВОД итогов 14 11 11

17_

11 19 Множеств Uffijjjjjifx' ''[ЩЩШ*)^1 '' R-кэадрат 0j627868 Иормиров 0,581351 Стандарт 5 ?40683 Наблюден 10 21 21 22 21 2± 25 Регрессия ? ? 1 429,4616 ^ШЙ^^ЙШ^^ Остаток 8 254,5384 31,8173. Итого 9 68* 26 27 21 29 Перемени 1,029884 0,280322 ЗЩ927 , ОЗЗШ 1Д76309 GJ83489 1.676309 Рис. 3.19. Расчет коэффициентов линии характеристики и коэффициента детерминации с помощью пакета "Анализ данных "

В качестве начала выходного интервала выбираем ячейку А12, поэтому выбираем ее курсором и щелкаем мышью. Вновь наводим курсор на знак 3 и щелкаем мышью. Появилось развернутое окно "Регрессия". Наводим курсор на кнопку ОК и щелкаем мышью. На листе появились данные как показано на рис. 3.19. Из таблицы нас интересуют только три параметра. В строке 16 стоит показатель R-квадрат, а в строках 28 и 29 первые цифры представляют собой соответственно коэффициенты у и (3.

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

Еще по теме 3.5. Определение уравнения линии характеристики и коэффициента детерминации с помощью программы Excel:

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