How to Calculate the Present Value (PV) of Future Lease Payments in Excel

by | May 2, 2023

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 obligation recorded on the balance sheet for both operating leases and finance leases. This recognition provides more visibility of lease obligations to the users of the financial statements.

The calculation is performed using the terms and payments specified in the lease and a rate of return, or interest rate, 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 right-of-use (ROU) asset.

PV (Present Value) vs. NPV (Net Present Value)

Accountants occasionally use the terms present value and net present value interchangeably, but they have distinct meanings. PV, or present value, is used to calculate today’s value of future payments or receipts, but not combined payments and receipts. In lease accounting, we use present value to establish the assets or liabilities related to lease obligations or lease receivables.

Net present value, or NPV, is commonly used in capital budgeting decisions and other types of financial analyses as a way to determine the benefit of investing in a particular capital asset. In this usage, “net” means the calculation is using both inflows and outflows of cash. A potential investor may use this calculation to analyze the value of combined payments and receipts to understand what the cumulative profit or loss of an investment will be over time.

Minimum lease payments and future lease payments

Under the new lease accounting standards, how we calculate the present value of lease payments has not changed. What has changed, however, is that under ASC 842, IFRS 16, and GASB 87, the calculation of the present value of lease payments is required for all in-scope leases. Lessees reporting under IFRS 16 or GASB 87 will only have finance leases upon transition and will continue to discount the future lease payments for these types of leases to their present value. ASC 842, however, continues to distinguish between operating and finance leases but requires obligations for both to be recorded on the balance sheet.

Therefore, under ASC 842, lease payments for both operating and finance leases will need to be discounted to their present value. Furthermore, the definition of lease payments under ASC 842  changed slightly from the definition of minimum lease payments under ASC 840.

Minimum lease payments

Under the legacy lease accounting standard, ASC 840, the FASB required lessees to establish a lease liability and lease asset for all leases meeting the criteria for a capital lease. For leases classified as capital, lessees performed a calculation to determine the present value of the minimum lease payments which was used as the basis for the capital lease asset and liability values. Within ASC 840-10-25-6, the standard defines minimum lease payments as the financial obligations a lessee must make in connection with the leased asset.

Future lease payments

Under ASC 842, lessees are required to establish a lease liability and ROU asset for both operating and finance leases (previously capital leases). Lessees perform a present value calculation on future lease payments to determine the initial lease liability recorded on their balance sheet.

Future lease payments are defined in ASC 842-10-30-5 as payments that relate to the use of the underlying asset during the lease term. These payments include:

  • Fixed payments required by the lease agreement, such as base rent
  • In-substance fixed payments required by the lease agreement. (In-substance fixed payments are payments that may appear to be variable, but are, in effect, fixed.)
  • Variable lease payments that depend on an index or rate, initially measured using the index or rate at the lease commencement date
  • The exercise price of a purchase option if the lessee is reasonably certain to exercise that option
  • Penalties for terminating the lease if the lease term reflects the lessee exercising the option to terminate the lease
  • Fees paid by the lessee to the owners of a special purpose entity for structuring the transaction
  • Amounts probable of being owed by the lessee as the result of a residual value guarantee

Future lease payments are reduced by incentives paid to or payable to the lessee and exclude amounts allocated to non-lease components, any guarantee of the lessor’s debt by the lessee, and variable lease payments, other than those specified above.

Read our article Lease Payments: Establishing the Initial Lease Liability and ROU Asset under ASC 842 for more details on what is considered a lease payment and how to extract that information from your lease agreement.

How to calculate the present value of a payment stream using Excel in 5 steps

As discussed above, 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, to comply with the new lease standards, you will need to know how to calculate the present value of the lease payments. This is especially true if you are not using software and prefer to use Excel spreadsheets to manage your leases.

This article will address how to calculate the present value of the lease payments using Excel. While we believe accounting for your leases in Excel leaves too much room for error, if you prefer Excel, we can at least help you use it correctly. LeaseQuery also offers a free Present Value Calculator to perform this calculation for you.

Get the free Present Value Calculator to follow along:

Present Value Calculator

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:

Excel table with period, cash, and present value

Step 2: Enter amounts in the Period and Cash columns

Enter the number of payment periods in the Period column. The period represents the length of time over which interest is accrued, typically a month, quarter, or year.  In this example, we are calculating the present value of ten years/periods of payments due at the beginning of the period, so the periods are numbered 0 to 9. Note, if payments were made in arrears, the numbering would be 1 to 10.

Next, enter the cash payment amount for each period in the Cash column. Let’s assume this lease is written as annual payments of $1,000, due at the beginning of the year, increasing 5% annually. See the below illustration:

Excel table with period and cash in columns

Step 3: Insert the PV function

In the first row of the Present Value column, click on the “insert function” button. From the dialogue box that pops up, select “financial” in the dropdown, then scroll down and select “PV”.

Inserting the PV function in Excel

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 needed for Excel to perform the calculation. Enter 6% as the discount rate we are using in this example. In the Nper field, 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 (though 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 the formula down the column to the last period listed.

Rate, Npr, Pmt, and Fv fields in Excel

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 10 annual payments of $1,000 with 5% escalations and an interest rate of 6%, or $9,586.

Sum of the present value column in Excel

This example shows one way to calculate the present value of lease payments using Excel.

Present value calculator

If this seems like too many steps, we have created a free, downloadable present value calculator in Excel that performs this calculation for you. To use the tool, populate the items in dark blue at the top of the template and select values for the drop-down records:

  1. Enter the number of periods
  2. Enter the discount rate
  3. Enter the payments
  4. Indicate whether the periods are monthly, quarterly, or annual
  5. Specify if the payments are made at the beginning of the period or the end of the period

The tool will then calculate the present value for you automatically. (See the image of the template below with inputs from this example).

Present Value Calculator

If you are using Excel to calculate both the present value of lease payments and the lease liability amortization schedule, read our additional blog that illustrates how to calculate the present value of lease payments and generate the lease amortization schedule in one step with Excel. With this method, you will have the basic calculations needed to comply with the new lease accounting standards.

Summary

Knowing how to calculate the present value of lease payments is necessary to comply with the new lease accounting rules. This calculation is required to record lease liabilities and the related asset balances on the balance sheet, and all entities required to comply with any of the new lease accounting standards need to accurately perform the calculation of the present value of the future lease payments.

ASC 842 Lease Accounting Guide

Present Value Calculator