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

The new lease accounting rules require the vast majority of leases to be capitalized. The capitalized amount is calculated as the present value of the lease payments. It goes without saying; therefore, 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:

  1. Calculate the present value of lease payments only, using Excel
  2. 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, using 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.

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) In an Excel spreadsheet, title three columns with the following headers: Period, Cash and Present Value, as shown below:

Present Value Minimum Lease Payments – Step 1

Step 2) 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 the step 2 illustration:

Present Value Minimum Lease Payments – Step 2

Step 3) 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.

Present Value Minimum Lease Payments – Step 3

Step 4) 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 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.

Present Value Minimum Lease Payments – Step 4

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.

Present Value Minimum Lease Payments – Step 5

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

As always, we write detailed blogs like this to demonstrate that our experts at LeaseQuery are not just real estate professionals, but also lease accounting experts. If you need to comply with the new lease accounting standards, you’ll find LeaseQuery offers the premium solution.

Trust us, there’s a difference. We understand the challenges faced not just by real estate and equipment leasing professionals, but also the accounting departments supporting both groups. Our lease management software reflects our expertise.

Talk to One of Our Expert Lease Accountants Today.

Unlike other software vendors, we aren’t former real estate professionals trying to learn accounting on your dime. We’re accountants just like you.

Sign up for a demo today and let us prove it.

Request A Demo