Примеры функции БС в Excel с фиксированной процентной ставкой

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

Примеры использования финансовой функции БС в Excel

Пример 1. Вкладчик сделал депозит с ежемесячной капитализацией на сумму 100 000 рублей под 13% годовых сроком на 4 года. Какую сумму средств он сможет снять со своего депозитного счета по окончанию действия договора с банком?

Исходные данные:

Формула для расчета:

Описание аргументов:

B3/12 – ставка за период (капитализация выполняется ежемесячно);
B4 – число периодов капитализации вклада;
0 – сумма выплаты за период капитализации (неизвестная величина в рамках данной задачи, поэтому значение 0);
B2*(-1) – начальная сумма вклада (инвестиция, которая должна являться отрицательным числом).

Результаты расчета:

Спустя 4 года вкладчик получит 167 733 рубля.

Расчет суммы долга по кредиту по состоянию на 30-й период погашения

Пример 2. Заемщик взял кредит в банке под 26% годовых на сумму 220 000 рублей сроком на 3 года с ежемесячным фиксированным платежом. Какой будет сумма задолженности заемщика по окончанию 30-го периода выплат?

Исходные данные:

Формула для расчета:

=БС(B3/12;30;ПЛТ(B3/12;B4;B2);B2)

Описание аргументов:

B3/12 – месячная процентная ставка;
30 – номер периода для расчета остатка задолженности;
ПЛТ(B3/12;B4;B2) – функция, возвращающая сумму ежемесячного платежа;
B2 – тело кредита.

Полученный результат:

Фактическая задолженность за кредит по окончанию 30-го месяца составит примерно 49372 рубля.

Сравнительный инвестиционный анализ условий депозита в банке

Пример 3. Вкладчик получил предложения по депозитному вкладу от двух банков с различными условиями:

Ставка – 12% годовых, капитализация – ежемесячная.
Ставка – 33% годовых, капитализация – ежеквартальная.

Определить, какое из предложений является более выгодным, если сумма вклада – 100000 рублей, срок действия договора – 2 года.

Исходные данные:

Формула для расчета:

С помощью функции ЕСЛИ определяем, в каком случае будущая стоимость окажется больше и возвращаем соответствующее значение. Полученный результат:

Выведем результаты расчетов функций БС и разницу сумм:

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

Особенности использования финансовой функции БС в Excel

Функция БС используется наряду с прочими финансовыми функциями (ПС, ПЛТ, КПЕР и другими) и имеет следующий синтаксис:

=БС(ставка;кпер;плт;[пс];[тип])

Описание аргументов:

ставка – аргумент, принимающий числовое или процентное значение ставки за указанный период. Обязательный для заполнения. Если по условию используется годовая ставка, необходимо выполнить пересчет по следующей формуле: R=Rg/n, где Rg – годовая ставка, n – число периодов.
кпер – числовое значение, характеризующее число периодов оплаты. Аргумент обязателен для заполнения. Если кредит был взят на период 3 года, выплаты по которому должны производиться каждый месяц, аргумент кпер должен принять значение 3*12=36 (12 – месяцы в году).
плт – числовое значение, характеризующее фиксированную сумму выплаты за каждый период. Аргумент обязателен для заполнения. Если выплата за период является неизвестной величиной, аргумент плт может принимать значение 0, но при этом следующий аргумент задается явно.
[пс] – приведенная стоимость на данный момент. Например, когда заемщик берет кредит у финансовой организации, тело кредита является приведенной на текущий момент стоимостью. По умолчанию аргумент [пс] принимает значение 0, а плт должен иметь отличное от нуля значение.
[тип] – числовое значение, характеризующее тип выплат: в конце или начале периода. Принимает только два значения: 0 (если явно не указан) и 1.

Примечание 1:

При указании аргумента ставка можно использовать процентный формат данных (например 17%) и числовой аналог (0,17).
Расходные операции (текущая стоимость, выплата за период) должны быть указаны со знаком «-», то есть являться отрицательными числами.
Функция БС использует в расчетах следующую формулу:

Данная функция может быть использована для расчета баланса на конец периода и остаточной суммы задолженности по кредиту на текущий момент.
Если процентная ставка меняется со временем, для расчетов следует использовать формулу БЗРАСПИС.
Аргументы функции могут являться числовыми значениями или текстовыми данными, которые могут быть преобразованы в числа. Если один или несколько аргументов функции БС принимают текстовые строки, не содержащие числовые значения, будет возвращен код ошибки #ЗНАЧ!.

Примечание 2: функция БС также применяется для определения остатка задолженности по кредиту с аннуитетным графиком выплат, при этом дополнительные проценты и комиссии учтены не будут. Аннуитетный график предполагает фиксированную сумму погашения для каждого периода выплат (состоит из процентов и тела кредита).