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

Что делает эта функция?

Эта функция сравнивает два значения и проверяет, являются ли они абсолютно одинаковыми.

Если числа совпадают, результатом будет 1, в противном случае — 0.

Работает только с числами, текстовые значения на входе отдают результат #ЗНАЧ!

Форматирование числа не имеет существенного значения, поэтому числа, которые выглядят округленными из-за удаления десятичных разрядов, будут по-прежнему корректно соответствовать не округленным значениям.

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

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

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

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

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

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

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

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

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

Описание функции ДЕЛЬТА

Проверяет равенство двух значений. Возвращает 1, если число1 = число2, и 0 в противном случае. Эта функция используется для фильтрации множества значений. Например, суммируя несколько функций ДЕЛЬТА, можно подсчитать количество равных пар. Эту функцию также называют дельта-функцией Кронекера.

Формула разницы между числами в процентах в Excel

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

A и B одного знака (+ и +, или − и −)

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

В данном случае, A это 120 тысяч рублей, а B, соответственно, 100 тысяч рублей, применим формулу и найдем соотношение между ними:


В итоге получаем, что 120 тысяч рублей больше, чем 100 тысяч рублей на 20%.

Действительно, все достаточно просто, но теперь рассмотрим те же самые величины, но с обратным знаком: -120 и -100.
И для лучшей наглядности расчета рассмотрим не доходы компании, а ее убытки, и представим, что убытки компании в этом году составили -120 тысяч рублей, когда в прошлом году составляли -100 тысяч рублей.

Логично предположить, что мы должны получить ответ -20%, так как на самом деле -120 меньше, чем -100 (с точки зрения математики, чем левее число на числовой оси, тем оно меньше) и, следовательно, в результате получить отрицательный процентный прирост.
Другими словами, убытки компании увеличились и результаты компании ухудшились, а значит прирост в процентах отрицательный.
Проверяем формулу и повторяем расчет:


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

Скорректируем расчет, вместо величины B в знаменателе формулы поставим ее модуль.
Тогда формула расчета разницы в процентах примет окончательный вид:


Пересчитаем пример и посмотрим какой ответ в итоге получится:


Отлично, все сошлось. При этом обратите внимание, что для положительных величин формула на самом деле осталась прежней и никак не поменялась, так как модуль положительные числа не изменяет.

Вставка в Word

Рассмотрим 3 способа:

  1. Напечатайте вместо знака английскую букву «D». Выделите её и в верхнем меню на вкладке «Главная» установите шрифт «Symbol». Буква автоматически превратится в дельту.

  2. В любом месте документа введите «0394» и нажмите горячие клавиши Alt и X. Цифры преобразуются в символ. Раскладка клавиатуры должна быть английской.

    0 3 9 4Alt + X = Δ

  3. На полноценной клавиатуре с правым цифровым блоком клавиш можно воспользоваться следующим методом. Зажимаем Alt и набираем «916». Вводить цифры надо именно на правом цифровом блоке.

    Alt + 9 1 6 = Δ

A и B разных знаков (+ и −, или − и +)

Однако при попытке произвести такое сравнение, мы приходим к логическому несоответствию.
Например, давайте подумаем во сколько число 20 больше числа -5?

Предположим, что мы каким-то образом посчитали отношение и нашли численный ответ (предположу, что на предыдущий вопрос в качестве ответа хочется сказать, что в 5 раз больше):


Однако точно такие же расчеты можно привести для чисел 20 и 4 (получается, что 20 одновременно в 5 раз больше, и чем 4 и чем -5).
Такого быть не может, в связи с чем получается, что посчитать разницу между двумя величинами с разными знаками в процентах корректно нельзя, так как сравниваются не сопоставимые друг с другом величины.

Поэтому в подобных случаях принято писать N/M — Not Meaningful (также встречаются обозначения N.M., NM), что можно перевести как не имеет смысла или не имеет значения.
В официальных пресс-релизах или презентациях крупных компаний это может обозначаться следующим образом (к примеру, результаты компании Thomson Reuters за 1 квартал 2017 г.):


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

Сначала с помощью функции ЕСЛИ проверим условие, что величины одного знака — это равносильно условию, что их произведение больше нуля. В том случае если условие выполняется, то считаем по формуле, если же не выполняется, то в качестве ответа возвращаем ошибку (обозначение NM):

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

Результатом применения функции является определение прибыльности облигаций. Программа может выдать не результат, а знак ошибки #ЧИСЛО! Это происходит в нескольких случаях:

  • введены даты без использования функции «ДАТА»;
  • значение ставки меньше нуля;
  • указана цена меньше или равная нулю;
  • погашение указано меньше или равно нулю;
  • базис меньше нуля или больше четырех.

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

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

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

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

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

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

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

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

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

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

Знак дельта на Mac

Счастливые обладатели яблочной продукции Mac или Macbook могут использовать горячие клавиши Option + J.

Примеры использования функции ДОХОД для анализа облигаций

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

Необходимо вычислить доходность облигаций при следующих условиях:

  1. Облигации были проданы 15 февраля 2008 года.
  2. Срок погашения – 15 ноября 2016.
  3. Ставка по купонам определена 5,75%.
  4. Цена составляет 95.04 номиналов.
  5. Выкупная стоимость — 100 номиналов.
  6. Проценты выплачиваются раз в полгода.
  7. Значение базиса – 0 (30/360).

Последовательно вводите все значения заполняя таблицу с даннымиВ ячейку B9 вводите данные для аргументов функции ДОХОД.

Можно вводить формулу с клавиатуры или указывать соответствующие ячейки мышью. При этом прописать имя функции нужно вручную. Затем откройте скобку и последовательно через «пробел» укажите все ячейки, которые должны быть учтены.

Пример. Предприятие продало облигации 14 июля 2015. Установлен небольшой срок их погашения 14 июля 2017. Предлагается ставка 6%. Цена бумаг 97,4 номинальных значений. Стоимость для выкупа – 107 номиналов. Выплата процентов проводится 4 раза в год. Базис 2 – (48/360).

Это обозначает, что расчет доходности к погашению облигаций составляет (6,16% х 2года)=12,32%.

Пример. Ценные бумаги проданы 2 августа 2004. Погашение произошло 13 мая 2018. Купонная ставка по ним – 5,3%. Цена бумаг – 93 номинала. Стоимость для выкупа – 104 номинала. Проценты начисляются раз в год. Базис – 0 (30/360).

С помощью программы Excel удалось всего за несколько минут установить, что самыми прибыльными инвестициями являются облигации из второго примера.

Учет налогов

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

Калькулятор сроков возврата инвестиций в банковские депозиты

Пример . Реализовать калькулятор, который рассчитывает, сколько времени потребуется на получение определенной прибыли от депозита при известной начальной сумме вклада и годовой процентной ставки. Предусмотреть вывод сообщения об ошибке в случае, если одно или несколько исходных данных не указаны.Поскольку для расчета используется логарифм из частного ожидаемой и начальной суммы с основанием в виде суммы депозита, выраженного в процентах (то есть 100%), и процентной ставки, то каждая ячейка из диапазона B2:B4 не должна являться пустой или принимать значение 0, поскольку логарифм нуля или логарифм с основанием 1 (100%) не может быть вычислен. Поэтому для расчетов используем следующую формулу:

В качестве проверочного выражения функции ЕСЛИ выступает значение, являющееся суммой возвращаемых результатов функциями ДЕЛЬТА. Последние сравнивают значения, содержащиеся в ячейках B2, B3 и B4 соответственно с 0 (второй аргумент явно не указан). Данный вариант записи соответствует логическому И (общий результат равен ИСТИНА, если все выражения, проверяемые функцией И, возвращают результат ИСТИНА). Полученное значение (0 или любое число от 1 до +∞) будет интерпретировано как ЛОЖЬ или ИСТИНА соответственно.

При незаполненных полях получим следующий результат:

Пример расчета:

Напишем формулу для преобразования дробного числа в годы и месяцы:

В результате получим:

Как посчитать дельту (разницу в процентах) в Excel

По иронии судьбы, функция ДЕЛЬТА не поможет посчитать дельту в Excel. Сравнивая данные в ячейках, функция лишь отвечает на вопрос, равна ли их дельта нулю или нет (иными словами, есть ли дельта).

А посчитать дельту между A и B можно без применения каких-либо функций вовсе, лишь зная две простые формулы ее подсчета:

Второй вариант в Excel использовать удобнее, т.к. первый требует использования скобок.

Не дайте буквам в формуле вас запутать — B обозначает первый замер (результат «до»), а A — второй (результат «после»). Иными словами, A случилось после B, их алфавитный порядок здесь не при чем.

Чтобы полученный результат выражался в процентах, нужно изменить формат ячеек на процентный:

Расчет вероятности выпуска бракованной продукции на производстве в Excel

Пример. При изготовлении деталей на производственно-ремонтном заводе существует вероятность брака производимой продукции, которая равная коэффициенту 0,001. Определить, какая вероятность появления 10 выбракованных деталей (в этом случае вся партия считается браком) в партии из 35000 шт. Считается, что вероятностями ниже 10% можно пренебречь (то есть их приравнивают к нулю).

Вид исходной таблицы данных:

Для расчета используем следующую формулу:

Функция ОКРУГЛВНИЗ округляет до 0 все значения, которые меньше числа 0,01, то есть 10%-й вероятности. Функция ДЕЛЬТА принимает только один аргумент (выражение, полученное на основе формулы Пуассона для вычисления маловероятных случайных ситуаций), то есть сравнение выполняется с числом 0 (нуль).

Результат вычислений:

Если вычислять формулу поэтапно, будет видно, что вероятность по формуле Пуассона равна примерно 0,00974 < 0,01, поэтому результат округления равен 0. Тогда функция ДЕЛЬТА возвращает значение 1 (0=0), которое интерпретируется как логическое ИСТИНА.

Особенности использования функции ДЕЛЬТА в Excel

Функция имеет следующий синтаксис:

=ДЕЛЬТА(число1Описание аргументов:

  • число1 – обязательный аргумент, принимающий данные числового типа, которые будут сравнены по значению с числом, переданным в качестве второго аргумента, или нулем, если последний опущен;
  • [число2] – необязательный для заполнения аргумент, характеризующий числовое значение, с которым выполняется операция сравнения. Если явно не указан, принимает значение 0 (нуль).

Примечания:

  1. Рассматриваемая функция выполняет операцию сравнения только числовых значений. Если в качестве одного из аргументов было передано имя, текстовая строка или логические ИСТИНА и ЛОЖЬ, результатом выполнения функции ДЕЛЬТА будет код ошибки #ЗНАЧ!.
  2. Функция ДЕЛЬТА может быть использована для сравнения двух дат, которые являются результатами выполнения функции ДАТА или других функций, возвращающих данные формата Дата, поскольку Excel выполняет автоматическое преобразование в формат времени Excel (соответствующее числовое значение).
  3. Если в качестве первого аргумента функции ДЕЛЬТА было передано число 0, а в качестве второго – ссылка на пустую ячейку, результатом вычислений будет число 1, поскольку пустое значение интерпретируется как числовое значение 0.
  4. Рассматриваемая функция также имеет название дельта-функция Кронекера.
  5. Данную функцию можно использовать вместо логического знака равенства «=» в выражениях, которые используются как условия проверки в логических функциях. Например, функция =ЕСЛИ(ДЕЛЬТА(1;1);”равны”;”не равны”) вернет значение «равны». При этом преобразование чисел 1 и 0 в логические ИСТИНА и ЛОЖЬ выполняется автоматически.
  6. Функция ДЕЛЬТА не может быть использована в качестве формулы массива. Например, формула массива =ДЕЛЬТА (A1:A5;B1B5) вернет массив ошибок #ЗНАЧ!, даже если ячейки из диапазонов A1:A5 и B1:B5 содержат только числовые значения.
Источники

  • https://SEMTools.guru/ru/excel-functions/delta/
  • http://Excel2010.ru/kak-rasschitat-doxod-po-obligaciyam-ispolzuya-funkcii-excel.html
  • https://rostsber.ru/publish/stocks/bonds_ytm.html
  • https://msoffice-prowork.com/ref/excel/excelfunc/engineering/delta/
  • https://tutorexcel.ru/matematika/raznica-v-procentax-mezhdu-dvumya-chislami-v-excel/
  • https://realadmin.ru/softing/znak-delta.html
  • https://exceltable.com/funkcii-excel/raschet-dokhodnosti-obligaciy-v-excel
  • https://exceltable.com/funkcii-excel/primery-funkcii-delta

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: