How to Create an Amortization Table in Excel

šŸ“‰ How to Create an Amortization Table in Excel

by

Need to track your loan payments or visualize how interest and principal change over time? Excel makes it easy to build a professional amortization table with automatic calculations.

šŸ’” What is an Amortization Table?

An amortization table breaks down each payment on a loan ā€” showing how much goes toward interest, how much reduces the principal, and what the remaining balance is. This is especially useful for:

  • šŸ  Mortgages
  • šŸš— Car loans
  • šŸŽ“ Student loans
  • šŸ¢ Business loans

šŸ›  Step-by-Step: Build Your Amortization Table in Excel

1. Set Your Loan Parameters

Create input cells at the top of your spreadsheet for:

  • Loan Amount (e.g., $100,000)
  • Annual Interest Rate (e.g., 5%)
  • Loan Term (e.g., 15 years)

2. Calculate the Monthly Payment

Use the built-in PMT function:

=PMT(InterestRate/12, TermYears*12, -LoanAmount)

This gives you a fixed monthly payment.

3. Set Up the Table Columns

Create headers:

  • Payment #
  • Payment
  • Principal
  • Interest
  • Balance

4. Add Formulas

Each row will calculate the portion of the payment that goes to interest, principal, and the remaining loan balance. Use formulas like:

  • =PreviousBalance * MonthlyRate for Interest
  • =Payment - Interest for Principal
  • =PreviousBalance - Principal for new Balance

Drag these formulas down for the full loan term.

5. Format Your Table

Use borders, currency format, and center alignment for readability. Conditional formatting can help highlight:

  • Final payments
  • Interest-heavy months

šŸ“„ Download a Ready-to-Use Amortization Table Template

Save time with our prebuilt, professional-grade Excel amortization table. Just input your loan details and see the full schedule instantly.

šŸ“„ Download Amortization Table Template

šŸ“Š Bonus: Visualize Your Loan

Create charts to show:

  • Interest vs. principal over time
  • Total cost of the loan
  • Loan balance trend

šŸš€ Final Thoughts

An Excel amortization table is a powerful tool for understanding your loan and planning your finances. Whether you're paying off a mortgage or planning a new loan, this template gives you clarity and control.

Need help customizing it for a specific loan scenario? Iā€™m happy to help tailor it for you!