Using Excel to Calculate the Inital Lease Liability (and ROU Asset) and to create the Accretion Table

In this blog post, I’d like to show you how to create the amortization schedule for the Lease Liability using excel. As noted in a previous post, you are going to need to prepare this amortization schedule under both the straight-line approach (leases of land and buildings) and the accelerated approach (leases of all other types of assets). There are two ways to prepare the amortization schedule:

  • method 1, where you create the amortization schedule and calculate the present value of the minimum lease payments at the same time using the goal seek function in excel (in my opinon, this is the easier method) and
  • method 2, where you first calculate the present value of the minimum lease payments, then create the amortization schedule in two separate steps.

Here is the scenario we’ll use, a 10 year lease with annual payments made in arrears as follows: Year 1 – 10: 10,000; 10,500; 11,000; 11,500; 12,000; 12,500; 13,000; 13,500; 14,000; 14,500.

Let’s assume the interest rate inherent in the lease is 6%. (In a future post I will show how to calculate this rate).

Method 1:  The amortization schedule to show accretion of the lease liability will have five columns:

A

Period

B

Cash Due

C

Interest

D

Principal

E

Balance

 

For ease of identification, let’s call them columns A, B, C, D and E for now. The period column (column A) simply identifies the year, and Column B shows the cash payments. As such, our amortization table for the lease liability looks like this so far:

A

Period

B

Cash Due

C

Interest

D

Principal

E

Balance

0

-

1

10,000

2

10,500

3

11,000

4

11,500

5

12,000

6

12,500

7

13,000

8

13,500

9

14,000

10

14,500

 

In period 0, put zeros under Columns B, C, D and E.  Column C (interest) is simply the rate inherent in the lease (6% in this example) times the balance at the end of the previous period (Column E of the previous period).  So in Period 1 Column C, put the following formula: “= 6%*(Period  0 Column E). In Period 2 Column C, put “=6%*(Period 1 Column E), etc.

Column D (Principal) is simply the difference between Column B and Column C, and Column E for each period is the difference between Column D for that period and Column E from the previous period. So basically, in Period 1 Column E, you would put in the following formula: “= (Period 0 Column E) – (Period 1 Column D).

Once we input all these formulas, believe it or not, our amortization table is complete. If done correctly, it should look like this:

A B C D E
Period Cash Interest Principal Balance
               -                -                -                -                  -
                1      10,000                -      10,000      (10,000)
                2      10,500          (600)      11,100      (21,100)
                3      11,000      (1,266)      12,266      (33,366)
                4      11,500      (2,002)      13,502      (46,868)
                5      12,000      (2,812)      14,812      (61,680)
                6      12,500      (3,701)      16,201      (77,881)
                7      13,000      (4,673)      17,673      (95,554)
                8      13,500      (5,733)      19,233    (114,787)
                9      14,000      (6,887)      20,887    (135,674)
              10      14,500      (8,140)      22,640    (158,315)

At the end of Period 10, the Lease Liability Balance (Column E) should be zero, so what we have to do is figure out what value at Period 0 Column E would give us a balance of Zero at Period 10 Column E. Luckily, excel has a function called Goal seek that enables us do exactly that, and it is effortless. All you do is select the cell in Period 10 Column E. Then, in your menu function, under “Data,” and then under “What if Analysis,” Select ‘Goal Seek.” A dialog box will pop up that looks like this:

Under “Set Cell”, select Period 10 Column E. In “To Value,” put Zero. In “By Changing Cell,” select Period 0 Column E. Once you hit ok, your amortization schedule will change as follows:

A B C D E
Period Cash Due Interest Principal Balance
               0                -                -                -         88,402
                1      10,000         5,304         4,696         83,706
                2      10,500         5,022         5,478         78,229
                3      11,000         4,694         6,306         71,922
                4      11,500         4,315         7,185         64,738
                5      12,000         3,884         8,116         56,622
                6      12,500         3,397         9,103         47,519
                7      13,000         2,851      10,149         37,370
                8      13,500         2,242      11,258         26,112
                9      14,000         1,567      12,433         13,679
              10      14,500            821      13,679                   0

So basically, at commencement of the lease, the lease liability (and Right-of-use Asset) is 88,402. After year 1, the lease liability will be 83,706, and at the end of year 2 it will be 78,229, etc. Note that the only time the lease liability and ROU Asset will be the same (in this example) is at the beginning and at the end of the lease. In the next post, I will show you the second method of calculating the initial lease liability.  To download an excel file that shows this amortization schedule already built, click Example-Amortization-Schedule.

This entry was posted in Lease Liability, ROU Asset and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>