An amortization schedule template shows exactly how every loan payment is split between principal and interest, and what your balance will be after each payment. Below you can download three free amortization schedule templates in Excel: a standard loan amortization schedule for mortgages and personal loans, a schedule with extra payments that shows how much time and interest you save, and a car loan amortization schedule that works out the amount financed from the price, trade-in, down payment, and sales tax.
Each template is an editable .xlsx file. Type your loan details into the yellow cells and the full payment table fills in automatically. The files also work in Google Sheets and LibreOffice Calc, and they print neatly if you want a paper copy for your records.
What Is an Amortization Schedule?
An amortization schedule is a table that lists every payment on a loan from the first payment to the last. For each payment it shows the payment date, the amount of the payment, how much of that amount goes to interest, how much reduces the principal, and the remaining balance. When the last payment is made, the balance reaches zero.
Amortization applies to loans with equal payments over a fixed term, such as home mortgages, car loans, student loans, and personal loans. Because interest is charged on the outstanding balance, the early payments contain a large share of interest and a small share of principal. As the balance falls, the interest part shrinks and the principal part grows, even though the total payment stays the same.
Why Use an Amortization Schedule Template in Excel?
- You see the true cost of a loan. The total interest and total cost are shown before you sign, so you can compare offers fairly.
- Payments are calculated for you. The template uses the standard payment formula, so you do not need to work out the math by hand.
- You can test extra payments. See how a small extra amount each month shortens the loan and cuts interest.
- Every payment is planned. The payment dates, balances, and payoff date are listed in one place.
- It is easy to change. Update the rate, term, or amount and the whole schedule recalculates instantly.
- No special software is needed. Everything runs in Excel with ordinary formulas, so you can inspect and adjust them.
What to Include in an Amortization Schedule
- Loan amount: the principal you borrow, after any down payment.
- Annual interest rate: the yearly rate stated by the lender.
- Loan term: the number of years or months you will repay the loan.
- First payment date: used to date every later payment.
- Payment number and date: a running count of each installment.
- Beginning balance: the amount owed at the start of the period.
- Payment amount: the fixed payment, with the last payment adjusted for rounding.
- Principal and interest: the two parts of each payment.
- Ending balance: the amount still owed after the payment.
- Totals: total interest paid, total cost of the loan, and the payoff date.
How to Calculate a Loan Payment and Interest
The fixed monthly payment on an amortizing loan is found with the PMT function. In Excel the formula looks like =PMT(rate/12, years*12, -loan amount). For a $250,000 loan at 6.5% for 30 years, the monthly payment is $1,580.17.
To split any single payment, multiply the beginning balance by the monthly interest rate (the annual rate divided by 12). That is the interest for the month. Subtract it from the payment and the remainder is principal. In the first month of the example, interest is $250,000 × 6.5% ÷ 12, which is about $1,354.17, so only about $226 of the first payment reduces the balance. The template does this for every row, rounds each amount to the cent, and makes the final payment close out the balance exactly.
Download the Free Amortization Schedule Templates
All three amortization schedule templates are hosted on this website and are free to download. Each preview image matches the download button directly below it.
1. Loan Amortization Schedule
A standard monthly schedule for a mortgage, personal loan, or any fixed-rate loan with a term of up to 30 years. Enter the loan amount, interest rate, term, and first payment date. The sheet shows the monthly payment, total interest, total cost of the loan, the share of the cost that is interest, and the payoff date, followed by a full 360-row payment table that shortens automatically for shorter terms.
2. Amortization Schedule With Extra Payments
This schedule adds a fixed extra payment each month and a one-time lump sum paid at a payment number you choose, such as a bonus or tax refund. It then shows the number of payments saved, the interest saved, and the new payoff date. In the sample, a $300,000 loan at 6% over 30 years with an extra $200 a month and a $10,000 lump sum after five years saves 97 payments and about $117,000 of interest.
3. Car Loan Amortization Schedule
A schedule built for vehicle financing. Enter the vehicle price, trade-in value, down payment, sales tax rate, and title and registration fees, and the template calculates the amount financed, the monthly payment, and total interest. The payment table supports terms of up to 84 months, and it shows how much interest you pay as a percentage of the car’s price.
Tips for Using an Amortization Schedule
- Compare several offers. Run the same loan amount through different rates and terms before you choose a lender.
- Check the payment against the lender’s quote. A small difference may mean your quote includes insurance, taxes, or fees that are not part of the loan payment.
- Remember that the rate is annual. The template divides it by 12 for monthly payments, which is how most loans are quoted.
- Plan extra payments early. Because early payments are mostly interest, an extra payment made in the first years saves the most.
- Confirm that extra payments go to principal. Ask your lender to apply additional money to the principal balance and check that there is no prepayment penalty.
- Keep a copy. Save the schedule with your loan documents so you can compare it with your lender’s statements.
- Update after a rate change. For an adjustable-rate loan, create a new schedule from the current balance when the rate resets.
How Extra Payments Save Interest
Extra payments are one of the simplest ways to reduce the cost of a loan. Every extra dollar goes straight to principal, which lowers the balance on which future interest is calculated. That means the following month’s interest is lower, so more of your regular payment goes to principal, and the effect snowballs over time.
The second template makes this visible. Change the extra monthly payment from $0 to $100, $200, or $500 and watch the payoff date move forward and the interest saved grow. Even small amounts matter on a long loan. A one-time lump sum early in the loan can save as much as many months of small extra payments, so use the one-time payment cell to test windfalls as well.
Mortgage vs. Car Loan Amortization
Mortgages and car loans use the same math, but they behave differently in practice. A mortgage usually runs 15 or 30 years, so the interest is spread over many years and the total interest can be as large as the amount you borrowed. A car loan is shorter, often 36 to 84 months, so total interest is much smaller, but longer terms can still leave you owing more than the car is worth.
For a car loan, the amount financed is not the sticker price. It is the price minus the trade-in and down payment, plus sales tax and fees if you finance them. The car loan template calculates this for you, so the monthly payment matches what you will actually borrow. A shorter term raises the monthly payment but reduces total interest, and the template makes the trade-off easy to see.
How to Customize the Amortization Schedule Templates
All inputs are in the yellow cells, and all results update from the formulas. To use the schedule for bi-weekly payments, divide the monthly payment by two and shorten the term accordingly, or use the extra payment template and add one-twelfth of a payment each month. To show a different currency, select the money cells and choose another currency format from the Number Format menu.
If you want to add a column for property tax or insurance, insert it to the right of the table and keep it outside the principal and interest columns so the balance stays correct. You can also copy the schedule sheet and keep one version for each loan you want to compare, such as a 15-year and a 30-year option.
Please note these templates are for planning and education. Your lender’s official statement will always control, and actual payments can differ slightly because of rounding rules, payment dates, or fees.
You can also check these important templates like the monthly budget template for fitting the payment into your income, the family budget planner template for planning household spending, and the cash flow projection template for checking future cash needs.
Frequently Asked Questions
What is an amortization schedule?
An amortization schedule is a table that shows every payment on a loan, how much of each payment is interest and how much is principal, and the balance remaining after each payment. It ends with a zero balance on the final payment.
How do I make an amortization schedule in Excel?
Download the loan amortization schedule template on this page, then enter the loan amount, annual interest rate, term in years, and first payment date. The monthly payment, interest, principal, and balance for every payment are calculated automatically.
Why is most of my early payment interest?
Interest is charged on the outstanding balance, which is highest at the start of the loan. As you pay down the balance, interest falls and a larger part of each fixed payment goes to principal.
How do extra payments change an amortization schedule?
Extra payments reduce the principal faster, so less interest is charged on later payments. The loan is paid off earlier and total interest falls. Use the extra payment template to test different monthly amounts and one-time payments.
Can I use this template for a car loan or personal loan?
Yes. The standard schedule works for any fixed-rate loan with monthly payments, and the car loan template adds fields for trade-in, down payment, sales tax, and fees. Terms of up to 84 months are supported for the car loan schedule.
