Our previous blog on this topic focused on one important thing: How to calculate the present value of lease payments using Excel spreadsheets. This week, we will show you how to calculate the present value of minimum lease payments AND prepare the liability amortization schedule for the lease liability in the same step, using Excel.
Let us stress that this information is important not just for companies that plan on continuing to use Excel spreadsheets for lease management. It is also a useful tool if you plan to use lease accounting and lease management software, as you can use the information in this blog to ensure that your chosen software provider is actually performing this calculation accurately.
Basically, with the method we explained below, you will have everything you need to comply with the new lease rules powered only by an Excel spreadsheet. Next week’s blog will feature a comprehensive example of how to transition from the current lease accounting rules to the new lease accounting standards, and we will be referencing the methods utilized in this blog for our calculations.
For now, here are the steps to follow to calculate the present value of lease payments AND the lease liability amortization schedule using Excel, when the payment amounts are different. Let’s use an example:
Calculate the present value of lease payments for a 10-year lease with annual payments of $1,000 with 5% escalations annually, paid in advance. Assume the rate inherent in the lease is 6%.
In an Excel spreadsheet, title five columns with the following headers: Period, Cash, Expense, Liability Reduction and Liability Balance, as shown below:
Enter the number periods starting from 0 to 9, and enter the cash payments in each period. Because payments are made in advance, the first payment of $1,000 is made in period 0. Please see illustration below:
Enter “0” for expense in period 0 and period 1 (because payments are made in advance). In expense for period 2, enter the cell for the period 1 liability balance and multiply by 6%. See below.
Copy the formula for expense in period 2 down for the remaining expense columns.
The formula for each liability reduction image is the corresponding cash minus the corresponding expense. See below.
Enter “0” for the liability balance in period 0. In liability balance for period 1, enter the cell for liability balance in period 0 minus the liability reduction in period 1. So it’s the previous liability balance, reduced by the current liability reduction (see below).
Copy the formula for liability in period 1 down for the remaining liability balance columns.
Select the liability balance for period 10. Go to the “Data” tab, then the “What-if Analysis” Tab, then select “Goal Seek.”
In the dialog box that follows, make sure the “Set cell” is set to the cell representing the liability balance for period 10, in the “To Value” enter 0, and in the “By Changing Cell” enter the cell representing the liability balance for period 0. See below.
After you click ok, Excel will prepare your amortization schedule for you automatically. See below.
Based on this, the present value of a 10-year lease with payments of $1,000 annually, 3% escalations and a rate inherent in the lease of 6% is $9,586.
Please note the following:
This is the exact same amount we calculated using the other method in last week’s blog.
Each month, your payment (the cash column) is split between expense (the expense column) and liability reduction (the liability reduction column). To illustrate, your total payment of $1,050 in period 2 is allocated as $515 in expense and $535 in liability reduction.
With this schedule you have everything you need to make your journal entries for the entire life of the lease.