cancel
Showing results for 
Search instead for 
Did you mean: 

Account Aging Metrics / Utilization spreadsheet

Highlighted
New Contributor

Re: Account Aging Metrics / Utilization spreadsheet

Thank you! Working fine for me! Running on High Sierra osx 10.13.6, Excel 16.41.

Cheers!



AoOA- 6.8 - AAoA - 4.5 - Clean Profile - 40% UTI - 17k/41k Limits

Message 11 of 66
Highlighted
Established Contributor

Re: Account Aging Metrics / Utilization spreadsheet


@Birdman7 wrote:

I noticed an anomaly, the first row of calculations under all accounts works in the second file but not in the first

 

@ccquest Microsoft office professional+ 2016


First row is all "regular" functions, second row begins the various IFs.

 

If you can, try to set B5 to be: 

=TEXT(MINIFS($J$18:$J$103,$B$18:$B$103,"Revolver")/SWITCH($A$5,"Months",1,"Years",12),"0.00") & SWITCH($A$5,"Months"," months","Years"," years")

 

Maybe it'll let you save it that way, but won't load them in for some reason. Basically the little _xl whatever towards the start is breaking it on mine, but because my license is having issues due to the 365 update I can't actually do anything, how nice!

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

Re: Account Aging Metrics / Utilization spreadsheet

What command should be there rather than the ones your named?
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 13 of 66
Highlighted
Established Contributor

Re: Account Aging Metrics / Utilization spreadsheet


@Birdman7 wrote:
What command should be there rather than the ones your named?

The one I quoted above is an example, but here's a Microsoft page on the issue I'm having at least. With Pro Plus 2016 you should have them all working though.

 

edit » oh, MINIFS is another new function duh. This page says Excel 2019.

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

Re: Account Aging Metrics / Utilization spreadsheet

@Birdman7, @ccquest : Which version of Excel are you both using?

 

I have Microsoft 365, so it's always the latest version, which I suspect ccquest is using (or Excel 2019, first standalone version to include the SWITCH statement).

 

I think this might help a lot of people, but version control might fry ccquest's mind. lol (It would certainly kill me after using Git for so long.)

 

I've never looked into using GitHub for Excel files, because I was always sharing with other Microsoft 365 users.

There is a paid subscription option, using xltrail (Link), but that's crazy for this. Maybe there is a free, open-source option out there?

 

Anyway, this is a great start for an Excel worksheet. I would use the following formula for any 'Average Age' calculations that display in 'Years' (as the SWITCH function uses in this worksheet):

 

=INT(avgCell/12)&"yr "&ROUND(MOD(avgCell,12),0)&"mo"

where avgCell is the average age in straight number format with fractional part.

 

...using SWITCH:

=@SWITCH($A$5,"Months",AVERAGE(J18:J103),"Years",(INT(AVERAGE(J8:J103)/12)&"yr"&ROUND(MOD(AVERAGE(J8:J103),12),0)&"mo"),"0.00")

( AVERAGE(J18:J103) can be defined as a name in Name Manager)

 

[EDIT: It looks like this after that change, just in AAoA for now]

 

example_aging_yr_mo.png

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 15 of 66
Highlighted
Super Contributor

Re: Account Aging Metrics / Utilization spreadsheet

The new command is killing me. It started 2019. Anyway to save a file in a format that would make it compatible with the 16? Or is there a command I can replace it with?

 

SWITCH started 2016.. I can process it...EDIT apparently not!

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 16 of 66
Highlighted
Established Contributor

Re: Account Aging Metrics / Utilization spreadsheet

I'll have it ready shortly and also trying to incorporate CassieCard's idea too.
as of 10/13/20
Current Cards:
Message 17 of 66
Highlighted
Established Contributor

Re: Account Aging Metrics / Utilization spreadsheet

@Birdman7  give this one a shot please

 

https://www.dropbox.com/s/9hs28je6xotd5in/Account%20Aging%20Metrics%20%28for%20not%202019%29.xlsx?dl...

 

I think I changed them all, this doesn't include the display change like @CassieCard suggested yet though.

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

Re: Account Aging Metrics / Utilization spreadsheet

I’m trying that now but the MINIFS is killing me. Y’all said switch didn’t come out till 2019 by the page I went to said 16 and I think my 16 is reading it
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 19 of 66
Highlighted
Super Contributor

Re: Account Aging Metrics / Utilization spreadsheet

No now I’m getting the error in front of the switch commandSmiley Embarassed

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 20 of 66
Advertiser Disclosure: The offers that appear on this site are from third party advertisers from whom FICO receives compensation.