Как составить таблицу анализ инвестиций

Привет, меня зовут Михаил, и у меня нет кредитов, ипотеки и работы. Инвестировать я начал, когда еще был студентом.

Моя основная финансовая боль всегда была связана с эффективным учетом всех активов — то есть всего, что у меня есть. Я инвестирую через различных брокеров, не только в РФ, но и за ее пределами, а еще вкладываю в недвижимость, депозиты, монеты и страхование юнит-линкед.

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

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

Как работает таблица

Изначально мой отчет был табличкой в экселе с использованием упрощенного языка программирования VBA, но сейчас я перенес его в гугл-таблицу без использования скриптов.

Чтобы таблица была не просто очередным шаблоном, я дал ей собственное имя — SilverFir: Investment Report. Название говорит о том, что это инвестиционный отчет, а silver fir отсылает к разновидности вечнозелёных деревьев.

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

Форматы данных. В настройках таблицы указаны региональные настройки Соединенных Штатов. Это означает, что разделитель целой и дробной части числа — точка, то есть 105.1 — правильная запись, а 105,1 выдаст ошибку. Это сделано, чтобы не загромождать формулы автоматической заменой точки на запятую. Все американские и многие российские сайты выдают цены именно с точкой в качестве разделителя.

Даты указаны в формате «год-месяц-день», то есть «2020-03-11» — 11 марта 2020 года.

Основные параметры, используемые в таблице. Чтобы заполнить таблицу и корректно ею пользоваться, необходимо знать следующие параметры:

1. Идентификатор — обычно тикер или ISIN, международный идентификационный код ценной бумаги.
2. Валюта — в соответствии с кодами валют: USD, RUB, EUR, GBP.
3. Дата покупки — нужна, чтобы считать доходность и определять стоимость в рублях на момент покупки, если актив в валюте, отличной от рубля.
4. Количество — может измеряться и в штуках, и в квадратных метрах.
5. Цена покупки — заносится вместе с комиссией, ведь иной раз за сделку приходится платить ощутимые комиссионные.

Пошаговое руководство по заполнению

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

Представим, что у вас есть несколько типов активов: два вклада в разных валютах, ИИС, обычный брокерский счет, арендная квартира в Москве и монета «Георгий Победоносец». Разберемся, как получить полную картину по сбережениям.

Начнем с вкладов. Готовые примеры занесены в строки 7 и 8 таблицы.

1. «Имя» — произвольное.
2. «Базовая цена» — 50 000 Р.
3. «Ожидаемая дох-ть, %» — 5,8.
4. «Дата поступления» — 2021-03-22. Это дата, когда вы сможете обналичить вклад.
5. «Размер поступления» — в ячейке уже стоит формула, суммирующая первоначальную сумму вклада и ожидаемый доход.
6. «Валюта» — RUB.
7. «Посредник» — в этой ячейке я указываю название брокера, банка или просто тип активов, например «вклады». Это нужно для того, чтобы было удобно просматривать данные в сводных таблицах.
8. «Дата покупки» — 2020-03-22.

Если ваш вклад не в рублях, то таблица автоматически рассчитает начальные затраты в рублях в столбце «Цена покупки, Р» по курсу на дату открытия вклада.

Разнесем данные по столбцам таблицы, которые надо заполнить вручную:

1. «Имя» — ОФЗ-ПД 26225 10/05/34.
2. «Идентификатор» — SU26225RMFS1.
3. «Кол-во» — 1000 штук. Не 100, потому что цена облигации на Мосбирже указывается в процентах. Для того чтобы корректно вести расчеты в рублях, эти проценты можно умножить на 10: номинал облигации — 1000 Р.
4. «Базовая цена»: 1000 Р (номинал облигации) × 89% (цена покупки) × 100 шт. = 89 000 Р.
5. «Валюта» — RUB.
6. «Посредник» — в этом случае ИИС.
7. «Дата покупки» — 2018-09-03.

Брокерский счет. Допустим, на брокерском счете — бумаги двух эмитентов:

1. 70 рублевых облигаций группы компаний «Пионер» серии БО-02, код ценной бумаги — RU000A0JWK66, куплены 28 сентября 2018 года по цене 65% от номинала, то есть за 45 500 Р.
2. 10 акций биржевого инвестиционного фонда FXMM, куплены 20 апреля 2018 года по цене 1426,8 Р за штуку.

Разнесем данные по столбцам таблицы. Для облигаций ГК «Пионер»:

1. «Имя» — группа компаний «Пионер» БО-02.
2. «Идентификатор» — RU000A0JWK66.
3. «Кол-во» — 700 штук.
4. «Базовая цена» — 1000 Р × 65% × 70 шт. = 45 500 Р.
5. «Валюта» — RUB.
6. «Посредник» — рос. брокер.
7. «Дата покупки» — 2018-09-28.

Если в дальнейшем я буду докупать те же бумаги, нужно просто обновить в этой строке количество бумаг и базовую цену. Остальные значения остаются неизменными. Таким образом, «Дата покупки» — это, строго говоря, дата первой покупки актива.

Квартира в Москве. Основная идея табличного отчета в том, что можно брать цены на совершенно любые классы финансовых активов — лишь бы они публиковались в интернете в свободном доступе. Так и с недвижимостью: есть множество сайтов, которые публикуют статистику цен. Представим, что у вас есть доля в мини-квартире в Москве, в районе Арбата. Квартира сдается в аренду. Правда, вам принадлежит всего 1 м². Купили вы эту долю 13 декабря 2017 года за 460 000 Р. Каждый месяц 25 числа вы получаете арендный доход — 3742 Р.

Монета «Георгий Победоносец». Такая же ситуация и с инвестиционными монетами. Например, 11 лет назад, 13 июля 2009 года, вы купили 50 серебряных монет «Георгий Победоносец» номиналом 3 Р. Заплатили 600 Р за каждую.

Что делать после заполнения данных

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

Теперь можно узнать следующие показатели по каждому из активов:

1. Прибыль или убыток у вас по позиции на текущий момент в рублях — независимо от валюты вложения: столбец G.
2. Сколько процентов годовых приносит эта инвестиция, если срок больше года, или просто процент изменения, если срок меньше года: столбец H.
3. Ожидаемая доходность и средний ежемесячный доход в валюте вложения: столбцы J и K.
4. Даты грядущих поступлений — или прошлых, если информация по будущим выплатам еще недоступна: столбцы L и M. Если вы покупаете дивидендные акции, ячейки посчитают размер и дату ближайшего дивиденда.
5. Процентное изменение бумаги за последние несколько лет для акций: столбец I. За сколько именно — зависит от сайта, откуда берутся данные.
6. Текущий вес бумаги в портфеле: столбец V.

Анализ сводных показателей портфеля

Перейдем теперь к сводным показателям всего портфеля. Их можно смотреть на разных вкладках.

«Данные» — это главная вкладка, куда вносятся все исходные. Светло-голубым выделены ячейки, которые надо заполнить вручную. Также на этой вкладке рассчитывается прибыль и убыток по позиции, дата и размер ближайшего поступления от актива.

«Валюты» — полностью автоматическая вкладка, которая содержит отчет по используемым валютам. Как только вы редактируете что-либо на вкладке «Данные», этот мини-отчет сразу меняется.

«Посредники» — отчетная вкладка, которая показывает распределение сумм по брокерам и весовое значение процента капитала. Еще она показывает количество бумаг у каждого брокера и расчетный ежемесячный доход, также этот доход отображается в процентах годовых.

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

«Классы активов» — здесь вы увидите отчет о диверсификации вашего портфеля. Я формализовал описания классов активов из Quicken и описаний нескольких авторов, в том числе Сергея Спирина, Александра Силаева, Павла Комаровского.

«Покупки» — это мини-отчет об истории покупок по времени. Здесь вы сможете узнать, в каком месяце сколько денег потратили.

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

«Капитал график» — визуализирует данные с вкладки «Капитал».

«Идентификаторы» — в графическом виде отображает распределение по бумагам в таблице.

«Отчет» — сводный отчет о планируемых поступлениях на три месяца вперед в рублях, то есть сумма купонов, арендных платежей. Также вкладка дает информацию о ближайших выплатах на 30 дней вперед и назад, а еще — о лидерах роста и падения вместе с историей капитала.

Любая инвестиция нуждается в тщательных расчетах. Иначе инвестор рискует потерять вложенные средства.

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

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

Инвестор вкладывает деньги в готовое предприятие. Тогда ему необходимо оценить эффективность работы (доходность, надежность). Либо в новое дело – все расчеты проводятся на основе данных, полученных в ходе изучения рынка (инфраструктуры, доходов населения, уровня инфляции и т.д.).

Рассмотрим создание бизнеса с нуля. Рассчитаем прибыльность предприятия с помощью формул Excel. Для примера будем брать условные товары и цифры. Важно понять принцип, а подставить можно любые данные.

Итак, у нас есть идея открыть небольшой магазин. Определимся с затратами. Они бывают

• постоянными (нельзя рассчитать на единицу товара);
• переменными (можно рассчитать на единицу товара).

Первоначальные вложения – 300 000 рублей. Деньги расходуются на оформление предпринимательства, оборудование помещения, закупку первой партии товара и т.д.

Составляем таблицу с постоянными затратами:

* Статьи расходов индивидуальны. Но принцип составления — понятен.

По такому же принципу составляем отдельно таблицу с переменными затратами:

Для нахождения цены продажи использовали формулу: =B4*(1+C4/100).

Следующий этап – прогнозируем объем продаж, выручку и прибыль. Это самый ответственный этап при составлении инвестиционного проекта.

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

Для подсчета выручки использовалась формула: =СУММПРОИЗВ(B3:B6;Лист2!$D$4:$D$7). Где первый массив – объемы продаж; второй массив – цены реализации.

Выручка минус переменные затраты: =B7-СУММПРОИЗВ(B3:B6;Лист2!$B$4:$B$7).

Прибыль до уплаты налогов: =B8-Лист1!$B$14 (выручка без переменных и постоянных затрат).

Налоги ЕНВД: =Лист1!A10*1800*0,15*3 (1800 – базовая доходность по виду деятельности, 3 – количество месяцев, С12 – площадь помещения).

Чистая прибыль: прибыль – налоги.



Оценка инвестиционного проекта в Excel

Рассчитывают 4 основных показателя:

• чистый приведенный эффект (ЧПЭ, NPV);
• индекс рентабельности инвестиций (ИРИ, PI);
• внутреннюю норму доходности (ВНД, IRR);
• дисконтированный срок окупаемости (ДСО, DPP).

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

Сначала дисконтируем каждый положительный элемент денежного потока.

Создадим новый столбец. Введем формулу вида: = положительный элемент денежного потока / (1 + ставка дисконтирования)^ степень, равная периоду.

Теперь рассчитаем чистый приведенный эффект:

1. С помощью функции СУММ.



2. С помощью встроенной функции ЧПС.
   
   
   Чтобы получить чистый приведенный эффект, складываем результат функции с суммой инвестиций.
   
   
   Цифры совпали:
   

Найдем индекс рентабельности инвестиций. Для этого нужно разделить чистую приведенную стоимость (ЧПС) на объем инвестированных средств (со знаком «+»):

Результат – 1,90.

Посчитаем IRR инвестиционного проекта в Excel. Напомним формулу:

ВНД = ΣДП_t/ (1 + ВНР)^t = И.

ДП_t– положительные элементы денежного потока, которые нужно продисконтировать по такой ставке, чтобы чистый приведенный эффект равнялся нулю. Внутренняя норма доходности – такая ставка дисконтирования, при которой выпадает равенство вида:

ΣДП_t / (1 + ВНР)^t – И = 0,

NPV = 0.

Воспользуемся инструментом «Анализ «Что-Если»»:

1. Ставим курсор в ячейку со значением чистого приведенного эффекта. Выбираем «Данные»-«Анализ Что-Если»-«Подбор параметра».



2. В открывшемся окне в строке «Значение» вводим 0 (чистый приведенный эффект должен равняться 0). В поле «Изменяя значение ячейки» ссылаемся на ставку дисконтирования. Нужно изменить ее так, чтобы соблюдалось приведенное выше равенство.



3. Нажимаем ОК.

Ставка дисконтирования равняется 0,41. Следовательно, внутренняя норма доходности составила 41%.

Моделирование рисков инвестиционных проектов в Excel

Используем метод имитационного моделирования Монте-Карло. Задача – воспроизвести развитие бизнеса на основе результатов анализа известных элементов и взаимосвязей между ними.

Продемонстрируем моделирование рисков на простейшем примере. Составим условный шаблон с данными:

Ячейки, которые содержат формулы ниже подписаны своими значениями соответственно.

Прогнозируемые показатели – цена услуги и количество пользователей. Под этими данными делаем запись «Результаты имитации». На вкладке «Данные» нажимаем «Анализ данных» (если там нет инструмента придется подключить настройку). В открывшемся окне выбираем «Генерация случайных чисел».

Заполняем параметры следующим образом:

Нам нужно смоделировать ситуацию на основе распределений разного типа.

Для генерации количества пользователей воспользуемся функцией СЛУЧМЕЖДУ. Нижняя граница (при самом плохом варианте событий) – 1 пользователь. Верхняя граница (при самом хорошем варианте развития бизнеса) – 50 покупателей услуги.

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

Чтобы оценить риски, нужно сделать экономико-статистический анализ. Снова воспользуемся инструментом «Анализ данных». Выбираем «Описательная статистика».

Программа выдает результат (по столбцу «Коэффициент эффективности»):

Скачать анализ инвестиционного проекта в Excel

Можно делать выводы и принимать окончательное решение.

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

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

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

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

Совет: Шаблоны можно копировать прямо в свой аккаунт GDrive или сохранять в Excel через пункт меню Файл -> Сохранить

Таблица №1: Учет личных финансов

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

Таблица №1: Учет личных финансов — шаблон таблицы: здесь

Из каких основных элементов состоит таблица:

1. Статьи доходов — крупным шрифтом и заливкой выделена строка которая суммирует остальные статьи доходов
2. Курс рубля — если у вас доходы/расходы в разной валюте, вы можете приводить их к одной деля/умножая на служебную строку с курсом
3. Статьи расходов — крупным шрифтом и заливкой выделена строка которая суммирует остальные статьи доходов
4. Дни — столбцы с днями в которые заносятся доходы/расходы за день, в этих столбцах значения суммируются по вертикали
5. Итого — сумма которая суммирует все значения по статьям доходов и расходов
6. Итого в рублях — сумма в евро * значения курса в сервисной строке (строка
7. Листы с месяцами и лист с итогами года — в листах с итогами года немного другая структура, там вместо суммы в днях месяца — сумма значений месяцев годы

Как работать с таблицей:

В начале нужно составить структуру таблицы, определив постоянные статьи расходы и доходов. Потом начать регулярно заполнять таблицу (желательно каждый день). Я например, вношу доходы расходы в заметку с текущим днем в Evernote (в которой кроме этого хранятся планы на день и мысли / идеи дня), а потом в конце дня переношу таблицу с личными финансами. Более подробно как работать с таблицей мы записали в этом видео

В каком курсе подробнее рассматриваем: Мастер продуктивности

Таблица №2: Подборка новостей компаний-эмитентов

Цель: собрать в одном месте самые важные новости за неделю / месяц и используя фильтр по меткам провести фундаментальный анализ по эмитентам.

Таблица: Важные новости — шаблон таблицы: здесь

Из каких основных элементов состоит таблица:

1. Инициалы сотрудника который добавил новость
2. Дата добавления новости
3. Заголовок новости
4. Тикер эмитента или краткая аббревиатура названия страны
5. Ссылка на новость
6. Важность новости (можно оценивать

Как работать с таблицей:

Один раз в день заносятся важные новости, в конце дня / недели расставляются приоритеты и делается сортировка по эмитентам (столбцы D, E, F) после чего влияние новости сравнивается с тенденциями по тех. анализу.

В каком курсе подробнее рассматриваем: Школа Франклина Трейдер — Америка

Таблица №3: Расчет размера позиции

Цель: рассчитать сколько акций можно купить при заданном уровне риска + также видно какой максимальный убыток по портфелю вы можете получить если сработают все ваши Stop Loss. 

Таблица №3: Расчет размера позиции — шаблон таблицы: здесь

Из каких основных элементов состоит таблица:

1. Размер капитала: Указываем размер капитала
2. Максимальный риск за неделю / месяц: Указываем пороговое значение капитала за неделю после которого мы закроем позиции (например за неделю 5%) а снижение с начала месяца 10% => это нужно чтобы не заигрываться и вовремя остановиться если рынок поменялся и вы перестали «в него попадать» (если торговать разные рынки и инструменты и не брать слишком большой риск в каждой позиции то к этому уровню подходить не будете)
3. Стандартные данные — дата сделки, тикер эмитента, причина входа (тезисно)
4. Стоп-цена — уровень цены при котором вы закроете позицию (Stop Loss) — по мере движения цены в вашу сторону вы его двигаете за рынком (НО НЕ ПРОТИВ ВАС!)
5. Количество бумаг — изменяете вручную, исходя из максимального риска на позицию (для нас это от 0,2% до 5%)
6. Сумма риска на портфель — значения максимального риска по каждой позиции суммируется и видно какой частью от вашего портфеля вы рискуете, если все пойдет не так как вы хотели.
7. P/L — соотношение риска к потенциальной прибыли (уровня Take Profit к уровню Stop Loss)
8. Курс доллара — если в одном портфеле бумаги и в рублях и долларах и в евро — этот столбец служебный для приведения всех позиций к единой валюте)

Как работать с таблицей:

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

В каком курсе подробнее рассматриваем: Школа Франклина Трейдер — Америка , Школа Франклина Трейдер — Россия

Таблица №4: Динамика инвестиционного портфеля

Цель: видеть, как изменяется стоимость вашего инвестиционного портфеля в динамике, учитывая до внесение средств

Таблица №4: Динамика инвестиционного портфеля — шаблон таблицы: здесь

Из каких основных элементов состоит таблица:

1. Стоимость портфеля — значение стоимости вашего портфеля за вычетом всех обязательств (обычно берем из брокерского отчета или биржевого терминала)
2. Завод-вывод средств за неделю — сумма завод-вывод денег с начала недели
3. Завод-вывод c начала месяца — сумма завод-вывод денег с начала месяца — идет накопительным итогам (суммируются недели с начала месяца)
4. Завод-вывод c начала года — сумма завод-вывод денег с начала года — идет накопительным итогам (суммируются месяцы с начала года)
5. Прирост портфеля за неделю — считается по формуле =(значение стоимости на этой неделе — завод с начала недели) / значение стоимости на прошлой недели => в %
6. Прирост портфеля с начала месяца — считается по формуле =(значение стоимости на последний день месяца — завод с начала месяца) / значение стоимости на последний день прошлого месяца => в %
7. Прирост портфеля с начала года — считается по формуле =(значение стоимости на последний день недели — завод с начала года) / значение стоимости на последний день прошлого года => в %
8. Значения месяцев — под каждый последний день месяца вставляется новый столбец с данными и выделяем его большим шрифтом — чтобы было понятно что это итог месяца
9. Значения неделю — под каждую неделю вставляется новый столбец с данными

Как работать с таблицей:

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

В каком курсе подробнее рассматриваем: Школа Франклина Трейдер — Инвестор, Школа Франклина Трейдер — Америка , Школа Франклина Трейдер — Россия

Таблица №5: Вероятность исполнения ваших прогнозов по рынку

Цель: видеть и точно оценивать насколько точно вы можете прогнозировать движение того или иного актива (фондовый индекс, валюта, акция которой вы регулярно торгуете). Например, если вы видите что вероятность ваших прогнозов стала ниже 50% — значит по этому инструменту рынок поменялся и ваши прогнозы не срабатывают.

Таблица №5: Вероятность исполнения ваших прогнозов по рынку — шаблон таблицы: здесь

Из каких основных элементов состоит таблица:

1. Ссылка с на страницу с данными — это может быть сайт где есть цены закрытия дней торгуемого актива, для примера мы используем для анализа графиков платформу Tradingview
2. Столбец с неделей — под каждую неделю вправо вставляется новый столбец, также под последний день месяца вставляется новый столбец, чтобы подвести итоги месяца
3. Значение актива — цена закрытия последнего дня недели / месяца
4. Изменение за неделю в % — считается по формуле цена закрытия последнего дня текущей недели / цена закрытия последнего дня прошлой недели
5. Прирост портфеля за неделю — на сколько бы изменился в деньгами виртуальный портфель за неделю (сумма портфеля указана в ячейке А2 — по умолчанию это 100 000$)
6. Прирост портфеля c начала месяца — на сколько бы изменился в деньгами виртуальный портфель с начала месяца
7. Прирост портфеля c начала года — на сколько бы изменился в деньгами виртуальный портфель с начала года
8. Исполнен прогноз за неделю или нет — если да — указываем YES, если нет NO, если была неопределенная ситуация и прогноз вы не ставили IM (от англ. Indefinite Movement)
9. Оценка вероятности прогноза в % — считается по формуле сумма значение YES / (YES + NO). Важно следить за вероятность в динамике, чтобы она росла — это значит вы на верном пути 😉

Как работать с таблицей:

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

Пример сводной таблицы:

В каком курсе подробнее рассматриваем:  Школа Франклина Трейдер — Америка , Школа Франклина Трейдер — Россия

На этом все — надеемся эти таблицы вам пригодятся!

Если вы полный ноль в этом — смотрите видео-уроки на Youtube о том как работать с таблицами Google Spreadsheets, а также регистрируйтесь в курс Школа Франклина Трейдер в котором мы подробно рассматриваем все эти вопросы.

Пока идет распродажа этот курс можно получить со скидкой 70% — вот ссылка.

С уважением, Александр Цыглин
основатель TFC3.NET

P.S. Запишитесь на бесплатный ежемесячный интернет-семинар по биржевой торговле

P.P.S. Подпишитесь на наш канал на YouTube и получайте новые видео-уроки первым!

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

Инфографика: Оценка эффективности инвестиций, инвестиционного портфеля, акций

Показатели эффективности инвестиций на фондовом рынке

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

★ Инвестиционная оценка в Excel. Расчет NPV, IRR, DPP, PI за 5 минут

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

Доходность инвестиций. Оценка и анализ акций

Первый один из самых важных показателей инвестиции (акции, облигации, фьючерса и т.д.) является ее доходность. Она отражает привлекательность финансового инструмента для инвесторов. Для примера мы будем оценивать доходность акции. Так чем выше привлекательность акции, тем выше ее доходность и стоимость на фондовом рынке. Для того чтобы оценить доходность акций воспользуемся сервисом сайта finam.ru, который позволяет получить текущие котировки акций в режиме реального времени.

Пример оценки доходности акций ОАО «Газпром» в Excel

Рассмотрим оценку доходности акции ОАО «Газпром» (GAZP) в Excel. Были загружены недельные котировки за 31.01.2014 – 31.01.2015 г. Далее необходимо вставить котировки акций в таблицу, как представлено на рисунке ниже.

Для расчета доходности акции можно воспользоваться следующей формулой:

Доходность ОАО «Газпром» = (B6-B5)/B5

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

Доходность ОАО «Газпром»  =LN(B6/B5)

Прогнозирование доходности акции

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

На рисунке ниже показан результат расчета будущей доходности ОАО «Газпром» по данной модели. Формула оценки доходности будет следующая:

Доходность акции ОАО «Газпром»  =СРЗНАЧ(C6:C56)

Оценка риска акции в Excel

Под риском акции подразумевается его волатильность или изменчивость (данную трактовку ввел Г.Марковиц). То есть чем больше чувствительность изменения котировок, тем выше риск акции. Для расчета риска необходимо рассчитать стандартное отклонение доходностей акции от среднего. На рисунке ниже представлена формула расчета стандартного отклонения доходностей акции ОАО «Газпром».

Оценка риска акции ОАО «Газпром» =СТАНДОТКЛОН(C6:C56)

Оценка эффективности инвестиций. Коэффициент Шарпа

Коэффициент Шарпа (англ. Sharp ratio) – самый распространенный коэффициент оценки эффективности инвестиций на фондовом рынке, был введен экономистом У. Шарпом в 1966г. Данный коэффициент используют для анализа акций, фьючерсов, инвестиционных портфелей, стратегий. Коэффициент Шарпа показывает отношение доходности к риску инвестиции.

Формула расчета коэффициента Шарпа

Формула расчета коэффициента Шарпа следующая:

где:

r_p – средняя доходность инвестиционного портфеля;

r_f – средняя доходность безрискового актива;

σ_p – стандартное отклонение доходностей инвестиционного портфеля (риск портфеля).

Как видно, коэффициент Шарпа показывает отношение избыточной доходности инвестиционного портфеля к риску. За безрисковую доходность по активу, на практике, берут:

• Доходность по банковскому вкладу наиболее надежных банков РФ;
• Доходность государственных ценных бумаг (ГКО, ОФЗ);
• Размер ключевой ставки ЦБ РФ;

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

Оценка эффективности инвестиций по коэффициенту Шарпа

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

Пример расчета коэффициента Шарпа для оценки эффективности инвестиционного портфеля в Excel

Рассчитаем показатель Шарпа для инвестиционного портфеля. Для этого необходимо знать доходность по инвестиционному портфеля за определенный интервал времени. В данном примере рассматривался расчет для инвестиционного портфеля за период с 10.02.2014 по 17.03.2014 года по неделям.  Формулы расчета следующие:

Средняя доходность инвестиционного портфеля =СРЗНАЧ(B5:B10)

Риск инвестиционного портфеля  =СТАНДОТКЛОН(B5:B10)

Доходность безрискового актива = 15%/50.  Данное значение соответствует средней доходности по банковскому вкладу (15%), поделенное на количество недель в году.

Коэффициент Шарпа =(C5-E5)/D5

На рисунке ниже представлены результаты расчета.

Коэффициент Шарпа составил 0,12 – это говорит о том, что данный портфель управляется довольно рискованно, то есть уровень риска выше, чем уровень доходности.

Коэффициент бета для оценки эффективности инвестиций

Коэффициент бета (англ. beta coefficient) ­– показывает чувствительность изменения доходности инвестиционного портфеля от доходности рынка (рыночного индекса). Данный коэффициент используется как самостоятельно, так и в модели оценки капитальных активов CAPM (Capital Assets Price Model).  Коэффициент бета отражает систематический риск инвестиции.

Формула расчета коэффициента бета

где:

β – коэффициент бета;

r_p – доходность инвестиционного портфеля;

r_m – рыночная доходность;

σ²_m – дисперсия рыночной доходности.

Оценка эффективности инвестиций по коэффициенту бета

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

Оценка риска акций. Пример расчета коэффициента Бета в Excel

Для расчета коэффициента бета необходимо рассчитать ковариацию между доходностями инвестиционного портфеля и доходностями рыночного индекса (индекса РТС) и соотнести с дисперсией доходности рынка. Формула в Excel расчета следующая:

Коэффициент Бета =КОВАР(B5:B10;C5:C10)/ДИСП(C5:C10)

Коэффициент бета равен 0,22 – что показывает умеренный уровень рыночного риска для данного инвестиционного портфеля.

Коэффициент Трейнора для оценки эффективности инвестиций

Коэффициент Трейнора (англ. Treynor ratio) – показатель эффективности инвестиции (инвестиционного портфеля), который показывает отношение превышение доходности инвестиции над систематическим риском  портфеля. В качестве систематического риска используют значение рыночного риска, который рассчитывается как коэффициент бета. Коэффициент бета отражает чувствительность изменения доходности инвестиций и доходности рынка (рыночного индекса). Показатель Трейнора используется многими инвестиционными компаниями для осуществления ранжирования качества управления финансовыми продуктами.

Коэффициент Трейнора. Формула расчета

где:

r_p – средняя доходность инвестиционного портфеля;

r_f – средняя доходность безрискового актива;

β_p – рыночный риск инвестиционного портфеля.

Формула расчета коэффициента бета следующая:

где:

σ_pm – ковариация между доходностью инвестиционного портфеля и доходностью рынка;

σ²_m – дисперсия доходности рынка.

Оценка эффективности инвестиций на основе коэффициента Трейнора

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

Пример расчета коэффициента Трейнора для оценки эффективности инвестиционного портфеля в Excel

Рассмотрим на примере расчет коэффициента Трейнора для оценки результативности инвестиционного портфеля. Данные использовались аналогичные как для расчета коэффициента Шарпа, отличие заключается в использовании доходности рыночного индекса (доходность рынка). На практике, доходность рынка соответствует доходность фьючерса на индекс (RTSI). Формулы расчета коэффициента Трейнора следующие:

Средняя доходность инвестиционного портфеля =СРЗНАЧ(B5:B10)

Коэффициент бета  =КОВАР(B5:B10;C5:C10)/ДИСП(C5:C10)

Доходность безрискового актива =0,03% (Расчет аналогичен как при  расчете коэффициента Шарпа)

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

Коэффициент Альфа Йенсена для оценки эффективности инвестиций

Коэффициент альфа Йенсена (англ. Jensen index) – размер превышения доходности инвестиционного портфеля над среднерыночной. Данный показатель был разработан Йенсеном в 1968 году и используется для оценки эффективности управления инвестиционными портфелями. Формула расчета коэффициента Альфы Йенсена следующая:

где:

r_p – средняя доходность инвестиционного портфеля;

r_f – средняя доходность безрискового актива;

r_f – среднерыночная доходность;

β_p – рыночный риск инвестиционного портфеля.

Под среднерыночной доходностью подразумевают доходность рынка, которая равна доходности индекса РТС.

Оценка инвестиционного портфеля на основе Альфы Йенсена

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

Пример расчета коэффициента Альфа Йенсена для оценки эффективности инвестиционного портфеля в Excel

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

Коэффициент Альфа Йенсена = D5-(F5+E5*(G5-F5))

Коэффициент Йенсена составил 0,01 (1%). Так как он имеет положительное значение, это говорит об эффективном управлении портфелем и получение доходности выше среднерыночной в среднем на 1%. Активная стратегия управления инвестициями оправдывает себя.

Коэффициент Модильяни для оценки эффективности инвестиций

Коэффициент Модильяни (аналог: индекс Модильяни, коэффициент М2) – показатель отражающий эффективность управления инвестиционным портфелем. Данный показатель был предложен Франко Модильяни в 1997 г. и позволяет сравнивать между собой различные инвестиционные портфели.

Индекс Модильяни. Формула расчета

где:

r_p – средняя доходность инвестиционного портфеля;

r_f – средняя доходность безрискового актива;

β_p – рыночный риск инвестиционного портфеля;

σ_m – стандартное отклонение доходности рынка;

σ_p – стандартное отклонение доходностей инвестиционного портфеля (риск портфеля).

Оценка коэффициента Модильяни

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

Пример расчета коэффициента Модильяни для оценки эффективности инвестиций в Excel

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

 Резюме

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

Автор: к.э.н. Жданов Иван Юрьевич