Using Alteryx to perform Lease Accounting Calcs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi -
Has anyone used Alteryx to perform lease accounting ASC 842/ IFRS 16 calcs?
- Labels:
- Common Use Cases
- Help
- Transformation
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Yes looking to build out table that showcases ROU Asset, its balances, Lease liability, amortization expense and interest expense each month
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
What's your use case? Attaching a workflow alone is not enough context... What are you trying to do?
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I am looking to build out formulas and output a amortization table in line with IFRS 16
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
