Как вести учет облигаций в Excel
Екатерина Кутняк | 04.12.2023 13:48
Часть 1. Считаем НКД (накопленный купонный доход)
Мало кто из частных инвесторов заморачивается ведением своего портфеля. Конечно, это неправильно, но вполне можно понять. Слишком много времени уходит на учет, а его всегда и всем не хватает. Поэтому и доверяют своему торговому приложению. Но, не видя картинки в целом, очень трудно принимать как стратегические, так и тактические решения по портфелю.
Сейчас есть приложения, которые помогают вести портфель. Они не бесплатные, но порядок в портфеле того стоит.
Скачайте приложение
Но я хотела рассказать о том, как можно вести облигационный портфель в Excel. Какие есть специализированные функции для этого. Возможно, кому-то это будет полезно.
Рассмотрим в качестве примера выпуск УральскаяСталь-БО-001Р-02. Какая информация о бумаге нам может понадобиться?
Для начала:
Ø Наименование
Ø ISIN
Ø Дата погашения
Ø Купон
Ø Начало купонного периода
Ø Окончание купонного периода
Ø Количество купонов в год
В нашем случае это будет выглядеть таким образом.
Следующий этап — расчет НКД.
НКД — самый сложный момент при учете облигаций в портфеле. Его значение меняется каждый день, и руками его вбивать не вариант. Особенно когда много бумаг.
Есть три пути заполучить НКД в свой портфель.
1. Посчитать
Но для того, чтобы НКД всегда был актуальным, нам нужна актуальная дата в каждый момент времени.
Для этого в Excel есть функция СЕГОДНЯ. Это очень простая функция без аргументов, которая позволяет всегда иметь текущую дату в портфеле.
Зная текущую дату, мы можем рассчитать накопленный купонный доход. Для этого используем специальную функцию НАКОПДОХОД.
Дата_выпуска — начало купонного периода
Первый_доход — окончание купонного периода
Дата_согл — текущая дата (функция СЕГОДНЯ)
Ставка — купонная доходность
Номинал — номинал облигации
Есть еще два параметра, которые не поместились в Окне аргументов
Частота — количество купонных выплат в год
Базис — вот про базис нужно поговорить подробнее.
Базис – это методика, по которой рассчитывается процент (в нашем случае купон). Excel предоставляет возможность выбрать из 5 типов базисов.
На российском облигационном рынке для 99% бумаг расчет купона идет исходя из того, что в месяце фактическое количество дней, а в году 365 дней (високосные годы не учитываются). Это базис под номером 3. Его можно применять для всех рублевых облигаций.
Теперь у нас есть НКД, который будет меняться вместе с датой каждый день.
Формула НАКОПДОХОД будет работать для выплат купонов 1, 2 или 4 раза в год. Сейчас появилось много бумаг (их уже около 300), по которым купон выплачивается ежемесячно. Для таких выпусков встроенные облигационные функции работать не будут. Придется считать вручную.
2. Как посчитать НКД вручную?
Посмотрим на примере ЕвроТранс-001P-02. Нам потребуется все тот же набор параметров для расчета.
Зная начало и конец купонного периода, несложно рассчитать длину купонного периода. Она нам потребуется.
Используя текущую дату, найдем количество дней с начала купонного периода (ячейка В11).
А также рассчитаем размер купона в рублях. Если у нас есть купонная доходность, размер купонного периода, и мы знаем, что базис Фактический/365, то размер купона рассчитать довольно просто.
А затем рассчитываем НКД, используя размер купона и количество дней с начала купонного периода (ячейка В15).
И наконец, для тех, кто не слишком дружит с расчетами...
3. НКД можно «тянуть» с биржи
Как, впрочем, и размер купона.
Для этого нужно использовать следующую формулу:
=ПОДСТАВИТЬ(@ ФИЛЬТР.XML(ВЕБСЛУЖБА("https://iss.moex.com/iss/engines/stock/markets/bonds/boards/TQCB/securities/"&В3&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,ACCRUEDINT");"//document//data//rows//row/@ACCRUEDINT");".";".")
Где В3 — ссылка на ячейку с ISIN.
НКД с биржи будет отличаться. Причина проста. Биржа считает НКД на дату расчета, а расчеты по облигациям проходят по схеме Т+1. То есть, и купон, соответственно, на завтра.
Если вы ведете учет своих облигаций с использованием Excel, то уметь считать НКД очень важно. А то, что НКД не нужно менять вручную, сильно облегчает процесс.
Надеюсь, это было полезно. А в следующей статье расскажу о расчёте доходностей.
Торговля финансовыми инструментами и (или) криптовалютами сопряжена с высокими рисками, включая риск потери части или всей суммы инвестиций, поэтому подходит не всем инвесторам. Цены на криптовалюты чрезвычайно волатильны и могут изменяться под действием внешних факторов, таких как финансовые новости, законодательные решения или политические события. Маржинальная торговля приводит к повышению финансовых рисков.
Прежде чем принимать решение о совершении сделки с финансовым инструментом или криптовалютами, вы должны получить полную информацию о рисках и затратах, связанных с торговлей на финансовых рынках, правильно оценить цели инвестирования, свой опыт и допустимый уровень риска, а при необходимости обратиться за профессиональной консультацией.
Fusion Media напоминает, что информация, представленная на этом веб-сайте, не всегда актуальна или точна. Данные и цены на веб-сайте могут быть указаны не официальными представителями рынка или биржи, а рядовыми участниками. Это означает, что цены бывают неточны и могут отличаться от фактических цен на соответствующем рынке, а следовательно, носят ориентировочный характер и не подходят для использования в целях торговли. Fusion Media и любой поставщик данных, содержащихся на этом веб-сайте, отказываются от ответственности за любые потери или убытки, понесенные в результате осуществления торговых сделок, совершенных с оглядкой на указанную информацию.
При отсутствии явно выраженного предварительного письменного согласия компании Fusion Media и (или) поставщика данных запрещено использовать, хранить, воспроизводить, отображать, изменять, передавать или распространять данные, содержащиеся на этом веб-сайте. Все права на интеллектуальную собственность сохраняются за поставщиками и (или) биржей, которые предоставили указанные данные.
Fusion Media может получать вознаграждение от рекламодателей, упоминаемых на веб-сайте, в случае, если вы перейдете на сайт рекламодателя, свяжитесь с ним или иным образом отреагируете на рекламное объявление.