Для оптимизации портфеля из трёх бумаг используем программу EXCEL 10.
1. Введём исходные данные и рассчитаем средние значения, дисперсии и стандартные отклонения показателей доходности активов А, В и С.
Исходная таблица представлена на рис.1.
Рис.1. Таблица исходных данных
Для определения суммы в ячейке С9 используем функцию =СУММ(А5:А8).
Средний доход в ячейке С10 определяется с использованием функции =СРЗНАЧ(С5:С8).
В примере определения дисперсии для малой выборки в знаменателе принимаем количество значений n = 4. Дисперсия в ячейке С11 определяется с использованием функции =ДИСПР(С5:С8). Функции =ДИСП(), =ДИСПА() и =ДИСПВ() используются в тех случаях, когда количество исходных данных принимается равным (n – 1).
Стандартное отклонение в ячейке С12 определяется с использованием функции =КОРЕНЬ(С10).
Аналогичным образом определяются показатели по активам В и С.
2. Для построения матрицы ковариаций используем функцию =КОВАР(). Для ячейки С17
Рис.2. Определение значений ковариаций
3. Для построения матрицы корреляций используем функцию =КОРРЕЛ(). Для ячейки К5 расчёт представлен на рис.3.
Рис.3. Расчёт показателя корреляции
4. После составления матриц ковариаций и корреляций можно приступить к оптимизации портфеля. Исходя из собственных представлений, зададим доли бумаг в портфеле: А – 0,5; В – 0,3 и С – 0,2. Лист примет вид, представленный на рис. 4.
Рис.4. Лист, подготовленный к оптимизации портфеля
Рассчитаем ожидаемую дисперсию, стандартное отклонение и ожидаемую доходность портфеля при заданных долях бумаг А, В и С. В ячейках К12:К14; L12:L14 и M12:M14 помещаются произведения долей каждой бумаги на коэффициент ковариации этого сочетания. Для ячейки К12 функция будет иметь вид =J12*K11*C16. Аналогичным образом рассчитываются значения для остальных названных ячеек.
В ячейках К15, L15 и M15 помещаются суммы произведений для каждого вида бумаг. Для ячейки К15 функция будет иметь вид =СУММ(К12:К14). Аналогичным образом рассчитываются значения для ячеек L15 и M15.
Дисперсия портфеля (ячейка J16) определится как сумма взвешенных дисперсий всех бумаг. Функция будет иметь вид =СУММ(К15:M15).
Стандартное отклонение портфеля (ячейка J17) определится как квадратный корень из дисперсии портфеля. Функция имеет вид =КОРЕНЬ(J16).
Ожидаемая доходность портфеля (ячейка J18) определяется как сумма произведений средней доходности каждой бумаги на её долю. Функция имеет вид =C10*J12+D10*J13+E10*J14.
Для поиска доли каждой бумаги в портфеле с минимальным риском при заданной доходности портфеля используем встроенный в EXCEL компонент «Поиск решения». Он находится в строке меню «Данные».
Параметры поиска решения имеют вид, представленный на рисунке (рис.5).
Рис.5. Параметры поиска решения
Оптимизируется целевая функция «Дисперсия портфеля» ячейка J16, изменяемые доли бумаг (ячейки J12: J14), ограничения на сумму долей (J15=1) и ожидаемую доходность при первоначально установленных долях (J18=15,75). В меню параметры устанавливаются неотрицательные значения переменных. В качестве метода решения выбирается «Поиск решения нелинейных задач методом ОПГ».
После нажатия клавиши «Найти решение» получаем решение, которое определяет доли каждой бумаги в портфеле с минимальным риском (ячейки J12:J14), дисперсию портфеля (ячейка J16), стандартное отклонение портфеля (ячейка J17) и ожидаемую доходность (ячейка J18).
Для получения копии листа решения задачи в программе Excel необходимо:
1. Зафиксировать таблицу клавишей Print Screen SysRq.
2. В программе Paint: Вставить Обрезать Копировать
3. В программе WORD Вставить. Полученный рисунок отформатировать.
Отчёт о проделанной работе должен содержать:
1) титульный лист;
2) описание условия задачи;
3) описание хода решения задачи;
4) выводы о результатах оптимизации;
5) копию листа решения задачи в программе Excel.