Loan Amortization Excel Template

Building a Loan Amortization Excel Template That Actually Works

A loan amortization schedule in Excel is just a table that breaks down each payment into interest and principal over the life of a loan. That's it. The real difficulty isn't the concept, it's getting the formulas right so they don't silently produce garbage numbers when someone changes a term or swaps between compounding frequencies. Start with your input section. Put loan amount, annual interest rate, loan term in years, and payment frequency on separate cells at the top. Keep them all referenced by single-cell names or at least clear cell references so nobody tries to hardcode numbers into formulas later. I once had a client send me a file where someone had typed the interest rate directly into twelve different formula cells because they copied a template from a vendor and thought they were being clever. It took me twenty minutes to find the issue and another hour to fix the downstream damage. Calculate your periodic rate by dividing the annual rate by the number of payments per year. Calculate your total number of payments by multiplying the loan term in years by the payments per year. Then use the PMT function to get your periodic payment amount. The basic formula looks like this: =PMT(periodic_rate, total_periods, -loan_amount). The negative sign on the loan amount flips the result so the payment shows as a positive number. Without it, every payment in your schedule will appear as a negative, which confuses everyone who isn't familiar with Excel's cash flow conventions.

For the amortization table itself, create columns for payment number, beginning balance, payment amount, principal portion, interest portion, and ending balance. The first row starts with your original loan amount as the beginning balance. The interest portion for any given payment is simply the beginning balance multiplied by the periodic rate. The principal portion is your total payment minus the interest portion. The ending balance is the beginning balance minus the principal portion. Carry the ending balance forward as the next row's beginning balance and copy the formulas down for the full term. Here's where most people mess up: they forget that the PMT function assumes payments are made at the end of each period. If your loan requires payments at the beginning of the period, you need to add a third argument to PMT and set it to 1. =PMT(rate, nper, -pv, 1). I learned this the hard way when a commercial lender sent me a loan with a first payment due immediately and my amortization schedule was off by one full compounding period. The difference was only a couple hundred dollars on a fifty-thousand-dollar loan, but when you're building these templates for actual financial reporting, that kind of error looks terrible.

The Edge Case That Broke My Schedule

A borrower came to me with a loan that had a biweekly payment schedule but monthly compounding. Most templates assume the payment frequency matches the compounding period, which makes the math straightforward. When they don't match, you need to adjust the periodic rate using the effective rate formula. I used =(1 + annual_rate/12)^(12/26) - 1 to convert the monthly compounding rate into an equivalent biweekly rate for the PMT calculation. Without that adjustment, the payment was wrong enough to throw the entire schedule off by several months on the payoff date. This is the kind of thing nobody warns you about until you've already built three templates that don't work. People think amortization schedules are linear. They're not. The interest portion decreases exponentially while the principal portion increases correspondingly. The curve is gentle at the beginning and steep near the end, which means early payments barely touch the balance on longer-term loans. On a thirty-year mortgage at 6 percent, your first payment is roughly 85 percent interest and 15 percent principal. You won't notice that in the table unless you actually look at the split. Another thing: rounding. Excel will do calculations in full floating-point precision behind the scenes, but if you format the cells to show two decimal places, the displayed numbers might not add up exactly to your payment. The difference is usually fractions of a cent, but on a 360-payment schedule those fractions accumulate. I always include a rounding column that forces each principal and interest figure to two decimals, then calculate the ending balance from the rounded numbers instead of from the unrounded precision. It keeps the schedule readable and prevents the final payment from being off by a few dollars because of cumulative rounding drift.

Don't trust the XNPV or NPV functions for amortization. They serve different purposes entirely. NPV discounts future cash flows to present value, which is useful for investment analysis but doesn't give you a payment schedule. Amortization is a deterministic calculation based on the loan contract terms, not a discounted cash flow problem.

Limitations You Should Know About

This template approach works fine for standard fixed-rate loans with regular payments. It falls apart quickly with adjustable-rate mortgages, loans that have payment holidays, or any situation where the payment amount changes mid-term. For those cases, you'd need a custom script or a dedicated loan management tool rather than a spreadsheet template. I've seen people try to force an ARM into a standard amortization template by manually changing the payment amount in individual rows. It technically works but the formulas become unmaintainable after the first rate adjustment, and anyone who touches the file afterward is going to break it. Also worth noting: this template doesn't account for fees, escrow, or prepayment penalties. If you need those, add separate sections for them, but keep them out of the core amortization calculation or your interest and principal splits will be meaningless. A borrower who prepays extra doesn't change the contractual payment amount, so the PMT-based schedule stays the same. You can add a column for additional principal payments if you want to model accelerated payoff, but that's a separate feature from the base template. If you want a download link for a working Loan Amortization Excel Template, I can share one I built for personal use. It handles monthly and biweekly payment frequencies, includes proper rounding, and has a note reminding users about the end-of-period assumption. The file is straightforward and intentionally minimal. I'm not going to spend time making it look pretty because that's not what makes it useful. The formulas are what matter, and they're all visible so you can audit them yourself. That's more than most commercial templates give you.