Функция бз в excel где находится
Обновлено: 06.07.2024
Функция БС в Excel… Очень полезная формула для людей, имеющих сбережения в банке, или располагающие другими инвестиционными формами вложение денежных средств. Думаю, увидев первый раз описание этой формулы в самом Microsoft Excel понять её предназначение довольно сложно. Однако, прочитав эту статью, Вы получите опровержение её непонятности, и подсчёт сложного процента будет решен в считанные секунды.
Итак, представим такую обычную ситуацию. У нас есть денежные средства, например 1 000 $ и нами было принято решение отнести эту сумму в банк. Он предлагает следующие условия: берет у нас деньги на 1 год под процентную ставку 12% годовых при ежемесячной капитализации, т.е. начисление процентов происходит каждый месяц, но их вычисление происходит каждый день.
Сколько Вы думаете, у нас накопится денег через 1 год? Некоторые могут моментально ответить 1 120 $ (1000*12%=1120$) Это неправильный ответ. Другие будут ссылаться на ежемесячную капитализацию, т.е. в их понимании в первый месяц будет начислено 10$ (1000*1%), во второй 10,1 (1010*1%) и так далее. При таком подсчёте, который уже изображен на картинке получается 1126,8 $. Но это тоже не точный ответ.
Для продвинутых и любознательных, которые хотят просмотреть формулы, можно открыть / скачать Excel-файл . Всё дело в том, что процент от суммы высчитывается в банке, как правило, каждый день, а не каждый месяц. И рассчитать итоговую сумму таким методом, который указан на картинке, крайне нерационально, но можно. У нас в таком случае получится 365/366 заполненных колонок, что выглядит очень громоздко. А если нужно просчитать сумму вклада за большой период, на 5 или 10 лет!? Всё это решается в одной ячейке, при помощи функции БС.
Начинаем разбираться, как ей пользоваться. Итак, заходим в Microsoft Excel вводим формулу =БС и далее рекомендуется нажать Аргументы функции.
Вот теперь начнём заполнять поля исходя из нашего примера.
Первое поле Ставка. В нашем примере сюда вписываем 12%/365, 12 процентов – это годовая ставка, а 365 количество дней, периодов по которым считается процент. Если процент высчитывается (не путать с выплатами) один раз в месяц, то делить придётся на 12 (месяцев).
Второе поле Кпер. Это количество периодов, отображающих нахождение в банке (для нашего опять же случая). Мы положили на год или 365 дней, соответственно вводим 365.
Третье поле Плт. Выплата, которую мы будем снимать каждый период. Но мы не будем ничего снимать, поэтому оставляем незатронутой или ставим 0.
Чётвертое поле Пс. Приведенная стоимость – та сумма, которую мы изначально инвестируем. Важный момент, т.к. мы её отдаём и для нашего кармана это временный минус, то и вводить нужно число отрицательное.
Пятое поле Тип. Заполнение этого поля имеет значение, в случае заполнения поля Плт. Если Плт не трогали, то и Тип можно оставить не тронутым. Есть только два варианта заполнения поля 0 или 1. Ноль вводиться при выплате в начале периода, а единица при выплате в конце периода.
На этом настройка формулы БС завершена. При использовании функции БС в нашем примере накопится 1127,47 $ - это точная цифра. Вводите свои данные и получайте достоверные результаты. Успеха в инвестировании и подсчёте прибыли!
(англ. FV ) — возвращает будущую стоимость инвестиций при условиях постоянной процентной ставки, периодических постоянных платежей или единого общего платежа (в виде начальной инвестиции, определяемой аргументом « пс »): = БС(ставка;кпер;плт;[пс];[тип]), где:
- « ставка » — процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12 , при ежеквартальных выплатах аргумент « ставка » будет равен 6%/4 ;
- « кпер » — общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент « кпер » будет равен 5*12 ;
- « плт » — постоянная выплата за каждый период (выплаты — отрицательные значения, поступления — положительные значения): например, если ежемесячный платеж по кредиту составляет 10 000 руб., то аргумент « плт » будет равен -10 000 .
- « пс » — приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0 и необходимо обязательно указать аргумент « плт »),
- « тип » — срок выплаты в начале (1) или в конце периода (0) (если аргумент « тип » опущен, предполагается значение 0, т.е. в конце периода).
2. БЗРАСПИС
( англ. FVSCHEDULE ) — возвращает будущую стоимость инвестиций после начисления ряда сложных процентов (с переменной процентной ставкой, подойдет для вкладов с капитализацией процентов): =БЗРАСПИС(первичное;план), где:
- « первичное » — стоимость инвестиции на текущий момент,
- «план» — массив применяемых процентных ставок.
3. ПС
( англ. PV) — возвращает приведенную (текущую) стоимость инвестиции или займа (на основе постоянной процентной ставки): =ПС(ставка; кпер; плт; [бс]; [тип]) , где:
- « ставка » — процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12 , при ежеквартальных выплатах аргумент « ставка » будет равен 6%/4 ;
- « кпер » — общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент « кпер » будет равен 5*12 ;
- « плт » — постоянная выплата за каждый период (выплаты — отрицательные значения, поступления — положительные значения): например, если ежемесячный платеж по кредиту составляет 10 000 руб., то аргумент « плт » будет равен -10 000 .
- « бс » — будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0 и необходимо обязательно указать аргумент « плт »),
- « тип » — срок выплаты в начале (1) или в конце периода (0) (если аргумент « тип » опущен, предполагается значение 0, т.е. в конце периода).
4. ЧПС
( англ. NPV) – возвращает чистую приведенную или дисконтированную стоимость инвестиции при условии серии периодических денежных потоков и с использованием ставки дисконтирования: =ЧПС(ставка; значение1; [значение2],… ), где:
- «ставка» — ставка дисконтирования за один период;
- « значение1, значение2,… » — предполагаемые выплаты и поступления (должны быть равномерно распределены во времени, при этом выплаты должны осуществляться в конце каждого периода).
5. ЧИСТНЗ
( англ. XNPV) — возвращает чистую приведенную стоимость для денежных потоков, не обязательно являющихся периодическими: =ЧИСТНЗ(ставка;значения;даты), где:
- «ставка» — ставка дисконтирования за один период;
- « значение1, значение2,… » — предполагаемые выплаты и поступления (денежные потоки, соответствующие графику платежей, приведенному в аргументе «даты». Если первое значение является затратами или выплатой, оно должно быть отрицательным. Все последующие выплаты дисконтируются на основе 365-дневного года. Ряд значений должен содержать по крайней мере одно положительное и одно отрицательное значение);
- «даты» — график дат платежей, который соответствует платежам для денежных потоков.
6. ПЛТ
( англ. PMT) — возвращает сумму периодического платежа с постоянным процентом и постоянной суммой платежа (подходит для расчета платежей по аннуитету): =ПЛТ(ставка; кпер; пс; [бс]; [тип]), где:
- « ставка » — процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12 , при ежеквартальных выплатах аргумент « ставка » будет равен 6%/4 ;
- « кпер » — общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент « кпер » будет равен 5*12 ;
- « пс » — приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0),
- « бс » — будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0),
- « тип » — срок выплаты в начале (1) или в конце периода (0) (если аргумент « тип » опущен, предполагается значение 0, т.е. в конце периода).
7. ПРПЛТ
( англ. IPMT) — возвращает сумму процентных платежей за указанный период только в том случае, если платежи в каждом периоде осуществляются равными частями: =ПРПЛТ(ставка;период;кпер;пс;[бс];[тип]), где :
- « ставка » — процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12 , при ежеквартальных выплатах аргумент « ставка » будет равен 6%/4 ;
- «период» — период, для которого требуется найти платежи по процентам (число в интервале от 1 до аргумента «кпер»);
- « кпер » — общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент « кпер » будет равен 5*12 ;
- « пс » — приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0),
- « бс » — будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0),
- « тип » — срок выплаты в начале (1) или в конце периода (0) (если аргумент « тип » опущен, предполагается значение 0, т.е. в конце периода).
8. СТАВКА
( англ . RATE) – возвращает ставку процентов по аннуитету за один период: =СТАВКА(кпер; плт; пс; [бс]; [тип]; [прогноз]), где:
- « кпер » — общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент « кпер » будет равен 5*12 ;
- « плт » — постоянная выплата за каждый период (выплаты — отрицательные значения, поступления — положительные значения): например, если ежемесячный платеж по кредиту составляет 10 000 руб., то аргумент « плт » будет равен -10 000 ;
- « пс » — приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0);
- « бс » — будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0, а аргумент « пс » является обязательным);
- « тип » — срок выплаты в начале (1) или в конце периода (0) (если аргумент « тип » опущен, предполагается значение 0, т.е. в конце периода);
- «прогноз» — предполагаемая величина ставки (если аргумент «прогноз » опущен, предполагается значение 10%).
9. ЭФФЕКТ
(англ. EFFECT) — возвращает фактическую (или эффективную) годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году, за которые начисляются сложные проценты: =ЭФФЕКТ(номинальная_ставка;кол_пер) , где:
- «номинальная_ставка» — номинальная процентная ставка;
- «кол_пер» — количество периодов в году, за которые начисляются сложные проценты.
10. ДОХОД
( англ. YIELD) — возвращает доходность ценных бумаг ( облигаций ), по которым производятся периодические выплаты процентов: =ДОХОД(дата_согл; дата_вступл_в_силу; ставка; цена; погашение, частота; [базис]), где:
- « дата_согл » — дата расчета за ценные бумаги (дата продажи ценных бумаг покупателю, более поздняя, чем дата выпуска);
- « дата_вступл_в_силу » — срок погашения ценных бумаг (момент, когда истекает срок действия ценных бумаг);
- « ставка » — годовая процентная ставка для купонов по ценным бумагам;
- « цена » — цена ценных бумаг на 100 рублей номинальной стоимости;
- « погашение » — выкупная стоимость ценных бумаг на 100 рублей номинальной стоимости;
- « частота » — кол-во выплат по купонам за год (для ежегодных — 1, для полугодовых — 2, для ежеквартальных — 4);
- «базис» — используемый способ вычисления дня (если 0 или опущен, то используется американский (NASD) 30/360).
11. ВСД
( англ. IRR) – возвращает внутреннюю ставку доходности для потоков денежных средств (для платежей (отрицательные величины) и доходов (положительные величины), которые имеют место в следующие друг за другом и одинаковые по продолжительности периоды): =ВСД(значения; [предположения]), где:
- «значения» — массив или ссылка на ячейки, содержащие ряд денежных выплат (отрицательные значения) и поступлений (положительные значения), происходящих в регулярные периоды времени (по крайней мере одна положительная и одна отрицательная величина);
- «предположение » — величина, предположительно близкая к результату ВСД ( в большинстве случаев нет необходимости задавать аргумент «предположение». Если он опущен, предполагается значение 10%).
12. МВСД
( англ. MIRR) – возвращает модифицированную внутреннюю ставку доходности, учитывая процент от реинвестирования средств (при котором положительные и отрицательные денежные потоки имеют разные значения ставки): =МВСД(значения;ставка_финанс;ставка_реинвест), где:
Microsoft Excel функция БС возвращает будущую стоимость инвестиции на основе процентной ставки и постоянного графика платежей.
Функция БС - это встроенная в Excel функция, которая относится к категории финансовых функций.
Её можно использовать как функцию рабочего листа (WS) и функцию VBA в Excel.
Как функцию рабочего листа, функцию БС можно ввести как часть формулы в ячейку рабочего листа.
В качестве функции VBA вы можете использовать функцию FV в коде макроса, который вводится через редактор Microsoft Visual Basic Editor.
Синтаксис
Синтаксис функции БС в Microsoft Excel:
Аргументы или параметры
ставка Процентная ставка для инвестиции. кпер Количество платежей для аннуитета. плт Сумма платежа за каждый период. пс Необязательно. Это текущая стоимость платежей.Если этот параметр опущен, предполагается, что пс равно 0. тип Необязательно. Он указывает, когда должны быть произведены выплаты.
Если аргумент тип опущен, то предполагается, что значение тип равно 0. тип может быть одним из следующих значений:
| Значение | Пояснение |
|---|---|
| 0 | Платежи подлежат оплате в конце периода. (по умолчанию) |
| 1 | Платежи подлежат оплате в начале периода. |
Возвращаемое значение
Функция БС возвращает числовое значение.
Применение
- Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Тип функции
- Функция рабочего листа (WS)
- Функция VBA
Пример (как функция рабочего листа)
Рассмотрим несколько примеров Excel БС, чтобы понять, как использовать Excel функцию БС как функцию рабочего листа в Microsoft Excel.
Первый пример возвращает будущую стоимость инвестиции, в которую вы вносите $5000 на сберегательный счет, приносящий 7,5% годовых. Вы собираетесь вносить $250 в начале месяца каждый месяц на 2 года.
Функция БС в 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 – тело кредита.
Фактическая задолженность за кредит по окончанию 30-го месяца составит примерно 49372 рубля.
Сравнительный инвестиционный анализ условий депозита в банке
Пример 3. Вкладчик получил предложения по депозитному вкладу от двух банков с различными условиями:
- Ставка – 12% годовых, капитализация – ежемесячная.
- Ставка – 33% годовых, капитализация – ежеквартальная.
Определить, какое из предложений является более выгодным, если сумма вклада – 100000 рублей, срок действия договора – 2 года.
Формула для расчета:
БС(B3/12;B5;0;B6*(-1));"Первый банк";"Второй банк")' >
С помощью функции ЕСЛИ определяем, в каком случае будущая стоимость окажется больше и возвращаем соответствующее значение. Полученный результат:
Выведем результаты расчетов функций БС и разницу сумм:
Как видно, несмотря на высокую годовую ставку, первый банк сделал более выгодное предложение, поскольку по условиям предложенного договора капитализация является ежемесячной. То есть, чем чаще происходит капитализация, тем быстрее увеличивается сумма вклада.
Особенности использования финансовой функции БС в Excel
Функция БС используется наряду с прочими финансовыми функциями (ПС, ПЛТ, КПЕР и другими) и имеет следующий синтаксис:
=БС( ставка;кпер;плт ;[пс];[тип])
- ставка – аргумент, принимающий числовое или процентное значение ставки за указанный период. Обязательный для заполнения. Если по условию используется годовая ставка, необходимо выполнить пересчет по следующей формуле: R=Rg/n, где Rg – годовая ставка, n – число периодов.
- кпер – числовое значение, характеризующее число периодов оплаты. Аргумент обязателен для заполнения. Если кредит был взят на период 3 года, выплаты по которому должны производиться каждый месяц, аргумент кпер должен принять значение 3*12=36 (12 – месяцы в году).
- плт – числовое значение, характеризующее фиксированную сумму выплаты за каждый период. Аргумент обязателен для заполнения. Если выплата за период является неизвестной величиной, аргумент плт может принимать значение 0, но при этом следующий аргумент задается явно.
- [пс] – приведенная стоимость на данный момент. Например, когда заемщик берет кредит у финансовой организации, тело кредита является приведенной на текущий момент стоимостью. По умолчанию аргумент [пс] принимает значение 0, а плт должен иметь отличное от нуля значение.
- [тип] – числовое значение, характеризующее тип выплат: в конце или начале периода. Принимает только два значения: 0 (если явно не указан) и 1.
Примечание 2: функция БС также применяется для определения остатка задолженности по кредиту с аннуитетным графиком выплат, при этом дополнительные проценты и комиссии учтены не будут. Аннуитетный график предполагает фиксированную сумму погашения для каждого периода выплат (состоит из процентов и тела кредита).
Читайте также: