cancel
Showing results for 
Search instead for 
Did you mean: 

Account Aging Metrics / Utilization spreadsheet

Highlighted
Established Contributor

Account Aging Metrics / Utilization spreadsheet

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.

 

image.png

 

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 @Birdman7 's wonderful General Scoring Primer Master Thread. Also credit to @CassieCard 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).

as of 11/20/20
Current Cards:
Message 1 of 66
65 REPLIES 65
Highlighted
Super Contributor

Re: Account Aging Metrics / Utilization spreadsheet


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

 

image.png

 

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 @Birdman7 's wonderful General Scoring Primer Master Thread. Also credit to @CassieCard for the table idea.


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

-Our Community’s updated scoring wisdom: Link to Scoring Primer.
-For Negative Reason Codes see: CassieCard’s Score Factors thread.
-ccquest’s workbook to calculate metrics for you: Link to Workbook.

Nov 2020, Clean/Thick/Mature/No New Account Scorecard (EX9 not updated yet. Oldest/avg varies. Estimates above.)
Real world mortgage maxes are: EQ5-818, TU4-839, EX2-844.


RIP:
(Everything said is JMHO and is not endorsed by FICO or MF. I have no affiliation with either, just a grateful member.)
Message 2 of 66
Highlighted
Established Contributor

Re: Account Aging Metrics / Utilization spreadsheet

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

as of 11/20/20
Current Cards:
Message 3 of 66
Highlighted
Super Contributor

Re: Account Aging Metrics / Utilization spreadsheet

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

-Our Community’s updated scoring wisdom: Link to Scoring Primer.
-For Negative Reason Codes see: CassieCard’s Score Factors thread.
-ccquest’s workbook to calculate metrics for you: Link to Workbook.

Nov 2020, Clean/Thick/Mature/No New Account Scorecard (EX9 not updated yet. Oldest/avg varies. Estimates above.)
Real world mortgage maxes are: EQ5-818, TU4-839, EX2-844.


RIP:
(Everything said is JMHO and is not endorsed by FICO or MF. I have no affiliation with either, just a grateful member.)
Message 4 of 66
Highlighted
Established Contributor

Re: Account Aging Metrics / Utilization spreadsheet


@Birdman7 wrote:

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


Hmmm, what sort of errors/where?

as of 11/20/20
Current Cards:
Message 5 of 66
Highlighted
Super Contributor

Re: Account Aging Metrics / Utilization spreadsheet

It it only does it when I enable editing
-Our Community’s updated scoring wisdom: Link to Scoring Primer.
-For Negative Reason Codes see: CassieCard’s Score Factors thread.
-ccquest’s workbook to calculate metrics for you: Link to Workbook.

Nov 2020, Clean/Thick/Mature/No New Account Scorecard (EX9 not updated yet. Oldest/avg varies. Estimates above.)
Real world mortgage maxes are: EQ5-818, TU4-839, EX2-844.


RIP:
(Everything said is JMHO and is not endorsed by FICO or MF. I have no affiliation with either, just a grateful member.)
Message 6 of 66
Highlighted
Super Contributor

Re: Account Aging Metrics / Utilization spreadsheet

@ccquest #NAME?

 

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

-Our Community’s updated scoring wisdom: Link to Scoring Primer.
-For Negative Reason Codes see: CassieCard’s Score Factors thread.
-ccquest’s workbook to calculate metrics for you: Link to Workbook.

Nov 2020, Clean/Thick/Mature/No New Account Scorecard (EX9 not updated yet. Oldest/avg varies. Estimates above.)
Real world mortgage maxes are: EQ5-818, TU4-839, EX2-844.


RIP:
(Everything said is JMHO and is not endorsed by FICO or MF. I have no affiliation with either, just a grateful member.)
Message 7 of 66
Highlighted
Super Contributor

Re: Account Aging Metrics / Utilization spreadsheet

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
-Our Community’s updated scoring wisdom: Link to Scoring Primer.
-For Negative Reason Codes see: CassieCard’s Score Factors thread.
-ccquest’s workbook to calculate metrics for you: Link to Workbook.

Nov 2020, Clean/Thick/Mature/No New Account Scorecard (EX9 not updated yet. Oldest/avg varies. Estimates above.)
Real world mortgage maxes are: EQ5-818, TU4-839, EX2-844.


RIP:
(Everything said is JMHO and is not endorsed by FICO or MF. I have no affiliation with either, just a grateful member.)
Message 8 of 66
Highlighted
Established Contributor

Re: Account Aging Metrics / Utilization spreadsheet

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

as of 11/20/20
Current Cards:
Message 9 of 66
Highlighted
Super Contributor

Re: Account Aging Metrics / Utilization spreadsheet

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

-Our Community’s updated scoring wisdom: Link to Scoring Primer.
-For Negative Reason Codes see: CassieCard’s Score Factors thread.
-ccquest’s workbook to calculate metrics for you: Link to Workbook.

Nov 2020, Clean/Thick/Mature/No New Account Scorecard (EX9 not updated yet. Oldest/avg varies. Estimates above.)
Real world mortgage maxes are: EQ5-818, TU4-839, EX2-844.


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