How to Calculate the Present Value of Lease Payments – Excel

Last Updated on December 27, 2022 by Morgan Beard

What is the Present Value of Lease Payments?

Present value (also referred to as PV) of lease payments, is a financial calculation that measures the worth of a future sum of money. A future sum of money being a stream of payments given a specified return rate over a given time, according to My Accounting Course. With our Occupier present value calculator excel template, you can start working through your amortization schedules.

Lessees are required to calculate the present value of any future lease payments and record those financial obligations on the balance sheet for both finance and operating leases. The present value calculation defines the lease liability for a given lease. 

In its essence, present value states that a dollar today does not have the same worth as a dollar tomorrow. In fact, the present value is typically less than the future value. So, when determining the lease liability and ROU asset, the future lease cash flows must undergo the present value calculation. 

How Each Standard Explains Present Value

The present value calculation has not changed from ASC 840 to ASC 842. Here is an overview of how the new standards define present value of a lease:

Under both standards, lessees record, regardless of the lease classification, a right-of-use asset and lease liability at the lease commencement date. The initial right-of-use asset and lease liability is measured based on the present value of the lease payments (as defined in the standards) using the interest rate implicit in the lease (unless the rate cannot be readily determined, in which case the incremental borrowing rate of the lessee will be used).

14.1.3.1 Lessee accounting – Balance sheet (ASC 842 and IFRS 16)

How to Calculate the Present Value

The Present Value Formula – An Equation

• PV = Present Value
• CF = Future Cash Flow
• r = Discount Rate
• t = Number of Years

Inputs

In order to calculate the present value of lease payments, judgements will need to be made on the following inputs when calculating the present value:

• Discount Rate  – the rate implicit in the lease whenever that rate is readily determinable. Read more here on How to Determine the Discount Rate Under ASC 842
• Lease Payments – The future cash flows (ie. lease payments) are necessary so that these payments can be present valued

The Present Value Calculator – Excel Template

We have found relying on Excel for lease accounting calculations leaves room for human error. For those who prefer Excel, our spreadsheet will help you with accurate present value calculations.

Check out our Present Value Calculator Excel Template here:

Step 1:
After downloading our Present Value Calculator Template above, you’ll find that the excel headers and formulas are already created for you. If you haven’t done so already, download the Excel File linked in the image above. 

Step 2:
Now you can input your lease data. That includes the annual discount rate, the periodic discount rate, the periodic payments. Lastly, you’ll need to indicate if the payments are made at the beginning or the end of the month. In our example below, the inputs are as follows:

• Annual Discount Rate: 5.50%
• Period Discount Rate: .46%
• Periodic Payments: Monthly
• Payments Made: Beginning of the month

Step 3:
If you downloaded the spreadsheet above, you’ll see that the file is preset with a particular example. We are assuming a 5 year term with $100.000 monthly lease payments all made at the beginning of the month. For your purposes, you can go ahead and update the periods with your lease term and the monthly lease payment price. 

Looking for additional Present Value Calculator support? Then book some time with Lauren Covell, Certified Public Account and the VP of Finance here at Occupier. She created this file and enjoys discussing lease accounting with folks beginning their ASC 842 transition.