2. How to create the lease amortization schedule and calculate your lease liability
- Create five-column spreadsheet
- Enter the number of periods and cash payments
- Enter expense formula
- Fill expense column
- Enter liability reduction formula
- Enter liability balance formula
- Fill remaining liability balance
- Perform “What-If Analysis” on liability balance
- Set liability balance value to 0 with goal seek
- Click “OK”
Private companies in particular may be tempted to try to use an Excel spreadsheet for lease accounting, but this information is important even if you plan to use lease accounting software for compliance with the new standard. You can use the information in this blog to ensure that your chosen software provider is performing this calculation accurately. Transitioning to ASC 842, IFRS 16, and GASB 87 can be difficult, but there are resources that can help you gain an understanding of the methods laid out below for our calculations.
What is the lease liability?
The lease liability is defined as the present value of your future lease payments. This is calculated as the initial step in accounting for a lease under ASC 842, and this amount is then used to calculate the ROU (right-of-use) asset, that is recorded in addition to the liability for operating leases and capital leases.
A lessee’s obligation to make the lease payments arising from a lease, measured on a discounted basis.
How to create the lease amortization schedule and calculate your lease liability
Download our free present value calculator to follow along:
Follow the steps below to calculate the present value of lease payments and the lease liability amortization schedule using Excel when the payment amounts are not constant, illustrated with 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%.
Step 1: Create an Excel spreadsheet with these five columns
Create a new Excel spreadsheet and title five columns with the following headers: Period, Cash, Expense, Liability Reduction, and Liability Balance, as shown below:
Step 2: Enter the number of periods and cash payments
Enter the number of periods corresponding to the lease term starting from 0, 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. The annual payments then escalate at a 5% rate. Please see illustration below:
Step 3: Enter the expense formula
Enter “0” for expense in period 0 (because payments are made in advance). In Expense for period 1, enter the cell reference for the period 0 liability balance and multiply by 6%. See below.
Step 4: Fill the expense column
Copy the formula for expense in period 1 down for the remaining Expense rows.
Step 5: Enter the formula for liability reduction
The formula for each liability reduction amount is the corresponding cash payment minus the corresponding expense. See below.
Copy the formula down the entire Liability Reduction column.
Step 6: Enter the formula for liability balance
Enter “0” for the Liability Balance in the line above period 0. In liability balance for period 0, enter the formula for the above cell’s liability balance minus the liability reduction in period 0. This will equal the previous period’s liability balance, reduced by the current liability reduction (see below).
Step 7: Fill the remaining liability balance column
Copy the formula for the liability balance in period 0 down for the remaining Liability Balance rows.
Step 8: Perform “What-If Analysis” on the liability balance
Select the liability balance for period 9. In the top bar in Excel, go to the “Data” tab, then the “What-if Analysis” Tab, then select “Goal Seek.”
Step 9: Set liability balance value to 0 by using goal seek
In the dialog box that follows, make sure “Set cell” is set to the cell representing the liability balance for period 9, in the “To Value” enter 0, and in “By changing cell” enter the cell reference representing the liability balance for the period above period 0. See below.
Step 10: Click “OK”
Click “OK” to have Excel run the goal seek analysis. Excel will provide the beginning liability balance and your amortization schedule will be completed automatically as a result of the formulas you input. See below.
For this example, the present value of a 10-year lease with payments of $1,000 annually, 5% escalations, and a rate inherent in the lease of 6% is $9,586.
Summary
This schedule will provide you with the calculations for your journal entries for the entire life of the lease, if you’re using Excel. If you’re using a lease accounting software, the information above will help you cross-check the calculations performed by your provider so you can ensure accuracy.
Hi, shoul I add the prepaid rent to the begining of the ROU asset calculation?
Hi Teresa. You should be able to find answers in our article that covers prepaid rent: https://leasequery.com/blog/prepaid-rent-other-rent-accounting-under-asc-842-explained/
Please I need clarity on Capital lease in the book of the lessee.
I am of the opinion that the lessee should recognize the lease item as a fixed asset and amortized accordingly
Hi Joshua,
You can find the answers to your question in this blog: https://leasequery.com/blog/capital-lease-accounting-finance-lease-accounting-example/
Thanks for your interest!
Hi, will this amortization schedule be the same for calculating lease liability with monthly lease payment and with 5% escalation every year?
Hi Jay –
Great question! Yes, this is the basic lease amortization schedule that can be adjusted for rent escalations, de-escalations, rent holidays, etc. When entering the payment information, just factor in the 5% escalation, or whatever payment terms are specified in your agreement. Thanks for reaching out!
What would the formulas be if one needed to take into account at transition, a reduction of the deferred rent balance on the books?
Hi Guillermo,
We have an article that answers this exact question: Operating Lease Accounting under the New Standard, ASC 842: Full Example and Explanation. Check it out!
Hello, I am trying to replicate the schedule using your instructions and not having success.
In step three, I used 6% all the way down. In following steps there is no expense reflected. Then in step 7, expense is negative and appears to be increasing incrementally by 6%.
Can you please explain so I can use this to prepare amortization schedules.
thanks so much!
Karen
Hi Karen,
Sure, I can help. This article is demonstrating how to calculate a beginning lease liability using Excel functions. We are calculating the present value of the payments with the facts presented and completing the amortization table for that present value together.
In Step 3 you are completing the expense column. Expense for each period is calculated as the annual interest rate times the prior period’s ending balance. You only accrue interest/lease expense on the unpaid liability balance. No expense will be reflected in the steps until we populate the liability balance columnn with the liability balance amount.
In Step 7 we populated the liability balance column with the formula of the prior period end liability balance less the calculated liability reduction. The expense account is negative because it is computing expense as the liability balance (which is negative until we populate a beginning liability balance) times the interest rate.
If you complete through Step 7 and then use the goal seek function described in Step 8, you calculate a beginning liability balance of $9,585.98. When this amount (or any amount) is placed in the beginning liability balance cell, the liability balance will be positive and be reduced by the caclulated liability reduction column amount until it goes to $0. With a positive liability balance amount, the expense will be positive as well.
You can test this by calculating the present value of 10 years of annual lease payments of $1,000 with 5% escalations annually, paid in advance. Assume the rate inherent in the lease is 6%. If you input this present value amount into the beginning liability balance cell and put the same forumlas in each of the columns, you will also create an amortization table with an ending liabilty balance as $0.
Thanks for reaching out!
Hi,
Please help me for this question: In case the lessee purchase the asset at the end of the lease term, paying $1000 to the lessor, should present value of this amount be included in calculating lease liability?
Thanks in advance,
Xinhpt
Hi Xinhpt,
When the lease agreement includes an option for the lessee to purchase the underlying asset and the lessee is reasonably certain they will exercise that option at the end of the lease term, the lessee should include the stated (or estimated) purchase price in their initial lease liability calculation under most lease accounting guidance, including ASC 840, ASC 842, IFRS 16 and GASB 87.
Best Regards,
LeaseQuery
Are free rents (incentives) included to calculate initial PV of lease liabilities and ROU asset and subsequently subtracted from the calculated amounts?
Hi JL,
Thanks for the question. Free rent, or rent abatement, is not included in the initial lease liability or ROU asset calculation. To calculate the lease liability you take the present value of all required lease payments over the term of the lease – for the periods of free rent, those payments are not required so you would not include the payment amounts in the PV calculation but you would include those periods as part of your lease term. Please check out our article on Rent Abatement for a full example.
If you have a true incentive – cash payments received from the lessor to encourage signing of the lease – those cash payments are included in the calculation of the lease liability. Incentive payments received at or before the commencement of the lease reduce the total amount of consideration of the lease. Incentive payments payable at lease commencement reduce the lease payment(s) in the month they are due.
Please check back soon, as we have an article on incentives that will be published in a couple of weeks.
Does it mean that the final payment is not required to include in the lease schedule?
Hi Nicole,
All payments are required to be used to calculate your lease liablity and would also be included in any related amortization schedules.
Thanks,
Kiley Arnold
We have paid a fixed amount of payment for our leases without any additional cost. My question is how do we get the incremental interest rate when we are calculating the PV ?
Hi Samuel,
Thanks for reaching out! ASC 842 and IFRS 16 define the incremental borrowing rate similarily as the rate a bank would charge for obtaining a collateralized loan with like terms and dollar value to your lease. The calcuation for the incremental borrowing rate is quite complex and we suggest seeking assistance from your finance and/or treasury department(s) to determine the correct rate for your organization.
Regards,
Kiley
How to use the goal seek to calculate the incremental borrowing rate in ifrs 16
Hi Rady,
Thank you for taking the time to contact us. This article is demonstrating how to build an amortization table using the effective interest method in Excel. We show you how to use the goal seek tool in Excel, but this is only applicable after the rate inherent in the lease or the incremental borrowing rate is determined. The incremental borrowing rate and the inherent rate are used to present value the lease liability, and the effective interest rate is the actual interest paid taking into account the compounding of interest over time.
Best,
Kiley
Dear Sir,
My question is regarding interest expenses regarding a 10-years lease contract. We have made advance payment for two years (2019 & 2020).
Currently we have recorded Dr.Prepaid rent and Cr.Cash.
If we move to IFRS16, We will do Cr.Prepayment and Dr.ROU?
So, my question is when we need to start record interest expenses?. My understanding is it shoud start record from 2021 because prepayment is up to 2020.
Please kindly reply me the correct recording.
Thank you in advance.
Best Regards,
Myint Myo
Mr. Myo,
Thank you for your question. Yes, as your question deals with the IFRS 16 standard you would, upon transition, credit prepaid rent and debit your ROU asset. Similarly, you would need to start recording interest expense upon transition. If you need any further clarification please let us know.