cancel
Showing results for 
Search instead for 
Did you mean: 

Account Aging Metrics / Utilization spreadsheet

tag
Anonymous
Not applicable

Re: Account Aging Metrics / Utilization spreadsheet


@ccquest wrote:

Alright, here's the latest version everybody!

 

This should work for all versions and shows the age in a bit better format thanks to recommendation of Cassie.

 

image.png

 

Hope it works better for everyone and thanks for the feedback!


That looks nice! I'll take a look inside.

 

Now that you've given birth to it, you're stuck with raising it for a minimum of 18 years!

 

"Every program attempts to expand until it can read mail. Those programs which cannot so expand are replaced by ones which can."

- Zawinski's Law of Software Envelopment (Link)

Message 31 of 70
Anonymous
Not applicable

Re: Account Aging Metrics / Utilization spreadsheet

So far so good looks like an awesome creation!
Message 32 of 70
Anonymous
Not applicable

Re: Account Aging Metrics / Utilization spreadsheet

There is an error in the AAoOIA formula in cell H9. It returns #DIV/0! if there are no Open Installment accounts. (The Average function didn't find any Open Installment accounts - 0/0.)

 

Use this formula in H9 instead:

=INT(IFERROR(AVERAGEIFS(J18:J200,B18:B200,"Installment", C18:C200, "Open"),0)/12)&"yr "&ROUND(MOD(IFERROR(AVERAGEIFS(J18:J200,B18:B200,"Installment", C18:C200, "Open"),0),12),0)&"mo"

 

IL TOTAL LIMIT will be $0.00 with no OR closed installment accounts on file. That will make IL % return a #DIV/0! error.

 

Use this formula in F15:

=IF(H15=0,0,G15/H15)

 

This workbook can be used by anyone with Excel 2007 or later, if the total dollar amount cell formulas are changed to use SUMIFS.

 

It looks like this for REV $, cell G13  (change range criteria for the other values):

=SUMIFS(F18:F200,B18:B200,"Revolver", C18:C200, "Open")

 

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, citeria2)

Matching Revolver and Open here. Cell H15 (IL TOTAL LIMIT) would need to match Installment and Open, and so on for the other cells.

Message 33 of 70
ccquest
Established Contributor

Re: Account Aging Metrics / Utilization spreadsheet

That's what happens when you build it just based on your own case and don't bother testing others Smiley Tongue

I've added more logic based on whether or Installment and Revolvers are present and/or open. Stayed away from SUMIFS still because the Microsoft site doesn't list it as supported in 2007, earliest is "Excel Starter 2010" but doesn't even mention 2013 or 16.

This version overwrote the one from before so link should take you to the latest still.
as of 1/1/23
Current Cards:
Message 34 of 70
Anonymous
Not applicable

Re: Account Aging Metrics / Utilization spreadsheet

@ccquestYou can bet if Cassie told you SUMIFS works on 2007, it will! 😉 But, I’ve got 2016, so I am great now!! I can’t wait to have time to put my data in! Hopefully most ppl have '16 or higher anyway. If not, upgrade might be worth it. 

I think it’s awesome! what did you change in the latest version? I made the two changes Cassie suggested, H9 & F15. But tell us what goodies you added re: logic!

Im excited! If we can get people to use this, we can get accurate data instead of the skewed stuff from the frontends, it’ll make it so much easier to solve people’s issues and collect accurate dps!

Message 35 of 70
ccquest
Established Contributor

Re: Account Aging Metrics / Utilization spreadsheet

Patch Notes:

- Bug fixes and improvements

 

The end.

 

Just kidding, hate those ones! The ones off the top of my head are:

  • Filled in formulas for H18:J200. I hadn't done that before and only noticed when I was going back to test the issues Cassie brought up. These will show blank if other relevant columns in A:G are not filled in.
  • Changed conditional formatting to only apply alternate banding through row 200. This should make it a little lighter to load, sometimes too many formatting rules slows things down.
  • Under All Accounts:
    • For top line, added an IF-COUNTA combo to make sure there are accounts listed at all, if not stay blank.
    • For revolving accounts, added COUNTIF to make sure there are Revolvers listed, otherwise stay blank.
    • For installment, same as above.
    • For aggregate %, added an IF-COUNTA combo to first make sure there is any account listed. If there isn't, remain blank.
  • For Open Accounts:
    • IF-COUNTIF combos to check if there are accounts listed with an Open status, if not stay blank.

 

Think that covers them...for the %s it may have been more direct to check if the Total Limit in columns D or H were $0 and just blank it out, but I wanted to have similar logic throughout all of them that looks at the data instead of other calculations.

as of 1/1/23
Current Cards:
Message 36 of 70
Anonymous
Not applicable

Re: Account Aging Metrics / Utilization spreadsheet


@ccquest wrote:

That's what happens when you build it just based on your own case and don't bother testing others Smiley Tongue

That's exactly how I build most Windows apps for myself - bare minimum, occasional try-catch block, several synchronous functions that lock up the UI thread because too lazy to thread it properly....lol total opposite of what I would do for any corporate application.

 

I've added more logic based on whether or Installment and Revolvers are present and/or open. Stayed away from SUMIFS still because the Microsoft site doesn't list it as supported in 2007, earliest is "Excel Starter 2010" but doesn't even mention 2013 or 16.

This version overwrote the one from before so link should take you to the latest still.


You did great using array formulas!

 

Microsoft lists most functions with a compatibility line of "Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web Excel 2019, Excel 2019 for Mac", but the file compatibility checker in Excel will tell a different story. Aiming for Excel 97-2003 compatibility is painful and just not worth it anyway.

 

Your file is 2007+ compatible as is - I just think it's easier to read SUMIFS functions (introduced with 2007) if I'm changing a cell formula anyway.

 

(@Anonymous also) Should TOTAL LIMIT under ALL ACCOUNTS include a Closed Installment Loan Limit? It's a straight SUM under AGG $ and TOTAL LIMIT.

Message 37 of 70
Anonymous
Not applicable

Re: Account Aging Metrics / Utilization spreadsheet

Change the number of decimal places for any Percentage format cell to 2.

 

I'm setting up my profile to report 2 cards as close to 9.5% as possible without going over.

With a single decimal place, Utilization will show as 9.5% when I actually have 9.45% (189/2000) and 9.49% ( 1234/13000).

 

Less than 9.5% should not cause a jump into the 10%+ interval, based on new info from the AMA.

Message 38 of 70
ccquest
Established Contributor

Re: Account Aging Metrics / Utilization spreadsheet


@Anonymous wrote:

 

(@Anonymous also) Should TOTAL LIMIT under ALL ACCOUNTS include a Closed Installment Loan Limit? It's a straight SUM under AGG $ and TOTAL LIMIT.


That part was done 100% on purpose. I wasn't sure on exactly how closed accounts report and I'm pretty sure I've read it varies based on the account type and maybe lender too. Like some closed revolvers may have a $600 balance left on what was a $2500 limit and report at 100% utilization because it's closed, while others may be 24%. It's just flat total numbers to avoid any misrepresentation there. 

 

There may be some version of aggregate revolver utilization to incorporate though that looks at open accounts and also adds in closed revolvers with balances as if they were always at 100% (so add the balance as a limit instead of even considering the limit).

as of 1/1/23
Current Cards:
Message 39 of 70
Anonymous
Not applicable

Re: Account Aging Metrics / Utilization spreadsheet

We’re not exactly sure how the algorithm handles closed accounts. I think we’re fairly certain it does not show maxed out unless balance chased possibly.

If the account is closed with no balance, the limit should not be included, whether revolving or installment.

If a revolver is closed with a balance, the balance should be included in balances, but for the limits, I’m really uncertain. We could have two cells, one with and one without the closed revolver limit maybe?

I have seen no evidence closed installment loans are treated differently from closed revolvers.

A potential development suggestion. Include an inquiry counter that included dates and type of inquiry.

 

Edited

Message 40 of 70
Advertiser Disclosure: The offers that appear on this site are from third party advertisers from whom FICO receives compensation.