Алгоритм построения модели Марковица в ms Excel (численное решение)
Модель Марковица в данном случае записывается следующим образом:
целевая функция:
линейные ограничения:
.
Подготовка данных для построения модели с использованием промежуточных результатов (средних значений доходности, ковариационной матрицы, риска), полученных в ходе аналитического решения задачи. Прежде всего необходимо эти результаты разместить на листе MS Excel так, как это показано на рис. 4.3. Заметим, что в ячейке $AP$12 содержится величина дисперсии, т.е. величина риска, возведенная в квадрат. Далее следует ввести матрицу из коэффициентов линейных ограничений (AJ13:AO19); строку из нулей, соответствующую первоначальной структуре портфеля (AJ24:AO24); единичный вектор (AP13:AP19).
Задание целевой функции путем введения в ячейку AQ12 формулы
=МУМНОЖ(AJ24:AO24;МУМНОЖ(AJ4:AO9;ТРАНСП(AJ24:AO24))).
3. Задание ограничений путем введения в ячейки (AQ13:AQ20) формулы
=МУМНОЖ(AJ13:AO20;ТРАНСП(AJ24:AO24)).
4. Вызов модуля Поиск решения (Сервис – Поиск решения). В результате появится окно, в котором необходимо:
установить целевую ячейку $AQ$12, равной минимальному значению (см. рис. 4.4);
Рис. 4.3. Первоначальные установки при построении модели Марковица
Рис. 4.4. Модуль Поиск решения (модель Марковица)
в строке «Изменяя ячейки» указать диапазон $AJ$24:$AO$24 (см. рис. 4.4);
указать ограничения (см. рис. 4.4):
$AQ$13=$AP$13; $AQ$14<=$AP$14; $AQ$15<=$AP$15; $AQ$16<=$AP$16; $AQ$17<=$AP$17; $AQ$18<=$AP$18; $AQ$19<=$AP$19; $AQ$20=$AP$20.
нажать кнопку Выполнить. В результате появится окно, представленное на рис. 4.5.
Рис. 4.5. Результаты Поиска решения (модель Марковица)