How to Calculate the Present Value of Lease Payments in Excel (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 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.

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

Accountants occasionally use the terms, present value and net present value interchangeably, but they do 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 over time will actually be.

Minimum lease payments and future lease payments

Under the new lease accounting standards, there is no change to how we calculate the present value of lease payments. What has changed, however, is that under ASC 842, IFRS 16, and GASB 87, the present value of lease payments calculation is required for all leases. Organizations reporting under IFRS 16 and 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.

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

(Video) Compute the Present Value of Minimum Future Lease Payments

Minimum lease payments

Under the legacy leasing standard, ASC 840, the FASB requires lessees to establish a lease liability and lease asset for all leases that meet the criteria for a capital lease. For leases classified as capital, lessees perform a calculation to determine the present value of minimum lease payments that is used as a basis for the capital lease asset and liability values. Within ASC 840-10-25-6, this standard defines minimum lease payments as the financial obligations that a lessee must make in connection with the leased asset. These payments specifically include:

  • Minimum rental payments required by the lessor over the lease term
  • Residual value guarantees
  • Penalties that the lessee incurs upon failure to renew or extend the lease
  • Payments made before the beginning of the term
  • Fees paid by the lessee to the owners of a special purpose entity for structuring the transaction

Contingent rentals and executory costs are not included in the minimum lease payments.

Future lease payments

Under the new leasing standard, ASC 842, lessees are required to establish a lease liability and a right-of-use asset for both operating and finance leases (previously capital leases). Lessees perform a present value calculation of the future lease payments to determine the initial lease liability recorded on the 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, unavoidable. Common examples of in-substance fixed payments are fixed common area maintenance charges, fixed tax payments, and fixed insurance payments.)
  • 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
  • For the lessee only, the 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 for more details on what specifically 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

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. This is especially true if you do not plan on using any software and would prefer to use Excel spreadsheets to manage your leases. However, we believe managing your leases in Excel leaves too much room for error.

(Video) How to Calculate a Lease Liability in Microsoft Excel

If you prefer Excel, we can at least help you use it correctly. Here are your two simple 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 article will address how to calculate the present value of the lease payments using Excel. We also built an Excel template that performs this calculation for you automatically.

Get the free Present Value Calculation Template to follow along

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:

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

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.

(Video) Leases IFRS 16 Schedule in Excel Automation

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:

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

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

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

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.

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

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.

(Video) How to Calculate Lease Payments

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

There you have it, a way to calculate the present value of lease payments using Excel.

Present value calculator

If that seems like too many steps, we have created a free, downloadable present value calculator in Excel that performs 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 period or at 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).

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

Additionally, if you are using Excel to calculate both the present value of lease payments and the lease liability amortization schedule, read our follow-up blog illustrating how to calculate the present value of lease payments and get the lease 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.

Summary

Knowing how to calculate the present value of lease payments in Excel is necessary to comply with the new lease accounting rules. This calculation is required to record lease liabilities and related asset balances on the balance sheet, to provide more visibility of lease obligations to the users of the financial statements. All entities that must comply with any of the new lease accounting rules need to be able to accurately perform the present value calculation of the future lease payments.

Related articles

Check out the following blogs to learn more about lease accounting calculations and how to perform them.

(Video) NPV in Excel and Operating Lease

FAQs

How do you calculate present value of lease payments? ›

Conclusively, the present value of the minimum lease payment is simply the sum of all of the lease payments that are to be made in the future, in today's dollar terms, added to the value of the estimated value of the leased asset once the lease is over.

How do you calculate present value using Excel? ›

Present value (PV) is the current value of an expected future stream of cash flow. Present value can be calculated relatively quickly using Microsoft Excel. The formula for calculating PV in Excel is =PV(rate, nper, pmt, [fv], [type]).

How do I calculate present value in Excel with different payments? ›

So the present value of multiple future cash flows is going to be the sum of the present values of

How do you calculate PMT manually? ›

The format of the PMT function is:
  1. =PMT(rate,nper,pv) correct for YEARLY payments.
  2. =PMT(rate/12,nper*12,pv) correct for MONTHLY payments.
  3. Payment = pv* apr/12*(1+apr/12)^(nper*12)/((1+apr/12)^(nper*12)-1)

When should I use PV in Excel? ›

The Excel PV function is a financial function that returns the present value of an investment. You can use the PV function to get the value in today's dollars of a series of future payments, assuming periodic, constant payments and a constant interest rate.

How do you calculate present value of monthly payments? ›

The formula for determining the present value of an annuity is PV = dollar amount of an individual annuity payment multiplied by P = PMT * [1 – [ (1 / 1+r)^n] / r] where: P = Present value of your annuity stream. PMT = Dollar amount of each payment. r = Discount or interest rate.

How do you calculate present value on a calculator? ›

Present Value Factor using any calculator - YouTube

What is lease payment example? ›

For example, let's say John Doe owns a house on Main Street. He does not live in the house; he decides to lease it to Jane Smith. John continues to be the owner of the house, but Jane agrees to pay John $800 a month in return for letting her live there for a year. The $800 is Jane's lease payment.

Why is PV in Excel negative? ›

In this example, the pmt argument is a negative number because we invest the money. If you calculate PV of an annuity that pays to you, then enter pmt as a positive number, and you'll get a negative PV as the result. The future value is not used in this calculation, therefore the fv argument is omitted.

How do you calculate present and absent in Excel? ›

Excel COUNTIF Function
  1. Select a cell.
  2. Type =COUNTIF.
  3. Double click the COUNTIF command.
  4. Select a range.
  5. Type ,
  6. Select a cell (the criteria, the value that you want to count)
  7. Hit enter.

How do you calculate present value of monthly payments? ›

The formula for determining the present value of an annuity is PV = dollar amount of an individual annuity payment multiplied by P = PMT * [1 – [ (1 / 1+r)^n] / r] where: P = Present value of your annuity stream. PMT = Dollar amount of each payment. r = Discount or interest rate.

What is NPV formula in Excel? ›

What is the NPV Function? The NPV Function[1] is an Excel Financial function that will calculate the Net Present Value (NPV) for a series of cash flows and a given discount rate. It is important to understand the Time Value of Money, which is a foundational building block of various Financial Valuation methods.

How do you find the present value of a residual? ›

The regular present value formula is CF / (1 + r)^t, where "CF" is the cash flow in year "t." To conclude the example, if the terminal year is five, the present value of the residual value is about $26,640 [$34,000 / (1 + 0.05)^5 = $34,000 / 1.05^5 = $26,640].

How do you calculate the present value of future payments? ›

To determine the present value of a future amount, you need two values: interest rate and duration.
...
Written by Rami Cohen
  1. Start with your interest rate, expressed as a fraction. So 5% is 0.05.
  2. Add 1 to the interest rate.
  3. Raise the result to the power of duration.
  4. Divide the amount by the result.

Videos

1. Present value - minimum lease payments E21-2
(Ed Kaplan)
2. Present value for lease
(Nader M)
3. Leases Present Value Calculations
(The Accounting PhD)
4. Lease accounting | Present value of lease payments
(Excel Clinic)
5. Excel 2010: Buy versus lease calculation
(Codible)
6. How to Calculate the Lease Liability | Lessee | IFRS 16
(Edspira)
Top Articles
Latest Posts
Article information

Author: Jonah Leffler

Last Updated: 02/17/2023

Views: 5542

Rating: 4.4 / 5 (45 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Jonah Leffler

Birthday: 1997-10-27

Address: 8987 Kieth Ports, Luettgenland, CT 54657-9808

Phone: +2611128251586

Job: Mining Supervisor

Hobby: Worldbuilding, Electronics, Amateur radio, Skiing, Cycling, Jogging, Taxidermy

Introduction: My name is Jonah Leffler, I am a determined, faithful, outstanding, inexpensive, cheerful, determined, smiling person who loves writing and wants to share my knowledge and understanding with you.