Расчет доходности инвестиций в EXCEL

Формула расчета доходности портфеля с примерами. Текущая доходность, абсолютная и в процентах. Как определить и сравнить выгодность инвестиций с помощью годовой доходности. Определение прибыли инвестиций с учетом ввода-вывода средств. Готовая таблица Excel со всеми формулами и расчетами.

Почему не приложения?

Потому что есть старый-добрый Эксель. Все давно придумано.

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

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

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

Изначально мой отчет был табличкой в экселе с использованием упрощенного языка программирования 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. Цена покупки — заносится вместе с комиссией, ведь иной раз за сделку приходится платить ощутимые комиссионные.

Как считать доходность?

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

Сложность заключается в том, что большинство подходов к расчету доходности подразумевают простую формулу:

$$ R =frac{A}{B}$$ 

А – полученный доход

В – стартовые инвестиции

Представим себе жизненную ситуацию, когда человек в январе инвестировал 10 000 р, а в декабре – 90 000 р. К концу года на инвестиционном счете оказалось 110 000 р (ценные бумаги выросли в цене). Какова доходность инвестиций? Что на что делить? Если мы возьмем доход в 10 000 р и разделим на сумму всех инвестиций – 100 000 р, то получим очень сложно интерпретируемый результат – 10%. Ведь большую часть срока на счете находилось всего 10 000 р, а остаток добавлен только за месяц до конца года …

Или еще более интересный пример. В январе инвестор положил на брокерский счет 100 000 р, а в декабре забрал с него 90 000 р. К концу года на брокерском счете фигурировала сумма 15 000 р. Если просто сложить пополнения и изъятия получится что суммарная инвестиция равна 100 000 – 90 000 = 10 000 р. Разделив доход на суммарные инвестиции, получим слишком оптимистичные 50%. Очевидно, что так делать нельзя …

Получение токена

Работа с API происходит через токен. Процесс получения токена описан в документации на

GitHub’е

. Для его получения нужно:

1. Перейти на сайт Тинькоффа и выполнить вход в свой аккаунт.
2. Убедиться, что функция «Подтверждение сделок кодом» отключена.
3. Пролистать вниз страницы до пункта токен для OpenAPI и нажать на «Токен для торговли»
4. Скопировать и сохранить токен. Он отображается только один раз, но можно выпускать неограниченное количество токенов.

После получения токена можно приступить к работе с таблицей. Перейдите по

ссылке

и скопируйте к себе таблицу, в которой будете вести учёт.

Вставьте свой токен в ячейку B1 в листе «Настройки».

Важно!

После вставки токена не удаляйте и не переименовывайте лист “Настройки”. Если в ячейке B3 появится текущий курс доллара — поздравляем, все готово к работе.

Основные функции скрипта

:

• getPrice() — получить текущую стоимость инструмента по тикеру
• getTrades (ticker) — получить список всех операций по определенному инструменту. В скобках необходимо указать тикер инструмента, по которому надо увидеть список операций
• getAllTrades() — получить список всех операций по всем инструментам. В скобках можно указать временной промежуток и посмотреть операции с определенного момента
• getPortfolio() — получение текущего портфеля
• getCurrencies() — получение валютных активов
• getTradesIIS (ticker) — получить список всех операций по определенному инструменту (ИИС). В скобках необходимо указать тикер инструмента, по которому надо увидеть список операций
• getAllTradesIIS() — получить список всех операций по всем инструментам (ИИС). В скобках можно указать временной промежуток и посмотреть операции с определенного момента
• getIISPort() — получение текущего портфеля на ИИС
• getCurrenciesIIS() — получение валютных активов на ИИС
• getUSDval() — получить текущий курс доллара (по стакану)

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

Пример работы функции getPortfolio для получения портфеля

Зачем инвестору вести учет активов

Отсутствие контроля финансов – причина многих проблем как для личного или семейного бюджета, так и для портфеля инвестора. И дело не только в том, что о результатах инвестирования придется перед кем-то отчитываться (например, перед налоговой). Учет нужен прежде всего самому инвестору. Конечно, он потребует определенных усилий и временных, а иногда и денежных затрат. Но ошибочно считать, что правильный учет и мониторинг активов не добавит вам доходности. Давайте подробно разберемся, почему это важно и полезно.

1. Вы наблюдаете динамику того, что происходит с вашим портфелем. Особенно это важно при долгосрочных вложениях. Не видя темпов роста, за счет чего и какие активы растут, а какие – падают, вы с большой вероятностью совершите ошибки при выборе инструментов и стратегии. Иными словами, инвестируете «вслепую».
2. Не учитывая инвестиции, вы не сможете проводить осмысленные ребалансировки портфеля. Со временем диверсификация ваших вложений будет только расти и роль учета – тоже.
3. Учет требует от инвестора самоорганизации и дисциплины. Это приучает вас действовать своевременно, придерживаться полезных правил и принципов, находить в себе силы отказаться от потенциально убыточных сделок. Контроль за инвестициями – неотъемлемая часть риск-менеджмента.
4. Наконец, учет и отчетность тесно связаны. Пустить инвестирование «на самотек» – означает рисковать получением штрафных санкций. Например, от неуплаченных вовремя налогов или невыполненных условий договора с брокером. Это негативно скажется на доходности.

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

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

Не менее важен учет и контроль в планировании личных финансов. Любой инвестор – прежде всего, обычный человек, которому сложно выделить и сберечь средства для инвестирования без грамотно построенного личного финансового плана. Пример таблицы учета денежных потоков (отдельная благодарность Т-Ж) мы рассматриваем в рамках бесплатной недели обучения на Курсе Ленивого инвестора:

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

Это сложно!

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

Шаблон для расчета IRR инвестиций в EXCEL

Для быстрого расчета результативности инвестиций предлагаем простой шаблон в EXCEL.

Шаблон считает IRR для каждого из периодов инвестиций, и за последние 6 периодов (колонка «IRR за 6 периодов»). Периоды могут быть произвольными: один месяц, один год. Более того, в калькуляторе используется функция XIRR (ЧИСТВНДОХ), которая умеет считать IRR даже для неравных между собой периодов. Это значит, что в колонке «Дата» можно указывать любую дату, а не только начало месяца или, например, конец года. Удобнее всего вносить новые данные каждый раз, когда пополняется портфель или когда происходит изъятие средств. Для интереса можно вносить новые данные чаще, даже когда нет пополнений портфеля. Например можно указывать даты, когда в размере портфеля происходят какие-то значимые изменения или просто с некоторой заданной регулярностью.

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

Загрузить калькулятор результативности портфеля в формате EXCEL

Что дает учет

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

Несколько таблиц

У меня есть две основные таблицы, которые я веду уже много лет:

• Семейный бюджет.
• Инвестиции.

Есть еще вспомогательные таблицы. Я тоже периодически их посматриваю и заполняю:

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

Bloomberg

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

Учет инвестиций в Excel

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

[ Скачать шаблон учета инвестиций для ранних пенсионеров ]

Скопируйте себе файл в Гугл Таблицы. Либо сохраните его в формате XLSX и настройте его под себя в Экселе.

Шаблон не самый идеальный:

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

Портфель

В этой вкладке я веду учет активов. Хочу отметить наиболее интересные колонки. Сортирую в порядке важности:

• Дата выплаты. Я живу на доходы от рынка. Мне критически важно знать когда именно я получу дивиденды, купоны и ренту.
• Выплата. Сколько денег я получу на счет.
• Дд, чист, %. Чистая дивидендная доходность. Уже с учетом налогов. Дает понимание не пора ли сменить “дойную коровку” на другую. Или может лучше перейти на “коз” и “кур”.
• Дивиденд в рублях. Размер дивиденда на одну акцию. Технический параметр.
• Доля акции или облигации в портфеле. Если одна компания занимает в портфеле более 15%, то стоит задуматься о ребалансировке. Если акции в сумме занимают слишком существенную долю (более 85%), то мне некомфортно. Это тоже повод задуматься о балансировке.
• Справедливая цена акции. При какой цене стоит задуматься о продаже актива. Очень условная цифра. Я убрал значения по всем бумагам, чтобы не смущать читателей.

История

Тут я тщательно записываю сделки и пополнения портфеля. Снова пишу в порядке важности:

• На какие суммы пополнил портфель.
• Зачем снимал деньги.
• Когда купил или продал актив.
• Почему купил или продал.

Очень важны даты. Они могут помочь в будущем. Например, для налоговой оптимизации.

Примечание: цветовая разметка помогает быстрее считывать информацию.

Дивиденды

Вторая по популярности вкладка (после портфеля):

• Сколько получил дивидендов и купонов.
• Когда мне отправили деньги.
• Когда я их получил фактически.
• Какие налоги заплатил с дивидендов и купонов.

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

Анализ и план закупок

Данная вкладка – поле для творчества. Здесь я творю что хочу. Отвечаю себе на следующие вопросы:

• Не стоит ли добавить в портфель новую дивидендную “коровку”.
• Что я буду покупать в моменты коррекций.
• Что я буду менять в периоды ребалансировки.
• Вердикт по эмитенту.
• А что там на западных рынках?
• и т.д.

Внимание! Названия эмитентов и мнения не является инвестиционной рекомендацией. Это лишь мои размышления. Все данные устарели.

Динамика капитала

Заглядываю туда раз в год. Веду эту вкладку для галочки. Почему? Потому что очень велик соблазн начать соревноваться с бенчмарком, с друзьями и с коллегами-инвесторами. Я убежден, что это крайне вредно для инвестора-пенсионера. Мне важен ответ только на один вопрос – хватит ли мне дивидендов и купонов, чтобы прожить следующие годы.

Примечание:

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

Файлы для скачивания

Для скачивания файлов необходимо зарегистрироваться или авторизоваться

Будет полезным!

——————-

——————-