Excel формула для расчета доходности облигаций

Excel для финансиста

Поиск на сайте

Расчет простой доходности облигации к погашению

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

Считаю, что простую доходность облигации уместно использовать, если выбранный срок инвестирования в облигацию невысок (например, до 2 лет) и сумма инвестирования невелика. В таком случае полученного купонного дохода просто не хватит для реинвестирования, и эффективную доходность считать нет смысла.

В данном примере разберём расчёт простой доходности облигации к погашению на примере одной из популярных государственных облигаций (ОФЗ) ОФЗ-26218-ПД. Расчёт других облигаций (муниципальных и корпоративных) ничем принципиально не отличается от расчёта доходности ОФЗ.

Пример представлен для случая покупки ОФЗ через брокерскую контору, принцип расчёта доходности так называемых «народных ОФЗ«, приобретаемых через банки, немного другой (есть дополнительные комиссии и условия досрочного возврата) и будет рассмотрен отдельно.

Для расчёта необходимо знать следующие данные:

• номинал облигации (с учётом прошедшей амортизации),
• дату погашения облигации (в этом простом примере не будем рассматривать доходность к оферте),
• ставка купонов (в % годовых),
• сколько купонов в год выплачивается (при равномерной выплате),
• дату последнего выплаченного купона,
• текущая рыночная цена (в % от номинала).

Всю эту информацию можно найти на специализированных сайтах типа rusbonds.ru, cbonds.info, bonds.finam.ru и многих других.

Итак, расчёт простой доходности облигации к погашению ОФЗ-26218-ПД. Это облигация подходит для простого расчёта, так как есть постоянная доходность (размер купона не меняется) и нет амортизации (то есть номинал неизменен). Для случаев амортизируемых облигаций и переменных купонов лучше использовать другие способы, которые рассмотрим отдельно.

В первых строках размещена общая информация об облигации. В ячейке С7 рассчитывается срок до погашения простой формулой «=C6-СЕГОДНЯ()«.

Обратите внимание, что пример сохранён 25.05.2017, в другой день все расчёты будут иными.

В строке 11 рассчитывается число купонов для погашения формулой «=ОКРУГЛВНИЗ(C7/365;0)*C10+1«: вычисляем количество полных лет до погашения, умножаем на количество купонов в год и добавляем 1 (так как последний купон выплачивается в момент погашения).

В строке 13 рассчитывается накопленный купонный доход (НКД) формулой «=(СЕГОДНЯ()-C12)/365*C9*C5«: он зависит от текущей даты, даты последней выплаты купона, купонной доходности и номинала.

С помощью рассчитанного НКД и цены, по которой облигация торгуется на рынке («чистой цены»), рассчитывается так называемая «грязная цена» — стоимость, которую необходимо заплатить за покупку облигации (без учёта брокерских комиссий). Это сумма инвестиций.

В строках 17-20 рассчитывается доход от инвестирования в облигацию ОФЗ, равный доходу от погашения облигации плюс купонный доход за всё время инвестирования.

В строке 22 рассчитана прибыль, равная разности дохода и сумме инвестиций. В строке 23 — она же в процентном виде. Видим, что за время инвестирования сумма инвестиций более чем удвоилась, но срок очень долгий (14 лет) и простая доходность к погашению составила всего лишь 7,35%.

Эту таблицу можно использовать для расчёта простой доходности к погашению любых облигаций, которые имеют фиксированный купон и не предусматривают амортизацию номинала. Для более сложных случаев стоит составить таблицу платежей и поступлений (финансовых потоков) и использовать функцию Excel ЧИСТВНДОХ, как это сделано в следующем примере расчёта эффективной доходности облигации ОФЗ, смотрите соответствующую статью Расчёт эффективной доходности облигации к погашению.

Скачать пример расчёта простой доходности облигации ОФЗ к погашению : doh_obl_prost

Источник

Пример функции ДОХОД для анализа доходности инвестиций в Excel

Финансовые показатели облигаций могут быстро рассчитываться в Excel. Для этого используется функция ДОХОД. С ее помощью вычисляется доходность ценных бумаг, по которым периодически выплачиваются проценты (дивиденды).

Как работает функция ДОХОД в Excel?

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

Как рассчитать доходность облигаций в Excel? Для решения данной задачи используется функция ДОХОД. Как и любая другая функция, ее синтаксис состоит из имени и аргументов. В строке формул сначала ставится знак равенства, после прописывается имя функции, а затем заполняем ее аргументы входящими значениями всех необходимых показателей.

Всего аргументов у сложной функции 7. Из них 6 обязательных для заполнения:

1. Дата_согл. Расчетный день, когда облигация продана. Дата, когда долговые обязательства переданы покупателю. Не стоит путать ее с датой выпуска облигации.
2. Дата_вступл_в_силу. День, когда заем возвращается покупателю. Это срок погашения облигации, когда истекает срок ее действия.
3. Ставка. Годовая ставка процентов по облигации. Процент, который получает покупатель ценных бумаг.
4. Цена. Показатель определяет цену облигации на 100 рублей номинала.
5. Погашение. Стоимость, по которой выкупаются ценные бумаги.
6. Частота. Число выплат по облигации в год. Может быть равна 1, 2 или 4.
7. Базис. Этот аргумент не является обязательным. Поэтому он заключается в квадратные скобки. Он определяет способ вычисления дней.

Существует 5 видов базиса:

• — 0 – Американский 30/360;
• — 1 – факт/факт;
• — 2 – факт/360;
• — 3 – факт/365;
• — 4 – Европейский 30/360.

Аргументы всегда берутся в круглые скобки. Все они перечисляются по порядку. Между ними ставится знак «;».

Синтаксис функции прописывается следующим образом:

=ДОХОД(дата_согл; дата_вступл_в_силу; ставка; цена; погашение; частота; [базис])

Обязательно учитывать то, что все даты в Excel записываются в виде последовательных чисел. Недопустимо использование текстового формата. За 1 принято 1 января 1900 года. Все последующие даты просто прибавляют число прошедших дней. Поэтому все значения рассчитываются с помощью функции «ДАТА».

Результатом применения функции является определение прибыльности облигаций. Программа может выдать не результат, а знак ошибки #ЧИСЛО! Это происходит в нескольких случаях:

• введены даты без использования функции «ДАТА»;
• значение ставки меньше нуля;
• указана цена меньше или равная нулю;
• погашение указано меньше или равно нулю;
• базис меньше нуля или больше четырех.

Примеры использования функции ДОХОД для анализа облигаций

Для вычисления доходности ценных бумаг нужно ввести в первый столбик таблицы все аргументы функции.

Пример 1. Необходимо вычислить доходность облигаций при следующих условиях:

1. Облигации были проданы 15 февраля 2008 года.
2. Срок погашения – 15 ноября 2016.
3. Ставка по купонам определена 5,75%.
4. Цена составляет 95.04 номиналов.
5. Выкупная стоимость — 100 номиналов.
6. Проценты выплачиваются раз в полгода.
7. Значение базиса – 0 (30/360).

Последовательно вводите все значения заполняя таблицу с данными:

В ячейку B9 вводите данные для аргументов функции ДОХОД. Формула будет выглядеть так:

Доходность облигаций составляет 4,26%.

Можно вводить формулу с клавиатуры или указывать соответствующие ячейки мышью. При этом прописать имя функции нужно вручную. Затем откройте скобку и последовательно через «пробел» укажите все ячейки, которые должны быть учтены.

Пример 2. Предприятие продало облигации 14 июля 2015. Установлен небольшой срок их погашения 14 июля 2017. Предлагается ставка 6%. Цена бумаг 97,4 номинальных значений. Стоимость для выкупа – 107 номиналов. Выплата процентов проводится 4 раза в год. Базис 2 – (48/360).

Для вычисления прибыльности облигации введите данные в таблицу по принципу, который стал понятен из примера 1:

В итоговой ячейке B9 приписываем функцию:

Это обозначает, что расчет доходности к погашению облигаций составляет (6,16% х 2года)=12,32%.

Пример 3. Ценные бумаги проданы 2 августа 2004. Погашение произошло 13 мая 2018. Купонная ставка по ним – 5,3%. Цена бумаг – 93 номинала. Стоимость для выкупа – 104 номинала. Проценты начисляются раз в год. Базис – 0 (30/360).

По аналогии составляем таблицу, используя функцию =ДОХОД():

Прибыльность акций оценена в 3,52%.

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

Источник

Расчет доходности к погашению для облигаций в EXCEL

Довольно часто случаются ситуации, когда доходность к погашению и другие важные параметры облигации необходимо рассчитать самостоятельно. Встроенные функции EXCEL позволяют это сделать довольно легко. Единственное, что необходимо знать – это данные о будущих выплатах купонов и предполагаемые условия сделки.

Получаем данные

Информацию о купонных выплатах по российским облигациям (и еврооблигациям) можно получить на сайте rusbonds.ru (необходимо зарегистрироваться).

Из таблицы купонов нужны только даты и суммы выплат.

Для прогнозирования доходности к погашению также потребуется:

• Цена покупки (обычно выражается в процентах от номинала)
• НКД (накопленный купонный доход)
• Номинал облигации
• Брокерская комиссия

Калькулятор доходности к погашению в EXCEL

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

В примере использованы данные еврооблигации Московского кредитного банка со сроком погашения в 2024 году (тикер: CBOM-24).

В EXCEL для этого существует довольно удобная функция XIRR (ЧИСТВНДОХ), которая позволяет быстро и просто считать доходность к погашению. Функция использует две колонки данных: колонка «Даты» и колонка «Денежный поток».

Доходность к погашению (Yield to maturity, YTM) – это IRR (ВНД) денежного потока инвестора, покупающего облигацию. При этом предполагается, что облигация держится до погашения.

Кроме доходности к погашению калькулятор считает:

• Купонную доходность
• Доходность при погашении (ценовая доходность)
• Модифицированную доходность (сумма купонной доходности и ценовой доходностей)
• Дюрацию
• Модифицированную дюрацию

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

Учет налогов

Для некоторых облигаций предусмотрен налог на купон. Часто инвестор должен заплатить НДФЛ при погашении. Шаблон позволяет учесть такие ситуации. Для этого выберете, платится ли НДФЛ за купон и платится ли НДФЛ при погашении:

Самостоятельное изменение калькулятора

Мы постарались сделать калькулятор максимально простым. Поэтому в нем не учитываются другие более сложные ситуации, связанные с облигациями. Например, калькулятор не учитывает возможный НДФЛ от валютной переоценки еврооблигаций. Но подобные изменения в калькулятор можно вносить самостоятельно, если вы имеете опыт работы с EXCEL. Аналогичные изменения можно внести для другой популярной ситуации — учета поступлений налоговых вычетов в ИИС типа «А».

UPDATE 23.01.2020

Добавлен калькулятор доходности к погашению для еврооблигаций с учетом валютной переоценки и соответствующего налога.

В новой версии калькулятора IRR считается не только в валюте, но и в рублях с учетом курсовой разницы и налога на валютную переоценку. Для того, чтобы воспользоваться этими возможностями, необходимо вставить курсы валюты в колонку «Курс ЦБ РФ». Курсы могут быть историческими данными, если вы проверяете реальный IRR уже погашенной облигации, или прогнозными, если необходимо определить доходность к погашению при каком-то сценарии изменения курса валюты.

Источник

Расчет доходности облигаций. Делюсь таблицей EXCEL

Каждый будний день я рассказываю о расчете доходности облигаций к погашению. Все статьи содержат скриншоты таблиц с собранной мной информацией. Сегодня я решил поделиться ей со всеми желающими. С помощью моего EXCEL-я можно облегчить свой труд по вычислению доходности бондов.

Пример таблицы представлен на скриншоте. Под ним прикреплена кнопка для оплаты. Детям на конфеты 200 рублей и Вам не дорого. После оплаты Вы получите ссылку для скачивания файла. Эту идею мне подсказали мои друзья. На моей электронной почте каждый день кто-то просит прислать этот файл. Я решил автоматизировать процесс и брать за это символическую плату.

Оплата проводится с помощью сервиса Яндекс.Деньги. Вы можете самостоятельно выбрать удобный способ оплаты (Карта или Я.Деньги). Считайте это Вашей первой удачной инвестицией в знания. Если что-то не получится Вы всегда сможете мне позвонить в Вибер. Номер внутри.

Несколько слов о методике работы созданного мной файла. К заполнению обязательны все ячейки залитые серым цветом. Рассмотрим их более подробно.

Эмитент — Произвольное название организации выпустившей облигацию.

Название бумаги — Произвольное название как в программе QUIK или в инвестиционном приложении от Вашего брокера.

ISIN — Номер ценной бумаги. Его можно найти в программе QUIK или в инвестиционном приложении от Вашего брокера. Он необходим для удобства дальнейшего поиска, так как названия бумаг в разных программах могут отличаться.

Муниципальная или ОФЗ (Да), корпоративная (Нет) — необходимо ответить на этот вопрос Да или Нет для расчета налоговой базы. Муниципальные облигации и ОФЗ не облагаются НДФЛ, а корпоративные попадают под начисление налога.

Номинал — Обязательное указывать для расчета доходности к погашению. Эту информацию можно найти в торговых системах или в программах, которые выдал брокер.

Дней до погашения — необходимо заполнить данное поле для расчета процентов.

Год выпуска — поле используется для определения налоговой базы. Корпоративные облигации выпущенные с 2017 по 2020 год не облагаются налогом.

Лучшая цена в стакане в % — укажите лучшую цену продажи в стакане по выбранному инструменту. Информацию можно посмотреть в программе QUIK или в инвестиционном приложении от вашего брокера.

НКД — Накопленный купонный доход. Он ежедневно меняется, поэтому перед началом расчетов заново уточняйте его размер.

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

Период купона, дней — Укажите периодичность выплаты купона. Например, 30, 91, 180 или 365 дней. Эта информация также доступна в торговых системах или программах выданные вашим брокером.

Ставка купона, % — Эта информация необходима для определения налога на купонный доход 13% или 35%. Зная её значение мы можем более точно посчитать доходность всего инструмента.

Ключевая ставка ЦБ РФ, % — она меняется в среднем один раз в месяц и публикуется на сайте Центрального Банка РФ. Информация необходима для оценки соблюдения правила КС+5%.

После заполнения всех ячеек можно увидеть результат вычислений. Он будет выражен в годовом проценте в строке «ПЛАНОВАЯ ДОХОДНОСТЬ К ПОГАШЕНИЮ». Обращаю Ваше внимание, если Вами будет получен отрицательный результат воздержитесь от покупки такой облигации.

Для ответа на Ваши вопросы по работе с моим калькулятором почитайте предыдущие темы в этом блоге. При возникновении технических трудностей, пожалуйста, сразу напишите мне на электронную почту investorp@bk.ru или в VIBER указанные внутри файла. Я с удовольствием отвечу на Ваши вопросы. Мы можем вместе посчитать несколько интересных вариантов бумаг.

Как самостоятельно посчитать доходность облигаций с помощью калькулятора я рассказал здесь >>>

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

Источник