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:
In the new excel spreadsheet and name the five columns:
Each row will include the date of the payment and the payment amount for the life of the lease.
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.
The lease payment amount, less the interest occurred is the amount that the lease liability balance will be reduced.
This formula is the previous balance less the liability reduction amount to give the closing balance of the lease liability after the payment.
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.
Ensure the balance unwinds to zero. If it does, you have calculated the lease liability using goal seek.
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.