Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Calculate a Lease Payment in Excel (4 Easy Ways)

If you want to calculate a lease payment, Excel can come really handy. The main objective of this article is to explain how to calculate a lease payment in Excel.


Download Practice Workbook


What Is Lease Payment?

Lease payment generally refers to the rental payment. For this type of payment, there is an agreed contract between the lessor and the lessee. It can include different types of properties for a specific time period.

There, are 3 components of a lease payment.

  • Depreciation Cost
  • Interest
  • Tax

The Depreciation Cost is the loss in value of the property which is spread throughout the lease period. The formula for Depreciation Cost is,

Depreciation Cost = (Adjusted Capitalized Cost – Residual Value)/Lease Period

Here,

Adjusted Capitalized Cost is the addition of Negotiated Price with any other dealer fees and Outstanding Loan minus the Down Payment if there is any.

Residual Value is the value of the property at the end of the Lease Period.

The Lease Period is the lease contract’s length.

Interest means the interest payments on loans. The formula for Interest is,

Interest = (Adjusted Capitalized Cost – Residual Value)*Money Factor

Here,

The formula for Money Factor is,

Money Factor = Interest Rate/24

Tax refers to the tax amount applied to Depreciation Cost and Interest. The formula for Tax is,

Tax = (Depreciation Cost + Interest)* Tax Rate

Finally, the formula for Lease Payment is,

Lease Payment = Depreciation + Cost Interest + Tax


4 Easy Ways to Calculate a Lease Payment in Excel

In this article, I will explain how to calculate a lease payment in Excel in 4 easy ways. Here, I have taken the following dataset to explain how to calculate a lease payment. This dataset contains Amount Details.

4 Easy Ways to Calculate a Lease Payment in Excel


1. Using Generic Formula to Calculate a Lease Payment in Excel

In this first method, I will use the generic formula to calculate a lease payment in Excel. Here, I will show you 2 different examples for your better understanding.


Example-01: Calculating Lease Payment When Residual Value Is Given

For this first example, I have taken the following dataset. Suppose, you want to buy a car on lease. The Lease Period will be 36 months and will charge a 9% Interest Rate. Your Negotiated Price is $45,000 with a Down Payment of $5,000 and an Outstanding Loan of $7,000. The Residual Value of the car is $30,000 and the Tax Rate is 6%.

Now, I will show you how you can calculate your monthly Lease Payment with this data.

Using Generic Formula to Calculate a Lease Payment in Excel

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to calculate your Adjustable Capitalized Cost. Here, I selected cell C13.
  • Secondly, in cell C13 write the following formula.
=C5-C6+C7 

Here, the formula will subtract the value in cell C6 which is Down Payment from the value in cell C5 which is the Negotiated Price. And then sum the result with the value in cell C7 which is the Outstanding Loan. Finally, the formula will return the Adjustable Capitalized Cost as result.

  • Thirdly, press ENTER to get the result.

Using Generic Formula to Calculate a Lease Payment in Excel

  • Now, select the cell where you want to calculate your Depreciation Cost. Here, I selected cell C14.
  • Next, in cell C14 write the following formula.
=(C13-C8)/C11

Here, the formula will subtract the value in cell C8 which is the Residual Value from the value in cell C13 which is the Adjusted Capitalized Cost. Then, divide the result by the value in cell C11 which is the Lease Period. Finally, the formula will return the Depreciation Cost.

  • After that, press ENTER to get the Depreciation Cost.

Using Generic Formula to Calculate a Lease Payment in Excel

Now, I will calculate the Money Factor.

  • Firstly, select the cell where you want your Money Factor. Here, I selected cell C15.
  • Secondly, in cell C15 write the following formula.
=C9/24

Here, the formula will divide the value in cell C9 which is the Interest Rate by 24, and return the Money Factor as result.

  • Thirdly, press ENTER to get the Money Factor.

Now, I will calculate the Interest.

  • Firstly, select the cell where you want your Interest. Here, I selected cell C16.
  • Secondly, in cell C16 write the following formula.
=(C13+C8)*C15

Using Generic Formula to Calculate a Lease Payment in Excel

Here, the formula will sum the value in cell C13 which is the Adjusted Capitalized Cost with the value in cell C8 which is the Residual Value, and then multiply it by the value in cell C15 which is the Money Factor. Finally, the formula will return the Interest.

  • Thirdly, press ENTER and you will get your Interest.

At this point, I will calculate the Tax.

  • Firstly, select the cell where you want your Tax. Here, I selected cell C17.
  • Secondly, in cell C17 write the following formula.
=(C16+C14)*C10

Using Generic Formula to Calculate a Lease Payment in Excel

Here, the formula will sum the value in cell C16 which is the Interest with the value in cell C14 which is the Depreciation Cost, and then multiply it by the value in cell C10 which is the Tax Rate. Finally, it will return Tax as result.

  • Thirdly, press ENTER to get the result.

Now, I will calculate the Monthly Lease Payment.

  • Firstly, select the cell where you want your Monthly Lease Payment. Here, I selected cell C18.
  • Secondly, in cell C18 write the following formula.
=C14+C16+C17

Using Generic Formula to Calculate a Lease Payment in Excel

Here, the formula will return the summation of the value in cell C14 which is the Depreciation Cost, the value in cell C16 which is the Interest, and the value in cell C17 which is the Tax. And, this will be the Monthly Lease Payment.

  • Finally, press ENTER to get the Monthly Lease Payment.


Example-02: Calculating Monthly Lease Payment When Residual Value Is Not Given

To explain this example, I have taken the following dataset. Suppose, you want to buy a car on lease. The Retail Price of the car is $50,000 and the Selling Price is $45,000. Here, the Lease Period is 36 months with a Residual of 60% and a Tax Rate of 6% along with a Money Factor of 0.001.

Now, I will show you how to calculate your Monthly Lease Payment with this data.

Using Generic Formula to Calculate a Lease Payment in Excel

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to calculate your Residual Value. Here, I selected cell C12.
  • Secondly, in cell C12 write the following formula.
=C5*C8

Here, the formula will multiply the Retail Price by the Residual and return the Residual Value.

  • Thirdly, press ENTER to get the Residual Value.

Using Generic Formula to Calculate a Lease Payment in Excel

Now, I will calculate the Depreciation Cost.

  • Firstly, select the cell where you want to calculate the Depreciation Cost. Here, I selected cell C13.
  • Secondly, in cell C13 write the following formula.
=(C6-C12)/C10 

Here, the formula will subtract the Residual Value from the Selling Price, and then divide it by the Lease Period. It will return the Depreciation Cost.

  • Thirdly, press ENTER to get the Depreciation Cost.

Using Generic Formula to Calculate a Lease Payment in Excel

  • After that, select the cell where you want to calculate the Interest. Here, I selected cell C14.
  • Next, in cell C14 write the following formula.
=(C12+C6)*C7

Here, the formula will sum the Residual Value and the Selling Price and then multiply it by the Money Factor. It will return Interest as result.

  • Finally, press ENTER and you will get your Interest.

Using Generic Formula to Calculate a Lease Payment in Excel

Now, I will calculate the Total.

  • Firstly, select the cell where you want your Total. Here, I selected cell C15.
  • Secondly, in cell C15 write the following formula.
=C13+C14

Here, the formula will sum the Depreciation Cost and the Interest and return the Total.

  • Thirdly, press ENTER to get the result.

Using Generic Formula to Calculate a Lease Payment in Excel

After that, I will calculate the Tax.

  • Firstly, select the cell where you want to calculate Tax. Here, I selected cell C16.
  • Secondly, in cell C16 write the following formula.
=C15*C9

Using Generic Formula to Calculate a Lease Payment in Excel

Here, the formula will multiply the Total by Tax Rate and return the Tax.

  • Thirdly, press ENTER.

Finally, I will calculate the Lease Payment.

  • Firstly, select the cell where you want your Monthly Lease Payment. Here, I selected cell C17.
  • Secondly, in cell C17 write the following formula.
=C15+C16

Using Generic Formula to Calculate a Lease Payment in Excel

Here, the formula will return the summation of Total and Tax which is the Monthly Lease Payment.

  • Thirdly, press ENTER and you will get the Monthly Lease Payment.

Read More: How to Calculate Monthly Payment on a Loan in Excel (2 Ways)


2. Employing PMT Function to Calculate a Lease Payment in Excel

In this method, I will explain how to calculate a lease payment in Excel by employing the PMT function.

To explain this method, I have taken the following dataset. Suppose, you want to buy a car. The Selling Price of the car is $45,000. Here, the Residual Value is $30,000 with an annual Interest Rate of 6% and the Lease Period is 36 months.

Now, I will show you how to calculate the Monthly Lease Payment by using the PMT function.

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want your Monthly Lease Payment. Here, I selected cell C10.
  • Secondly, in cell C10 write the following formula.
=PMT(C7/12,C8,-C5,C6,0)

Employing PMT Function to Calculate a Lease Payment in Excel

Here, in the PMT function, I selected C7/12 as the rate because I am calculating on a monthly basis. Then, I selected C8 as nper, -C5 as PV, C6 as FV, and 0 as Type. The formula will return the Monthly Lease Payment.

  •   Finally, press ENTER and you will get your Monthly Lease Payment.

Employing PMT Function to Calculate a Lease Payment in Excel

Read More: How to Calculate Loan Payment in Excel (4 Suitable Examples)


3. Applying Generic Formula to Calculate Present Value of Lease Payment

In this method, I will explain how to calculate the Present Value of Lease Payment in Excel by applying the generic formula.

Here, I have taken the following dataset to explain this example.

Applying Generic Formula to Calculate Present Value of Lease Payment

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to calculate your lease Amount after each period. Here, I selected cell C10.
  • Secondly, in cell C10 write the following formula.
=D4

Here, the formula will return the value in cell D4 which is the Annual Lease Payment as the result.

  • Thirdly, press ENTER and you will get the result.

Applying Generic Formula to Calculate Present Value of Lease Payment

  • After that, select the cell where you want to calculate the lease Amount after 1 period. Here, I selected cell C11.
  • Next, in cell C11 write the following formula.
=C10*$D$5+C10

Here, the formula will multiply the lease Amount at the beginning of the Period by Escalation and then sum it with the lease Amount at the beginning of the Period. It will return the lease Amount after Period 1.

  • Finally, press ENTER.

Applying Generic Formula to Calculate Present Value of Lease Payment

Here, you can see I have copied my formula and got the lease Amount after every Period.

Applying Generic Formula to Calculate Present Value of Lease Payment

Now, I will calculate the Present Value.

  • Firstly, select the cell where you want your Present Value. Here, I selected cell D10.
  • Secondly, in cell D10 write the following formula.
=C10/((1+$D$6)^B10)

Here, the formula will sum 1 with Discount Rate and raise the result to the power of Period. Then, divide the lease Amount by the result. And thus, it will return the Present Value.

  • Thirdly, press ENTER.

Applying Generic Formula to Calculate Present Value of Lease Payment

  • After that, drag the Fill Handle to copy the formula.

Now, you can see I have copied the formula to the other cells.

Applying Generic Formula to Calculate Present Value of Lease Payment

After that, I will calculate the Total Lease Amount.

  • Firstly, select the cell where you want to calculate the Total.
  • Secondly, write the following formula in the selected cell.
=SUM(C10:C13)

Here, the SUM function will return the summation of cell range C10:C13 which is the Total Lease Amount.

  • Thirdly, press ENTER to get the Total.

Applying Generic Formula to Calculate Present Value of Lease Payment

Now, I will calculate the Total Present Value.

  • Firstly, select the cell where you want your Total. Here, I selected cell D14.
  • Secondly, in cell D14 write the following formula.
=SUM(D10:D13)

Here, the SUM function will return the summation of cell range D10:D13 which is the Total Present Value.

  • Finally, press ENTER.

Applying Generic Formula to Calculate Present Value of Lease Payment

Read More: How to Calculate Auto Loan Payment in Excel (with Easy Steps)


4. Using PV Function to Calculate Present Value of Lease Payment

In this method, I will use the PV function to calculate the Present Value of Lease Payment. Let’s see the steps.

Steps:

  • To begin with, insert the lease Amount by following the steps from Method-03.

Using PV Function to Calculate Present Value of Lease Payment

Now, I will calculate the Present Value of the Lease Payment.

  • Firstly, select the cell where you want your Present Value. Here, I selected cell D10.
  • Secondly, in cell D10 write the following formula.
=PV($D$6,B10,0,-C10,0)

Here, in the PV function, I selected cell D6 as rate, B10 as nper, 0 as pmt, -C10 as fv, and 0 as type. The formula will return the Present Value.

  • Finally, press ENTER to get the Present Value.

Using PV Function to Calculate Present Value of Lease Payment

  • Now, drag the Fill Handle to copy the formula.

Here, you can see I have copied the formula and got the Present Value after every Period.

Using PV Function to Calculate Present Value of Lease Payment

At this point, I will calculate the Total lease Amount.

  • Firstly, select the cell where you want to calculate the Total.
  • Secondly, write the following formula in the selected cell.
=SUM(C10:C13)

Here, the SUM function will return the summation of cell range C10:C13 which is the Total lease Amount.

  • Thirdly, press ENTER to get the Total.

Now, I will calculate the Total Present Value.

  • Firstly, select the cell where you want your Total. Here, I selected cell D14.
  • Secondly, in cell D14 write the following formula.
=SUM(D10:D13)

Using PV Function to Calculate Present Value of Lease Payment

Here, the SUM function will return the summation of cell range D10:D13 which is the Total Present Value.

  • Finally, press ENTER.

Using PV Function to Calculate Present Value of Lease Payment

Read More: How to Calculate Car Payment in Excel (with Easy Steps)


How to Calculate Lease Liability

In this section, I will explain how you can calculate Lease Liability in Excel. I will explain this with the following example.

How to Calculate Lease Liability

Let’s see the steps.

Steps:

  • Firstly, insert 0 as Interest for the first year.

  • Secondly, select the cell where you want your Liability Reduction. Here, I selected cell E8.
  • Thirdly, in cell E8 write the following formula.
=C8-D8

How to Calculate Lease Liability

Here, the formula will subtract the Interest from the Lease Amount and return the Liability Reduction.

  • Finally, press ENTER to get the Liability Reduction.

  • After that, drag the Fill handle to copy the formula.

How to Calculate Lease Liability

Now, you can see I have copied the formula to the other cells. Here, the result is not correct because I have not entered all the data.

How to Calculate Lease Liability

At this point, I will calculate the Liability Balance.

  • Firstly, select the cell where you want to calculate the Liability Balance.
  • Secondly, write the following formula in that selected cell.
=F7-E8

Here, the formula will subtract the value in cell E8 from the value in cell F8 and return the Liability Balance.

  • Thirdly, press ENTER.

  • Next, drag the Fill Handle to copy the formula.

How to Calculate Lease Liability

Now, you can see that I have copied the formula to the other cells.

Here, I will calculate the Interest.

  • Firstly, select the cell where you want to calculate the Interest. Here, I selected cell D9.
  • Secondly, in cell D9 write the following formula.
=F8*$D$4

How to Calculate Lease Liability

Now, this formula will multiply the Discount Rate by the Liability Balance from the year before and return the Interest.

  • Thirdly, press ENTER and you will get the Interest.

  • After that, drag the Fill Handle to copy the formula.

How to Calculate Lease Liability

Here, you can see I have copied the formula.

  • Now, select the cell where you want your Opening Liability Balance. Here, I selected cell F7.
  • Next, go to the Data tab.
  • Then, select What-If Analysis.

A drop-down menu will appear.

  • After that, select Goal Seek from the drop-down menu.

How to Calculate Lease Liability

Now, a dialog box will appear.

  • Firstly, select the last cell of Liability Balance as Set cell.
  • Secondly, write 0 as To value.
  • Thirdly, select the first cell as By changing cell.
  • After that, select OK.

How to Calculate Lease Liability

Here, a dialog box named Goal Seek Status will appear.

  • Now, select OK.

Finally, you can see that I have calculated Lease Liability and got all the correct values.


Practice Section

Here, I have provided a practice dataset for you to practice how to calculate a Lease Payment in Excel.


Conclusion

To conclude, I tried to cover how to calculate a lease payment in Excel. Here, I explained 4 different methods of doing it. I hope this article was helpful for you. Lastly, if you have any questions let me know in the comment section below.


Related Articles

Mashhura Jahan

Mashhura Jahan

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo