Present value of lease payments explained
Present value, commonly referred to as PV, is the calculation of what a future sum of money or stream of cash flows is worth today given a specified rate of return over a specified period of time.
Under the new lease accounting standards, lessees are required to calculate the present value of any future lease payments to determine the obligations to be recorded on the balance sheet for both operating and finance leases. The calculation is performed using the term and payments specified in the lease and a rate of return that is specific to either the lease or the organization. The present value of the lease payments is used to establish both a lease liability and a (ROU) asset.
How to calculate the present value of a payment stream using Excel in 5 steps
Under the new lease accounting standards, lease capitalization is required for the vast majority of leases. The capitalized balance is calculated as the present value of the lease payments. Therefore, to comply with the new lease standards, you will need to know how to calculate the present value of lease payments.
If you prefer Excel, we can at least help you use it correctly. Here are your two 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 article will address how to calculate the present value of the lease payments using Excel.
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 amounts in the Period and Cash columns
Enter the number of payment periods in the Period column. In this example we are calculating the present value of ten periods of payments due at the beginning of the period, so periods are numbered 0 to 9. Note that if payments were made in arrears, the numbering would start from 1 to 10.
Next, enter the cash payment amounts for each period in the Cash column. This example starts with payments of $1,000, increasing 5% annually. 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”.
Step 4: Enter the Rate, Nper Pmt and Fv
After you click OK, another dialogue box will pop up into which you will insert the function arguments for Excel to perform the calculation. Enter 6% as the discount rate we are using in this example. In the Nper box, enter the cell reference for the first period. Enter 0 for Pmt, and in the field for Fv enter the cell reference for the first cash payment amount. Select type as 0 (frankly, it doesn’t matter if you select 0 or 1 here because we are discounting via the period column). Once the formula dialogue box is completed, click ok for the formula to populate the first row in the Present Value column. Copy that formula all the way down.
Step 5: Sum the Present Value column
Once you have calculated the present value of each periodic payment separately, sum the values in the Present Value column. This sum equals the present value of a 10-year lease with annual payments of $1,000, 5% escalations and a rate inherent in the lease of 6%, or $9,586.
There you have it, a way to calculate the present value of lease payments using Excel.