Account Aging Metrics / Utilization spreadsheet

[Anonymous]

@ccquest: Can you add version information to the filename?

Account Aging Metrics (for not 2019 v2).xlsx

to

Account Aging Metrics (Excel 2007 and up)-v2.0.1.xlsx

Where version is Semantic Versioning: Major (e.g., adding a worksheet), Minor (adding feature), Patch (fixing bugs in Minor)

[Anonymous]

@ccquest Edited: If we make it 4 cells for aggregate revolving utilization and 4 for total revolving limits, it might help us to determine how the algorithm is actually treating it, if we have some people using this and getting the data points back.

Imagine an option for open, closed, chargeoff, and collection. Collection and chargeoff balances would add to total balances, but again we don’t know what to do with limits for chargeoffs and closed with a balance. (no limits for collections.)

I would suggest leaving “total limits” without any closed or chargeoff limits. I would put a separate cell that says “with closed limits” and a second additional cell saying “with chargedoff limits” and a third saying "with closed and chargedoff limits", and a fourth that says "with closed and chargedoff limits as their balances."

 Then have 4 aggregate revolving utilization split into those 4.

[Anonymous]

I edited my last 2 posts, you may want to re-read them @ccquest .

@Anonymous would that be a minor revision? (for purposes of version number)

[Anonymous]

Can you imagine layperson in scoring being able to use this and be able to accurately give us all the metrics rather than relying on who knows what, credit karma or lack of understanding of the proper way to calculate a metric, or whatever.

Because many of the times the reasons we’re unable to resolve a problem, or it takes more time, is because the calculation is off; this will fix that. they can just plug their values in and give us a screenshot of the results!!😁😁

[Duke_Nukem]

Excellent job, @ccquest  and all others that helped tweak the file!  My DW had some CC app activity yesterday, so I'll probably post some DP's later today in the CC forums using this great tool!

I did some minor formatting to the result tables to make it a little more palatable (for my eyes at least).  Adding a screenshot below.

Question:  I'm always confused with AU accounts, so do AU accounts need to have any kind of separate partitioning or AAoA (perhaps others) calculated?

[Anonymous]

---

@Duke_Nukem wrote:

Excellent job, @ccquest  and all others that helped tweak the file!  My DW had some CC app activity yesterday, so I'll probably post some DP's later today in the CC forums using this great tool!

I did some minor formatting to the result tables to make it a little more palatable (for my eyes at least).  Adding a screenshot below.

Question:  I'm always confused with AU accounts, so do AU accounts need to have any kind of separate partitioning or AAoA (perhaps others) calculated?

---

@Duke_Nukem Wow that's a good point, I hadn't even thought of that. Thank you for pointing that out. On my own personal spreadsheet I have One cell that calculates my aging with my AUs and another that does it without because mine don't count on versions 8 and 9.

and therein lies the solution to that. We know they will always count on 542 and as long as the anti-abuse algorithm is not catching it it will also count on the metrics for version 8 and 9. So as is, it's good for the mortgage scores no matter what AND for those whose authorized user accounts are counting.

However, if ones AUs are not counting on versions 8 and 9 because of the anti-abuse algorithm, I would recommend they copy the sheet and have a second worksheet without the authorized user accounts, so the person would know their metrics for version 8 and 9.

So, as long as your authorized user accounts are counting, your good all the way around, but if they're not counting, yeah, it would require a second worksheet without those accounts.

edited 8.28.20 5:40pm. @Duke_Nukem 

[Anonymous]

While this is going to be so so so cool!! people can just paste their metrics and it’ll be so easy to figure things out in comparison to the old way!

But like he said it was originally designed for him so wasn’t facing all the potential needs, but it’s broadened quite nicely! And from where it’s at, someone can customize it however they like!

BIG kudos to @ccquest for creating and sharing this and BIG thanks to my Technical Advisor @Anonymous for all her assistance and for helping us get it polished and ready for everyone! I’m sure there will be more revisions.

[Anonymous]

@ccquestHey can we get a percentage of revolvers with a balance metric in a cell? And a # of revolvers with a balance metric in a cell? Then want to do the same thing again for accounts?

@ccquest and it's very important we have one for open and a second for open + closed, because we have evidence that the metric includes closed revolvers at least on one version. 

Edited: 9.28.20 4:52pm

[Anonymous]

@Duke_Nukem all your authorized user accounts are counting on versions 8 and 8 right?

[Anonymous]

---

@ccquest wrote:

---

@Anonymous wrote:

(@Anonymous also) Should TOTAL LIMIT under ALL ACCOUNTS include a Closed Installment Loan Limit? It's a straight SUM under AGG $ and TOTAL LIMIT.

---

That part was done 100% on purpose. I wasn't sure on exactly how closed accounts report and I'm pretty sure I've read it varies based on the account type and maybe lender too. Like some closed revolvers may have a $600 balance left on what was a $2500 limit and report at 100% utilization because it's closed, while others may be 24%. It's just flat total numbers to avoid any misrepresentation there. 

There may be some version of aggregate revolver utilization to incorporate though that looks at open accounts and also adds in closed revolvers with balances as if they were always at 100% (so add the balance as a limit instead of even considering the limit).

---

@Anonymous to the question as posed, no, not unless it was in chargeoff status or closed with a balance, and then, again like I said, we don't know how that would report, so that's why I say we can calculate it both ways and hopefully discover what the correct answer is. 

@ccquest but is it adding installment limits to total limits even when it's closed without a balance?