С помощью команды СЕРВИС, Подбор параметра решается обратная задача определения значения одного любого аргумента (ставка купонных выплат, доход, частота и др.) при заданном курсе (цене) ценной бумаги и сохранений значений прочих параметров.
Например, можно определить необходимый уровень ставки для данных контрольного примера, но при условии, что облигация продаётся по курсу ниже номинала на 10 пунктов при сохранении значений прочих параметров.
В данном случае результат подбора купонной ставки – 9,325%.
Постройте сценарий параметров функции ЦЕНА, в качестве изменяемых ячеек выберите значения ставка, доход, частота. Создайте итоговую таблицу, иллюстрирующую вариантные расчёты.
Например, для данных контрольного примера можно построить сценарии:
Структура
сценария
Номера сценариев
Изменяемые ячейки
Купон
9%
9%
15%
15%
9%
9%
Ставка
12,57%
12,57%
12,57%
12,57%
15,00%
15,00%
Частота
Ячейки результата
ЦЕНА (курс)
89.00
88.87
107.48
107.57
82.37
82.14
Если купон не превосходит ставку, цена меньше номинала (меньше 100). С увеличением частоты выплат; если купон меньше ставки, цена падает; если купон больше ставки, цена растёт и может превысить номинал.
· Функция НАКОПДОХОД
Купонный доход накапливается в интервале времени между их выплатами. После приобретения ценной бумаги дата очередного купонного платежа вычисляется с помощью функции ДАТАКУПОНПОСЛЕ. Функция НАКОПДОХОД вычисляет накопленный на момент приобретения ценной бумаги купонный доход (сумму):
Дата соглашения должна быть меньше даты первой выплаты, в противном случае возвращается ошибка вычисления.
Функция НАКОПДОХОД вычисляется по формуле:
, где
Pn - номинал ценной бумаги;
ik – ставка (размер купонных выплат);
p – периодичность выплат;
А – число накопленных дней для j-го квазикупонного периода в оставшемся периоде выплат;
NC – число квазикупонных периодов, которые укладываются в оставшийся период. Если это
число содержит дробную часть, то оно округляется с избытком до следующего целого;
NL – нормальная продолжительность в днях j-го квазикупонного периода в оставшемся
периоде.
Например, облигации номиналом 1000 руб. с купонной ставкой 9%, периодичность выплат – раз в полугодие выпущены (дата выпуска) 1.09.96. Дата первой оплаты купонов – 1.03.97, базис расчётов – 1.
Тогда накопленный купонный доход на момент приобретения (дата_соглашения) – 12.12.96 вычисляется с помощью функции:
Величина накопленного дохода по облигации зависит от даты соглашения; чем позже куплена облигация от момента выплаты предыдущего купона, тем больше величина накопленного купонного дохода.
Вопрос №3
Функции для расчётов по ценным бумагам с нарушением периодичности выплаты процентов.
Периодичность оплаты купонов ценных бумаг может нарушаться. Необходимо правильно рассчитывать ставку помещения (доход) и курс (цену) ценной бумаги в случае нарушения периодичности платежей.
· Функция ДОХОДПЕРВНЕРЕГ
Данная функция возвращает доход (ставку помещения) по ценным бумагам с нерегулярным (коротким или длинным) первым периодом выплаты купона:
ППП EXCEL вычисляет функцию ДОХОДПЕРВНЕРЕГ методом итераций на основе формулы для функции ЦЕНАПЕРВНЕРЕГ. Доход вычисляется за 100 итераций до тех пор, пока вычисляемая цена для заданного дохода не станет близкой к значению аргумента цена.
Например, облигации выпущены 1.12.95 и имеют 9% купонный доход (ставка) с выплатой процентов (частота) – раз в полугодие. Облигации приобретены (дата соглашения) 1.5.96 по курсу (цена) 87 с погашением (дата вступления в силу) 1.03.97 по курсу (погашение) 100. Дата первого купона – 1.08.96, базис расчета – 1.
Дата нормальной выплаты купона определяется по формуле:
Значение функции ДОХОДПОСЛНЕРЕГ вычисляется по формуле:
, где
Pn – курс погашения;
Pr – цена;
p – частота периодических выплат;
Aj – количество накопленных дней для j-го квазикупонного периода в нерегулярном
периоде, отсчитанное вперёд от даты последней выплаты перед погашением;
DCj – количество дней, сосчитанных для каждого j-го квазикупонного периода, делённое
на продолжительность фактического купонного периода;
DSCj – количество дней от даты соглашения до даты следующего купона для каждого j-го
квазикупонного периода;
NC – количество квазикупонных периодов, укладывающихся в нерегулярный период. Если это число является дробным, то оно округляется с избытком до ближайшего целого;
NLj – нормальная продолжительность в днях j-го квазикупонного периода в нерегулярном купонном периоде.
Например, облигации имеют 9% купонный доход (ставка) с выплатой процентов (частота) раз в полугодие. Облигации приобретены (дата соглашения) 1.05.96 по курсу (цена) 87 с погашением (дата вступления в силу) 1.03.97 по курсу (погашение) 100. Дата последнего купона – 1.04.96, базис расчета – 1.
Тогда с помощью функции определяется ставка помещения при условии отличной даты последнего купона:
Возвращает курс покупки (цену за 100 руб. нарицательной стоимости) ценных бумаг для нерегулярного (короткого или длинного) первого периода купонных выплат:
Значение функции ЦЕНАПЕРВНЕРЕГ – короткий первый купон:
, где
Pn – цена (курс) погашения;
i – доход (ставка помещения);
ik – ставка (размер купонных выплат);
p – частота купонных выплат;
А – количество дней от начала периода купона до даты соглашения (накопленные
дни);
DSC – количество дней от даты соглашения до даты следующего купона;
DFC – количество дней от начала нерегулярного купона до даты первого купона;
Е – количество дней в периоде купона;
N – количество оплачиваемых купонов между датой соглашения и датой погашения
(число округляется до ближайшего целого).
Цена облигации при нерегулярном длинном первом купоне рассчитывается по формуле:
, где
Pn – цена (курс) погашения;
i – доход (ставка помещения);
ik – ставка (размер купонных выплат);
p – частота купонных выплат;
Aj – количество дней от начала j-го квазикупонного периода в нерегулярном периоде
DCj – количество дней от указанной даты (или даты выпуска) до первого
квазикупона (j = 1) или количество дней в квазикупоне (j = 2, …, NC);
DSC – количество дней от даты соглашения до даты следующего купона;
Е – количество дней в периоде купона;
N – количество оплачиваемых купонов от даты первого фактического купона до
даты погашения (если это число является дробным, оно округляется с избытком
до ближайшего целого);
NC – количество периодов квазикупонов, укладывающихся в нерегулярный период
(число округляется с избытком до ближайшего целого);
NLj – нормальная продолжительности в днях полного j-го квазикупонного периода в
нерегулярном периоде;
Nq – количество полных периодов квазикупонов от даты соглашения до первого
купона.
Например, облигации выпущены (дата_выпуска) 1.12.95 и приобретены (дата_соглашения) 1.05.96. Предполагаемая дата погашения (дата_вступления_в_силу) – 1.03.97 по курсу – 100. Купонный доход (ставка) – 9% с выплатой раз в полугодие, ставка помещения – 21,23%. Дата выплаты первого купона – 1.08.96. Временной базис расчёта – 1.
Дата очередного купонного платежа после приобретения облигации определяется с помощью функции ДАТАКУПОНПОСЛЕ:
ДАТАКУПОНПОСЛЕ(«1.5.96»; «1.3.97»; 2;1) = 1.9.96.
Цена (курс) покупки облигации определяется по формуле:
Проанализируем изменение цены (курса) покупки в зависимости от даты купонной выплаты и даты приобретения ценной бумаги с помощью Таблицы подстановки.
Дата купонной выплаты
Дата приобретения
01.05.96
06.05.96
11.05.96
16.05.96
21.05.96
02.06.96
88,62
88,75
88,88
89,02
89,15
12.06.96
88,35
88,48
88,61
88,75
88,88
22.06.96
88,08
88,21
88,34
88,48
88,61
02.07.96
87,81
87,94
88,07
88,20
88,33
12.07.96
87,54
87,67
87,80
87,93
88,06
22.07.96
87,27
87,40
87,53
87,66
87,79
01.08.96
87,00
87,13
87,26
87,39
87,52
Таким образом, чем позже приобретается ценная бумага, тем курс покупки больше.
· Функция ЦЕНАПОСЛНЕРЕГ
Функция рассчитывает курс покупки (цену за 100 руб. нарицательной стоимости) ценных бумаг для нерегулярного (короткого или длинного) последнего периода купона:
Например, облигации приобретены (дата_соглашения) 1.05.96. Предполагаемая дата погашения (дата_вступления_в_силу) – 1.03.97 по курсу – 100. Купонный доход (ставка) – 9% с выплатой раз в полугодие, ставка помещения – 27.78%. Дата выплаты первого купона – 1.03.96. Временной базис расчёта – 1.
Цена (курс) покупки облигации определяется по формуле:
, где
, где
, где
, где