Loan Amortization Table Excel: How to Build One From Scratch

What Is a Loan Amortization Table?

If you've ever wondered where your mortgage payment actually goes each month — or why your loan balance seems to barely move in the first few years despite making thousands in payments — an amortization table answers these questions with uncomfortable clarity. It's probably the most revealing financial document you'll ever create, and building one yourself in Excel takes about 15 minutes. The exercise transforms an abstract monthly payment into a concrete understanding of how your money splits between interest (which enriches your lender) and principal (which builds your equity).

A loan amortization table breaks down every payment on a loan into its two components — how much goes toward interest and how much reduces the principal balance — for every single payment over the life of the loan. If you have a 30-year mortgage with 360 monthly payments, the amortization table shows all 360 rows: the payment amount, the interest portion, the principal portion, and the remaining balance after each payment. This isn't just an accounting exercise; it reveals the true cost structure of your loan in a way that a simple monthly payment number never does.

Most borrowers are surprised when they first see an amortization table for their mortgage or car loan. In the early years, the majority of each payment goes toward interest rather than principal. On a typical 30-year mortgage, your first payment might be 75% interest and only 25% principal — meaning most of your money is going to the lender, not toward building equity. The ratio gradually shifts over time: by the end of the loan, almost all of each payment is principal. Understanding this dynamic helps you make informed decisions about extra payments, refinancing, and loan selection.

Building your own amortization table in Excel gives you complete control over the analysis. You can model different scenarios: what happens if you make extra payments, how does refinancing at a lower rate affect total interest paid, what's the difference between a 15-year and 30-year term. Online calculators show you results; an Excel amortization table shows you how those results work — and lets you modify any variable to see how changes affect the outcome. This hands-on understanding of loan mechanics is genuinely valuable for anyone with a mortgage, car loan, student loan, or business loan.

This guide walks through building a complete loan amortization table in Excel from scratch, using the PMT, IPMT, and PPMT functions. You don't need advanced Excel skills — basic formula knowledge is sufficient. By the end, you'll have a working amortization schedule that you can customise for any loan.

Understanding the PMT, IPMT, and PPMT Functions

Excel provides three functions specifically designed for loan amortization calculations. Understanding how each works — and when to use each — is the key to building an accurate amortization table.

PMT (Payment) calculates the fixed periodic payment for a loan based on constant payments and a constant interest rate. The syntax is =PMT(rate, nper, pv), where rate is the periodic interest rate (annual rate divided by payments per year), nper is the total number of payments, and pv is the present value (the loan amount). For a $200,000 loan at 5% annual interest for 30 years: =PMT(0.05/12, 360, -200000) returns $1,073.64 per month. The negative sign on the loan amount is an Excel convention — loans received are negative (incoming cash), payments made are positive (outgoing cash).

IPMT (Interest Payment) calculates the interest portion of a specific payment. The syntax is =IPMT(rate, per, nper, pv), where per is the specific payment number you're calculating. =IPMT(0.05/12, 1, 360, -200000) returns $833.33 — the interest portion of the first payment. =IPMT(0.05/12, 360, 360, -200000) returns $4.45 — the interest in the last payment. This function shows how dramatically the interest portion decreases over the life of the loan.

PPMT (Principal Payment) calculates the principal portion of a specific payment. =PPMT(0.05/12, 1, 360, -200000) returns $240.31 — the principal in the first payment. =PPMT(0.05/12, 360, 360, -200000) returns $1,069.19 — the principal in the last payment. At the beginning, principal is small; at the end, it's nearly the entire payment. IPMT + PPMT always equals PMT for any given payment number.

You can build an amortization table using either approach: the IPMT/PPMT functions (entering each directly for each payment number) or the formula-based approach (calculating interest as balance × rate and principal as payment − interest, then updating the balance). Both produce identical results. The formula-based approach is more intuitive and easier to modify for scenarios like extra payments; the IPMT/PPMT approach is more compact. This guide uses the formula-based approach because it makes the mechanics visible and modifiable.

Practical Uses for Your Amortization Table

Once you've built a working amortization table, it becomes a powerful financial planning tool that you'll use for multiple decisions throughout your financial life.

Mortgage analysis is the most common use. Before buying a home, run amortization tables for different loan amounts, rates, and terms to understand the true cost of each option. A 15-year mortgage has higher monthly payments than a 30-year but saves hundreds of thousands in interest. Your amortization table makes this comparison concrete — not just a theoretical savings number but a month-by-month demonstration of how each option plays out.

Car loan comparison helps you negotiate better terms. Auto dealers present monthly payments prominently but downplay total cost and interest rates. Building an amortization table for each financing offer reveals which deal actually costs less over the life of the loan. A lower monthly payment spread over more months often costs more total than a higher payment on a shorter term — your amortization table proves it with specific numbers.

Student loan repayment planning benefits from amortization analysis, particularly when comparing income-driven repayment plans against standard repayment. Standard repayment on a 10-year plan has higher monthly payments but dramatically less total interest than income-driven plans that stretch to 20–25 years. An amortization table for each scenario shows the lifetime cost difference — which is often tens of thousands of dollars.

Extra payment strategy development uses the amortization table to determine the most cost-effective way to accelerate debt payoff. Should you make one extra payment per year or add $100 to each monthly payment? Your amortization table answers this precisely for your specific loan — the answer depends on your interest rate, remaining balance, and remaining term.

Modelling both strategies side by side shows which saves more interest in your situation. The amortization table transforms the abstract question 'should I pay extra on my loan?' into a concrete answer with specific dollar amounts — making it one of the most practically useful financial tools you can build in Excel.

Tax planning also benefits from amortization analysis. Mortgage interest is tax-deductible for many homeowners who itemise deductions. Your amortization table's interest column shows exactly how much interest you'll pay in any given year, helping you project your itemised deduction amount and plan your tax strategy accordingly. This is particularly relevant in early loan years when the interest portion is highest and the tax benefit is most significant.

Business loan analysis works the same way — commercial borrowers use amortization tables to project cash flow requirements, compare financing options, and evaluate whether the cost of borrowing is justified by the expected return on investment. If a business loan costs $50,000 in total interest but the investment it funds generates $200,000 in additional revenue, the amortization table confirms the maths that justifies the borrowing decision.

One often-overlooked use: comparing the total cost of renting versus buying over a specific time horizon.

If your mortgage amortization shows that you'd pay $180,000 in interest over the first 7 years of a 30-year mortgage, and renting during the same period would cost $168,000 in total rent, the financial comparison becomes clearer than the conventional wisdom that 'buying is always better than renting.' Your amortization table provides the mortgage cost side of the equation with exact numbers rather than rough estimates or convenient assumptions that might skew the comparison unfairly in either direction depending on who's making the particular financial argument being made.

Common Mistakes When Building Amortization Tables

Several errors commonly trip up people building their first amortization table in Excel. Catching these before they compound through 360 rows saves frustration and ensures accurate analysis.

Using the annual interest rate instead of the monthly rate is the most common mistake. If your annual rate is 6%, the monthly rate is 0.5% (6% ÷ 12), not 6%. Using 6% as the monthly rate produces payments that are wildly too high and interest that doesn't match reality. Always divide the annual rate by the number of payments per year before using it in PMT, IPMT, or any interest calculation. The formula should reference the monthly rate cell (=annual_rate/12), not the annual rate directly.

Forgetting the negative sign on the present value in the PMT function produces a negative payment amount. Excel treats cash received (the loan) as negative and cash paid (payments) as positive. If your PMT result is negative, add a negative sign before the present value argument: =PMT(rate, nper, -pv). Some people prefer to multiply the result by -1 instead — both approaches work, but be consistent throughout the table.

Not locking cell references with absolute references ($) causes formulas to shift incorrectly when copied down. The monthly rate, total payments, and loan amount cells should all use $ signs (e.g. $B$6) because they're fixed values that every row references. The payment number and previous balance references should be relative because they change with each row. Getting this mix right on the first row means the formulas copy correctly to all 360 rows; getting it wrong means manually fixing every row.

Rounding errors can cause the final balance to be slightly above or below zero (e.g. $0.03 or -$0.02). This is normal — it results from rounding monthly payments to the nearest cent while the mathematical calculation produces values with many decimal places. A final balance within $1 of zero is acceptable. If the balance is off by more than a dollar, there's a formula error somewhere in the table that needs investigation.

Template: Ready-to-Use Layout

Here's the exact layout for building your amortization table. Set up these cells and formulas, and you'll have a working schedule in about 15 minutes.

Row 1: Label 'Loan Amount' in A1, enter the dollar amount in B1 (e.g. 250000). Row 2: 'Annual Interest Rate' in A2, enter as decimal in B2 (e.g. 0.065 for 6.5%). Row 3: 'Loan Term (Years)' in A3, enter the term in B3 (e.g. 30). Row 4: 'Payments Per Year' in A4, enter 12 in B4. Row 5: 'Total Payments' in A5, formula =B3*B4 in B5. Row 6: 'Monthly Rate' in A6, formula =B2/B4 in B6. Row 8: 'Monthly Payment' in A8, formula =PMT(B6,B5,-B1) in B8.

Row 10 headers: A10 = 'Payment #', B10 = 'Payment', C10 = 'Interest', D10 = 'Principal', E10 = 'Extra', F10 = 'Balance'. Row 11 (first payment): A11 = 1, B11 = $B$8, C11 = $B$1*$B$6, D11 = B11-C11, E11 = 0, F11 = $B$1-D11-E11. Row 12 (second payment): A12 = 2, B12 = $B$8, C12 = F11*$B$6, D12 = B12-C12, E12 = 0, F12 = F11-D12-E12. Copy row 12's formulas down through row 370 (for 360 payments). The balance in the final row should be approximately $0.

Below the table, add summary cells: 'Total Paid' = SUM(B11:B370)+SUM(E11:E370), 'Total Interest' = SUM(C11:C370), 'Total Principal' = SUM(D11:D370)+SUM(E11:E370). The total principal should equal your original loan amount. If it doesn't, check for formula errors in the principal or extra payment columns.

Amortization for Different Loan Types

While the basic amortization structure is the same for all fixed-rate loans, different loan types have characteristics worth understanding when building your Excel table.

Mortgage amortization follows the standard structure described in this guide — fixed monthly payments over 15 or 30 years (or other terms). Mortgages are the most common use case for amortization tables because the long terms and large amounts make the interest-vs-principal dynamics most dramatic and most financially significant. If you only build one amortization table in your life, make it for your mortgage — the insights into your largest financial commitment are worth the 15 minutes of setup.

Auto loan amortization works identically but with shorter terms (typically 36–72 months) and smaller amounts. Car loans with shorter terms (36–48 months) have higher payments but significantly lower total interest than longer terms (60–72 months). Your amortization table shows exactly how much extra total cost the lower monthly payment of a longer term adds — which helps you negotiate financing terms at the dealership from a position of knowledge rather than just reacting to monthly payment quotes.

Student loan amortization may involve multiple loans with different rates and terms. Building a separate amortization table for each loan and then comparing the interest costs helps you decide which loan to pay extra toward first (typically the highest-rate loan — the 'avalanche method' — produces the most savings, though some people prefer paying off the smallest balance first for psychological momentum — the 'snowball method'). Your amortization tables quantify the difference between these strategies for your specific loans.