The formula that calculates your monthly lease payment

A lease payment in Excel uses the PMT function, which calculates a fixed monthly payment based on four pieces of information: the interest rate (called the money factor), the vehicle price, the residual value, and the lease term in months. The formula looks like this:

=PMT(money_factor, months, -(cap_cost - residual_value))

This gives you the depreciation portion of your payment. Then you add a separate calculation for the rent charge, which is interest on the cap cost and residual value combined. The total monthly payment is depreciation plus rent charge plus taxes and fees (which vary by state and dealer).

The reason this matters: building the calculation yourself shows you exactly where your payment comes from, and you can change any number to see how it shifts. A dealer's quote is a single number; your spreadsheet is a tool.

Key Takeaways

  • The PMT function calculates only the depreciation and interest portions of your lease payment; taxes and fees are added separately and vary by location.
  • You need four inputs to start: the money factor (from the lease offer), the capitalized cost, the residual value, and the number of months.
  • The rent charge is calculated separately as (cap cost + residual value) ÷ 2 × money factor × 12, then divided by the number of months.
  • Building the formula yourself lets you test different cap costs or terms to see how each changes your payment before you sign.

Setting up your spreadsheet with the right inputs

Start a new Excel sheet and label a column for your inputs. You need: the capitalized cost (the negotiated price of the vehicle), the residual value (what the leasing company says the car will be worth at lease end, usually given as a percentage), the money factor (the interest rate, provided by the lessor), and the lease term in months.

In the next column, enter the actual numbers from your lease offer. The money factor usually appears as a decimal like 0.0015 or 0.0020. If the lessor gives you an interest rate as a percentage instead, divide by 2400 to convert it to a money factor (for example, 3.6% ÷ 2400 = 0.0015).

The residual value on your lease paperwork might be shown as a percentage of the manufacturer's suggested retail price (MSRP). Multiply that percentage by the MSRP to get the dollar amount. For example, if the residual is 55% and the MSRP is $35,000, the residual value is $19,250.

Calculating depreciation with the PMT function

In a cell below your inputs, type the PMT formula. The syntax is =PMT(rate, nper, pv). For a lease, this becomes =PMT(money_factor, months, -(cap_cost - residual_value)). The negative sign before the cap cost minus residual value is required by Excel's PMT function.

Example: if your cap cost is $32,000, residual value is $18,000, money factor is 0.0015, and the lease is 36 months, the formula is =PMT(0.0015, 36, -(32000-18000)). This returns approximately $388.89, which is the depreciation charge per month.

The PMT function assumes the money factor is a monthly rate, which it is in lease calculations. Do not divide it further. If you see a different result than expected, check that your money factor is in decimal form (0.0015, not 0.15%) and that the cap cost minus residual value is negative in the formula.

Adding the rent charge to your calculation

The rent charge is the interest portion of your payment. It is calculated as ((cap_cost + residual_value) ÷ 2) × money_factor × 12 ÷ months. This formula finds the average value of the vehicle over the lease, multiplies it by the annual interest rate (money factor × 12), then divides by the number of months to get a monthly figure.

Using the same example: =((32000 + 18000) ÷ 2) × 0.0015 × 12 ÷ 36. This equals (25000) × 0.018 ÷ 36, which is approximately $12.50 per month.

Add this rent charge cell to your depreciation cell to get the base lease payment before taxes and fees. In this example, $388.89 + $12.50 = $401.39.

Accounting for taxes, registration, and dealer fees

Taxes and fees are not part of the PMT calculation because they vary by state, county, and dealer. Some states tax the full cap cost; others tax only the depreciation amount. Some charge registration upfront; others spread it across monthly payments. Some dealers add acquisition fees or documentation fees.

Create a separate row for taxes and fees. If your state taxes the depreciation amount (the cap cost minus residual value), multiply that by your state's sales tax rate. If it taxes the full cap cost, multiply the cap cost by the rate. Add any acquisition fee, documentation fee, or registration charge that the dealer listed on your quote.

Divide the total taxes and fees by the number of months to get a monthly amount, then add it to your base lease payment. This gives you the full monthly payment you will actually owe.

Testing different scenarios to understand the impact

Once your formula is built, change the cap cost cell to see how negotiating a lower price affects your payment. Reduce it by $1,000 and watch the payment drop. This shows you the real value of negotiation before you sit down with a dealer.

You can also test different lease terms. Change the months from 36 to 48 and recalculate. The depreciation charge per month goes down (because you spread the loss over more months), but the rent charge changes too because the formula recalculates the monthly interest. Longer leases are not always cheaper per month.

If the lessor gives you multiple money factor options based on credit, plug in each one to see the difference in your payment. A 0.0005 difference in money factor might seem small, but over 36 months it adds up.

Common mistakes to avoid when building your formula

The most frequent error is forgetting the negative sign before the cap cost minus residual value in the PMT function. Excel's PMT function expects a negative present value (the amount borrowed), so without the negative sign, you get a negative payment result.

Another mistake is using the money factor as an annual rate instead of a monthly rate. The money factor is already a monthly rate; if you divide it by 12, your rent charge will be far too low. Similarly, if the lessor gives you an annual percentage rate (APR) instead of a money factor, convert it correctly: divide the APR by 2400, not by 12.

A third error is including taxes and fees inside the PMT formula instead of calculating them separately. Taxes and fees are not part of the depreciation or rent charge; they are added on top. Mixing them into the PMT function will give you a wrong result.

Frequently Asked Questions

What if my lease offer shows an interest rate as a percentage instead of a money factor?

Divide the percentage by 2400 to convert it to a money factor. For example, 3.6% ÷ 2400 = 0.0015. This is the standard conversion because a money factor is a monthly rate, and there are 2400 months in 200 years (the industry standard for this calculation). Use the resulting money factor in your PMT formula.

Why does my calculated payment not match the dealer's quote?

The most common reason is that taxes and fees are calculated differently than you assumed. Check the dealer's quote to see whether they taxed the full cap cost or only the depreciation, and whether they included acquisition fees, documentation fees, or registration. Add or remove these items from your calculation to match their total.

Can I use this formula to compare leases from different dealers?

Yes. Build the same spreadsheet for each dealer's offer, using their cap cost, residual value, money factor, and fees. Line them up side by side and compare the final monthly payment. This shows you which dealer is offering the better deal on the same vehicle, assuming the cap cost and residual value are similar.

What happens to my payment if I negotiate the cap cost down by $2,000?

Change the cap cost cell in your spreadsheet and the depreciation charge will drop when ready. The rent charge will also drop slightly because the average vehicle value over the lease is lower. The total impact is roughly $55 to $60 per month for every $2,000 reduction in cap cost, depending on your money factor and lease term.

Do I need to include the down payment in the cap cost?

No. The cap cost is the negotiated price of the vehicle. A down payment (called a cap cost reduction) is subtracted from the cap cost before the lease payment is calculated. If you are putting $3,000 down, subtract it from the cap cost in your spreadsheet. A larger down payment lowers your monthly payment but does not reduce the interest you pay overall.