Using Alteryx to perform Lease Accounting Calcs

[Nilsend]

Hi -

Has anyone used Alteryx to perform lease accounting ASC 842/ IFRS 16 calcs? 

[caltang]

Hmm, I guess your next question is regarding a use case right? Maybe it'll be better if you went direct to your use case question? I'm sure IFRS16 calcs can be done via Alteryx.

[Nilsend]

Yes looking to build out table that showcases ROU Asset, its balances, Lease liability,  amortization expense and interest expense  each month 

[caltang]

Do you have some sample data? I work a lot with IFRS requirements across finance and actuarial, so I know a thing or two even without deep domain knowledge. Maybe we can start with 1 use case?

[Nilsend]

see attached

[caltang]

What's your use case? Attaching a workflow alone is not enough context... What are you trying to do?

[Nilsend]

I am looking to build out formulas and output a amortization table in line with IFRS 16

[WeiLi]

i m late to the party here... but the answer is yes it can.

i am using it for IFRS 15 calculations. get ready to use a ton of macros and using a spare PC to run the workflow, unless your company paid for Alteryx Cloud.

i tried doing it with Python, which is just equally slow and likely takes longer to build out the program.

[apathetichell]

Hey I haven't looked at IFRS and/or Lease Accounting in eons - but I glanced through the attached workflow and it seemed reasonably solid for building an amortization table. Is there something you are missing here specific to IFRS 16? @Nilsend - what are your next steps - this looked like a good start?

[Calvin6KV]

Hey, kinda late to this discussion but I was looking for some materials to guide my juniors on lease computation so I thought I would just use this thread. I had a look at the attached workflow, and it seems like a very good start. I would just like to elaborate further on the steps to compute ROUA and LL:

1. Calculate PV of future cash flows:

Use the Formula tool to compute the PV using the FinancePV function, e.g. -FinancePV([Interest Rate], [No. of Payments], [Lease Payment], 0, 0).

***Do take note that the last "0" in formula should be "1" if the lease payments are made in the beginning of period instead of end of period.

2. Prepare amortisation table:

The standard formula for calculating LL = Opening balance - Lease payments + interest expense. The challenge here is that the opening balance and the interest expense for each period is dependent on the row above (i.e. previous period). For those familiar with for loops, this is what we need to do here. It is difficult to use a Multi-Row Formula tool to solve this, so the trick is to build a simple iterative macro that will repeat the formula above for each period up to the end of the lease term.

For ROUA, it will be simpler with the formula being = Opening balance - Depreciation, where Depreciation = PV / number of periods. 

3. Next steps:

Once the table is completed, simply use the Filter and Summarise tools to extract the relevant figures (for journal posting/audit testing).

Hope this clears up a little on lease accounting based on IFRS 16.