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

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

`=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.

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

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`

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`

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`

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.

`=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**.

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

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

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.

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`

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`

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)`

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

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.

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

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

`=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**.

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

`=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)`

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 (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 Paymen**t. 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.

`=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**.

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

`=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)`

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

- Finally, press
**ENTER**.

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

Letâ€™s see the steps.

**Steps:**

- Firstly, insert
**0**as**Interest f**or the first year.

- Secondly, select the cell where you want your L
**iability Reduction**. Here, I selected cell**E8**. - Thirdly, in cell
**E8**write the following formula.

`=C8-D8`

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.

`=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.

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`

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.

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

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?

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 by12for monthly payments)nper= the total number of periods (in this case,36months)PV= the present value of the lease (in this case, the selling price plus maintenance and repair costs, or Rs28440+ Rs4500= Rs32940)FV= the future value of the lease (in this case, the residual value, or7.5%of the selling price, or Rs2133)In our dataset, in

Cell C11we 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