Инвестирование в отраслевые фонды в Экселе
Написать эту статейку меня побудила замечательная серия многоуважаемого @abak «Теория инвестиций для начинающих». В качестве конфетки внизу даю ссылку на табличку, получающую текущие цены на европейские фонды, их прибыльность и другие параметры из интернета. Кроме того мне хотелось бы немного расширить изначальную статью с точки зрения начинающего инвестора, инвестирующего из дома и лишенного вкусностей внутренней кухни Дойче банка.
Предостережение: Автор ни разу не спец в скрэпинге интернет-сайтов и инвестициях. Всё что здесь описывается делалось для души и на коленке. Автор никому ничего не обещает, а лишь делится собственным опытом и мыслями по поводу инвестиций, в частности в отраслевые фонды. Ещё одна цель – дать пищу для размышлений, а не предоставить рецепт от всех болезней. Всё, что здесь описано, добывалось автором путём проб и ошибок, а, как известно: „Срубленные головы стремительно умнеют“. Потому, главный посыл статейки: Не бояться экспериментировать.
Почему фонды
Хотя вся необходимая информация и доступна и бесплатна, докопаться до реально работающих схем, приносящих доход, каждому приходится самому. Та пена, которую все ежедневно видят на баннерах типа: „Покупай акции такие-то и продавай такие-то“ не имеют ничего общего с реальной жизнью. В частности, реклама фондов, рассылаемая банками („Вложись в супер-ETF и обрети целых 4.5% годовых. “), оказала на автора противоположное действие и отложило инвестиции в фонды на три года. Прежде чем перейти к фондам я остановлюсь на минусах акций и облигаций.
Трудно установить точку входа и выхода из позиции. Все индикаторы вроде для того и существуют, но на практике не работают.
Анализ всяких технических вкусностей вроде ROE… (см. finance.yahoo.com, раздел Statistics и Financials), а также прибылей за определённые промежутки времени, по аналогии с выложенной таблицей для оценки фондов, даёт прибыльность меньшую, нежели фонды.
Абсолютно непонятна логика/психология рынка: Почему если в новостях говорят о том, что у фирмы был хороший или плохой год, курс может как упасть, так и подняться. Где логика?
С акциями такое чувство, будто все фирмы мухлюют с отчётностью (привет, Wirecard!)
Можно по недосмотру нарваться на что-то экзотическое, например на облигацию, которая погашается по мере приближения к моменту погашения, зато
Куча плюсов облигаций:
Они понятны: время их действия ограничено, купоны выплачиваются регулярно, купив облигацию сегодня и продав по тому же курсу завтра, получаешь прибыль с купона.
Можно играть на курсах, зная, что по истечению времени действия облигации будет выплачена полная её стоимость (а может и не выплачена, если по облигации случился дефолт).
В случае дефолта, владельцы облигаций обладают преимуществом при получении имущества обанкротившейся фирмы (проданного конечно, никто не пришлёт Вам по почте старый стул). Правда выплат по дефолтным облигациям можно ждать лет десять: года три назад я с удивлением увидел в поиске облигаций Lehman Brothers.
Поэтому я выбрал…
Фонды
Точнее отраслевые фонды. В отличие от индексных фондов, в которые входят взвешенные акции определённого индекса, в отраслевых фондах намешана любая солянка. Главное, чтобы вкладчики были довольны.
Чтобы облегчить себе, а теперь уже и вам, жизнь, я составил табличку на старом добром Экселе, которая грабит данные с сайта ariva.de. Так как странички иногда меняют формат и наполнение, то табличка требует постоянного ухода, что я постараюсь и делать через GitHub. В общем, снимаю шляпу перед теми, кто занимается скрэпингом профессионально: это редкостное болото, вытянуть из которого что-то дельное очень не просто.
Таблица
Для тех, кто никогда не работал с Selenium в Экселе, существует библиотека SeleniumBasic. Коротенькое описание установки для работы с ней приведено здесь. По нажатии на „Update“, таблица загружает данные о прибыли ценной бумаги (ЦБ) с сайта „ariva.de“.
Таблица настроена на работу с запятой в качестве разделителя между целой и дробной частями числа. Американский формат записи, через точку, не тестировался.
WKN (Колонка „C“): Немецкий аналог „Международного идентификационного кода ценной бумаги“ (ISIN), только короче. ISIN настолько международный, что finance.yahoo.com им не пользуется, предпочитая свои, „мериканьские“, обозначения. Дикари-с. WKN-единственный обязательный входной параметр таблицы.
Favorites (Колонка „D“): Если ячейка пуста, и чек-бокс „Update only favorites“ отмечен, то для ускорения данная ЦБ будет пропущена. Раз в неделю я обновляю данные по всем ЦБ, но чаще всего только по тем, которые меня интересуют.
Страна (Колонка „E“): Страна не всегда обновляется корректно. В таких случаях ячейка со страной остаётся пустой.
Отрасль (Колонка „F“): Довольно грубое деление по отраслям. На немецком, так что гугл транслейт в помощь. Используется чтобы равномерно распределить ЦБ по разным секторам экономики.
Benchmark (Колонка „G“): Из какого индекса набраны акции.
Валюта (Колонка „H“): Тут всё понятно. Сайт видимо переводит всё в евро, а потому иные валюты я пока не встречал.
URL (Колонка „I“): В случае отсутствия автоматически обновляется по WKN.
Прибыль ЦБ за определённый период (Колонки „J“ — „N“): Прибыль за 3/6 месяцев, 1/3/5 лет. Зелёным цветом помечены ЦБ из верхнего 85% перцентиля, красным – из нижнего 15% перцентиля.
Время обновления (Колонка „O“): Если ЦБ уже была обновлена сегодня, то при повторном обновлении (тоже сегодня) она будет пропущена.
Остальные колонки говорят сами за себя: Цена в евро (Колонка „P“), Альфа (Колонка „Q“), Бета (Колонка „R“): Отношение Шарпа (Колонка „S“).
Сортировка осуществляется автоматически, выбором соответствующего критерия лист-боксом. Для сортировки важны данные после ключевого слова „Sorting“ в колонке A. Категории перечислены в ячейках „A59:A67“, после которых (колонка B) идут формулы выбора соответствующей колонки для сортировки и вид сортировки, по возрастанию (TRUE) или убыванию (FALSE).
Цель таблицы: Обновить данные и предоставить мне и вам удобные сортировки по разным критериям:
3m/6m/1yr/3yrs/5yrs: Доходность ЦБ за данный период в процентах. В принципе меня обычно интересует только доходность за последние 3 месяца.
Положение (место) в каждой категории (3m/6m…) вычисляется в колонках AB:AI.
Ну и в колонке „AJ“ находится сумма всех категорий. Теоретически, чем меньше число, тем больший доход принесёт фонд. Стоит также отменить, что в сумму входят так же значения альфа и бета и отношение Шарпа, хотя они не являются линейнозависимыми. В общем можно и альфа и бета выкинуть.
В качестве простеньких бонусов:
Часто задают вопрос: А откуда следует, что если фонд давал прибыль в прошлом, то он будет давать прибыль и в будущем? Для ответа нарисуем график прибыли между 6 месяцами и 3 месяцами (условное будущее) и между годом и 6 месяцами (прошлое). Как видно из графика, чем больше прибыль в прошлом, тем больше прибыль и в будущем. Если убрать сильно выпадающие точки в верхней части графика, то коэффициент корреляции будет ещё выше: 0.2. Для автора этот результат был откровением, так как автор ожидал более хаотичного распределения точек. И, не забываем, что посредине, между 6 месяцами и годом был кризис в виде ковида. Выпадающие две верхних точки относятся к „зелёной энергетике“, т. Е. к хайпу в настоящий момент. Конечно, хотелось бы, чтобы наклон был больше, но ведь растёт!
Второй график – зависимость прибыли за год и отношения Шарпа. Красивый результат, прямо как иллюстрация из учебника. Впрочем, ничего удивительного, добавить волатильность и будет прямая линия.
Как работает табличка
Сперва несколько простых напоминаний, которые все знают, но без которых никуда:
Инвестиции: в недвижимость, акции, фонды, образование, золото — это всегда риск. Поэтому просто необходимо оставлять неприкосновенный запас на экстренный случай.
Ждать кризиса, чтобы вложиться на низких ценах, или бояться кризиса, это примерно как откладывать по тем же причинам рождение детей. Кризис будет, и будет он завтра, а сегодня необходимо инвестировать (ну и параллельно делать детей).
Конечно у каждого свой стиль жизни и потому свой стиль инвестирования. На семейном совете было решено часть сэкономленных в прошлом средств частями перевести в ценные бумаги и больше туда ничего не докладывать.
Что я стараюсь делать:
Продаю ЦБ только не ранее 3 месяцев после покупки. Даю каждой ЦБ шанс показать на что она способна. А могу и продать и раньше – хозяин счёта всё-таки я, а не табличка.
Если ЦБ лежит на счету уже более 3 месяцев и выбывает из первой десятки, то могу её продать и купить что-то из первой пятёрки.
Когда наступает время очередного зуда и хочется продать или купить или и то и другое, я залезаю на „finanzen.net“/“ariva.de“ в раздел „Fonds“/„ETFs“ в разделы „Fonds-Suche“ и „ETF-Suche“ (поиск по фондам и ETF), ищу фонды с максимальной прибыльностью за год и вношу их в табличку. Таким образом проблема попасть на фонд вроде индексного фонда японского рынка и зависнуть на десятилетия в минусах практически невозможна.
Что я стараюсь не делать:
Вкладываться в иные валюты, нежели евро и доллар. Если посмотреть в долгосрочной перспективе, все региональные валюты (рубль, турецкая лира, ранд, бразильский реал) постоянно снижаются. Единственное наверное исключение это юань, но в него вложиться надо сильно постараться – китайские рынки так и остаются за китайской же стеной. Видимо по традиции боятся варваров с севера, и правильно делают.
Вкладываться в хайп: Руки, конечно, чешутся, но Тесла/Биткоин не для меня. Если хотите, чтобы Тесла/Биткоин обрушилась, напишите мне и я их куплю. Убытки поделим пополам 🙂
Меня смущают фонды, не торгующиеся на бирже (пример: „green benefit Global Impact Fund“). Да, он есть в моей копилке, но его покупка растянулась на три дня, так что быстро избавиться от него в течение 10 минут и по нужной цене скорее всего не удастся.
Не связываюсь с акциями и облигациями европейских фирм: К ним у меня никакого доверия после банкротства AirBerlin и Sympatex, чьи дефолтные облигации до сих пор висят мёртвым грузом на моём счету. Только моя медлительность спасла меня от покупки Wirecard прямо перед их дефолтом. Да и Баффета недавно немцы обидели.
Я не вкладываю в ЦБ дополнительных денег и вот почему (это лишь моё видение, оно не критично, у каждого свой стиль инвестирования):
Фонд должен приносить деньги мне, а не я ему. Поэтому 10% прибыли я вывожу и вкладываю в покупку золота, наличной валюты (можно и в тушенку/патроны, зависит от фантазии), в НЗ, на случай если всё обрушится.
Не получается откладывать 10% с зарплаты получается – наверное намазываем на хлеб слишком много масла и моемся горячей водой.
А это ни тепло, ни холодно:
Комиссия инвестиционной компании (ИК): Не особо обращаю внимания на комиссию. В некоторых случаях она достигает 5%, но и отдача фонда в таком случае может быть неплохой.
StopLoss-ы: Иногда применяю, но чаще нет. Их иногда внезапно срывает, после чего позиция откатывается на старые значения. Но уже без меня.
Продавать всё во время кризиса. Да, в этот раз (март 2020) стоп-лоссы сработали как часы и я всё быстро распродал, в основном с прибылью. Но потом рынки достаточно быстро восстановились и я еле успел вложиться. Так что вышло то на то.
Можно застраховать фонды через Put-опционы. Тут у меня двойственная позиция:
Покупать: Автор предпочитает спать спокойно и видеть, что счёт в ИК не зависит от падения рынка.
Не покупать: Автор предпочитает спать спокойно, а не подгадывать момент, когда рынок упал с целью продать опцион.
В общем, и так и эдак плохо. Реально, последние две мои покупки опционов закончились потерей денег, ну а в сумме: плюс/минус ноль.
Количество фондов: Не имеет значения, главное в верхней трети таблицы. Сейчас 7. Инвестирую каждый раз приблизительно одну и ту же сумму денег, чтобы потом легче было посмотреть где и сколько выиграл/проиграл.
Инвестиционный диапазон: В моём случае, редко какой фонд задерживается более чем на год, но я инвестор скорее нервный, чем рисковый.
Доходность за три года приблизительно 21% годовых. Дело в том, что треть денег сейчас находится в связанном состоянии в акциях. В будущем буду потихоньку от них избавляться и переводить в отраслевые фонды.
Риски
Доллар обрушится, обрушится…: Честно говоря, не верю по одной причине:
Штаты собирают налоги со всего мира: Вот с какого бодуна мне приходится регулярно заполнять форму W-8BEN и посылать её в ИК, хотя я ни разу не гражданин США, а лишь купил их акции или АДР (американские депозитарные расписки)?
А вдруг будет новый кризис?
Кризис будет, но, экономика сейчас достаточно устойчива. Проверено корона-вирусом.
А вдруг завтра война?
Очень возможно, учитывая события 2014 года. В этом случае все накопления превратятся в ничто и именно на этот случай 10% прибыли и откладываются в НЗ (золото/тушенка/патроны). Автор до сих пор с содроганием вспоминает прочитанные в школе рассказы очевидцев о блокадном Ленинграде, когда за золото выменивали килограмм требухи типа лёгких.
А как на счёт искусственного раскачивания лодки, вроде того что сделали с GameStop?
Вот это действительно рискованно. Надеюсь у них хватит такта остановиться. А вообще, вся ситуация с GameStop сильно напоминает Россию в февральскую революцию, когда кучке большевиков удалось расшатать за пару месяцев огромную империю. Есть в Reddite что-то анархо-большевицкое.
Заключение
Вся информация, имеющаяся в статье, получена из открытых источников и ни разу не секретная. Кроме того, табличка скорее корова Шрёдингера – неизвестно, жива она, или уже мертва, узнать это можно только опытным путём, начав инвестировать.
Блог Александра Кашина об инвестициях
Инвестиции, пассивный доход и финансовая независимость
Таблица Excell для учета портфеля
1.Основная вкладка — Портфель
Здесь отмечаем свои покупки: сколько акций и по какой средней цене куплены. Котировки вы можете вписывать вручную. Или загрузить эту таблицу в гугл докс и вставлять их из гугл финанс.
Также здесь отмечаем общую сумму взносов на счет.
2. Вкладка «Графики»
В ней отмечаем даты и какая была доходность портфеля.
3. Вкладка «Взносы»
В ней указываем свои взносы и СЧА (стоимость портфеля).
таблица для формирования портфеля
таблица для учета инвестиций
таблица для расчета инвестиционного портфеля в excel
таблица для ведения инвестиционного портфеля
учет портфеля ценных бумаг в excel
таблица инвестиционного портфеля в excel
таблица для инвестиционного портфеля
пример учета инвестиционного портфеля в excel
Запись опубликована автором sostoyanie в рубрике Без рубрики с метками Основы, Полезное. Добавьте в закладки постоянную ссылку.
Как быстро посчитать свою доходность от инвестиций | Формула + готовый шаблон (файл) в Гугл
Сколько вы заработали (или потеряли) денег на инвестициях за прошлый год? А в сумме за 2-3 года? 5 лет? Какой был у вас результат в позапрошлом году?
Все эти данные можно конечно достать из отчетов брокеров. Или примерно прикинуть, сколько было и сколько стало денег на счете через определенно время. И вывести какой-то финансовый результат. Прибыль пять, десять, пятьдесят, сто тысяч, миллион рублей ( долларов, евро).
А сколько это в относительной доходности? То есть, годовой прибыли в процентах. Ситуация может осложняться периодическими вводами-выводами денег с брокерского счета.
Закинули сегодня денежку. Через пару месяцев еще, потом еще. Иногда снимали средства. Или дивиденды могут капать сразу на карту (а не на брокерский счет). В итоге получается целая вереница движений денег.
Ну так что, сможете сказать про свои реальные финансовые достижения?
Если ответ положительный, то респект.
Я уже несколько лет самостоятельно веду учет эффективности своего инвестиционного портфеля. И могу с точностью до доли процента ответить какую доходность я получил в том, позатом году и так далее.
А зачем это нужно?
Зачем вести учет инвестиций
Причин несколько. Вот главные из них.
Вы удивитесь, но есть полутрейдеры-полуинвесторы, играющиеся на бирже. И вроде бы как у них все это получается. Там закрыл сделку в плюсе, здесь. Тут немного прогорел. Потом еще. Наступает белая полоса — и снова прибыль.
Произошла сильная просадка по позиции. Закрывать с убытком жалко. Оставим (заморозим) до лучших времен. Со временем котировки восстановятся. И можно будет избавиться от позиции — закрыть ее в ноль.
А что в итоге? Какова результативность?
Самый частый ответ — «Ну я что-то зарабатываю. Вот смотри у меня есть статистика сделок.»
А давай переведем твой результат за несколько лет в среднегодовую доходность? И поймем, насколько выгодна твоя стратегия.
И тут выясняется неприятная вещь. Оказывается со всеми сделками, комиссиями брокеру и прочими расходами (включая периодические заморозки убыточных позиций) — реальный результат не такой впечатляющий. Доходит до того, что некоторые «успешные» игроки, за несколько лет на самом деле получали окололунную или весьма скромную доходность.
Среднегодовая доходность позволяет оценить эффективность используемой стратегии.
Второй важный момент — это сравнение с эталоном (бенчмарком).
Это может быть какой-нибудь фондовый индекс. Или например, безрисковые ставки по банковским вкладам или надежным облигациям.
Допустим, вы все посчитали. Вывели себе какой-то результат. Например, за прошлый год вы заработали 15% годовых. Много это или мало?
Непонятно. Нужно с чем-то сравнивать. Если вы инвестируете в российские акции, то ориентир для вас будет индекс Московской биржи. Вернее финансовые инструменты в лице фондов (БПИФ или ETF).
Покупаете американские акции? Берем соответствующий ETF для сравнения. На тот же индекс S&P 500.
И может получится, у вас годовая прибыль 15%, а соответствующий индекс вырос на 25%.
Или за 3 последних года, путем отбора, анализа отдельный акций инвестор получал среднегодовую доходность 12%. А фонд показал среднегодовую прибыль в 14%.
Тратить времени и сил больше, а получать меньше, по сравнению с простым следованием за индексом. Есть повод по крайней мере задуматься. И может быть что-то поменять в своей стратегии.
Как считать доходность?
Многие скажут, финансовый результат можно посмотреть в отчетах или личном кабинете своего брокера.
Действительно. У большинства брокером это есть.
Во-первых. Некоторые брокеры не совсем корректно рассчитывают доходность (не знаю с чем это связано).
Во-вторых. Когда у тебя несколько брокерских счетов + возможно есть другие варианты вложений (те же банковские вклады), то как все это сложить? Или к примеру вы меняете брокера и переносите все активы к другому — вся система учета ломается. И можно начинать с чистого листа.
С помощью функции XIRR можно довольно просто вывести среднегодовую доходность за любой промежуток времени. Даже с учетом ввода-вывода средств.
Формула расчета доходности:
= XIRR ( B1:B»x» ; C1:C»x» ) * 100
= XIRR (денежные потоки ; даты выплат )
Потребуются два вида данных:
- Даты пополнения или снятия денег со счета.
- Суммы этих денежных потоков.
В итоге получается таблица примерно такого вида.
Обратите внимание на несколько моментов.
- Выводы средств со счета — отрицательные числа.
- Дивиденды и купоны. Поступают на брокерский счет сразу, то их не учитываем. Если депозитарий кидает выплату на другой счет (банковскую карту) — то это записываем как вывод средств (со знаком минус).
- Итоговая сумма должна быть отрицательной (как будто вы выводите все деньги со счета). Иначе вся логика расчетов рушится (система выдаст ошибку).
Средняя доходность за несколько лет
Аналогично можно рассчитать среднегодовую доходность за несколько прошлых периодов.
И вуаля. Все готово.
Разное
Как считать доходность по отдельным годам?
Все тоже самое. Только нужно знать дополнительно сколько денег было на счете (или счетах) на начало и конец каждого года.
Как лучше вести учет, если несколько счетов?
Вариант 1. Все ваши инвестиции работают по принципу одной стратегии. Весь капитал составляют единую структуру портфеля, то разбивать учет на несколько составляющих, не вижу смысла. Простой пример, долгосрочное пассивное накопление капитала.
Вариант 2. Когда у вас несколько различных стратегий, то лучше вести по ним отдельный учет. Например, вы копите деньги на пенсию. Для этого у вас есть активы, которые вы просто покупаете и держите всю жизнь. Параллельно вы торгуете на срочном рынке (с кредитным плечом). Возможно еще спекулируете акциями.
И правильно будет разделить учет доходностей на несколько разные портфелей. Плюс один общий, как суммарный результат.
Готовая таблица
Все вышеперечисленные примеры с расчетами доходностей выложил в онлайн-таблице (нужен аккаунт гугл).
Переходите по ссылке. Сразу делайте копию (в меню «Файл» — > «Сделать копию») и таблица будет доступна для редактирования и использования.
Источник https://habr.com/ru/post/540670/
Источник https://sostoyanie.com/tablica-excell-dlja-ucheta-portfelja/
Источник https://vse-dengy.ru/dohodnost-investitsiy-xirr.html