Learn How to Build an Amortization Table in Excel

Understanding What an Amortization Table Is and Why It Matters

An amortization table is a document that shows the breakdown of each loan payment over time. When you take out a loan—whether it's a mortgage, car loan, or personal loan—you make regular payments. An amortization table displays exactly how much of each payment goes toward principal (the amount you borrowed) and how much goes toward interest (the cost of borrowing). This table also shows your remaining loan balance after each payment.

State WIC Programs Guide →

Amortization tables are useful for multiple reasons. They help borrowers understand their loan structure and predict when the loan will be paid off. Banks and lenders often provide amortization tables to borrowers, but knowing how to build one in Excel gives you the ability to create custom tables for different loan scenarios. For example, you might want to see what happens if you make extra payments, or you might want to compare different loan terms to see which saves you the most money in interest.

The word "amortization" comes from Latin and means "to kill" or "to pay off gradually." Each payment you make gradually kills off the debt. Early in a loan's life, most of your payment goes to interest. As you progress through the loan, more of each payment goes toward principal. This is why someone who pays off a 30-year mortgage in 15 years saves enormous amounts in interest—they're paying principal much faster.

Understanding amortization helps you make informed decisions about loans. You might realize that a longer loan term means paying significantly more in total interest, even though monthly payments are lower. Or you might discover that making one extra payment per year can shorten your loan by several years. These insights come directly from reading and building amortization tables.

Practical Takeaway: Before building your table, gather your loan information: the total amount borrowed (principal), the annual interest rate, and the loan term in months or years. These three pieces of information are all you need to create a complete amortization table.

Setting Up Your Excel Spreadsheet and Creating Column Headers

Begin by opening a blank Excel spreadsheet. You'll organize your table with column headers that will guide all your calculations. The standard amortization table includes five main columns: Payment Number, Payment Amount, Principal, Interest, and Remaining Balance. Some people add additional columns for cumulative interest paid or cumulative principal paid, but these five are essential.

Get Your Free Anki Flashcard Learning Guide →

In cell A1, type "Payment Number." This column will simply count from 1 to the total number of payments you'll make. In cell B1, type "Payment Amount." This will be the same for every payment (assuming a fixed-rate loan). In cell C1, type "Principal." This shows how much of each payment reduces your actual debt. In cell D1, type "Interest." This shows how much you're paying in borrowing costs. In cell E1, type "Remaining Balance." This shows what you still owe after each payment.

Format your headers so they stand out. You might bold them, add a background color, or increase the font size. This makes your table easier to read. Below your headers, you'll need a row for your initial loan information. Many people use Row 2 to list: the loan amount (principal), the annual interest rate (as a decimal), and the loan term in months. Some people put this information to the right of the table in cells like G2, G3, and G4, labeled as "Loan Amount," "Annual Rate," and "Loan Term (months)."

The placement of your input cells matters because you'll reference them in formulas. If your loan amount is in cell G2, your interest rate in G3 (as a decimal, like 0.05 for 5%), and your loan term in G4, you'll use these cell references throughout your calculations. This approach also lets you change the loan details and watch the entire table update automatically.

Set your column widths so all information is visible. Column A (Payment Number) can be narrow. Columns B through E should be wide enough to display currency values comfortably, typically between 1 and 1.5 inches. You might also format columns B, C, D, and E as currency with two decimal places, so they display values like $500.00 rather than 500.

Practical Takeaway: Create a clean, organized layout before entering any calculations. This prevents errors and makes your table much easier to understand later. A well-organized spreadsheet can be reused for different loans by simply changing your input values.

Calculating Your Fixed Monthly Payment Using the PMT Formula

The foundation of your amortization table is the fixed monthly payment amount. For a standard loan with fixed interest, you pay the same amount each month. Excel has a built-in formula called PMT that calculates this payment automatically. The formula is: =PMT(rate, nper, pv)

Learn About Washington State DMV License Renewals →

The PMT formula requires three inputs: "rate" (the monthly interest rate), "nper" (the number of periods, or months), and "pv" (the present value, or loan amount). Here's the critical part: your annual interest rate must be converted to a monthly rate. If your annual rate is 5%, divide it by 12 to get the monthly rate (0.05/12 = 0.00417, or about 0.417% per month).

Let's work through an example. Suppose you have a $300,000 loan at 5% annual interest for 30 years (360 months). Your formula would be: =PMT(0.05/12, 360, -300000). Note that the loan amount is negative. Excel's PMT function uses this convention: you're borrowing money (negative from the lender's perspective, negative in the formula), and you're paying it back (positive payments). The result of this formula is approximately $1,610.46 per month.

If you've stored your values in cells (loan amount in G2, annual rate in G3, and loan term in months in G4), your formula becomes: =PMT(G3/12, G4, -G2). This makes your spreadsheet dynamic. If you change the loan amount, rate, or term, the payment amount recalculates automatically.

Place this formula in a cell you've designated for the payment amount. Many people put it in cell B2 or in a cell near their input values, like H2, with a label next to it. Once you have your fixed payment amount, you'll use it throughout your amortization table. Every row will reference this same payment amount because it doesn't change month to month.

Practical Takeaway: The PMT formula handles the complex mathematics of determining your fixed payment automatically. Understanding that this payment includes both interest and principal, but the ratio changes each month, is key to understanding why the amortization table shows different breakdowns for each payment.

Building the Interest and Principal Columns with Step-by-Step Formulas

Now you'll create the formulas that populate your table row by row. Start with the first payment (Row 3, assuming your headers are in Row 1 and your initial data in Row 2). For the first payment, the interest portion is straightforward: multiply the remaining balance by the monthly interest rate.

Free Guide to Snydersville DMV Office Information →

In cell D3 (Interest for Payment 1), enter: =G2*(G3/12). This takes your original loan amount (G2) and multiplies it by the monthly interest rate (G3/12). Using the $300,000 at 5% example, this equals $300,000 × 0.00417 = $1,250. So of your first $1,610.46 payment, $1,250 goes to interest.

The principal portion is what's left: the total payment minus the interest. In cell C3 (Principal for Payment 1), enter: =B3-D3 (or =$1,610.46-$1,250 = $360.46). Or, if your payment is in cell B2, use: =B$2-D3. The dollar sign before the B locks that reference so when you copy the formula down, it always refers to B2, but D3 changes to D4, D5, and so on as you copy.

The remaining balance after the first payment is the original loan amount minus the principal paid: =G2-C3, which equals $300,000 - $360.46 = $299,639.54.