Set up the basic lease formula in Excel

A lease payment in Excel uses the PMT function, which calculates a fixed monthly payment based on the capitalized cost, residual value, money factor, and lease term. The formula structure is: =PMT(money_factor, number_of_months, -(capitalized_cost - residual_value)) + (capitalized_cost + residual_value) * money_factor.

This breaks into two parts: the depreciation payment (what you pay for the car's loss in value) and the finance charge (interest on the amount borrowed). Excel's PMT function handles the depreciation piece; you add the finance charge separately. The negative sign before the capitalized cost difference tells Excel to treat it as money you owe, which reverses the sign of the result.

Start by creating a simple spreadsheet with labeled cells for each input: capitalized cost, residual value, money factor, and lease term in months. Put the formula in a single cell below these inputs, and Excel will calculate your monthly payment automatically.

Key Takeaways

  • The PMT function calculates only the depreciation portion of your lease payment; you must add the finance charge (capitalized cost plus residual value, multiplied by money factor) as a separate line.
  • Money factor is an annual interest rate divided by 2,400, so a 0.0025 money factor equals a 6% annual rate.
  • Capitalized cost is the negotiated price of the car; residual value is what the leasing company estimates it will be worth at lease end.
  • The lease term is always in months, so a three-year lease is 36 months in your formula.
  • Once you build the formula once, you can change any single input and see the new payment instantly without recalculating by hand.

Gather the four numbers you need from your lease agreement

Your lease paperwork contains all the inputs Excel needs. The capitalized cost appears on the lease agreement as the negotiated selling price of the vehicle—this is what you haggled down to, not the manufacturer's suggested retail price. Look for it labeled as "cap cost" or "adjusted capitalized cost" (which includes fees and credits).

The residual value is the leasing company's prediction of what the car will be worth when the lease ends. It is usually shown as a percentage of the MSRP (for example, 55% residual value) or as a dollar amount. If you see only a percentage, multiply it by the MSRP to get the dollar figure for your formula.

The money factor is the lease agreement's version of an interest rate. It appears as a decimal like 0.0025 or 0.00275. If your paperwork shows an annual percentage rate (APR) instead, divide it by 2,400 to convert it to a money factor. A 6% APR becomes 0.0025; a 7.2% APR becomes 0.003.

The lease term is the number of months you are leasing the car. Standard terms are 24, 36, 48, or 60 months. Use the month count, not years, in your Excel formula.

Build the depreciation payment formula step by step

Open a blank Excel spreadsheet and create a row for each input. In column A, type the labels: "Capitalized Cost", "Residual Value", "Money Factor", "Lease Term (months)". In column B, enter the actual numbers from your lease agreement.

Below these inputs, create a cell for "Depreciation Payment". In the cell next to it (column B), type the PMT formula. For example, if your inputs are in cells B1 through B4, the formula is: =PMT(B3, B4, -(B1-B2)). This tells Excel to calculate the monthly payment needed to pay off the difference between capitalized cost and residual value over the lease term, using the money factor as the interest rate.

Press Enter. Excel will return a negative number—this is normal. The negative sign means money flowing out of your pocket. Take the absolute value (ignore the negative sign) to see the actual depreciation payment amount.

If you want Excel to display it as a positive number automatically, wrap the formula in ABS: =ABS(PMT(B3, B4, -(B1-B2))). This removes the negative sign without changing the value.

Add the finance charge to complete the monthly payment

The finance charge is the second part of your lease payment. It is calculated as: (Capitalized Cost + Residual Value) × Money Factor. This represents the interest you pay on the amount the leasing company is financing.

In the row below your depreciation payment, create a label "Finance Charge". In the adjacent cell, type: =(B1+B2)*B3. This adds the capitalized cost and residual value, then multiplies by the money factor to get your monthly finance charge.

Now create a final row labeled "Total Monthly Payment". In the cell next to it, add the two payments together: =B6+B8 (or whatever cells contain your depreciation payment and finance charge). This is your actual monthly lease payment before taxes, registration, and any other fees.

Test your formula with a real lease example

Use these sample numbers to verify your spreadsheet is working correctly. Capitalized cost: $28,000. Residual value: $16,800 (60% of $28,000 MSRP). Money factor: 0.0025 (6% APR). Lease term: 36 months.

The depreciation payment should be approximately $314 per month. The finance charge should be approximately $112 per month (($28,000 + $16,800) × 0.0025 = $112). The total monthly payment should be around $426 before taxes and fees.

If your numbers are significantly different, double-check that the money factor is correct (divide the APR by 2,400) and that the lease term is in months, not years. A common mistake is entering 3 instead of 36 for a three-year lease, which will make the payment appear much higher than it should be.

Modify your spreadsheet to compare different lease scenarios

Once your basic formula works, you can use it to test "what-if" scenarios. Copy your input section to the right, creating columns for different lease offers. Keep the formula structure the same but change the cell references in each column to point to the new inputs.

For example, if you are deciding between two cars or two lease terms, set up one scenario in columns A–B and another in columns D–E. Copy your depreciation and finance charge formulas to both sections. Now you can see side-by-side which option costs less per month without recalculating by hand.

You can also use this to see how negotiating the capitalized cost affects your payment. Lower the cap cost by $1,000 and watch the monthly payment drop. This shows you exactly how much each dollar of negotiation saves you over the lease term.

Frequently Asked Questions

What if my lease agreement shows APR instead of money factor?

Divide the APR by 2,400 to convert it to a money factor. A 6% APR becomes 0.0025; a 7.2% APR becomes 0.003. Then use that money factor in your PMT formula. This conversion is standard across all leasing companies.

Why does the PMT function return a negative number?

Excel's PMT function returns a negative number because it treats the loan amount as money you receive and the payment as money you pay back. Wrap the formula in ABS() to display it as positive, or simply ignore the negative sign when reading the result.

Should I include taxes and fees in the capitalized cost?

Your lease agreement will show "adjusted capitalized cost," which already includes taxes, registration, dealer fees, and other charges. Use this adjusted figure, not the base vehicle price, to match your actual monthly payment.

Can I use this formula to calculate a car loan payment instead of a lease?

Yes, but the setup is different. For a loan, use =PMT(monthly_interest_rate, number_of_months, -loan_amount). The monthly interest rate is the annual rate divided by 12. A lease uses money factor (annual rate divided by 2,400) because it accounts for the residual value differently.

What happens if I change the lease term from 36 to 48 months?

Both the depreciation payment and total payment will drop because you are spreading the cost over more months. However, your total interest paid over the life of the lease will increase. Excel will recalculate instantly when you change the term in your spreadsheet.