Calculation Issues

Excel PMT: Calculate Lease or Loan Payments for Equipment

Rana GarciaRana Garcia Nov 12, 2026 868 views

Question details

The user needs to calculate equipment lease or loan payments in Excel without using a depreciation percentage as the financing rate.

Product
Microsoft Excel financial functions, especially PMT
Device & OS
not provided
Scenario
An equipment payment calculation uses a $550,000 value, a 24-month term, and a 0.8% depreciation rate, but the correct residual value and financing rate need to be determined.
Observed behavior
The depreciation rate was being considered as the PMT interest rate, although depreciation should generally be used to estimate residual value while the payment rate reflects financing cost or money factor.
Before you start

Confirm whether the 0.8% figure is a straight-line or declining-balance depreciation rate, and obtain the contract's financing rate or money factor. Also verify whether payments occur at the beginning or end of each period.

Solution 1Recommended

Use residual value and the financing rate in Excel PMT

Use this method when the lease contract provides a money factor or financing rate and a residual-value calculation is required.

Depreciation and financing are separate parts of a lease calculation. Use depreciation to estimate the equipment's residual value, then use the lease money factor or approved financing rate as PMT's rate argument.

For a real lease, the contract may use a payment formula other than PMT. Confirm the rate, residual value, payment timing, taxes, fees, and sign conventions with the instructor, lender, or accounting policy.

1
Calculate straight-line residual value

In an empty Excel cell, enter =550000-(550000*0.8%*24). This calculates a residual value of $444,400 when 0.8% represents a monthly straight-line decline.

2
Calculate declining-balance residual value

If depreciation is compounded monthly, select another cell and enter =550000*(1-0.8%)^24. This returns approximately $453,568.43.

3
Enter the lease payment formula

In a new cell, enter =PMT(moneyFactor,24,-550000,residualValue,1), replacing moneyFactor and residualValue with the relevant cell references or contract values. The final 1 assumes payments are made at the beginning of each period.

4
Check the result and payment timing

Review the PMT result against the contract. Use 0 instead of 1 for payments made at the end of each period, and keep the present value and future value signs consistent so the payment displays with the desired sign.

Confirm the contract method: A depreciation percentage is usually not the PMT financing rate. The correct rate may be a money factor, cost of capital, required return, or another rate specified by the contract or assignment.

Calculate Equipment Lease Payments with WPS Spreadsheet

WPS Spreadsheet supports PMT formulas and cell references for modeling equipment loans and leases. Enter the residual value, financing rate, term, and payment timing in a worksheet, then review the result in a familiar spreadsheet interface.

  1. 1. Open a spreadsheet: Launch WPS Office, choose Spreadsheet, and open a blank workbook or an existing .xlsx file.
  2. 2. Enter the lease assumptions: Create labeled cells for equipment cost, term, money factor, residual value, and payment timing.
  3. 3. Add the PMT formula: Select a result cell and use =PMT(rate,nper,pv,fv,type), replacing each argument with the corresponding cell reference.
  4. 4. Check and save the model: Review the sign conventions and payment timing, then choose File > Save As to save the completed workbook as an .xlsx file.
Build PMT-based lease and loan payment calculations with residual values and payment-timing options.Use familiar spreadsheet formulas and cell references to test different financing assumptions.Open, edit, and save Microsoft Excel .xlsx files with strong format compatibility.Use a lightweight spreadsheet tool for classroom exercises, financial models, and business calculations.
microsoft office alternative - wps office

Frequently Asked Questions

Can I use the depreciation rate as the PMT rate for an equipment lease?

Usually no. Use the depreciation rate to estimate residual value, while PMT's rate should be the lease money factor, financing cost, or other rate specified by the contract or assignment.

What residual value should I enter in the PMT formula?

For the example, straight-line depreciation gives =550000-(550000*0.8%*24), or $444,400. Declining-balance depreciation gives =550000*(1-0.8%)^24, or approximately $453,568.43. Use the method required by the contract or instructor.

Should the PMT type argument be 0 or 1 for a lease?

Use 1 when payments are made at the beginning of each period and 0 when payments are made at the end. Confirm the payment timing in the lease agreement.

Why does Excel show a negative PMT result?

Excel uses cash-flow signs. A common setup is a negative present value, such as -550000, with a positive residual value, causing PMT to return a positive payment. Reverse the signs if you need the opposite presentation.

How do I calculate a fully amortized equipment loan instead of a lease?

Use the periodic financing rate, the number of payments, the loan amount as the present value, and 0 as the future value, for example =PMT(annualRate/12,numberOfPayments,-loanAmount,0,0) for monthly end-of-period payments.