All,
I'm working on an aging report for transactions due at different dates and I've hit a wall.
I have premium transactions due at different days, then I have a cash amount which needs to be applied first to the oldest transactions, then the remainder to the next transaction, etc. For each of these transactions I need to calculate outstanding balance.
I need to be able to do it in Power Query. However, I cannot even figure out a formula to do it in Excel.
Below is the mock up data. Once I know how to figure out the BALANCE amount, I'll be able to do the aging.
| POLICY | DUE DATE | TRANSACTION | AMOUNT | BALANCE |
| AAAA | 4/14/2018 | PREMIUM | $128.53 | $0.00 |
| AAAA | 5/15/2018 | PREMIUM | $14.83 | $2.91 |
| AAAA | 6/14/2018 | PREMIUM | $14.83 | $14.83 |
| AAAA | 7/15/2018 | PREMIUM | $14.83 | $14.83 |
| AAAA | 8/14/2018 | PREMIUM | $14.83 | $14.83 |
| AAAA | 9/14/2018 | PREMIUM | $9.33 | $9.33 |
| AAAA | CASH | ($131.44) | ||
| BBBB | 4/14/2018 | PREMIUM | $83.58 | $0.00 |
| BBBB | 5/15/2018 | PREMIUM | $12.38 | $0.00 |
| BBBB | 6/14/2018 | PREMIUM | $12.38 | $8.59 |
| BBBB | 7/15/2018 | PREMIUM | $12.38 | $12.38 |
| BBBB | 8/14/2018 | PREMIUM | $12.38 | $12.38 |
| BBBB | 9/14/2018 | PREMIUM | $12.38 | $12.38 |
| BBBB | CASH | ($99.75) |
Would you be able to help?
Thank you,
Blanka
Hi Blanka,
The balance doesn't make sense. Surely the balance for AAAA as at 6/14 is $17.74 ($2.91 + $14.83) and so on.
Also, what date does the CASH payment get received? You need to know the date received in order to do the ageing balance.
Mynda
Hi Mynda,
We recently started installment payments and it's throwing me for a loop.
We load entire premium into the system at one time, but because the payments can be done in installments, not the entire premium is due at once.
The entire premium for policy AAAA above is $197.18 but parts of it are due on different dates. The payment (cash) in the amount of $131.44 was received on 7/24/18 (July 24th).
$128.53 was applied to the first premium with the due date of 4/14/18, therefore the balance for that date is now $0.
The remaining $2.91 was applied to the premium with due date of 5/15/18, so the balance should be $11.92 for this part of the premium (you're right, I do have an error in my table).
At this point, there is no more cash left, so the premium with due date of 6/14/18 is fully outstanding. Therefore the balance due on 6/14/18 is the full $14.83.
Same goes for all the remaining due dates - the entire premium amount is outstanding.
I need to calculate the aging as of 9/30/18 (September 30th), so in this case $2.91 + $14.83 would be over 90 days, then $14.83 would be 61-90 days, another $14.83 would be 31-60 days, and $9.33 would be 1-30 days.
I feel like I am overthinking it.
Thank you,
Blanka
Hi Blanka,
Interesting challenge 🙂
Cash should be in a separate table, otherwise it will make thinks more complicate than it should be. We managed to display the data as you wanted (I continued what Mynda started), it contains complex operations though.
Ageing should be easy now, if you need help just let us know.
Cheers,
Catalin
Hi Catalin,
Thank you so much!
The calculations you created are way above my head. This looks like advanced PQ so now I feel better, because I thought that it was a simple answer that was staring me in the face and I just couldn't figure it out. 🙂
I'll play around with it. I'll have to also try a scenario where I have more cash than premium (it does happen).
Thank you again.
Blanka
