4. Вычисление основных платежей и платы по процентам
Основные платежи и платы по процентам вычисляются с помощью формул или финансовых функций ОСПЛТ и ПРПЛТ.
Функция ПРПЛТ возвращает платежи по процентам за данный период на основе периодических постоянных выплат и постоянной процентной ставки.
Синтаксис: ПРПЛТ (Ставка; Период; Кпер; Пс; Бс; Тип).
Функция ОСПЛТ возвращает величину выплаты за данный период на основе периодических постоянных платежей и постоянной процентной ставки.
Синтаксис: ОСПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип).
Аргументы функций ПРПЛТ: и ОСПЛТ:
Ставка – процентная ставка за период,
Период – задает период, значение должно быть в интервале от 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 %.
Решение:
Откройте Лист 4 и переименуйте его в Задание 4.
Введите данные, представленные на рис. 13.
Рис. 13. Функции для вычисления основных платежей и
платы по процентам.
Ежегодная плата вычисляется в ячейке В4 по формуле:
=ПЛТ(процент; срок; -размер_ссуды),
где ячейки В2, В3 и В5 имеют имена: процент, срок и размер_ссуды, соответственно. Присвоение имени ячейке осуществляется с помощью команды Вставка / Имя / Присвоить.
За первый год плата по процентам в ячейке В8 вычисляется по формуле:
=D7*процент.
Основная плата в ячейке С8 вычисляется по формуле:
=ежегодная_плата-В8,
где ежегодная_плата – имя ячейки В4.
Остаток долга в ячейке D8 вычисляется по формуле:
=D7-C8.
В оставшиеся годы эти платы определяются с помощью протаскивания маркера заполнения выделенного диапазона B8:D8 вниз по столбцам.
Данные результаты расчетов должны быть следующими (рис. 14.):
Рис. 14. Вычисление основных платежей и платы по процентам.
Вычисление будущего значения вклада
Функция БС вычисляет будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки.
Функция БС подходит для расчета итогов накоплений при ежемесячных банковских взносах.
Синтаксис: БС (Ставка; Кпер; Плт; Пс; Тип).
Аргументы:
Ставка – процентная ставка за период,
Кпер – общее число периодов выплат,
Плт – величина постоянных периодических платежей,
Пс – текущее значение, то есть общая сумма, которую составят будущие платежи,
Тип – число 0 или 1, обозначающее, когда должна производиться выплата.
Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 – в начале периода. Если тип = 0 и БС = 0, то функция БС вычисляется по формуле (6):
(6)
где А – Плт; i – Ставка; n – Кпер.
Функция КПЕР вычисляет общее количество периодов выплаты для данного вклада на основе периодических постоянных выплат и постоянной процентной ставки.
Синтаксис: КПЕР(Ставка; Плт; Пс; БС; Тип).
Аргументы:
Ставка – процентная ставка за период,
Плт – величина постоянных периодических платежей,
Пс – текущее значение, т.е. общая сумма, которую составят будущие платежи,
БС – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты.
Если аргумент БС опущен, он полагается равным 0 (например, будущая стоимость займа равна 0), тип – число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 – то в начале периода. Если тип = 0 и БС = 0 функция КПЕР вычисляется по формуле (7):
(7)
где Р – ПС; i – Ставка; А – Плт.
Задание 5. Вы хотите зарезервировать деньги для специального проекта, который будет осуществлен через год. Предположим, вы собираетесь вложить 1000 руб. при годовой ставке 6 %. Вы собираетесь вкладывать по 100 руб. в начале каждого месяца в течение года. Сколько денег будет на счете в конце 12 месяцев?
Решение:
Откройте Лист 5 и переименуйте его в Задание 5.
Ведите данные в ячейки А1:С6 (см. рис. 15).
Рис. 15. Расчет будущего значения вклада
В ячейку С8 введите формулу:
=БС(6 %/12; 12; -100; -1000; 1)
получаем ответ: 2 301,40 руб (см. рис. 16).
Рис. 16. Диалоговое окно Аргументы функции БС