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 10/13/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!

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 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 10/13/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

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 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 10/13/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
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 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

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 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
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 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 10/13/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

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