Константин Шляхтин Независимый Финансовый Советник
Вы можете обратиться ко мне за финансовой консультацией. Оставьте свои контакты, и тему консультации. Я с вами свяжусь.
9 комментариев

Как составить инвестиционный портфель? (для разбирающихся в Excel).

Способов формирования инвестиционного портфеля много, но есть принципы которые используются во многих подходах. Это:

  • волатильность инструмента;
  • средняя доходность за определенный период;
  • корреляция между инструментами в портфеле;
  • отклонение от средней цены;
  • и много другое…

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

Предлагаю на моем примере составить инвестиционный портфель отвечающий следующим критериям:

  1. Срок инвестирования 3 года;
  2. Портфель должен состоять из фондов ETF (в России на московской бирже таких 12 штук, о том что это такое, читайте здесь);
  3. Возраст ETF не менее 2 лет;

1 ШАГ. На сайте investfunds.ru делаем отборку ETF отвечающим нашим критериям.

FXAU FXCN FXDE FXJP FXMM FXRB FXRU FXUK FXUS iFXIT FXGD

2 ШАГ. С помощью сервиса http://mfd.ru загрузим историю каждого инструмента:

2017-02-03_11-14-06

(в таблице загружены данные с 19.05.2014, отображено за последний год. Справа выведены изменения в процентном соотношении)

3 ШАГ. В сводной таблице вычислим значения каждого инструмента в отдельности:

  • среднюю доходность;
  • 2 максимальных значения за всю историю (период неделя);
  • доверительное значение (подробнее) за промежуток 156 недель (3 года);
  • стандартное отклонение от средней цены;
  • минимальная сумма инвестирования (это последняя стоимость инструмента);
  • минимальное значение за последний год;
  • стандартное отклонение за последний год;
  • и стандартную просадку.

2017-02-03_11-20-30

(результат 3 шага)

4 ШАГ. Вычисляем корреляцию между инструментами с помощью надстройки в Excel “Анализ Данных” (подробнее)

2017-02-03_11-43-26

5 ШАГ. Вычисления с помощью надстройки в Excel “Поиск Решений” (подробнее)

В “Поиске Решений” введем несколько обязательных условий для вычислений:

  1. Сумма инвестиций для каждого инструмента не должна превышать 25%.
  2. Обязательно в портфель включим фонд с золотом FXGD=5%.

Получим следующее:

2017-02-03_12-12-35

 

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

2017-02-03_11-57-00

(Мы получили готовый портфель, с прогнозом на три года. Максимальный доходность 18% годовых, средняя 13% годовых, минимальная 8% годовых)

2017-02-03_11-59-25

(график со значениями Средний и Минимальный доход за год)

 В результате этих вычислений мы получили диверсифицированной портфель состоящий из 6 консервативных инструментов, который с высокой вероятностью на отрезке 3 года даст почти 14% годовых.

*Все данные вычислены исходя из истории котировок ETF, что не дает гарантированного результата в будущем.

9 комментариев

  1. Спасибо! Очень интересное применение Excel. А ведь таким образом можно же составить прогноз по состоянию на год назад и сравнить прогноз с тем что получилось, да? 😉

  2. Константин, не тот столбец данных взяли из выгрузки FXGD. Вместо CLOSE взяли HIGH. По остальным инструментам ок.

  3. Константин, есть несколько вопросов. 1. Как рассчитывали “СрЗНач доверит” и для каких целей? 2. Что такое “СКО корр”? Это СКО за последний год? 3. Как считали “станд просадка”?

  4. Спасибо. Идея интересная, понять бы как вы еще это все высчитали – было бы вообще супер, а так полезность данной статьи не очень, поскольку для себя этот анализ никак не повторить

  5. @@saldafon:disqus @@kostas_1981:disqus Подскажите, что за значения в ячейках столбца M, значения M3, N3 и O3? И как именно получились максимальные, средние и минимальные доходности? И “СКО корр” — СКО за последний год?

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *