TM1 Forum

Has anyone out there ever done anything in TM1 with a FIFO (first-in, first-out) distribution of data. Here’s the scenario:

I’ve got a situation where I need to calculate the FX gain on an intercompany note interest payment. The gain is calculated by taking the difference between the FX rate in the month when the interest was accrued and the month when it was actually paid. I have the interest accrual transactions in one cube, by note, recorded in a time dimension as to the month in which it was accrued. I have the interest payments in another cube, by note, recorded in the month in which the payment was made. The trick is I now need to merge the data from these two cubes into another cube with the data spread across two time dimensions, one for when the interest was accrued and one for when the payment was made.

Merging the payment data into the cube along the payment time dimension is easy as it can come directly from the payment cube time dimension. My problem is how to distribute that payment along the time dimension as to when it was accrued. The rule is that the interest should treated as FIFO, meaning when a payment is made it should count against the oldest interest still outstanding and move forward in time until the payment is exhausted. For example, let's assume the following facts:
Now, let's say I make a payment of $25,000 in April 2017. That payment should be applied $10,000 to January, $10,000 to February and $5,000 to March. The next payment made should then start with the remaining $5,000 that was left from March and move forward to any interest accrued since the first payment.

Right now I’m having a mental block and cannot seem to figure out the best way to spread the data into the accrual time dimension like the above examples. A rule-based solution would be best but TI is also acceptable. Any suggestions?

I don't see why you need to do any specific accounting to match interest accrual and payment periods at all. I'm attaching a little model that shows how I would do it in Excel-- is this what you're trying to accomplish or am I missing it?

Matt

I would think of a mixup of both measures like interest (with negative sign) and payment (kept with positive sign) to be generalized as accounting transactions.
Then ordering this by time and making a cumulation per transaction, similar as my bank is doing in my account statements.
After each iteration by day (or whatever period length makes up your interest frequency, but I guess it will beside of your example in fact follow rather a daily pattern ( 30/360)) could be calculated the interest days applied to this. Based on these interest days and the interest rate the gain could be calculated over the period to regard.
I hope, it does help more than it confuses... but better than nothing. For a more TM1 based solution I have to leave my TM1-rookie state behind of me first, I fear...