What an amortization schedule shows you

An amortization schedule is a table that breaks down each loan payment into principal and interest. It shows you exactly how much of each monthly payment goes toward paying down the loan balance versus paying the lender's interest charge. Building one in Excel takes about ten minutes and gives you a clear picture of how your loan actually works.

Most people never see their amortization schedule unless they ask for it. Your lender has one — it's how they know what you owe — but you can build your own from scratch using only the loan amount, interest rate, and payment term. Once it's built, you can change any number and watch how the whole schedule recalculates, which is why Excel is better than a calculator for this task.

Key Takeaways

  • An amortization schedule shows how much of each payment goes to principal versus interest, and how your balance shrinks over time.
  • You need four pieces of information to build one: the loan amount, annual interest rate, number of payments, and payment frequency (usually monthly).
  • Excel's PMT function calculates your fixed monthly payment automatically; you then use straightforward formulas to split each payment into principal and interest.
  • Once built, you can change the loan amount or rate and watch the entire schedule recalculate, which helps you compare different loan offers.

Gather the four numbers you need

Before you open Excel, write down the loan amount, the annual interest rate, the loan term in years, and confirm the payment frequency. For a mortgage, that's usually monthly. For a car loan, also monthly. For some business loans, it might be quarterly or annual.

The annual interest rate is the one that matters here — not the monthly rate. If your loan documents say 6% APR, that's what you use. If they say 0.5% monthly, multiply by 12 to get 6% annual. Having these four numbers in front of you before you start typing prevents mistakes.

Set up the column headers and loan details

Open a blank Excel sheet. In the first few rows, create a summary section at the top. Put "Loan Amount" in cell A1, and the dollar amount in B1. Put "Annual Interest Rate" in A2 and the percentage in B2 (enter it as 0.06 for 6%, not 6). Put "Loan Term (Years)" in A3 and the number in B3. Put "Monthly Payment" in A4 — you'll calculate this in the next step.

Leave a blank row, then starting in row 6, create your column headers. In A6, type "Payment #". In B6, type "Payment Amount". In C6, type "Principal". In D6, type "Interest". In E6, type "Remaining Balance". These five columns are all you need.

Calculate the fixed monthly payment

Click on cell B4 (next to "Monthly Payment"). Type this formula: =PMT(B2/12,B3*12,-B1)

This formula uses Excel's PMT function. It divides your annual rate by 12 to get the monthly rate, multiplies your years by 12 to get the total number of payments, and uses the negative loan amount (the minus sign matters). The result is your fixed monthly payment. For a $300,000 loan at 6% over 30 years, this returns about $1,799.37.

The negative sign in front of B1 is important — it tells Excel you're borrowing money, not lending it. Without it, the result will be negative and confusing.

Build the payment-by-payment breakdown

In A7, type 1 (for payment number 1). In B7, type =$B$4 (this locks the payment amount to the cell you just calculated, so it stays the same every row). In C7, type a formula for principal. In D7, type a formula for interest. In E7, type a formula for the remaining balance.

For the first payment, the interest is straightforward: the remaining balance before the payment (which is the original loan amount) times the monthly interest rate. In D7, type =B1*(B2/12). This calculates one month's interest on the full loan amount.

The principal is whatever's left of your payment after interest. In C7, type =B7-D7. This subtracts the interest from the payment amount, leaving the principal portion.

The remaining balance is the loan amount minus the principal you just paid. In E7, type =B1-C7. After the first payment, this shows how much you still owe.

Copy the formulas down for all payments

Now you'll set up row 8 so that it references row 7, and then copy that pattern all the way down. In A8, type =A7+1 (this increments the payment number). In B8, type =$B$4 again. In D8, type =E7*(B$2/12) — this calculates interest on the remaining balance from the previous row. In C8, type =B8-D8. In E8, type =E7-C8 — this subtracts the principal from the previous balance.

Select cells A8 through E8. Copy them. Then select the range A9 down to however many rows you need. For a 30-year mortgage, that's row 366 (12 months × 30 years, plus your header rows). Paste. Excel will automatically adjust every formula so that each row references the row above it. Your schedule is now complete.

Scroll down to the last payment. The remaining balance should be $0 or very close to it (sometimes $0.01 due to rounding). If it's way off, check that your formulas in row 8 reference E7, not B1.

Use your schedule to compare loan offers

Now that the schedule is built, change the numbers in B1, B2, or B3 and watch everything recalculate. If a lender offers you 5.5% instead of 6%, change B2 to 0.055 and see how much your monthly payment drops and how much less interest you pay over the life of the loan. If you're deciding between a 15-year and 30-year mortgage, change B3 and compare the monthly payment and total interest.

This is where a spreadsheet beats a calculator or a loan website. You can see the exact month-by-month impact of each choice, not just a summary number. You can also print or save different versions with different assumptions, so you have them side by side when you're making a decision.

Frequently Asked Questions

Why does my remaining balance not hit exactly zero?

Rounding. Excel stores more decimal places than it displays, so the final balance might show $0.01 or -$0.01. This is normal and means your schedule is correct. If the final balance is more than a few cents off, check that your formulas in row 8 reference the row above (E7, not B1).

Can I use this for a loan with a different payment frequency?

Yes. If payments are quarterly instead of monthly, divide the annual rate by 4 instead of 12, and multiply the years by 4 instead of 12. For annual payments, divide by 1 and multiply years by 1. The structure stays the same; only the divisor changes.

What if the interest rate changes partway through the loan?

Your schedule assumes a fixed rate. If the rate adjusts, you'd need to recalculate the payment at the adjustment date and start a new schedule from that point. Most adjustable-rate loans come with a schedule from the lender that shows when and how the rate changes.

Can I add extra payments to the schedule?

Yes, but it requires a more complex formula. Add a column for "Extra Payment" and modify the remaining balance formula to subtract both the principal and the extra payment. Then you can enter extra amounts in specific rows, and the schedule will recalculate to show when the loan pays off early.