cancel
Showing results for 
Search instead for 
Did you mean: 

Account Aging Metrics / Utilization spreadsheet

mickvain
Regular 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- 7 - AAoA - 4.6 - Clean Profile - 23% UTI - 10k/44k Limits

Message 11 of 70
ccquest
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 4/1/21
Current Cards:
Message 12 of 70
Birdman7
Super Contributor

Re: Account Aging Metrics / Utilization spreadsheet

What command should be there rather than the ones your named?
-Scoring wisdom: Credit Scoring Primer, Payment History, Amount of Debt, Length of History/New Credit, Mix/Disputes/Freezes/Searches, Mortgage Scores/Negative Reason Codes/V.9, Helpful Links,
Anonymous’s Reason Statements Thread,
iv's friendly statement to reason statement concordance,

ccquest’s Workbook to calculate metrics.
Clean/Thick/Mature/No New Revolver - 8,9; Clean/Thick/Mature/No New Account - 5/4/2.
Correct Ag.Util. under 5% all times. (Oldest/avg varies. Estimates above.)
Real world mortgage maxes are: EQ5-818, TU4-839, EX2-844.




(Everything said is JMHO and is not endorsed by FICO or MF. I have no affiliation with either, just a grateful member.)
Message 13 of 70
ccquest
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 4/1/21
Current Cards:
Message 14 of 70
Anonymous
Not applicable

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

Message 15 of 70
Birdman7
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!

-Scoring wisdom: Credit Scoring Primer, Payment History, Amount of Debt, Length of History/New Credit, Mix/Disputes/Freezes/Searches, Mortgage Scores/Negative Reason Codes/V.9, Helpful Links,
Anonymous’s Reason Statements Thread,
iv's friendly statement to reason statement concordance,

ccquest’s Workbook to calculate metrics.
Clean/Thick/Mature/No New Revolver - 8,9; Clean/Thick/Mature/No New Account - 5/4/2.
Correct Ag.Util. under 5% all times. (Oldest/avg varies. Estimates above.)
Real world mortgage maxes are: EQ5-818, TU4-839, EX2-844.




(Everything said is JMHO and is not endorsed by FICO or MF. I have no affiliation with either, just a grateful member.)
Message 16 of 70
ccquest
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 4/1/21
Current Cards:
Message 17 of 70
ccquest
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 @Anonymous suggested yet though.

as of 4/1/21
Current Cards:
Message 18 of 70
Birdman7
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
-Scoring wisdom: Credit Scoring Primer, Payment History, Amount of Debt, Length of History/New Credit, Mix/Disputes/Freezes/Searches, Mortgage Scores/Negative Reason Codes/V.9, Helpful Links,
Anonymous’s Reason Statements Thread,
iv's friendly statement to reason statement concordance,

ccquest’s Workbook to calculate metrics.
Clean/Thick/Mature/No New Revolver - 8,9; Clean/Thick/Mature/No New Account - 5/4/2.
Correct Ag.Util. under 5% all times. (Oldest/avg varies. Estimates above.)
Real world mortgage maxes are: EQ5-818, TU4-839, EX2-844.




(Everything said is JMHO and is not endorsed by FICO or MF. I have no affiliation with either, just a grateful member.)
Message 19 of 70
Birdman7
Super Contributor

Re: Account Aging Metrics / Utilization spreadsheet

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

-Scoring wisdom: Credit Scoring Primer, Payment History, Amount of Debt, Length of History/New Credit, Mix/Disputes/Freezes/Searches, Mortgage Scores/Negative Reason Codes/V.9, Helpful Links,
Anonymous’s Reason Statements Thread,
iv's friendly statement to reason statement concordance,

ccquest’s Workbook to calculate metrics.
Clean/Thick/Mature/No New Revolver - 8,9; Clean/Thick/Mature/No New Account - 5/4/2.
Correct Ag.Util. under 5% all times. (Oldest/avg varies. Estimates above.)
Real world mortgage maxes are: EQ5-818, TU4-839, EX2-844.




(Everything said is JMHO and is not endorsed by FICO or MF. I have no affiliation with either, just a grateful member.)
Message 20 of 70
Advertiser Disclosure: The offers that appear on this site are from third party advertisers from whom FICO receives compensation.