Aging Report Formula?

S

savosean

New Member
Joined
Jun 7, 2018
Messages
36
I was wondering if anyone knew if this was possible, I am trying to age a large file of accounts (5000+) that simply have user ID's attached to them, amounts and their date. What I am trying to do is to allocate each age appropriately. Meaning I would like to subtotal per User Id the fact that the amount in the first dated transaction is still owing to this day, however, it received two payments. Therefore, technically owing since 08-22-17 as seen below would be 465, and another 200 owing as of 12-31-18.

Illustration 1A :

USER $ Date outstanding 1-30 days 31-60 days 61-90 days
123 565 08-22-17 XXX XXX xxx xxx
123 -55 08-22-18 XXX XXX xxx xxx
123 -45 11-12-18 XXX XXX xxx xxx
123 200 12-31-18 XXX XXX xxx xxx

(EDIT: the formatting is not working for this table, but the columns should all be in order USER ID, Amount, DATE, number of days outstanding, 1-30 days, 31-60 days, 61-90 days,... and it continues on to 5+ years)

So far I am unable to do this correctly with out manually adjusting the subtotal feature. I've been able to subtotal each USER ID, therefore I know how old each transaction is... except its not really a running aging report at this point. Is what I am asking possible? If further explanation is required please let me know.

thanks
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Aging. Are you using a Pivot Table and grouping the Number of Days past-due?
 
Upvote 0
SpillerBD said:
Aging. Are you using a Pivot Table and grouping the Number of Days past-due?
Click to expand...

I could use a pivot table, however, I used the subtotal feature... to subtotale every column at each change in USER ID. However, this doesn't correct allocate the aging of a debt, if payments have come in since then. As my output is lets say 180 days past due $500 , 90 days past due -$300, and 30 days past due $50... when what I'd really like to see is 180 days past due $200, and 30 days past due $50. Basically applying the payment to the oldest debt, and still having it showcase that debt has been owing since 180+ days.

I also have situations where 180 days past due $500, 90 days past due -$600, and 30 days past due $70... Therefore, the oldest debt has been eliminated, however, they overpaid the account. So in the account it should show a credit balance of $30 in the 90 days past due column.

I haven't really been able to find a solution online, and don't really know if what I am asking is even possible. Was hoping for some insight...
 
Upvote 0
savosean said:
I could use a pivot table, however, I used the subtotal feature... to subtotale every column at each change in USER ID. However, this doesn't correct allocate the aging of a debt, if payments have come in since then. As my output is lets say 180 days past due $500 , 90 days past due -$300, and 30 days past due $50... when what I'd really like to see is 180 days past due $200, and 30 days past due $50. Basically applying the payment to the oldest debt, and still having it showcase that debt has been owing since 180+ days.

I also have situations where 180 days past due $500, 90 days past due -$600, and 30 days past due $70... Therefore, the oldest debt has been eliminated, however, they overpaid the account. So in the account it should show a credit balance of $30 in the 90 days past due column.

I haven't really been able to find a solution online, and don't really know if what I am asking is even possible. Was hoping for some insight...
Click to expand...

Hi,

See if this helps:

ABCDEFG
1User IDAmountDateAmount dueDateDue fromDue Amount
21235658/22/20175658/22/2017
3123-558/22/20185108/22/2017
4123-4511/12/20184658/22/20178/22/2017465
512320012/31/201820012/31/2018
6123-106/21/201919012/31/201812/31/2018190

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
D2=IF(A2=A1,IF(B2<0,B2+D1,B2),B2)
E2=IF(B2>=0,C2,E1)
F2=IF(E2=E3,"",E2)
G2=IF(F2="","",D2)
D3=IF(A3=A2,IF(B3<0,B3+D2,B3),B3)
E3=IF(B3>=0,C3,E2)
F3=IF(E3=E4,"",E3)
G3=IF(F3="","",D3)
D4=IF(A4=A3,IF(B4<0,B4+D3,B4),B4)
E4=IF(B4>=0,C4,E3)
F4=IF(E4=E5,"",E4)
G4=IF(F4="","",D4)
D5=IF(A5=A4,IF(B5<0,B5+D4,B5),B5)
E5=IF(B5>=0,C5,E4)
F5=IF(E5=E6,"",E5)
G5=IF(F5="","",D5)
C6=TODAY()
D6=IF(A6=A5,IF(B6<0,B6+D5,B6),B6)
E6=IF(B6>=0,C6,E5)
F6=IF(E6=E7,"",E6)
G6=IF(F6="","",D6)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Subtotal acts on your data rather than reporting on your data, which is what a Pivot Table does.
What I see is that you have no base transaction, just debits and credits.
A Payment shouldn't be on this report. It is a different transaction. However, in this scenario a payment could be recorded with the same information as the invoice so the two items would be summed together and the aging $ correct.
 
Upvote 0
Aryatect said:
Hi,

See if this helps:

ABCDEFG
1User IDAmountDateAmount dueDateDue fromDue Amount
21235658/22/20175658/22/2017
3123-558/22/20185108/22/2017
4123-4511/12/20184658/22/20178/22/2017465
512320012/31/201820012/31/2018
6123-106/21/201919012/31/201812/31/2018190

<tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
D2=IF(A2=A1,IF(B2<0,B2+D1,B2),B2)
E2=IF(B2>=0,C2,E1)
F2=IF(E2=E3,"",E2)
G2=IF(F2="","",D2)
D3=IF(A3=A2,IF(B3<0,B3+D2,B3),B3)
E3=IF(B3>=0,C3,E2)
F3=IF(E3=E4,"",E3)
G3=IF(F3="","",D3)
D4=IF(A4=A3,IF(B4<0,B4+D3,B4),B4)
E4=IF(B4>=0,C4,E3)
F4=IF(E4=E5,"",E4)
G4=IF(F4="","",D4)
D5=IF(A5=A4,IF(B5<0,B5+D4,B5),B5)
E5=IF(B5>=0,C5,E4)
F5=IF(E5=E6,"",E5)
G5=IF(F5="","",D5)
C6=TODAY()
D6=IF(A6=A5,IF(B6<0,B6+D5,B6),B6)
E6=IF(B6>=0,C6,E5)
F6=IF(E6=E7,"",E6)
G6=IF(F6="","",D6)

<tbody>
</tbody>

<tbody>
</tbody>
Click to expand...

This is great, however, a few follow up questions I have in regards to this. What's found in B6 shouldn't go against the more recent debt, it should go against the oldest debt if one is still present. Therefore, G4 should be 455 due since 2017-08-22, and G6 should see 200 due since 2018-12-31.

Additionally, I've tried continuing the code with another USER ID and it doesn't seem to flow through, is there something specific I must do... as in cannot simply drag the code down? I will keep looking at it, and see if I can figure out how on my own too. Thank you very much for the help!


USER ID

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
Amount

<colgroup><col></colgroup><tbody>
</tbody>

Date

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
Amount due

<colgroup><col></colgroup><tbody>
</tbody>

Date

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
Due From

<colgroup><col></colgroup><tbody>
</tbody>

Due amount

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>123
565
2017-08-22

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
565

<colgroup><col></colgroup><tbody>
</tbody>

2017-08-22

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
123
-55
2018-08-22

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>510
2017-08-22

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
123
-45
2018-11-12

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>465
2017-08-22

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
2017-08-22

<colgroup><col></colgroup><tbody>
</tbody>

465
123
200
2018-12-31

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>200
2018-12-31

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
123
-10
2019-06-21

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>190
2018-12-31

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
2018-12-31

<colgroup><col></colgroup><tbody>
</tbody>

190
234
560
2016-08-22

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>560
2016-08-22

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
2016-08-22

<colgroup><col></colgroup><tbody>
</tbody>

560
234
234
2019-08-22

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>234
2017-08-22

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
234
-4
2018-11-12

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>189
2017-08-22

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
234
-78
2018-12-31

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>111
2017-08-22

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
234
-45
2019-06-21

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>66
2017-08-22

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
2017-08-22

<colgroup><col></colgroup><tbody>
</tbody>


<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>66

<tbody>
</tbody>
<strike></strike>
 
Upvote 0
SpillerBD said:
Subtotal acts on your data rather than reporting on your data, which is what a Pivot Table does.
What I see is that you have no base transaction, just debits and credits.
A Payment shouldn't be on this report. It is a different transaction. However, in this scenario a payment could be recorded with the same information as the invoice so the two items would be summed together and the aging $ correct.
Click to expand...

Could you explain this further to me? I am trying to grasp this concept... I have used a Pivot table and it brings forth the same results as my subtotal function has. How do you mean "a payment could be recorded with the same information as the invoice so the two items would be summed together and the aging correct?"

As of now I have everything summed(Pivot Table) in such that all of the transactions associated to the same user ID are within the same row. I have columns throughout from 1-30 days past due all the way to 5+ years...

For example in USER ID 123 they have a debt of $500 past due from 180days which falls in the 121-180days past due column, however, made payments of $120 which fall within the 31-60 days pay due column, and has new debt from 1-30days past due of $200.

Therefore, USER ID 123 is 180 days past du on $380, and is 1-30 days past due on $200. This is the result I want to report, for over 5000 USER ID's which is a report I'd like to do every month. Therefore, I am in the process of trying to find a way of doing such a thing in the most automatic way possible so I can setup a procedures list for the next person to complete this tasks without much hassle.
 
Upvote 0
savosean said:
For example in USER ID 123 they have a debt of $500 past due from 180days which falls in the 121-180days past due column, however, made payments of $120 which fall within the 31-60 days pay due column, and has new debt from 1-30days past due of $200.
Click to expand...

The payment APPLIES to the debt. So it doesn't apply to itself as you are listing them.
So if you have a payment listed in your transaction list it should have a listing to the invoice(s) it applies to. The complication for a simple table is when a payment applies to multiple debt items. This complication is where a database generally provides a solution that a simple table does.

Most people will think of the transactions at there summary status and not the individual transaction that net to the current balance, and usually an accounting application does it anyway.

In an Excel Table, you would have the transaction detail and fields for the application. An Invoice would apply to itself, so it would appear to have duplicate information. A Payment would list its information and then in the application field the part of the payment applied and the invoice application being made to.
Its the case of "your data must support your reporting"
 
Upvote 0
SpillerBD said:
The payment APPLIES to the debt. So it doesn't apply to itself as you are listing them.
So if you have a payment listed in your transaction list it should have a listing to the invoice(s) it applies to. The complication for a simple table is when a payment applies to multiple debt items. This complication is where a database generally provides a solution that a simple table does.

Most people will think of the transactions at there summary status and not the individual transaction that net to the current balance, and usually an accounting application does it anyway.

In an Excel Table, you would have the transaction detail and fields for the application. An Invoice would apply to itself, so it would appear to have duplicate information. A Payment would list its information and then in the application field the part of the payment applied and the invoice application being made to.
Its the case of "your data must support your reporting"
Click to expand...

I believe I sort of understand what you are saying, however, I think I was oversimplifying the situation. And therefore, getting a bit lost in your explanation. As I was explaining it as if the first debit transaction is an invoice simply as an example. It's not... These are not invoices and payments from clients, I used that as an example in attempts to simplify my issue. This is a giant ledger, whereas the amounts in here are associated to a specific function in the organization. Sometimes we owe clients, other times, the client owes us. Hence the credit and debit transactions throughout. This ledger is a few years old now, and therefore their are transactions in there fairly dated. The only ID field that matches transactions together is their USER ID, besides that they have no relation. However, I don't want to be saying their is a transaction of 180 days old that's $500, and a transaction that's 90 days old that's -$60, and a 30 day old transaction of $60... I'd like it to accurately represent that currently sitting in this USER ID's account is $440 and it's 180 days old, and $60 which is 30 days old.

From my understanding of what you are saying is that I need to identify the credit amounts to certain debit amounts, however, this would be manual work and therefore defeat the purpose of what I am trying to do. If I am not understanding I am sorry.
 
Last edited:
Upvote 0
I take it then the ledger may be held in a system apart from Excel.
And I think you may be in a bit of a pickle where the data will not support exactly what you want with out a manual reconciliation. If the transactions have a relationship to match debits and credits then you should be good. But it doesn't sound like the data supports it.
It may not well support a manual reconciliation one month that can easily carry-forward to the next.

You will need to identify the relationships that are available to determine to what extent you can work with the data.
There may still be an approach where you work in the Table with a running balance for each ID, and when the running balance goes <zero, grab that date for aging. There would be more than just that step though....
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Ageing Bucket Formula
Replies
4
Views
224
Fluff
Fluff
P
  • Question
Analytics Project - Sumifs
Replies
1
Views
222
bobaol
B
K
  • Question
Ageing Report based on conditions
Replies
2
Views
431
kunal86
K
M
  • Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
231
Peter_SSs
Peter_SSs
R
  • Solved
Read through XML file
Replies
1
Views
241
rjtaylor
R

Forum statistics

Threads
1,214,938
Messages
6,122,346
Members
449,080
Latest member
Armadillos

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top