Free Excel Loan Calculator Template (2026) | Sheets & Cells
PERSONAL FINANCE TEMPLATE

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.xlsx
File size~90 KB
Format.xlsx (Excel 2016+)
LicenseFree personal & commercial use
Last updatedJanuary 2026
Any loan typeMortgage, auto, student, personal
Any currencyUSD, EUR, GBP, INR, PKR…
Extra paymentsSee time & interest saved
Up to 40 years480 monthly rows built in

Preview: 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.

🧮 Calculator sheet — instant results Sheet 2 of 5
Loan Inputs
Loan Amount $250,000
Interest Rate (APR) 6.75%
Term (years) 30
Start Date Jan 2026
Extra Monthly Payment $0
Monthly Payment
$1,621.50
Principal & Interest, before taxes & insurance
Total Interest
$333,738
Total Paid
$583,738
Payoff Date
Jan 2056
Interest Ratio
57.2%

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.

📊 Amortization Schedule — payment by payment Sheet 3 of 5
# 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
Lifetime split — how your total payments break down:
Principal 42.8%
Interest 57.2%

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:

No Extra Payment
Payoff In
30 yrs 0 mo
Total Interest
$333,738
Interest Saved
$0
+$100 / month
Payoff In
24 yrs 6 mo
Total Interest
$259,946
Interest Saved
$73,792
+$500 / month
Payoff In
14 yrs 11 mo
Total Interest
$129,847
Interest Saved
$203,891

Numbers based on the $250K, 6.75%, 30-year example above. Your actual results depend on your loan.

Ready to see the numbers on your own loan?
Download .xlsx

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:

Payment = P × r × (1+r)ⁿ / ((1+r)ⁿ − 1)

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:

=PMT(6.75%/12, 30*12, -250000) → $1,621.50

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.

Template limitation: This calculator assumes a fixed rate throughout the loan. For adjustable-rate mortgages (ARMs), it accurately models the initial fixed period — but after the reset date, actual payments will differ. To model an ARM, you can create a second sheet with the post-reset rate and treat it as a new loan starting at the reset balance.

How to use it — 3-step setup

1

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.
2

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.
3

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:

See the real math on your loan — one download away.
Download .xlsx

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 →