Задачи на финансовые функции в Excel примеры

2.4.1 Общие рекомендации

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

  • определение наращенной суммы (будущей стоимости),

  • определение начального значения (текущей стоимости),

  • определение срока платежа и процентной ставки,

  • расчет периодических платежей, связанных с погашением займов.

Для расчетов Excel использует приведенную выше формулу (22)

Эти формулы используют встроенные функции БC, КПЕР, ПC, ПЛТ, ЭФФЕКТ и другие.

В финансовых функциях Excel необходимо строго учитывать знаки величин PV, FV и С. Когда мы отдаем какую – либо величину, ставим перед ней знак минус, если получаем – плюс.

Работать с финансовыми функциями удобно с помощью Мастера функций

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

Не забывайте в поле Норма вводить величину процентной ставки за период r/m, а в поле Число – периодов – число периодов выплат или начисления процентов n=k·m.

2.4.2 Вычисление будущего значения

В Excel будущему значению FV соответствует функция БС.

БС — стоимость постоянных платежей в определенные периоды на основе постоянной процентной ставки.

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

Синтаксис

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

ставка — процентная ставка за период.

кпер — количество периодов, в которые производится вы­плата годовых процентов.

плт -выплата- размер выплаты, производимой в каждом периоде; это значение постоянно в течение всего времени выплат. Обычно плата состоит из основного платежа и платежа по процентам без учета других налогов и сборов.

пс — общая сумма всех будущих платежей с настоящего момента. Если аргумент пс опущен, то он полагается равным 0.

тип — число, определяющее когда должна производиться выплата. Может принимать значения 0 или 1: 0 — выплата в конце периода, 1 — выплата в начале периода.

Единицы измерения для аргументов ставка и кпер должны быть согласованы. Если производятся ежемесячные платежи по четырехгодичному займу из расчета 12% годовых, то норма должна быть 12%/12, а кпер должно быть 4*12. Если про­изводятся ежегодные платежи по тому же займу, то ставка должна быть 12%, а кпер должно быть 4.

Ваш вклад представляется отрицательным числом, а деньги, которые вы получите, представляются положительным числом.

В принятых в данной работе обозначениях

FV=БС(r/m; k·m; С; PV; тип).

Пример 15 Определим, сколько денег будет на счету через год, если вы собираетесь вложить 1000 рублей под 6% годовых (что составит в месяц 6%/12 или 0,5%). Причем вы собираетесь вкладывать по 100 рублей в начале каждого следующего месяца в течение года.

Через год на счете будет:

БС (0,5%;12;-100;-1000;1)=2301,40 р.

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

Пример 16 Определить сумму вклада на банковском счете, если положить 37 тыс. руб. на 3 года под 11,5% годовых. Проценты начисляются каждые полгода.

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

Поскольку необходимо рассчитать единую сумму вклада на основе постоянной процентной ставки, то используем функцию БС(). В связи с тем, что проценты начисляются каждые полгода, аргумент ставка равен 11,5%/2. Общее число периодов начисления равно 3*2 (аргумент кпер). По условию аргумент пс (начальное значение) равен 37000 руб. и задается в виде отрицательной величины (- 37 000), т.к. с точки зрения вкладчика это отток его денежных средств (вложение средств). Аргумент платеж отсутствует, т.к. вклад не пополняется, аргумент тип равен 0, т.к. в подобных операциях проценты начисляются в конце каждого периода (задается по умолчанию). Тогда к концу 3-го года на банковском счете имеем:

= БС(11,5%/2;3*2;;-37000) = 51746,86 руб.

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

Расчет процентной ставки для различной периодичности начислений

Метод начисления процентов

Общее число периодов начисления процентов

Ставка процента за период начисления, %

Ежегодный

Полугодовой

N*2

Квартальный

N*4

Месячный

N*12

Ежедневный

N*365

Функция БС в 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. Вкладчик получил предложения по депозитному вкладу от двух банков с различными условиями:

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. При указании аргумента ставка можно использовать процентный формат данных (например 17%) и числовой аналог (0,17).
  2. Расходные операции (текущая стоимость, выплата за период) должны быть указаны со знаком «-«, то есть являться отрицательными числами.
  3. Функция БС использует в расчетах следующую формулу:
  4. Данная функция может быть использована для расчета баланса на конец периода и остаточной суммы задолженности по кредиту на текущий момент.
  5. Если процентная ставка меняется со временем, для расчетов следует использовать формулу БЗРАСПИС.
  6. Аргументы функции могут являться числовыми значениями или текстовыми данными, которые могут быть преобразованы в числа. Если один или несколько аргументов функции БС принимают текстовые строки, не содержащие числовые значения, будет возвращен код ошибки #ЗНАЧ!.

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

Оставьте комментарий