Lease Liability Amortization Schedule: How to Calculate It in Excel

by | Nov 18, 2019 | 22 comments

Previously, we covered how to calculate the present value of lease payments using Excel spreadsheets. In this article, we will demonstrate how to calculate the present value of your lease payments as well as prepare the liability amortization schedule for the lease liability in the same step, using Excel.

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.ASC 842 and IFRS 16 Lease Accounting Guide

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:

Present Value Calculator

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:

Create Excel Spreadsheet with Five Columns

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:

Enter Number Periods and Cash Payments

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.

Enter Expense Formula

Step 4: Fill the expense column

Copy the formula for expense in period 1 down for the remaining Expense rows.

Fill Expense Column

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.

Enter Formula for Liability ReductionCopy 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).

Enter Excel formula for Liability Balance

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.

Fill Liability Balance Columns

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.”

What-If Analysis in Excel

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.

Goal Seek in Excel

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.

Lease Amortization Schedule

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.

Related articles

Present Value Calculator

22 Comments

  1. Teresa Morales

    Hi, shoul I add the prepaid rent to the begining of the ROU asset calculation?

    Reply
  2. Joshua Osikha

    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

    Reply
  3. Jay

    Hi, will this amortization schedule be the same for calculating lease liability with monthly lease payment and with 5% escalation every year?

    Reply
    • Kiley Arnold

      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!

      Reply
  4. Guillermo Birmingham

    What would the formulas be if one needed to take into account at transition, a reduction of the deferred rent balance on the books?

    Reply
  5. Karen Lotito

    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

    Reply
    • Kiley Arnold

      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!

      Reply
  6. Xinh

    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

    Reply
    • Kiley Arnold

      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

      Reply
  7. jL

    Are free rents (incentives) included to calculate initial PV of lease liabilities and ROU asset and subsequently subtracted from the calculated amounts?

    Reply
    • Kiley Arnold

      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.

      Reply
  8. Nicole

    Does it mean that the final payment is not required to include in the lease schedule?

    Reply
    • Kiley Arnold

      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

      Reply
  9. Samuel

    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 ?

    Reply
    • Kiley Arnold

      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

      Reply
  10. Rady

    How to use the goal seek to calculate the incremental borrowing rate in ifrs 16

    Reply
    • Kiley Arnold

      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

      Reply
  11. Myint Myo Thwe

    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

    Reply
    • David Buchanan

      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.

      Reply

Submit a Comment

Your email address will not be published.

Please complete the equation below: *