Января 1998; 1 марта 1998; 30 октября 1998; 15 февраля 1999; 1 апреля 1999.

Ставку дохода, соответствующую нулевой XNPV, определим с помощью функции

ЧИСТВНДОХ(значения; даты),

ЧИСТВНДОХ(А1:А5; В1:В5) = 37.49%.

Этот проект имеет смысл, если рыночная норма дохода меньше, чем вычисленное значение (k< 37.49%).

 

Функция МВСД

Функция возвращает модифицированную внутреннюю скорость оборота средств (внутреннюю норму дохода) для ряда последовательных периодических операций с наличными. Функ­ция МВСД учитывает как стоимость инвестиции, так и доход, получаемый от реинвестирования.

 

Формула для вычисления функции МВСД имеет вид:

 

 

(3.17)

n - общее число выплат и поступлений;

- положительные значения (поступления);

- отрицательные значений (выплаты);

r - норма прибыли, выплачиваемой за деньги, находя­щиеся в обороте;

f - норма прибыли, получаемой за деньги в обороте при реинвестировании.

 

Синтаксис МВСД(значения; финансовая_норма; реинвест_норма)

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

q Аргумент значения должен содержать по крайней мере одно положительное и одно отрицательное значение для того, чтобы можно было вычислить модифицированную внутреннюю скорость оборота. В противном случае функция МВСД возвращает значение ошибки #ДЕЛ/0!.

q Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако, ячейки, которые содержат нулевые значения, учитываются.

финансовая_норма - это норма прибыли, выплачиваемой за деньги, находящиеся в наличном обороте.

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

Замечания

q МВСД использует порядок расположения чисел в аргументе значения для определения порядка выплат и поступлений. Убедитесь, что значения выплат и поступлений введены в нужной последовательности и с правильными знаками (положительные значения для получаемых денег и отрицательные значения для выплачиваемых).

q Если n - это количество чисел в аргументе значения, f - это финансовая_норма, а r - это реинвест_норма, то формула для вычисления функции МВСД будет иметь вид:

(3.18)

 

 

 

 

Задача 33. Пять лет назад была взята ссуда в размере 1 млрд. руб. под 10% годовых для финансирования проекта, при­быль по которому за эти годы составила: 100, 270, 450, 340 и 300 млн. руб. За эти годы Вы реинвестировали получаемую прибыль под 12 процентов годовых. Найти модифицированную внутреннюю скорость оборота инвестиции.

Решение.

Введем в ячейку А1 значение -1 000 млн. руб., а в ячейки А2:А6 значения прибыли за каждый год - 100, 270, 450, 340 и 300 млн. руб.

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

МВСД(значения; финансовая_норма; реинвест_норма)

МВСД(А1:А6; 10%; 12%) = 12,25% процентам

 

4. Использование финансовых функций Excel
 по ценным бумагам.

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

Таблица 4.1. Описание аргументов финансовых функций Excel по ценным бумагам

	Аргумент	3начения аргумента
1.	базис	Принятый способ исчис­ления процентного периода (года, месяца).
2.	выкуп	Цена (в абсолютном выражении) или курс (в относительном выражении) при продаже ценной бумаги инвестором.
3.	Дата_вступления_в_силу	Календарная дата пога­шения (дата окончания действия ценной бумаги) или выкупа ценной бумаги у инвестора
4.	Дата_выпуска	Календарная дата выпуска ценной бумаги эмитентом.
5.	Дата_первой_выплаты	Календарная дата первой выплаты процентов по ценной бумаге.
6.	Дата_соглашения	Календарная дата покупки (дата инвестиций в ценные бумаги) инвестором.
7.	доход	Годовая доходносгь цен­ной бумаги в % (ставка помещения).
8.	ивестиция	Рыночная цена (в абсолютном выражении) или курс (в относительном выражении) ценной бума­ги при ее покупке инвес­тором.
9.	купон	Годовая ставка выплат по купонам s %.
10	номинал	Нарицательная стоимость (номинал) ценной бумаги.
11	первый_купон	Календарная дата первой выплаты процентов по ценной бумаге (см. дата_первой_выплаты).
12	периодичность	Количество выплат по купонам в течение года (см. частота).
13	погашение	Цена (в абсолютном выражении) или курс (в отно­сительном выражении) продажи ценной бумаги инвестором.
14	последняя_выплата	Календарная дата послед­ней выплаты процентов по ценной бумаге.
15	скидка	Учетная (дисконтная) ставка в % к цене погаше­ния.
16.	ставка	Годовая ставка выплат по купонам в %.
17	цена	Рыночная цена (в абсо­лютном выражении) или курс (в относительном выражении) ценной бумаги при покупке инвестором (см. инвестиция).
18	Частота	Количество выплат по купонам в течение года (см. периодичность).

 

Таблица 4.2. Варианты значения временного базиса в финансовых расчетах.

Тип базиса	Обозначение	Комментарий
0 или опущено	US (NASD) 30/360	Американский стандарт, месяц равен 30 дням, год - 360 дням.
1	Фактический /фактический	Фактически длина месяца и года (в том числе - високосный год равен 366 дням).
2	Фактически/ЗбО	Фактическая длина месяца, год равен 360 дням
3	Фактический/Зб5	Фактическая длина месяца, год равен Зб5 дням
4	Европейский 30/360	Европейский стандарт, месяц равен 30 дням, год - 360 дням

 

На практике применяются различные варианты расчета длительности периодов.

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

Встроенные финансовые функции разбиты для изучения на группы согласно специфике ценных бумаг. При необходи­мости приведены нестандартные формулы, используемые Excel, и даны к ним комментарии.

 

4.1 Функции расчета временных параметров операций
 с периодической выплатой процентов

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

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

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

	Аргумент	3начения аргумента
1.	Дата_соглашения	дата соглашения для ценных бумаг. Более поздняя, чем дата выпуска, она определяет дату, когда ценные бумаги были проданы покупателю.
2.	Дата_вступления_в_силу	Календарная дата погашения (дата окончания действия ценной бумаги) или выкупа ценной бумаги у инвестора. Эта дата определяет истечение срока действия ценных бумаг.
3	Периодичность	Количество выплат по купонам за год: ежегодные выплаты периодичность = 1 полугодовые выплаты периодичность = 2 ежеквартальные выплаты периодичность = 4.
4	Базис	Используемый способ вычисления дня.

Функция ДНЕЙКУПОН

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

ДНЕЙКУПОН(дата_соглашения; дата_вступления_в_силу; периодичность; базис)

Дата соглашения является датой продажи покупателю купона, например, облигации. Срок платежа представляет собой дату истечения срока действия купона. Пусть, например, облигация со сроком действия 30 лет выпущена 1 января 1996 года и была приобретена покупателем через шесть месяцев после своего выпуска. Датой выпуска будет являться 1 января 1996 года, датой соглашения - 1 июля 1996, а срок погашения такой облигации - 1 января 2026 года, то есть через 30 лет после даты выпуска.

Функция ЧИСЛКУПОН

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

ЧИСЛКУПОН(дата_соглашения;дата_вступления_в_силу;
периодичность;базис)

Функция ДАТАКУПОНДО

Возвращает последнюю дату выплаты купона, предшествующую дате соглашения (покупки) ценной бумаги:

ДАТАКУПОНДО(дата_соглашения;дата_вступления_в_силу; периодичность;базис)

Дата соглашения является датой продажи покупателю купона, например, облигации. Срок платежа представляет собой дату истечения срока действия купона. Пусть, например, облигация со сроком действия 30 лет выпущена 1 января 1996 года и была приобретена покупателем через шесть месяцев после своего выпуска. Датой выпуска будет являться 1 января 1996 года, датой соглашения - 1 июля 1996, а срок погашения такой облигации - 1 января 2026 года, то есть через 30 лет после даты выпуска.

Функция ДНЕЙКУПОНДО

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

ДНЕЙКУПОНДО(дата_соглашения;дата_вступления_в_силу;
периодичность;базис)

Функция ДАТАКУПОНПОСЛЕ

Возвращает следующую дату купонных платежей после даты соглашения (покупки) ценной бумаги:

ДАТАКУПОНПОСЛЕ(дата_соглашения;дата_вступления_в_силу;
периодичность;базис)

Функция ДНЕЙКУПОНПОСЛЕ

Возвращает число дней от даты соглашения до срока следующего купона.

ДНЕЙКУПОНПОСЛЕ(дата_соглашения; дата_вступления_в_силу;периодичность;базис)

При одинаковых параметрах ценных бумаг выполняется равенство:

ДНЕЙКУПОН = ДНЕЙКУПОНДО + ДНЕЙКУПОНПОСЛЕ

Задача 34. Облигации выпущены на следующих условиях:

 

аргументы	Значения аргументов	Номера ячеек
дата_соглашения	6.09.1999	В1
дата_вступления_в_силу	12.09.2001	В2
периодичность	2	В3
базис	1	В4

Определить:

1. Число дней в периоде купона для облигаций;

2. Количество купонов, которые могут быть оплачены между датой соглашения (покупки) и датой вступ­ления в силу (погашения) облигации;

3. Последнюю дату выплаты купона, предше­ствующую дате соглашения (покупки) ценной бумаги;

4. Количество дней, прошедших от момента купонного платежа, до даты соглашения (покупки) ценной бумаги;

5. Дату следующего купонного платежа по облигациям.

6. Количество дней, прошедших от даты соглашения (покупки) до очередного купонного платежа по облигациям.

Решение

Пусть в рассмотренном примере значения аргументов для расчета временных параметров находятся в ячейках с В1 по В4. Ячейки с В5 по В10 зарезервируем для расчета временных параметров облигаций.

 

№	Временные параметры облигаций	Расчетные функции и значения вычисляемых параметров	Номера ячеек
1	длительность периодов		В5
2	количество купонов		В6
3	последняя дата выплаты		В7
4	количество дней ДО		В8
5	дата следующего платежа		В9
6	количество дней ПОСЛЕ		В10

В ячейке В5 запишем функцию ДНЕЙКУПОН

=ДНЕЙКУПОН( В1; В2; В3; В4 ) = 184

Число дней в периоде купона для облигаций составит 184 дня.

В ячейке В6 запишем функцию ЧИСЛКУПОН

= Ч ИСЛКУПОН( В1; В2; В3; В4 ) = 5

Пять купонов, которые могут быть оплачены между датой соглашения (покупки) и датой вступ­ления в силу (погашения) облигации.

В ячейке В7 запишем функцию ДАТАКУПОНДО

= ДАТАКУПОНДО ( В1; В2; В3; В4 )

Предыдущая дата купона перед датой соглашения составит 12.03.1996

В ячейке В8 запишем функцию ДНЕЙКУПОНДО

= ДНЕЙКУПОНДО ( В1; В2; В3; В4 ) = 178

Количество дней, прошедших от момента купонного платежа, до даты соглашения (покупки) облигации составит 178.

В ячейке В9 запишем функцию ДАТАКУПОНПОСЛЕ

= ДАТАКУПОНПОСЛЕ ( В1; В2; В3; В4 ) = 12.09.96.

Следующий купонный платеж по облигациям 12.09.96.

В ячейке В10 запишем функцию ДНЕЙКУПОНПОСЛЕ

= ДНЕЙКУПОНПОСЛЕ ( В1; В2; В3; В4 ) = 6

От даты соглашения (покупки) до очередного купонного платежа по облигациям пройдет 6 дней.

 

4.2 Функции для расчетов ценных бумаг
 с периодической выплатой процентов

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

Функция ДОХОД

Возвращает оценку дохода в виде годовой ставки поме­щения по операциям с ценными бумагами при заданной ку­понной ставке и разности цен (курсов) покупки и погашения за указанный период действия ценной бумаги:

ДОХОД(дата_соглашения;дата_вступления_в_силу;ставка;цена;погашение;частота;базис)

q Дата_соглашения - это дата соглашения для ценных бумаг. Эта дата, более поздняя, чем дата выпуска, когда ценные бумаги были проданы покупателю.

q Дата_вступления_в_силу - это срок погашения для ценных бумаг. Эта дата определяет истечение срока действия ценных бумаг.

q Ставка - это годовая процентная ставка по купонам для ценных бумаг.

q Цена - это цена ценных бумаг на 100 руб. номинальной стоимости.

q Погашение - это цена при погашении за 100 руб. номинальной стоимости ценных бумаг.

q Частота - это количество выплат по купонам за год. Для ежегодных платежей частота = 1; для полугодовых платежей частота = 2;
для ежеквартальных платежей частота = 4.

q Базис - это тип используемого способа вычисления дней.

Расчет ведется согласно формуле годовой ставки по­мещения(годовая совокупная доходность). Функция учитывает, сколько периодов купонов укладывается до даты погашения, а также время от момента выплаты купона до даты соглашения.

Если только один или менее периодов купона укладываются до даты погашения, то функция ДОХОД вычисляется следующим образом:

 

A - количество дней от начала периода купона до даты соглашения (накопленные дни).

DSR - количество дней от даты соглашения до даты погашения.

E - количество дней в периоде купона.

Если более одного периода купона, то функция ДОХОД вычисляется итерационным методом (но не более 100 итера­ции), используя метод Ньютона на основе формулы для функции ЦЕНА. Доход изменяется до тех пор, пока вычис­ляемая цена для данного дохода не станет близкой к указан­ному значению аргумента цена.

Задача 35. Облигации выпущены на следующих условиях:

аргументы	Значения аргументов	Номера ячеек
Дата_соглашения	6.09.93	В1
Дата_вступления_в_силу	12.09.97	В2
Ставка	9%	В3
Цена	89	В4
Погашение	100	В5
Частота	2	В6
Базис	европейский	В7
Доход		В8

Определить доход в виде годовой ставки поме­щения по операциям с облигациями.

Пусть в рассмотренном примере значения аргументов для расчета дохода находятся в ячейках с В1 по В7. Ячейку В8 зарезервируем для расчета дохода в виде годовой ставки поме­щения по операциям с облигациями.

В ячейке В8 запишем функцию ДОХОД

= ДОХОД ( В1; В2; В3; В4;В5;В6;В7 ) = 12.57%

ДОХОД(6.09.93; 12.09.97; 0.09; 89; 100; 2; 1) = 12.57%

Доход в виде годовой ставки поме­щения по операциям с облигациями составит 12.57%.

С помощью команды CEPBИC\Подбор параметра можно определить значение аргумента ставка для задаваемого уровня дохода ценной бумаги.

Задача 36.

Для примера, рассмотренного в задаче 35 требует­ся определить размер купонной ставки, если годовая ставка дохода составляет 15%. Пусть в рассмотренном примере значения параметров для расчета функции ДОХОД находятся в ячейках с В1 по В7. В ячейке В8 запишем функцию ДОХОД

= ДОХОД(В1;В2;В3;В4;В5;В6;В7)

Курсор установим в ячейку В8 и вызовем команду CEPBИC\Подбор параметра.

В раскрывшемся диалоговом окне установим:

q В поле Установить значение – В8;

q В поле Значение – 0,15;

q В поле Изменяя значение ячейки – В3.

В результате выполнения команды CEPBИC\Подбор параметра в ячейке В3 будет находиться значение размера купонной ставки равное 11,564%.

Функция ЦЕНА

Функция рассчитывает курс (цену) покупки ценной бу­маги с периодическими платежами на момент ее приобрете­ния и возвращает цену за 100 руб. нарицательной стоимости ценных бумаг, по которым выплачивается периодический процент.

ЦЕНА(дата_соглашения;дата_вступления_в_силу;ставка;доход;погашение;частота;базис)

Дата_ - это срок погашения для ценных бумаг. Эта дата определяет истечение срока действия ценных бумаг.