How to Prepare an Amortization Schedule in Excel: Easy Guide

An amortization schedule is basically your loan’s “receipt in slow motion.” It shows every payment you’ll make,
how much of each payment goes to interest, how much goes to principal,
and what your remaining balance looks like after each payment. And yesat the start, it can feel like your money
is being politely escorted straight to Interest Land. Don’t panic. That’s normal.

The good news: Excel is ridiculously good at this. With a few inputs and three core functions (PMT,
IPMT, PPMT), you can build a clean, customizable amortization schedule you can
reuse for mortgages, auto loans, personal loans, or any fixed-rate loan with regular payments.

What You’ll Build (and Why It Matters)

By the end of this guide, you’ll have a table that includes:

• Payment number and date
• Beginning balance
• Total payment (principal + interest)
• Interest portion
• Principal portion
• Ending balance
• (Optional) Extra payments and early payoff tracking

This schedule is useful for budgeting, comparing loan offers, estimating total interest, and running “what if I pay
$100 extra?” scenarios without needing a separate calculator every time.

Step 1: Set Up Your Loan Inputs (The “Control Panel”)

Open a new worksheet and create a small input block near the top. This keeps your formulas readable and makes the
schedule easy to update later.

Recommended input cells

Tip: Format B3 as Percentage and B6 as Date. Your future self will thank you.

Step 2: Calculate the Monthly (or Periodic) Payment with PMT

Most fixed-rate loans have a constant payment amount each period. In Excel, that’s the PMT function:

• rate = interest rate per period (annual rate divided by payments per year)
• nper = total number of payments (years times payments per year)
• pv = present value (your loan amount)

In a cell like B9 (label it “Payment”), use:

Why ABS()? Excel financial functions follow a cash-flow sign convention where money you pay often shows as
negative. ABS() flips it positive so your schedule looks like normal human math.

Step 3: Build the Amortization Table Structure

Now create your table headers starting around row 12 (adjust as you like). Example:

• A12: Payment #
• B12: Payment Date
• C12: Beginning Balance
• D12: Payment
• E12: Interest
• F12: Principal
• G12: Extra Payment
• H12: Ending Balance

Row 13: the first payment

Set your first row like this:

• A13 (Payment #): 1
• B13 (Payment Date): =$B$6
• C13 (Beginning Balance): =$B$2
• D13 (Payment): =$B$9 (or wherever your PMT result is)
• G13 (Extra Payment): =$B$7

Step 4: Calculate Interest and Principal with IPMT and PPMT

The two functions that make amortization schedules feel like magic are:

• IPMT: interest portion of a payment for a given period
• PPMT: principal portion of a payment for a given period

For your first payment row (row 13), use:

E13 (Interest)

F13 (Principal)

Notice how A13 is the payment number (the period). That’s what will change as you copy formulas down.

Step 5: Update the Balance (Where the Loan Actually Shrinks)

The balance decreases by the amount of principal you pay. If you add an extra payment, it (usually) goes to principal too.

H13 (Ending Balance)

The MAX(0, ...) keeps your ending balance from going negative in the final row. (Because negative debt would
be delightful, but lenders tend to be weird about that.)

Step 6: Copy Down for the Full Term

Now create the next row (row 14) and copy everything down for as many payments as you need.

A14 (Payment #)

B14 (Payment Date)

If your payments are monthly, this increments one month. If you switch payments per year, the formula still behaves sensibly.

C14 (Beginning Balance)

Copy your Payment, Interest, Principal, Extra Payment, and Ending Balance formulas down for the entire schedule.
Your schedule should now show:
interest starting high, principal starting low, and gradually flipping over time.

A Quick Mini-Example (So You Can Sanity-Check Your Output)

Using the example inputs (250,000 loan, 6.5% annual interest, 30 years, monthly payments), your first few lines should resemble:

The exact dollar amounts depend on rounding and formatting, but the “shape” should be correct:
interest declines over time while principal increases.

Step 7: Add Totals (Total Interest, Total Paid, and Other Truth Bombs)

Once your table is built, you can quickly calculate totals:

• Total Interest: sum of the Interest column
• Total Principal Paid: sum of the Principal column (should equal the loan amount, unless you stopped early)
• Total Paid: sum of Payment column (+ extra payments if used)

If you want fast totals without summing the whole column, Excel also offers cumulative functions like
CUMIPMT and CUMPRINC to calculate interest/principal paid over a range of periods.
They’re especially handy if you want “interest paid in year 1” or “principal paid through month 36.”

Step 8: Extra Payments (The “Own Your Home Sooner” Button)

Adding extra payments can reduce total interest and shorten the loan termsometimes dramatically.
Your table already supports this if you included an “Extra Payment” column.

Make extra payments smarter with guardrails

Two practical improvements:

1. Don’t overpay the final balance. If you’re close to payoff, limit the extra payment so you don’t push the balance negative.
   You can replace G13 (and down) with:

   (This assumes your principal portion is already calculated.)

2. Stop when the balance hits zero. You can conditionally blank out rows after payoff with IF(H13=0,"",...),
   or simply filter to show only rows with balance > 0.

Common Excel Amortization Mistakes (and Quick Fixes)

1) “My payment is negative”

Totally normal. Use ABS() around PMT/IPMT/PPMT, or put a minus sign in front of PMT:
=-PMT(...).

2) “My numbers look wrongway too high or too low”

This usually comes from mismatched periods. If you’re paying monthly, your rate must be monthly (annual/12),
and your number of payments must be total months (years*12). If you switch to biweekly, everything must switch together.

3) “Interest doesn’t shrink over time”

That’s a red flag. Check that your beginning balance is linked to the prior ending balance (C14 = H13), and that the period
argument in IPMT/PPMT is referencing the correct payment number cell in each row.

4) “My last balance is a tiny negative number (like -0.01)”

That’s rounding. Use MAX(0,...) on the ending balance and consider rounding currency fields to two decimals.

Make It Look Professional (Because You’ll Probably Screenshot It)

• Format currency columns as Currency with two decimals.
• Freeze the header row so you can scroll like a civilized person.
• Turn the schedule into an Excel Table (Ctrl+T) for banded rows and easy filters.
• Add conditional formatting to highlight the payoff row (where Ending Balance = 0).
• Optional: Insert a simple line chart of Ending Balance over time for a quick “debt goes down” dopamine hit.

When to Double-Check with a Second Source

Excel is accurate, but you should still verify your schedule against your lender’s documentation if:

• Your loan has fees folded into payments (APR vs. interest rate differences)
• You have escrow components (taxes/insurance) mixed into the payment
• The loan uses non-standard compounding or irregular payment dates
• You have an adjustable-rate period (ARM) that changes the interest rate midstream

For plain-vanilla fixed-rate loans, this schedule will match the standard amortization math closely. For anything fancy,
treat your Excel schedule as a powerful estimate and modeling toolnot a legal contract.

of Real-Life “Amortization Schedule in Excel” Experience (The Fun Part)

If you’ve ever built an amortization schedule in Excel, you already know it starts out feeling like a calm weekend project
and ends like a small mystery novel. The first time most people try it, the spreadsheet works… sort of. The payment shows
up negative, the interest looks suspiciously large, and the ending balance finishes at -$0.03like Excel is quietly charging
you three cents for emotional damage.

The most common “aha” moment is realizing that amortization is less about fancy formulas and more about keeping your time units
consistent. Monthly loan? Monthly rate. Monthly number of periods. Monthly date steps. The minute someone plugs a 6.5% annual
rate directly into PMT without dividing by 12, the payment jumps like it just saw a ghost. Suddenly the monthly payment
looks like a private jet lease, and the schedule becomes a horror story called Attack of the Misplaced Decimal.

Then there’s the sign convention. Excel isn’t being petty when it returns negatives; it’s being “finance-y.” It wants cash flows
to behave like real cash flows: money out is negative, money in is positive. That’s why wrapping everything in ABS()
feels like putting sunglasses on the spreadsheet. Same math, less drama.

Extra payments are where the spreadsheet becomes genuinely fun. People love running “what if” scenarios: “What if I add $50?”
“What if I add $200?” “What if I pay weekly instead of monthly?” This is also where you learn the difference between
making a bigger payment and actually reducing principal. If you model extra payments as extra principal,
your balance drops faster, interest drops faster, and the schedule ends earlierlike watching a Netflix series on 2x speed, but for debt.
The first time someone sees the payoff date move up by years, the spreadsheet basically becomes a motivational poster.

Real life also teaches you that the “monthly payment” on a mortgage statement might include more than principal and interest.
Escrow for taxes and insurance can make the statement payment higher than your PMT-based number. If you forget that and try to match your
lender’s total payment line-for-line, you’ll think your spreadsheet is wrong when it’s actually just… focused. Your schedule is modeling
the loan amortization math (principal and interest). The statement is modeling your full housing payment ecosystem.

Finally, once you’ve built this once, you stop thinking of amortization schedules as complicated and start thinking of them as reusable.
Change the loan amount, rate, term, payment frequency, and start datedone. The table instantly updates, and you’ve got a personal “loan lab”
that beats most online calculators because you can see (and control) every step. And if anyone asks why you’re smiling at Excel, you can say,
“I just watched interest lose a fight with a well-placed formula.” Which is a very normal thing to say. Probably.

Conclusion

An amortization schedule in Excel is one of those skills that pays you back (pun fully intended). Once you set up the input block,
use PMT for the payment, IPMT and PPMT for the breakdown, and update balances row-by-row, you’ve got a
flexible tool you can reuse for years. Add extra payments and totals, and your spreadsheet becomes a decision-making machinenot just a table.