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