- •Пояснительная записка
- •Краткое изложение некоторых тем курса, задания для самостоятельной работы
- •Тема 1. Финансовые вычисления в Excel
- •1.1. Финансовые функции
- •1.2. Функции анализа долгосрочных финансовых операций
- •1.2.1. Функции для анализа потоков платежей
- •1.2.2. Задания для самостоятельной работы
- •1.2.3. Функции для разработки планов погашения кредитов
- •1.2.4. Задания для самостоятельной работы
- •1.2.5. Функции анализа эффективности инвестиционных проектов
- •1.2.6. Задания для самостоятельной работы
- •1.3. Функции для анализа ценных бумаг
- •1.3.1. Функции анализа облигаций с фиксированным купоном
- •1.3.2. Задания для самостоятельной работы
- •1.3.3. Функции для анализа краткосрочных финансовых операций
- •1.3.4. Задания для самостоятельной работы
- •1.3.5. Функции для анализа краткосрочных ценных бумаг
- •1.3.6. Задания для самостоятельной работы
- •Тема 2. Поиск оптимальных решений
- •2.1. Транспортная задача
- •2.2. Задания для самостоятельной работы
- •Тема 3. Анализ финансово-хозяйственной деятельности торгового предприятия средствами Microsoft Excel
- •3.1. Показатели ликвидности
- •3.2. Показатели платежеспособности
- •3.3. Показатели деловой активности
- •3.4. Показатели рентабельности
- •4. Организация решения учетных задач средствами Microsoft Excel
- •Список рекомендуемой литературы
- •Приложения
- •Образцы таблиц для листа Отчетность
- •Содержание
- •Решение финансово-экономических задач в среде табличного процессора microsoft ехcеl Лабораторный практикум для студентов специальности "Финансы и кредит"
1.3.1. Функции анализа облигаций с фиксированным купоном
Для анализа облигаций с фиксированным купоном в Excel реализованы 15 функций (табл. 7).
Таблица 7. Функции для анализа облигаций с фиксированным купоном
Синтаксис |
Аргументы |
|
ДАТАКУПОНДО |
ДАТАКУПОНДО (дата_соглашения; дата_вступления_ в_ силу; периодичность; базис) |
|
ДАТАКУПОНПОСЛЕ |
ДАТАКУПОНПОСЛЕ (дата_соглашения;;дата_вступле-ния _в_силу; периодичность; базис) |
|
ДНЕЙКУПОНДО |
ДНЕЙКУПОНДО (дата_ соглашения; дата_ вступления_ в_ силу; периодичность; базис) |
|
ДНЕЙКУПОНПОСЛЕ |
ДНЕЙКУПОНПОСЛЕ (дата_соглашения; дата_ вступления _в_силу; периодичность; базис) |
|
ДНЕЙКУПОН |
ДНЕЙКУПОН (дата_соглашения; дата_вступления_в_ силу; периодичность; базис) |
|
ЧИСЛКУПОН |
ЧИСЛКУПОН (дата_соглашения; дата_вступления_ в_ силу; периодичность; базис) |
|
ДЛИТ |
ДЛИТ дата_соглашения; дата_вступления_в_силу; купон; доход; периодичность; базис) |
|
МДЛИТ |
МДЛИТ (дата_соглашения; дата_вступления_в_силу; купон; доход; частота; базис) |
|
ЦЕНА |
ЦЕНА (дата_соглашения; дата_вступления_в_силу; ставка; доход; погашение; частота; базис) |
|
НАКОПДОХОД |
НАКОПДОХОД (дата_выпуска; дата_первой_выплаты; дата_соглашения; ставка; номинал; периодичность; базис) |
|
ДОХОД |
ДОХОД (дата_соглашения; дата_вступления_в_силу; ставка; цена; погашение; частота; базис) |
|
ДОХОДПЕРВНЕРЕГ |
ДОХОДПЕРВНЕРЕГ (дата_соглашения; дата_вступле-ния_в_силу; дата_выпуска; первый_купон; ставка; цена; погашение; частота; базис) |
|
ДОХОДПОСЛНЕРЕГ |
ДОХОДПОСЛНЕРЕГ (дата_соглашения; дата_ ступления_в_силу; последняя_выплата; ставка; цена; погашение; частота; базис |
|
Окончание табл. 7 |
||
Синтаксис |
Аргументы |
|
ЦЕНАПЕРВНЕРЕГ |
ЦЕНАПЕРВНЕРЕГ (дата_соглашения; дата_вступле-ния _в_силу; дата_выпуска; первый купон, ставка; доход; погашение; частота; базис) |
|
ЦЕНАПОСЛНЕРЕГ |
ЦЕНАПОСЛНЕРЕГ (дата_соглашения; дата_вступле-ния_в_силу; последняя_выплата; ставка; доход; погашение; частота; базис) |
Дата_соглашения — это дата, когда ценные бумаги были проданы покупателю, выраженная в числовом формате (более поздняя, чем дата выпуска).
Дата_вступления_в_силу — дата вступления в силу (срок погашения) ценных бумаг, выраженная в числовом формате. Определяет истечение срока действия ценных бумаг.
Периодичность — количество выплат по купонам за год. Для ежегодных выплат периодичность равна 1, для полугодовых выплат — 2, для ежеквартальных выплат периодичность равна 4.
Базис — это используемый способ вычисления дня.
Купон — годовая процентная ставка для купонов по ценным бумагам.
Доход — это годовой доход по ценным бумагам.
Частота — количество выплат по купонам за год.
Дата_выпуска — это дата выпуска ценных бумаг, выраженная в числовом формате.
Дата_первой_выплаты — это дата первой выплаты по ценным бумагам, выраженная в числовом формате.
Ставка — годовая процентная ставка для купонов по ценным бумагам.
Номинал — это номинальная стоимость ценных бумаг. Если номинал опущен, то функция НАКОПДОХОД использует значение 1000 р.
Погашение — выкупная цена при погашении за 100 р. номинальной стоимости ценных бумаг.
Цена — это цена ценных бумаг на 100 р. номинальной стоимости.
Первый_купон — это дата первого купона для ценных бумаг, выраженная в числовом формате.
Последняя_выплата — это дата последнего купона для ценных бумаг, выраженная в числовом формате.
Приведем следующий пример.
Пример 8
Рассмотрим возможность приобретения предприятием облигаций внутреннего валютного займа (ОВВЗ).
Проведем расчет эффективности операции на 18.03.97 г. исходя из следующих данных.
Дата выпуска ОВВЗ — 14.05.96 г. Дата погашения — 14.05.2011 г. Купонная ставка — 3 %. Число выплат — один раз в год. Средняя курсовая цена на дату операции — 34,75 ед. изм. Требуемая норма доходности — 12 % годовых.
В соответствии с рис. 10, подготовим таблицу и заполним ее исходными данными.
|
A |
B |
C |
D |
E |
1 |
Анализ купонных облигаций |
||||
2 |
Серия ОВВЗ |
|
|
Дата покупки |
18.03.1997 |
3 |
Дата выпуска |
14.05.1996 |
|
Цена к номиналу |
34,75 |
4 |
Дата погашения |
14.05.2011 |
|
Норма доходности |
12,00% |
5 |
Дата первой выплаты купона |
14.05.1997 |
|
|
|
6 |
Ставка купона |
3,00% |
|
|
|
7 |
Цена погашения (в % к номиналу) |
100 |
|
|
|
8 |
Число выплат в году |
1 |
|
|
|
9 |
|
|
|
|
|
10 |
Дата предыдущей выплаты купона |
|
|
|
|
11 |
Дата следующей выплаты купона |
|
|
|
|
12 |
Дней от начала периода купона до покупки |
|
|
|
|
13 |
Число дней в периоде купона |
|
|
|
|
14 |
Число дней до следующей выплаты |
|
|
|
|
15 |
Число остающихся выплат |
|
|
|
|
16 |
|
|
|
|
|
17 |
Дюрация |
|
|
|
|
18 |
Модифицированная дюрация |
|
|
|
|
19 |
Цена облигации исходя из доходности (Р) |
|
|
|
|
20 |
Доходность к погашению (YTM) |
|
|
|
|
21 |
Текущая доходность |
|
|
|
|
22 |
Накопленный процент (НКД) |
|
|
|
|
Рис. 10. Исходная таблица для анализа купонных облигаций
В приведенной таблице исходные (неизменяемые) характеристики займа содержатся в блоке ячеек В2:В8. Значения изменяемых переменных задачи вводятся в ячейки Е2:Е4. Вычисляемые параметры содержатся в блоке В10:В22.
Рассмотрим решение примера с использованием функций изучаемой группы.
Функции для определения характеристик купонов
Функция ДАТАКУПОНДО () вычисляет дату предыдущей (т. е. до момента приобретения облигации) выплаты купона. С учетом введенных исходных данных функция, заданная в ячейке В10, имеет вид:
= ДАТАКУПОНДО (Е2;В4;В8)
Результат: 14.05.96 г.
Если бы такая выплата производилась, по условиям займа, она действительно должна была бы состояться именно 14.05.96 г.
Функция ДАТАКУПОНПОСЛЕ() вычисляет дату следующей (после приобретения) выплаты купона. Введем функцию в ячейку В11:
=ДАТАКУПОНПОСЛЕ (Е2;В4;В8)
Результат: 14.05.97 г.
Полученная дата совпадает со сроком выплаты первого купона, как и следует из условия примера.
Функция ДНЕЙКУПОНДО () вычисляет количество дней, прошедших с момента начала периода купона до момента приобретения облигации. Эту функцию задаем в ячейке В12:
= ДНЕЙКУПОНДО (Е2;В4;В8)
Результат: 304 дня.
Таким образом, с момента начала периода купона до даты приобретения облигации (18.03.97 г.) прошло 304 дня.
Функция ДНЕЙКУПОН () вычисляет количество дней в периоде купона. По условиям выпуска облигации купоны выплачиваются один раз в году. Таким образом, число дней в периоде купона должно быть равным 360 (финансовый год). Эту функцию задаем в ячейке В13:
=ДНЕЙКУПОН (Е2;В4;В8)
Результат: 360 дней.
Функция ДНЕЙКУПОНПОСЛЕ () вычисляет количество дней, оставшихся до даты ближайшей выплаты купона (с момента приобретения облигации). Введем функцию в ячейку В14:
=ДНЕЙКУПОНПОСЛЕ (Е2;В4;В8)
Результат: 56 дней.
Таким образом, периодический доход по облигации будет получен через 56 дней после ее приобретения.
Функция ЧИСЛКУПОН () вычисляет количество оставшихся выплат (купонов) с момента приобретения облигации до срока погашения. Введем функцию в ячейку В15:
=ЧИСЛКУПОН (Е2;В4;В8)
Результат: 15 выплат.
Согласно полученному результату, с момента приобретения облигации и до срока ее погашения будет произведено 15 выплат, что полностью соответствует условиям займа.
Функции для определения дюрации
Для обязательств с выплатой периодических доходов важную роль играет временный показатель — средневзвешенная продолжительность платежей, или дюрация.
Функция ДЛИТ() вычисляет дюрацию D. Заданная в ячейке В17, функция имеет вид:
=ДЛИТ (Е2;В4;В6;Е4;В8)
Результат: 9,39.
Таким образом, средневзвешенная продолжительность платежей по 15-летней ОВВЗ составит 9 лет и около 140 дней.
Функция МДЛИТ () реализует модификационную формулу для определения дюрации MD. Функция задана в ячейке В18:
=МДЛИТ (Е2;В4;В6;Е4;В8)
Результат: 8,39.
Полученный результат на целый год меньше.
Функции для определения курсовой цены и доходности облигации
Функция ЦЕНА() позволяет определить современную стоимость 100 единиц номинала облигации (т. е. курс) исходя из требуемой нормы доходности на дату ее покупки. Функция задана в ячейке В19:
=ЦЕНА(Е2;В4;В6;Е4;В7;В8)
Результат: 40,06.
Полученная величина 40,06 представляет собой цену облигации, которая обеспечивает требуемую норму доходности — 12 %. Ее величина больше средней цены покупки (34,75). Мы получим дополнительную прибыль приблизительно в 5,3 на каждые 100 единиц номинала при погашении облигации.
Функция ДОХОД () вычисляет доходность облигации к погашению. Функция задана в ячейке В20:
=ДОХОД (Е2;В4;В6;Е3;В7;В8)
Результат: 13,63 %.
Полученный результат несколько выше требуемой нормы доходности и в целом подтверждает прибыльность данной операции.
Ячейка В21 содержит формулу для расчета текущей (на момент совершения сделки) доходности как отношение купонной ставки (ячейка В6) к цене приобретения облигации (ячейка Е3):
=В6/Е3*100
Результат: 8,63 %.
Таким образом, текущая доходность операции составляет 8,63 %, что значительно выше купонной ставки, однако ниже доходности к погашению.
Функция НАКОПДОХОД () рассчитывает величину накопленного купонного дохода НКД на дату сделки. Функция задана в ячейке В22:
=НАКОПДОХОД (В3;В11;Е2;В6;В7;В8)
Результат: 2,53.
Полученная в результате таблица должна иметь вид, представленный на рис. 11.
|
A |
B |
C |
D |
E |
1 |
Анализ купонных облигаций |
||||
2 |
Серия ОВВЗ |
|
|
Дата покупки |
18.03.1997 |
3 |
Дата выпуска |
14.05.1996 |
|
Цена к номиналу |
34,75 |
4 |
Дата погашения |
14.05.2011 |
|
Норма доходности |
12,00% |
5 |
Дата первой выплаты купона |
14.05.1997 |
|
|
|
6 |
Ставка купона |
3,00% |
|
|
|
7 |
Цена погашения (в % к номиналу) |
100 |
|
|
|
8 |
Число выплат в году |
1 |
|
|
|
9 |
|
|
|
|
|
10 |
Дата предыдущей выплаты купона |
14.05.1996 |
|
|
|
11 |
Дата следующей выплаты купона |
14.05.1997 |
|
|
|
12 |
Дней от начала периода купона до покупки |
304 |
|
|
|
13 |
Число дней в периоде купона |
360 |
|
|
|
14 |
Число дней до следующей выплаты |
56 |
|
|
|
15 |
Число остающихся выплат |
15 |
|
|
|
16 |
|
|
|
|
|
17 |
Дюрация |
9,3936634 |
|
|
|
18 |
Модифицированная дюрация |
8,3871995 |
|
|
|
19 |
Цена облигации исходя из доходности (Р) |
40,055698 |
|
|
|
20 |
Доходность к погашению (YTM) |
13,63% |
|
|
|
21 |
Текущая доходность |
8,633% |
|
|
|
22 |
Накопленный процент (НКД) |
2,53333 |
|
|
|
Рис. 11. Результаты анализа ОВВЗ