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

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

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