I'm building a model to capture and plan leases against the IFRS16 standard. I use a numbered list and have a form to capture start date, end date, initial payments, fixed payments and discount rate. The output is a report by lease showing Interest, Depreciation, Lease Liability. We need the function to modify the leases from a point in time - all calculations up to the modification date remain static, then the new calculations take over. There could be multiple modifications.
My first thought is to have a child list (also numbered) and module to capture the "New" inputs like New End Date, New Discount Rate, Impairment Value. I have built this and a copy of the calculation module based on the new inputs (for the child). The problem I'm having is combining the results of the Parent and child leases into one based on their modification dates.
Has anyone built a model for IFRS16, and can offer some tips on how to build the modifications function?
Great use case. Here's one idea for you. Hope I'm not oversimplifying.
"We need the function to modify the leases from a point in time - all calculations up to the modification date remain static, then the new calculations take over. There could be multiple modifications."
Set up two line items, one that has the fixed value and one that uses your calculation.
Add a third line item, boolean, that checks the current date, or period, against your lease.
The fourth line item checks the boolean and computes your result. IF [Lease is before Period] THEN [Static Value] ELSE [Calculated Value]
You could, of course, combine all that into one line item but it makes it hard to read and understand.
Essentially that's what's going on - except that you could have multiple leases end points, particularly for some long lease items. So you've got lease 1 with all the information that gets discounted down, replaced by another, replaced by another. Quite the painful modelling experience - which is why it's the only use case where i've thought easier to by an off the shelf solution.
But you have a master item and then multiple children with all the various data points that then gets rolled up accordingly.
Thanks for replying Jared. Andrew's correct in his reply where they all feed into one result - I'm currently trying to have a module with the parent lease by Time and feed the child values in based on their "effective date" but it's proving challenging!
Hi @joshuastockwell - worth reaching out to your BP on this. At a previous company we used a solution from Vuealta (@sean_culligan might be able to help here) that had all the relevant calculations, but I know that Anaplan themselves have a solution.