Loan repayment schedule in Excel

This example will show you how to create a loan repayment schedule in Excel.

1. We use the function PMT (PLT) to calculate the monthly payment on a loan with an annual interest rate of 5%, a duration of 2 years and a cost (loan amount) of $20000. We gave names to the cells with the original data:
   

   Cell	Value	First name	Name (rus)
   B1	5%	AnnualInterestRate	Annual_interest_rate
   B2	2 years	Years	Credit term
   B3	12	PaymentsPerYear	Payments_per_year
   B4	$ 20000	Amount	Loan_cost

   As a result, we got the expression:

   =PMT(AnnualInterestRate/PaymentsPerYear, Years*PaymentsPerYear, Amount)

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

2. Use the function PPMT (OSPLT) to calculate the main payout. The second argument specifies the payment number:

   =PPMT(AnnualInterestRate/PaymentsPerYear, A7, Years*PaymentsPerYear, Amount)

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

   

3. Use the function IPMT (IPMT) to calculate the interest payment. The second argument specifies the payment number:

   =IPMT(AnnualInterestRate/PaymentsPerYear, A7, Years*PaymentsPerYear, Amount)

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

   

4. Update balance:

   =Amount+C7

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

   

5. Highlight a range A7: E7 (contribution) and drag it down one line. Change the balance formula:

   =E7+C8

   

6. Highlight a range A8: E8 (second payment) and drag it down to line 30.

It will take 24 months to repay this loan. See how with each payment the principal increases and the percentage decreases.