ОПТИМИЗАЦИЯ ПОРТФЕЛЯ ИНВЕСТИЦИЙ С ПОМОЩЬЮ EXCEL

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

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

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

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

При инвестировании ценных бумаг инвестор формирует портфель этих бумаг и использует для этого наиболее известные и апробированные на практике модели: Марковица, Шарпа, Тобина и другие. Математические модели всех портфелей в значительной степени похожи друг на друга: имеется критерий, который необходимо оптимизировать (как правило, минимизировать) по некоторым входящим в него параметрам, на которые наложены определенные ограничения.

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

Необходимо выбрать те инвестиционные проекты, которые обеспечат максимальное совокупное значение показателя NPV.

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

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

Математическая оптимизация представляет собой процесс нахождения экстремума (максимума или минимума) функции при заданных ограничениях (условная оптимизация) или без ограничений (безусловная оптимизация).

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

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

Начиная с версии Excel 2007 кнопка для запуска Поиска решения появится на вкладке Данные. В версиях до Excel 2007 аналогичная команда появится в меню Сервис.

Поиск решения позволяет анализировать задачи трех типов:

1)             линейные (все зависимости между переменными задачи линейны);

2)             нелинейные (между переменными задачи существует хотя бы одна непропорциональная зависимость);

3)              целочисленные (результаты решения должны быть целыми числами). Продемонстрируем на конкретном примере.

Фирма рассматривает возможность участия в финансировании десяти проектов, предполагаемые условия реализации которых, приведены в таблице 1. Инвестиционный бюджет фирмы равен 450000 рублей.

 Таблица 1.

Исходные данные по проектам

Следует значения инвестиций взять со знаком «-» (минус). С использованием известных финансовых функций в таблице 2 определим значения NPV, PI и IRR (функции ЧПС и ВСД).

Таблица 2.

 Расчетная таблица оценки инвестиционных проектов

ОПРЕДЕЛИМ ОПТИМАЛЬНЫЙ НАБОР ПОРТФЕЛЯ ИНВЕСТИЦИЙ И ДОЛЮ УЧАСТИЯ В КАЖДОМ ПРОЕКТЕ

Таблица 3. 

Данные о доле участия в каждом проекте

Примечание: В качестве показателя нормы доходности используется ставка IRR (внутренняя норма рентабельности). Этот показатель для каждого инвестора является определенной заранее величиной, с использованием которой осуществляется и отбор проектов для инвестирования, и все иные расчеты, связанные с оценкой перспектив участия инвестора в проекте. Кроме всего прочего, удобство использования в качестве критерия ставки IRR состоит в том, что инвестор всегда имеет представление о её минимальном размере, поскольку в практике инвестиционной деятельности этот показатель является одним из базовых критериев при отборе проектов.

 1)     В качестве управляемых переменных выбирается доля участия в каждом проекте, все переменные не должны превышать 1, так как проекты не финансируются больше одного раза. Под каждую управляемую переменную резервируется пустая ячейка, в нашем случае ячейки В36:В45.

2) В качестве целевой функции выступает величина совокупного NPV от всех проектов. Создается целевая ячейка, в которую заносится формула расчета совокупного NPV. Выберем ячейку, например, А80. Над ячейкой запишем имя показателя. Для расчета NPV используется математическая функция СУММПРОИЗВ. В качестве первого массива указываются адреса ячеек со значениями NPV для каждого проекта, в качестве второго массива - адреса ячеек с управляемыми переменными.

1)   В качестве ограничений модели учитывается инвестиционный бюджет и тот факт, что все переменные лежат на интервале от 0 до 1. Формулу с ожидаемыми инвестиционными затратами поместим в ячейку А106. Для расчета инвестиционных затрат используется математическая функция СУММПРОИЗВ. В качестве первого массива указываются адреса ячеек со значениями объема инвестиций для каждого проекта, в качестве второго массива - адреса ячеек с управляемыми переменными.

1)    Формирование математической модели: на этом этапе в виде функции записывается цель задачи, а в виде неравенств и уравнений ее ограничения. Математическая модель задачи имеет вид:

Z = 10241× х1 + 51551× х2 + 23838× х3 + 35732 × х4 +11416 × х5 + 33646× х6 + 51415× х7 + 12238× х8 + 16943× х9 +8483× х10 ® max

67640 × х1 + 5103440 × х2 + 269727 × х3 + 92352 × х4 +71191× х5 + 95980× х6 + 87051× х7 + 103473× х8 + 103112 × х9 +95338× х10 £ 450000

, i = 1, 2, 3, 4, 5, 6, 7,8, 9,10

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

После нажатия кнопки Найти решение появится следующий вид окна, рисунок 5.

Далее, зайти в параметры и установить флажки в позициях → нажать кнопку «ОК» → нажать кнопку «Выполнить».

Результаты решения задачи представлены в таблице 4.

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

1 УСЛОВИЕ. ОПРЕДЕЛИМ ОПТИМАЛЬНЫЙ НАБОР ПОРТФЕЛЯ ИНВЕСТИЦИЙ ПРИ УСЛОВИИ, ЧТО ПРОЕКТЫ НЕ ПОДДАЮТСЯ ДРОБЛЕНИЮ

Таблица 5. 

Данные о доле участия в каждом проекте

Таблица 6. 

Исходные данные

В этом случае к исходной модели добавляется ограничение целочисленности управляемых переменных. Для задания целочисленности указывается, что переменные бинарные (Excel 2010) или двоичные (Excel 2007).

а) NPV 196182 руб. → max

б) инвестиционные затраты 448550 руб. ≤ 450000 руб. Результаты решения задачи представлены в таблице 7.

Таблица 7.

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

2    УСЛОВИЕ. ОПРЕДЕЛИТЬ ОПТИМАЛЬНЫЙ НАБОР ПОРТФЕЛЯ ИНВЕСТИЦИЙ ПРИ УСЛОВИИ, ЧТО ПРОЕКТЫ B, D, F, G, H ЯВЛЯЮТСЯ ВЗАИМОИСКЛЮЧАЮЩИМИ. ПРОЕКТЫ НЕ ПОДДАЮТСЯ ДРОБЛЕНИЮ

Таблица 8. 

Данные о доле участия в каждом проекте

Исходные данные см. таблица 6.

К ограничениям в пункте 2) следует добавить новое условие. Проекты B, D, F, G, H являются взаимоисключающими, т.е. выполняться может только один из этих проектов, либо все эти проекты не выполняются. В математическую модель добавится ограничение:

а) NPV 113989 руб. → max

х2 + х4 + х6 + х7 + х8 £ 1

б) инвестиционные затраты        415110 руб. ≤ 450000 руб. в) х2 + х4 + х6 + х7  + х8

1 £ 1

Результаты решения задачи представлены в таблице 9.

Таблица 9.

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

3   УСЛОВИЕ. ОПРЕДЕЛИТЬ ОПТИМАЛЬНЫЙ НАБОР ПОРТФЕЛЯ ИНВЕСТИЦИЙ ПРИ УСЛОВИИ, ЧТО ПРОЕКТЫ C, E И F ЯВЛЯЮТСЯ ВЗАИМОЗАВИСИМЫМИ

Таблица 10. 

Данные о доле участия в каждом проекте

Исходные данные см. таблица 6.

К ограничениям в пункте 3) следует добавить новые условия. Проекты С, Е и F являются взаимозависимыми, т.е. они либо выполняются одновременно, либо все эти проекты не выполняются. В математическую модель добавятся ограничения:

а) NPV 96084 руб. → max 

х3 - х5 = 0 и х3 - х6 = 0

б) инвестиционные затраты        407650 руб. ≤ 450000 руб. в) х2 + х4 + х6 + х7  + х8

1 £ 1

х3 - х5 = 0

0 = 0

х3 - х6 = 0

0 = 0

Результаты решения задачи представлены в таблице 11.

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

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