Amortization Schedule in Excel
An amortization schedule is a table or chart that outlines the repayment of a loan over time. It provides a detailed breakdown of each payment, showing how much of it goes toward repaying the loan principal and how much is allocated to paying interest. Amortization schedules are commonly used for mortgages, car loans, and other installment loans to help borrowers understand their payment structure and track the progress of loan repayment.
Amortization Formula
The monthly payment in an amortization schedule can be calculated using the following formula:
PMT = (P*r*(1 + r)^n) / ((1 + r)^n – 1)
where:
- PMT is the monthly payment
- P is the principal loan amount
- r is the monthly interest rate (annual interest rate divided by 12 and expressed as a decimal)
- n is the total number of monthly payments
Steps to Create an Amortization Schedule in Excel
To create an amortization schedule in Excel, you can follow these steps:
Set up your worksheet by creating a new Excel worksheet and labeling the columns as follows: Payment Number, Payment, Principal Payment, Interest Payment, Total Payment, Remaining Balance, and Interest Rate.
Input your loan details by entering the loan amount (principal), annual interest rate, and loan term in months into separate cells.
Calculate the monthly payment using the loan amortization formula:
=PMT(D1, C1, -A1)
Where:
- D1 is the cell containing the monthly interest rate
- C1 is the cell containing the loan term in months
- A1 is the cell containing the loan amount
Create the amortization schedule by entering the payment number, payment, interest payment, principal payment, total payment, and remaining balance for each month in the corresponding columns. You can use the following formulas to calculate the interest payment, principal payment, and remaining balance:
Interest Payment = Payment * Monthly Interest Rate Principal Payment = Payment – Interest Payment Remaining Balance = Loan Amount – SUM(Principal Payment)
Auto-fill the formulas in the Interest Payment, Principal Payment, Total Payment, and Remaining Balance columns down to match the total number of payments in your loan.
Amortization schedules can be used to create a budget. The completed amortization schedule will now show you how much of each payment goes toward principal and interest and how much is left to pay on the loan at any given time. You’ll be able to see how, over time, the interest portion decreases while the principal portion increases with each payment.
