Материал: Ивличева Н.А. Информиционные системы... Практикум Ч. 1
31
Рисунок 4.1
Расчет доходности
Задача
По рассчитанному биржевому индексу определите его доходность.
Под доходностью в момент времени t понимают величину
r t P t P t 1 , где P t 1
P t – цена актива в момент времени t ,
P t 1 – цена актива в предыдущий момент времени t 1. Как правило, доходность выражают в процентах.
Введите на лист Excel данные, представленные на рисунке 4.2. Рассчитайте доходность индекса, результаты сведите в таблицу, установите для индекса процентный формат с тремя знаками после запятой.
32
Рисунок 4.2
Расчет средней доходности и степени риска актива
Задача
Используя данные о доходности пяти компаний, приведенные на рисунке 4.3, рассчитайте среднюю доходность и степень риска актива.
Рисунок 4.3
Под средней доходностью актива понимают среднее арифметическое доходов за промежуток времени. В Excel для подсчета среднего значения можно использовать статистическую функцию СРЗНАЧ().
Степень риска актива связывают с разбросом значений, характеристикой которого могут служить дисперсия доходности и среднее квадратическое отклонение. В Excel для подсчета дисперсии используется статистическая функция
33
ДИСПР(). Среднее квадратическое отклонение представляет собой квадратный корень из дисперсии, для его подсчета можно использовать математическую функцию КОРЕНЬ().
Введите на лист Excel данные о доходности акций компаний, представленные на рисунке 4.3. Заполните таблицу средней доходности и рисков по образцу, приведенному на рисунке 4.4. Для средней доходности и отклонения установите процентный формат отображения с тремя знаками после запятой.
Рисунок 4.4
Сравнение доходности актива с доходностью рынка
Задача
Определите степень зависимости доходности акций эмитентов от рыночной ситуации и тип актива.
Степень зависимости доходности ценной бумаги от рынка выражается квадратом коэффициента корреляции между доходностью ценной бумаги за период времени и доходностью биржевого индекса за этот же период.
Рассчитайте степень зависимости доходности акций эмитентов от рынка. Результат оформите в виде таблицы, представленной на рисунке 4.5 (в столбце Зависимость от рынка). Для расчета коэффициента корреляции используйте функцию КОРРЕЛ() из категории Статистические. В качестве
аргументов функции укажите диапазон доходности акций соответствующего эмитента за период времени и диапазон доходности биржевого индекса за этот же период.
Для сравнения доходности бумаги со средней доходностью рынка используют модель регрессии, в которой доходность ценной бумаги выражается через доходность биржевого индекса уравнением
r t b0 b1rM t , где
rM t – доходность биржевого индекса, b0 , b1 – коэффициенты регрессии.
В таблице, представленной на рисунке 4.5, рассчитайте коэффициенты регрессии b0 и b1 . Для их расчета можно воспользоваться статистической
функцией Excel ЛИНЕЙН(). Для этого следует:
1.Выделить две ячейки, в которые будут помещены вычисленные значения коэффициентов.
2.Выполнить команду меню Вставка – Функция и в категории Стати-
стические выбрать функцию ЛИНЕЙН.
3.В качестве известных значений y указать диапазон доходности акций
34
соответствующего эмитента за период времени, в качестве известных значений х – диапазон доходности биржевого индекса за этот же период. Остальные
аргументы вводить не нужно.
4. Одновременно нажать клавиши Ctrl + Shift + Enter.
Уровень доходности актива в сравнении с рыночным уровнем определяет коэффициент b1 . Если значение этого коэффициента больше 1, то актив считается
«агрессивным» (доходность выше рыночной), если меньше 1 – «оборонительным» (доходность ниже рыночной).
В таблице, представленной на рисунке 4.5, определите тип актива, используя функцию ЕСЛИ() из категории Логические.
Рисунок 4.5
Формирование инвестиционного портфеля
Задача
Сформируйте оптимальный инвестиционный портфель, то есть, портфель, достигающий требуемой доходности при минимальном риске.
Портфелем инвестора называется набор чисел w1 , w2 ,...wk , показывающих долю каждого актива среди имеющихся. Обязательно выполнение следующих условий:
1.wi 1;
2.Для любого i выполнено условие wi 0 .
Доходностью портфеля называется величина m wi mi , где
mi – доходность i -ой ценной бумаги.
Под риском портфеля понимают величину
2 cov ri , rj wi wj , где
cov ri , rj – выборочная ковариация доходностей i -ой и j -ой ценной бумаги.
I. Сформируем сначала инвестиционный портфель, содержащий равные доли акций каждого эмитента. Рассчитаем доходность и риск портфеля.
Создайте на листе Excel таблицу, представленную на рисунке 4.6. В столбец Всего введите формулу подсчета суммы по строке.
35
Рисунок 4.6
Создайте на листе Excel ковариационную таблицу, представленную на рисунке 4.7. Для заполнения столбца wi и строки wj используйте ссылки на ячейки, содержащие соответствующие доли в таблице Инвестиционный портфель (рисунок 4.6).
Для вычисления ковариации доходностей используйте статистическую функцию КОВАР(). Напомним, что значения доходностей находятся в таблице,
приведенной на рисунке 4.3.
Рисунок 4.7
Создайте на листе Excel целевую таблицу:
Для расчета доходности портфеля используйте функцию СУММПРОИЗВ() из категории Математические, в качестве ее аргументов введите диа-
пазоны ячеек, содержащих средние доходности акций и их доли в портфеле.
Для быстрого вычисления суммы произведений при расчете риска портфеля можно воспользоваться дополнительными возможностями функции СУММ().
1.Вставьте в ячейку функцию СУММ();
2.Выделите диапазон ковариационной матрицы, содержащий значения wi,
нажмите клавишу * (умножение);
3.Выделите диапазон ковариационной матрицы wj, нажмите клавишу *;
4.Выделите диапазон ковариационной матрицы, содержащий значения ковариаций;
5.Одновременно нажмите клавиши Ctrl+Shift+Enter.
Таким образом, выбранный инвестиционный портфель имеет следующие параметры:
.
То есть, портфель, составленный из 20% акций каждого эмитента, принесет средний доход 0,538% в день.