What an amortization table shows you

An amortization table is a spreadsheet that breaks down each loan payment into principal and interest, and shows your remaining balance after each payment. Instead of seeing only your monthly payment amount, you see exactly how much of each payment goes toward interest, how much reduces what you owe, and what your balance is at any point in the loan.

This matters because the split between principal and interest changes with every payment. Early payments are mostly interest; later payments are mostly principal. An amortization table makes that visible, which helps you understand the true cost of a loan and what happens if you pay extra.

Key Takeaways

  • An amortization table needs four pieces of information: the loan amount, the interest rate, the loan term in months, and the monthly payment amount.
  • Excel's PMT function calculates the monthly payment automatically once you enter the loan details, so you do not have to do the math by hand.
  • The table itself uses straightforward formulas that repeat: each row subtracts interest from the payment to find principal, then subtracts principal from the balance to find the new balance.
  • You can use the finished table to see how extra payments reduce the loan term, or to calculate how much interest you will pay over the life of the loan.

Setting up the loan information section

Start a new Excel spreadsheet and create a small section at the top to hold the loan details. In cell A1, type "Loan Amount". In B1, enter the amount borrowed — for example, 250000 for a mortgage. In A2, type "Annual Interest Rate" and in B2 enter the rate as a decimal (0.065 for 6.5 percent). In A3, type "Loan Term (Months)" and in B3 enter the number of months — 360 for a 30-year mortgage.

In A4, type "Monthly Payment". In B4, enter the formula =PMT(B2/12, B3, -B1). This formula divides the annual rate by 12 to get the monthly rate, uses the term in months, and uses the loan amount as a negative number (Excel's convention for money borrowed). The result is the fixed monthly payment amount. Excel will show this as a positive number.

Leave a blank row, then in A6 type "Month", in B6 type "Payment", in C6 type "Principal", in D6 type "Interest", and in E6 type "Balance". These are your column headers for the amortization table itself.

Building the first row of the table

In A7, type 1 for the first month. In B7, enter the formula =B$4 — this pulls the monthly payment amount from your setup section and uses a dollar sign before the 4 so the reference stays locked when you copy the formula down. In D7, enter =B$2/12*E6 — this calculates interest by multiplying the monthly rate by the previous balance. For the first payment, the previous balance is the original loan amount, which you will reference in E6.

In C7, enter =B7-D7 — this subtracts the interest from the payment to find how much principal you paid. In E7, enter =E6-C7 — this subtracts the principal paid from the previous balance to find your new balance. In E6 (the row above), enter your original loan amount from B1, so type =B1.

Copying the formulas down for all months

Select cells A7 through E7. Copy them. Click on A8 and select down to the row that matches your loan term — for a 360-month loan, select through row 366 (row 7 plus 360 months). Paste the formulas. Excel will automatically adjust the row references so each row calculates based on the previous row's balance, while keeping the rate and payment locked to your setup section.

The balance in the final row should be very close to zero — usually within a few cents due to rounding. If it is significantly off, check that your formulas in row 7 are correct and that you pasted them to the right number of rows.

Reading and using the finished table

Scan down the Interest column to see how interest payments shrink over time. In the first month, most of your payment goes to interest. By the final month, almost all of it goes to principal. The Balance column shows what you still owe after each payment.

To find the total interest paid over the life of the loan, click an empty cell and enter =SUM(D7:D366) (adjusting the row numbers to match your table). This adds up every interest payment. Subtract this from the total of all payments to verify it matches your principal amount, or straightforward compare it to the loan amount to see how much extra you paid in interest.

Modeling extra payments

To see what happens if you pay extra each month, modify the Payment column. Instead of using the fixed payment in B4, enter a higher amount in specific rows. For example, in B10, type 1500 instead of the formula. The interest and principal columns will recalculate automatically, and you will see the balance drop faster. The loan will end before your original term — the balance will hit zero and stay there.

You can also create a second table next to the first one to compare scenarios side by side: one showing the standard payment, another showing what happens if you pay an extra 200 per month. This makes it straightforward to see how much faster you would pay off the loan and how much interest you would save.

Troubleshooting common problems

If the balance does not reach zero, check that your PMT formula in B4 is correct and that you copied it exactly. A common mistake is forgetting the negative sign before B1 — Excel needs it to calculate the payment correctly. If the balance goes negative before the final row, your payment amount is higher than necessary, which is fine for extra-payment scenarios but suggests an error if you are using the PMT formula.

If the Interest column shows the same value in every row, you likely forgot the dollar signs in the rate reference (B$2/12). Without them, the formula adjusts to the wrong cell as you copy it down. If the Balance column shows the same value repeatedly, check that E7 references E6 correctly and that you pasted the formulas to enough rows.

Frequently Asked Questions

Can I use this table for a loan with a variable interest rate?

Not with a single formula. A variable-rate loan changes the interest rate at set points, so you would need to split the table into sections. Calculate each section with its own rate, starting the next section with the balance from the previous one. This is more work but shows you exactly when the rate changes and how it affects your payments.

What if my loan has a balloon payment at the end?

Modify the final payment in the Payment column to include the balloon amount. The Interest and Principal columns will recalculate for that row, and the Balance will drop to zero (or close to it). The rest of the table stays the same because the balloon payment only affects the last month.

How do I add a column for cumulative interest paid so far?

In column F, type "Cumulative Interest" as a header. In F7, enter =D7. In F8, enter =F7+D8. Copy that formula down. Each row will show the total interest paid from month 1 through that month, making it straightforward to see how much interest you have paid at any point.

Can I use this for a loan where payments change, like a graduated payment mortgage?

Yes. Instead of using the PMT formula in every row, enter the actual payment amount for each period. The Interest and Principal columns will calculate correctly based on whatever payment you enter. This works for any loan where you know the payment schedule in advance.