Цель задачи: ознакомиться с технологиями расчетов дохода по купонным облигациям, сравнительного анализа рентабельности инвестиций и расчетов текущей стоимости 1 облигации, реализованными в Excel.
Предлагается, пользуясь встроенными функциями Excel:
1) рассчитать:
· цену покупки пакета облигаций;
· цену продажи пакета облигаций;
· сумму купонных выплат по пакету облигаций за весь период обращения;
2) рассчитать рентабельность инвестиций и сравнить ее с альтернативной инвестицией при депозитном вкладе той же суммы на тот же календарный срок (учитывать простые проценты);
3) рассчитать текущую стоимость 1 облигации при указанной норме доходности.
Для расчетов понадобятся следующие функции Excel:
· СТЕПЕНЬ() – для нахождения степени числа;
· ОКРУГЛ() – для нахождения округления числа;
· ДОЛЯГОДА() – для нахождения количества лет от даты покупки до даты погашения.
ПРИМЕЧАНИЕ: Порядок использования функций – см. в мастере функций, который находится на листе Excel в закладке Вставка в подзакладке Функция (fx).
|
|
Исходные данные для задания 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 заданию