http://ru.excelfunctions.eu/%D0%91%D0%A1/A%D0%BD%D0%B3%D0%BB%D0%B8%D0%B9%D1%81%D0%BA%D0%BE%D0%BC
http://office.microsoft.com/ru-ru/excel-help/HP010069823.aspx
Функция бс
Применимо к: Microsoft Office Excel 2007
Печать
Показать все
Возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки.
Синтаксис
БС (ставка ;кпер ;плт ;пс;тип)
Дополнительные сведения об аргументах функции БС, также о других финансовых функциях см. в описании функции ПС.
Ставка - процентная ставка за период.
Кпер - общее число периодов платежей по аннуитету.
Плт - выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент «плт» состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент «пс» является обязательным.
Пс - приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент «пс» опущен, предполагается значение 0. В этом случае аргумент «плт» является обязательным.
Тип - число 0 или 1, обозначающее срок выплаты. Если аргумент «тип» опущен, предполагается значение 0.
Замечания
Убедитесь, что единицы измерения, выбранные для аргументов «ставка» и «кпер», соответствуют друг другу. При ежемесячных выплатах по четырехгодичному займу из расчета 12 процентов годовых используйте значение 12%/12 для аргумента «ставка» и 4*12 - для аргумента «кпер». При ежегодных платежах по тому же займу используйте значение 12% для аргумента «ставка» и 4 - для аргумента «кпер».
Все аргументы, которым соответствуют выплачиваемые денежные средства (например, сберегательные вклады), представляются в виде отрицательных чисел, а получаемые (например, дивиденды) - в виде положительных.
Пример 1
Инструкции
|
|
|
ПРИМЕЧАНИЕ. Годовая процентная ставка делится на 12, поскольку начисление сложных процентов производится ежемесячно.
Функция плт
Применимо к: Microsoft Office Excel 2007
Печать
Показать все
Возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.
Синтаксис
ПЛТ (ставка ;кпер ;пс ;бс;тип)
Более подробное описание аргументов функции ПЛТ см. в описании функции ПС.
Ставка - процентная ставка по ссуде.
Кпер - общее число выплат по ссуде.
Пс - приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.
Бс - требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нолю), т. е. для займа, например, значение бс равно 0.
Тип - число 0 (ноль) или 1, обозначающее, когда должна производиться выплата.
Замечания
Выплаты, возвращаемые функцией ПЛТ, включают основные платежи и платежи по процентам, но не включают налогов, резервных платежей или комиссий, иногда связываемых со ссудой.
Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов «ставка» и «кпер». Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12%/12 для задания аргумента «ставка» и 4*12 для задания аргумента «кпер». Если вы делаете ежегодные платежи по тому же займу, то используйте 12 процентов для задания аргумента «ставка» и 4 для задания аргумента «кпер».
Совет. Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, умножьте возвращаемое функцией ПЛТ значение на «кпер».
Пример 1
Чтобы этот пример проще было понять, скопируйте его на пустой лист.
Копирование примера
|
|
|
Функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки. Функция имеет следующий синтаксис:
ПЛТ (ставка; кпер; пс; бс; тип).
Аргументы функции ПЛТ
Обязательный аргумент. Процентная ставка за один период.
Примечание: если она выражена в процентах за год, то эту величину нужно разделить на количество периодов
Обязательный аргумент (постоянная величина). Количество, периодов производимых выплат.
Примечание: это значение не может меняться в течение всего периода выплат. Обычно это значение включает основной платеж и платеж по процентам, но не налоги и сборы.
Обязательный аргумент. Приведенная (текущая) стоимость, т. е. общая сумма, которая на данный момент равноценна ряду будущих платежей.
Примечание: Представляется отрицательным числом в случае выплаты денежных средств и положительным - в случае их получения. Если аргумент опущен, то он полагается равным 0. В этом случае должно быть указано значение аргумента пс.
Необязательный аргумент. Значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты.
Примечание: если аргумент «бс» опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0).
Необязательный аргумент. Указывает, когда должна производиться выплата.
Примечание: аргумент равен нулю, если выплата производится в конце периода, и единице - если в начале. «Тип» по умолчанию равен нулю.
Excel имеет значительную популярность среди бухгалтеров, экономистов и финансистов не в последнюю очередь благодаря обширному инструментарию по выполнению различных финансовых расчетов. Главным образом выполнение задач данной направленности возложено на группу финансовых функций. Многие из них могут пригодиться не только специалистам, но и работникам смежных отраслей, а также обычным пользователям в их бытовых нуждах. Рассмотрим подробнее данные возможности приложения, а также обратим особое внимание на самые популярные операторы данной группы.
В группу данных операторов входит более 50 формул. Мы отдельно остановимся на десяти самых востребованных из них. Но прежде давайте рассмотрим, как открыть перечень финансового инструментария для перехода к выполнению решения конкретной задачи.
Переход к данному набору инструментов легче всего совершить через Мастер функций.
В Мастер функций также можно перейти через вкладку «Формулы» . Сделав переход в неё, нужно нажать на кнопку на ленте «Вставить функцию» , размещенную в блоке инструментов «Библиотека функций» . Сразу вслед за этим запустится Мастер функций.
Имеется в наличии также способ перехода к нужному финансовому оператору без запуска начального окна Мастера. Для этих целей в той же вкладке «Формулы» в группе настроек «Библиотека функций» на ленте кликаем по кнопке «Финансовые» . После этого откроется выпадающий список всех доступных инструментов данного блока. Выбираем нужный элемент и кликаем по нему. Сразу после этого откроется окно его аргументов.
ДОХОД
Одним из наиболее востребованных операторов у финансистов является функция ДОХОД . Она позволяет рассчитать доходность ценных бумаг по дате соглашения, дате вступления в силу (погашения), цене за 100 рублей выкупной стоимости, годовой процентной ставке, сумме погашения за 100 рублей выкупной стоимости и количеству выплат (частота). Именно эти параметры являются аргументами данной формулы. Кроме того, имеется необязательный аргумент «Базис» . Все эти данные могут быть введены с клавиатуры прямо в соответствующие поля окна или храниться в ячейках листах Excel. В последнем случае вместо чисел и дат нужно вводить ссылки на эти ячейки. Также функцию можно ввести в строку формул или область на листе вручную без вызова окна аргументов. При этом нужно придерживаться следующего синтаксиса:
ДОХОД(Дата_сог;Дата_вступ_в_силу;Ставка;Цена;Погашение»Частота;[Базис])
БС
Главной задачей функции БС является определение будущей стоимости инвестиций. Её аргументами является процентная ставка за период («Ставка» ), общее количество периодов («Кол_пер» ) и постоянная выплата за каждый период («Плт» ). К необязательным аргументам относится приведенная стоимость («Пс» ) и установка срока выплаты в начале или в конце периода («Тип» ). Оператор имеет следующий синтаксис:
БС(Ставка;Кол_пер;Плт;[Пс];[Тип])
ВСД
Оператор ВСД вычисляет внутреннюю ставку доходности для потоков денежных средств. Единственный обязательный аргумент этой функции – это величины денежных потоков, которые на листе Excel можно представить диапазоном данных в ячейках («Значения» ). Причем в первой ячейке диапазона должна быть указана сумма вложения со знаком «-», а в остальных суммы поступлений. Кроме того, есть необязательный аргумент «Предположение» . В нем указывается предполагаемая сумма доходности. Если его не указывать, то по умолчанию данная величина принимается за 10%. Синтаксис формулы следующий:
ВСД(Значения;[Предположения])
МВСД
Оператор МВСД выполняет расчет модифицированной внутренней ставки доходности, учитывая процент от реинвестирования средств. В данной функции кроме диапазона денежных потоков («Значения» ) аргументами выступают ставка финансирования и ставка реинвестирования. Соответственно, синтаксис имеет такой вид:
МВСД(Значения;Ставка_финансир;Ставка_реинвестир)
ПРПЛТ
Оператор ПРПЛТ рассчитывает сумму процентных платежей за указанный период. Аргументами функции выступает процентная ставка за период («Ставка» ); номер периода («Период» ), величина которого не может превышать общее число периодов; количество периодов («Кол_пер» ); приведенная стоимость («Пс» ). Кроме того, есть необязательный аргумент – будущая стоимость («Бс» ). Данную формулу можно применять только в том случае, если платежи в каждом периоде осуществляются равными частями. Синтаксис её имеет следующую форму:
ПРПЛТ(Ставка;Период;Кол_пер;Пс;[Бс])
ПЛТ
Оператор ПЛТ рассчитывает сумму периодического платежа с постоянным процентом. В отличие от предыдущей функции, у этой нет аргумента «Период» . Зато добавлен необязательный аргумент «Тип» , в котором указывается в начале или в конце периода должна производиться выплата. Остальные параметры полностью совпадают с предыдущей формулой. Синтаксис выглядит следующим образом:
ПЛТ(Ставка;Кол_пер;Пс;[Бс];[Тип])
ПС
Формула ПС применяется для расчета приведенной стоимости инвестиции. Данная функция обратная оператору ПЛТ . У неё точно такие же аргументы, но только вместо аргумента приведенной стоимости («ПС» ), которая собственно и рассчитывается, указывается сумма периодического платежа («Плт» ). Синтаксис соответственно такой:
ПС(Ставка;Кол_пер;Плт;[Бс];[Тип])
ЧПС
Следующий оператор применяется для вычисления чистой приведенной или дисконтированной стоимости. У данной функции два аргумента: ставка дисконтирования и значение выплат или поступлений. Правда, второй из них может иметь до 254 вариантов, представляющих денежные потоки. Синтаксис этой формулы такой:
ЧПС(Ставка;Значение1;Значение2;…)
СТАВКА
Функция СТАВКА рассчитывает ставку процентов по аннуитету. Аргументами этого оператора является количество периодов («Кол_пер» ), величина регулярной выплаты («Плт» ) и сумма платежа («Пс» ). Кроме того, есть дополнительные необязательные аргументы: будущая стоимость («Бс» ) и указание в начале или в конце периода будет производиться платеж («Тип» ). Синтаксис принимает такой вид:
СТАВКА(Кол_пер;Плт;Пс[Бс];[Тип])
ЭФФЕКТ
Оператор ЭФФЕКТ ведет расчет фактической (или эффективной) процентной ставки. У этой функции всего два аргумента: количество периодов в году, для которых применяется начисление процентов, а также номинальная ставка. Синтаксис её выглядит так:
ЭФФЕКТ(Ном_ставка;Кол_пер)
Нами были рассмотрены только самые востребованные финансовые функции. В общем, количество операторов из данной группы в несколько раз больше. Но и на данных примерах хорошо видна эффективность и простота применения этих инструментов, значительно облегчающих расчеты для пользователей.
Функция ПЛТ в Excel входит в категорию «Финансовых». Она возвращает размер периодического платежа для аннуитета с учетом постоянства сумм платежей и процентной ставки. Рассмотрим подробнее.
Синтаксис и особенности функции ПЛТ
Синтаксис функции: ставка; кпер; пс; [бс]; [тип].
Расшифровка аргументов:
- Ставка – это проценты по займу.
- Кпер – общее количество платежей по ссуде.
- Пс – приведенная стоимость, равноценная ряду будущих платежей (величина ссуды).
- Бс – будущая стоимость займа после последнего платежа (если аргумент опущен, будущая стоимость принимается равной 0).
- Тип – необязательный аргумент, который указывает, выплата производится в конце периода (значение 0 или отсутствует) или в начале (значение 1).
Особенности функционирования ПЛТ:
- В расчете периодического платежа участвуют только выплаты по основному долгу и платежи по процентам. Не учитываются налоги, комиссии, дополнительные взносы, резервные платежи, иногда связываемые с займом.
- При задании аргумента «Ставка» необходимо учесть периодичность начисления процентов. При ссуде под 6% для квартальной ставки используется значение 6%/4; для ежемесячной ставки – 6%/12.
- Аргумент «Кпер» указывает общее количество выплат по кредиту. Если человек совершает ежемесячные платежи по трехгодичному займу, то для задания аргумента используется значение 3*12.
Примеры функции ПЛТ в Excel
Для корректной работы функции необходимо правильно внести исходные данные:
Размер займа указывается со знаком «минус», т.к. эти деньги кредитная организация «дает», «теряет». Для записи значения процентной ставки необходимо использовать процентный формат. Если записывать в числовом, то применяется десятичное число (0,08).
Нажимаем кнопку fx («Вставить функцию»). Откроется окно «Мастер функций». В категории «Финансовые» выбираем функцию ПЛТ. Заполняем аргументы:
Когда курсор стоит в поле того или иного аргумента, внизу показывается «подсказка»: что необходимо вводить. Так как исходные данные введены в таблицу Excel, в качестве аргументов мы использовали ссылки на ячейки с соответствующими значениями. Но можно вводить и числовые значения.
Обратите внимание! В поле «Ставка» значение годовых процентов поделено на 12: платежи по кредиту выполняются ежемесячно.
Ежемесячные выплаты по займу в соответствии с указанными в качестве аргументов условиями составляют 1 037,03 руб.
Чтобы найти общую сумму, которую нужно выплатить за весь период (основной долг плюс проценты), умножим ежемесячный платеж по займу на значение «Кпер»:
Исключим из расчета ежемесячных выплат по займу платеж, произведенный в начале периода:
Для этого в качестве аргумента «Тип» нужно указать значение 1.
Детализируем расчет, используя функции ОСПЛТ и ПРПЛТ. С помощью первой покажем тело кредита, посредством второй – проценты.
Для подробного расчета составим таблицу:
Рассчитаем тело кредита с помощью функции ОСПЛТ. Аргументы заполняются по аналогии с функцией ПЛТ:
В поле «Период» указываем номер периода, для которого рассчитывается основной долг.
Заполняем аргументы функции ПРПЛТ аналогично:
Дублируем формулы вниз до последнего периода. Для расчета общей выплаты суммируем тело кредита и проценты.
Рассчитываем остаток по основному долгу. Получаем таблицу следующего вида:
Общая выплата по займу совпадает с ежемесячным платежом, рассчитанным с помощью функции ПЛТ. Это постоянная величина, т.к. пользователь оформил аннуитетный кредит.
Таким образом, функция ПЛТ может применяться для расчета ежемесячных выплат по вкладу или платежей по кредиту при условии постоянства процентной ставки и сумм.
По-настоящему мощным инструментом Excel является благодаря своей уникальной многофункциональности и умению решать задачи людей из разных профессиональных областей. Excel незаменим для менеджеров и экономистов, предпринимателей и финансистов, бухгалтеров и аналитиков, математиков и инженеров. Универсальность ему придают специфические встроенные функции, которые те или иные специалисты используют в своих расчётах.
Одна из самых больших и популярных категорий функций - финансовые. В последней версии Excel есть 55 функций, относящихся к этой группе. Многие из них специфические и узконаправленные, но некоторые могут пригодиться практически каждому. Одна из таких базовых функций - ПЛТ (PMT).
Как гласит официальная справка, функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянной процентной ставки. Если Вас смущает специфический термин "аннуитет" - не пугайтесь. Иными словами, с помощью функции ПЛТ можно рассчитать сумму, которую нужно будет выплачивать каждый месяц при условии, что процент по кредиту не изменится и платежи вносятся регулярно равными суммами.
Синтаксис функции
Функция имеет следующий синтаксис:
ПЛТ(ставка; кпер; пс; [бс]; [тип])
Разберем по очереди все аргументы:
- Ставка. Обязательный аргумент. Представляет процентную ставку за период. Самое главное здесь - не ошибиться в пересчете размера ставки на нужный период. Если предполагается погашать кредит ежемесячными платежами, а ставка годовая - то ее нужно перевести в месячную, разделив на 12. Если же, например, кредит гасится 1 раз в квартал, то годовую ставку нужно поделить на 4 (и получить таким образом ставку за 1 квартал). Ставку можно указать в процентах или в сотых долях.
- Кпер. Обязательный. Этот аргумент представляет собой число расчетных периодов (сколько раз будет вноситься платёж в счёт погашения кредита). Как и ставка, этот аргумент зависит от того, какой расчетный период принят для вычислений. Если кредит получен на 5 лет с платежами 1 раз в месяц, то Кпер = 5*12 = 60 периодов . Если же на 3 года, с платежами 1 раз в квартал - то Кпер = 3*4 = 12 периодов .
- Пс . Обязательный. Сумма кредита, то есть объем долга, который нужно будет погасить будущими платежами.
- [бс]. Необязательный. Сумма долга, которая должна остаться неоплаченной после истечения всех расчетных периодов. Обычно этот аргумент равен 0 (кредит должен быть погашен полностью). Так как аргумент необязательный, то его можно не указывать (в таком случае он будет принят равным нулю).
- [тип]. Необязательный. Обозначает момент произведения выплаты - в начале или в конце периода. Для первого случая нужно указать единицу, а для второго ноль (или вообще пропустить этот аргумент). В большинстве случаев используется второй вариант - выплаты в конце периода, а значит чаще всего этот аргумент можно опустить.
Особенностью синтаксиса функции является указание направления денежного потока. Если денежный поток входящий (например, сумма полученного кредита, указанная в аргументе Пс), то необходимо указывать его как положительное число. Исходящие потоки наоборот, указываются как отрицательные числа (например, после вычисления функция ПЛТ вернет отрицательный результат, так как размер платежа по кредиту - это исходящий денежный поток).
Примеры использования
Задача 1. Расчет суммы выплат по кредиту
Предположим, что в банке получен кредит на сумму 1 000 000 руб. под 17,5% годовых на срок 6 лет. Кредит будет погашаться равными платежами ежемесячно на протяжении всего срока займа. К концу срока будет выплачена вся сумма долга. Первый платеж будет внесен в конце первого периода. Необходимо найти величину ежемесячного платежа.
Итак, нам известна годовая ставка, а кредит будет погашаться ежемесячно. Значит для расчета нам потребуется перевести годовую ставку в месячную, разделив 17,5% на 12 месяцев. В первый аргумент записываем 17,5%/12 .
Кредит получен на 6 лет. Выплачивается ежемесячно. Значит, количество периодов выплат = 6*12. Во второй аргумент записываем 72 .
В третий аргумент пишем сумму кредита. Она равна 1 000 000 руб. (для займополучателя это входящий денежный поток, указываем его как положительное число).
Четвертый аргумент опустим, так как сумма будет полностью погашена к концу срока. Пятый аргумент также опустим, так как выплаты производятся в конце периода.
Формула примет вид:
ПЛТ(17,5%/12;72;1000000)
Результат вычисления равен -22526,05 руб . Число отрицательное, так как платеж по кредиту для займополучателя является исходящим денежным потоком. Именно такую сумму нужно будет вносить каждый месяц для погашения кредита, описанного в условии.
Чтобы посчитать сумму итоговой переплаты, нужно умножить ежемесячный платеж на число периодов (Кпер) и вычесть из полученного результата сумму займа (Пс).
Задача 2. Расчет суммы пополнения депозита для накопления определенного объема средств
В банке открыт пополняемый депозит со ставкой 9% годовых. Вы планируете каждый квартал вносить на депозит одинаковую сумму денег (например, часть полученной квартальной премии) с целью накопить на счете через 4 года ровно 1 000 000 руб. Вопрос: на какую сумму нужно пополнять счёт каждый квартал?
Первый аргумент указываем как 9%/4 (так как годовую ставку нужно перевести в квартальную), второй аргумент = 4*4 (4 года по 4 квартала - итого 16 взносов). Третий аргумент - сумма кредита. Его мы принимаем за 0, так как ничего не брали. Четвертый аргумент - будущая стоимость. Указываем сумму, которую хотим накопить (1 000 000 руб.). Пятый аргумент снова опускаем (выплаты в конце периода, это самая распространенная ситуация).
Получим формулу:
ПЛТ(9%/4;4*4;0;1000000).
Результат вычисления: -52 616,63 руб. Такую сумму нужно вносить на указанный депозит каждый квартал, чтобы через четыре года иметь на счету миллион рублей.
Общая сумма внесенных средств = 52616,63 * 16 = 841 866,08 руб. Остальное накоплено за счет процентов.
Особенности функции
При использовании функции обращайте внимание на следующие моменты:
- функция предназначена только для аннуитетных платежей (то есть равных платежей через равные промежутки времени);
- функция работает по классической кредитной модели, что не всегда совпадает с тем, что предлагают современные кредитные организации. Во многих случаях условия кредитования не позволят успешно применить к ним функцию ПЛТ и придется расписывать отдельную модель и искать решение с помощью Подбора параметра или Поиска решения (создание подобной модели можно заказать на нашем сайте - tDots.ru );
- функция учитывает выплату основной части долга и начисленных процентов, но не принимает в расчет различные дополнительные начисления, комиссии, налоги и сборы и т.д.;
- знак числа (положительный или отрицательный) задаёт направление денежного потока. Поток от кредитора к должнику (например, сумма займа) будет иметь один знак, а поток от должника к кредитору (например, сумма ежемесячного погашения) - противоположный (неважно, плюс или минус).
Поддержать наш проект и его дальнейшее развитие можно .
Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram:
Похожие статьи
