Free Excel Loan Calculator Template
Calculate the true cost of any loan — mortgage, auto, student, personal, business. See your monthly payment, the full 360-month amortization schedule, and how much extra payments would save you. Works with any currency, any interest rate, any term. Free forever, no signup, no watermark.
Loan Calculator 2026 — full workbook
Five sheets: calculator with instant results, full amortization schedule, extra-payment scenarios, refinancing comparison, setup guide. Open, type your numbers, get answers.
↓ Download loan-calculator-2026.xlsxPreview: what you'll see when you open it
The Calculator sheet is deliberately minimal — four inputs, one summary. Type your loan amount, interest rate, term, and start date; the monthly payment and total-interest figures appear instantly.
The Amortization Schedule sheet breaks down every single payment for the life of the loan — what goes to principal, what goes to interest, and your remaining balance after that payment. The color coding shows how heavily front-loaded interest is on a typical mortgage.
| # | Date | Payment | Principal | Interest | Balance |
|---|---|---|---|---|---|
| 1 | Feb 2026 | $1,621.50 | $215.25 | $1,406.25 | $249,784.75 |
| 2 | Mar 2026 | $1,621.50 | $216.46 | $1,405.04 | $249,568.29 |
| 3 | Apr 2026 | $1,621.50 | $217.68 | $1,403.82 | $249,350.61 |
| ⋯ 354 more monthly rows ⋯ | |||||
| 358 | Nov 2055 | $1,621.50 | $1,594.05 | $27.45 | $3,283.83 |
| 359 | Dec 2055 | $1,621.50 | $1,603.02 | $18.47 | $1,680.81 |
| 360 | Jan 2056 | $1,690.27 | $1,680.81 | $9.46 | $0.00 |
The Extra Payment Scenarios sheet lets you see what even a small additional monthly payment does to your loan. Three side-by-side scenarios show the dramatic impact of paying more:
Numbers based on the $250K, 6.75%, 30-year example above. Your actual results depend on your loan.
What's inside the workbook
Five sheets, all pre-configured and cross-linked. Type your numbers once — every sheet updates:
- Setup Guide — Two-minute walkthrough on the first sheet.
- Calculator — Enter loan amount, rate, term, start date, and optional extra payment. See monthly payment and totals instantly.
- Amortization Schedule — Payment-by-payment breakdown for up to 480 months (40 years). Every row shows principal, interest, and balance. Works for any loan term.
- Extra Payment Scenarios — Three side-by-side scenarios (baseline vs two extra-payment amounts) showing exactly how much interest and time you'd save.
- Refinancing Comparison — Compare your current loan against a refinance offer. Break-even date and lifetime savings computed automatically.
Features that make this different from other loan calculators
Instant results
Change any input — loan amount, rate, term — and every number updates immediately. No "calculate" button. No submit. Just arithmetic Excel handles in microseconds.
Full amortization schedule
All 360 monthly payments (or 480 for 40-year loans) laid out with principal, interest, and running balance. See exactly when you cross the halfway point in equity built.
Extra-payment impact analysis
Try +$50, +$100, +$500 monthly extra. See the payoff date shift, and count the interest saved. Motivating math for anyone with an extra $50/month.
Refinancing calculator
Compare your existing loan to a refinance offer. Break-even month, lifetime savings, and payment difference all computed. Answers the "is refinancing worth it" question.
Works with any loan, any currency
Mortgage in USD, car loan in EUR, student loan in GBP, personal loan in INR — swap the currency symbol and the template works identically. The math is universal.
Uses standard financial functions
Built on Excel's PMT, PPMT, IPMT, CUMIPMT, CUMPRINC — the same functions banks use. Numbers match what your lender will quote you, to the penny.
Who this loan calculator is for
Homebuyers & refinancers
See the true cost of any mortgage. Compare 15-year vs 30-year, or evaluate whether refinancing makes sense.
Car buyers & auto loans
Test different loan terms and rates before you walk into the dealership. Know your monthly payment ceiling.
Student loan borrowers
Model the impact of extra payments on federal or private student debt. See what a $50/month extra payment does.
How loan interest actually works (the math)
Every loan uses the same formula, whether it's a $500 payday loan or a $500 million commercial mortgage. Payment is calculated so the loan pays down to zero at the exact end of the term, with interest computed on the remaining balance each month:
Where P = loan principal, r = monthly interest rate (annual rate ÷ 12), n = total number of monthly payments (years × 12).
The template uses Excel's PMT function, which implements this formula. For our $250,000 example at 6.75% over 30 years:
The frontloaded-interest problem
Look at the amortization schedule mockup above. Payment #1 splits $215 to principal and $1,406 to interest — that's 87% interest. Payment #360 splits $1,681 to principal and $9 to interest — 99.4% principal.
This isn't the bank being sneaky — it's just how compound interest math works. Since interest is calculated on the outstanding balance, and the balance is highest at the beginning, that's when the interest bite is largest. The bank profits most in the first ~10 years of your loan. This is exactly why extra payments early in the loan life save so much interest — every extra dollar of principal you pay off early stops compounding for you.
APR vs Interest Rate — the difference
These get used interchangeably but they mean different things. The template accepts either — just be consistent about which one you enter.
Interest Rate
The pure cost of borrowing money, expressed as an annual percentage. Excludes fees, insurance, and other charges. This is what determines the monthly principal-and-interest payment on the amortization schedule.
Example: 6.50% on a mortgage note.
APR (Annual Percentage Rate)
The interest rate plus fees, points, and other loan costs, expressed as an annualized rate. Higher than the interest rate by design. Legally required disclosure in the US and most countries — comparison-shop with this number, not the interest rate.
Example: 6.75% APR on the same mortgage note = 6.50% interest + fees baked in.
Which should you enter in the template? Use the interest rate for accurate monthly payment calculations. Use the APR for comparison shopping between lenders. The template computes both if you enter the interest rate and the total upfront fees.
Fixed vs Variable rate
Fixed Rate
Interest rate locked for the entire loan term. Your monthly payment never changes. Predictable, safer, but usually starts higher than variable.
Most 30-year US mortgages, standard student loans, auto loans.
Variable / Adjustable Rate
Interest rate resets periodically based on a market index. Starts lower than fixed, but your monthly payment can rise (or fall) at each reset. Higher risk, higher potential reward.
ARMs (5/1, 7/1, 10/1 mortgages), most HELOCs, many credit lines.
How to use it — 3-step setup
Enter your loan on the Calculator sheet
Only four required inputs: loan amount, annual interest rate, term in years, and start date.
- Loan amount = the principal you're borrowing (not the home price — subtract down payment).
- Interest rate = annual rate as a percentage (e.g., 6.75, not 0.0675).
- Term = years (e.g., 30 for a 30-year mortgage).
- Start date = the month you'll make your first payment.
Check the summary and browse the amortization
The Calculator sheet shows monthly payment, total interest, and payoff date. Switch to the Amortization Schedule to see every payment.
- Notice how the principal portion grows and interest shrinks over time.
- Find your equity crossover — the payment where principal exceeds interest.
- Filter or sort the schedule if you're comfortable with Excel Tables.
Model extra payments and refinancing
Try scenarios that fit your situation:
- Extra Payment Scenarios sheet — enter $50, $100, or $500 as monthly extra. See time and interest saved.
- Refinancing Comparison sheet — enter your current loan details and the refinance offer. See if it's worth it.
- Save your workbook file for future reference — great for annual financial reviews.
Customizing the template
Change the currency
Select all money cells (Loan Amount, Monthly Payment, and every column in the amortization table showing dollars) → right-click → Format Cells → Currency → pick your symbol. USD, EUR, GBP, INR, PKR, AUD, CAD, JPY — the template works identically with any currency, since loan math is currency-agnostic.
Different payment frequency (bi-weekly, weekly)
The template assumes monthly payments (the standard). For bi-weekly, replace 12 with 26 in the PMT formula and multiply term × 26 instead of × 12. Bi-weekly effectively squeezes an extra payment in per year, saving significant interest. Same principle for weekly (52 payments/year).
Add property taxes and insurance (mortgage PITI)
The Calculator sheet has an optional PITI section — enter your annual property tax and homeowners insurance, and the template shows your full monthly housing payment. Not included in amortization since only P&I actually pays down the loan; taxes and insurance go to escrow.
Longer loans (40+ years) or shorter ones (5, 7, 10 years)
The amortization sheet has 480 rows built in (40 years × 12 months). For loans shorter than 40 years, ignore the extra rows — they show blank when the balance hits zero. For loans longer than 40 years (rare), extend the amortization by copying the last populated row and pasting downward.
Compare two loans side-by-side
Duplicate the Calculator sheet (right-click tab → Move or Copy → Create a copy). Enter each loan on its own sheet. Or use the built-in Refinancing Comparison sheet for a proper side-by-side with break-even math.
Excel functions used inside this template
These are the financial and utility functions that power the workbook — click any to see how they work:
Frequently asked questions
Is this loan calculator really free?
Yes — free for personal and commercial use, no signup, no email, no watermark. Financial professionals can use it with clients; educators can use it in classrooms; individuals can use it for their own planning. A link back to this page is appreciated but not required.
Will the calculations match my bank's numbers exactly?
To the penny, yes — the template uses the same PMT formula that banks use. The only reason your monthly bill might differ slightly: (1) escrow for taxes and insurance, added on top of P&I; (2) private mortgage insurance (PMI); (3) prepaid interest for a partial first month if you closed mid-month. The template's P&I number is accurate.
What's the difference between principal and interest?
Principal is the amount you actually borrowed — the money that needs to be repaid. Interest is the fee the lender charges for letting you use their money over time. Every monthly payment is split between the two. Early in the loan, most of your payment is interest; late in the loan, most is principal. The amortization schedule shows this split for every single payment.
How can I use this for an auto loan (typically 3-7 years)?
Just enter the shorter term. Loan amount = purchase price minus down payment minus trade-in value. Interest rate = your dealer's or bank's quoted rate. Term = 36, 48, 60, 72, or 84 months (divide by 12 to enter years). The template works identically regardless of loan size or term — the math is the same for a $20K car loan as for a $2M mortgage.
Does it work for student loans?
Yes for standard fixed-rate student loans. For loans with grace periods, deferment, or income-driven repayment (IDR), you'll need to model the pre-repayment interest accrual separately, then use this template starting from the balance when repayment begins. Federal loans with variable IDR payments require a different tool.
How much does an extra $100/month actually save on a mortgage?
On the $250K, 30-year, 6.75% example: an extra $100/month pays the loan off in 24 years 6 months (5.5 years early) and saves $73,792 in interest. The exact amount depends on your loan, but the ratio holds up — even a small extra payment early has an outsized impact because it stops interest from compounding on that dollar for the rest of the loan.
Should I make extra principal payments or invest that money?
Depends on your interest rate vs expected investment return. Extra principal payment is a guaranteed return equal to your loan's interest rate. Investing might earn more, but with risk. Rule of thumb: if your loan rate is under 4%, invest instead. If over 7%, pay down the loan. In between, it's a judgment call based on your risk tolerance. This calculator gives you the pay-down math; only you know your alternatives.
How do I know if refinancing is worth it?
Use the Refinancing Comparison sheet. Enter your current loan and the refinance offer (including all closing costs). The template calculates the break-even month — the point where cumulative savings exceed the refinance costs. If you plan to stay in the home past that date, refinancing is worth it. If you'll move sooner, it isn't. Simple as that.
What if my loan has a variable interest rate?
The template assumes a fixed rate throughout. For an ARM (adjustable-rate mortgage), the initial fixed period is accurately modeled. After the rate reset, you'd need to enter a new "loan" starting from the balance at reset, with the new rate and remaining term. The Refinancing Comparison sheet works well for this since the mechanics are identical.
How do I handle a loan with unusual terms — balloon payment, interest-only, etc.?
Standard amortizing loans work out of the box. For interest-only loans, set the term long enough that principal reduction is minimal in the payment period you care about. For balloon loans, model the amortizing portion with the template, then add the balloon payment separately. For truly non-standard loans (payment holidays, rate step-ups, etc.), you'll need to build a custom schedule — the template is a starting point.
Can I use it in Google Sheets?
Yes — PMT, PPMT, IPMT, CUMIPMT, CUMPRINC all work identically in Google Sheets. Upload the .xlsx to Google Drive and open with Google Sheets. Some conditional formatting may look slightly different but the math is identical.
How do I calculate my mortgage's PITI (Principal, Interest, Taxes, Insurance)?
The Calculator sheet has an optional PITI section — enter annual property tax and annual homeowners insurance, and it computes the total monthly housing payment. Note that PMI (Private Mortgage Insurance) is separate and only applies if your down payment was under 20% — add that manually as a line item.
Does the template account for tax deductions (mortgage interest deduction)?
No — the template shows raw loan math, not after-tax cost. Mortgage interest may be tax-deductible depending on your jurisdiction and personal tax situation. For a rough after-tax cost, multiply total interest paid by (1 − your marginal tax rate). Consult a tax professional for accurate after-tax calculations; the deduction rules changed significantly under the 2017 US Tax Cuts and Jobs Act and vary by country.
Why does the last payment differ slightly from all the others?
Rounding. Every monthly payment is calculated to the nearest cent, but the actual loan balance decays in fractional cents. Over 359 payments, the rounding errors accumulate to a small residual balance — the last payment absorbs this difference to zero out the loan exactly. Banks handle this the same way; your final payment will show slightly different from your regular monthly amount.
Related templates
Model any scenario with the Sheets & Cells AI Add-in
Ask questions in plain English — "what if I paid $250 extra per month?", "compare this to a 15-year at 5.5%", "when will I have $50K in equity?" — and the Add-in builds the formula and shows the answer. Right inside Excel.
Learn about the Add-in →