Using Excel to Calculate Present Value of Minimum Lease Payments

NPV for excel

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. 

To continue reading, 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.

2013 Lease Exposure Draft: Type A Leases

In this post I will explain Type A leases in detail from the lessee perspective. We will discuss how to calculate the initial and subsequent values of the lease liability and the initial and subsequent values of the Right of Use (ROU) Asset. In a later blog we will discuss how lessees will address the transition from leases that are classified as operating leases under current GAAP to Type A leases under the lease exposure draft.

Examples of Type A Leases
Examples of Type A Leases

Recall that in general, leases of personal property (for instance, vehicles and equipment) are Type A leases, while leases of real property (land and buildings) are generally Type B leases. The best way to explain how the lease liability and right of use (ROU) asset are determined is by using an example. So here goes:

Example 1: Entity A (Lessee) enters into a 10 year lease of equipment with payments of $10,000/yr in years 1 through 5, and $15,000/yr in years 6 through 10. Assume that the lessee’s incremental borrowing rate is 6%, and payments are made in advance. Assume the useful life of the equipment is 20 years, and the fair value is $150,000.
Analysis of Example 1: The first thing to notice here is that the underlying asset in this lease is Equipment, which is personal property. Recall from our previous post that leases of personal property are classified as Type A leases unless:

1) the lease term is insignificant compared to the total economic life of the asset, or
2) the present value of the minimum lease payments is insignificant compared to the fair value of underlying asset.

The lease term is 50% of the useful life of the asset. This is not insignificant. The present value of the minimum lease payments is $94,700. (This is how the present value of the mimimum lease payments was calculated). This is over 60% of the fair value of the equipment, which is not an insignificant amount.
Because the exceptions in 1) and 2) above are not met, this is a Type A lease.

On the lease commencement date (not the execution date), company would record the ROU asset and the Lease Liability. The entry would be a debit to the ROU asset and a credit to the lease liability for the present value of the minimum lease payments, as follows:

Dr. ROU Asset 94,700
Cr. Lease Liability 94,700
To record ROU asset and Lease liability at commencement.

The ROU asset would be depreciated straight-line, so each year the following entry will be made:

Dr. Depreciation Expense 9,470
Cr. Accumulated Depreciation ROU Asset 9,470
To record amortization of ROU asset at year end.

The Lease Liability would be amortized using the effective interest method according to the following table:

 

Period Cash Expense Liab Reduction Liab Balance

0

94,700

1

10,000

10,000

84,700

2

10,000

5,082

4,918

79,782

3

10,000

4,787

5,213

74,569

4

10,000

4,474

5,526

69,043

5

10,000

4,143

5,857

63,185

6

15,000

3,791

11,209

51,977

7

15,000

3,119

11,881

40,095

8

15,000

2,406

12,594

27,501

9

15,000

1,650

13,350

14,151

10

15,000

849

14,151

The cash column represents the cash paid, the expense is the interest rate times the previous month’s liability balance, the liability reduction is the difference between the cash and the expense, while the liability balance is the difference between the previous month’s liability balance and the liability reduction.

Based on the table above, the following entry would be made to represent the first month’s payment:

Dr. Lease Liability      10,000
Cr. Cash                 10,000
To record first lease payment.

Note that the first payment has no interest expense recorded. This is because interest is a function of time, and if a payment is made at the beginning of the lease term then no time has passed for interest to accrue. As such, the entire payment goes against the principal. The entry to record the second lease payment will be as follows:

Dr. Interest Expense 5,082
Dr. Lease Liability 4,918
Cr. Cash 10,000
To record second lease payment.

Notice that after year 10, the lease liability will be at zero, and the lease asset would be fully amortized. Also notice that the expense associated with the lease hits the income statement twice; once as amortization (depreciation expense) of the ROU asset, and another as interest expense from the lease liability. As a result, rather than having rent expense evenly recorded through the lease term (as is currently the case with operating leases), the expense is “front loaded,” meaning that there is greater expense recorded in the earlier years of the lease term than in the latter years.

Now that we have covered Type A leases, I will move on to explain Type B leases in a separate post. I will also cover transition guidance from current GAAP to Type A leases in another post.

About LeaseQuery:

LeaseQuery, LLC provides a cloud-based, lease accounting software and lease management system which enables companies who lease real estate and/or equipment to easily comply with lease accounting guidelines. Visit us at www.LeaseQuery.com

Excel Template to Calculate Present Value of Minimum Lease Payments

This is free excel tool you can use to calculate the present value of future minimum lease payments. It is a template that works for periods up to 120 years. To calculate using your own numbers, change the values in the cells highlighted in yellow. The file should be self-explanatory, but if you want instructions they are included below. Click the following link to download: Calculate Present Value of Minimum Lease Payments in Excel.

Here are some instructions on using the file:

To use this template, do the following:

1)      Enter the number of periods.

2)      Enter the annual rate.

3)      From the dropdown, select “Months” if your period is in months, or “Years” if in years.

4)      Select “Beginning” if your payments are made at the beginning of the period, or “End” if your payments are made at the end.

5)      In the Cash column (highlighted in yellow), enter the cash payments.

The present value of the minimum lease payments will be displayed at the bottom.

How to use Excel to Calculate the Present Value of Minimum Lease Payments when the payment amount changes each period

This post by George Azih explains how to use excel to calculate the present value of minimum lease payments when each payment is different. To see how to use excel to calculate the present value of lease payments when the payment amounts do not change, click here.

Example:

Let’s calculate the present value of the minimum lease payments for a lease with a lease term of 24 months. The lease payment starts at 5,000/month, and increases by 1,000 every 6 months. Payments are made at the beginning of the month. The Lessee’s borrowing rate is 6%.

The following embedded excel file shows you how to do this easily. (You can actually see my formulas):

From the file above (scroll down in the embedded file), you can see that the present value is  $146,543.92.

Here is a manual explanation:  Perform the following steps:

1)      Create a table with three Headers, namely, “Period”, “Payment” and “Present Value” as follows:

Period

Payment

Present Value

 

2)      In each row under “Period” put in the number of Periods. Start with “1” if payments are made at the end of the month, and end at the actual number of periods. If payments are made at the beginning of the month, start with “0”, and end at the actual number of periods minus 1. As a result, our Table now looks like this:

 

Period

Payment

Present Value

0

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

Total Present Value

 

Note that in this example, because the payments are made at the beginning of the month, the “Period” column begins with 0 and ends with 23. If payments were made at the end of the month, it would have begun with 1 and ended with 24.

 

3)      In each row under “Payment,” put the payment for that month. Remember that our payment starts at $5,000/month, and every 6 months the monthly payment increases by $1,000. So our table now looks like this:

 

Period

Payment

Present Value

0

5,000

1

5,000

2

5,000

3

5,000

4

5,000

5

5,000

6

6,000

7

6,000

8

6,000

9

6,000

10

6,000

11

6,000

12

7,000

13

7,000

14

7,000

15

7,000

16

7,000

17

7,000

18

8,000

19

8,000

20

8,000

21

8,000

22

8,000

23

8,000

Total Present Value

 

To get the values for “Present Value,” we have to use the PV function in excel. We will put the formula in for the first cell (period 0), then drag the formula down. Go to the first cell under “Present Value” and click the function button.  Choose “PV,” (It should be under financial):

When you click “OK, you should see the following:

Enter the following:

Rate = 6%/12 = 0.5% = 0.005 (in excel, you can either enter 0.5% or 0.005 without the percentage sign)

Nper = (Select the corresponding “Period cell” for that Present Value. So if your first “Present Value” cell is “C2,” then  Nper should be “A2”)

Pmt = 0

FV = (Select the negative of the corresponding  “Payment cell”  for that Present Value. So if your first “Present Value” cell is “C2,” then Fv should be “-B2”)

Type = 0

Your screen should look like this:

Using this information, excel should give us a value of $5,000 for the first “Present Value”.

Now copy the formula down for the rest of the “Present Values,” and for the “Total Present Value,” add all the values in the “Present Values” column. Your table should now look like this:

Period

Payment

Present Value

0

5,000

5,000.00

1

5,000

4,975.12

2

5,000

4,950.37

3

5,000

4,925.74

4

5,000

4,901.24

5

5,000

4,876.85

6

6,000

5,823.11

7

6,000

5,794.14

8

6,000

5,765.31

9

6,000

5,736.63

10

6,000

5,708.09

11

6,000

5,679.69

12

7,000

6,593.34

13

7,000

6,560.53

14

7,000

6,527.90

15

7,000

6,495.42

16

7,000

6,463.10

17

7,000

6,430.95

18

8,000

7,313.09

19

8,000

7,276.71

20

8,000

7,240.50

21

8,000

7,204.48

22

8,000

7,168.64

23

8,000

7,132.97

Total Present Value

146,543.92

 From the table, we can see that the total present value of the minimum lease payments is 146,543.92.

How to use Excel to Calculate the Present Value of Minimum Lease Payments when the payment amount does not change.

Reader Request: This post explains how to use excel to calculate the present value of minimum lease payments when each payment is equal.  To learn how to calculate the present value of lease payments when payments change each period, click here. To calculate the present value for lease payments when the payment amounts do not change, perform the following steps:

Note: This method only works when every lease payment amount is the same each period. To see how to calculate the present value of minimum lease payments when one or more payments are different (or when there are some months with free rent), click here.

Go to a cell in excel, and click the function button.  Choose “PV,” (It should be under financial):

When you click “OK, you should see the following:

Let “Rate” be your interest rate (this is usually the interest rate which the company would get charged if the company went to a bank to borrow the money to buy the asset instead of leasing it). Please note that this rate depends on your payment frequency. If the payments are made monthly, then you would have to divide your annual rate by 12.

NPer is the number of payment periods. So if the Payments are made monthly and the lease is over five years, then the number of NPer is 60.

Pmt is simply your payment amount.

Fv should be zero.

Type should be “1” if payments are made at the beginning of the period, or 0 if payments are made at the end of the period.

Example:

Assume we have a 10 year lease with monthly payments of $2,500/month, paid in advance (at the beginning of each month). Assume that the lessee’s borrowing rate is 6%. To use excel to calculate this:

Rate = 6%/12 = 0.5% = 0.005 (in excel, you can either enter 0.5% or 0.005 without the percentage sign)

Nper = 120

Pmt = 2500

FV = 0

Type = 1 (Because payments are made in advance, at the beginning of each month).

Using this information, excel should give us a present value of $226,309.55 for the lease.