Встроенные функции прикладного пакета Microsoft Excel
Лабораторная работа 11
Для проведения финансовых операций используют следующие встроенные функции прикладного пакета Microsoft Excel:
1) БС(ставка; кпер; платеж; нc; [тип]) - позволяет определить будущее значение потока платежей, т.е. величину FV;
2) КПЕР(ставка; платеж; нз; бс; [тип]) - определяет количество периодов начисления процентов;
3) СТАВКА(кпер; платеж; нз; бс; [тип]) - определяет величину процентной ставки по инвестиции;
4) ПС(ставка; кпер; платеж; бс; [тип]) - определяет приведенную к текущему моменту стоимость денежной суммы;
5) НОМИНАЛ(эф_ставка; кол_пер ) - определяет номинальную ставку при известной эффективной;
6) ЭФФЕКТ(ном_ставка; кол_пер) - эффективная ставка.
Большинство функций имеют одинаковый набор базовых аргументов: ставка – процентная ставка (норма доходности или цена заемных средств r); кпер – срок (число периодов n) проведения операции; выплата – величина периодического платежа (CF); нз – начальное значение (величина PV); бс – будущее значение (FV); [тип] – тип начисления процентов (1 – начало периода, 0 – конец периода), необязательный аргумент.
Рассмотрим определение будущей стоимости инвестиций.
Пример 1.Определить будущую величину вклада в 10 000 р., помещенного в банк на 5 лет под 5 % годовых, если начисление процентов осуществляется: а) раз в году; б) раз в месяц.
Для решения воспользуемся функцией БС(ставка; кпер; платеж; нc; [тип]). Вид формулы для решения:
а) БС(0,05; 5; 0; -10 000) =12 762,82 р.;
б) БС(0,05/12; 5*12; 0; -10 000) = 12 833,59 р.
Значение процентной ставки (аргумент «ставка») обычно задается в виде десятичной дроби: 5 % – 0,05; 10 % – 0,1; 100 % – 1 и т.д. Если начисление процентов осуществляется m-раз в году, аргументы необходимо откорректировать соответствующим образом:
r = r/m и n = n 
m.
Аргумент функции «начальное значение – нз» задан в виде отрицательной величины (-10 000 р.), так как с точки зрения вкладчика эта операция влечет за собой отток его денежных средств в текущем периоде с целью получения положительной величины (12 762,82 р.) через 5 лет.
Однако для банка, определяющего будущую сумму возврата средств по данному депозиту, этот аргумент должен быть задан в виде положительной величины, так как означает поступление средств (увеличение пассивов): БС(0,05; 5; 0; 10 000) = -12 762,82 р. Полученный же при этом результат – отрицательная величина, так как операция означает расходование средств (возврат денег банком вкладчику).
Аргумент «выплата» не используется при анализе элементарных потоков, поэтому здесь и в дальнейшем он имеет нулевое значение.
Часто возникает необходимость сравнения условий финансовых операций, предусматривающих различные периоды начисления процентов. В этом случае осуществляют приведение соответствующих процентных ставок к их годовому эквиваленту с помощью эффективной процентной ставки.
Для вычисления эффективной и номинальной процентных ставок используют функции НОМИНАЛ() и ЭФФЕКТ(). Эти функции удобно использовать при сравнении операций с различными периодами начисления процентов. При этом доходность финансовой операции обычно измеряется эффективной процентной ставкой.
Пример 2.Ставка банка по срочным валютным депозитам составляет 18 % годовых. Какова реальная доходность вклада (т.е. эффективная ставка) если проценты выплачиваются:
а) ежемесячно ЭФФЕКТ(0,18; 12) = 0,1956 или 19,56 %;
б) раз в год ЭФФЕКТ(0,18; 1) = 0,18 или 18 %.
Функция НОМИНАЛ()выполняет обратное действие, т.е. позволяет определить номинальную ставку по известной величине эффективной. Например, НОМИНАЛ(0,1956; 12) = 0,1799 или 18 %.
Автоматизацию расчетов при помощи функций ПС(), КПЕР() и СТАВКА() рассмотрим в примерах 3 и 4.
Пример 3.Выплаченная по 4-х летнему депозиту сумма составила величину в 14 641 р. Определить первоначальную величину вклада, если ставка по депозиту равна 10 % годовых.
ПС(0,1; 4; 0; 14 641; 0)= 10 000 р.
Функция КПЕР (ставка; выплата; нз; бс; [тип]) - вычисляет количество периодов начисления процентов, исходя из известных величин r, FV и PV.
Пример 4.По вкладу в 10 000 р., помещенному в банк под 5 % годовых, начисляемых ежегодно, была выплачена сумма 12 762,82 р. Определить срок проведения операции (количество периодов начисления).
КПЕР(0,05; 0; -10 000; 12 762,82) = 5 лет.
Соответственно при начислении процентов раз в месяц, число необходимых периодов будет равно:
КПЕР(0,05/12; 0;-10 000;12 762,82) =60 месяцев.
Следует обратить особое внимание на то, что результатом применения функции является число периодов (а не число лет), необходимое для проведения операции.
Функция СТАВКА() - вычисляет процентную ставку, которая в зависимости от условий операции может выступать либо в качестве цены, либо в качестве нормы ее рентабельности.
Определим процентную ставку для примера 4.
СТАВКА(5; 0; -10000; 12 762,82,0,0) = 0,05 или 5 %.
Результат вычисления величины r выдается в видепериодической процентной ставки. Для определения годовой процентной ставки, полученный результат следует умножить на количество начислений в году.
Необходимо помнить, что для получения корректного результата при работе функций КПЕР() и СТАВКА(), аргументы «нз» и «бс» должны иметь противоположные знаки. Данное требование вытекает из экономического смысла подобных операций.
Группу функций Microsoft Excel, предназначенную для автоматизации расчетов характеристик аннуитетов, составляют функции БС(), КПЕР(), НОРМА(), ПЗ(), к которым добавляется функция определения периодического платежа – ПЛТ().
Пример 1. Господин Иванов в конце каждого месяца переводит 1 000 р. на счет в банк, начисляющий ежемесячно сложные проценты по номинальной ставке 9 % годовых. Какая сумма накопится на счете за два года, при сохранении на это время всех указанных условий без изменения?
Рисунок 1 -Применение функции БС для расчета будущей стоимости аннуитета
В зависимости от выбора пользователем из полного списка аргументоввстроеннойфункцииБС() (рисунок 1) подмножества тех аргументов, значения которых известны в задаче, можно с помощью одной и той же функции посчитать и наращенную сумму вклада, и будущую стоимость аннуитета, причем с переключением формул между типами потоков платежей постнумерандо и пренумерандо. Рассмотрим возможные варианты:
1) 1,61 р. = БС (0,1;5;0;-1;0) – будущая стоимость одного вложенного рубля (нз = -1) после пяти раз (число_периодов = 5) присоединения к нему процентных денег, начисляемых в конце периода по ставке сложных процентов 10 % (норма = 0,1) без дополнительных поступлений и выплат (выплата = 0, тип = 0);
2) 6,11 р. = БС (0,1;5;-1;0;0) – будущая стоимость потока пяти периодических платежей (число_периодов = 5) единичного размера, вносимых (выплата = -1) регулярно в конце периода (потоку постнумерандо соответствует тип = 0, значение используется по умолчанию) при начислении 10 % сложных (норма=0,1) за период между моментами внесения платежей на поступившие ранее средства;
3) 6,72 р. = БС (0,1;5;-1;0;1) – будущая стоимость потока пяти периодических платежей (число_периодов = 5) единичного размера
(выплата = -1), поступающих в начале периода (потоку пренумерандо соответствует тип = 1) при начислении за каждый период между платежами 10 % сложных (норма = 0,1).
Функция ПЛТ(ставка;кпер;пс;бс;тип) - возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.
Пример 2.Требуется определить размер периодическогоплатежа при заданной будущей величине фонда в 46 410 р., процентной ставке в 10 % годовых при ежемесячном начислении процентов. Период анализа 4 года.
ПЛТ(0,1/12; 4*12; 0; 46 410) = -790,33 р.
Условиями данной операции наличие первоначальной суммы на депозите в момент времени t = 0 не предусмотрено, поэтому значение параметра «пс» равно нулю.
Пример 3.Финансовая компания создает фонд для погашения обязательств путем помещения в банк суммы в 50 000 р., с последующим ежегодным пополнением суммами по 10 000 р. Ставка по депозиту равна 10 % годовых. Какова будет величина фонда к концу 4-го года ?
БС(0,1; 4; -10 000; -50 000)= 119 615,0 р.
Соответственно изменится и формат функции для определения величины ежегодного платежа: ПЛТ(0,1; 4; -50 000; 119 615) = -10 000,0 р.
Необходимо отметить, что начисление процентов в начале каждого периода всегда приводит к большему значению будущей величины аннуитета за тот же срок.