Финансовые функции MS Excel для вкладных и кредитных операций

Ответ:14,93%.

Задача 15. Клиенту банка необходимо накопить 200 тыс. руб. за 2 года. Клиент обязуется вносить в начале каждого месяца постоянную сумму под 9% годовых. Какой должна быть эта сумма?

Ответ: 7,58 тыс. р.

Задача 16. Клиент банка осуществляет заем в размере 5000 руб. под 6% годовых на 6 месяцев. Определить ежемесячные платежи клиента. Платежи осуществляются в конце месяца.

Ответ: 847,98 р.

Задача 17. Определить платежи по процентам за первый месяц от трехгодичного займа в 100000 руб. из расчета 10% годовых.

Ответ: 833,33 р.

Задача 18. Клиент ежегодно в течение 5 лет вносил деньги на свой счет в банке и накопил 40 000 руб. Определить, какой доход получил клиент банка за последний год, если годовая ставка составила 13,5%.

Ответ: 4030,77 р.

Задача 19. Определить значение основного платежа для первого месяца двухгодичного займа в 60000 руб. под 12% годовых.

Ответ:2 224,41р.

Задача 20. Организация взяла ссуду в банке в размере 500 тыс. руб. на 10 лет под 10,5% годовых; проценты начисляются ежемесячно. Определить сумму выплат по процентам за первый месяц и за третий год периода.

Ответ:4,375 тыс. р., 44,143 тыс. р.

Финансовые функции  MS Excel для анализа ценных бумаг

1. Необходимые сведения

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

 

Аргументы функций для анализа ценных бумаг

Наименование	Значение	Примечание
Дата_согл	Дата приобретения	Должен вводиться с использованием функции ДАТА
Дата_вступл_в_силу	Дата погашения	Должен вводиться с использованием функции ДАТА. Дата погашения должна быть позже даты приобретения.
Частота	Количество купонных выплат в год	Допускаются только три значения: 1 –  для ежегодных выплат; 2 –  для полугодовых; 4 –  для ежеквартальных.
Базис	Правило начисления количества дней в месяце и году	Принимаемые значения: 0 или  отсутствие – американский метод  (NASD): 30 дней в месяц/360 дней в году (при следующих условиях: если начальная дата является 31-м числом месяца, то она полагается равной 30-му числу того же месяца; если конечная дата является 31-м числом месяца и начальная дата меньше, чем 30-е число, то конечная дата полагается равной 1-му числу следующего месяца, в противном случае конечная дата полагается равной 30-му числу того же месяца); 1 - фактическое  количество дней в месяце/фактическое  количество дней в году; 2 - фактическое  количество дней в месяце/360 дней  в году; 3 - фактическое  количество дней в месяце/365 дней  в году; 4 –  европейский метод: 30 дней в месяце/360 дней в году (при условии: начальная  и конечная даты, которые приходятся  на 31-е число месяца, полагаются  равными 30-му числу того же  месяца)
Купон	Купонная ставка	Аргумент должен быть положительным  числом
Доход	Требуемая норма доходности (рыночная ставка)	Аргумент должен быть выражен в  процентах.
Ставка	Купонная ставка
Цена	Рыночная цена покупки (% от номинала)
Дата_выпуска	Дата выпуска облигации	Должен вводиться с использованием функции ДАТА
Первый_купон	Дата первой купонной выплаты	Должен вводиться с использованием функции ДАТА. Дата первой купонной выплаты должна быть позже даты приобретения.
Погашение	Сумма погашения (% от номинала)
Последняя выплата	Дата последней купонной выплаты для ценных бумаг	Должен вводиться с использованием функции ДАТА. Дата приобретения должна быть позже даты последней купонной выплаты.
Инвестиция	Объем инвестиции
Купон	Купонная ставка	Аргумент должен быть положительным  числом.
Номинал	Номинальная стоимость	Аргумент может быть задан как  в виде абсолютной величины, так  и в процентах.
Первый_доход	Дата первой купонной выплаты	Должен вводиться с использованием функции ДАТА.
Метод_выч	Логическое значение определяет способ вычисленных процентов	Аргумент принимает следующие  значения: «истина» или опущен –  для расчета с даты выпуска ценной бумаги, «ложь» - для расчета с даты последнего купона.
Дата_погашения	Дата погашения	Должен вводиться с использованием функции ДАТА.
Скидка	Учетная ставка	Если значение аргумента не является целым числом, то оно усекается. Если значение аргумента < 0, функция возвращает значение ошибки #ЧИСЛО! Если  значение аргумента = 0, функция возвращает значение ошибки #ДЕЛ/О!
Дроб_руб	Дробь в виде «целая часть, числитель»	Разрядности числителя и знаменателя  дроби должны быть согласованы. Если знаменатель – двузначное число, числитель должен быть задан так  же.
Дес_руб	Десятичное число
Дроб	Знаменатель	Если значение аргумента не является целым числом, то оно усекается. Если значение аргумента < 0, функция возвращает значение ошибки #ЧИСЛО! Если  значение аргумента = 0, функция возвращает значение ошибки #ДЕЛ/О!

 

Функция ДАТАКУПОНДО. Вычисляет порядковый номер даты предыдущей купонной выплаты до даты приобретения облигации. Чтобы просмотреть полученный результат в виде даты, нужно задать формат ячейки Краткая дата или Длинный формат даты.

Синтаксис: ДАТАКПОНДО (дата_согл; дата_вступл_в_силу; частота; базис).

Функция ДАТАКУПОНПОСЛЕ. Вычисляет порядковый номер даты следующей купонной выплаты от даты приобретения облигации. Чтобы просмотреть полученный результат в виде даты, нужно задать формат ячейки Краткая дата или Длинный формат даты.

Синтаксис: ДАТАКУПОНПОСЛЕ (дата_согл; дата_вступл_в_силу; частота; базис).

Функция ДЛИТ. Вычисляет средневзвешенную продолжительность платежей или дюрацию Маколея (Macaulay duration) ценной бумаги. Синтаксис: ДЛИТ (дата_согл; дата_вступл_в_силу; купон; доход; частота; базис).

Функция ДНЕЙКПОН. Вычисляет количество дней в период купонной выплаты, в течение которого была приобретена облигация. Синтаксис: ДНЕЙКПОН (дата_согл; дата_вступл_в_силу; частота; базис).

Функция ДНЕЙКУПОНДО. Вычисляет количество дней, прошедших с начала периода купонной выплаты до приобретения облигации. Синтаксис: ДНЕЙКПОНДО (дата_согл; дата_вступл_в_силу; частота; базис).

Функция ДНЕЙКУПОНПОСЛЕ. Вычисляет количество дней, оставшихся до даты ближайшей купонной выплаты с момента приобретения облигации. Синтаксис: ДНЕЙКПОНПОСЛЕ (дата_согл; дата_вступл_в_силу; частота; базис).

Функция ДОХОД. Вычисляет доходность облигации к погашению (yield to maturity, YTM). Доходность к погашению представляет собой процентную ставку, устанавливающую равенство между текущей стоимостью потока платежей по облигации PV и ее рыночной ценой P Синтаксис: ДОХОД (дата_согл; дата_вступл_в_силу; ставка; цена; погашение; частота; базис).

Функция ДОХОДКЧЕК. Вычисляет доходность краткосрочной облигации к погашению по простым процентам в виде годовой ставки. Синтаксис: ДОХОДКЧЕК (дата_согл; дата_вступл_в_силу; цена).

Функция ДОХОДПЕРВНЕРЕГ. Вычисляет доходность облигации с первым периодом купонной выплаты, отличающимся от остальных. Синтаксис: ДОХОДПЕРВНЕРЕГ (дата_согл; дата_вступл_в_силу; дата_выпуска; первый_купон; ставка; цена; погашение; частота; базис).

Функция ДОХОДПОГАШ. Вычисляет доходность к погашению ценной бумаги с выплатой процентов при погашении (в России – депозитного или сберегательного сертификата). Синтаксис: ДОХОДПОГАШ (дата_согл; дата_вступл_в_силу; дата_выпуска; ставка; цена; базис).

Функция ДОХОДПОСЛНЕРЕГ. Вычисляет доходность облигации с последним периодом купонной выплаты, отличающимся от остальных. Синтаксис: ДОХОДПОСЛНЕРЕГ (дата_согл; дата_вступл_в_силу; последняя_выплата; ставка; цена; погашение; частота; базис).

Функция ДОХОДСКИДКА. Вычисляет годовую доходность облигации, реализуемой с дисконтом (скидкой). Функция ДОХОДСКИДКА отличается от функции ДОХОДЧЕК тем, что, во-первых, цена погашения, используемая при расчете, может отличаться от номинала (то есть возможна перепродажа облигации до погашения) и, во-вторых, не требуется умножение на поправочный коэффициент. Синтаксис: ДОХОДСКИДКА (дата_согл; дата_вступл_в_силу; цена; погашение; базис).

Функция ИНОРМА. Вычисляет доходность (в виде годовой ставки, рассчитанной по простым процентам) финансовой операции, сущность проведения которой заключается в инвестировании некоторой суммы на дату начала операции и последующего получения суммы по завершении операции. Синтаксис: ИНОРМА (дата_согл; дата_вступл_в_силу; инвестиция; погашение; базис).

Функция МДЛИТ. Вычисляет модифицированную дюрацию (modified duration, MD) для ценной бумаги. Модифицированная дюрация отличается от дюрации Маколея (см. функцию ДЛИТ) тем, что учитывает периодичность начисления процентов по ценной бумаге. Синтаксис: МДЛИТ (дата_согл; дата_вступл_в_силу; купон; доход; частота; базис)

Функция НАКОПДОХОД. Вычисляет величину накопленного к дате операции процентного (купонного) дохода (accrued interest). Накопленный купонный доход имеет значение, когда облигация покупается или продается в момент времени между двумя купонными выплатами. Синтаксис: НАКОПДОХОД (дата_выпуска; первый_доход; дата_согл; ставка; номинал; частота; базис; метод_выч).

Функция НАКОПДОХОДПОГАШ. Вычисляет величину абсолютного дохода на момент погашения по ценной бумаге с выплатой процентов при погашении. Синтаксис: НАКОПДОХОДПОГАШ (дата_выпуска; дата_погашения; ставка; номинал; базис).

Функция ПОЛУЧЕНО. Вычисляет сумму, полученную к сроку погашения ценной бумаги по известному значению учетной ставки. Синтаксис: ПОЛУЧЕНО (дата_согл; дата_вступл_в_силу; инвестиция; скидка; базис)

Функция РАВНОКЧЕК. Вычисляет показатель эквивалентного годового купонного дохода по известной величине учетной ставки. Синтаксис: РАВНОКЧЕК (дата_согл; дата_вступл_в_силу; скидка).

Функция РУБЛЬ.ДЕС. Преобразует цену, представленную в виде натуральной дроби, в цену, выраженную десятичным числом. Синтаксис: РУБЛЬ.ДЕС (дроб_руб; дроб).

Функция РУБЛЬ.ДРОБЬ. Преобразует цену в рублях, выраженную десятичным числом, в цену в рублях, представленную в виде дроби. Синтаксис: РУБЛЬ.ДРОБЬ (дес_руб;дроб).

Функция СКИДКА. Вычисляет учетную ставку, соответствующую цене покупки ценной бумаги и эквивалентную ее доходности к погашению. Синтаксис: СКИДКА (дата_согл; дата_вступл_в_силу; цена; погашение; базис).

Функция ЦЕНА. Вычисляет цену за 100 рублей номинальной стоимости ценных бумаг, по которым выплачивается периодический процент. Синтаксис: ЦЕНА (дата_согл; дата_вступл_в_силу; ставка; доход; погашение; частота; базис).

Функция ЦЕНАКЧЕК. Вычисляет курсовую цену облигации. Под курсовой ценой понимается текущая цена облигации в расчете на 100 денежных единиц его номинала. Оценка стоимости краткосрочной бескупонной облигации заключается в определении современной величины элементарного потока платежей по формуле простых процентов, исходя из требуемой нормы доходности (рыночной ставки). Функция предназначена для использования в тех случаях, когда ценная бумага держится до погашения Синтаксис: ЦЕНАКЧЕК (дата_согл; дата_вступл_в_силу; скидка). Аргументы функции ЦЕНАКЧЕК такие же как и у функции РАВНОКЧЕК.

Функция ЦЕНАПЕРВНЕРЕГ. Вычисляет текущую стоимость облигации с первым периодом купонной выплаты, отличающимся от остальных. Синтаксис: ЦЕНАПЕРВНЕРЕГ (дата_согл; дата_вступл_в_силу; дата_выпуска; первый_купон; ставка; доход; погашение; частота; базис).

Функция ЦЕНАПОГАШ. Вычисляет курсовую стоимость ценной бумаги с выплатой процентов при погашении, соответствующую требуемой норме доходности инвестора (рыночной ставке). Синтаксис: ЦЕНАПОГАШ (дата_согл; дата_вступл_в_силу; дата_выпуска; ставка; доходность; базис).

Функция ЦЕНАПОСЛНЕРЕГ. Вычисляет текущую стоимость облигации с последним периодом купонной выплаты, отличающимся от остальных. Синтаксис: ЦЕНАПОСЛНЕРЕГ (дата_согл; дата_вступл_в_силу; последняя_выплата; ставка; цена; погашение; частота; базис).

Функция ЦЕНАСКИДКА. Вычисляет курсовую стоимость облигации, реализуемой с дисконтом (скидкой). Функция ЦЕНАСКИДКА отличается от функции ЦЕНАКЧЕК тем, что, во-первых, цена погашения, используемая при расчете, может отличаться от номинала (то есть возможна перепродажа облигации до погашения) и, во-вторых, не требуется умножение на поправочный коэффициент. Синтаксис: ЦЕНАСКИДКА (дата_согл; дата_вступл_в_силу; скидка; погашение; базис).

Функция ЧИСЛКУПОН. Вычисляет количество купонных выплат, которые могут быть оплачены в периоде между датой приобретения и датой погашения. Синтаксис: ЧИСЛКУПОН (дата_согл; дата_вступл_в_силу; частота; базис).

2. Примеры решения задач

Задача 1. Рассматривается возможность приобретения облигаций трех типов, каждая из которых с номиналом в 100 руб. и сроком погашения 9.10.2007 г. Курсовая стоимость этих облигаций на дату 25.07.2005 г. составила соответственно 90, 80 и 85 руб.

Годовая процентная ставка по купонам (размер купонных выплат) составляет:

• для первой облигации 8 % при полугодовой периодичности выплат;
• для второй облигации – 5 % при ежеквартальной периодичности выплат;
• для третьей облигации – 10 % с выплатой 1 раз в год. 

Расчеты ведутся в базисе фактический/фактический.

Провести анализ эффективности  вложений в покупку этих облигаций, если требуемая норма доходности составляет 15%.

Алгоритм решения  задачи.

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

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

Выполним также расчет доходности, непосредственно задавая  значения аргументов в функции ДОХОД.

Рис.2. Применение функции ДОХОД для оценки доходности облигаций

Аргументы, содержащие даты, введем с помощью функции ДАТА (можно также указывать ссылки на ячейки, содержащие даты).

Для облигации первого  типа:

=ДОХОД (ДАТА(2005;7;25);ДАТА(2007;10;9);8%;90;100;2;1)= 13,36%

Для облигации второго  типа:

=ДОХОД (ДАТА(2005;7;25);ДАТА(2007;10;9);5%;80;100;4;1)= 15,93%

Для облигации третьего типа:

=ДОХОД (ДАТА(2005;7;25);ДАТА(2007;10;9);10%;85;100;1;1)= 18,83%

Результаты, полученные различными способами, совпадают.

Доходность по второй и  третьей облигациям (15,93% и 18,83% соответственно) выше заданной нормы (15%), а по первой облигации (13,36%) –  ниже. Следовательно, целесообразно покупать облигации  второго и третьего типов.

 

Задача 2. Коммерческий банк предлагает свои сберегательные сертификаты номиналом 100000 руб. сроком на 8 месяцев. Дата соглашения – 10.01.2005 г. Цена продажи составляет 85000 руб. Способ вычисления дня –  фактический/360. Необходимо определить доход за этот период.

Алгоритм решения  задачи.

Для вычисления доходности данной финансовой операции,  возвращающейся в виде годовой ставки, рассчитанной по простым процентам, используем  функцию  ИНОРМА. Исходные данные задачи представим в виде таблицы. В соответствующую ячейку введем формулу, обеспечивающую вычисление доходности сберегательного сертификата (рис.3).

Для проверки правильности результата в функцию ИНОРМА введем значения аргументов в непосредственном виде:

= ИНОРМА (ДАТА(2005;1;10);ДАТА(2005;9;10);85000;100000;2) = 26,14%

Результаты вычислений совпадают.

Рис.3. Иллюстрация применения функции ИНОРМА для оценки доходности сертификатов

3. Задачи

Задача 1. Облигация номиналом в 10 000 руб. и сроком погашения 20.07.2008 г. приобретена 5.05.2005 г. Выплаты по купонам осуществляются каждые полгода при способе вычисления дня – фактический/365. Необходимо определить:

• количество предстоящих купонных выплат;
• дату предшествующей купонной выплаты;
• дату следующей купонной выплаты;
• длительность купонного периода;
• количество дней от начала действия периода до даты соглашения;
• количество дней  от даты соглашения до даты следующего периода.