Эффективная доходность облигации excel

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 для финансиста

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

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

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

Считаю, что простую доходность облигации уместно использовать, если выбранный срок инвестирования в облигацию невысок (например, до 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 предлагает ряд функций, используемых для вычисления различных финансовых показателей облигаций. Облигацией называется финансовый инструмент, в котором покупатель выступает в роли заимодателя, а учреждение или государство, выпустившее облигации, — в роли заемщика. Однако многие функции, связанные с облигациями, выходят за рамки материала настоящей книги. В этой статье представлены примеры некоторых наиболее распространенных функций.

Некоторые свойства облигаций являются также и аргументами функций, связанных с облигациями.

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

Оценка облигаций

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

Для примера предположим, что некоторая компания выпустила облигации с номинальной стоимостью 100 долларов, сроком погашения 10 лет и 6%-ной ставкой, выплачиваемой дважды в год.

• Если процентная ставка по кредитам растет, ставка в 6% становится не такой уж и привлекательной. Покупатели уже не захотят платить 100 долларов за облигацию — они будут их покупать за меньшую сумму.
• Если процентная ставка по кредитам падает, ставка в 6% становится привлекательной. В данном случае покупатели готовы платить больше номинальной стоимости.

Функция ЦЕНА вычисляет сумму, которую покупатели потенциально готовы заплатить за облигацию в ожидании определенного дохода. Синтаксис этой функции таков: ЦЕНА(расчетный_день;срок_погашения;ставка;доходность;погашение;частота;базис ). Учитывая приведенные выше факты, инвестор, желающий получить 7,5% годовых, должен использовать следующую формулу для вычисления приемлемой цены облигации, срок погашения которой наступает через 8 лет: =ЦЕНА(СЕГОДНЯ();СЕГОДНЯ()+ДАТА(8;1;0);,06;,075;100;2) .

Результатом этой формулы будет 91,10 долларов — именно такую сумму стоит заплатить за облигацию номиналом в 100 долларов с шестью процентами годовых, чтобы прибыльность составила 7,5%. Каждый год инвестор будет получать 6%*100 долларов, плюс при погашении облигации он получит дополнительную прибыль в 8,90 долларов. Эти два компонента — проценты и дисконт — формируют доходность облигаций. Фактическая дата выпуска облигации может отличаться от даты ее покупки инвестором. Облигации выпускают многие организации, так производитель кондиционеров кватро клима имеет достаточно высокую ликвидность своих ценных бумаг, которая основывается на качестве продукции.

В рассмотренном примере облигации были выпущены за два года до их покупки, следовательно, инвестор будет получать за них проценты только в течение 8 лет. Если же процентная ставка упала с момента выпуска и инвестору достаточно 5,2%, формула слегка изменится: =ЦЕНА(СЕГОДНЯ();СЕГОДНЯ()+ДАТА(8;1;0);,06;,052;100;2) . При этих обстоятельствах инвестор может заплатить за стодолларовую облигацию 105,18 долларов. На рис. 11.17 эти вычисления показаны на рабочем листе.

Вычисление доходности

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

Рис. 1. Использование функции ЦЕНА

Инвестор все еще заинтересован в покупке десятилетней облигации с 6 процентами, выплачиваемыми дважды в год, однако на этот раз он хочет заплатить всего 93,95 долларов за стодолларовую облигацию. Следующая формула вычисляет процентную ставку за восемь лет, оставшихся до даты погашения: =ДОХОД(СЕГОДНЯ();СЕГОДНЯ()+ДАТА(8;1;0);,06;93,95;100;2 ). Если инвестор заплатит за облигацию 93,95 долларов, то получит от своих инвестиций 7%. Если бы он заплатил за облигацию больше ста долларов, то получил бы от инвестиций меньше 6% (рис. 2).

Рис. 2. Когда цена выше номинальной стоимости облигации, прибыльность ниже купона

Источник

Пример функции ДОХОД для анализа доходности инвестиций в 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 удалось всего за несколько минут установить, что самыми прибыльными инвестициями являются облигации из второго примера.

Источник