Which loan really costs less? Put both side by side and let PMT compute each monthly payment and total interest. A lower rate with a longer term can still cost more — the math settles it.
The example
$25,000 at 6%/5yr versus 5.5%/6yr.
| A | B | C | |
|---|---|---|---|
| 1 | Loan A | Loan B | |
| 2 | Payment | $483.32 | $408.20 |
| 3 | Total interest | $3,999 | $4,390 |
The formula
Payment and total interest for each loan:
How it works
Compare the true cost, not just the payment:
PMT(rate/12, years*12, -principal)gives the monthly payment for each loan.- Total paid is
payment × number of payments; subtract the principal for total interest. - A longer term lowers the payment but usually raises total interest — compare the interest, not the payment alone.
- Build both columns from the same formulas so changing a rate or term updates the comparison instantly.
Add the break-even: if one loan has fees, compute how many months of payment savings it takes to recover them — =fees / monthlySaving.
Try it: interactive demo
Set principal and two rate/term options.
Variations
Total interest
All-in cost of borrowing:
Interest only (CUMIPMT)
Over the whole term:
Fee break-even
Months to recover fees:
Pitfalls & errors
Compare total interest, not payment. A smaller payment over more months often costs more overall.
Sign convention. Enter principal negative (or negate PMT) so the payment is positive.
Match the period. Monthly loans use rate/12 and years*12; mixing annual and monthly skews everything.
Practice workbook
Frequently asked questions
How do I compare two loans in Excel?
How do I find total interest on a loan?
How do I account for loan fees?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class