calculating age of debt (30,60,90 days)

[Laurence Lombard]

Can anyone show me the logic for calculating the 30, 60,90,>120 ages of
items in an account. The calculations are complicated by the fact that
there might be new invoices and down payments in the period under question
eg:
Date/description/amount/balance
1 May /Invoice/$1000/$1000
15 June/Payment/$250/$750
28 June/Invoice/$500/$1250

Thanks
Laurence

 

[Laurence Lombard]

Can anyone show me the logic for calculating the 30, 60,90,>120 ages of
items in an account. The calculations are complicated by the fact that
there might be new invoices and down payments in the period under question
eg:
Date/description/amount/balance
1 May /Invoice/$1000/$1000
15 June/Payment/$250/$750
28 June/Invoice/$500/$1250

Thanks
Laurence

I managed to figure out something. I have tackled the problem with VBA
code. It would be interesting to know whether one can do it with
formulas. Here is my take on the subject
https://dl.dropbox.com/u/11809449/DebtAgeAnalysis.xls