Excel Loan Payment Template: Calculate Any Loan in Minutes
Whether you’re sizing up a car loan, a mortgage, or a small business note, Excel can tell you the exact monthly payment in seconds. This guide walks you through the loan payment math, shows you how to build a simple template from scratch, and then points you to a done-for-you suite if you’d rather skip the formula-wrangling entirely. Want a number right now? Try our free loan calculator — then come back to build (or buy) the full template.
The math behind every loan payment
Every fixed-rate loan payment comes down to three inputs: the amount you borrow (principal), the interest rate, and the number of payments. Excel bundles the underlying time-value-of-money formula into a single function called PMT, so you never have to write out the algebra yourself.
The syntax is:
=PMT(rate, nper, pv)- rate — the interest rate per period. For a monthly payment on a loan quoted as an annual rate, divide the annual rate by 12.
- nper — the total number of payments (years × 12 for a monthly loan).
- pv — the present value, i.e. the loan amount you’re borrowing.
So a $25,000 car loan at 6% annual interest over 5 years is:
=PMT(6%/12, 5*12, 25000)That returns -$483.32. The result is negative because Excel treats the payment as cash leaving your pocket. If a negative number bothers you, wrap it in -PMT(...) or put a minus sign in front of the loan amount. If you want a deeper walkthrough of the function itself, our guide to the PMT function and the PMT reference page cover every argument in detail.
Build a reusable template in five rows
Hard-coding numbers inside a formula is fine for a one-off, but a real template uses cell references so you can change the inputs and watch the payment update. Here’s a layout anyone can build in under two minutes:
| Cell | Label | Example value |
|---|---|---|
| B1 | Loan amount | 25000 |
| B2 | Annual rate | 6% |
| B3 | Term (years) | 5 |
| B4 | Monthly payment | =-PMT(B2/12, B3*12, B1) |
Now change B1, B2, or B3 and B4 recalculates instantly. To make this bulletproof, lock the input cells with absolute cell references before you copy the formula anywhere, and give your inputs friendly handles using named ranges so the formula reads =-PMT(Rate/12, Years*12, Loan). That single change turns a cryptic formula into something a colleague can actually read.
This same pattern powers our standalone tutorials on calculating a monthly payment and calculating a mortgage payment — the math is identical whether it’s a $2,000 phone or a $500,000 house.
Splitting payment into principal and interest
A single payment number is useful, but most people also want to know how much of each payment is interest versus principal. That’s where an amortization schedule comes in — a row-by-row table that tracks the shrinking balance over the life of the loan. The companion functions are IPMT (interest portion of a given payment) and PPMT (principal portion).
You can build one by hand following our create an amortization schedule tutorial. It works, but it’s fiddly: one mis-typed sign or a reference that doesn’t lock properly and your final balance lands at -$3.71 instead of zero. Rounding compounds the problem across 360 rows, which is why we have a whole post on fixing rounding errors.
When a free template isn’t enough
A DIY template is perfect for a quick gut-check. But the moment you want to compare loans, model overpayments, or hand a clean file to a client or spouse, the cracks show. You end up duplicating tabs, copy-pasting formulas that break, and squinting at a wall of numbers with no chart.
That’s exactly the gap the Loan & Mortgage Calculator Suite fills.
Get the Loan & Mortgage Calculator Suite ($14)
For the price of a lunch, you get a polished workbook that does what a blank spreadsheet can’t:
- Full amortization schedule, auto-built. Type in your loan amount, rate, and term and the entire payment-by-payment table populates — principal, interest, and running balance — landing precisely at zero on the final row. No PPMT typos, no rounding drift.
- Extra-payment modeling. Add an extra $100 a month, or a one-time $5,000 lump sum, and instantly see how many months you shave off and how many thousands in interest you save. This is the calculation people most often get wrong by hand.
- 3-loan side-by-side comparison. Compare a 15-year vs. 30-year mortgage, or three different lender offers, on one screen — payment, total interest, and payoff date lined up so the cheapest option is obvious.
- A real dashboard with charts. Balance-over-time and interest-vs-principal charts that update live, so you can see the loan paying down instead of scrolling a 360-row table.
- Zero formula errors, instant use. Every formula is locked and tested. You change the yellow input cells; nothing else can break. No setup, no debugging — open it and go.
The free approach teaches you the mechanics, which is genuinely worth knowing. The suite saves you the hour of building, the second hour of debugging, and gives you visuals you’d never bother to chart yourself.
A quick worked example
Say you’re choosing between a 30-year mortgage at 6.5% and a 15-year at 5.8% on a $400,000 loan.
- 30-year:
=-PMT(6.5%/12, 30*12, 400000)→ $2,528/month - 15-year:
=-PMT(5.8%/12, 15*12, 400000)→ $3,332/month
The 15-year costs $800 more each month — but over the life of the loan it saves you well over $300,000 in interest. The suite’s comparison tab surfaces that total-interest number side by side automatically, and the extra-payment tab lets you test a middle path: keeping the 30-year loan but paying it like a 20-year. If you’re modeling a variable rate, pair this with our adjustable-rate mortgage walkthrough, and for any growth or savings scenario, compound interest uses the same time-value-of-money engine.
Whether you build it yourself or buy it ready-made, Excel takes the guesswork out of borrowing. If you value your time, the Loan & Mortgage Calculator Suite pays for itself the first time it stops you from picking the wrong loan.
FAQ
Why does Excel’s PMT function return a negative number?
Excel follows a cash-flow convention: money you pay out is negative, money you receive is positive. Since a loan payment leaves your account, PMT returns it as a negative. Put a minus sign in front — =-PMT(...) — to display it as a positive figure.
How do I get a monthly payment from an annual interest rate?
Divide the annual rate by 12 for the rate argument and multiply the years by 12 for the nper argument. For a 5-year loan at 6% annual, that’s =PMT(6%/12, 5*12, loan). Mixing an annual rate with a monthly term count is the most common PMT mistake.
Can PMT include taxes and insurance for a mortgage?
No. PMT only calculates principal and interest. To get a full PITI payment, calculate the P&I with PMT, then add your monthly property tax and insurance in a separate cell and sum them. The Loan & Mortgage Calculator Suite has dedicated input cells for exactly this.
What’s the difference between PMT, IPMT, and PPMT?
PMT gives you the total fixed payment. IPMT returns just the interest portion of a specific payment, and PPMT returns just the principal portion. For any given period, IPMT plus PPMT equals PMT — which is how an amortization schedule is built.
