Определение будущей стоимости. Функция бс
Функция БС рассчитывает будущую стоимость периодических постоянных платежей и будущее значение единой суммы вклада или займа на основе постоянной процентной ставки.
Для вычисления будущего значения единой суммы используются аргументы нз, норма, число_периодов.
Задача 1
Рассчитаем, какая сумма ожидается на счёте, если 27 тыс. руб. положены на 33 года под 13,5% годовых. Проценты начисляются каждые полгода.
Оформите таблицу следующим образом:
Выделите ячейку В7 и вызовите мастер функций (Вкладка формулы, Вставить функцию).
Из списка категорий выберите Финансовые, из списка функций БС. Обратите внимание, что при выборе функции внизу первого диалогового окна Мастера функций появляется описание этой функции. Кроме того при необходимости можно вызвать справку по этой функции:
Далее заполните диалоговое окно так как это показано на рисунке ниже:
Ответьте на вопрос: почему ставка делиться на 2, а количество периодов умножается на 2, а аргумент ПС берется со знаком «минус»?
После нажатия кнопки ОК в ячейке отобразиться результат вычисления функции.
Предположим, есть два варианта инвестирования средств в течение 4 лет: в начале каждого года под 26% годовых или в конце каждого года под 38% годовых. Пусть ежегодно вносится 300 тыс. руб. Определим, сколько денег окажется на счёте в конце 4-го года для каждого варианта.
Примечание: если выплаты производятся в конце периода, аргумент тип берём равный 0, если в начале периода 1.
Решение:
Рассчитайте, какая сумма будет на счёте, если сумма размером 5000 тыс. руб. размещена под 12% годовых на 3 года, а проценты начисляются каждые полгода. Ответ: 7092,60 тыс. руб.
Расчёт постоянных периодических выплат. Функция плт
Функция ПЛТ вычисляет величину выплаты за один период на основе фиксированных и постоянной процентной ставки.
Задача 4
В апреле 1998 г. в банк было вложено 16000 руб. Какую сумму денег необходимо вносить дополнительно в начале каждого месяца, если к 01.09.2002 г. необходимо иметь на счету 31000 руб. Ставка банковского процента не меняется за всё время хранения вклада и составляет 9% годовых. Начисленные проценты присоединяются к остатку вклада ежемесячно.
Примечание: Аргумент период рассчитывается как количество месяцев с апреля 1998 г. по сентябрь 2002 г. (не включая сентябрь). Аргумент сумма вклада берём со знаком «-«, т.к. функция ППЛАТ возвращает значение суммы ежемесячных выплат по кредиту, взятому в банке, а в нашей задаче говорится о вложении денег (т.е. задача обратная).
Допустим, банк выдал ссуду 200 млн. руб. на 4 года под 18% годовых. Ссуда выдана в начале года, а погашение начинается в конце года одинаковыми платежами. Определите размер ежегодного погашения ссуды.
Задача 6*
Определите размер ежегодного погашения займа размером 50 млн. руб., выданного на 3 года под 38% годовых.
Расчёт срока платежа. Функция кпер
Функция КПЕР вычисляет общее число периодов выплат как для единой суммы вклада (займа), так и для периодических постоянных выплат на основе постоянной процентной ставки. Если платежи производятся несколько раз в год, найденное значение необходимо разделить на число расчётных периодов в году, чтобы найти число лет выплат.
Функция может применяться в следующих расчетах:
Если рассчитывается общее число периодов начисления процентов, необходимых для того, чтобы начальная сумма размером нз достигла указанного будущего значения бс, то формула имеет вид =КПЕР(норма; ;нз;бс)
Для расчёта общего числа периодов, чрез которое совокупная величина фиксированных периодических выплат составит указанное значение бс. Формула имеет вид =КПЕР(норма; выплата; ;бс;1)
При погашении займа размером нз равномерными постоянными платежами в конце каждого расчётного периода число периодов, через которое произойдёт полное погашение, равно КПЕР(норма; выплата; нз)
Рассчитать, через сколько лет вклад размером 1 млн. руб. достигнет величины 1 млрд. руб., если годовая ставка процента по вкладу 16,79% и начисление процентов производится ежеквартально.
Ожидается, что ежегодные доходы от реализации проекта составят 33 млн. руб. необходимо рассчитать срок окупаемости проекта, если инвестиции к началу поступления доходов составят 100 млн. руб., а норма дисконтирования 12,11%
Примеры функции БС в 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 – тело кредита.
Фактическая задолженность за кредит по окончанию 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.
- При указании аргумента ставка можно использовать процентный формат данных (например 17%) и числовой аналог (0,17).
- Расходные операции (текущая стоимость, выплата за период) должны быть указаны со знаком «-», то есть являться отрицательными числами.
- Функция БС использует в расчетах следующую формулу:
- Данная функция может быть использована для расчета баланса на конец периода и остаточной суммы задолженности по кредиту на текущий момент.
- Если процентная ставка меняется со временем, для расчетов следует использовать формулу БЗРАСПИС.
- Аргументы функции могут являться числовыми значениями или текстовыми данными, которые могут быть преобразованы в числа. Если один или несколько аргументов функции БС принимают текстовые строки, не содержащие числовые значения, будет возвращен код ошибки #ЗНАЧ!.
Примечание 2: функция БС также применяется для определения остатка задолженности по кредиту с аннуитетным графиком выплат, при этом дополнительные проценты и комиссии учтены не будут. Аннуитетный график предполагает фиксированную сумму погашения для каждого периода выплат (состоит из процентов и тела кредита).
Финансовые функции в Microsoft Excel
В Microsoft Excel предусмотрено огромное количество разнообразных функций, позволяющих справляться с математическими, экономическими, финансовыми и другими задачами. Программа является одним из основных инструментов, использующихся в малых, средних и больших организациях для ведения различных видов учета, выполнения расчетов и т.д. Ниже мы рассмотрим финансовые функции, которые наиболее востребованы в Экселе.
- Вставка функции
- Популярные финансовые функции
- БС
- ВСД
- ДОХОД
- МВСД
- ИНОРМА
- ПЛТ
- ПОЛУЧЕНО
- ПС
- СТАВКА
- ЦЕНА
- ЧПС
Вставка функции
Для начала вспомним, как вставить функцию в ячейку таблицы. Сделать это можно по-разному:
- Выбрав нужную ячейку щелкаем по значку “fx (Вставить функцию)” слева от строки формул.
- Или переключаемся во вкладку “Формулы” и жмем аналогичную кнопку, расположенную в левом углу ленты программы.
Независимо от выбранного варианта, откроется окно вставки функции, в котором требуется выбрать категорию “Финансовые”, определиться с нужным оператором (например, ДОХОД), после чего нажать кнопку OK.
На экране отобразится окно с аргументами функции, которые требуется заполнить, после чего нажать кнопку OK, чтобы добавить ее в выбранную ячейку и получить результат.
Указывать данные можно вручную, используя клавиши клавиатуры (конкретные значения или ссылки на ячейки), либо встав в поле напротив нужного аргумента, выбирать соответствующие элементы в самой таблице (ячейки, диапазон ячеек) с помощью левой кнопки мыши (если это допустимо).
Обратите внимание, что некоторые аргументы могут не показываться и необходимо пролистать область вниз для получения доступа к ним (с помощью вертикального ползункам справа).
Альтернативный способ
Находясь во вкладке “Формулы” можно нажать кнопку “Финансовые” в группе “Библиотека функций”. Раскроется список доступных вариантов, среди которых просто кликаем по нужному.
После этого сразу же откроется окно с аргументами функции для заполнения.
Популярные финансовые функции
Теперь, когда мы разобрались с тем, каким образом функция вставляется в ячейку таблицы Excel, давайте перейдем к перечню финансовых операторов (представлены в алфавитном порядке).
Данный оператор применяется для вычисления будущей стоимости инвестиции исходя из периодических равных платежей (постоянных) и размера процентной ставки (постоянной).
Обязательными аргументами (параметрами) для заполнения являются:
- Ставка – процентная ставка за период;
- Кпер – общее количество периодов выплат;
- Плт – неизменная выплата за каждый период.
Необязательные аргументы:
- Пс – приведенная (нынешняя) стоимость. Если не заполнять, будет принято значение, равное “0”;
- Тип – здесь указывается:
- 0 – выплата в конце периода;
- 1 – выплата в начале периода
- если поле оставить пустым, по умолчанию будет принято нулевое значение.
Также есть возможность вручную ввести формулу функции сразу в выбранной ячейке, минуя окна вставки функции и аргументов.
Синтаксис функции:
Результат в ячейке и выражение в строке формул:
Функция позволяет вычислить внутреннюю ставку доходности для ряда денежных потоков, выраженных числами.
Обязательный аргумент всего один – “Значения”, в котором нужно указать массив или координаты диапазона ячеек с числовыми значениями (по крайней мере, одно отрицательное и одно положительное число), по которым будет выполняться расчет.
Необязательный аргумент – “Предположение”. Здесь указывается предполагаемая величина, которая близка к результату ВСД. Если не заполнять данное поле, по умолчанию будет принято значение, равное 10% (или 0,1).
Синтаксис функции:
Результат в ячейке и выражение в строке формул:
ДОХОД
С помощью данного оператора можно посчитать доходность ценных бумаг, по которым производится выплата периодического процента.
Обязательные аргументы:
- Дата_согл – дата соглашения/расчета по ценным бумагам (далее – ц.б.);
- Дата_вступл_в_силу – дата вступления в силу/погашения ц.б.;
- Ставка – годовая купонная ставка ц.б.;
- Цена – цена ц.б. за 100 рублей номинальной стоимости;
- Погашение – суммы погашения или выкупная стоимость ц.б. за 100 руб. номинальной стоимости;
- Частота – количество выплат за год.
Аргумент “Базис” является необязательным, в нем задается способ вычисления дня:
- 0 или не заполнен – армериканский (NASD) 30/360;
- 1 – фактический/фактический;
- 2 – фактический/360;
- 3 – фактический/365;
- 4 – европейский 30/360.
Синтаксис функции:
Результат в ячейке и выражение в строке формул:
Оператор используется для расчета внутренней ставки доходности для ряда периодических потоков денежных средств исходя из затрат на привлечение инвестиций, а также процента от реинвестирования денег.
У функции только обязательные аргументы, к которым относятся:
- Значения – указываются отрицательные (платежи) и положительные числа (поступления), представленные в виде массива или ссылок на ячейки. Соответственно, здесь должно быть указано, как минимум, одно положительное и одно отрицательное числовое значение;
- Ставка_финанс – выплачиваемая процентная ставка за оборачиваемые средства;
- Ставка _реинвест – процентная ставка при реинвестировании за оборачиваемые средства.
Синтаксис функции:
Результат в ячейке и выражение в строке формул:
ИНОРМА
Оператор позволяет вычислить процентную ставку для полностью инвестированных ц.б.
Аргументы функции:
- Дата_согл – дата расчета по ц.б.;
- Дата_вступл_в_силу – дата погашения ц.б.;
- Инвестиция – сумма, вложенная в ц.б.;
- Погашение – сумма к получению при погашении ц.б.;
- аргумент “Базис” как и для функции ДОХОД является необязательным.
Синтаксис функции:
Результат в ячейке и выражение в строке формул:
С помощью этой функции рассчитывается сумма периодического платежа по займу исходя из постоянства платежей и процентной ставки.
Обязательные аргументы:
- Ставка – процентная ставка за период займа;
- Кпер – общее количество периодов выплат;
- Пс – приведенная (нынешняя) стоимость.
Необязательные аргументы:
- Бс – будущая стоимость (баланс после последней выплаты). Если поле оставить незаполненным, по умолчанию будет принято значение, равное “0”.
- Тип – здесь указывается, как будет производиться выплата:
- “0” или не указано – в конце периода;
- “1” – в начале периода.
Синтаксис функции:
Результат в ячейке и выражение в строке формул:
ПОЛУЧЕНО
Применяется для нахождения суммы, которая будет получена к сроку погашения инвестированных ц.б.
Аргументы функции:
- Дата_согл – дата расчета по ц.б.;
- Дата_вступл_в_силу – дата погашения ц.б.;
- Инвестиция – сумма, инвестированная в ц.б.;
- Дисконт – ставка дисконтирования ц.б.;
- “Базис” – необязательный аргумент (см. функцию ДОХОД).
Синтаксис функции:
Результат в ячейке и выражение в строке формул:
Оператор используется для нахождения приведенной (т.е. к настоящему моменту) стоимости инвестиции, которая соответствует ряду будущих выплат.
Обязательные аргументы:
- Ставка – процентная ставка за период;
- Кпер – общее количество периодов выплат;
- Плт – неизменная выплата за каждый период.
Необязательные аргументы – такие же как и для функции “ПЛТ”:
- Бс – будущая стоимость;
- Тип.
Синтаксис функции:
Результат в ячейке и выражение в строке формул:
СТАВКА
Оператор поможет найти процентную ставку по аннуитету (финансовой ренте) за 1 период.
Обязательные аргументы:
- Кпер – общее количество периодов выплат;
- Плт – неизменная выплата за каждый период;
- Пс – приведенная стоимость.
Необязательные аргументы:
- Бс – будущая стоимость (см. функцию ПЛТ);
- Тип (см. функцию ПЛТ);
- Предположение – предполагаемая величина ставки. Если не указывать, будет принято значение по умолчанию – 10% (или 0,1).
Синтаксис функции:
Результат в ячейке и выражение в строке формул:
Оператор позволяет найти цену за 100 рублей номинальной стоимости ц.б., по которым производится выплата периодического процента.
Обязательные аргументы:
- Дата_согл – дата расчета по ц.б.;
- Дата_вступл_в_силу – дата погашения ц.б.;
- Ставка – годовая купонная ставка ц.б.;
- Доход – годовой доход по ц.б.;
- Погашение – выкупная стоимость ц.б. за 100 руб. номинальной стоимости;
- Частота – количество выплат за год.
Аргумент “Базис” как и для оператора ДОХОД является необязательным.
Синтаксис функции:
Результат в ячейке и выражение в строке формул:
С помощью данной функции можно определить чистую приведенную стоимость инвестиции исходя из ставки дисконтирования, а также размера будущих поступлений и платежей.
Аргументы функции:
- Ставка – ставка дисконтирования за 1 период;
- Значение1 – здесь указываются выплаты (отрицательные значения) и поступления (положительные значения) в конце каждого периода. Поле может содержать до 254 значений.
- Если лимит аргумента “Значение 1” исчерпан, можно перейти к заполнению следующих – “Значение2”, “Значение3” и т.д.
Синтаксис функции:
Результат в ячейке и выражение в строке формул:
Заключение
Категория “Финансовые” в программе Excel насчитывает свыше 50 различных функций, но многие из них специфичны и узконаправлены, из-за чего используются редко. Мы же рассмотрели 11 самых востребованных, по нашему мнению.
Какой показатель рассчитывается с использованием функции бс
alt=»Excel2007″ width=»80″ height=»80″ />Функция БС в 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 $ — это точная цифра. Вводите свои данные и получайте достоверные результаты. Успеха в инвестировании и подсчёте прибыли!