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.

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

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.

Read More: How to Calculate Monthly 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.

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.

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.

• 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.

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.

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.

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.

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.

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.

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.

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.

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

• Thirdly, press ENTER to get the Residual Value.

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.

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.

• 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.

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.

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.

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

• Thirdly, press ENTER to get the result.

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.

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.

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. Using 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.

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.

Read More: How to Calculate Loan Payment in Excel

3. Applying Generic Formula to Calculate the 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.

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.

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.

• 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.

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.

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

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

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.

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.

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

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

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.

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.

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

• Finally, press ENTER.

Read More: How to Calculate Auto Loan Payment in Excel

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.

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.

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.

• 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.

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.

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.

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

• Finally, press ENTER.

Read More: How to Calculate Car Payment in Excel

How to Calculate Lease Liability in Excel

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Download Practice Workbook

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

• How to Calculate Coupon Payment in Excel
• How to Calculate Monthly Mortgage Payment in Excel
• How to Calculate Balloon Payment in Excel
• How to Calculate Monthly Payment with APR in Excel

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