How to Calculate Lease Payments in Excel – 4 Methods

Written by Mashhura Jahan

Last updated: Aug 3, 2024

This dataset showcases the Amount Details.

Read More: How to Calculate Monthly Payment in Excel

Method 1 – Using a Generic Formula to Calculate Lease Payments in Excel

Example 1 – Calculating Lease Payment When Residual Value Is Given

This is the dataset:

Steps:

Calculate the Adjustable Capitalized Cost

Select a cell to calculate your Adjustable Capitalized Cost. Here, C13.
In C13 enter the following formula.

=C5-C6+C7

Press ENTER to see the result.

Calculate the Depreciation Cost

Select a cell to calculate Depreciation Cost. Here, C14.
Enter the following formula in C14.

=(C13-C8)/C11

Press ENTER to see the Depreciation Cost.

Calculate the Money Factor

Select a cell to see the Money Factor. Here, C15.
Enter the following formula in C15.

=C9/24

Press ENTER to see the Money Factor.

Calculate the Interest

Select a cell to see the Interest. Here, C16.
Enter the following formula in cell C16.

=(C13+C8)*C15

Press ENTER to see the Interest.

Calculate the Tax

Select a cell to see the Tax. Here, C17.
Enter the following formula in cell C17.

=(C16+C14)*C10

Press ENTER to see the result.

Calculate the Monthly Lease Payment.

Select a cell to see the Monthly Lease Payment. Here, C18.
Enter the following formula in C18.

=C14+C16+C17

Press ENTER to see the Monthly Lease Payment.

Example 2 – Calculating the Monthly Lease Payment When Residual Value Is Not Given

This is the dataset:

Steps:

Calculate the Residual Value

Select a cell to calculate your Residual Value. Here, C12.
Enter the following formula in C12.

=C5*C8

Press ENTER to see the Residual Value.

Calculate the Depreciation Cost.

Select a cell to calculate the Depreciation Cost. Here, C13.
Enter the following formula in C13.

=(C6-C12)/C10

Press ENTER to see the Depreciation Cost.

Calculate the Interest

Select a cell to calculate the Interest. Here, C14.
Enter the following formula in C14 .

=(C12+C6)*C7

Press ENTER to see the Interest.

Calculate the Total

Select a cell to display the Total. Here, C15.
Enter the following formula in C15.

=C13+C14

Press ENTER to see the result.

Calculate the Tax

Select a cell to calculate the Tax. Here, C16.
Enter the following formula in C16.

=C15*C9

Press ENTER.

Calculate the Lease Payment

Select a cell to see the Monthly Lease Payment. Here, C17.
Enter the following formula in C17.

=C15+C16

Press ENTER and see the Monthly Lease Payment.

Read More: How to Calculate Monthly Payment on a Loan in Excel

Method 2 – Using the PMT Function to Calculate Lease Payments in Excel

This is the dataset:

Steps:

Select a cell to see the Monthly Lease Payment. Here, C10.
Enter the following formula in C10.

=PMT(C7/12,C8,-C5,C6,0)

Press ENTER and see the Monthly Lease Payment.

Read More: How to Calculate Loan Payment in Excel

Method 3 – Applying a Generic Formula to Calculate the Present Value of Lease Payments

This is the dataset:

Calculate the lease Amount after each period

Steps:

Select a cell to calculate the lease Amount after each period. Here, C10.
Enter the following formula in C10 .

=D4

Press ENTER and see the result.

Calculate the lease Amount after period 1

Select a cell to calculate the lease Amount after period 1. Here, C11.
Enter the following formula in C11 .

=C10*$D$5+C10

Press ENTER.

Drag the Fill Handle to copy the formula.

The lease Amount after each Period is displayed.

Calculate the Present Value.

Select a cell to see the Present Value. Here, D10.
Enter the following formula in D10 .

=C10/((1+$D$6)^B10)

Press ENTER.

Drag the Fill Handle to copy the formula.

The formula is copied to the other cells.

Calculate the Total Lease Amount.

Select a cell to calculate the Total.
Enter the following formula in the selected cell.

=SUM(C10:C13)

Press ENTER to see the Total.

Calculate the Total Present Value.

Select a cell to see the Total. Here, D14.
Enter the following formula in D14 .

=SUM(D10:D13)

Press ENTER.

Read More: How to Calculate Auto Loan Payment in Excel

Method 4 – Using the PV Function to Calculate the Present Value of Lease Payments

Steps:

Insert the lease Amount as described in Method 3.

Calculate the Present Value of the Lease Payment.

Select a cell to see the Present Value. Here, D10.
Enter the following formula in D10.

=PV($D$6,B10,0,-C10,0)

Press ENTER to see the Present Value.

Drag the Fill Handle to copy the formula.

The formula returns the Present Value after each Period.

Calculate the Total lease Amount

Select a cell to calculate the Total.
Enter the following formula in the selected cell.

=SUM(C10:C13)

Press ENTER to see the Total.

Calculate the Total Present Value.

Select a cell to see the Total. Here, D14.
Enter the following formula in D14.

=SUM(D10:D13)

Press ENTER.

Read More: How to Calculate Car Payment in Excel

Calculate Lease Liability in Excel

This is the dataset:

Calculate the Liability Reduction

Steps:

Insert 0 as Interest for the first year.

Select a cell to see the Liability Reduction. Here, E8.
Enter the following formula in E8.

=C8-D8

.

Press ENTER to see the Liability Reduction.

Drag the Fill handle to copy the formula.

The formula is copied to the other cells.

Calculate the Liability Balance

Select a cell to calculate the Liability Balance.
Enter the following formula in the selected cell.

=F7-E8

Press ENTER.

Drag the Fill Handle to copy the formula.

The formula is copied to the other cells.

Calculate the Interest.

Select a cell to calculate the Interest. Here, D9.
Enter the following formula in D9.

=F8*$D$4

Press ENTER and you will see the Interest.

Drag the Fill Handle to copy the formula.

The formula is copied to the other cells.

Calculate the Opening Liability Balance

Select a cell to see the Opening Liability Balance. Here, F7.
Go to the Data tab.
Select What-If Analysis.
Select Goal Seek from the drop-down menu.

In the dialog box select the last cell of Liability Balance as Set cell.
Enter 0 as To value.
Select the first cell in By changing cell.
Click OK.

Click OK in the Goal Seek Status in the dialog box .

Lease Liability was correctly calculated.

Download Practice Workbook

Calculating Lease Payment.xlsx

Related Articles

<< Go Back to Calculate Payment in Excel | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

SaveSavedRemoved 0

Tags: Calculate Payment in Excel

Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

2 Comments

Reply
Mayank May 3, 2023 at 4:02 PM

Calculate the monthly lease payment with following information – Selling Price Rs 28440, Maintainence and repair for 36 months- 4500 (To be Included in Lease payment 28440+4500), Residual Value 7.5%, interest rate at 20% the lease period of 36 months. how do we calculate the monthly payment in excel?
- Reply
  Fahim Shahriyar Dipto May 3, 2023 at 5:13 PM
  
  To calculate the monthly lease payment in Excel, you can use the following formula:
  =PMT(rate/12,nper,-PV,FV)
  where:
  rate = the interest rate per period (in this case, 20% divided by 12 for monthly payments)
  nper = the total number of periods (in this case, 36 months)
  PV = the present value of the lease (in this case, the selling price plus maintenance and repair costs, or Rs 28440 + Rs 4500 = Rs 32940)
  FV = the future value of the lease (in this case, the residual value, or 7.5% of the selling price, or Rs 2133)
  In our dataset, in Cell C11 we entered the below formula.
  =PMT(C9/12,C8,(-C6),C7,0)
  See the image for better visualization.
  
  Hope you are able to calculate the monthly lease payment now. Have a nice day. Keep supporting us.
  Regards,
  Fahim Shahriyar Dipto
  Excel and VBA Content Developer

Leave a reply Cancel reply

Submit Excel Problem