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.