Как рассчитать доходность облигации к погашению в excel

Обновлено: 02.07.2024

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы получить исторические котировки автоматизированным способом, а не выписывать их вручную (массив данных может быть весьма объёмным), следует обратиться к перечню авторизованных Московской биржей распространителей информации, который представлен на сайте биржи.

Следующий шаг — выбрать интересующие активы (в нашем примере — «МосБиржа Акции и ПИФы») и поместить их в раздел «Выбранные тикеры» (в примере мы рассмотрим Сбербанк, «НЛМК» АО и Yandex cla). Далее выбираем таймфрейм и временной интервал (для удобства визуализации процесса мы выбрали месячный таймфрейм и интервал, равный одному году), далее в поле «Формат» следует выбрать «Текстовый» и «Все тикеры в одном файле», после чего вводим «Имя файла» — «Портфель. txt», выбираем в разделе «Разделитель полей» пункт «Табуляция», выбираем в поле «Десятичный разделитель» пункт «Запятая», вводим в поле «Формат даты/времени» «dd/MM/yy, hhmmss», отмечаем галочкой пункт «Добавить заголовок файла», вводим «Формат записи» «TICKER, PER, DATE, TIME, CLOSE, VOLUME» и нажимаем на клавишу «Получить данные».

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

Полученные данные следует скопировать в программу MS Excel для проведения дальнейших расчётов.

Далее приступим к расчёту доходности представленных активов за период относительно предыдущего. Для этого вводим в ячейку свободного столбца формулу расчёта доходности, в которой мы из цены закрытия периода вычитаем цену закрытия предыдущего периода, делим данную разность на цену закрытия предыдущего периода и умножаем частное от деления на 100 для приведения к процентной форме. Для этого мы в ячейку G3 вводим формулу «=(E3-E2)/E2*100» для вычисления доходности за месяц по бумаге Yandex cla.

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

Следующий этап — вычислить среднюю годовую доходность за выбранный промежуток времени. Для выполнения поставленной задачи следует в свободную ячейку ввести формулу, вычисляющую среднее значение от сформированного диапазона, и умножить на 12 (так как мы анализировали средние доходности за 12 месяцев). В нашем примере мы ввели в ячейку G15 формулу «=СРЗНАЧ(G3:G14)*12» и получили среднюю годовую доходность за анализируемый период.

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

В результате мы получили среднюю доходность в годовых процентах за анализируемый временной период по искомым бумагам. Она составила 39,68% по Yandex cla, 28,10% по НЛМК и 36% по Сбербанку.

После получения годовой доходности по искомым бумагам можно приступать к вычислению доходности портфеля, состоящего из данных бумаг. Для примера предположим, что мы решили сформировать портфель в следующих процентных долях: 40% — «Яндекс», 20% — НЛМК и 20% — Сбербанк. В общем виде доходность портфеля равна сумме произведений доходностей входящих в него бумаг на их процентные доли. Следовательно, расчёт доходности рассматриваемого портфеля можно представить как 0,4*39,68+0,2*26,10+0,2*36. Для произведения представленных вычислений в Excel в ячейку G18 (или в любую другую) введём формулу «=G15*0,4+N15*0,2+U15*0,2». Таким образом, доходность портфеля составит 28,29% годовых.

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

На какую доходность стоит ориентироваться начинающему инвестору? Про связь доходности с выбором инвестиционной стратегии Как я рассчитываю будущую дивидендную доходность своего портфеля Дивиденды — один из важнейших элементов инвестиционной стратегии Как рассчитать доходность облигации при инвестировании И как правильно выбирать облигации на основании будущей доходности Формула для расчёта доходности облигаций к погашению 12 российских эмитентов, акции которых могут показать наибольший рост в следующем году Сравниваем показатели компаний и ищем наиболее перспективные акции Положение компании на мировом рынке алюминия и долгосрочные тенденции спроса на этот металл

Москва, ул. Летниковская, д. 2, стр. 4

Будьте в курсе новых публикаций!
Подпишитесь на дайджест «Открытого журнала» и получайте подборку публикаций за неделю.

Размещённые в настоящем разделе сайта публикации носят исключительно ознакомительный характер, представленная в них информация не является гарантией и/или обещанием эффективности деятельности (доходности вложений) в будущем. Информация в статьях выражает лишь мнение автора (коллектива авторов) по тому или иному вопросу и не может рассматриваться как прямое руководство к действию или как официальная позиция/рекомендация АО «Открытие Брокер». АО «Открытие Брокер» не несёт ответственности за использование информации, содержащейся в публикациях, а также за возможные убытки от любых сделок с активами, совершённых на основании данных, содержащихся в публикациях. 18+

АО «Открытие Брокер» (бренд «Открытие Инвестиции»), лицензия профессионального участника рынка ценных бумаг на осуществление брокерской деятельности № 045-06097-100000, выдана ФКЦБ России 28.06.2002 (без ограничения срока действия).

ООО УК «ОТКРЫТИЕ». Лицензия № 21-000-1-00048 от 11 апреля 2001 г. на осуществление деятельности по управлению инвестиционными фондами, паевыми инвестиционными фондами и негосударственными пенсионными фондами, выданная ФКЦБ России, без ограничения срока действия. Лицензия профессионального участника рынка ценных бумаг №045-07524-001000 от 23 марта 2004 г. на осуществление деятельности по управлению ценными бумагами, выданная ФКЦБ России, без ограничения срока действия.

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

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

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

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

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

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

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

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

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

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

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

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

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

Учет налогов

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

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

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

UPDATE 23.01.2020

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

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

Файлы для скачивания

Калькулятор доходности к погашению облигаций в EXCEL
Файл: ytm_calculator.xlsx
Размер: 36439 байт

Калькулятор доходности к погашению для еврооблигаций в EXCEL
Файл: ytm_eurobonds.xlsx
Размер: 58522 байт

Для скачивания файлов необходимо зарегистрироваться или авторизоваться

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

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

Подготовка данных для облигационного калькулятора

На первый взгляд может показаться, что работа усложнилась, ведь нужно заполнять в несколько раз больше ячеек, чем в предыдущем калькуляторе. На самом деле — нет, исходные данные можно взять из терминала QUIK. Для этого в терминале создайте таблицу «Текущие торги», как показано на рисунке 2. Желательно, чтобы порядок столбцов соответствовал изображению. Добавьте в таблицу интересующие вас выпуски облигаций. Ничто не мешает вам добавить все торгующиеся облигации. Для калькулятора важно лишь заполнение всех полей, чтобы выполнить расчёты. Но лучше, если вы сразу отсеете заведомо неподходящие облигации.

Я, например, поступаю следующим образом. Обычно сразу добавляю все облигации из списков «МБФР: Т+ Облигации» и «МБФР: Т0 облигации». В большинстве случаев меня интересуют облигации со сроками погашения до определённой даты, поэтому я оставляю только их. Следующий этап отсеивания — валюта номинала. Удаляю все облигации, не номинированные в рублях (в столбце «Валюта» не указано SUR для соответствующего выпуска). Последний этап отсеивания — удаление облигаций, по которым нет рыночного предложения: мне нужно покупать облигации, поэтому продавцы должны присутствовать. Если в столбце «Предл.» стоит 0, это означает, что продавцов нет. Эти выпуски надо удалить обязательно, т.к. калькулятор не сможет посчитать по ним доходность. Также стоит отсеять выпуски, где цена предложения слишком низкая, ниже 80–90. Скорее всего, это дефолтные облигации или просто сомнительные эмитенты.

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

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

При необходимости растиражируйте формулы из последних шести столбцов (на рисунке подчёркнуты красным) и/или удалите лишние строки. На этом расчёт можно считать завершённым. Сортируйте и фильтруйте полученные данные так, как вам удобно.

Я обычно удаляю из полученной таблицы выпуски, не удовлетворяющие требованиям по доходности. В моём случае нижняя грань доходности равна ставке ЦБ РФ. Если в итоге остаётся много выпусков, то могу ужесточить требования. Часто хороший выбор остаётся, даже если вы устанавливаете нижнюю грань доходности равной ставке ЦБ, увеличенной на два процентных пункта.

С верхней гранью доходности немного сложнее. Дело в том, что из-за особенностей математических расчётов дефолтные облигации могут показывать чрезвычайно высокую доходность. Такие выпуски лучше сразу удалять из таблицы, чтобы они не мешали. Раньше я отсеивал выпуски облигаций, по которым доходность к погашению превышала двойную ставку ЦБ РФ, т.е. при текущей ставке 7% исключились бы из рассмотрения облигации с доходностью выше 14%. Однако с развитием рынка высокодоходных облигаций (ВДО) я поднял эту плану до 20%. Тем не менее если облигация даёт такую высокую доходность, то это несёт в себе дополнительные риски. Нужно особенно осторожно относиться к покупкам таких облигаций, хотя эмитенты могут быть вполне надёжными.

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

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

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

Как считать доходность?

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

Сложность заключается в том, что большинство подходов к расчету доходности подразумевают простую формулу:

А – полученный доход

В – стартовые инвестиции

Представим себе жизненную ситуацию, когда человек в январе инвестировал 10 000 р, а в декабре – 90 000 р. К концу года на инвестиционном счете оказалось 110 000 р (ценные бумаги выросли в цене). Какова доходность инвестиций? Что на что делить? Если мы возьмем доход в 10 000 р и разделим на сумму всех инвестиций – 100 000 р, то получим очень сложно интерпретируемый результат – 10%. Ведь большую часть срока на счете находилось всего 10 000 р, а остаток добавлен только за месяц до конца года …

Или еще более интересный пример. В январе инвестор положил на брокерский счет 100 000 р, а в декабре забрал с него 90 000 р. К концу года на брокерском счете фигурировала сумма 15 000 р. Если просто сложить пополнения и изъятия получится что суммарная инвестиция равна 100 000 – 90 000 = 10 000 р. Разделив доход на суммарные инвестиции, получим слишком оптимистичные 50%. Очевидно, что так делать нельзя …

Более подробно о теме расчетов доходности без пополнений и изъятий читайте в статье: Правильный расчет среднегодовой доходности в инвестициях

IRR или Внутренняя норма доходности (ВНД)

Одним из самых простых и распространенных способов измерить результативность инвестиций является расчет IRR (Internal Rate of Return, Внутренняя норма доходности). IRR – это не совсем доходность. Формально IRR или Внутренняя норма доходности (ВНД) – это процентная ставка, при которой приведённая стоимость денежных поступлений (списаний) равна размеру исходных инвестиций. IRR очень распространен в бизнесе и финансах. При помощи этой величины считается, например, рентабельность проектов в бизнесе. Аналогично считается доходности к погашению для облигаций. IRR можно считать это своего рода стандартом при измерении результативности.

Еще одно важное преимущество – IRR легко считается в EXCEL и других электронных таблицах.

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

Шаблон для расчета IRR инвестиций в EXCEL

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

Шаблон считает IRR для каждого из периодов инвестиций, и за последние 6 периодов (колонка «IRR за 6 периодов»). Периоды могут быть произвольными: один месяц, один год. Более того, в калькуляторе используется функция XIRR (ЧИСТВНДОХ), которая умеет считать IRR даже для неравных между собой периодов. Это значит, что в колонке «Дата» можно указывать любую дату, а не только начало месяца или, например, конец года. Удобнее всего вносить новые данные каждый раз, когда пополняется портфель или когда происходит изъятие средств. Для интереса можно вносить новые данные чаще, даже когда нет пополнений портфеля. Например можно указывать даты, когда в размере портфеля происходят какие-то значимые изменения или просто с некоторой заданной регулярностью.

Кроме IRR инвестиционного портфеля в шаблоне можно посмотреть общий прирост портфеля (на сколько размер портфеля отличается от объема инвестированных средств).

Учет результатов инвестиций для сложных портфелей

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

Хотя это и не основная функция калькулятора, но его довольно просто можно использовать для расчета доходности к погашению для облигаций. Доходность к погашению для облигаций определяется именно как IRR всего денежного потока.

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

В примере показан прогноз доходности к погашению для облигации с купоном 40 руб (два раза в год) и текущей стоимостью 98% (980 р) и погашением в 2024 году. Предполагается, что облигация держится до погашения. В данном случае имеет релевантность только последнее значение IRR (в момент погашения), так как изменение цены облигации прогнозировать очень сложно. IRR за 6 периодов тоже большого смысла для облигаций не имеет.

Ограничения калькулятора

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

Другие финансовые калькуляторы для EXCEL можно найти разделе Калькуляторы.

Файлы для скачивания

Калькулятор результативности инвестиций в EXCEL
Файл: investment_tracker.xlsx
Размер: 48684 байт

Для скачивания файлов необходимо зарегистрироваться или авторизоваться

Читайте также: