How to Calculate a Lease Liability using Excel 

The lease liability is the present value of the known future lease payments at a point in time. A lease liability is required to be calculated for both ASC 842 & IFRS 16.

Refer below for seven steps on how to calculate the lease liability using excel’s goal seek. The lease liability we’re going to calculate is based on the following terms: 

  • 12-year lease term
  • $10,000 payments at the beginning of each year
  • Discount rate: 7%

Step 1 - Create a spreadsheet and set up columns

In the new excel spreadsheet and name the five columns:

  • Date
  • Lease payment
  • Interest
  • Liability reduction
  • Closing liability balance

Step 2 - Enter the payment amounts and the payment dates

Each row will include the date of the payment and the payment amount for the life of the lease. 

Step 3 - Calculate the interest on each payment

Calculate the interest incurred on each payment using the discount rate of 6%. For the first payment, there is no interest incurred as it’s made at the commencement of the lease. 

Step 4 - Calculate the reduction of the lease liability for each payment

The lease payment amount, less the interest occurred is the amount that the lease liability balance will be reduced.

Step 5 - Input the formula to calculate the closing balance of the lease liability

This formula is the previous balance less the liability reduction amount to give the closing balance of the lease liability after the payment. 

Step 6 - Calculate the opening balance of the lease liability using excel’s goal seek function

This will calculate the opening balance amount of the lease liability. From this amount, the lease liability will unwind to zero. To do this in excel, select Data > What-if Analysis > Goal Seek.

Step 7 - You’re done 

Ensure the balance unwinds to zero. If it does, you have calculated the lease liability using goal seek.

Conclusion

This is one method of calculating the lease liability. I would highly recommend reading How to Calculate the Present Value of Future Lease Payments to understand there are more and arguably more accurate methods when calculating the lease liability. 

No time for manual lease accounting calculations? Let Cradle do it