Формулы табличной модели

После составления плановой таблицы необходимо связать показатели формулами для вычислений, как показано в таблице 2. В результате ввода формул для вычислений получим таблицу 3.

Таблица 2

Таблица 3

Поиск оптимального портфеля

Предлагаем вручную:

1. Сформировать оптимальный портфель с максимизацией доходности и минимальном риске. Менять удельные веса акций в портфеле, т.е. данные в диапазоне Доля, добиваясь максимальной доходности портфеля в ячейке Е18 и одновременно минимального риска (дисперсии) в ячейке G18. Следить, чтобы сумма весов портфеля в ячейке Е16 всегда равнялась 100%. К сожалению, в рамках принятой портфельной теории и в большинстве рыночных реалий доходность и риск изменяются в одном направлении. Они вместе либо увеличиваются, либо уменьшаются. Добиться экстремального улучшения одного из показателей без ухудшения другого не удастся.

1. Сформировать оптимальный портфель с максимизацией доходности при ограниченном риске. Задать приемлемое значение риска портфеля (дисперсии), например 7%. Менять удельные веса акций в портфеле, т.е. данные в диапазоне Доля, добиваясь максимальной доходности портфеля в ячейке Е18 и одновременно не превышать заданного значений риска (дисперсии) в ячейке G18. Контролировать, чтобы сумма весов (долей) портфеля в ячейке Е16 всегда равнялась 100%.

2. Сформировать оптимальный портфель с минимизацией риска при заданной величине доходности. Задать приемлемое значение доходности портфеля, например 16%. Менять удельные веса акций в портфеле, т.е. данные в диапазоне Доля, добиваясь минимального значения риска (дисперсии) в ячейке G18 и одновременно доходности портфеля в ячейке Е18 не менее заданного. Следить, чтобы сумма весов портфеля в ячейке Е16 всегда равнялась 100%.

Теперь вы убедились, что вручную составить оптимальный план почти невозможно. Предлагаем составить оптимальный план с помощью программы оптимизации.

Поиск портфеля с максимальной доходностью и ограниченным риском

Вызвать команду Поиск решения в группе Анализ вкладки Данные (для MS OFFICE 2007) или команду Сервис/Поиск решения (для MS OFFICE 97/2003). Появляется окно Поиск решения (рисунок 1), в котором в поле Целевая ячейка вводим адрес доходности портфеля Е18. В поле Изменяя ячейки вводим адреса диапазона Доля искомых весов активов портфеле Е10:Е14. . В поле Ограничения вводим 3 строки условий, ограничивающих область допустимых решений задачи:

1. значения долей активов в портфеле не должны быть отрицательными;

2. сумма долей активов в портфеле должна составлять 100%;

3. портфельный риск (дисперсия) должны быть не более 7,1%.

Рисунок 1 Диалоговое окно «Поиск решения»

После настройки модели и установки параметров алгоритма нажимаем кнопку Выполнить, в окне Результаты поиска решения указать Тип отчета: Результаты, Устойчивость, Пределы. Получен портфель максимальной доходности при заданном уровне риска.

Поиск портфеля с заданной доходностью и минимальным риском

В строках 21:29 таблицы 2,3 отражены две модели оптимизации портфелей: модель максимизации доходности и модель минимизации риска. Загрузим для решения модель минимизации риска. Вызвать команду Поиск решения. Появляется окно Поиск решения, в котором нажать кнопку Параметры. В окне Параметры поиска решения нажать кнопку Загрузить модель. В окне Загрузка модели (рисунок 2) в поле Задайте область модели ввести диапазон хранения модели минимального риска D21:D29.

Рисунок 2 Диалоговое окно «Загрузка модели»

После нажатия ОК появится окно для подтверждения загрузки новой модели (рисунок 3).

Рисунок 3 Окно подтверждения загрузки новой модели

Щелкнуть ОК. Появится окно неверной формулы ограничений (рисунок 4).

Рисунок 4 Окно неверной формулы ограничений

Щелкнуть ОК. Проверить модель в окне Поиск решения (рисунок 5).

Рисунок 5 Диалоговое окно «Поиск решения»

В модели пропало последнее ограничение по доходности портфеля. Это проблема русификации Excel. Добавить ограничение с английской точкой в дроби, как показано на рисунке 6. Выполнить Поиск решения (рисунок 7). Вы получите портфель минимального риска при заданном уровне доходности.

Рисунок 6 Диалоговое окно «Добавление ограничений»

Рисунок 7 Диалоговое окно «Поиск решения»

Одноиндексная математическая модель рынка при известных Бета и остаточной дисперсии (несистемный, индивидуальный риск) акций позволяет сформировать оптимальные инвестиционные портфели двух типов: портфель максимальной доходности при ограниченном риске и портфель минимального риска при заданной доходности.