Learn How to Build an Amortization Table in Excel

What an Amortization Table Actually Is and Why It Matters

An amortization table is a month-by-month breakdown of a loan showing how much you owe, how much you're paying toward principal versus interest, and what remains after each payment. Think of it as a detailed roadmap for any loan with a fixed payment amount—like a mortgage, car loan, or student loan.

Learn About Washington State DMV License Renewals →

The word "amortization" comes from Latin meaning "to kill" or "to extinguish," which is exactly what happens: you're systematically extinguishing the debt over time. Understanding how this process works prevents surprises and gives you visibility into your actual financial situation.

Here's why building one in Excel matters. A typical 30-year mortgage involves 360 payments. Your lender sends you an amortization schedule, but it's often a PDF that's hard to modify or analyze. By building your own table in Excel, you can instantly see what happens if you make extra payments, what the total interest cost really is, or how refinancing at a different rate would affect your timeline. You control the numbers and the story they tell.

Consider a real scenario: A person with a $250,000 mortgage at 6.5% interest over 30 years pays $1,580.17 monthly. The amortization table reveals that in year one alone, $15,236 of their $18,962 in total payments goes straight to interest—not principal. Year two looks slightly better, but the ratio stays heavily weighted toward interest for the first decade. That's the kind of insight an amortization table reveals that a monthly statement never will.

Practical takeaway: Building an amortization table transforms loan documents from something you sign and forget into a tool you can interrogate. It answers "what if" questions about your debt in seconds rather than calling your lender.

The Core Components You'll Build Into Your Table

Every functional amortization table in Excel needs seven core columns: Payment Number, Payment Date, Beginning Balance, Payment Amount, Principal, Interest, and Ending Balance. Let's break what each column tracks and why it's essential.

Free Guide to Snydersville DMV Office Information →

Payment Number is straightforward—this is simply the count of the payment (1, 2, 3...360 for a 30-year mortgage). This helps you locate specific payments quickly and makes the table easier to navigate.

Payment Date shows when each payment is due. For a monthly loan, this will increment by one month each row. This column becomes especially useful if you're analyzing when you'll own something outright or if you want to map payments against your actual paycheck calendar.

Beginning Balance is the loan amount you still owe at the start of that payment period. For payment one, this is your original loan amount. For payment two, it's what remained after payment one, and so on. This number decreases with every payment.

Payment Amount is the fixed dollar amount you pay each month. For most standard loans, this stays exactly the same from month one through the final payment. Some loans have variable rates, but we'll focus on fixed-rate loans for this guide since they're more straightforward and common.

Interest is the portion of your payment that goes to the lender as interest. This is calculated by multiplying your beginning balance by the monthly interest rate (annual rate divided by 12). Early payments have higher interest portions because the balance is larger. Late payments have lower interest portions because the balance has shrunk.

Principal is the portion of your payment that reduces what you actually owe. It's calculated as Payment Amount minus Interest. As you progress through the loan, this number grows while interest shrinks.

Ending Balance is your beginning balance minus the principal payment. This becomes your next row's beginning balance, creating the chain that runs through the entire loan.

Practical takeaway: These seven columns capture the complete financial story of loan repayment. Understanding what each column represents means you can troubleshoot errors and modify the table for different scenarios without confusion.

Setting Up the Headers and Loan Parameters in Excel

Before you build formulas, set up a clean workspace that separates your loan inputs from your amortization table. This makes the spreadsheet easier to maintain and modify later.

Learn About Free DMV Practice Permit Tests →

Start by creating a "Loan Details" section at the top of your spreadsheet. In cells like A1 through B6, create labels and corresponding values for: Loan Amount (original principal), Annual Interest Rate, Loan Term in Years, and Payment Frequency (monthly, quarterly, etc.). For example:

• A1: "Loan Amount" | B1: 250000
• A2: "Annual Interest Rate" | B2: 0.065 (entered as 6.5% or 0.065)
• A3: "Loan Term (Years)" | B3: 30
• A4: "Payment Frequency" | B4: Monthly

In A5, create a label "Monthly Payment" and use Excel's PMT function in B5. The formula looks like this: =PMT(B2/12, B3*12, -B1). This divides your annual rate by 12 for monthly calculation, multiplies years by 12 to get total payments, and uses a negative loan amount by convention. The result will be your fixed monthly payment amount—in this example, around $1,580.17.

Below this, starting around row 8 or 9, create your column headers in a single row. Use cells A8 through G8 for: Payment #, Payment Date, Beginning Balance, Payment Amount, Interest, Principal, and Ending Balance. Format these as bold and consider adding a light background color so they stand out from data rows.

In the first data row (row 9), begin entering formulas. Payment # is simply 1. Payment Date can be a starting date you choose (like the first of the next month). Beginning Balance for payment one equals your original loan amount from B1. Payment Amount references your calculated monthly payment from B5 and stays the same throughout. These first entries establish the pattern that will repeat down the column.

Format your currency columns (Beginning Balance, Payment Amount, Interest, Principal, Ending Balance) as currency with two decimal places. This prevents rounding confusion later. Interest and Principal columns especially benefit from this precision since errors compound across 360 rows.

Practical takeaway: Separating loan parameters from your amortization table means you can change the loan amount or interest rate in one cell and watch the entire table recalculate instantly, revealing how different scenarios affect your repayment.

Building the Formula Logic for Each Payment Period

Once your headers and first row are set up, the remaining rows follow a repeating pattern of formulas. Row 10 (payment 2) will look almost identical to row 9, except it references the ending balance from row 9 as its beginning balance. This chain continues through your final payment.

Free Guide to Trump Payment Programs and Options →

Let's say your first data row is row 9 and your loan details are in B1 (amount), B2 (rate), B5 (payment amount). Here's how to structure row 10:

• A10 (Payment #): =A9+1 (This adds 1 to the previous payment number)
• B10 (Payment Date): =DATE(YEAR(B9),MONTH(B9)+1,DAY(B9)) or use =B9+30 for approximate 30-day months
• C10 (Beginning Balance): =G9 (This is the ending balance from the previous row)
• D10 (Payment Amount): =$B$5 (The $ symbols lock this to your calculated payment, so it never changes)
• E10 (Interest): =C10*($B$2/12) (Beginning balance times monthly rate)
• F10 (Principal): =D10-E10 (Payment amount minus interest)