График погашения кредита в Excel

Этот пример покажет вам, как создать график погашения кредита в Excel.

1. Мы используем функцию PMT (ПЛТ) для расчета ежемесячного платежа по кредиту с годовой процентной ставкой 5%, продолжительностью 2 года и стоимостью (суммой кредита) в размере $20000. Мы дали имена ячейкам с исходными данными:
   

   Ячейка	Значение	Имя (eng)	Имя (рус)
   B1	5%	AnnualInterestRate	Годовая_процентная_ставка
   B2	2 года	Years	Срок_кредита
   B3	12	PaymentsPerYear	Платежей_в_год
   B4	$20000	Amount	Стоимость_кредита

   В итоге получили выражение:

   =PMT(AnnualInterestRate/PaymentsPerYear, Years*PaymentsPerYear, Amount)
=ПЛТ(Годовая_процентная_ставка/Платежей_в_год; Срок_кредита*Платежей_в_год; Стоимость_кредита)

   

2. Используйте функцию PPMT (ОСПЛТ), чтобы рассчитать основную часть выплаты. Второй аргумент определяет номер платежа:

   =PPMT(AnnualInterestRate/PaymentsPerYear, A7, Years*PaymentsPerYear, Amount)
=OCПЛТ(Годовая_процентная_ставка/Платежей_в_год; A7; Срок_кредита*Платежей_в_год; Стоимость_кредита)

   

3. Используйте функцию IPMT (ПРПЛТ) для расчета платежа по процентам. Второй аргумент определяет номер платежа:

   =IPMT(AnnualInterestRate/PaymentsPerYear, A7, Years*PaymentsPerYear, Amount)
=ПРПЛТ(Годовая_процентная_ставка/Платежей_в_год; A7; Срок_кредита*Платежей_в_год; Стоимость_кредита)

   

4. Обновите баланс:

   =Amount+C7
=Стоимость_кредита+C7

   

5. Выделите диапазон A7:E7 (взнос) и протяните его на одну строку вниз. Измените формулу баланса:

   =E7+C8

   

6. Выделите диапазон A8:E8 (второй платеж) и протяните его вниз до строки 30.

Понадобится 24 месяца, чтобы погасить этот кредит. Посмотрите, как с каждым платежом увеличивается основная часть и уменьшается процент.