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

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

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

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

Таблица купонных выплат на сайте Rusbond.ru

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

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

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

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

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

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

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

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

Определение:

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

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

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

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

Учет налогов

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

учет НДФЛ при расчете доходности к погашению

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

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

UPDATE 23.01.2020

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

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

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

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

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

Размер: 36439 байт

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

Размер: 40573 байт


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


Комментарии

  1. Сергей 11 января 2020, 23:58 # 0
    Вопросы по вашему калькулятору:
    1. Как он будет выглядеть, если я периодически докупаю и продаю часть облигаций? Это более реальная ситуация, в которой нужно вычислить доходность инвестиций в данную облигацию.
    Если я купил N1 облигаций, потом докупил N2, потом продал N3 и т.д., то я эти суммы могу поставить в вашу таблицу (соблюдая "+" и "-"), функция ЧИСТВНДОХ отработает нормально? А как быть с купонами, по какой простой формуле подсчитать, какое число купонов на какую дату имеется, чтобы ручками границы СУММ каждый раз не двигать?

    2. Как изменится ваш калькулятор, если у меня несколько облигаций и я не хочу для каждой отдельно считать. У меня один файл, в котором я по датам фиксирую покупку всех моих облигаций и выплаты купонов по ним. Я не пойму, как нужно доработать функцию ЧИСТВНДОХ, чтобы внутри она в столбцах дат и денежных потоков выбирала только для указанной облигации (название каждой облигации в каждой строке есть), как фильтр правильно поставить?
    1. Сергей 12 января 2020, 07:21(Комментарий был изменён) # 0
      Сергей, всё о чем вы спрашиваете, имеет отношение к управлению портфелем облигаций. Калькулятор в статье предназначен для анализа одной конкретной ценной бумаги. Как правило, это нужно перед покупкой облигации и помогает принять решение.
      Управление облигационным портфелем — это отдельная тема. Простеньким шаблоном в EXCEL здесь всех вопросов не решить. Для портфеля нет такого понятия, как «доходность к погашению», например. Хотя можно считать среднюю доходность по купону и усредненную доходность к погашению, входящих в портфель ценных бумаг. Некоторые из этих вопросов будут обсуждаться на вебинаре 14 января.
      В будущем на Финариуме планируем курс по управлению портфелем облигаций.
      1. Владимир 22 января 2020, 22:14 # 0
        Доброго времени суток. Уточните, пожалуйста, для получения результатов по какой-либо облигации, необходимо заполнить столбцы «Даты», «купоны», строчки «Комиссия брокера» ( очевидно комиссия брокера + комиссия биржи), «Номинал», «НКД», «Покупка», «Купон», «Кол-во»? Потребуется время чуть большее, нежели это сделать на калькуляторе. Перенести необходимые данные с русбондс скопом нельзя? Спасибо
        1. Сергей 23 января 2020, 06:45 # 0
          нет, перенести данные автоматически не получится. Этот калькулятор — для тех, кто любит считать всё самостоятельно. Тем более что здесь можно запрограммировать гораздо больше ситуаций, чем дают обычные калькуляторы на сайтах. Например, можно посмотреть, как изменение курса доллара повлияет на доходность к погашению.
        2. Юрий 16 ноября 2021, 19:48 # 0
          Да дкп портфеля формально нет, но есть IRR потока платежей портфеля, что в сущности и есть дкп портфеля.
      2. Сергей 12 января 2020, 17:58 # 0
        Спасибо.
        1. Калькулятор считает доходность с учетом реинвестирования или без? Если без, то как его нужно изменить, чтобы было с реинвестированием? Если с учетом реинвестирования, то как его изменить, чтобы было без реинвестирования?
        2. В чем смысл данного калькулятора, если доходность к погашению транслируется биржей? Чтобы узнать текущую цену, вам же все равно нужно смотреть сайт биржи или квик.
        1. Сергей 12 января 2020, 22:04 # 0
          «доходность к погашению» предполагает реинвестирование дивидендов. Как вы собираетесь считать доходность без реинвестирования? Что на что делить будете? :) В каком-то смысле доходностью без реинвестирования можно считать доходность по купону.
          Калькулятор полезен в двух случаях:
          1. Когда в QUIK (или другом терминале) не считает доходность к погашению самостоятельно. Например, для многих еврооблигаций доходность по неизвестным причинам не считается.
          2. Для проверки различных вариантов покупки с разными условиями сделок.
        2. Сергей 12 января 2020, 22:18 # 0
          Доходность к погашению вы считаете по формуле внутренней доходности проекта, которая предполагает cashflow: в начале проекта инвестиции со знаком минус, в течение проекта идет положительный денежный поток, который получает инвестор, т.е. априори предполагается, что деньги выводятся.

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

          В вашем калькуляторе получается, что после каждого купона, его нужно реинвестировать, т.е. должен быть отрицательный денежный поток, который увеличивает количество облигаций, в итоге каждая следующая купонная выплата будет больше, т.к. количество облигаций стало больше. В конце сумма погашения тоже будет больше, т.к. облигаций стало больше.
          1. Сергей 12 января 2020, 23:45(Комментарий был изменён) # 0
            Другой пример.
            Инвестор вкладывает в проект 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.
            1. Сергей 13 января 2020, 13:58 # 0
              Сергей, вы, вероятно, слабо знакомы с дисконтированием и IRR. Чтобы получить доходность, равную IRR, денежные потоки необходимо реинвестировать по ставке, равной IRR. Это несложно проверить на простейших примерах. «Доходность к погашению» для облигаций — это IRR по определению. Поэтому калькулятор учитывает реинвестирование купонов по ставке IRR.
            2. Сергей 18 января 2020, 15:01 # 0
              Сергей, как считать доходность ПК облигаций? Если я могу предположить как будет изменяться ключевая ставка в ближайшее время, то как нужно модифицировать ваш файл, чтобы учесть возможное изменение купонов за предыдущие 6 мес. по ставке RUONIA?
              1. Сергей 19 января 2020, 13:31 # 0
                Ставки облигация ПК, привязанные к RUONIA, определяются как среднее арифметическое значений ставок RUONIA за последние 6 месяцев + 1,20%. Например, у ОФЗ 29006 ближайшая ставка на февраль уже определена — 8,73%. Это выше, чем у ОФЗ с постоянным купоном, но и цена у 29006 сейчас на уровне 107%.

                Прогнозировать дальнейшие значения на основе изменения ключевой ставки (КС) можно, т.к. процентов на 90% ставка RUONIA зависит именно от КС и ликвидности в банковском секторе. Ликвидность прогнозировать сложно. Но про ставку на ближайшее время все более или менее понятно. Берите ставки RUONIA за последние месяцы, считайте среднее арифметическое… и в общем-то всё (не забудьте добавить 1,2% и посмотреть дельту, связанную с ликвидностью).
              2. Сергей 24 января 2020, 17:14 # 0
                Сергей,
                1. IRR — это усредненная годовая доходность или абсолютная доходность между двумя крайними датами? Например, есть ли срок до погашения более года, скажем 2 года и 8 мес, то я получаю по IRR среднюю годовую доходность?
                2. Если по бумаге осталось до погашения 4 мес, то IRR — это также будет приведенная годовая доходность или доходность за 4 мес.?
                1. Сергей 24 января 2020, 17:33 # 0
                  В этом калькуляторе использована функция XIRR. Она всегда приводит результат к году. Вне зависимости от периода (даже если он меньше года).
                2. Сергей 08 марта 2020, 16:24 # 0
                  В вашем калькуляторе функция XIRR предполагает реинвестирование купонов с той же доходностью, что вряд ли получится. Как в формуле расчета учесть другую ставку реинвестирования купонов?
                  Есть формула MIRR, но в ней нет дат, она считает за равные промежутки.
                  1. Сергей 08 марта 2020, 23:53 # 0
                    Здесь получается некоторый нюанс — формула XIRR учитывает реинвестирование внутри себя автоматом, а реально нам приходят купоны в виде кэша. Если мы его реинвестируем, то мы теряем на комиссии брокера и биржи.
                    У вас в формуле комиссия учтена только в сумме инвертирования. С купонов вы вычитаете только НДФЛ, а почему не вычитаете также и комиссию?
                    1. Сергей 09 марта 2020, 12:54(Комментарий был изменён) # 0
                      Цель калькулятора — расчет доходности к погашению. По определению доходность к погашению для облигаций это IRR. Поэтому учитывать комиссии при реинвестировании купонов для получения IRR нет смысла. Например, инвестор может выводить купоны с брокерского счета и не реинвестировать их вообще. Доходность к погашению облигации от этого не изменится.

                      Я понимаю откуда постоянно берутся подобные вопросы. Люди которые не слишком понимают DCF хотят вычислить «доходность». Но если в инвестициях появляются пополнения и снятия со счета, доходность как таковая не определена. Поэтому в облигациях считают сразу несколько параметров: доходность к погашению, купонную доходность и т.п.

                      Если же вы все купоны реинвестируете, то пополнений и снятий нет, и IRR считать нет необходимости. Достаточно разделить конечную сумму на начальную и вычесть единицу — получится доходность.
                      1. Сергей 10 марта 2020, 01:14 # 0
                        «Например, инвестор может выводить купоны с брокерского счета и не реинвестировать их вообще. Доходность к погашению облигации от этого не изменится.»
                        — а моя доходность от покупки данной облигации измениться? Просьба пояснить.

                        Мои рассуждения: Расчет IRR по XIRR предполагает реинвестирование купонов по ставке IRR, т.е. я получив купоны должен их сразу же куда-то реинвестировать по ставке IRR. Только в этом случае я от купленной облигации получу расчетную доходность к погашению, иначе моя итоговая доходность от покупки облигации окажется ниже.
                        Я рассуждаю правильно, или в чем ошибка?
                  2. Сергей 10 марта 2020, 18:01 # 0
                    Если в облигации есть оферта, то как считать доходность к погашению — только к дате оферты, т.к. ставку купона после оферты могут изменить? Оферту брать по 100% или она может быть другой?
                    1. Сергей 13 марта 2020, 12:00 # 0
                      Сергей, поскольку этот вопрос довольно часть звучит, решил написать небольшую статью на эту тему: Доходность к погашению облигации и реинвестирование купонов.
                    2. flabber.09 03 апреля 2020, 19:53 # 0

                      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 не последней версии и из-за этого формулы сбиваются?
                      1. Сергей 04 апреля 2020, 07:53 # 0
                        К сожалению, ошибку воспроизвести не получилось… Обе версии калькулятора работают при НДФЛ равном нулю.
                        Уточните, в какой момент возникает ошибка.
                        Если есть желание разобраться самостоятельно в формулах, значения всех переменных можно посмотреть в формулы/диспечер имен.
                        В частности, purcahse — это сумма расходов при покупке облигации.
                      2. flabber.09 04 апреля 2020, 08:21 # 0
                        Заменил в формуле purchase на F11 и все заработало. Спасибо за то что вы делаете. Добра Вам.
                        1. flabber.09 04 апреля 2020, 08:24 # 0
                          Еще в формуле возникает ошибка в Дюрации и Модифицированной дюрации при колличестве купонов в год больше 4. Не понял с чем это связано.
                          1. flabber.09 04 апреля 2020, 08:36 # 0
                            Не могли бы Вы дать пояснения по НДФЛ на купоны по корпоративным облигациям выпущенным после 2017 в свете последних событий (Обращение президента)? По ним сейчас НДФЛ будут снимать уже 13% или с какого времени? Или это только ОФЗ касается?
                            1. Сергей 08 апреля 2020, 05:48 # 0
                              НДФЛ на купонных доход по корпоративным облигациям будут начислять. Но это изменение начнет действовать с 2021 года. И пока по нему есть вопросы. Например, не понятно, будет ли НДФЛ начисляться на ИИС.
                            2. Рустам 15 февраля 2021, 18:07 # 0
                              не работает калькулятор
                              1. Сергей 16 февраля 2021, 10:31 # 0
                                Рустам, что конкретно не работает в калькуляторе?
                              2. Алексей 16 февраля 2021, 18:00 # 0
                                Возможно ли посчитать данным калькулятором не к погашению, а в случае когда я размещаю средства в облигации на короткий промежуток, от недели, двух недель, месяц? Я так понимаю где-то нужно исправлять формулу на дневные значения (в днях).
                                1. Сергей 17 февраля 2021, 15:15 # 0
                                  Честно говоря, в такой ситуации легче всего разобраться в том, что такое IRR, и сделать свой калькулятор. Этот калькулятор будет править сложнее.
                                  1. Алексей 18 февраля 2021, 09:20 # 0
                                    Сергей, спасибо! Кстати, в интернете сталкиваюсь с тем, что при расчете IRR используют формулу =ЧИСТВНДОХ и =ВСД.
                                    И какую использовать правильнее не могу взять в толк. Wikipedia топит за =ВСД. И вроде бы ясно что одна (=ВСД) используется для равных промежутков выплат купонов, а вторая (=ЧИСТВНДОХ) для непостоянных периодов, но понимания нет как верно посчитать. Исправьте меня, если откровенно напутал.
                                    1. Сергей 18 февраля 2021, 10:20 # 0
                                      При работе с купонными или дивидендными платежами лучше пользоваться =ЧИСТВНДОХ, т.к. платежи могут поступать неравномерно. Кроме того, =ЧИСТВНДОХ удобен тем, что всегда дает результат в годовых.
                                2. Ева 21 марта 2021, 13:37 # 0
                                  Здравствуйте! Всё ввела в калькулятор, но ничего не считает… Когда встаю ю, например, на купонную доходность в формуле не отображается знаменатель последней дроби. Что может быть не так?
                                  1. Ева 21 марта 2021, 14:47 # 0
                                    Вернее не находит показателя purchase, то есть покупки. У вас он обозначен как price
                                    1. Сергей 23 марта 2021, 13:31 # 0
                                      Елена, пришлите ваш файл на info@rostsber.ru
                                      Постараемся помочь.
                                  2. Алексей 17 августа 2021, 15:43 # 0
                                    Подскажите пожалуйста, а есть калькулятор для расчёта облигаций с амортизацией? Очень нужен.
                                    Либо возможно этот как-то преобразовать?
                                    1. Сергей 18 августа 2021, 16:51 # 0
                                      А чем этот калькулятор не подходит?
                                      1. Алексей 19 августа 2021, 09:36 # 0
                                        разобрался, спасибо)) всё одинаково, просто ставим сумму купона + амортизация. и Формула ЧистВнДох всё верно считает)
                                        Спасибо ещё раз!
                                    2. Юрий 16 ноября 2021, 22:16 # 0
                                      У меня одно замечание к Вашему калькулятору дкп. Как я понимаю, дкп Вы считаете эффективной годовой ставкой. На рынке используются и другие варианты дкп — облигационная дкп (номинальная годовая с периодом начисления равным купонному периоду), дкп за купонный период и непрерывно начисляемая дкп. В котировках, как правило, указывается номинальная дкп, а она, вообще говоря, отличается от эффективной. В используемой Вами функции ДЛИТ по доходом понимается номинальная дкп, к тому же у вас опущен параметр базис, но в этом случае используется правило 30/360, тогда как в ОФЗ неявно предполагается правило АСТ/365. Наконец, при вычислении модифицированной дюрации вы делите годовую дюрацию (найденную по эффективной дкп) на полугодовой коэффициент роста =1+дкп/2. Но так делать нельзя, ведь эффективная дкп не номинальная. Модифицированная дюрация по номинальной ставке не сильно но все же отличается от дюрации по эффективной ставке.
                                      1. Сергей 17 ноября 2021, 10:44 # 0
                                        Юрий, спасибо за полезные замечания. Если есть интерес и возможность, попробуйте внести изменения в калькулятор. Выпустим и опубликуем его откорректированную версию.
                                        В любом случае, я обязательно поправлю то о чем вы говорите. Но я смогу этим заняться в декабре.

                                      Оставьте комментарий