Excel таблица для мониторинга облигационного портфеля с данными из API московской биржи

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

В таблице реализовано:

— Краткое название бумаги
— Доходность купона в %
— Доходность купона в рублях
— НКД
— Цена бумаги в процентах
— Номинал бумаги
— Цена бумаги в рублях (смог решить вопрос с амортизируемыми бумагами)
— Дата погашения
— Дата оферты
— Доходность к оферте
— YTM
— Эффективная доходность
— G-spread
— Дней до погашения
— Дюрация

Всё это будет вам доступно лишь при введении ISIN бумаги. Реализовано много решений, которые сильно упрощают работу.

+ ко всему этому в таблице есть простенькие формулы, помогающие в подсчёте не для одной бумаги, а если их у вас множество

Сама таблица находится тут

В этой статье я разберу каждый из пунктов по отдельности, чтобы сразу ответить на все вопросы

Для большего понимания можете также заглянуть в мою предыдущую статью. В ней я подробно рассказываю как работают формулы

Начинаем с ISIN и режима торгов

Это два самых главных элемента, которые нужны для расчёта всех остальных формул.

ISIN подставляется руками, а режим торгов высчитывается по формуле:

=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/securities/»&A2&"/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=BOARDID");"//document//data//rows//row/@BOARDID")

Название бумаги и количество

Название бумаги выдаётся краткое, чтобы можно было проще читать. Оно отдаётся посредством формулы:

=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SHORTNAME");"//document//data//rows//row/@SHORTNAME")

Количество бумаг указывается вами в зависимости от размера вашего портфеля. Снизу автоматически подсчитывается суммарное количество по всем бумагам в портфеле.

Купон в рублях и процентах + НКД

Купон в процентах - это годовая процентная доходность облигации, считается по формуле:

=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,COUPONPERCENT");"//document//data//rows//row/@COUPONPERCENT")

Купон в рублях- это размер 1 купона в рублях, считается по формуле:

=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=COUPONVALUE");"//document//data//rows//row/@COUPONVALUE")

НКД указывается актуальный, также считается по формуле:

=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=ACCRUEDINT");"//document//data//rows//row/@ACCRUEDINT")

Цена бумаги и общая стоимость бумаг в портфеле

Это моя личная гордость, долго мучался с тем как же считать цену бумаги. Сначала проценты умножал на 10, но это не помогало, если у компании была хоть одна амортизация или же нестандартный номинал.

Цена бумаги в % показывает процентную стоимость бумаги относительно номинала. Номинал берётся за 100%. Это считается по формуле:

=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=LAST");"//document//data//rows//row/@LAST")

Номинал бумаги особенно актуален для расчёта бумаг с амортизацией и считается по формуле:

=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=FACEVALUE");"//document//data//rows//row/@FACEVALUE")

И наконец цена бумаги. Умножается номинал на процентную цену и показывает текущую цену в рублях.

Стоимость бумаг в портфеле- это обычное перемножение количество бумаг в портфеле на цену 1 облигации

Дата погашения и дата оферты

В дате погашения ничего удивительного, она считается по формуле:

=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=MATDATE");"//document//data//rows//row/@MATDATE")

В формуле по дате оферты я добавил условие, чтобы если оферты нет, то писалось «нет оферты» вместо выпадающей ошибки. Считается по формуле:

=ЕСЛИОШИБКА(ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=OFFERDATE");"//document//data//rows//row/@OFFERDATE");«нет оферты»)

Всевозможные доходности

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

=ЕСЛИОШИБКА(ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=YIELDTOOFFER");"//document//data//rows//row/@YIELDTOOFFER");«нет оферты»)

YTM показывает % годовых, которые даёт бумага. Рассчитывается по формуле:

=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=orderbook&orderbook.columns=YIELD");"//document//data//rows//row/@YIELD")

Тут могут немного некорректно отображаться доходности флоатеров или бумаг с близкими офертами.

Эффективная доходность показывает значение в годовых, но до ближайшего события (погашение/оферта)

Например, в примере на скриншоте последняя строка- это облигации М.Видео. Их YTM составляет 21,64% годовых, но оферта уже менее, чем через год, поэтому Эффективная доходность чуть меньше и составляет 21,3267

Эффективная доходность считается по формуле:

=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=marketdata_yields&marketdata_yields.columns=EFFECTIVEYIELD");"//document//data//rows//row/@EFFECTIVEYIELD")

G-spread подтягивается с Мосбиржи и показывает разницу в доходности относительно ОФЗ. Считается по формуле:

=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=marketdata_yields&marketdata_yields.columns=GSPREADBP");"//document//data//rows//row/@GSPREADBP")

Дней до погашения и дюрация:

Дни до погашения не смог по формуле из API подтянуть, не ожидал такого))))
В итоге отнимаю от даты погашения сегодняшнюю дату и получаю нужное значение)

Дюрация уже считается нормально по формуле:

=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=marketdata_yields&marketdata_yields.columns=DURATION");"//document//data//rows//row/@DURATION")

Дата следующего купона и периодичность выплат

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

=ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,NEXTCOUPON,COUPONVALUE");"//document//data//rows//row/@NEXTCOUPON")

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

Частота купонных выплат указывается в количестве раз в год и считаются по формуле:

= 365/ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/bonds/boards/»&B2&"/securities/"&A2&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=COUPONPERIOD");"//document//data//rows//row/@COUPONPERIOD")

Из-за того, что в формуле присутствует обычное деление, то иногда могут быть такие числа как 3,4 или 7,1. Исправлю этот недочёт в будущем.

Расчёты под денежный поток

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

Спасибо тем, кто прочитал до конца, надеюсь, что было полезно. Если у вас есть вопросы или замечания, то пишите комментарии, на все отвечу.

Также ставьте лайк и подписывайтесь, это мотивирует!

Мой канал в телеграм, где я пишу больше всего постов и публикую таблицы: https://t.me/filippovich_money