Что такое кпер в excel

Обновлено: 04.07.2024

Функция ПЛТ в Excel входит в категорию «Финансовых». Она возвращает размер периодического платежа для аннуитета с учетом постоянства сумм платежей и процентной ставки. Рассмотрим подробнее.

Синтаксис и особенности функции ПЛТ

Синтаксис функции: ставка; кпер; пс; [бс]; [тип].

Ставка – это проценты по займу.
Кпер – общее количество платежей по ссуде.
Пс – приведенная стоимость, равноценная ряду будущих платежей (величина ссуды).
Бс – будущая стоимость займа после последнего платежа (если аргумент опущен, будущая стоимость принимается равной 0).
Тип – необязательный аргумент, который указывает, выплата производится в конце периода (значение 0 или отсутствует) или в начале (значение 1).

Особенности функционирования ПЛТ:

В расчете периодического платежа участвуют только выплаты по основному долгу и платежи по процентам. Не учитываются налоги, комиссии, дополнительные взносы, резервные платежи, иногда связываемые с займом.
При задании аргумента «Ставка» необходимо учесть периодичность начисления процентов. При ссуде под 6% для квартальной ставки используется значение 6%/4; для ежемесячной ставки – 6%/12.
Аргумент «Кпер» указывает общее количество выплат по кредиту. Если человек совершает ежемесячные платежи по трехгодичному займу, то для задания аргумента используется значение 3*12.

Примеры функции ПЛТ в Excel

Для корректной работы функции необходимо правильно внести исходные данные:

Размер займа указывается со знаком «минус», т.к. эти деньги кредитная организация «дает», «теряет». Для записи значения процентной ставки необходимо использовать процентный формат. Если записывать в числовом, то применяется десятичное число (0,08).

Нажимаем кнопку fx («Вставить функцию»). Откроется окно «Мастер функций». В категории «Финансовые» выбираем функцию ПЛТ. Заполняем аргументы:

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

Обратите внимание! В поле «Ставка» значение годовых процентов поделено на 12: платежи по кредиту выполняются ежемесячно.

Ежемесячные выплаты по займу в соответствии с указанными в качестве аргументов условиями составляют 1 037,03 руб.

Чтобы найти общую сумму, которую нужно выплатить за весь период (основной долг плюс проценты), умножим ежемесячный платеж по займу на значение «Кпер»:

Исключим из расчета ежемесячных выплат по займу платеж, произведенный в начале периода:

Для этого в качестве аргумента «Тип» нужно указать значение 1.

Детализируем расчет, используя функции ОСПЛТ и ПРПЛТ. С помощью первой покажем тело кредита, посредством второй – проценты.

Для подробного расчета составим таблицу:

Рассчитаем тело кредита с помощью функции ОСПЛТ. Аргументы заполняются по аналогии с функцией ПЛТ:

В поле «Период» указываем номер периода, для которого рассчитывается основной долг.

Заполняем аргументы функции ПРПЛТ аналогично:

Дублируем формулы вниз до последнего периода. Для расчета общей выплаты суммируем тело кредита и проценты.

Рассчитываем остаток по основному долгу. Получаем таблицу следующего вида:

Общая выплата по займу совпадает с ежемесячным платежом, рассчитанным с помощью функции ПЛТ. Это постоянная величина, т.к. пользователь оформил аннуитетный кредит.

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

В этой статье описаны синтаксис формулы и использование функции СТАВКА в Microsoft Excel.

Описание

Синтаксис

СТАВКА(кпер; плт; пс; [бс]; [тип]; [прогноз])

Примечание: Полное описание аргументов "кпер", "плт", "пс", "бс" и "тип" см. в разделе, посвященном функции ПС.

Аргументы функции СТАВКА описаны ниже.

Кпер — обязательный аргумент. Общее количество периодов платежей по аннуитету.

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

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

Fv Необязательный. Будущая стоимость или баланс, который вы хотите достичь после последнего платежа. Если значение "ок" опущено, предполагается значение 0 (например, будущая стоимость займа — 0). Если аргумент "пс" опущен, необходимо включить аргумент "pmt".

Тип Необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата.

Когда нужно платить

В конце периода

В начале периода

Прогноз Необязательный. Предполагаемая величина ставки.

Если аргумент "прогноз" опущен, предполагается, что его значение равно 10 %.

Если функция СТАВКА не сходится, попробуйте изменить значение аргумента "прогноз". Функция СТАВКА обычно сходится, если значение этого аргумента находится между 0 и 1.

Замечания

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

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

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

Примеры как использовать функцию КПЕР в Excel

Пример 1. Вкладчик внес депозит под 16% годовых на сумму 120000 рублей с ежемесячной капитализацией вклада (простые проценты). Сколько лет потребуется для накопления 300000 рублей?

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

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

Возвращенный функцией КПЕР результат разделен на количество периодов капитализации в году для расчета числа лет, необходимых для накопления требуемой суммы. Результат расчетов:

Вкладчик должен оставлять деньги на депозитном счете на протяжении почти 6 лет.

Расчет реальной суммы долга с процентами и переплатой в Excel

Пример 2. Клиенту банка был выдан кредит на сумму 10000 рублей под 23% годовых с ежемесячной оплатой 700 рублей. Сколько всего денег получит банк по окончанию срока кредитного договора?

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

Общая сумма кредита рассчитывается как произведение фиксированной суммы ежемесячного платежа и количества периодов выплат. В данном случае количество периодов равно 16,85 (нецелое число), значит, последняя выплата должна составить меньше 700 рублей. Найдем целое число периодов:

Чтобы определить, какую часть тела кредита было погашено за 16 целых периодов выплат, воспользуемся следующей функцией:

За последний неполный период необходимо вернуть следующую часть тела кредита:

Рассчитаем оставшиеся проценты к уплате:

Так как платеж включает в себя оплату тела кредита и процентов, насчитанных за период, определим размер последнего платежа по формуле:

Общая сумма, которую получит банк, составит 11796 рублей, а размер последнего платежа – 597 рублей.

Расчет сроков погашения кредита с помощью функции КПЕР

Пример 3. Банк выдал кредит на сумму 35000 рублей под 27% годовых. Размер ежемесячного платежа составляет 1500 рублей. Через сколько месяцев клиент выплатить 50% кредита?

Исходная таблица данных:

На основании тождества аннуитетных платежей (сумма величины платежа в погашение тела кредита за все периоды, тела кредита и будущей стоимости равна нулю, то есть ОБЩДОХОД+ПС+БС=0) используем следующую формулу:

Выражение -B2*(1-50%)) характеризует будущую стоимость и было получено из уравнения:

Для выплаты 50% кредита потребуется вносить ежемесячный платеж на протяжении примерно 20 месяцев.

Особенности использования функции КПЕР в Excel

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

=КПЕР( ставка;плт;пс ;[бс];[тип])

Описание аргументов (первые три аргумента – обязательные для заполнения):

ставка – числовое значение, характеризующее ставку за 1 период выплат (для ссуд) или капитализации (для депозитных вкладов). Аргумент может быть указан в виде дробного числа или в качестве значения в процентном формате (например, 14,5% или 0,145 – эквивалентные варианты записи). Если в условии задачи указана годовая ставка, необходимо выполнить пересчет по формуле Rп=Rг/12, где Rп – ставка за период, Rg – годовая ставка, 12 – число месяцев в году.
плт – числовое значение, соответствующее сумме выплаты за период, которая является фиксированной величиной (простые проценты).
пс – числовое значение, характеризующее текущую стоимость инвестиции (например, сумма, выданная кредитной организацией в долг клиенту, или сумма средств, положенных на депозитный счет в банк).
[бс] – числовое значение, соответствующее будущей стоимости инвестиции. Например, данный аргумент может характеризовать сумму, которую получит вкладчик по окончанию действия договора по депозитному вкладу. Если аргумент явно не указан или принимает значение 0 (нуль), функция КПЕР вернет количество периодов выплат до полного погашения задолженности. Аргумент необязателен для заполнения, по умолчанию принимается значение 0.
[тип] – необязательный аргумент, характеризующий способ выплат (0 – выплата на конец периода, 1 – выплата на начало периода).

Функция БС в 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: функция БС также применяется для определения остатка задолженности по кредиту с аннуитетным графиком выплат, при этом дополнительные проценты и комиссии учтены не будут. Аннуитетный график предполагает фиксированную сумму погашения для каждого периода выплат (состоит из процентов и тела кредита).

Читайте также: