Попробую разбавить на этом канале сухую тему Excel-трюков (хотя для написания этого материала он нам понадобиться) и поговорить про инвестирование в облигации Российских компаний и ОФЗ.
Речь пойдет о том, где брать информацию по доходности облигаций и датам выплаты купонов и как ее интерпретировать для расчета денежного потока в удобном формате Excel-файла.
Вся информация может быть взята с сайта Московской биржи на странице Календаря облигационных выплат. Здесь размещены три таблицы: "Погашение и амортизация", "Выплаты купонов" и "Оферты". Для анализа нам потребуются первые две таблицы: "Погашение и амортизация" - отсюда возьмем информацию по дате погашения облигаций в 2021 году и таблица "Выплата купонов" - в ней содержится основная информация по облигациям, датам выплаты купонов, номинальной стоимости и доходности. Необходимо указать период, за который должна быть сформирована таблица. Предлагаю сделать выгрузку за 2021 год:
Таблицы загружаются в формате CSV (красная кнопка "Экспорт в CSV"). Свежая версия Excel (16) открывает файл корректно без дополнительных действий, если же у вас возникают проблемы с кодировкой - то можно воспользоваться встроенным в Excel мастером экспорта, через меню Файл-Экспорт. Разделителем значений в файле является символ ";".
Необходимо обе таблицы перенести в одну книгу. Сделать это можно очень просто - открыть обе книги и в одной из книг нажать на лист (на область его имени) правой кнопкой мыши и выбрать пункт "Переместить или скопировать" в открывшемся окне выбрать книгу, в которую необходимо перенести лист и выделить необходимый лист по желанию поставить галочку на вкладке "Сделать копию" (в этом случае в выбранной книге будет создана копия листа, иначе - лист просто перенесется). Также советую дать понятные наименования листам.
Итак, основная таблица, которая нам потребуется - это таблица "Выплата купонов". В нее необходимо затянуть данные по дате погашения облигаций из таблицы "Погашение и амортизация" - колонка "D" при помощи функции ВПР().
Можно разделить облигации по количеству выплаты купонов в год:
В выборку попало 1268 облигаций разных эмитентов с общим количеством платежей по купонам за 2021 год - 3 525.
По подавляющему большинству облигаций купоны за год выплачиваются дважды (66,9%), по 24% - четыре раза в год. Один раз в год выплата купонов происходят по 5,2% облигаций. Отдельно хочу выделить облигации по которым выплачиваются купоны 12 и более раз в год (фиолетовые сектор, плюс небольшое число группы Иное).
2. Проанализировать купонную доходность облигаций (колонка - доходность купона) - процентной выплаты от номинала облигации (не путать с доходностью облигации!). Примерно так выглядит график по ставкам купонов из нашей выборки:
Среднегодовая доходность - 9%, медианная - 8,8%. Если ваша облигация имеет номинал 1000 рублей с доходностью 10% в год и выплатой купона 4 раза в год, до размер каждой выплаты будет равен: 1000 * 10% / 4 = 25 рублей. Из этой суммы также, в зависимости от эмитента, но почти во всех случаях - будут удержаны налоги и, возможно, комиссионное вознаграждение брокера.
В принципе, полученных данных уже достаточно, чтобы можно было построить график денежного потока по купонным доходам на 2021 год. Достаточно выбрать свой портфель облигаций, умножить их номинал на количество, которое вы планируете приобрести и рассчитать купонные платежи используя выгруженные нами данные с сайта Московской Биржи. Также можно рассчитать налоговые платежи, чтобы CF был более точным.
Материалы можно обогащать, например, добавить информацию об эмитенте облигации и ссылку на информацию об отчетности эмитента - вся эта информация есть в удобном Excel формате на сайте Московской биржи.
Если тема инвестиций вам интересна - поставьте лайк этому посту. Так я пойму, что нужно писать еще:)))