Задача формирования оптимального портфеля имеет два критерия: риск и доходность. Получить решение, одновременно минимизирующее риск и максимизирующее доходность, невозможно. Поэтому при решении этой задачи учитывается мнение инвестора, которое предусматривает либо при заданном уровне доходности минимизацию риска, либо при заданном уровне риска максимизацию доходности.
Сначала рассмотрим задачу, когда требуется минимизировать риск при заданном уровне доходности. Запишем условие этой задачи в матричной форме, используя для этого ранее введенные обозначения,
, (4.1)
, (4.2)
. (4.3)
С помощью функции Лагранжа сведем задачу условной минимизации (4.1) – (4.3) к задаче безусловной минимизации
. (4.4)
С целью минимизации запишем для функции Лагранжа условия минимума 1-го порядка, которые получаются, если ее продифференцировать по w, 
и 
. После сокращения на 2 имеем систему
; (4.5)
; (4.6)
, (4.7)
из которой нетрудно получить
. (4.8)
Подставив полученное выражение (4.8) в (4.6) и (4.7), предварительно поменяв местами сомножители, получаем систему из двух уравнений с двумя неизвестными и
(4.9)
Если ввести обозначения
, 
, 
, (4.10)
то систему (4.9) можно записать в виде
(4.11)
Решение системы (1.24) методом Крамера, позволяет получить
, 
. (4.12)
Подставляя (4.12) в (4.8), получаем уравнение для расчета структуры оптимального портфеля с заданной доходностью 
. (4.13)
Алгоритм построения модели Марковица в MS Excel (аналитическое решение)
1. Ввод исходной информации (табл. П1, см. Приложение 1) с восстановленными в предыдущем параграфе пропусками в данных, отражающих динамику стоимости акций СургутНГ и Сбербанка (см. рис. 4.1).
Рис. 4.1. Фрагмент исходных данных в MS Excel
2. Расчет однодневной и средней доходности акций каждой компании (см. табл. 4.1).
Таблица 4.1
Динамика доходности акций
| I | J | K | L | M | N | O | |
| Лукойл | Газпром | СургутНГ | НГМК | Сбербанк | Роснефть | ||
| 01.04.2009 | |||||||
| 02.04.2009 | -0,4060 | 9,4195 | 5,7755 | 7,5093 | 1,0096 | 10,6154 | |
| 03.04.2009 | 7,7846 | 0,9081 | -1,7251 | 6,6790 | 8,7237 | 4,5858 | |
| 06.04.2009 | 0,1427 | -2,9406 | 0,0949 | -2,1038 | 5,0657 | -2,9851 | |
| 07.04.2009 | 0,4775 | -3,6137 | -1,4091 | -2,1361 | -5,4583 | -2,6938 | |
| 08.04.2009 | 5,3692 | 3,0372 | 3,7286 | 4,3082 | 5,9938 | 4,3310 | |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 25.12.2009 | 0,6318 | 0,8298 | 0,7851 | 0,7262 | -0,1234 | 1,1637 | |
| 28.12.2009 | 1,2496 | -0,2908 | 0,0836 | 0,9878 | -0,7168 | 2,2091 | |
| 29.12.2009 | 1,0216 | 1,1500 | 2,3236 | 1,8253 | 0,9336 | -1,2423 | |
| 30.12.2009 | -1,0639 | -0,6310 | -0,7050 | -0,0865 | 1,1717 | -1,4157 | |
| 31.12.2009 | -0,1536 | 0,3668 | 0,2280 | -0,6854 | 0,5973 | 1,8640 | |
| СРЕДНИЕ ЗНАЧЕНИЯ | |||||||
| 0,1789 | 0,2475 | 0,1739 | 0,4418 | 0,8059 | 0,3418 |
П р и м е ч а н и е:
| Содержимое ячеек столбца J | ……… | Содержимое ячеек столбца O | |
| =(B4/B3-1)*100 |
| =(G4/G3-1)*100 | |
| =(B5/B4-1)*100 |
| =(G5/G4-1)*100 | |
| =(B6/B5-1)*100 |
| =(G6/G5-1)*100 | |
| =(B7/B6-1)*100 |
| =(G7/G6-1)*100 | |
| =(B8/B7-1)*100 |
| =(G8/G7-1)*100 | |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| =(B191/B190-1)*100 |
| =(G191/G190-1)*100 | |
| =(B192/B191-1)*100 |
| =(G192/G191-1)*100 | |
| =(B193/B192-1)*100 |
| =(G193/G192-1)*100 | |
| =(B194/B193-1)*100 |
| =(G194/G193-1)*100 | |
| =(B195/B194-1)*100 |
| =(G195/G194-1)*100 | |
| СРЕДНИЕ ЗНАЧЕНИЯ | |||
| =СРЗНАЧ(J4:J195) | ……… | =СРЗНАЧ(O4:O195) |
3. Вычисление отклонений однодневных доходностей от среднего значения (см. табл. 4.2).
Таблица 4.2
Отклонения доходности
| R | S | T | U | V | W | |
| Лукойл | Газпром | СургутНГ | НГМК | Сбербанк | Роснефть | |
| -0,5850 | 9,1720 | 5,6015 | 7,0675 | 0,2037 | 10,2736 | |
| 7,6057 | 0,6605 | -1,8990 | 6,2371 | 7,9178 | 4,2439 | |
| -0,0362 | -3,1881 | -0,0790 | -2,5456 | 4,2598 | -3,3269 | |
| 0,2986 | -3,8612 | -1,5830 | -2,5779 | -6,2643 | -3,0356 | |
| 5,1903 | 2,7897 | 3,5547 | 3,8664 | 5,1879 | 3,9891 | |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 0,4528 | 0,5823 | 0,6112 | 0,2844 | -0,9294 | 0,8219 | |
| 1,0707 | -0,5383 | -0,0903 | 0,5459 | -1,5228 | 1,8673 | |
| 0,8427 | 0,9025 | 2,1497 | 1,3835 | 0,1277 | -1,5841 | |
| -1,2428 | -0,8785 | -0,8789 | -0,5283 | 0,3657 | -1,7575 | |
| -0,3325 | 0,1193 | 0,0540 | -1,1272 | -0,2086 | 1,5222 |
П р и м е ч а н и е:
| Содержимое ячеек столбца R | ……… | Содержимое ячеек столбца W | |
| =J4-J$197 |
| =O4-O$197 | |
| =J5-J$197 |
| =O5-O$197 | |
| =J6-J$197 |
| =O6-O$197 | |
| =J7-J$197 |
| =O7-O$197 | |
| =J8-J$197 |
| =O8-O$197 | |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| =J191-J$197 |
| =O191-O$197 | |
| =J192-J$197 |
| =O192-O$197 | |
| =J193-J$197 |
| =O193-O$197 | |
| =J194-J$197 |
| =O194-O$197 | |
| =J195-J$197 |
| =O195-O$197 |
4. Нахождение ковариационной матрицы 
.
Для этого необходимо выделить пустые ячейки (Z4:AE9), затем ввести формулу
=МУМНОЖ(ТРАНСП(R4:W195);R4:W195)/(192-1)
и нажать клавиши Ctrl+Shift+Enter одновременно.
В результате появится ковариационная матрица
.
5. Нахождение матрицы, обратной к ковариационной 
.
Для этого необходимо выделить пустые ячейки (Z12:AE17), затем ввести формулу
=МОБР(Z4:AE9)
и нажать клавиши Ctrl+Shift+Enter одновременно.
В результате появится матрица, обратная к ковариационной
.
6. Расчет величин А, В, С, 
и 
.
Для этого необходимо транспонировать средние значения доходности, выделив пустые ячейки (Z19:Z24), введя формулу
=ТРАНСП(J197:O197)
и нажав клавиши Ctrl+Shift+Enter одновременно.
Далее необходимо ввести в ячейки (Z26:Z31) и (J198:O198) вектора из единиц.
Результаты расчетов представлены в табл. 4.3.
Таблица 4.3
Расчет величин А, В, С, и
| AB | AC | AD | AE | |
| 
| 
| 
| |
| 0,0486 | 0,0230 | 0,1372 | 0,3 | |
|
|
| ||
| 0,0061 | 2,9532 | 6,7934 |
П р и м е ч а н и е:
| Содержание ячеек столбца АВ | Содержание ячеек столбца АС | Содержание ячеек столбца АD | |
|
|
|
| |
| =МУМНОЖ(J197:O197; МУМНОЖ(Z12:AE17;Z19:Z24)) | =МУМНОЖ(J197:O197; МУМНОЖ(Z12:AE17;Z26:Z31)) | =МУМНОЖ(J198:O198; МУМНОЖ(Z12:AE17;Z26:Z31)) | |
|
|
|
| |
| =AB20*AD20-AC20^2 | =(AD20*AE20-AC20)/AB23 | =(AB20-AC20*AE20)/AB23 |
7. Расчет структуры оптимального портфеля (табл. 4.4).
Таблица 4.4
Структура оптимального портфеля (доходность 
)
| AB | AC | AD | AE | |
| 
| +
| 
| |
| 0,5284 | 6,7934 | 7,3218 | 0,3291 | |
| 0,7309 | 6,7934 | 7,5243 | -0,0428 | |
| 0,5137 | 6,7934 | 7,3071 | 0,3826 | |
| 1,3048 | 6,7934 | 8,0982 | 0,0607 | |
| 2,3801 | 6,7934 | 9,1735 | 0,1420 | |
| 1,0095 | 6,7934 | 7,8029 | 0,1285 |
П р и м е ч а н и е:
| Содержание ячеек столбца АВ | Содержание ячеек столбца АС | Содержание ячеек столбца АD | Содержание ячеек столбца АE | |
| =Z19*$AC$23 | =Z26*$AD$23 | =AB26:AB31+AC26:AC31 | =МУМНОЖ(Z12:AE17; AD26:AD31) | |
| =Z20*$AC$23 | =Z27*$AD$23 | =AB26:AB31+AC26:AC31 | =МУМНОЖ(Z12:AE17; AD26:AD31) | |
| =Z21*$AC$23 | =Z28*$AD$23 | =AB26:AB31+AC26:AC31 | =МУМНОЖ(Z12:AE17; AD26:AD31) | |
| =Z22*$AC$23 | =Z29*$AD$23 | =AB26:AB31+AC26:AC31 | =МУМНОЖ(Z12:AE17; AD26:AD31) | |
| =Z23*$AC$23 | =Z30*$AD$23 | =AB26:AB31+AC26:AC31 | =МУМНОЖ(Z12:AE17; AD26:AD31) | |
| =Z24*$AC$23 | =Z31*$AD$23 | =AB26:AB31+AC26:AC31 | =МУМНОЖ(Z12:AE17; AD26:AD31) |
8. Расчет величины риска (табл. 4.5).
Для этого сначала необходимо выделить ячейки (АА34:AF34), ввести формулу
=МУМНОЖ(ТРАНСП(AE26:AE31);Z4:AE9)
и нажать клавиши Ctrl+Shift+Enter одновременно.
В результате появится вектор 
.
Таблица 4.5
Расчет величины риска
| АА | Содержание ячейки | |
| 2,7712 | =(МУМНОЖ(AA34:AF34;AE26:AE31))^0,5 |
9. Подготовка данных для построения фронта эффективных портфелей из рискованных активов при заданном уровне доходности. Изменяя значения доходности и получая, таким образом, разные значения риска, формируем данные для построения фронта эффективных портфелей (табл. 4.6).
Таблица 4.6
Данные для построения фронта эффективных портфелей
| № п.п. | Риск | Доходность | № п.п. | Риск | Доходность |
| 1. | 2,7011 | 0,150 | 9. | 3,2485 | 0,550 |
| 2. | 2,7041 | 0,200 | 10. | 3,3856 | 0,600 |
| 3. | 2,7276 | 0,250 | 11. | 3,5333 | 0,650 |
| 4. | 2,7712 | 0,300 | 12. | 3,6902 | 0,700 |
| 5. | 2,8338 | 0,350 | 13. | 3,8552 | 0,750 |
| 6. | 2,9144 | 0,400 | 14. | 4,0273 | 0,800 |
| 7. | 3,0113 | 0,450 | 15. | 4,2057 | 0,850 |
| 8. | 3,1232 | 0,500 | 16. | 4,3895 | 0,900 |
10. Построение фронта эффективных портфелей (Вставка – Диаграмма – Точечная), рис. 4.2.
Рис. 4.2. Фронт эффективных портфелей из рискованных активов
при
Алгоритм построения модели Марковица в MS Excel (численное решение)
Модель Марковица в данном случае записывается следующим образом:
целевая функция:
линейные ограничения:
.
1. Подготовка данных для построения модели с использованием промежуточных результатов (средних значений доходности, ковариационной матрицы, риска), полученных в ходе аналитического решения задачи. Прежде всего необходимо эти результаты разместить на листе MS Excel так, как это показано на рис. 4.3. Заметим, что в ячейке $AP$12 содержится величина дисперсии, т.е. величина риска, возведенная в квадрат. Далее следует ввести матрицу из коэффициентов линейных ограничений (AJ13:AO19); строку из нулей, соответствующую первоначальной структуре портфеля (AJ24:AO24); единичный вектор (AP13:AP19).
2. Задание целевой функции путем введения в ячейку AQ12 формулы
=МУМНОЖ(AJ24:AO24;МУМНОЖ(AJ4:AO9;ТРАНСП(AJ24:AO24))).
3. Задание ограничений путем введения в ячейки (AQ13:AQ20) формулы
=МУМНОЖ(AJ13:AO20;ТРАНСП(AJ24:AO24)).
4. Вызов модуля Поиск решения (Сервис – Поиск решения). В результате появится окно, в котором необходимо:
1) установить целевую ячейку $AQ$12, равной минимальному значению (см. рис. 4.4);
Рис. 4.3. Первоначальные установки при построении модели Марковица
Рис. 4.4. Модуль Поиск решения (модель Марковица)
2) в строке «Изменяя ячейки» указать диапазон $AJ$24:$AO$24 (см. рис. 4.4);
3) указать ограничения (см. рис. 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) нажать кнопку Выполнить. В результате появится окно, представленное на рис. 4.5.
Рис. 4.5. Результаты Поиска решения (модель Марковица)
