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, and 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 software, and plan on continuing to use excel spreadsheets to manage your leases. While we think managing your leases in excel is a bad idea (see our post about that here), if you choose to use excel, we can at least help you use it correctly. Here are your 2 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 will also give you an excel template that performs this calculation for you automatically. Next week, our blog 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 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, 3% 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 I 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).
Excel Template Present Value of Minimum Lease Payments
If you would like to get a free copy of this template, please email us at [email protected] and we will gladly send it to you.
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. 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.
If you liked this post, consider reading the following:
To get a free trial of our Lease Management Software, click here.
About LeaseQuery: LeaseQuery is lease management software that helps companies manage their leases. Rather than relying on excel spreadsheets, our clients use LeaseQuery to get alerts for critical dates (renewals, etc), calculate the straight-line amortization of rent and TI allowances per GAAP, provide the required monthly journal entries (for both capital and operating leases) and provide the commitment disclosure reports required in the notes and the MD&A. Contact us here.
Read more at: http://www.leasequery.com/blog/