How to create a loan amortization schedule In Excel

Oct 31, 2020 • edited Nov 03, 2020

How to create a loan amortization schedule In Excel

This example teaches you how to create a loan amortization schedule in Excel.

1. We use the PMT function to calculate the monthly payment on a loan with an annual interest rate of 5%, a 2-year duration and a present value (amount borrowed) of $20,000. We have named the input cells.

PMT function

2. Use the PPMT function to calculate the principal part of the payment. The second argument specifies the payment number.

Principal Part

3. Use the IPMT function to calculate the interest part of the payment. The second argument specifies the payment number.

Interest Part

4. Update the balance.

Update Balance

5. Select the range A7:E7 (first payment) and drag it down one row. Change the balance formula.

Different Balance Formula

6. Select the range A8:E8 (second payment) and drag it down to row 30.

Loan Amortization Schedule

It takes 24 months to pay off this loan. See how the principal part increases and the interest part decreases with each payment.

#Tutorial#How To#Functions

How to use Ifs function In Excel

How to find the most frequently occurring word in Excel