<<
>>

4.3. Определение удельных весов активов в оптимальных портфелях и эффективной границы с помощью программы Excel

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

6 Программа Excel позволяет решать системы линейных уравнений. Алгоритм решения системы уравнений с помощью Excel приводится в приложении 1 к главе 5.

7 Найденное решение соответствует минимуму функции (4.13), так как вторые производные системы уравнений (4.15) положительны. (Условием минимума функции является положительное значение ее второй производной).

163

Глава 4. Определение эффективной границы и оптимальных портфелей

окошке переключателя курсором, нажав левую клавишу мыши, ставим флажок. Выбираем курсором кнопку ОК и нажимаем левую клавишу мыши. Надстройка Поиск решения установлена.

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

Пример 1.

Стандартное отклонение доходности первой акции (в десятичных значениях) равно 0,2, второй - 0,3, третьей - 0,4. Ковариация доходностей первой и второй бумаг составляет 0,0018, первой и третьей - 0,002, второй и третьей -0,008. Ожидаемая доходность (в десятичных значениях) первой бумаги равна 0,12, второй - 0,16, третьей - 0,22. Определить уд. веса бумаг в портфеле с риском 0,35, если уд. веса бумаг не могут принимать отрицательные значения.

Решение.

Расположим в ячейках Al, А2 и A3 значения ожидаемых доходностей соответственно первой (0,12), второй (0,16) и третьей (0,22) акций; в ячейках В1, С2, D3 - значения дисперсий первой (0,04), второй (0,09) и третьей (0,16) акций; в ячейке В2 - значение ковариации доходностей первой и второй бумаг (0,0018), в ВЗ - первой и третьей (0,002), в СЗ - второй и третьей (0,008).

Необходимо задать уд.

веса акциям для некоторого начального портфеля. Задаем их произвольно. Это необходимо для того, чтобы связать все уд. веса бумаг в портфеле в единую формулу и приравнять их к единице. В последующем при задании разного уровня риска портфеля уд. веса в данных ячейках будут изменяться, показывая решение задачи. Пусть уд. вес первой бумаги в десятичных значениях 0,2, второй - 0,3, третьей - 0,5.8 Соответственно расположим их в ячейках Е1-ЕЗ. В ячейке Е4 представим сумму ячеек с El по ЕЗ. Это можно сделать, напечатав в ячейке Е4 следующую формулу:

=Е1+Е2+Е3,

и нажав клавишу Enter. Поскольку сумма всех весов акций в портфеле должна равняться единице, то в данной ячейке появится единица.9 В ячейку F1 помещаем значение ожидаемой доходности портфеля, т.е. печатаем:

=А1*Е1+А2*Е2+АЗ*ЕЗ

В результате должны получить цифру 0,182.10 В ячейку F2 помещаем формулу риска портфеля, т.е. печатаем:

Для большого количества бумаг в портфеле удобно поступить следующим образом: задать уд. веса всем активам кроме последнего равными нулю, а последнего единице. Тогда в нашем примере в ячейках Е1-Е2 расположатся нули, а в ячейке ЕЗ единица.

9 Суммировать ячейки можно и с помощью следующего алгоритма: в ячейке Е4 печатаем: =СУММ(Е1:ЕЗ). Еще более удобно суммировать ячейки следующим приемом: наводим курсор на ячейку Е1, нажимаем левую клавишу мыши и, удерживая ее, проводим курсор вниз до ячейки Е4 включительно, и отпускаем клавишу. После этого наводим курсор на знак суммы X в строчке меню, и нажимаем левую клавишу мыши.

10 Как было показано в главе 1.1.6. ожидаемую доходность более удобно задать с помощью формулы =СУММПРОИЗВ или программы "Мастер функций".

164

Глава 4. Определение эффективной границы и оптимальных портфелей

= Я1*?1Л2 + С2*?2Л2 + ?3*?3Л2 + 2*Ш*?2*Я2 + + 2*?1*?3*ЯЗ + 2*?2*?3*СЗ

Нажимаем клавишу Enter. В данной ячейке должна появиться цифра 0,052716. Это риск портфеля, представленный дисперсией. (Стандартное отклонение доходности портфеля равно 0,2296).

Рассчитаем уд.

веса активов в оптимальном портфеле с риском 0,35 (т.е. 35%). Для этого выбираем курсором меню Сервис и щелкаем мышью. Появилось выпадающее меню. Курсором выбираем команду Поиск решения и нажимаем левую клавишу мыши. Появляется окно диалога "Поиск решения" (см. рис. 4.12).

Поиск решения

Установить целевую ячейку: |

Равной; (• максимальному значению *~ значению: (о™

С м^1нимальному значению { Изменяя ячейки: --------- ----------.....

\\-

[ ^граничения: ......—...........-......-........-......-.....:.....-------------------

"3 Предположить I

"3 Добавить

изменить

Удалить

j?jxj Выполнить" j

Закрыть

Параметры

Восстановить

Справка

Рис. 4.12. Окно диалога "Поиск решения "

Первая строчка в окне называется "Установить целевую ячейку". В качестве целевой задаем ячейку F1. В ней отражается доходность портфеля. Для этого наводим курсор на знак !Э в поле данной строки и щелкаем мышью. Окно "Поиск решения" превращается в поле строки. Наводим курсор на ячейку F1 и нажимаем левую клавишу мыши. Вновь наводим курсор на знак 51 и щелкаем мышью. Окно "Поиск решения" появляется целиком. В следующей строке окна стоит слово "Равной". Напротив него два круглых поля с надписями "максимальному значению" и "минимальному значению". Выбираем поле "максимальному значению". Если оно уже активизировано, то в нем стоит точка. Если поле является чистым, то наводим на него курсор и нажимаем левую клавишу мыши. В поле появилась точка. Следующая строка называется "Изменяя ячейки". В поле под данной строкой вводим ячейки от Е1 до ЕЗ. Делаем это следующим образом. Наводим курсор на знак !3 в поле данной строки и щелкаем мышью. Окно "Поиск решения" превращается в поле строки. Наводим курсор на ячейку Е1, нажимаем левую клавишу мыши и, удерживая ее, доводим до ячейки ЕЗ, отпускаем клавишу. Вновь наводим курсор на знак 3 и щелкаем мышью. Окно "Поиск решения" появляется целиком. Ниже расположена надпись "Ограничения".

В поле под этой надписью вводим ограничения модели. Первое ограничение заключается в том, что сумма всех уд. весов активов должна равняться единице. Ограничения задаем следующим образом. Наводим кур

165

Глава 4. Определение эффективной границы и оптимальных портфелей

сор на кнопку "Добавить" и нажимаем левую клавишу мыши. Появляется окно диалога "Добавление ограничения" (см. рис. 4.13). В нем три прямоугольных

Добавление ограничения

Ссылка на ячейку:

Р-~-3F

OK I Отмена

Рис. 4.13. Окно диалога "Добавление ограничения"

поля. В левое поле под строкой "Ссылка на ячейку" вносим адрес Е4. Для этого наводим курсор на знак 3 в поле этой строки и щелкаем мышью. Окно "Добавление ограничения" превращается в поле строки. Наводим курсор на ячейку Е4 и щелкаем мышью. Вновь наводим курсор на знак !Э и щелкаем мышью. Окно "Добавление ограничения" появляется целиком. В среднем поле наводим курсор на треугольник с правого края и нажимаем левую клавишу мыши. Открывается выпадающее меню. Выбираем в нем знак "=", т.е. наводим на него курсор и нажимаем левую клавишу мыши. В правом поле "Ограничение" печатаем цифру 1. Наводим курсор на команду "Добавить" и нажимаем левую клавишу мыши. Поля окна диалога "Добавление ограничения" вновь становятся свободными для внесения нового ограничения. Следующее ограничение состоит в том, что уд. веса акций в портфеле не должны быть отрицательными. В поле "Ссылка на ячейку" наводим курсор на знак Z3 в поле этой строки и щелкаем мышью. Окно "Добавление ограничения" превращается в поле строки. Наводим курсор на ячейку Е1, нажимаем левую клавишу мыши и, удерживая ее, доводим до ячейки ЕЗ, щелкаем мышью. Вновь наводим курсор на знак 31 и щелкаем мышью. Окно "Добавление ограничения" появляется целиком. В среднем поле наводим курсор на треугольник с правого края и нажимаем левую клавишу мыши. Открывается выпадающее меню. В среднем поле нажимаем на треугольник справа. В выпадающем меню курсором выбираем символ ">=" и щелкаем мышью.

В правом поле печатаем цифру 0. Наводим курсор на команду "Добавить" и щелкаем мышью. Поля окна диалога "Добавление ограничения" вновь становятся свободными. Вводим третье ограничение: риск портфеля равен 0,35. Риск вводим как значение дисперсии доходности портфеля. Дисперсия портфеля представлена в ячейке F2. В поле "Ссылка на ячейку" наводим курсор на знак 13 в поле этой строки и щелкаем мышью. Окно "Добавление ограничения" превращается в поле строки. Наводим курсор на ячейку F2 и щелкаем мышью. Вновь наводим курсор на знак 3 и щелкаем мышью. Окно "Добавление ограничения" появляется целиком. В среднем поле наводим курсор на треугольник с правого края и щелкаем мышью. Открывается выпадающее меню. Выбираем в нем курсором символ "=" и щелкаем мышью. В правом поле печатаем цифру 0,1225 (это дисперсия портфеля: 0,352 =0,1225). Все ограничения введены, поэтому курсором выбираем команду ОК и щелкаем мышью. Появляется окно диалога "Поиск решения". В правом верхнем углу

Ограничение:

13

Добавить

31

Справка

166

Глава 4. Определение эффективной границы и оптимальных портфелей

диалога "Поиск решения". В правом верхнем углу находится команда "Выполнить". Наводим на нее курсор и нажимаем левую клавишу мыши. В ячейках Е1-ЕЗ появилось решение, т.е. уд. веса акций в портфеле с риском 35%. В ячейке F1 появилось значение ожидаемой доходности данного портфеля.

Появилось окно "Результаты поиска решения". В нем предлагается на выбор два действия "Сохранить найденное решение" и "Восстановить исходные значения". Круглое окно "Сохранить найденное решение" помечено точкой. Если мы заинтересованы сохранить полученное решение, то наводим курсор на команду ОК и щелкаем мышью. Если мы хотим вернуться к предыдущим значениям, то наводим курсор на круглое поле слева от надписи "Восстановить исходные значения" и щелкаем мышью. В поле появляется точка. После этого наводим курсор на команду ОК и щелкаем мышью. В ячейках Е1-Е4, Fl, F2 появятся начальные значения.

В результате решения задачи в ячейках Е1-ЕЗ, Fl, F2 были получены соответственно следующие цифры: 0; 0,138018; 0,861982; 0,211719; 0,1225.

Это значит, что уд. вес первой бумаги в портфеле должен составить 0%, второй -13,8%, третьей - 86,2%. При этом ожидаемая доходность портфеля составит 21,17%, а риск будет 35%.

Мы нашли ожидаемую доходность и уд. веса оптимального портфеля для одного значения риска. Если повторить решение для разных уровней риска, то получим ряд значений ожидаемой доходности, которые позволят построить эффективную границу для данного набора бумаг. Чтобы определить доходность портфеля для нового уровня риска, например, 0,36 (т.е. 36%) надо поступить следующим образом. Выбираем курсором меню Сервис и щелкаем мышью. Появляется выпадающее меню. Курсором выбираем команду Поиск решения и щелкаем мышью. Открывается окно "Поиск решения". В данном окне сохранились все параметры, которые были введены ранее. Поэтому, для определения состава портфеля для нового уровня риска необходимо изменить в поле "Ограничения" только последнюю строку, которая относится к риску портфеля. Для этого наводим на нее курсор и нажимаем левую клавишу мыши. Строка выделяется синим цветом. После этого наводим курсор на команду "Изменить" и нажимаем левую клавишу мыши. Появилось окно "Изменение ограничения". В правом поле "Ограничение" печатаем новую цифру дисперсии. Для риска 0,36 это 0,1296. Наводим курсор на команду ОК и щелкаем мышью. Появляется окно диалога "Поиск решения". Наводим курсор на команду "Выполнить" и щелкаем мышью. В ячейках Е1-ЕЗ появились новые уд. веса акций в портфеле с риском 36%, а в ячейке F1 - значение ожидаемой доходности портфеля. Появилось окно "Результаты поиска решения". В нем выбираем команду "Сохранить найденное решение", наводим курсор на команду ОК и щелкаем мышью. Аналогичным образом, изменяя только одно ограничение - риск портфеля, находим оптимальные портфели для других значений стандартных отклонений.

В задаче одним из ограничений выступала не отрицательность уд. весов акций в портфеле. Если данное условие не вводить, т.е. исключить второе ограничение Е1:ЕЗ >=0, то получим решение оптимизационной задачи, допускающей короткие продажи акций.

167

Глава 4. Определение эффективной границы и оптимальных портфелей

Если портфель насчитывает большое количество бумаг, то в рамках представленного выше алгоритма решения задачи не очень удобно вводить в ячейку F2 формулу риска портфеля. Однако эту проблему легко снять, если воспользоваться матричным исчислением для определения риска портфеля, которое было представлено в примере 2 главы 1.2.5. Дополним текущий пример данным алгоритмом.

Заносим в ячейки с А1 по A3 ожидаемые доходности бумаг, в блок ячеек B1:D3 - ковариационную матрицу, в ячейки с Е1 по ЕЗ - уд. веса акций, в ячейку F1 - формулу ожидаемой доходности портфеля. В ячейках с А5 по С5 расположим транспонированную матрицу столбец уд. весов бумаг. Для этого выделяем блок А5:С5, т.е. наводим курсор на ячейку А5, нажимаем левую клавишу мыши и, удерживая ее, доводим до ячейки С5, отпускаем клавишу. Печатаем здесь формулу:

=ТРАНСП(Е1:ЕЗ)

После этого одновременно нажимаем клавиши Ctrl, Shift и Enter. В ячейках А5, В5 и С5 соответственно появятся цифры 0,2, 0,3 и 0,5. Теперь перемножим матрицу-строку А5:С5 и ковариационную матрицу B1:D3. Поэтому выделим для получения ответа интервал А7:С7. Для этого наводим курсор на ячейку А7, нажимаем левую клавишу мыши и, удерживая ее, доводим мышь до ячейки С7, и отпускаем клавишу. В выделенной строке печатаем формулу:

=МУМНОЖ(А5:С5;В1 :D3)

и одновременно нажимаем клавиши Ctrl, Shift и Enter. В ячейках получаем цифры 0,00954, 0,03136, 0,0828. Теперь перемножим полученную в ячейках А7:С7 матрицу строку на матрицу столбец в ячейках Е1:ЕЗ. Умножение дает одну цифру, поэтому для ответа уже известным способом выделяем ячейку F2 и печатаем в ней формулу:

=МУМНОЖ(А7:С7;Е1 :ЕЗ)

и нажимаем Enter, получаем цифру 0,052716. После этого переходим к использованию команды "Поиск решения". Все действия выполняются аналогично выполненным ранее. В результате для риска портфеля, представленного дисперсией 0,1225 получаем результат как показано на рис. 4.14. А В С D Е F G 1 0,12 0,04 0,0018 0,002 0 0,211719 2 0,16 0,0018 0,09 0,008 0,138018 0,1225 3 0,22 0,002 0,008 0,16 0,861982 4 1 Т" 0 0,138018 0,861982 о 7 0,001972- 0,019317 0,139021 8 Рис. 4.14. Определение оптимального портфеля

Следует также отметить, что транспонировать и перемножить матрицы можно и с помощью программы "Мастер функций" (см. главу 1.2.5. пример 2).

168

Глава 4. Определение эффективной границы и оптимальных портфелей

Команда "Поиск решения" также позволяет определить портфель с минимальным уровнем риска для требуемого уровня доходности. Все действия выполняются аналогично тому, как было показано выше, но с двумя отличиями. Во-первых, в окне "Поиск решения" выбираем строку "минимальному значению". Во-вторых, в строку "Установить целевую ячейку" заносим ячейку F2, поскольку наша задача состоит в минимизации дисперсии портфеля. В третьих, в качестве ограничения теперь учитываем не дисперсию портфеля, а ожидаемую доходность. Так, если мы определяем портфель с минимальной дисперсией для доходности 18%, то в окно "Ограничения" внесем запись Fl=0,18. В частности, решение примера из главы 4.2. в окне диалога "Поиск решения" будет представлено следующим образом (см. рис. 4.15):

Поиск решения

Установить целевую ячейку: Равной; О максимальному

(• минимальному значению Изменяя ячейки:

jI*E$l:*E$3

Граничения;

F$2 3

значению f значению: |0

$Е$1;$Е$3 >=0 $Е$4=*1 $F$1 =0,18

t; 21*1

Выполнить | Закрыть j

Рис. 4.15. Решение задачи на минимизацию риска портфеля

Соответственно получим следующий результат:

А

0,12 0,16 0,22

В

0,04 0,0018 0,002

0,0018 0,09 0,008

0,186667 0,355556 0,457778 0,009022 0,035998 0,076462

D Е F

0,002 0,186667 0,18 0,008 0,355556 0,049486 0,16 0,457778 1

Рис. 4.16. Результат решения задачи на минимизацию риска портфеля

169

Глава 4. Определение эффективной границы и оптимальных портфелей

<< | >>
Источник: А.Н. Буренин. Управление портфелем ценных бумаг. 2-е издание, исправленное и дополненное. Научно-техническое общество имени академика СИ. Вавилова . 2008

Еще по теме 4.3. Определение удельных весов активов в оптимальных портфелях и эффективной границы с помощью программы Excel:

  1. 4.5. Определение удельных весов активов в рыночном портфеле при возможности заимствования и кредитования с помощью программы Excel
  2. 5.1.1.2. Определение оптимального портфеля при копировании индекса с помощью программы Excel
  3. 5.2.1.3. Оценка величины не хеджируемого риска портфеля. Определение коэффициента детерминации портфеля с помощью программы Excel
  4. Приложение 7. Использование программы Excel для построения графика границы Марковца портфелей из двух активов
  5. 1.2.2. Определение дисперсии и стандартного отклонения доходности актива с помощью программы Excel
  6. ГЛАВА 4. ОПРЕДЕЛЕНИЕ ЭФФЕКТИВНОЙ ГРАНИЦЫ И ОПТИМАЛЬНЫХ ПОРТФЕЛЕЙ
  7. 4.7. Определение оптимального портфеля с помощью линейного программирования
  8. 2.1. Эффективная граница портфелей, состоящих из актива без риска и рискованного актива
  9. 3.5. Определение уравнения линии характеристики и коэффициента детерминации с помощью программы Excel
  10. ПРИЛОЖЕНИЕ 5. Определение значений вероятности нормально распределенной стандартной случайной величины с помощью программы Excel
  11. ПРИЛОЖЕНИЕ 4. Определение премии европейского опциона колл на акции, по которым не выплачиваются дивиденды, с помощью программы Excel
  12. 5.1.1.5. Определение дюрации Маколея и модифицированной дюрации облигации с помощью программы Excel
  13. 4.1. Определение эффективной границы с помощью кривых изосредних и изодисперсий