Расчет доходности к погашению для облигаций в EXCEL
Довольно часто случаются ситуации, когда доходность к погашению и другие важные параметры облигации необходимо рассчитать самостоятельно. Встроенные функции EXCEL позволяют это сделать довольно легко. Единственное, что необходимо знать – это данные о будущих выплатах купонов и предполагаемые условия сделки.
Получаем данные
Информацию о купонных выплатах по российским облигациям (и еврооблигациям) можно получить на сайте rusbonds.ru (необходимо зарегистрироваться).
Из таблицы купонов нужны только даты и суммы выплат.
Для прогнозирования доходности к погашению также потребуется:
- Цена покупки (обычно выражается в процентах от номинала)
- НКД (накопленный купонный доход)
- Номинал облигации
- Брокерская комиссия
Калькулятор доходности к погашению в EXCEL
Представленный калькулятор позволяет довольно просто посчитать доходность к погашению для облигации в зависимости от различных условий приобретения ценной бумаги.
В примере использованы данные еврооблигации Московского кредитного банка со сроком погашения в 2024 году (тикер: CBOM-24).
В EXCEL для этого существует довольно удобная функция XIRR (ЧИСТВНДОХ), которая позволяет быстро и просто считать доходность к погашению. Функция использует две колонки данных: колонка «Даты» и колонка «Денежный поток».
Доходность к погашению (Yield to maturity, YTM) – это IRR (ВНД) денежного потока инвестора, покупающего облигацию. При этом предполагается, что облигация держится до погашения.
Кроме доходности к погашению калькулятор считает:
- Купонную доходность
- Доходность при погашении (ценовая доходность)
- Модифицированную доходность (сумма купонной доходности и ценовой доходностей)
- Дюрацию
- Модифицированную дюрацию
Все параметры рассчитываются в валюте номинала облигации, поэтому шаблон может быть использован для еврооблигаций (доходности будут в валюте).
Учет налогов
Для некоторых облигаций предусмотрен налог на купон. Часто инвестор должен заплатить НДФЛ при погашении. Шаблон позволяет учесть такие ситуации. Для этого выберете, платится ли НДФЛ за купон и платится ли НДФЛ при погашении:
Самостоятельное изменение калькулятора
Мы постарались сделать калькулятор максимально простым. Поэтому в нем не учитываются другие более сложные ситуации, связанные с облигациями. Например, калькулятор не учитывает возможный НДФЛ от валютной переоценки еврооблигаций. Но подобные изменения в калькулятор можно вносить самостоятельно, если вы имеете опыт работы с EXCEL. Аналогичные изменения можно внести для другой популярной ситуации - учета поступлений налоговых вычетов в ИИС типа "А".
UPDATE 23.01.2020
Добавлен калькулятор доходности к погашению для еврооблигаций с учетом валютной переоценки и соответствующего налога.
В новой версии калькулятора IRR считается не только в валюте, но и в рублях с учетом курсовой разницы и налога на валютную переоценку. Для того, чтобы воспользоваться этими возможностями, необходимо вставить курсы валюты в колонку "Курс ЦБ РФ". Курсы могут быть историческими данными, если вы проверяете реальный IRR уже погашенной облигации, или прогнозными, если необходимо определить доходность к погашению при каком-то сценарии изменения курса валюты.
Файлы для скачивания
Калькулятор доходности к погашению облигаций в EXCEL
Размер: 36439 байт
Калькулятор доходности к погашению для еврооблигаций в EXCEL
Размер: 40573 байт
Для скачивания файлов необходимо зарегистрироваться или авторизоваться
Похожие материалы:
- Еврооблигации и налоги
- Расчет размера пенсионных накоплений в EXCEL
- Расчет результативности инвестиций в EXCEL
- Ребалансировка портфеля в EXCEL
- Доходность к погашению облигации и реинвестирование купонов
- Калькулятор досрочного погашения кредитов
- Методы досрочного погашения кредитов: Лавина
- Калькулятор доходности к погашению облигаций для Google Таблиц
- Методы досрочного погашения кредитов: Снежный ком
1. Как он будет выглядеть, если я периодически докупаю и продаю часть облигаций? Это более реальная ситуация, в которой нужно вычислить доходность инвестиций в данную облигацию.
Если я купил N1 облигаций, потом докупил N2, потом продал N3 и т.д., то я эти суммы могу поставить в вашу таблицу (соблюдая "+" и "-"), функция ЧИСТВНДОХ отработает нормально? А как быть с купонами, по какой простой формуле подсчитать, какое число купонов на какую дату имеется, чтобы ручками границы СУММ каждый раз не двигать?
2. Как изменится ваш калькулятор, если у меня несколько облигаций и я не хочу для каждой отдельно считать. У меня один файл, в котором я по датам фиксирую покупку всех моих облигаций и выплаты купонов по ним. Я не пойму, как нужно доработать функцию ЧИСТВНДОХ, чтобы внутри она в столбцах дат и денежных потоков выбирала только для указанной облигации (название каждой облигации в каждой строке есть), как фильтр правильно поставить?
Управление облигационным портфелем — это отдельная тема. Простеньким шаблоном в EXCEL здесь всех вопросов не решить. Для портфеля нет такого понятия, как «доходность к погашению», например. Хотя можно считать среднюю доходность по купону и усредненную доходность к погашению, входящих в портфель ценных бумаг. Некоторые из этих вопросов будут обсуждаться на вебинаре 14 января.
В будущем на Финариуме планируем курс по управлению портфелем облигаций.
1. Калькулятор считает доходность с учетом реинвестирования или без? Если без, то как его нужно изменить, чтобы было с реинвестированием? Если с учетом реинвестирования, то как его изменить, чтобы было без реинвестирования?
2. В чем смысл данного калькулятора, если доходность к погашению транслируется биржей? Чтобы узнать текущую цену, вам же все равно нужно смотреть сайт биржи или квик.
Калькулятор полезен в двух случаях:
1. Когда в QUIK (или другом терминале) не считает доходность к погашению самостоятельно. Например, для многих еврооблигаций доходность по неизвестным причинам не считается.
2. Для проверки различных вариантов покупки с разными условиями сделок.
В случае с облигациями:
Коли в таблице явно указаны купоны, то значит они выводятся как денежные средства. Получается, что в вашем калькуляторе IRR это доходность без реинвестирования.
Если бы было реинвестирование, то «купонов не было бы», они бы шли на рост стоимости, как в ETF на облигации — купоны не платят, они в конечной стоимости сидят.
В вашем калькуляторе получается, что после каждого купона, его нужно реинвестировать, т.е. должен быть отрицательный денежный поток, который увеличивает количество облигаций, в итоге каждая следующая купонная выплата будет больше, т.к. количество облигаций стало больше. В конце сумма погашения тоже будет больше, т.к. облигаций стало больше.
Инвестор вкладывает в проект 1000 руб. на 3 года.
Проект 1.
Вложение -1000 руб, каждый год в конце года он получает 400 руб. В формуле IRR будет 4 денежных потока: -1000, 400, 400, 400.
400 руб. с проекта он получает кэшем каждый год. Вывод: купоны по облигациям в вашей таблице — это кэш на руки.
IRR = 9.7%
Проект 2.
Тот же, но 1200 руб. инвестор получает в конце года. В формуле IRR 2 потока: -1000, 1200.
IRR = 6.27%.
Проект 1 с реинвестированием.
Инвестор каждый год получаемые 400 руб. обратно инвестирует в проект 1. Будет 2 потока: -1000, Х руб.
Этот Х мы ищем интерактивно таким образом, чтобы IRR стал равным 9.7%.
Получаем Х = 1320 руб., т.е. реинвестировав инвестор получил на 120 руб. больше.
Общий вывод — нет в вашем файле реинвестирования при расчете IRR.
Прогнозировать дальнейшие значения на основе изменения ключевой ставки (КС) можно, т.к. процентов на 90% ставка RUONIA зависит именно от КС и ликвидности в банковском секторе. Ликвидность прогнозировать сложно. Но про ставку на ближайшее время все более или менее понятно. Берите ставки RUONIA за последние месяцы, считайте среднее арифметическое… и в общем-то всё (не забудьте добавить 1,2% и посмотреть дельту, связанную с ликвидностью).
1. IRR — это усредненная годовая доходность или абсолютная доходность между двумя крайними датами? Например, есть ли срок до погашения более года, скажем 2 года и 8 мес, то я получаю по IRR среднюю годовую доходность?
2. Если по бумаге осталось до погашения 4 мес, то IRR — это также будет приведенная годовая доходность или доходность за 4 мес.?
Есть формула MIRR, но в ней нет дат, она считает за равные промежутки.
У вас в формуле комиссия учтена только в сумме инвертирования. С купонов вы вычитаете только НДФЛ, а почему не вычитаете также и комиссию?
Я понимаю откуда постоянно берутся подобные вопросы. Люди которые не слишком понимают DCF хотят вычислить «доходность». Но если в инвестициях появляются пополнения и снятия со счета, доходность как таковая не определена. Поэтому в облигациях считают сразу несколько параметров: доходность к погашению, купонную доходность и т.п.
Если же вы все купоны реинвестируете, то пополнений и снятий нет, и IRR считать нет необходимости. Достаточно разделить конечную сумму на начальную и вычесть единицу — получится доходность.
— а моя доходность от покупки данной облигации измениться? Просьба пояснить.
Мои рассуждения: Расчет IRR по XIRR предполагает реинвестирование купонов по ставке IRR, т.е. я получив купоны должен их сразу же куда-то реинвестировать по ставке IRR. Только в этом случае я от купленной облигации получу расчетную доходность к погашению, иначе моя итоговая доходность от покупки облигации окажется ниже.
Я рассуждаю правильно, или в чем ошибка?
Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /var/www/rostsber/data/www/rostsber.ru/core/components/jevix/model/jevix/jevix.class.php on line 121
Здравствуйте. При изменений ставки НДФЛ на 0% что-то сбивается и формулы более не работают. Пробовал разобраться в самих формулах, не понял что такое purchase. Возможно ли что Excel не последней версии и из-за этого формулы сбиваются?
Уточните, в какой момент возникает ошибка.
Если есть желание разобраться самостоятельно в формулах, значения всех переменных можно посмотреть в формулы/диспечер имен.
В частности, purcahse — это сумма расходов при покупке облигации.
И какую использовать правильнее не могу взять в толк. Wikipedia топит за =ВСД. И вроде бы ясно что одна (=ВСД) используется для равных промежутков выплат купонов, а вторая (=ЧИСТВНДОХ) для непостоянных периодов, но понимания нет как верно посчитать. Исправьте меня, если откровенно напутал.
Постараемся помочь.
Либо возможно этот как-то преобразовать?
Спасибо ещё раз!
В любом случае, я обязательно поправлю то о чем вы говорите. Но я смогу этим заняться в декабре.