How to Calculate Lease Payments in Excel – 4 Methods

Written by Mashhura Jahan

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 the cell where you want 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 the cell where you want to calculate your 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 the cell where you want the Money Factor. Here, C15.
Enter the following formula in C15.

=C9/24

Press ENTER to see the Money Factor.

Calculate the Interest

Select the cell where you want the Interest. Here, C16.
Enter the following formula in cell C16.

=(C13+C8)*C15

Press ENTER to see the Interest.

Calculate the Tax

Select the cell where you want 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 the cell where you want 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 the cell where you want 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 the cell where you want 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 the cell where you want 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 the cell where you want the Total. Here, C15.
Enter the following formula in C15.

=C13+C14

Press ENTER to see the result.

Calculate the Tax

Select the cell where you want to calculate the Tax. Here, C16.
Enter the following formula in C16.

=C15*C9

Press ENTER.

Calculate the Lease Payment

Select the cell where you want 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 the cell where you want 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 the cell where you want 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 the cell where you want 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 the cell where you want 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 the cell where you want 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 the cell where you want 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 shown in Method-03.

Calculate the Present Value of the Lease Payment.

Select the cell where you want 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 the cell where you want 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 the cell where you want 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 the cell where you want 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 the cell where you want 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 the cell where you want 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 the cell where you want 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 as 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