Idle Funds Aging

[Vapour02]

Hello all, can someone help me understand how am I able to find the aging for idle funds. For example, if my opening balance for first day is 10 no debit but credited 5 so closing balance is 5. Now next day opening balance is 5 debit again zero but credit 3 so closing balance is 2 , next day closing balance is 2 and so on. Now I want to find out how much days it took for the first day amount of 10 to be invested. Attached is the sample for the same. Please help.

[mceleavey]

Hi @Vapour02 ,

what do you mean "invested"?

Relate it to the data and columns you have provided.

Do you mean debited? So in your example data, your first line start with 0 then gets 10 credited, so how many rows until the debits add up to ten?

M.

[Vapour02]

Hi @mceleavey , sorry for the confusion. Yes invested as in debited. So if I have a 10 as opening balance it should be debited next day. If not how much days it took to debit the amount 10 is what I'm looking for. Also even if a new amount is credited next day. I need to know first how much days it took for 10 to be debited completely and then move to the next credited amount. 

[Vapour02]

Hi @mceleavey , sorry for the confusion. Yes invested as in debited. So if I have a 10 as opening balance it should be debited next day. If not how much days it took to debit the amount 10 is what I'm looking for. Also even if a new amount is credited next day. I need to know first how much days it took for 10 to be debited completely and then move to the next credited amount. 

[mceleavey]

Hi @Vapour02 ,

ok, so it looks like you would need an iterative macro. Take the first row closing balance, then assign the next rows debits until the original balance is "invested". Then remove row 1, and run row 2 with the remainder from row 1. Repeat until the debits are assigned, spit out the remaining records.

M.

[mceleavey]

@Vapour02 ,

A good place to start with Iterative Macros is HERE.

Hope this helps,

M.

[Vapour02]

Thank you! I'll give this a try. 

[Vapour02]

Can you please elaborate more on this. I haven't really worked with macros before.