Compare a car loan’s monthly payment to a lease. The loan payment comes from PMT; the lease combines depreciation and a rent charge. Side by side, the cheaper monthly option is clear.
The example
$35k car, 5% APR, 60 mo, $3k down.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Loan payment | $604 |
| 3 | Lease payment | ~$420 |
The formula
The formula:
How it works
How it works:
- Loan:
PMT(rate/12, months, -(price - down))— the financed monthly payment. - Lease: depreciation
(cap_cost - residual)/termplus a rent charge(cap_cost + residual) × money_factor. - Lease payments are usually lower but you own nothing at the end.
- A true comparison adds the residual value you keep when buying.
Lower payment isn’t lower cost. A lease often wins on monthly payment but leaves you with no asset; buying builds equity in a car worth the residual at payoff. Compare total cost minus what you own at the end — loan total − resale value vs all lease payments — not just the monthly figure.
Try it: interactive demo
Price, APR, months, down.
Variations
Lease depreciation
The base:
Lease rent charge
Money factor:
True buy cost
Net of resale:
Pitfalls & errors
Compare net of ownership. Subtract the resale/residual you keep when buying.
Mileage caps. Leases penalize over-mileage — factor it for high drivers.
Money factor ×2400 ≈ APR. Convert to compare lease and loan rates.
Practice workbook
Frequently asked questions
How do I compare lease vs buy in Excel?
Is a lower lease payment cheaper?
How do I convert a lease money factor to APR?
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