What an amortization schedule shows you
An amortization schedule is a table that breaks down each loan payment into principal and interest, and shows your remaining balance after each payment. When you build one in Excel, you can see exactly how much of your monthly mortgage or car payment goes toward interest versus the actual loan amount — and how that split changes over time.
Excel is the right tool for this because you enter the loan details once, then formulas do the math for every payment period automatically. You can change the loan amount, interest rate, or term length and watch the entire schedule recalculate in seconds. A calculator or a lender's amortization table shows you the numbers, but Excel lets you explore what-if scenarios: what if you paid extra each month, or refinanced at a lower rate.
Key Takeaways
- An amortization schedule in Excel uses four formulas — one each for interest paid, principal paid, remaining balance, and cumulative interest — that you copy down for each payment period.
- You need five pieces of information to start: the loan amount, annual interest rate, number of years, payment frequency (monthly, quarterly, etc.), and the first payment date.
- The payment amount itself comes from Excel's PMT function, which calculates what you owe each period based on the loan terms.
- Once the schedule is built, you can modify the loan amount or rate and see when ready how it changes your total interest paid and monthly payment.
Setting up your loan information at the top
Start a new Excel sheet and create a small reference section at the top where you enter the loan details once. In cells A1 through B5, set up labels and values like this:
| Cell | Label or Value |
| A1 | Loan Amount |
| B1 | 250000 (or whatever your loan is) |
| A2 | Annual Interest Rate |
| B2 | 0.065 (enter 6.5% as a decimal) |
| A3 | Loan Term (Years) |
| B3 | 30 |
| A4 | Payments Per Year |
| B4 | 12 (for monthly; use 4 for quarterly, 1 for annual) |
| A5 | Monthly Payment |
| B5 | (leave blank for now — you'll add a formula) |
In cell B5, enter the PMT formula to calculate your payment amount. Type: =PMT(B2/B4, B3*B4, -B1)
This formula divides the annual rate by the number of payments per year, multiplies the term by payments per year to get the total number of periods, and uses the negative loan amount (Excel requires this). The result is your fixed payment amount for each period.
Building the column headers and first payment row
Below your loan information, leave a blank row, then create column headers in row 7. In cells A7 through E7, type: Payment Number, Payment Date, Payment Amount, Interest Paid, Principal Paid, Remaining Balance.
In row 8, enter the first payment details. In A8, type 1. In B8, enter your first payment date (for example, =DATE(2024,2,1) for February 1, 2024). In C8, type =B$5 — this references your calculated payment amount and uses a dollar sign so the reference stays fixed when you copy the formula down.
In D8, calculate the interest paid in the first period. Type: =B1*B$2/B$4. This multiplies the original loan amount by the annual interest rate divided by payments per year. In E8, calculate principal paid: =C8-D8 (payment minus interest). In F8, calculate the remaining balance: =B$1-E8 (original loan minus principal paid in period 1).
Copying formulas down for all payment periods
Row 9 is where the formulas change slightly because the balance now depends on the previous row. In A9, type =A8+1 to increment the payment number. In B9, type =DATE(YEAR(B8),MONTH(B8)+1,DAY(B8)) to add one month to the previous date (adjust the +1 if you have quarterly or annual payments).
In C9, type =B$5 again. In D9, calculate interest on the new remaining balance: =F8*B$2/B$4 (previous balance times annual rate divided by payments per year). In E9, type =C9-D9. In F9, type =F8-E9 (previous balance minus principal paid this period).
Select the range A9:F9, copy it, then select from A10 down to the row that matches your total number of payments. For a 30-year monthly loan, that's row 368 (360 payments plus 8 header rows). Paste the formulas. Excel automatically adjusts the row references so each row calculates based on the previous one, while keeping the loan information references (B$1, B$2, B$4, B$5) constant.
Checking your work and using the schedule
Scroll to the bottom of your schedule and verify that the remaining balance in the final row is zero or very close to it (within a few cents due to rounding). If it's significantly off, check that your PMT formula in B5 is correct and that your interest rate is entered as a decimal, not a percentage.
Now you can use this schedule to answer real questions. Add up all the values in the Interest Paid column to see your total interest cost over the life of the loan. Change the loan amount in B1 and watch the entire schedule recalculate — useful for comparing a $250,000 mortgage to a $300,000 one. Adjust the interest rate in B2 to see how a rate change affects your payments and total interest.
If you want to model paying extra principal each month, add a new column called "Extra Principal" and modify the remaining balance formula to subtract both the regular principal and the extra amount. This shows you how much faster you'd pay off the loan and how much interest you'd save.
Common mistakes and how to fix them
The most frequent error is entering the interest rate as a percentage instead of a decimal. If you type 6.5 instead of 0.065 in B2, your payment will be wildly high. Fix it by dividing: change B2 to =6.5/100 or straightforward type 0.065 directly.
Another common issue is the remaining balance going negative before the final payment. This usually means your PMT formula is slightly off, or you've entered the loan term or payment frequency incorrectly. Double-check that B3 is the number of years (not months), and B4 is payments per year (12 for monthly, not 1).
If your dates are jumping by more than one month, check the date formula in column B. The formula =DATE(YEAR(B8),MONTH(B8)+1,DAY(B8)) works for monthly payments. For quarterly, change the +1 to +3. For annual, change it to +12.
Frequently Asked Questions
Can I use this schedule to compare different loan terms?
Yes. Create separate sections for each scenario — one with a 15-year term, one with 30 years — or straightforward change the values in your reference cells and note the totals. The schedule recalculates when ready, so you can see side-by-side how a shorter term increases your monthly payment but cuts your total interest significantly.
What if my interest rate changes mid-loan?
A single amortization schedule assumes a fixed rate. To model a rate change, build the schedule up to the month the rate changes, then start a new schedule below it using the remaining balance from the first schedule as the new loan amount and the new rate in your reference section.
How do I account for extra payments toward principal?
Add a column for extra principal payments. In the remaining balance formula, subtract both the regular principal and the extra amount. For example, change F9 from =F8-E9 to =F8-E9-G9, where G9 is your extra payment. The schedule will show the loan paid off earlier and total interest reduced.
Why does my remaining balance show a tiny amount left after the final payment?
Rounding in the payment calculation can leave a few cents. This is normal. In the final payment row, you can manually adjust the payment amount to equal the remaining balance plus interest, or straightforward note that the final payment will be slightly different from the others.
Can I use this for a car loan or personal loan?
Yes, the structure is identical. Enter your loan amount, annual interest rate, and term in years. Change "Payments Per Year" to 12 if it's monthly, and the formulas work the same way. The only difference is the numbers, not the math.