This is the one thing you must be able to do under the new lease accounting rules:

Under the new lease accounting standards, lease capitalization is required for the vast majority of leases. The capitalized amount is calculated as the present value of the lease payments. Therefore, it goes without saying that to comply with the new lease standard, you will need to know how to calculate the present value of lease payments. This is especially true if you do not plan on using any software and would prefer to use Excel spreadsheets to manage your leases.

We believe managing your leases in Excel is a bad idea.

However, if you prefer Excel, we can at least help you use it correctly. Here are your 2 simple options:

- Calculate the present value of lease payments only, using Excel
- Calculate the present value of lease payments AND amortization schedule using Excel.

This post will address **how to calculate the present value of the lease payments using Excel**. We also build an Excel template that performs this calculation for you automatically.

**Get the free Present Value Calculation Template to follow along**

Read our follow-up blog that will show you how to calculate the present value of lease payments **and** get the liability amortization schedule in **one step** with Excel. With this method, you will have **everything** you need to comply with the new lease accounting rules **powered only by an Excel spreadsheet**. You’re welcome.

Now on to today’s blog.

## How to calculate the present value of lease payments in 5 steps:

Here are the steps to follow to calculate the present value of lease payments using Excel when the payment amounts are different. Let’s use an example:

*Calculate the present value of lease payments for a 10-year lease with annual payments of $1,000 with 5% escalations annually, paid in advance. Assume the rate inherent in the lease is 6%.*

### Step 1: Create your table with headers

In an Excel spreadsheet, title three columns with the following headers: Period, Cash and Present Value, as shown below:

### Step 2: Enter the correct numbers in the Period column

Enter the number periods starting from 0 to 9. Note that if payments were made in arrears, the numbering would start from 1 to 9. See below for an illustration:

### Step 3: Insert the PV function

Go to the first row of the “Present Value” column, then click on the “insert function” button. From the dialogue box that pops up, select “Financial” in the dropdown, then scroll down and select “PV” (which stands for Present Value) under that.

### Step 4: Enter the Rate, Nper Pmt and Fv

After you click OK, another dialogue box will pop up. Enter 6% in the rate box. In the Nper box, enter the cell for the corresponding period. Enter 0 for Pmt, and in the field for Fv enter the cell for the corresponding cash. Keep type as zero (frankly, it doesn’t matter if you select zero or 1 here because we are discounting via the period column). Copy that formula all the way down.

### Step 5: Sum the “Present Value” column

Based on this, the present value of a 10-year lease with payments of $1,000 annually, 5% escalations and a rate inherent in the lease of 6% is $9,586.

There you have it, a way to use Excel to calculate the present value of lease payments using Excel. As we promised earlier, we are giving you a free template that does this calculation for you automatically. All you do is complete the items in yellow (enter the lease term, the payments, and specify if the payments are made at the beginning of the lease or at the end). The spreadsheet will then calculate your present value for you automatically. (See the image of the template below).

## More blogs and resources

If you liked this post, consider reading our follow-up blog on creating the lease liability amortization schedule in Excel.

**Interested in this template? Download our present value caluclation tool to calculate the present value of your lease payments**:

Good

How do you know what interest rate to use?

Hey Jake,

Thanks for reaching out. A lessee should use the rate implicit in the lease whenever that rate is readily determinable, we have found that this is uncommon that the lessee would know this rate. Therefore, If the rate implicit in the lease is not readily determinable, a lessee should use its’ incremental borrowing rate. The incremental borrowing rate can be determined by the rate of interest that a lessee would have to pay to borrow on a collateralized basis over a similar term an amount equal to the lease payments in a similar economic environment. Finally, if you are a private entity you can elect the practical expedient to use the risk-free rate, determined using a period comparable with that of the lease, for all leases.

Let us know if you need any further clarification and we would be happy to help.