There are a few ways in which the lease liability can be calculated and all of them follow the same principles. It involves discounting the future cash flows of a lease over the lease period.
Sounds simple enough if you have all the inputs to a lease, but actually performing this calculation in Excel can become confusing, considering that there are a few options available.
Below we will discuss the types of formula’s (specifically focused on Excel’s logic), what each formula does, how this is applicable for IFRS 16 lease accounting and then finally which formula logic we have implemented with the Rubli IFRS 16 lease accounting tool.
Type of Excel formulas
| Formula | Consideration |
|---|---|
| Excel “PV” Formula | The PV formula assumes that payments are equally spaced over the period of the lease and crucially the same amount. This is the simplest formula as it only requires a single amount, but can’t be applied to leases with escalations. |
| Excel “NPV” Formula | This formula assumes the same logic as the Excel PV logic (i.e., the payments are equally spaced), but the amounts can be different each period a payment is made. It therefore allows flexibility on payment changes like annual increases. However, the drawback here is that you can’t use a single amount to do the calculation – a separate table is required for the payments. |
| Excel “XNPV” Formula | This formula uses the specific date and corresponding amount that a payment will be made and then discount it to a present value based on the exact period it is in the future. This formula provides the most flexibility as it can handle any payment structure. However, when using Excel, it has the same drawback as the NPV formula as you still need a separate table that contains the payments. |
IFRS 16 application
Each of the formulas above will calculate the right present value for a lease to almost the same amount, however since there are months with different days and leap years with one more day, the Excel XNPV formula will ensure that the discount period for each payment is the most accurate representation of the lease liability to be calculated.
In most scenarios the restrictive nature of the PV formula means that the NPV of XNPV formula is required. For a large lease portfolio, the NPV/XNPV formulas becomes unwieldy and error prone when doing it in Excel as you need to create a payment table for each lease. And this is before you start dealing with modifications.
At Rubli, we follow a method similar to the XNPV approach which allows us to handle any payment structure, but without the burden of doing it all in Excel.
Furthermore, this allows the Rubli lease accounting tool to account for any changes, modifications, terminations, etc. on any day during a month with guaranteed accurate results.
Please get in touch to see how our flexible lease tool can assist you today.
