Как вести учет облигаций в Excel

Часть 1. Считаем НКД (накопленный купонный доход)

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

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

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

Рассмотрим в качестве примера выпуск УральскаяСталь-БО-001Р-02. Какая информация о бумаге нам может понадобиться?

Для начала:

• Наименование
• ISIN
• Дата погашения
• Купон
• Начало купонного периода
• Окончание купонного периода
• Количество купонов в год

В нашем случае это будет выглядеть таким образом.

Следующий этап – расчет НКД. НКД самый сложный момент при учете облигаций в портфеле. Его значение меняется каждый день и руками его вбивать не вариант. Особенно, когда много бумаг.

Есть три пути заполучить НКД в свой портфель.

1. Посчитать. Но для того, чтобы НКД всегда был актуальным, нам нужна актуальная дата в каждый момент времени.

Для этого в Excel есть функция СЕГОДНЯ. Это очень простая функция без аргументов, которая позволяет всегда иметь текущую дату в портфеле.

Зная текущую дату, мы можем рассчитать накопленный купонный доход. Для этого используем специальную функцию НАКОПДОХОД.

Дата_выпуска – начало купонного периода

Первый_доход – окончания купонного периода

Дата_согл – текущая дата (функция СЕГОДНЯ)

Ставка — купонная доходность

Номинал – номинал облигации

Есть еще два параметра, которые не поместились в Окне аргументов

Частота – количество купонных выплат в год

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

На российском облигационном рынке, для 99% бумаг расчет купона идет исходя из того, что в месяце фактическое количество дней, а в году 365 дней (високосные годы не учитываются). Это базис под номером 3. Его можно применять для всех рублевых облигаций.

Теперь у нас есть НКД, который будет меняться вместе с датой каждый день.

Формула НАКОПДОХОД будет работать для выплат купонов 1, 2 или 4 раза в год. Сейчас появилось много бумаг (их уже около 300), по которым купон выплачивается ежемесячно. Для таких выпусков встроенные облигационные функции работать не будут. Придется считать вручную.

1. Как посчитать НКД вручную? Посмотрим на примере ЕвроТранс-001P-02. Нам потребуется все тот же набор параметров для расчета.

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

Используя текущую дату, найдем количество дней с начала купонного периода (ячейка В11).

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

А затем рассчитываем НКД, используя размер купона и количество дней с начала купонного периода (ячейка В15).

1. И, наконец, для тех, кто не слишком дружит с расчетами, НКД можно «тянуть» с биржи. Как, впрочем, и размер купона.

Для этого нужно использовать следующую формулу:

=ПОДСТАВИТЬ(@ ФИЛЬТР.XML(ВЕБСЛУЖБА(«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, то уметь считать НКД очень важно. А то, что НКД не нужно менять вручную, сильно облегчает процесс.

Надеюсь, это было полезно. А в следующем посте расскажу о расчёте доходностей.