Технология решения задачи

Структура эффективного портфеля

Задание: решить предложенную задачу при помощи компьютера и предоставить отчет о проделанной работе в конце занятия. Отчет оформляется на листе бумаги. План отчета приведен в конце файла.

Даны ожидаемые доходности ценных бумаг А, В, С за 12 месяцев в процентах.

Вариант 1.

i k
	10,8	1,3	4,8
	6,4	4,1	4,9
	12,5	6,2	6,3
	2,3	7,8	2,5
		12,8	7,8
	7,6	13,1	10,5
	5,8	15,2	12,8
	10,2	14,3	6,8
	6,2		7,9
	1,2	10,5	18,9
	4,5		20,7
	10,2	9,5	23,6

Вариант 2.

i k
	1,8	0,8	2,5
	8,0	9,0	10,7
	6,2	3,7	8,7
	2,9	9,7	5,7
	8,0	0,2	11,3
	0,1	4,6	5,5
	6,5	2,4	7,3
	7,1	9,9	9,1
	9,4	2,7	6,8
	3,3	9,0	1,3
	6,4	7,3	10,1
	6,2	8,6	10,5

Вариант 3.

i k
	1,8	0,1	0,9
	1,1	12,7	14,9
	15,1	16,4	6,4
	17,3	0,7	6,6
	9,1	6,6	16,3
	19,1	14,8	6,6
	2,1	1,0	10,9
	1,5	13,7	13,1
	1,4	6,8	17,2
	7,3	6,9	19,7
	7,2	1,4	0,2
	14,7	10,8	13,0

Вариант 4.

i k
	19,5	17,7	9,9
	14,2	15,2	9,5
	15,7	3,6	15,6
	0,7	8,3	16,2
	1,7	13,4	16,3
	15,5	2,9	10,7
	7,6	3,4	0,7
	10,0	6,8	2,0
	2,0	13,9	8,7
	5,7	1,0	10,9
	10,0	0,6	1,0
	10,7	8,4	7,8

Вариант 5.

i k
	20,6	27,9	15,4
	20,2	26,5	11,4
	21,8	12,4	28,2
	9,3	17,9	14,3
	10,0	14,6	20,0
	16,0	26,4	28,5
	17,8	18,2	7,7
	27,5	1,6	29,9
	25,2	23,7	25,6
	4,9	6,7	14,7
	22,3	17,7	2,5
	11,1	6,9	11,8

Вариант 6.

i k
	28,0	22,8	7,4
	18,6	2,7	5,7
	19,3	16,1	14,6
	10,9	6,1	12,9
	2,2	0,6	18,9
	27,0	18,3	29,7
	29,0	7,3	20,8
	24,1	3,8	17,0
	18,5	15,9	13,7
	25,4	18,1	7,7
	8,5	16,3	9,8
	7,7	10,6	27,9

Вариант 7.

i k
	23,6	16,8	1,6
	22,9	20,9	20,8
	21,3	24,7	11,2
	4,5	9,1	11,6
	12,9	0,4	24,0
	23,9	9,2	15,8
	18,7	13,3	17,2
	14,4	3,9	11,7
	22,0	20,2	3,5
	17,5	3,2	10,8
	3,6	19,6	23,6
	0,6	10,3	5,7

Вариант 8.

i k
	11,6	6,5	14,9
	19,7	10,0	3,2
	9,6	4,4	10,5
	24,5	22,7	16,3
	15,0	22,8	5,9
	5,3	12,6	4,0
	23,5	5,8	1,7
	13,3	24,3	5,3
	6,3	1,6	18,2
	10,4	4,6	10,4
	8,4	7,5	0,9
	8,2	9,5	8,5

Вариант 9.

i k
	7,6	10,5	0,1
	22,4	8,6	19,7
	0,2	3,7	9,3
	3,9	18,6	3,7
	20,0	6,5	16,6
	16,2	19,1	1,3
	14,6	2,7	16,5
	15,8	16,9	0,2
	13,6	22,1	11,4
	12,9	12,3	4,0
	1,4	13,0	24,5
	9,1	11,6	7,9

Вариант 10.

i k
	13,6	9,3	10,1
	7,1	1,4	5,8
	11,4	24,5	10,2
	21,1	19,7	3,2
	26,7	17,6	1,0
	11,3	22,2	5,9
	16,1	4,6	1,5
	1,4	23,9	8,2
	27,6	12,9	4,3
	26,5	17,1	10,9
	14,6	1,9	12,7
	15,3	7,4	2,0

Требуется:

1. Рассчитать среднюю ожидаемую доходность и риск каждой акции.
2. Рассчитать ковариации доходностей акций друг с другом и построить ковариационную матрицу.
3. Решить задачу минимизации риска портфеля при условии, что ожидаемая доходность портфеля больше либо равна . Определить структуру оптимального портфеля с разрешением и запрещением короткой продажи.
4. Решить задачу максимизации доходности портфеля при условии, что риск доходности портфеля не превосходит . Определить структуру оптимального портфеля с разрешением и запрещением короткой продажи.

Технология решения задачи.

Шаг 0. Ввести условие задачи.

Шаг 1. Рассчитать средние ожидаемые доходности и риски акций.

Доходность акции рассчитаем как среднее арифметическое доходностей. Для этого воспользуемся встроенной функцией СРЗНАЧ. Поставим курсор в ячейку B15 и вызовем статистическую функцию СРЗНАЧ, которая возвращает среднее значение массива чисел. В качестве массива возьмем ячейки B3:B14. Эту функцию скопируем в ячейки C15:D15.

Риск акции рассчитаем при помощи встроенной статистической функции ДИСПР. Ставим курсор в ячейку B16 и вызываем функцию ДИСПР. В качестве массива выделяем ячейки B3:B14. Копируем функцию в другие ячейки, чтобы рассчитать риски акций В и С.

Шаг 2. Рассчитать ковариации доходностей акций друг с другом и построить ковариационную матрицу.

Используем встроенную статистическую функцию КОВАР. Поставим курсор в ячейку F4 и вызовем функцию КОВАР (Вставка – Функция – Статистические – КОВАР). Введем первый массив B3:B14, второй – $B3:$B14. Знаки $ поставим для того, чтобы при копировании адрес одного из столбцов не изменялся. Результатом будет ковариация доходностей акций А самих с собой, т.е. их риск. Скопируем эту формулу по строке в ячейки G4:H4.

Далее в ячейке F5 поместим формулу = КОВАР(B3:B14; $C3:$C14). При этом мы найдем ковариацию доходностей акции В и А. По второй строке нам нужны ковариации доходностей акции В с акциями А, В и С. Скопируем эту формулу по строке аналогично первой строке.

Третью строку ковариационной матрицы сформируем также. Только здесь должны находиться ковариации доходностей акции С с другими.

Шаг 3. Построить модель Марковица.

Мы рассчитали доходности и риски каждой акции. Известно, что портфель, составленный из акций может иметь риск и доходность в пределах минимального и максимального риска и доходности акций, его составляющих.

В нашем случае модель Марковица будет иметь следующий вид:

Модель 1.

Здесь - уровень доходности портфеля, x_j – доли акций в портфеле.

Подготовим форму для расчетов. Отведем ячейки под переменные – F10:H10, под значение доходности портфеля – G12, риск портфеля – G13. В ячейках F10:H10 поместим начальные значения переменных. Например: 1, 0, 0. В ячейку G12 введем формулу для расчета доходности портфеля: = СУММПРОИЗВ(B15:D15;F10:H10). (Вставка – Функция – Математические – СУММПРОИЗВ, чтобы ввести адреса ячеек, достаточно провести по ним курсор мыши)

Для подсчета риска портфеля в ячейке G13 необходимо ввести формулу:

= СУММПРОИЗВ(МУМНОЖ(F10:H10;F4:H6);F10:H10)

Эту формулу можно ввести двумя способами: полностью набрать вручную либо вызвать функцию СУММПРОИЗВ и в меню этой функции вместо массива 1 ввести вручную функцию МУМНОЖ(F10:H10;F4:H6).

В ячейке I10 запишем формулу = СУММ(F10:H10). Она необходима, чтобы ввести ограничение: сумма долей равна 1.

Шаг 4. Определить структуру оптимального портфеля.

Решение оптимизационной задачи произведем при помощи команды меню Сервис – Поиск решения. Появится окно:

Установить целевую ячейку: G13

Равной: минимальному значению

Изменяя ячейки: F10:H10

Ограничения: Добавить:

Ссылка на ячейку: G12

Знак: = (выбрать при помощи стрелки)

Ограничение: (уровень доходности)

Далее нажимаем кнопку Добавить, так как нужно ввести еще ограничение на сумму переменных (в ячейке I10 должно быть значение, равное 1). После ввода этого ограничения нажимаем кнопку ОК. Снова появится диалоговое окно Поиск решения.

Нажимаем кнопку Параметры и устанавливаем флажок Неотрицательные значения. После нажатия кнопки ОК снова возвращаемся в окно Поиск решения.

Мы полностью записали модель на компьютере. Теперь можно нажать кнопку Выполнить. Если все сделано верно, то появится сообщение:

Нажимаем кнопку ОК и изучаем результаты расчетов. Можно узнать каковы доли акций в портфеле, а также каков минимальный риск при заданном уровне доходности . Записываем данные расчетов в отчет.

Снова вызываем Поиск решения и убираем «птичку» в Параметрах – Неотрицательные значения, чтобы определить оптимальный портфель с разрешением коротких продаж (Выполнить). Записываем результаты расчетов в отчет.

Шаг 5. Оформить отчет о проделанной работе.

План отчета:

1. Фамилия имя, название группы, номер варианта.
2. Указать доходности акций и ковариационную матрицу. Дать сравнительную характеристику акций.
3. Записать модель Марковица в развернутом виде. Указать получившееся оптимальное решение и дать ответ о размещении капитала инвестора в портфеле из расчета, что у него в наличии 1 млн. рублей.

Шаг 6. Построить модель определения оптимального портфеля с максимальной доходностью при заданном уровне риска.

Модель 2.

Снова вызовем Поиск решения из меню Сервис и запишем в окне Поиска решения модель 2. Остальные действия проделайте самостоятельно и запишите в отчет результаты вычислений. Также найдите оптимальное решение, если разрешены короткие продажи.

Дополните отчет результатами вычислений по модели 2.

Поиск по сайту: