How to Use the Amortization Formula in Excel

Calculating loan payments, tracking principal and interest, and building amortization schedules are everyday needs for business owners, homeowners, and finance professionals. Excel provides powerful formulas and templates to break down your loan, so you can understand your payment structure, make informed financial decisions, and answer “how much will this cost me?” in seconds. In this guide, you’ll learn how to use Excel’s amortization formulas and build a full amortization schedule step by step.

Key Takeaways

• Use the PMT function in Excel to calculate fixed monthly loan payments.
• Amortization formulas show how each payment splits into principal and interest over time.
• The PPMT and IPMT functions break out the principal and interest for each payment period.
• You can build dynamic amortization tables using formulas and helper columns.
• VBA and Power Query allow for advanced automation and custom loan models.

About Amortization Formula in Excel

Amortization is the process of paying off a loan with equal payments over a set period. Each payment consists of principal and interest. Early payments go mostly to interest; later payments go mostly to principal. Excel can automate this breakdown, helping you analyze loans for mortgages, auto loans, student loans, and business financing.

Principal: The amount you borrowed

Interest rate: Annual rate (as a decimal, e.g., 5% = 0.05)

Number of periods: Total payments (months, years, etc.)

Payment: Fixed amount due each period

How to Calculate Amortization in Excel

Step 1: Enter Your Loan Details

Principal (Loan Amount): e.g., 100,000

Annual Interest Rate: e.g., 5%

Loan Term (in years): e.g., 15

Payments Per Year: e.g., 12 (monthly payments)

Enter these values in cells B2:B5.

Step 2: Calculate Monthly Payment with PMT

Formula for monthly payment in cell B6:=PMT(B3/B5, B4*B5, -B2)

Result: Fixed monthly payment (shows as negative; use a minus sign for outgoing payments).

Step 3: Build the Amortization Table

List payment periods (1, 2, …, N) in column E.

For each period, use:

• Interest Payment: =IPMT($B$3/$B$5, E2, $B$4*$B$5, -$B$2)
• Principal Payment: =PPMT($B$3/$B$5, E2, $B$4*$B$5, -$B$2)
• Remaining Balance: =Previous Balance - Principal Payment

Copy formulas down for each period.

Common Mistakes and Tips

Mistake: Wrong sign in the PMT formula
Use a negative sign on the loan amount for outgoing payments.

Mistake: Mismatched periods and rates
Match rate (monthly/yearly) and periods (months/years) units in all formulas.

Tip: Lock cell references with $ in your formulas
This lets you copy formulas down your table without errors.

Tip: Format as currency for easier reading
Apply currency formatting to all payment, principal, and interest columns.

Tip: Use a dynamic table or Excel Table for easier filtering and charting

Bonus Tips and Advanced Scenarios

Power Query for Multiple Loans
Load multiple loan records and automate amortization table creation with Power Query’s grouping and custom columns.

VBA Macro for Amortization Schedule

Sub CreateAmortizationSchedule()
    Dim principal As Double, rate As Double, periods As Integer, payment As Double
    Dim ws As Worksheet, i As Integer
    Set ws = Sheets.Add
    principal = 100000
    rate = 0.05 / 12
    periods = 180
    payment = Application.WorksheetFunction.Pmt(rate, periods, -principal)
    ws.Cells(1, 1) = "Period"
    ws.Cells(1, 2) = "Payment"
    ws.Cells(1, 3) = "Interest"
    ws.Cells(1, 4) = "Principal"
    ws.Cells(1, 5) = "Balance"
    Dim balance As Double
    balance = principal
    For i = 1 To periods
        ws.Cells(i + 1, 1) = i
        ws.Cells(i + 1, 2) = payment
        ws.Cells(i + 1, 3) = balance * rate
        ws.Cells(i + 1, 4) = payment - ws.Cells(i + 1, 3)
        balance = balance - ws.Cells(i + 1, 4)
        ws.Cells(i + 1, 5) = balance
    Next i
End Sub

Show Running Totals and Cumulative Interest
Add helper columns for running total interest and cumulative payments for deeper analysis.

Visualize the Breakdown
Use Excel charts to show how interest and principal portions change over time.

Practical Use Cases

Mortgage payment tracking

Auto loan or equipment financing schedules

Student loan planning and comparison

Business loan proposals and what-if scenarios

Comparing loan terms and early payoff impacts

FAQ

Q: What is the difference between PMT, PPMT, and IPMT?
A: PMT gives the total payment, PPMT gives the principal portion, IPMT gives the interest portion for a given period.

Q: Can I use these formulas for different payment frequencies?
A: Yes, just adjust the rate and periods (e.g., for quarterly, set periods per year to 4).

Q: How do I handle extra or early payments?
A: Add extra payments as a column and subtract from the balance in each row.

Q: Can I build an amortization schedule for variable rates?
A: Yes, but you’ll need to update the interest rate for each period manually or with more advanced formulas.

Q: How do I avoid formula errors?
A: Lock cell references, check signs, and match periods and rate units throughout your table.