В финансовой практике часто встречаются операции, характеризующиеся возникновением потоков платежей (денежных поступлений и выплат), распределенных во времени. Потоки платежей, при которых выплаты (поступления) денежных средств осуществляются равными суммами через одинаковые интервалы времени, принято называть обыкновенным или регулярным аннуитетом. Как правило, подобные потоки платежей возникают при проведении кредитно-депозитных операций, формировании различных фондов, долгосрочной аренде и т.п.
Количественный анализ финансовых данных в Excel при проведении таких операций сводится к исчислению следующих основных характеристик:
текущей величины (present value – PV) потока платежей;
будущей величины (future value – FV) потока платежей;
величины отдельного платежа (payment – P);
нормы доходности (цены) в виде процентной ставки (interest rate – r);
числа периодов проведения финансовой операции (например, лет, месяцев, и т.д.).
Используемые при этом методы базируются на технике исчисления сложных процентов.
Табличный процессор Excel предоставляет широкие возможности по моделированию подобных расчетов при помощи соответствующих встроенных в Excel финансовых функций: БC(), КПЕР(), СТАВКА(), ПЛТ(), ПС().
Все функции данной группы имеют одинаковый набор базовых аргументов:
процентная ставка (норма доходности или цена заемных средств);
срок (число периодов) проведения операции;
величина периодического платежа;
начальная сумма;
будущая стоимость (величина) денежных средств;
тип начисления процентов (1- начало периода, 0 – конец периода).
Рассмотрим применение вышеперечисленных функций в проведении финансовых расчетов и анализе обыкновенных аннуитетов на конкретном примере.
Предположим, Ваша фирма решила создать специальный фонд для погашения своих долгосрочных обязательств (кредитов, займов), срок погашения которых наступит, например, через 5 лет, путем периодического (ежегодного) пополнения депозита в банке. Начальная сумма депозита составляет 10000 тыс.руб. Размер ежегодных платежей – 1000 тыс.руб. Процентная ставка по банковскому депозиту – 15%.
Необходимо определить величину фонда к концу 5-го года.
Для решения поставленной задачи выполните следующие действия:
1) Введите исходные данные на рабочий лист. Диапазон таблицы С3:С6 содержит входные данные для расчета величины денежного фонда, т.е. будущей стоимости вложений (инвестиций).
!!! Введите исходные данные в новый лист
2) Установите курсор в ячейку рабочего листа, где будет рассчитана будущая стоимость вложений (депозита), в данном случае в ячейку C7.
3) Выполните команду Вставка/ Функция… или нажмите кнопку 
Стандартной панели инструментов. В категории финансовые из списка справа выберите функцию БC(). Нажмите ОК.
4) Excel выведет окно ввода аргументов выбранной функции. Введите в каждое поле запроса ссылку на ячейку, содержащую требуемое значение.
Функция БС() – позволяет определить будущую величину вклада (Future Value - FV) на основе периодических постоянных платежей при заданных величинах процентной ставки, числа периодов выплат и начальной суммы вклада.
Функция имеет следующий синтаксис:
=БС(норма;число_периодов;выплата;нз;тип),
где: норма – процентная ставка (норма доходности по депозиту);
число_периодов – срок (число периодов) проведения операции;
выплата – величина периодического платежа;
нз – начальная стоимость вложений (депозита);
тип – тип начисления процентов, является необязательным аргументом.
(0 – в конце периода; 1 – в начале периода). По умолчанию начисление процентов осуществляется в конце периода.
Следует обратить внимание на особенности задания аргументов:
Если процентная ставка задается как абсолютная величина, она должна иметь вид десятичной дроби, например как в примере: 15% - 0,15. Периодический платеж и начальная сумма задаются со знаком минус, т.к. в данной операции для фирмы они означают выплаты (расходование) денежных средств. Такие правила применимы для всех финансовых функций. В зависимости от условия поставленной задачи, значения периодического платежа и начальной стоимости могут быть введены в функцию как в виде положительных, так и отрицательных величин. Это зависит от того, какой экономический субъект проводит подобные расчеты. Кроме того, можно на этапе ввода аргументов определить конечный результат, возвращаемый функцией, который отображается внизу диалогового окна ввода аргументов.
Microsoft Excel при расчетах характеристик денежных аннуитетов выражает каждый показатель исходя из следующего соотношения:
,
где: НC – начальная (текущая) стоимость вклада;
БСi – будущая стоимость вклада через число периодов i;
норма – процентная ставка (норма доходности);
выплата – периодический платеж;
i – порядковый номер периода поведения финансовой операции;
тип – тип начисления процентов.
Таким образом, будущая стоимость вложений определяется по формуле:
,
Таким образом, для нашего предприятия будущее значение банковского депозита в конце 5-го года будет следующим:
=БС(0,15;5;-1000;-10000) (Возвращаемый результат: 26855,95 тыс.руб.)
Для банка, определяющего будущую сумму возврата средств по данному депозиту, функция имела бы следующий вид:
=БС(0,15;5;1000;10000) (Возвращаемый результат: -26855,95 тыс.руб.)
!!! Ознакомьтесь с работой функции БС(). Получите результат, приведенный выше самостоятельно
Функция КПЕР() – позволяет определить число выплат (поступлений) денежных средств, если известны процентная ставка, периодический платеж, начальная и будущая величины потоков платежей.
Предположим, необходимо определить количество периодов платежей (в данном примере – число лет). Функция будет выглядеть следующим образом:
=КПЕР(0,15;1000;10000;26855,95) (Возвращаемый результат: 5)
где: 0,15 – процентная ставка по депозиту; 1000 – периодический платеж; 10000 – первоначальная сумма депозита; 26855,95 – будущая величина депозита.
!!! Ознакомьтесь с работой функции КПЕР(). Получите результат, приведенный выше самостоятельно
Функция СТАВКА() – вычисляет процентную ставку, которая в зависимости от условий операции может выступать либо в качестве цены, либо в качестве нормы рентабельности данной операции. Особенностью ее применения является возможность использования необязательного параметра «прогноз» - предполагаемое значение процентной ставки (от 0 до 1 в долях). Если он не определен, то по умолчанию принимается значение 0,1 (10%).
Пусть в нашем примере будет неизвестна процентная ставка по банковскому депозиту. Тогда для ее расчета воспользуемся функцией СТАВКА(), синтаксис которой следующий:
=СТАВКА(5;-1000;-10000;26855,95) (Возвращаемый результат: 15%)
Для корректной работы функции платеж и сумма вклада должны быть заданы в виде отрицательных величин.
!!! Ознакомьтесь с работой функции СТАВКА(). Получите результат, приведенный выше самостоятельно
Функция ПЛТ() – применяется в том случае, если необходимо определить величину периодического платежа по ссуде при заданных величинах будущей стоимости вложений на основе постоянных выплат, срока, процентной ставки и настоящей стоимости вложений.
Формула расчета периодического платежа исходя из вышеприведенного соотношения будет иметь следующий вид:
,
Предположим, в нашем примере необходимо определить величину периодического платежа при заданных входных параметрах. Функция будет иметь следующий вид:
=ПЛТ(0,15;5;-10000;26855,95) (Возвращаемый результат: -1000)
Полученный отрицательный результат для фирмы означает отток денежных средств. Для банка, соответственно, наоборот.
!!! Ознакомьтесь с работой функции ПЛТ(). Получите результат, приведенный выше самостоятельно
Функция ПС() – позволяет определить текущую (т.е. на момент начала операции – present value) стоимость аннуитета, если известны 4 обязательных параметра (процентная ставка; число периодов; начальная стоимость; будущая стоимость денежных средств).
Для условия нашей задачи применение данной функции позволяет получить ответ на вопрос: «Какую сумму необходимо вложить в банк на депозит, чтобы получить через 5 лет величину вклада 26855,95 тыс.руб. при ежегодном пополнении вклада на 1000 тыс.руб., если годовая банковская ставка составляет 15%?».
Формула для определения текущей (настоящей) стоимости вклада (ПC):
,
Для нашего примера синтаксис функции будет следующим:
=ПС(0,15;5;-1000;26855,95) (Возвращаемый результат: -10000)
В случае, если периодичность выплат (поступлений) отлична от годовой, для любой из рассмотренных функций в данном разделе достаточно скорректировать соответствующим образом аргументы норма, и число периодов (i).
Допустим, при ежеквартальном пополнении вклада и начисления процентов, функция ПС() примет следующий вид:
=ПС(0,15/4;5*4;-1000;26855,95) (Возвращаемый результат: 1035,09)
!!! Ознакомьтесь с работой функции ПС(). Получите результат, приведенный выше самостоятельно
!!! Постройте аналогичную итоговую таблицу на новом листе.