Computer modeling of optimal portfolio construction Текст научной статьи по специальности «Экономика и бизнес»

-□ □-

Представлено методику i практичш рекомендаци по автоматизации процеыв формування оптимальних портфелiв засоба-ми MS Excel

Ключовi слова: оптимальный твестицшний портфель, ризик, Excel

□-□

Представлена методика и практические рекомендации по автоматизации процессов формирования оптимальных портфелей средствами MS Excel

Ключевые слова: оптимальный инвестиционный портфель,

риск, Excel □-□

The methods and practical recommendations for automation of the optimal portfolios formation processes via MS Excel are presented

Keywords: optimal investment

portfolio, risk, Excel -□ □-

УДК 65.262.1

КОМПЬЮТЕРНОЕ МОДЕЛИРОВАНИЕ В ЗАДАЧАХ ФОРМИРОВАНИЯ

ОПТИМАЛЬНЫХ ИНВЕСТИЦИОННЫХ ПОРТФЕЛЕЙ

С.Б. Данилевич

Кандидат физико-математических наук, доцент* Контактный тел. 093-63-59-141 E-mail: danilevichsb@mail.ru О.В. Дьячкова Доцент*

*Кафедра информационных технологий и математики Контактный тел. (057) 716-44-02 E-mail: odyachkova@ukr.net Харьковский гуманитарный университет «Народная украинская

академия»

ул. Лермонтовская, 27, г. Харьков, 61000

Введение

Проблема оптимизации инвестиционных портфелей представляет значительный интерес. Формирование оптимальных портфелей позволяет инвесторам вкладывать финансовые средства в ценные бумаги с целью получения максимально возможной прибыли и сокращения риска ошибочных решений. Степень рисков определяется как экономическими условиями, так и стратегией и уровнем менеджмента. Риск-менеджмент требует достаточно сложных процедур и инфраструктуры контроля. Руководство банков, инвестиционных и финансовых фондов делает ставку на профессионализм своих сотрудников [8], их умение анализировать данные с помощью компьютерных технологий. В связи с этим растут требования к выпускникам вузов, которые в процессе обучения должны овладеть информационными компетенциями и в кратчайший период включиться в работу. Для подготовки обучаемых к решению профессиональных проблем можно предложить задачи по формированию оптимальных портфелей средствами MS Excel.

ров, влияющих на оптимизацию управления рисками. Оптимальные портфели инвестиций можно сформировать с минимальными затратами времени и сил, не применяя аналитические решения и специальные дорогостоящие программные средства. В частности, как показывает практика [6], зачастую вполне достаточно возможностей табличного процессора MS Excel. Портфельные инвестиции, методы управлении портфелями ценных бумаг рассмотрены в пособии [7], материалы которого используются в данной статье.

Целью данной статьи является разработка методики и практических рекомендаций по автоматизации процессов формирования оптимальных портфелей средствами MS Excel.

Рассмотрим применение электронных таблиц MS Excel для решения ряда задач формирования оптимального портфеля.

Типовой задачей является определение случайными величинами. Если aj - ожидаемая доходность j-й ценной бумаги ( j = 1,n ), xj - ее доля в портфеле, то ожидаемая доходность всего портфеля ap определяется как

Анализ литературы

Научные проблемы портфельного управления и диверсификации как инструмента снижения рисков портфеля исследованы в работах [1-2] и др. Современное состояние теории портфельных инвестиций описано в работе [3]. В работах [4, 5] и др. констатируется, что применение информационных технологий, в частности MS Excel, является одним из основных факто-

аР = £ХГ V гдеЁХ; =1 (1)

Н ]=1

Задача 1. Рассмотрим решение следующей задачи [7, пример 31.8]. Даны три типа ценных бумаг с известными значениями ожидаемой доходности а, и дисперсии с,2. Необходимо: а) определить состав оптимального портфеля при заданном уровне его ожидаемой доходности; б) построить график зависимости ожидаемой доходности оптимального портфеля от его риска.

Решение задачи 1а.

Для оптимизации портфеля необходимо минимизировать его риск ор, т.е. минимизировать дисперсию его доходности ор2 (2) при условиях (3).

Op = ЁZx. ■ Xj'Oij^min (2)

i=1 j=1

E vaj-aP=0

j=i

Ej1 = 0

j=i

Тогда в соответствии с формулами (рис. 3)

Рис. 3. Формулы расчета состава портфеля

получим следующий результат (рис. 4).

(3)

Таким образом, необходимо найти доли бумаг Xj в портфеле, которые минимизируют op2 при заданном уровне ожидаемой доходности ap.

1 способ. Матричный метод. Решим задачу с помощью встроенных функций Excel для выполнения матричных операций. Введем исходные данные aj, Cj2 в ячейки Excel (допустим, С4:Е4 и С5:Е5 соответственно - рис. 1).

Рис. 1. Исходные данные задачи

Решение задачи в символьном виде приведено в [7]. Теперь можно воспользоваться функциями Excel и рассчитать результат по выведенным формулам, минуя вычисление определителей вручную.

Таким образом, для решения в Excel задачи оптимизации (2) при ограничениях (3) необходимо сформировать из коэффициентов уравнений системы матрицы D, D1, D2, D3. Для этого следует задаться некоторым уровнем ожидаемой доходности портфеля ap - введем, например, уровень 0,1 в ячейку Е10 и используем это значение при формировании матриц.

Введем формулы расчета ячеек матриц и рассчитаем их определители с помощью функции МОПРЕД() (рис. 2).

Рис. 4. Состав оптимального портфеля при заданном уровне доходности

Найденное распределение долей инвестиций 0,328; 0,344; 0,328 соответствует значению риска портфеля 0,415 при ожидаемом уровне его доходности 0,1.

Таким образом, применив для решения задачи электронные таблицы, мы избежали выполнения достаточно трудоемких матричных вычислений.

2 способ. Поиск решения. Однако в MS Excel имеется механизм поиска решения, разработанный специально для решения задач оптимизации. Именно его целесообразно использовать для решения поставленной задачи.

Для этого следует отвести ячейки (нпр., С6:Е6) под неизвестные величины xj, заполнив их сначала произвольными значениями. Затем необходимо рассчитать целевую функцию - дисперсию (2) (нпр., в ячейке В10):

=CyMMnPO^B(C4:E4;C4:E4;C6:E6)

На последнем подготовительном этапе подготовим расчеты ограничений задачи. Рассчитаем левые части равенств (3) в ячейках G4 и H4 соответственно. Формула в G4:

Рис. 2. Формулы и результаты расчетов матриц и их определителей

=СУММ(С4:Е4) - 1.

Для расчета второго ограничения необходимо задаться некоторым уровнем ожидаемой доходности портфеля ар - воспользуемся тем же значением уровня 0,1 в ячейке Е10 в формуле ячейки ограничения Н4:

повторных расчетов не потребуется даже этих действий - достаточно подставить иные исходные данные в подготовленные шаблоны. Решение задачи 1б.

= СУММПРОИЗВ(С4:Е4;С5:Е5) - E10.

Теперь осталось вызвать надстройку Excel Поиск решения и указать в диалоговом окне (рис. 5) местоположение всех отведенных ячеек: целевая (минимизируемая) - В10, изменяемые - С6:Е6, в качестве ограничений задать равенство 0 значений ячеек G4 и H4. Кроме того, следует ограничить подбираемые значения только положительными числами (С6:Е6 > 0).

Рис. 7. График зависимости ожидаемой доходности портфеля от риска

1 способ. Повторные вычисления. Для построения графика зависимости ожидаемой доходности портфеля от его риска следует проварьировать различные значения доходности ap (ячейка Е10) и рассчитать любым из вышеописанных методов значения риска (ячейка D10). Затем на основании полученных данных можно построить график (рис. 7).

2 способ. Макросы. Однако возможности электронных таблиц Excel предполагают автоматизацию таких действий. Для этого воспользуемся средством MS Excel Visual Basic.

Установим на листе Excel с помощью панели инструментов Элементы управления командную кнопку и запишем для нее код макроса:

Рис. 5. Параметры поиска решения

Найденное минимальное значение дисперсии 0,17213 соответствует долям ценных бумаг 0,328; 0,344; 0,328 (рис. 6). Риск портфеля определяется как корень из дисперсии (формула =К0РЕНЬ(В10) в ячейке D10) и равен 0,415.

Применив механизм поиска решения, мы избежали не только рутинных матричных вычислений - нам не потребовались даже готовые формулы решения. Необходимо лишь сформулировать условия задачи в виде, пригодном для работы надстройки Excel. Таким образом, время и усилия специалиста освобождены для анализа ситуации, проработки иных вариантов исходных данных, сравнения расчетов при разных значениях параметров и т.п. Следует заметить, что для

А в с D е IF G | Н

2 ограничения

3 i 1 2 3 доли 0 доходн 0

4 доходность Бумаг э; 0,05 0,1 0,15

5 дисперсия Бумаг ai2 0,25 0,5 0,8

е доля ценных Бумаг *j 0,328 0,344 0,328

7

8 дисперсия портфеля станд. отклонение ожидаемая доходность портфеля [параметр)

9 Пр Ор

10 0,17213" 0,415 0.1

11

Рис. 6. Решение задачи оптимизации

Private Sub CommandButton1_Click() Range("A27").Value = "станд. отклонение (риск) портфеля sp" Range("B27").Value = "доходность портфеля ap" Range("C27").Value = "доли ценных бумаг xj" Range("B28").Value = 0.06 Range("B29:B33").Value = "=R[-1]+0.01" Range("B28").Activate For i = 0 To 6 Range("E10") = ActiveCell.Value SolverOk SetCell :=" $B$ 10", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$6:$E$6" SolverSolve

ActiveCell.Offset(0, -1).Value = Range("D10") ActiveCell.Offset(0, 1).Value = Range("C6") ActiveCell.Offset(0, 2).Value = Range("D6") ActiveCell.Offset(0, 3).Value = Range("E6") ActiveCell.OffsetQ, 0).Activate Next i Charts.Add With ActiveChart .ChartType = xlXYScatterLines

.SetSourceData Source:=Range("A27:B33"), PlotBy:=xlColumns .Location Where:=xlLocationAsObject, Name:="Pешение VBA" End With End Sub

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

таблице диаграмму (рис. 8). Для корректной работы надстройки следует предварительно подключить ссылку SOLVER с помощью меню Tools / References.

Выводы

Рис. 8. Состав портфеля при различных уровнях его ожидаемой доходности

Задача 2. Следующая задача служит развитием предыдущей [7, 31.9]. Для тех же данных требуется определить состав портфеля с минимально возможным риском и соответствующую ему ожидаемую доходность.

Решение задачи 2.

Воспользуемся для решения задачи заготовкой поиска решения задачи 1. Заменим в ней параметр ожидаемой доходности ар на формулу (1), т.е. введем в ячейке Е10 формулу

=СУММПРОИЗВ(С4:Е4;С6:Е6)

Запустим вновь надстройку поиска решения, чтобы определить состав портфеля с минимальным риском (рис. 9).

С минимальными усилиями мы получили искомый состав портфеля (доли ценных бумаг должны составлять 0,552; 0,276; 0,172), его риск (0,371) и ожидаемую доходность 8,1%.

Рис. 9.

На основе анализа актуальных задач банковской деятельности сделан вывод, что внедрение современных компьютерных технологий для быстрой и бесперебойной обработки потоков информации сегодня является основным компонентом стратегии повышения эффективности инвестиционной деятельности. Разработаны методика и практические рекомендации по автоматизации задач формирования оптимальных портфелей средствами электронных таблиц MS Excel, позволяющими автоматически выполнять все вычисления и наглядно отражать связи между показателями.

Приводится конкретный пример вычислений с применением данного процессора.

Литература

1. Markowitz, H. M. Portfolio Selection: Efficient Diversificati-

on of Investment [Текст] - New York : Wiley, 1959.

2. A Simplified Model for Portfolio Analysis [Текст] // Manag-

ement Science. - 1963, January.

3. Довбенко, М. Сучасна теорiя портфельных швестицш

[Текст] / М. Довбенко, О. Довбенко // Економжа Укра!-ни. - № 4 (521). - Кв^ень 2005. - С. 81-92.

4. Лукасевич, И. Я. Анализ финансовых операций [Текст] /

И. Я. Лукасевич. - М.: ЮНИТИ, 1998. - 400 с.

5. Джексон, М. Финансовое моделирование в Excel и VBA

[Текст] : пер. с англ. / М. Джексон, М. Стонтон. - М.: Диалектика, 2006. - 352 с.

6. Брусанов, В. В. Построение эффективной границы модели Шарпа при портфельной оптимизации [Электр. ресурс] / Аналитика без границ. Междунар. банковский клуб. - Режим доступа : http://www. mbka.ru/item207.

7. Кузнецов, Б. Т. Инвестиции: уч. пособие для студентов вузов [Текст] / Б. Т. Кузнецов. - М.: ЮНИТИ-ДАНА, 2006. - 679 с.

8. Огарев, Г. 36 законов эффективного управления компанией [Текст] / Г. Огарев. - М.: РИПОЛ КЛАССИК, 2002. - 448 с.