cancel
Showing results for 
Search instead for 
Did you mean: 

Account Aging Metrics / Utilization spreadsheet

Highlighted
Frequent Contributor

Re: Account Aging Metrics / Utilization spreadsheet


@Birdman7 wrote:

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


Yes, all of our AU's count on each other's reports so no problem for the calculations shown.



Authorized User
Message 51 of 66
Highlighted
Established Contributor

Re: Account Aging Metrics / Utilization spreadsheet

  • Can you add version information to the filename?
    • Yes, but probably rather include it in the file so I can keep updating the file in DropBox and have it overwrite and keep the same link.
    • You don't want it to be version 93 by the end of the week??
  • 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.)
    • Going to need a cliff notes on the changes we'd want and how to calculate them. Overall I'm thinking moving the results to a different sheet and leaving the inputs on another one so it can be a full screen with it all together.
    • Are chargeoffs and collections handled differently? Not familiar with anything about them. Are you trying to see just $ amounts or ages? 
  • I'm always confused with AU accounts, so do AU accounts need to have any kind of separate partitioning or AAoA (perhaps others) calculated?
    • Based on how I've seen Birdman7 posting about it in some threads, I'm going to guess it should be an option to factor in. Like Open Accounts with and without AUs because .
    • AUs can only be for revolvers though, correct?
    • Might be able to work something in, possibly. Would need another column to indicate AU or not though.
    • Can AUs impact even if they are closed or does it automatically get removed??
  • Hey 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?
    • That should be easy enough. Just to make sure though, one for revolvers and another for all accounts correct? Nothing separated out for installments (by default I suppose open ones have a balance).
  • 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. 
    • Same as above
  • but is it adding installment limits to total limits even when it's closed without a balance?

    • Yes. It's a blanket sum of everything.

 

@Birdman7  @CassieCard 

as of 10/13/20
Current Cards:
Message 52 of 66
Highlighted
Valued Contributor

Re: Account Aging Metrics / Utilization spreadsheet


@ccquest wrote:
  • Can you add version information to the filename?
    • Yes, but probably rather include it in the file so I can keep updating the file in DropBox and have it overwrite and keep the same link.
    • You don't want it to be version 93 by the end of the week??

The problem with keeping it in the file properties is that I can't see which version is up there on Dropbox before I download it.

 

The Major.Minor.PatchLevel version scheme in the filename lets me know quickly if I am making corrections to an older version that I already downloaded.

 

Adding features will only increment the minor version - major will be 2 for quite a while.

Correcting typos/text/formatting will increment the patch level.

 

So it would be something like 2.93.2, after adding around 93 features to version 2, and fixing errors 2 times in the 2.93 release.

 

The rest of your answers to Birdman7's questions are great. I would be confused about much of the AU/charge-off stuff.

It's so easy to track every conceivable aging value in Excel, so I say just put everything in there.

27 FICO Scores + 3 VS3. MTG (Mortgage), AUT (Auto), and BKC (Bankcard) are scores 5,4, and 2 from the top.

Keeper of the Scrolls of ResilienceEQ FICO Resilience Index Score Leaderboard

46 forum members have shared this new credit score. Share yours today!

Message 53 of 66
Highlighted
Established Contributor

Re: Account Aging Metrics / Utilization spreadsheet


@CassieCard wrote:

The problem with keeping it in the file properties is that I can't see which version is up there on Dropbox before I download it.

 


It doesn't open to the preview of the file for you? For me it does and I'm thinking either just below the title or to the right of it. If you want to PM your email I can share it with you as editable so you can see the version history too.

 

I know how the versioning works, just joking how many edits it'll be getting over the week probably lol.

as of 10/13/20
Current Cards:
Message 54 of 66
Highlighted
Super Contributor

Re: Account Aging Metrics / Utilization spreadsheet


@ccquest wrote:
  • Can you add version information to the filename?
    • Yes, but probably rather include it in the file so I can keep updating the file in DropBox and have it overwrite and keep the same link.
    • You don't want it to be version 93 by the end of the week??
  • 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.)
    • Going to need a cliff notes on the changes we'd want and how to calculate them. Overall I'm thinking moving the results to a different sheet and leaving the inputs on another one so it can be a full screen with it all together.
    • Are chargeoffs and collections handled differently? Not familiar with anything about them. Are you trying to see just $ amounts or ages? 
  • I'm always confused with AU accounts, so do AU accounts need to have any kind of separate partitioning or AAoA (perhaps others) calculated?
    • Based on how I've seen Birdman7 posting about it in some threads, I'm going to guess it should be an option to factor in. Like Open Accounts with and without AUs because .
    • AUs can only be for revolvers though, correct?
    • Might be able to work something in, possibly. Would need another column to indicate AU or not though.
    • Can AUs impact even if they are closed or does it automatically get removed??
  • Hey 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?
    • That should be easy enough. Just to make sure though, one for revolvers and another for all accounts correct? Nothing separated out for installments (by default I suppose open ones have a balance).
  • 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. 
    • Same as above
  • but is it adding installment limits to total limits even when it's closed without a balance?

    • Yes. It's a blanket sum of everything.

 

@Birdman7  @CassieCard 


@ccquest Yes chargeoffs (COs) are different than collections (CAs). COs balances add to balances and to the numerator of revolver utilization. Its unknown how the CL is applied to the denominator, so I suggest multiple results until we find the correct method. 

 

CAs also apply to balances and to the numerator of utilization, but not to the denominator. 

 

I'd make CO and CA options and then:

 

1. Add CO (chargeoff) and CA balances to total open revolving balances. 

2. leave “total limits” without any closed or chargeoff limits. realized you have open and closed limits, dont include CAs anywhere

 

3. put an additional cell that says “Total Open rev Limits + closed rev with a balance limits” (TORL+ CL from closed revolvers with a balance.)

 

4. put a second additional cell saying “TORL + chargeoff limits” (TORL + reported CO CLs)

 

5. Put a 3d saying "TORL + closed and CO limits" (TORL + CL from closed revolvers with a balance + CO CL)

 

6. and a 4th that says "TORL with closed account and chargeoff balances as their limits." (TORL + closed revolver balances + CO balances.)

 

7. Then put 4 more cells, each with utilization calculations using total revolving balances over the results from 3, 4, 5, & 6  

8. COs and CAs I believe totally count on aging metrics. 

9. AUs count towards age until removed. Towards balances and utilization if open and counting. Yes only revolver. 

 

10. Yes The revolver metric is only for revolvers and the account metric includes other account types such as loans. We need number and percentage on revolver but only percentage on accounts. 

11. if an account is closed without a balance its limits should not be included in total limits. realized you have open and closed limits, dont include CAs anywhere

 

 

@ccquestEdited  10.6.20 10:07pm- I made a correction or 2 and tried to make it more readable.

For a collection of our current FICO scoring wisdom, updated as we learn, read the following. Watch the revision dates on the bottom of the first 8 posts as they are regularly updated: Link to Scoring Primer.


RIP:

Oldest/average age varies by bureau. Estimates above.


Updated Oct 2020, unless otherwise noted.

(Forgive typos, mobile.)(Everything said is Just IMHO.)

In order to better answer your questions and record your DPs, please provide your profile stats: Any baddies? Severity and recency? (clean/dirty), Number of accounts, both open and closed on CRs (thick/thin), AoOA? (Mature/young), AOYRA-Age of Youngest Revolving Account (new accounts/no new accounts)? Open/closed loan on CR?
For example, mine is clean/thick/mature/new account, with open loan on record.
If you don't know where you fall, just detail any baddies, your number of open and closed accounts, AoOA, AOYRA and whether you have a loan on record to start.

For utilization questions, list individual and aggregate utilizations, revolving and installment, please.
Message 55 of 66
Highlighted
Valued Contributor

Re: Account Aging Metrics / Utilization spreadsheet


@ccquest wrote:

@CassieCard wrote:

The problem with keeping it in the file properties is that I can't see which version is up there on Dropbox before I download it.

 


It doesn't open to the preview of the file for you? For me it does and I'm thinking either just below the title or to the right of it. If you want to PM your email I can share it with you as editable so you can see the version history too.

 

I know how the versioning works, just joking how many edits it'll be getting over the week probably lol.


Version number in a cell would be great! Yes, I can see the preview clearly.

 

I sort of thought you already knew the version format. And 93 edits is probably just getting started. lol

 

Before you know it, there will be a Space Invaders worksheet:

Excel_Space_Invaders.gif

27 FICO Scores + 3 VS3. MTG (Mortgage), AUT (Auto), and BKC (Bankcard) are scores 5,4, and 2 from the top.

Keeper of the Scrolls of ResilienceEQ FICO Resilience Index Score Leaderboard

46 forum members have shared this new credit score. Share yours today!

Message 56 of 66
Highlighted
Super Contributor

Re: Account Aging Metrics / Utilization spreadsheet

I really think the easiest way to filter it would be to add it to the list: open, closed, chargeoff, collection

As far as authorized users go, I wouldn’t touch it. I would simply allow people to copy the worksheet, if theirs doesn’t count on the new versions; then they have a second worksheet without the authorized user accounts for 8 & 9. 


Stated differently, First worksheet for 542 and the second one for 8 & 9, without AUs, for those where the authorized user accounts are not counting on the new versions. 

@ccquest Edit: if you read my earlier post explaining how to implement it, that was before I realized how much simpler this solution was. if you'll notice I edited that post. And even this one, im a habitual editor. Lol. 

For a collection of our current FICO scoring wisdom, updated as we learn, read the following. Watch the revision dates on the bottom of the first 8 posts as they are regularly updated: Link to Scoring Primer.


RIP:

Oldest/average age varies by bureau. Estimates above.


Updated Oct 2020, unless otherwise noted.

(Forgive typos, mobile.)(Everything said is Just IMHO.)

In order to better answer your questions and record your DPs, please provide your profile stats: Any baddies? Severity and recency? (clean/dirty), Number of accounts, both open and closed on CRs (thick/thin), AoOA? (Mature/young), AOYRA-Age of Youngest Revolving Account (new accounts/no new accounts)? Open/closed loan on CR?
For example, mine is clean/thick/mature/new account, with open loan on record.
If you don't know where you fall, just detail any baddies, your number of open and closed accounts, AoOA, AOYRA and whether you have a loan on record to start.

For utilization questions, list individual and aggregate utilizations, revolving and installment, please.
Message 57 of 66
Highlighted
Valued Contributor

Re: Account Aging Metrics / Utilization spreadsheet

A simple AAoA calculator: https://aaoa-calculator.org/

 

That's been around a few years. Might help someone that doesn't have Excel.

27 FICO Scores + 3 VS3. MTG (Mortgage), AUT (Auto), and BKC (Bankcard) are scores 5,4, and 2 from the top.

Keeper of the Scrolls of ResilienceEQ FICO Resilience Index Score Leaderboard

46 forum members have shared this new credit score. Share yours today!

Message 58 of 66
Highlighted
Established Contributor

Re: Account Aging Metrics / Utilization spreadsheet

Made some of the changes in this version, didn't really get any time in recent days though and this still doesn't account for things regarding CA/CO accounts.

https://www.dropbox.com/s/lxbd3bs7g65lpmp/Account%20Aging%20Metrics%20%28for%20not%202019%20v2.1%29....
as of 10/13/20
Current Cards:
Message 59 of 66
Highlighted
Super Contributor

Re: Account Aging Metrics / Utilization spreadsheet

I can’t wait to look at it! I wish you gave us a change log ... the suspense is killing me, lol!

Lotta people are already using it and I referred a couple people to use it, its going to help a lot of people!
For a collection of our current FICO scoring wisdom, updated as we learn, read the following. Watch the revision dates on the bottom of the first 8 posts as they are regularly updated: Link to Scoring Primer.


RIP:

Oldest/average age varies by bureau. Estimates above.


Updated Oct 2020, unless otherwise noted.

(Forgive typos, mobile.)(Everything said is Just IMHO.)

In order to better answer your questions and record your DPs, please provide your profile stats: Any baddies? Severity and recency? (clean/dirty), Number of accounts, both open and closed on CRs (thick/thin), AoOA? (Mature/young), AOYRA-Age of Youngest Revolving Account (new accounts/no new accounts)? Open/closed loan on CR?
For example, mine is clean/thick/mature/new account, with open loan on record.
If you don't know where you fall, just detail any baddies, your number of open and closed accounts, AoOA, AOYRA and whether you have a loan on record to start.

For utilization questions, list individual and aggregate utilizations, revolving and installment, please.
Message 60 of 66
Advertiser Disclosure: The offers that appear on this site are from third party advertisers from whom FICO receives compensation.