План погашения займа

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

Для вычисления выплат предназначена функция ПЛТ:

ПЛТ(ставка; количество_периодов; начальное_значение; будущее_значение; тип).

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

Для вычисления выплат на погашение основного долга предназначена функция ОСПЛТ:

ОСПЛТ(ставка; период; количество_периодов; начальное_значение; будущее_значение; тип).

Второй параметр — период — это порядковый номер периода, для которого производится расчет. Этот номер лежит в интервале от 1 до количество_периодов.

Для вычисления выплат на погашение процентов предназначена функция ПРПЛТ:

ПРПЛТ(ставка; период; количество_периодов; начальное_значение; будущее_значение; тип).

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

ПЛТ=ОСПЛТ+ПРПЛТ.

Пример 1.

Банк выдал долгосрочный кредит в сумме 40 000 долл. на 5 лет под 6% годовых. Погашение кредита должно производиться равными ежегодными выплатами в конце каждого года, включающими погашение основного долга и процентные платежи. Начисление процентов производится раз в год. Составить план погашения займа.

Решение. Выплаты составляют постоянную ренту постнумерандо. Результат вычислений представлен в таблице. 

Параметр Значение


Размер кредита $40 000,00


Срок 5


Ставка 6%







Период Платежи по процентам (ПРПЛТ) Платежи по основному долгу (ОСПЛТ) Годовая выплата (ПЛТ) Остаток основного долга
0


$40 000,00
1 -$2 400,00 -$7 095,86 -$9 495,86 $32 904,14
2 -$1 974,25 -$7 521,61 -$9 495,86 $25 382,54
3 -$1 522,95 -$7 972,90 -$9 495,86 $17 409,63
4 -$1 044,58 -$8 451,28 -$9 495,86 $8 958,35
5 -$537,50 -$8 958,35 -$9 495,86 $0,00
Итог -$7 479,28 -$40 000,00 -$47 479,28 $0,00
  1. Создайте по образцу таблицу с именем Параметры.

  2. Создайте по образцу таблицу с именем План.

  3. Создайте имена для ячеек со значениями параметров "Размер кредита", "Срок", "Ставка".

  4. Создайте формулы для расчета:

    =ПРПЛТ(Ставка;План[[#Эта строка];[Период]];Срок;Размер_кредита)

    =ОСПЛТ(Ставка;План[[#Эта строка];[Период]];Срок;Размер_кредита)

    =ПЛТ(Ставка;Срок;Размер_кредита)

  5. В таблице План подключите строку итогов (Конструктор/Параметры стилей таблиц/Строка итогов).

  6. Самостоятельно рассчитайте остаток основного долга, как разницу между текущим значением этого остатка и текущим платежом по основному долгу.

  7. Постройте столбиковую диаграмму, показывающую динамику платежей по годам. 

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

Функции ОБЩДОХОД и ОБЩПЛАТ

В Excel имеются функции, позволяющие вычислить платежи сразу за несколько периодов. Функции ОСПЛТ, предназначенной для расчетов в пределах одного периода, соответствует функция ОБЩДОХОД:

ОБЩДОХОД(ставка,количество периодов,начальное значение,номер начального периода,номер конечного периода,тип).

Аналогично, функции ПРПЛТ соответствует функция ОБЩПЛАТ:

ОБЩПЛАТ(ставка,количество периодов,начальное значение,номер начального периода,номер конечного периода,тип)