План погашения займа
Наиболее сложной частью анализа
постоянной ренты является определение размера выплат. Типичная ситуация здесь
такова. Кредитор выдает в начале срока некоторую сумму. Дебитор обязуется
погасить задолженность равными долями. При этом каждую выплату можно разбить на
две составляющих — одна идет на погашение основной задолженности, а другая — на
процентные выплаты.
Для вычисления выплат предназначена
функция ПЛТ:
ПЛТ(ставка; количество_периодов;
начальное_значение; будущее_значение; тип).
Будущее_значение — это баланс наличности, который нужно достичь после последней выплаты. Если будущее значение опущено, оно полагается равным 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 |
Создайте по образцу таблицу с именем Параметры.
Создайте по образцу таблицу с именем План.
Создайте имена для ячеек со значениями параметров "Размер кредита", "Срок", "Ставка".
Создайте формулы для расчета:
=ПРПЛТ(Ставка;План[[#Эта строка];[Период]];Срок;Размер_кредита)
=ОСПЛТ(Ставка;План[[#Эта строка];[Период]];Срок;Размер_кредита)
=ПЛТ(Ставка;Срок;Размер_кредита)
В таблице План подключите строку итогов (Конструктор/Параметры стилей таблиц/Строка итогов).
Самостоятельно рассчитайте остаток основного долга, как разницу между текущим значением этого остатка и текущим платежом по основному долгу.
Постройте столбиковую диаграмму, показывающую динамику платежей по годам.
Из приведенной таблицы нетрудно усмотреть, что при погашении долга равными платежами остаток долга с каждой выплатой уменьшается, следовательно, уменьшаются и процентные выплаты. В результате возрастает от периода к периоду размер платежей, идущих на погашение основного долга.
Функции ОБЩДОХОД и ОБЩПЛАТ
В Excel имеются функции, позволяющие вычислить платежи сразу за несколько периодов. Функции ОСПЛТ, предназначенной для расчетов в пределах одного периода, соответствует функция ОБЩДОХОД:
ОБЩДОХОД(ставка,количество
периодов,начальное значение,номер начального периода,номер конечного периода,тип).
Аналогично, функции ПРПЛТ
соответствует функция ОБЩПЛАТ:
ОБЩПЛАТ(ставка,количество периодов,начальное значение,номер начального периода,номер конечного периода,тип).