What Is an Amortization Schedule?
An amortization schedule is a table that lists each loan payment over the life of the loan, showing how much of each payment goes toward principal (the amount borrowed) and how much goes toward interest. It also displays the remaining balance after each payment.
For a fixed-rate amortized loan, the periodic payment amount is fixed for the entire term, but the split between principal and interest changes over time. Early payments are mostly interest; later payments are mostly principal. This is because interest is calculated on the remaining balance, which decreases with each payment. Note that with an adjustable-rate loan, the payment amount can change when the interest rate adjusts.
Spreadsheet formats like Excel or Google Sheets are excellent for editing and printing. For a fixed layout that is easy to share and print, PDF is the best choice. You can also use CSV if you need to import the data into another application.
- The schedule provides a complete breakdown of every payment, making it easier to track loan progress.
- It helps borrowers see the total interest paid over the life of the loan.
- It is used for mortgages, auto loans, personal loans, and other installment loans.
Key Inputs: Loan Amount, Interest Rate, and Term
To calculate an amortization schedule, you need three main inputs: the loan amount (principal), the annual interest rate, and the loan term (length of time to repay).
The loan amount is the total amount borrowed. The interest rate is the annual rate charged by the lender, usually expressed as a percentage. The loan term can be in years or months; for example, a 30-year mortgage has a term of 360 months.
- Loan amount: The initial balance, such as $200,000 for a mortgage.
- Annual interest rate: The yearly rate, e.g., 4.5% per year.
- Loan term: The number of years or months, e.g., 30 years or 360 months.
The Amortization Formula
The monthly payment for an amortized loan is calculated using the following standard formula:
M = P × [r(1+r)^n] / [(1+r)^n – 1]
Where: M is the monthly payment, P is the principal loan amount, r is the monthly interest rate (annual rate divided by 12), and n is the total number of payments (loan term in months).
- Convert the annual interest rate to a monthly rate by dividing by 12.
- Calculate the number of monthly payments by multiplying the number of years by 12.
- Plug the values into the formula to get the fixed monthly payment.
Example: Calculating a Monthly Payment
Let’s work through an example. Suppose you take out a $200,000 mortgage at an annual interest rate of 4.5% for 30 years.
First, convert the annual rate to a monthly rate: 4.5% / 12 = 0.375% per month. In decimal form, r = 0.00375.
Next, calculate the number of monthly payments: 30 years × 12 months/year = 360 months.
Plug these values into the formula: M = 200,000 × [0.00375(1.00375)^360] / [(1.00375)^360 – 1].
The result is a monthly payment of approximately $1,013.37.
- Principal (P): $200,000
- Monthly interest rate (r): 0.00375 (0.375%)
- Total payments (n): 360
- Monthly payment (M): $1,013.37
Building a Sample Amortization Table
After determining the monthly payment, you can build an amortization table. For each payment, compute the interest for the period (remaining balance × monthly interest rate), then subtract that interest from the payment to find the principal portion. Finally, subtract the principal portion from the remaining balance to get the new balance.
Repeat this process for each month until the balance reaches zero.
- Payment 1: Interest = $200,000 × 0.00375 = $750.00. Principal portion = $1,013.37 – $750.00 = $263.37. New balance = $200,000 – $263.37 = $199,736.63.
- Payment 2: Interest = $199,736.63 × 0.00375 = $749.01. Principal portion = $264.36. New balance = $199,472.27.
- As payments continue, the interest portion decreases and the principal portion increases.
| Payment Number | Payment | Interest | Principal | Remaining Balance |
|---|---|---|---|---|
| 1 | $1,013.37 | $750.00 | $263.37 | $199,736.63 |
| 2 | $1,013.37 | $749.01 | $264.36 | $199,472.27 |
| 3 | $1,013.37 | $748.02 | $265.35 | $199,206.92 |
| 4 | $1,013.37 | $747.03 | $266.34 | $198,940.58 |
| 5 | $1,013.37 | $746.03 | $267.34 | $198,673.24 |
How to Print or Download a Schedule
Once you have created your amortization schedule, you can print it or save it for reference. Many spreadsheet programs, such as Microsoft Excel or Google Sheets, allow you to format the schedule as a table and then print it.
For long schedules, such as a 30-year mortgage, you may want to use a portable document format (PDF) to preserve the layout and ease of sharing. You can also use online tools that generate a downloadable PDF or spreadsheet file.
- Use spreadsheet software to create the schedule and print it with page breaks for each year.
- Choose a PDF format for a fixed, professional-looking document.
- Consider saving the schedule as a CSV file if you need to import it into other software.
Frequently asked questions
What is the difference between principal and interest?
Principal is the amount of money you originally borrowed. Interest is the cost of borrowing that money, calculated as a percentage of the remaining balance. Each payment covers both, but early payments are mostly interest, while later payments are mostly principal.
Can I use this method for auto loans and personal loans?
Yes. The amortization formula applies equally to auto loans, personal loans, and any fixed-rate installment loan. As long as the interest rate is fixed and the payment schedule is amortized, the same calculation method works.
Why does my payment amount stay the same each month?
For a standard fixed-rate amortized loan, the payment is calculated to pay off the loan in equal installments over the term. The lender calculates a fixed payment so that the present value of all payments equals the loan amount. In each payment, the portion allocated to interest decreases and the portion to principal increases, but the total payment remains constant.