Расчеты по купонным облигациям

 

Цель задачи: ознакомиться с технологиями расчетов дохода по купонным облигациям, сравнительного анализа рентабельности инвестиций и расчетов текущей стоимости 1 облигации, реализованными в Excel.

Предлагается, пользуясь встроенными функциями Excel:

1) рассчитать:

· цену покупки пакета облигаций;

· цену продажи пакета облигаций;

· сумму купонных выплат по пакету облигаций за весь период обращения;

2) рассчитать рентабельность инвестиций и сравнить ее с альтернативной инвестицией при депозитном вкладе той же суммы на тот же календарный срок (учитывать простые проценты);

3) рассчитать текущую стоимость 1 облигации при указанной норме доходности.

 

Для расчетов понадобятся следующие функции Excel:

· СТЕПЕНЬ() – для нахождения степени числа;

· ОКРУГЛ() – для нахождения округления числа;

· ДОЛЯГОДА() – для нахождения количества лет от даты покупки до даты погашения.

ПРИМЕЧАНИЕ: Порядок использования функций – см. в мастере функций, который находится на листе Excel в закладке Вставка в подзакладке Функция (f_x).

Исходные данные для задания 4 представлены в таблице 1.3:

Таблица 1.3

Количество облигаций в пакете, шт.	100
Дата соглашения	01.01.07
Дата вступления в силу (погашения)	01.09.09
Цена покупки 1 штуки, руб.	1050
Цена при погашении 1 штуки (номинальная), руб.	1000
Купонная ставка, % (годовых)	15
Требуемая норма доходности, %	15

 

Алгоритм расчетов

Начнем с расчета цены покупки пакета облигаций в Excel по формуле:

=C7*C4,

где С7 – ячейка Excel, в которой хранится значение цены покупки 1 облигации;

С4 – ячейка Excel, в которой хранится значение количества облигаций в пакете.

Рассчитаем цену продажи пакета облигаций в Excel по формуле:

=C8*C4,

где С8 – ячейка Excel, в которой хранится значение цены при погашении 1 облигации;

С4 – ячейка Excel, в которой хранится значение количества облигаций в пакете.

Рассчитаем количество лет за анализируемый период в Excel по формуле:

=ДОЛЯГОДА(C5;C6),

где С5 – ячейка Excel, в которой хранится значение даты приобретения пакета облигаций;

С6 – ячейка Excel, в которой хранится значение даты погашения пакета облигаций.

Рассчитаем сумму купонных выплат по пакету облигаций за анализируемый период в Excel по формуле:

=C9/100*C8*C14*C4,

где С9 – ячейка Excel, в которой хранится значение купонной ставки по пакету облигаций в годовых процентах;

С8 – ячейка Excel, в которой хранится значение номинальной цены 1 облигации;

С14 – ячейка Excel, в которой хранится значение количества лет до погашения пакета облигаций;

С4 – ячейка Excel, в которой хранится значение количества облигаций в пакете.

Рассчитаем рентабельность инвестиций по пакету в Excel по формуле:

=(C13-C12+C15)/C12*100,

где С13 – ячейка Excel, в которой хранится значение цены продажи пакета облигаций;

С15 – ячейка Excel, в которой хранится значение суммы купонных выплат по пакету облигаций за анализируемый период;

С12 – ячейка Excel, в которой хранится значение цены приобретения пакета облигаций (или сумма первоначальных инвестиций).

Рассчитаем размер дохода по депозиту при 20% годовых и тех же суммах и сроках инвестирования в Excel по формуле:

=C12*20/100*C14,

где С14 – ячейка Excel, в которой хранится значение количества лет в анализируемом периоде;

С12 – ячейка Excel, в которой хранится значение цены приобретения пакета облигаций (или сумма первоначальных инвестиций).

Рассчитаем рентабельность инвестиций по депозиту при 20% годовых и тех же суммах и сроках инвестирования в Excel по формуле:

=C17/C12*100,

где С17 – ячейка Excel, в которой хранится значение размера дохода по депозиту;

С12 – ячейка Excel, в которой хранится значение цены приобретения пакета облигаций (или сумма первоначальных инвестиций).

Текущую стоимость 1 купонной облигации с постоянной величиной купона нужно рассчитывать по формуле:

                                                                                               (8)

где Р_обл – текущая стоимость 1 купонной облигации;

К – годовая купонная ставка;

N – номинальная стоимость 1 купонной облигации;

r – требуемая норма доходности;

n – число лет до погашения облигации.

Рассчитаем текущую стоимость 1 облигации при указанной норме доходности в Excel по формуле (в сумме по формуле (8) три слагаемых, так как количество лет расчета равно 2⅔, то есть не более 3):

=C9/100*C8/(1+C10/100)+C9/100*C8/СТЕПЕНЬ((1+C10/100);ОКРУГЛ(C14;0))+

+C9/100*C8/СТЕПЕНЬ((1+C10/100);C14)+C8/СТЕПЕНЬ((1+C10/100);C14),

где С9 – ячейка Excel, в которой хранится значение купонной ставки по пакету облигаций в годовых процентах;

С8 – ячейка Excel, в которой хранится значение номинальной цены 1 облигации;

С10 – ячейка Excel, в которой хранится значение указанной нормы доходности;

С14 – ячейка Excel, в которой хранится значение количества лет до погашения пакета облигаций.

Рассчитаем рентабельность инвестиций по текущей стоимости облигации в Excel по формуле (по всему пакету):

=(C13-C19*C4+C15)/C19/C4*100,

где С13 – ячейка Excel, в которой хранится значение цены продажи пакета облигаций;

С19 – ячейка Excel, в которой хранится значение рассчитанной текущей стоимости 1 облигации;

С15 – ячейка Excel, в которой хранится значение суммы купонных выплат по пакету облигаций за анализируемый период;

С4 – ячейка Excel, в которой хранится значение количества облигаций в пакете.

Алгоритм расчетов на листе Excel с указанием используемых формул и полученными результатами представлен на рисунке 4.

Результаты расчетов позволяют сделать следующие выводы:

· Цена покупки рассматриваемого пакета облигаций равна 105000 руб. Цена продажи рассматриваемого пакета облигаций равна 100000 руб. Сумма купонных выплат по рассматриваемому пакету облигаций за весь анализируемый период равна 40000 руб.;

· Общая рентабельность операции равна 33⅓%, что позволяет сделать вывод о невысокой эффективности инвестиций по сравнению с размещением (например) денег, потраченных на приобретение пакета облигаций, на депозит;

· По состоянию на 01.01.2007 депозитная ставка в коммерческих банках была не менее чем 20%. За анализируемый период доход – даже при начислении простых процентов – по такому вкладу составил бы 56000 руб., что существенно больше, чем сумма полученной выгоды по рассматриваемой операции: 40000 руб. При вкладе инвестируемой суммы (105000 руб.) на депозит рентабельность операции за рассматриваемый период была бы более 53%, что больше полученного в задаче значения рентабельности по приобретению пакета облигаций;

· Расчет текущей стоимости одной облигации показал, что по состоянию на 01.01.2007 ее цена должна была составить 1021,27 руб. за штуку. При этом рентабельность таких инвестиций составила 37,08%, что объясняется меньшей закупочной ценой одной облигации.

 

Рис. 4. Алгоритм расчетов по 4 заданию