topic Re: Account Aging Metrics / Utilization spreadsheet in Understanding FICO® Scoring

Account Aging Metrics / Utilization spreadsheet

ccquest — Mon, 28 Sep 2020 03:05:37 GMT

Just sharing this to maybe help some people that are wondering what their AAoA or AoYRA is or is going to be. I think I covered the big ones and also shows utilization split based on revolver and installment loans - this is one I was trying to better understand myself for scoring purposes (ie my installment loans went from 85% to 94% and that's the reason my score dropped - not just because I closed the original account).

With that in mind, the ages are based on how we understand the scoring algo sees them. That means it is dated back to the first of whatever month it was issued/opened in. 9/23 is the same as 9/1. As soon as it rolls to the next month, it counts as one month old. I also left the column for "Real Months Since Open" just to show how they compare, but it isn't used for the calculations.

The above is a screenshot for mine, each of the acronyms above gets a "tooltip" in Excel that shows what it means for those of that don't remember every acronym all the time.

Notes:

Formulas are all set for data in rows 18 to 200, beyond that you'll have to either alter formulas or insert rows in between yourself.
"As of date" at the top needs to be the same or later than any of the account open dates
Closed accounts are shown striken through, you can turn that off under conditional formatting but I like it

I'm not perfect and may be overlooking something, but I think the calculations are right based on what I've read in @Anonymous 's wonderful General Scoring Primer Master Thread. Also credit to @Anonymous for the table idea.

Updated with new link because I killed the old versions. If you used one of those, any of the data rows can be copied right over to the new one. This should work on all versions of Excel (at least semi recent ones, that aren't the brand new one).

Re: Account Aging Metrics / Utilization spreadsheet

Anonymous — Sun, 27 Sep 2020 22:21:56 GMT

@ccquest wrote:
Dropbox Link

Just sharing this to maybe help some people that are wondering what their AAoA or AoYRA is or is going to be. I think I covered the big ones and also shows utilization split based on revolver and installment loans - this is one I was trying to better understand myself for scoring purposes (ie my installment loans went from 85% to 94% and that's the reason my score dropped - not just because I closed the original account).

With that in mind, the ages are based on how we understand the scoring algo sees them. That means it is dated back to the first of whatever month it was issued/opened in. 9/23 is the same as 9/1. As soon as it rolls to the next month, it counts as one month old. I also left the column for "Real Months Since Open" just to show how they compare, but it isn't used for the calculations.

The above is a screenshot for mine, each of the acronyms above gets a "tooltip" in Excel that shows what it means for those of that don't remember every acronym all the time.

Notes:
Formulas are all set for data in rows 18 to 100, beyond that you'll have to either alter formulas or insert rows in between yourself.
"As of date" at the top needs to be the same or later than any of the account open dates
Age Unit for the calculations can show months or years, just use the dropdown to swap between them
Closed accounts are shown striken through, you can turn that off under conditional formatting but I like it
I'm not perfect and may be overlooking something, but I think the calculations are right based on what I've read in @Anonymous 's wonderful General Scoring Primer Master Thread. Also credit to @Anonymous for the table idea.

@ccquest wow cool! I can't wait to check it out!

Re: Account Aging Metrics / Utilization spreadsheet

ccquest — Mon, 28 Sep 2020 00:19:09 GMT

Revised spreadsheet for those that don't have Office 365 / 2019. This one uses IF instead of SWITCH.

Re: Account Aging Metrics / Utilization spreadsheet

Anonymous — Mon, 28 Sep 2020 01:10:54 GMT

@ccquest I’m getting some errors in that second one

Re: Account Aging Metrics / Utilization spreadsheet

ccquest — Mon, 28 Sep 2020 01:12:55 GMT

@Anonymous wrote:
@ccquest I’m getting some errors in that second one

Hmmm, what sort of errors/where?

Re: Account Aging Metrics / Utilization spreadsheet

Anonymous — Mon, 28 Sep 2020 01:18:46 GMT

It it only does it when I enable editing

Re: Account Aging Metrics / Utilization spreadsheet

Anonymous — Mon, 28 Sep 2020 01:20:59 GMT

@ccquest #NAME?

it's doing it on every one of the aging cells in both files

Re: Account Aging Metrics / Utilization spreadsheet

Anonymous — Mon, 28 Sep 2020 01:32:39 GMT

I went through the evaluate formula and it appears to be the switch command on the first one and the miniFS on the second one

Re: Account Aging Metrics / Utilization spreadsheet

ccquest — Mon, 28 Sep 2020 01:37:40 GMT

@Anonymous what are you using to edit? I'm not seeing any on Excel, Google Drive, or Numbers on my Macbook. Numbers doesn't update any of the calculations at the top because it doesn't support the function though.

edit » just tried it on Excel on the Macbook and noticed it's throwing some other stuff at the front on some of them (MINIF, MAXIF) but I don't have a subscription and apparently they've basically blocked my old key so can't really do any testing on there to help you out if that's what you're using. If you can, try Google Sheets instead, sorry.

Re: Account Aging Metrics / Utilization spreadsheet

Anonymous — Mon, 28 Sep 2020 01:39:27 GMT

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

Re: Account Aging Metrics / Utilization spreadsheet

Anonymous — Mon, 28 Sep 2020 01:39:55 GMT

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

Cheers!

Re: Account Aging Metrics / Utilization spreadsheet

ccquest — Mon, 28 Sep 2020 01:44:17 GMT

@Anonymous 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!

Re: Account Aging Metrics / Utilization spreadsheet

Anonymous — Mon, 28 Sep 2020 01:45:49 GMT

What command should be there rather than the ones your named?

Re: Account Aging Metrics / Utilization spreadsheet

ccquest — Mon, 28 Sep 2020 01:53:11 GMT

@Anonymous 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.

Re: Account Aging Metrics / Utilization spreadsheet

Anonymous — Mon, 28 Sep 2020 02:12:13 GMT

@Anonymous, @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]

Re: Account Aging Metrics / Utilization spreadsheet

Anonymous — Mon, 28 Sep 2020 02:31:15 GMT

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!

Re: Account Aging Metrics / Utilization spreadsheet

ccquest — Mon, 28 Sep 2020 02:19:58 GMT

I'll have it ready shortly and also trying to incorporate CassieCard's idea too.

Re: Account Aging Metrics / Utilization spreadsheet

ccquest — Mon, 28 Sep 2020 02:23:34 GMT

@Anonymous give this one a shot please

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

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

Re: Account Aging Metrics / Utilization spreadsheet

Anonymous — Mon, 28 Sep 2020 02:25:33 GMT

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

Re: Account Aging Metrics / Utilization spreadsheet

Anonymous — Mon, 28 Sep 2020 02:27:55 GMT

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