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.