§ 4. Описание работы функции осплт и всех ее аргументов.
Возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянных периодических платежей и постоянной процентной ставки.
Синтаксис функции: осплт(ставка;период;кпер;пс;бс;тип)
Ставка — процентная ставка за период.
Период — период. Значение должно находиться в интервале от 1 до «кпер».
Кпер — общее число периодов платежей по аннуитету.
Пс — приведенная к текущему моменту стоимость, т. е. общая сумма, которая на текущий момент равноценна ряду будущих платежей.
Бс — значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если этот аргумент опущен, то предполагается, что он равен 0 (например, значение «бс» для займа равно 0).
Тип — число 0 или 1, обозначающее срок выплаты.
Когда нужно выплатить
В конце периода
В начале периода
При вычислении необходимо убедиться, что для аргументов СТАВКА и КПЕР используются согласованные единицы измерения. Если производятся ежемесячные платежи по четырехгодичному займу из расчета 12% годовых, то ставка должна быть 12%/12 , а кпер должно быть 4*12. Если производятся ежегодные платежи по тому же займу, то ставка должна быть 12%, а кпер должно быть 4.
=ОСПЛТ([Ставка]/12, 1, [Кпер]*12, [ПС])
Основной платеж на первый месяц займа равен -75.62
Для получения месячной ставки разделите процентную ставку на 12. Для получения количества платежей умножьте на 12 число лет, в течение которых производятся выплаты.
Описание (Результат)
=ОСПЛТ([Ставка], [Период], 10, [ПС])
Основной платеж последнего года займа при заданных аргументах равен -27,598.05
§ 5. Набор исходных данных, которые будут использованы для демонстрации работы функции осплт.
Определить значение основного платежа для первого месяца двухгодичного займа в 60000 руб. под 12% годовых.
Решение задачи:
Данные по задаче представим в виде таблицы:
Сумма основного платежа по займу вычисляется с помощью функции ОСПЛТ: = ОСПЛТ (12%/12; 1; 24; 60000) = — -2 224,41руб.
Знак «минус» в результате означает, что сумму основного долга по займу необходимо внести. Отметим, что сумма выплаты по процентам, вычисляемая с помощью функции ПРПЛТ, и сумма основной выплаты за период, рассчитанная с помощью функции ОСПЛТ, равны полной величине выплаты, вычисляемой с помощью функции ПЛТ.
Готовая таблица имеет вид:
Составить график погашения ссуды, если берётся сумма размером 100000 рублей под 7,25% годовых, с ежемесячной выплатой на три года.
Решение задачи:
График погашения ссуды представляет собой таблицу, содержащую различную информацию по каждому периоду выплаты.
Начальные данные представим в виде таблицы:
Формулы, используемые для создания графика выплат:
Готовая таблица (график погашения ссуды) имеет вид:
На графике представлены основные и процентные суммы для всего срока платежа:
В банке взята в кредит сумма в размере 1000000 руб. Сроком на 4 года при годовой процентной ставке, равной 10 %. Необходимо рассчитать величину платежей по годам за период займа.
Функция ОСПЛТ для расчета регулярного платежа по кредиту в Excel
Функция ОСПЛТ в Excel предназначена для расчета значения сумм регулярных платежей, распределенных по периодам времени, которые необходимы для погашения общей суммы задолженности. Данные суммы принимают разные значения от периода к периоду, поэтому в отличие от другой функции (ПЛТ), рассматриваемая функция содержит дополнительный аргумент для указания номера периода.
Примеры расчетов регулярных платежей по аннуитетной схеме в Excel
Функция ОСПЛТ используется для расчетов задолженностей по аннуитетной схеме. То есть, сумма платежа за каждый период состоит из тела кредита (основной суммы задолженности) и процентов (части средств, которые выплачивают сверху за использование финансового продукта). Процентная ставка является неизменной величиной. Соотношение процентной части к телу кредита в каждом периодическом платеже меняется со временем. Рассматриваемая функция позволяет определить сумму основной задолженности (без учета процентов), выплаченной в определенный период согласно графику.
Пример 1. Банк выдал кредит на сумму 10 000 руб. под 18% годовых сроком на 1 год. Был составлен график ежемесячных выплат. Определить, какую сумму тела кредита выплатит клиент в 3-1 месяц.
Вид таблицы данных:
Для расчета используем следующую функцию:
- B3/12 – размер ставки, приведенной к числу периодов выплат (12 месяцев);
- 3 – номер периода, для которого выполняется расчет;
- B4 – общее число периодов (12 месяцев в году);
- B5 – сумма кредита по договору.
Полученное значение – отрицательное число, поскольку оно отражает расходы клиента по оплате финансового продукта.
Расчет динамики регулярных расходов на платежи по кредитам в Excel
Пример 2. Для финансового продукта из примера 1 определить общую сумму выплат по телу кредита за полгода.
Для расчета решения будем использовать формулу массива CTRL+SHIFT+Enter. Добавим вспомогательный список с номерами периодов:
Запишем следующую функцию:
Данная формула рассчитывает сумму всех значений выплат по телу кредита за первые 6 месяцев. Результат вычислений:
То есть, за половину периодов выплат будет выплачено только около 48% тела кредита.
Excel функция ОСПЛТ (PPMT)
Microsoft Excel функция ОСПЛТ возвращает платеж по основной сумме для определенного платежа на основе процентной ставки и постоянного графика платежей.
Функция ОСПЛТ — это встроенная в Excel функция, которая относится к категории финансовых функций.
Её можно использовать как функцию рабочего листа (WS) и функцию VBA в Excel.
Как функцию рабочего листа, функцию ОСПЛТ можно ввести как часть формулы в ячейку рабочего листа.
В качестве функции VBA вы можете использовать функцию PPMT в коде макроса, который вводится через редактор Microsoft Visual Basic Editor.
Синтаксис
Синтаксис функции ОСПЛТ в Microsoft Excel:
Аргументы или параметры
| Значение | Пояснение |
|---|---|
| 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 функцию ОСПЛТ в качестве функции рабочего листа в Microsoft Excel:
Первый пример возвращает сумму основного долга, выплаченного платежом, произведенным в 5-й месяце кредита в размере $5000 с ежемесячными выплатами по годовой процентной ставке 7,5%. Кредит подлежит погашению через 2 года (т.е. 2 х 12). Все выплаты производятся в начале периода.
Функции для расчета основных платежей и платы по процентам
Основные платежи и платы по процентам вычисляются с помощью формул или финансовых функций ОСПЛТ и ПРПЛТ.
Функция ПРПЛТ возвращает платежи по процентам за данный период на основе периодических постоянных выплат и постоянной процентной ставки.
Синтаксис: ПРПЛТ (Ставка; Период; Кпер; Пс; Бс; Тип).
Функция ОСПЛТ возвращает величину выплаты за данный период на основе периодических постоянных платежей и постоянной процентной ставки.
Синтаксис: ОСПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип).
Аргументы функций ПРПЛТ: и ОСПЛТ:
§ Ставка — процентная ставка за период,
§ Период — задает период, значение должно быть в интервале от 1 до «Кпер»,
§ Кпер — общее число периодов выплат годовой ренты,
§ Пс — приведенная стоимость, то есть общая сумма, которая равноценна ряду будущих платежей,
§ Бс — требуемое значение будущей стоимости, или остатка средств после последней выплаты.
Если аргумент Бс опущен, то он полагается равным 0 (нулю), то есть для займа, например, значение Бс равно 0.
§ Тип — число 0 или 1, обозначающее, когда должна производиться выплата.
Функции ПРПЛТ и ОСПЛТ тесно связаны между собой, а именно ПЛПj= i Bj-1, ОСНПj = А — ПЛПj, Bj = Вj-1 — ОСНПj при j Ѓё[0, n],
где j — номер периода,
ПЛПj, ОСНПj и Bj — это ПРПЛТ, ОСПЛТ и остаток долга, соответственно, за j-й период,
ПЛПо = 0, ОСНПо = 0, Bо — Пс,
А — величина выплаты за один период годовой ренты на основе постоянных выплат и постоянной процентной ставки, вычисляемая с помощью функции ПЛТ.
Пример 4. Вычислить основные платежи, платы по процентам, общей ежегодной платы и остатка долга на примере ссуды 100000 руб. на срок 5 лет при годовой ставке 2 %.
1. Откройте Лист 4 и переименуйте его в Задание 4.
2. Введите данные, представленные на рис. 13.
3. Ежегодная плата вычисляется в ячейке В4 по формуле:
=ПЛТ(процент; срок; -размер_ссуды),
где ячейки В2, В3 и В5 имеют имена: процент, срок и размер_ссуды, соответственно. Присвоение имени ячейке осуществляется с помощью команды Вставка / Имя / Присвоить.
4. За первый год плата по процентам в ячейке В8 вычисляется по формуле:
Рис. 13. Функции для вычисления основных платежей и платы по процентам.
5. Основная плата в ячейке С8 вычисляется по формуле:
где ежегодная_плата — имя ячейки В4.
Остаток долга в ячейке D8 вычисляется по формуле:
6. В оставшиеся годы эти платы определяются с помощью протаскивания маркера заполнения выделенного диапазона B8:D8 вниз по столбцам.
7. Данные результаты расчетов должны быть следующими (рис. 14.):
Рис. 14. Вычисление основных платежей и платы по процентам
Функции для расчета будущего значения вклада, процентной ставки и количества периодов выплаты долга
Функция БС вычисляет будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки.
Функция БС подходит для расчета итогов накоплений при ежемесячных банковских взносах.
Синтаксис: БС (Ставка; Кпер; Плт; Пс; Тип).
§ Ставка — процентная ставка за период,
§ Кпер — общее число периодов выплат,
§ Плт — величина постоянных периодических платежей,
§ Пс — текущее значение, то есть общая сумма, которую составят будущие платежи,
§ Тип — число 0 или 1, обозначающее, когда должна производиться выплата.
Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 — в начале периода. Если тип = 0 и БС = 0, то функция БС вычисляется по формуле (6):
где А — Плт; i — Ставка; n — Кпер.
Функция КПЕР вычисляет общее количество периодов выплаты для данного вклада на основе периодических постоянных выплат и постоянной процентной ставки.
Синтаксис: КПЕР(Ставка; Плт; Пс; БС; Тип).
§ Ставка — процентная ставка за период,
§ Плт — величина постоянных периодических платежей,
§ Пс — текущее значение, т.е. общая сумма, которую составят будущие платежи,
§ БС — будущая стоимость или баланс наличности, который нужно достичь после последней выплаты.
Если аргумент БС опущен, он полагается равным 0 (например, будущая стоимость займа равна 0), тип — число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 — то в начале периода. Если тип = 0 и БС = 0 функция КПЕР вычисляется по формуле (7):
где Р — ПС; i — Ставка; А — Плт.
Пример 5. Вы хотите зарезервировать деньги для специального проекта, который будет осуществлен через год. Предположим, вы собираетесь вложить 1000 руб. при годовой ставке 6 %. Вы собираетесь вкладывать по 100 руб. в начале каждого месяца в течение года. Сколько денег будет на счете в конце 12 месяцев?
1. Откройте Лист 5 и переименуйте его в Задание 5.
2. Ведите данные в ячейки А1:С6 (см. рис. 15).
Рис. 15. Расчет будущего значения вклада
3. В ячейку С8 введите формулу:
=БС(6 %/12; 12; -100; -1000; 1)
получаем ответ: 2 301,40 руб (см. рис. 16).
Рис. 16. Диалоговое окно Аргументы функции БС
Вычисление количества периодов выплаты долга
Общее количество периодов выплаты долга осуществляется с помощью функции КПЕР на основе периодических постоянных выплат и постоянной процентной ставки.
Синтаксис: КПЕР(Ставка; Плт; Пс; БС; Тип).
§ Ставка — процентная ставка за период,
§ Плт — величина постоянных периодических платежей,
§ Пс — текущее значение, т.е. общая сумма, которую составят будущие платежи,
§ БС — будущая стоимость или баланс наличности, который нужно достичь после последней выплаты.
Если аргумент БС опущен, он полагается равным 0 (например, будущая стоимость займа равна 0), тип — число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 — то в начале периода. Если тип = 0 и БС = 0 функция КПЕР вычисляется по формуле (7):
где Р — ПС; i — Ставка; А — Плт.
Пример 6. Вы берете в долг 1000 руб. при годовой ставке 1% и собираетесь выплачивать по 100 руб. в год, Какое будет число выплат долга?
1. Откройте Лист 6 и переименуйте его в Задание 6.
2. Ведите данные в ячейки А1:В6 (см. рис. 17).
Рис. 17. Расчет количества периодов выплат
3. В ячейку В6 введите формулу:
В результате получаем ответ: ?11. (см. рис. 18).
Рис. 18. Диалоговое окно Аргументы функции КПЕР
Вычисление процентной ставки
Функция СТАВКА вычисляет процентную ставку, необходимую для получения определенной суммы в течение заданного срока путем постоянных взносов, за один период.
Следует отметить, что функция СТАВКА вычисляет процентную ставку методом итераций, поэтому решение может быть и не найдено. Если после 20 итераций погрешность определения ставки превышает 0,0000001, то функция СТАВКА возвращает значение ошибки #ЧИСЛО!
Синтаксис: СТАВКА(КПЕР; Плт; Пс; БС; Тип; Предположение).
§ КПЕР — общее число периодов платежей по аннуитету;
§ Плт — регулярный платеж (один раз в период), величина которого остается постоянной в течение всего срока аннуитета. Обычно Плт состоит из платежа основной суммы и платежа процентов, но не включает других сборов или налогов. Если аргумент опущен, должно быть указано значение аргумента БС;
§ Пс — приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей;
§ БС — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент БС опущен, то он полагается равным 0 (например, БС для займа равно 0); тип — число 0 или 1, обозначающее, когда должна производиться выплата; предположение — предполагаемая величина ставки.
Если БС = 0 и тип = 0, функция СТАВКА является корнем уравнения (8):
Пример 7. Определить процентную ставку для четырехлетнего займа размером в 8000 руб. с ежемесячной выплатой 200 руб.
1. Откройте Лист 7 и переименуйте его в Задание 7.
2. Ведите данные в ячейки А1:В7 (см. рис. 19).
Рис. 19. Расчет процентной ставки
3. В ячейку В6 введите формулу:
4. В ячейку В7 введите формулу:
В результате получаем: месячная (так как период равен месяцу) процентная ставка равна 0,77 %. Процентная ставка годовая равна 9 % см. рис 20).
Рис.20. Результаты вычисления процентной ставки