1.2.3. Функции для разработки планов погашения кредитов

Разработка планов погашения кредитов — одна из важнейших и часто встречающихся на практике задач. Как правило, кредит погашается одинаковыми платежами, равномерно распределенными во времени. Такой метод погашения часто называется амортизацией долга. Возникающие при этом денежные потоки представляют собой аннуитет.

Основная задача планирования поступлений (выплат) по кредитам сводится к начислению составных элементов платежей и распределению их во времени. Для этих целей в Excel реализована специальная группа функций, формат которых приведен в табл. 4.

Таблица 4. Функции для разработки планов погашения кредитов

Ставка — это процентная ставка за период.

Период — это период, для которого требуется найти прибыль и который должен находиться в интервале от 1 до кпер.

Кпер — это общее число периодов выплат годовой ренты.

Нз — это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента.

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

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

Нач_период — это номер первого периода, участвующего в вычислениях. Периоды выплат нумеруются, начиная с 1.

Кон_период — это номер последнего периода, участвующего в вычислениях.

Финансовые функции ПЛПРОЦ и ОСНПЛАТ

 Пример 6

Рассмотрим пример вычисления основных платежей, платы по процентам, общей ежегодной платы и остатка долга на примере ссуды в 100000 усл. ден. ед. на срок 5 лет при годовой ставке 2 %.

Создадим таблицу, согласно рис. 7, на рабочем листе Excel с именем листа Пример 6-ПЛПРОЦ и заполним ее исходными данными по условию задачи. На рис. 7 исходные данные выделены серым цветом.

Рис. 7. Вычисление основных платежей и платы по процентам

Затем присвоим имена ячейкам таблицы В1, В2, В3 и В4. Для этого выполним следующие действия:

 выделим диапазон ячеек А1:В4;

 выполним команды Вставка/Имя/Создать/В столбце слева/ОК;

 выполним щелчок по стрелке раскрывающегося списка Поля имен и убедимся, что имена присвоены правильно. В качестве имен диапазонов взяты заголовки строк.

Формулы, используемые при расчете, приведены в табл. 5.

Таблица 5. Таблица с формулами

Основную плату и плату по процентам можно было бы найти и с помощью функций ОСНПЛАТ и ПЛПРОЦ, соответственно.

Функция ПЛПРОЦ

Функция ПЛПРОЦ возвращает платежи по процентам за данный период на основе периодических постоянных выплат и постоянной процентной ставки.

Перед вводом функции выделим блок ячеек (В7:В11).

В ячейку В15 введем формулу, где аргументы функции заданы именами диапазонов:

=ПЛПРОЦ(Ставка;А15:А19;Количество_Периодов;-Размер_ссуды)

Результат скопируем в ячейки В16:В19 методом автозаполнения.

Функция ОСНПЛАТ

Функция ОСНПЛАТ возвращает величину выплаты за данный период на основе периодических постоянных платежей и постоянной процентной ставки.

В ячейку С15 введем формулу, где аргументы функции заданы именами диапазонов:

=ОСНПЛАТ(Ставка;А15:А19;Количество_периодов;Размер_ссуды)

Результат скопируем в ячейки С16:С19 методом автозаполнения.

В ячейку D15 введем формулу =D14-C15.