Рассчитать доходность облигации эксель

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 калькулятором в отдельную статью.

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

Обязательно сохраните ссылку. Она придёт на электронную почту, которую вы укажите при оплате. С её помощью Вы сможете в любое удобное время получить интересующую таблицу. Если возникнут сложности, предложения или вопросы, пожалуйста, оставляйте их на электронную почту investorp@bk.ru. Я с удовольствием на них отвечу! Каждой облигации посвящён свой одноимённый файл.

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

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

Вторая таблица содержит сведения расходах на покупку 1 ценной бумаги. Для автоматического расчета здесь необходимо самостоятельно ввести цену по которой Вы хотите купить данную облигацию и нажать ENTER.

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

Следующий блок посвящён расчету суммы к погашению облигации. Обязательным полем является указание действующей ключевой ставки Центрального банка.

Благодаря ей будет произведена автоматическая оценка налога. Например, 13% или 35%. Затем эксель посчитает прогноз налоговой базы и сумму, которая будет удержана перед купонной выплатой. Подробный расчет можно увидеть в пятой таблице.

Здесь важно отметить один интересный факт. Расчет налога является прогнозом, так как мы не можем знать значение ключевой ставки Банка России в будущем. Анализ производится только на основе имеющейся информации. Снижение или увеличение ключевой ставки оказывает влияние на расчет налоговой базы.

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

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

На листе «Параметры» в каждом калькуляторе представлены исходные данные о предстоящих датах выплаты купонов и амортизации.

Для предварительной оценки каждой конкретной облигации я рекомендую использовать следующие колонки: F — количество купонов, G — дата выплаты, I — периодичность выплаты, L — ставка купона, N — суммы оставшихся купонов, Q — номинал. Остальные колонки являются техническими.

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

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

Надеюсь, что представленная в экселе информация будет для Вас информативной и достаточной для эффективной работы. Желаю Всем нам только удачных инвестиций!

Источник

3 часть. Учим Эксель-робота считать сумму облигаций к погашению.

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

ВНИМАНИЕ! Методика массового анализа ранка долговых финансовых инструментов представлена только для опытных инвесторов. Новичкам её использование строго запрещено! Инвестор должен обладать базовыми навыками работы с формулами в экселе, уметь отличать облигации с постоянным купоном от переменного и понимать принцип расчёта доходности.

На рабочем столе своего компьютера открываем файл «Мониторинг_облигаций.xlsx». Далее запускаем торговую систему Quik. Выгружаем из него данные. Для этого в любом месте таблицы со списком облигаций нажимаем правой кнопкой мыши и выбераем пункт «Вывод через DDE сервер». Через пару минут кликните «Остановить вывод». Произойдёт обновление информации в экселе.

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

Приступим к работе с экселем. Кликаем один раз мышкой по ячейке Q1 и вводим название колонки «Кол-во купонных выплат». Торговая система Квик показывает нам информацию только о сумме ближайшей купонной выплаты. Для расчёта доходности облигации к погашению нам нужно обладать сведениями о количестве предстоящих купонных выплат.

Напомню, второй этап расчёта доходности облигации: (Сумма всех предстоящих купонных выплат с учётом текущего — 13%) + Номинал = Сумма к погашению. Вся методика вычисления опубликована на этой странице моего блога .

Для определения количества предстоящих купонных выплат мы разделим количество дней до погашения на длительность купонного периода. Кликаем мышкой на ячейку Q2. Ставим знак «=». Далее нажимаем на ячейку М2 (дни до погашения). Ставим знак деления / (косая черта). Кликаем на ячейку F2 (длительность купона) и нажимаем Enter для подтверждения ввода.

Формула выглядит так: = M2/F2

Результатом вычислений стало дробное значение, но количество купонов это всегда целое число. Например, 1, 2, 3 и так далее. Нам необходимо округлить полученный результат. Для этого мы воспользуемся функцией (командой) ОКРУГЛВВЕРХ.

Она работает следующим образом =ОКРУГЛВВЕРХ(Число;Количество знаков). Число в нашем примере мы получаем с помощью формулы M2/F2, а округлить его нужно до 0 (нуля) знаков после запятой. Таким образом формула округления полученного результата будет выглядеть так:

Вводим её в ячейку Q2 и нажимаем Enter. Эксель округлил результат до целого числа. Проверим. До погашения облигации осталось 29 дней. Длительность купона 92 дня. Действительно, за этот период мы получим только одну выплату.

Формула работает правильно. Применяем её ко всем последующим строкам. Кликаем один раз на ячейку Q2. Она будет выделена зелёной рамкой. Нажимаем левой кнопкой мыши на нижний левый угол ячейки и продолжая удерживать левую кнопку тянем курсор вниз до конца списка. Затем отпускаем. Произойдёт автоматическое заполнение результатами вычисления созданной нами формулы по всем строкам.

Переходим к следующей колонке. Кликаем один раз на ячейку R1 и вводим название столбца «Сумма всех купонов без налога».

Источник