Как сделать инвест портфель в экселе

Добавил пользователь Алексей Ф.
Обновлено: 05.10.2024

Модели формирования инвестиционного портфеля, такие как модель Г.Марковица и У.Шарпа (Capital Asset Price Model) хорошо работают в периоды стабильного роста национальной экономики.

Как правило, это замечание относится для зарубежных фондовых рынков, для которых характерна более монотонная динамика развития. Применение моделей Марковица и Шарпа для развивающихся рынков, в частности для фондового рынка Российской Федерации и рынка других стран СНГ, приводит к модельным ошибкам и непредсказуемым убыткам по портфелю. Это связано, прежде всего, с динамикой и особенностями развития этих рынков, для которых свойственно нестабильность и импульсивность доходности, сильное влияние инсайдерской (внутренней) информации, несовершенство нормативно-правовой базы, доминирующее влияние сырьевых отраслей на общую динамику развития.

  1. Доходность ценной бумаги рассчитывается как математическое ожидание доходностей. Это допущение есть и в модели Шарпа.
  2. Единичный портфель представляет собой портфель, состоящий из всех рассматриваемых ценных бумаг, взятых в одинаковой пропорции. В модели Шарпа за эталонный портфель(бенчмарк) берется так называемый рыночный портфель, динамику которого часто описывает фондовый индекс. Для российского фондового рынка это индекс РТС (RTSI), для украинского рынка индекс ПФТС, для американского фондового рынка это S&P500.
  3. Доходность ценной бумаги прямо пропорционально доходности единичного портфеля. То же предположение в модели Шарпа для рыночного портфеля.
  4. Риск ценной бумаги рассчитывается как чувствительность изменения доходности ценной бумаги от изменения доходности единичного портфеля. Аналогично для модели Шарпа.
  5. В отличии от модели Шарпа за безрисковую ставку берется средняя доходность единичного портфеля, а не государственные обязательства.

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

Excel


Следующим этапом рассчитаем доходности этих акций по следующей формуле:

Где:
Ri– текущая доходность акции;
Pi– текущая стоимость акции;
Pi-1 – стоимость акции в предыдущем периоде.

Формула в Excel будет выглядеть следующим образом:
=(A3-A2)/A2

И аналогично рассчитываем доходности всех акций. На рисунке ниже показан расчет доходности по акциям. Каждый столбец представляет месячные доходности каждой акции.
Следующим этапом, для каждой акции рассчитаем среднее значение доходности за весь год, то есть за все временные отрезки. Формула для расчета следующая:

Где:
Rit– доходность i-ой акции за период t;
T- рассматриваемое количество временных периодов (в нашем случае 12).
Формула расчета в Excel средней доходности (AFLT) следующая:
=СРЗНАЧ(F3:F13)

Аналогично рассчитываются остальные доходности акций.

Доходность акций российского фондового рынка

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

Где:
–доходность единичного портфеля;
– доходность i-ой ценной бумаги за период t.
Средняя доходность единичного портфеля за все периоды рассчитывается так:

Где:
– средняя доходность единичного портфеля;
Т – рассматриваемое количество временных периодов;
– доходность единичного портфеля.
В Excel расчет доходностей единичного портфеля (ЕП)будет выглядеть следующим образом:
=СРЗНАЧ(F3:J3) – для расчета доходности ЕП;
=СРЗНАЧ(K2:K13) – для расчет средней доходности ЕП.


Далее рассчитаем чувствительность изменения доходности акции от изменения доходности единичного портфеля. Чувствительность показывает коэффициент бета (β). И формула его вычисления следующая:

Для упрощения расчета посчитаем сначала знаменатель коэффициента бета, он для всех акций будет одинаков, а после числитель.
Столбец знаменателя (L) рассчитывается по формуле:
=СТЕПЕНЬ((K3-$K$15);2)

И в ячейке L14 происходит расчет непосредственно знаменателя по формуле:
=СУММ(L3:L13)
Для вычисления числителя по периодам коэффициента бета сначала воспользуемся формулой.
=(F3-$F$14)*(K3-$K$15) (Для акций Аэрофлота, колонка М)

Аналогично для других акций.
Суммируем полученные результаты за все периоды, то есть непосредственно рассчитываем числителя. Расчеты находятся в ячейках L14-Q14 по формулам:
=СУММ(L3:L13)

Коэффициент бета (β) будет рассчитан как отношение числителей к знаменателю.
=M14/$L$14


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

В Excel формула будет следующая:
Сначала рассчитываем остаточный риск на каждый период:
=СТЕПЕНЬ((F3-$F$14-$M$15*M3);2)

После рассчитываем остаточный риск за все периоды (R14-V14):
=СРЗНАЧ(R3:R13)


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

Риск единично портфеля равен (U15):
=КОРЕНЬ(L14/12)

И так, давайте обобщим все полученные данные в таблицу.

Таблица

Создадим новый рабочий лист в Excel и построим следующую таблицу. Используя поиск решений нам необходимо найти доли акций в новом инвестиционном портфеле. На рисунке, они помечены синей колонкой. Перед нами стоит прямая задача максимизации доходность инвестиционного портфеля с ограничением на риск. Максимальный риск установим на отметке 5%. Заполним дополнительные столбцы для расчета доходности и риска.

R*W= B2*G2 – произведение средней доходности и весов;
β*W=G2*C2 – произведение бета акции и веса;
(β*W)^2=I2*I2 – квадрат произведения;
σ^2*W^2=D2*D2*G2*G2 – произведение квадратов;
СУММА W =СУММ(G2:G6) –сумма весов портфеля.

Поиск решений в excel


Формула расчета целевой ячейки с доходностью портфеля (C9) будет следующая.
=СУММ(B2*G2;B3*G3;B4*G4;B5*G5;G6*B6)+F4*СУММ(C2*G2;C3*G3;C4*G4;C5*G5;C6*G6)

Формула расчета риска инвестиционного портфеля:
=КОРЕНЬ(J7*E4*E4+K7)

Поиск решений в excel


В итоге мы получаем расчет долей акций в нашем инвестиционном портфеле. В итоге мы получили следующее соотношений весов акций в портфеле. Доля акций Аэрофлота (AFLT) составляет 37.7%, доля акций Якутэнерго (YKEN) составляет 40.5%, доля акций Сбербанка (SBER) 1.3%, доля акций Лукойла (LKOH) 0% и доля акций ГМКНорНикель (GMKN) 20.5%.

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

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

Для привлечения и вложения средств в какое-либо дело инвестору необходимо тщательно изучить внешний и внутренний рынок.

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

Финансовая модель инвестиционного проекта в Excel

Составляется на прогнозируемый период окупаемости.

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

Чтобы проект вызывал доверие, все данные должны быть подтверждены. Если у предприятия несколько статей доходов, то прогноз составляется отдельно по каждой.

Финансовая модель – это план снижения рисков при инвестировании. Детализация и реалистичность – обязательные условия. При составлении проекта в программе Microsoft Excel соблюдают правила:

Расчет экономической эффективности инвестиционного проекта в Excel

Для оценки эффективности инвестиций применяются две группы методов:

  • статистические (PP, ARR);
  • динамические (NPV, IRR, PI, DPP).

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

Экономическая формула расчета срока окупаемости:

Формула.

где IC – первоначальные вложения инвестора (все издержки),

CF – денежный поток, или чистая прибыль (за определенный период).

Расчет окупаемости инвестиционного проекта в Excel:

Поступления.

  1. Составим таблицу с исходными данными. Стоимость первоначальных инвестиций – 160000 рублей. Ежемесячно поступает 56000 рублей. Для расчета денежного потока нарастающим итогом была использована формула: =C4+$C$2.
  2. Рассчитаем срок окупаемости инвестированных средств. Использовали формулу: =B4/C2 (сумма первоначальных инвестиций / сумма ежемесячных поступлений).

Так как у нас дискретный период, то срок окупаемости составит 3 месяца.

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

Рентабельность инвестиций

ARR, ROI – коэффициенты рентабельности, показывающие прибыльность проекта без учета дисконтирования.

Формула2.

где CFср. – средний показатель чистой прибыли за определенный период;

IC – первоначальные вложения инвестора.

Пример расчета в Excel:

Вложения.

  1. Изменим входные данные. Первоначальные вложения в размере 160 000 рублей вносятся только один раз, на старте проекта. Ежемесячные платежи – разные суммы.
  2. Рассчитаем средние поступления по месяцам и найдем рентабельность проекта. Используем формулу: =СРЗНАЧ(C23:C32)/B23. Формат ячейки с результатом процентный.

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

Примеры инвестиционне6ого проекта с расчетами в Excel:

Статистические методы не учитывают дисконтирование. Зато позволяют быстро и просто найти необходимые показатели.

Следить за изменением стоимости акций и соблюдать пропорции.Я решил завести таблицу для отслеживания результата инвестиций на ИИС в мае 2020 года.На ИИС я придерживаюсь четкого плана в противовес случайному характеру покупок на брокерском счете. Никогда особо не доверял различным сервисам по анализу инвестиций. Могу сравнить их со своей таблицей при надобности, но никогда не отдам анализ в чужие руки. Мне важно досконально понимать, как считается каждое число, и своя таблица в этом плане опережает все подобные сервисы.Сам я работаю в сфере автоматизации, и потому таблица, по моему мнению, также должна быть полностью автоматизирована. Принципиально не хочу ничего вводить руками: знаю, что когда-нибудь забуду об этом или мне просто надоест. Основная часть информации подгружается благодаря OpenAPI Тинькофф Инвестиций. Почти все сделано с помощью скриптов. Формул — минимальное количество. Никаких брокерских отчетов загружать не надо.Главная страница таблицы состоит из нескольких блоков:Состав портфеля. Показывает, сколько у меня сейчас акций, текущую и средние цены, прибыль/убыток, текущие пропорции и пропорции после покупки.Графики трендов и изменения цен за выбранный период.Общая информация по портфелю. Смотрю здесь, обгоняю ли я вообще инфляцию.График отдельно выбранного актива с минимальным техническим анализом.

Как сделать таблицу в Excel для анализа портфеля ИИС.

Как сделать таблицу в Excel для анализа портфеля ИИС.

Особенности таблицы

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

Для начала я составил план распределения активов в портфеле. У вас должна быть тактика, которой вы будете придерживаться, иначе это все бессмысленно. Для себя решил, что мой портфель будет состоять только из ETF:

  • США (FXUS и FXIT) — 30%;
  • развитые страны (Германия FXDE + FXDM) — 30%;
  • развивающиеся страны (Россия TMOS, Китай FXCN) — 30%;
  • евробонды FXRU — 5%;
  • золото FXGD — 5.

Целевые пропорции указаны в столбце Target allocation. Столбец Current allocation показывает текущие пропорции в портфеле. Класс активов не имеет значения.

Моей задачей было написать алгоритм, следуя которому я мог бы покупать акции, максимально приближаясь к заветным пропорциям. Для этого я написал отдельный скрипт — WhatToBuy.js, который считывает текущие цены активов, их пропорции и доступный бюджет; а также ищет наибольшее отклонение от плановой доли актива и присуждает каждому активу приоритет для покупки.

Алгоритм довольно прост, но потребуются небольшие знания JavaScript. Результатом его работы станет точное указание, в каких количествах покупать тот или иной актив, сколько комиссии уйдет брокеру (Fee, строка 10) и сколько бюджета останется (Cash, строка 10).

Как сделать такую же таблицу

Я создавал таблицу на базе готового исходного кода. Низкий поклон этому человеку. Просто скопируйте код с этого сервиса и посмотрите, что и как работает. Если у вас есть навыки программирования, то вам не составит труда быстро адаптировать его под свои задачи.

Если навыков нет, то вкратце этот процесс добавления скрипта выглядит так:

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

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

Я также сделал шаблон своей таблицы, чтобы вы могли настроить его под себя. Чтобы воспользоваться им, сначала нужно получить доступ к своему портфелю при помощи OpenApi Тинькофф Инвестиций. Это самый сложный этап, так как подразумевает совершение нескольких нетривиальных операций.

В самой таблице порядок такой:

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

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

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

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

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

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

FXAU FXCN FXDE FXJP FXMM FXRB FXRU FXUK FXUS iFXIT FXGD

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, что не дает гарантированного результата в будущем.

Читайте также: