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:
Code: Select all
Interest outstanding coming into 2017 - $0
Interest accrued January 2017 - $10,000
Interest accrued February 2017 - $10,000
Interest accrued March 2017 - $10,000
Accrued interest at the end of March 2017 - $30,000
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?