Калькулятор доходности облигаций эксель

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 есть два инструмента, немного отличающиеся результативностью.

Самый простой инструмент — функция ДОХОД, у неё 7 аргументов:

• дата покупки облигации;
• дата погашения;
• процентная ставка купонных выплат в %;
• текущая цена покупки («грязная» цена), приведённая к 100% от номинала (если облигация сейчас стоит 1078 руб. с учётом НКД, делим на 10, чтобы получилось 107,8);
• цена погашения (обычно равна номиналу, если нет амортизации или оферты с особыми условиями);
• базис — способ вычисления дня. В формуле использован фактический базис (один день равен календарному), подробнее о вариантах базиса смотрите в справке Excel.

Скачайте файл с примером: doh_obl_eff. В ячейке С24 рассчитана доходность облигации ОФЗ-26218-ПД при покупке на дату 25.05.2018. Формула имеет вид: «=ДОХОД(СЕГОДНЯ();C6;C9;C15/10;100;2;1)»:

(Исходные данные для расчёта можно взять на специализированных сайтах типа rusbonds.ru, cbonds.info, bonds.finam.ru и многих других).

Как видим, эффективная доходность выше простой, как и ожидалось.

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

В файле примера справа создана таблица финансовых потоков. Первая строчка в ней — операция покупки облигации по «грязной» цене, сумма обязательно должна быть отрицательным значением. Далее перечислены операции по выплате купонов, данные также скопированы с сайта rusbonds, за исключением уже выплаченных купонов. Если по облигации предусмотрена амортизация (частичное погашение номинала), то эти операции также нужно будет отразить в таблице. Самая последняя операция — погашение облигации. Ещё ниже рассчитана прибыль как сумма всех финансовых потоков (платежей и поступлений).

В ячейке G37 рассчитана эффективная доходность облигации к погашению с помощью функции ЧИСТВНДОХ. Как видим, формула имеет очень простой вид: «=ЧИСТВНДОХ(G4:G34;F4:F34;8%)» В ней три аргумента: диапазон дат операций, суммы операций и третий необязательный аргумент — ориентировочное значение ответа, для облегчения работы алгоритма Excel.

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

Скачать пример расчёта эффективной доходности облигации ОФЗ: doh_obl_eff

Отметим, что для малого горизонта инвестирования (до 3 лет) рассчитывать эффективную доходность облигации — не очень информативное занятие, так как реинвестировать купонный доход в эту же ценную бумагу вряд ли получится. Зачастую нагляднее рассчитывать простую эффективность облигации. Пример расчёта простой эффективность облигации ОФЗ в Excel был рассмотрен нами ранее. Смотрите статью: Расчет простой доходности облигации к погашению

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Учет налогов

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

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

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

UPDATE 23.01.2020

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

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

Источник

Облигационный калькулятор частного инвестора

Простой инструмент для вычислений (в excel)

Работа с облигациями сопряжена с нечастыми, несложными, но весьма кропотливыми вычислениями. Каждый раз, когда требуется совершить операцию с облигацией (будем говорить о покупке), необходимо определить, насколько выгодной она будет. Например, если облигация продаётся по 103%, ставка купона — 12% годовых и до погашения три года, то такая облигация может быть выгодным вложением. А если облигация продаётся по 103% со ставкой купона 7% годовых и до погашения осталось несколько дней, то такие вложения приведут к потере денег. Для определения того, выгодной будет покупка или нет, я ориентируюсь на простую доходность к погашению. Обсуждение того, какие виды доходностей облигаций бывают и почему я использую именно эту, оставим за пределами данной статьи. Далее по тексту, если не оговорено иное, я подразумеваю простую доходность к погашению и облигации, номинированные в рублях.

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

Будущие выплаты в рублях можно определить математически, т.к. номинал облигации и ставка купона заранее известны, либо воспользоваться информационными сервисами наподобие Rusbonds.ru. Вычисление суммы будущих выплат в рублях может осложниться тем, что у многих облигаций ставка купона переменная, а некоторые выпуски подразумевают амортизацию долга. Использование информационных сервисов решает эту проблему, но добавляет сложностей в обработке результатов.

В частности, Rusbonds.ru ежедневно для каждого выпуска публикует сведения о доходности к погашению — как простой, так и эффективной. Проблема в том, что на эти данные нельзя полностью положиться, т.к. ликвидность облигаций часто бывает невысокой, и не всегда доступны те котировки, на основании которых Rusbonds.ru вычисляет доходность. Теоретически, можно настроить терминал так, чтобы он показывал доходность облигации для каждой заявки в очереди. Но QUIK показывает эффективную доходность к погашению, а это не то, что мне нужно. Эффективная и простая доходности к погашению коррелируют, хотя могут существенно отличаться друг от друга. В некоторых случаях эта разница может доходить до 150 базисных пунктов.

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

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

Рассчитать доходность можно в двух режимах — по «чистой» и по «грязной» цене. Теоретически, наиболее правильно считать по «грязной» цене, т.к. наличие НКД увеличивает стоимость покупки облигации и снижает доходность. На практике НКД слабо влияет на доходность. Мой облигационный калькулятор показывает, что даже если вы покупаете облигацию в последний день купонного периода, то доходность вашей покупки будет максимум на пять базисных пунктов отличаться от доходности, которую вы бы получили, если бы покупали облигацию с нулевым НКД. В большинстве случаев влияние НКД ограничивается двумя-тремя базисными пунктами. Хотя при работе с облигациями каждая мелочь имеет значение, для вас как частного инвестора особой роли играть не будет, получите вы доходность, например, 9,72% годовых или 9,74% годовых. В свою очередь, размер премии/дисконта к номиналу оказывает решающее значение на итоговую доходность.

Справка по облигационному калькулятору

• Поле «Текущая дата» менять вручную не рекомендуется. Значение текущей даты подставляется автоматически.
• «Дата погашения» вводится в формате «дд.мм.гг» (без кавычек), как показано на изображении.
• «Цена» — цена облигации в процентах от номинала. В это поле внесите ту цену, по которой планируете покупать облигацию.
• «Ставка купона» — в процентах годовых, знак «%» указывать не надо.
• При расчётах по «грязной» цене «Номинал» указывается в рублях, «НКД» — в рублях.
• Когда введёте все данные в поле «Доходность», увидите простую доходность к погашению для вашей цены.
• На надписи светло-серого цвета в левой части калькулятора не обращайте внимания, считайте их отладочной информацией.

Понравилась статья? Поделитесь ею в соцсетях, чтобы рассказать другим!

А также подписывайтесь на Открытый журнал , чтобы не пропустить новые публикации на канале!

Источник