Senior Contributor
bichonmom
Posts: 3,318
Registered: ‎05-05-2011
Re: How To Determine AAoA?

AAoA includes ALL accounts -- open or closed -- that appear on your CR. So, in effect, you have to calc your AAoA separately for each CRA, unless you happen to have all 3 exactly the same.

 

Here's an Excel s/sht that was posted by another poster at an earlier time. I just cut and pasted it into Excel and it has worked for me just great. Here's the post that I saved:

**********************************************

 

I had some spare time so I decided to create an Excel spreadsheet to keep track of my credit accounts (CC's, loans, etc) and it turned out quite nice.  The way I have it set up is that after inputting the CC information (e.g., opening date, balance, CL), the "Age" and "Util" values will automatically be generated.  Also, the formulas toward the bottom of the sheet calculate overall util four different ways (util across all accounts open and closed, util on all open accounts, util on all open and closed revolving accounts, and util on only open revolving accounts).  Also, oldest account age and AAoA are calculated and displayed in months and years.

 

Rather than emailing the spreadsheet, I have copied the formulas in a Microsoft Excel 2007 format and pasted them below.  You should be able to copy and paste the below information directly into Excel 2007 (I don't know if it will work on earlier versions).  When you first paste it into Excel, it will look pretty funky, but if you click the clipboard icon that appears at the bottom right corner of the area you pasted and click "Match Destination Format", it should look much cleaner then.  For the final touches, you'll want to change the format of the "Date Opened" column to a month/year format and then change the "Util" column to a percentage format (right click the top of the column and select "Format Cells").

 

 

Account Name

Date Opened

Age

Balance

Limit

Util

Type

Status

card

40645

=IF(B2<>"",(TODAY()-B2)/31, "")

284

3900

=IF(E2<>"", D2/E2, "")

Revolving

Open

card

38443

=IF(B3<>"",(TODAY()-B3)/31, "")

0

12500

=IF(E3<>"", D3/E3, "")

Revolving

Closed

card

39173

=IF(B4<>"",(TODAY()-B4)/31, "")

0

5500

=IF(E4<>"", D4/E4, "")

Revolving

Open

card

39753

=IF(B5<>"",(TODAY()-B5)/31, "")

0

4000

=IF(E5<>"", D5/E5, "")

Revolving

Closed

card

39814

=IF(B6<>"",(TODAY()-B6)/31, "")

0

5850

=IF(E6<>"", D6/E6, "")

Revolving

Open

card

38657

=IF(B7<>"",(TODAY()-B7)/31, "")

0

2900

=IF(E7<>"", D7/E7, "")

Revolving

Open

card

38657

=IF(B8<>"",(TODAY()-B8)/31, "")

0

2900

=IF(E8<>"", D8/E8, "")

Revolving

Open

card

38657

=IF(B9<>"",(TODAY()-B9)/31, "")

0

3900

=IF(E9<>"", D9/E9, "")

Revolving

Open

card

40026

=IF(B10<>"",(TODAY()-B10)/31, "")

0

6000

=IF(E10<>"", D10/E10, "")

Revolving

Closed

card

40391

=IF(B11<>"",(TODAY()-B11)/31, "")

8500

8500

=IF(E11<>"", D11/E11, "")

Installment

Open

 

 

=IF(B12<>"",(TODAY()-B12)/31, "")

 

 

=IF(E12<>"", D12/E12, "")

 

 

 

 

=IF(B13<>"",(TODAY()-B13)/31, "")

 

 

=IF(E13<>"", D13/E13, "")

 

 

 

 

=IF(B14<>"",(TODAY()-B14)/31, "")

 

 

=IF(E14<>"", D14/E14, "")

 

 

 

 

=IF(B15<>"",(TODAY()-B15)/31, "")

 

 

=IF(E15<>"", D15/E15, "")

 

 

 

 

=IF(B16<>"",(TODAY()-B16)/31, "")

 

 

=IF(E16<>"", D16/E16, "")

 

 

 

 

=IF(B17<>"",(TODAY()-B17)/31, "")

 

 

=IF(E17<>"", D17/E17, "")

 

 

 

 

=IF(B18<>"",(TODAY()-B18)/31, "")

 

 

=IF(E18<>"", D18/E18, "")

 

 

 

 

=IF(B19<>"",(TODAY()-B19)/31, "")

 

 

=IF(E19<>"", D19/E19, "")

 

 

 

 

 

 

 

 

 

 

 

 

=IF(B21<>"",(TODAY()-B21)/31, "")

 

 

=IF(E21<>"", D21/E21, "")

 

 

 

 

=SUM(C2:C21)

=SUM(D221)

=SUM(E2:E21)

=AVERAGE(F2:F21)

(all accounts)

 

 

 

 

 

 

=AVERAGEIF(H2:H21,"Open",(F2:F21))

(all open accounts)

 

 

 

 

 

 

=AVERAGEIF(G2:G21, "Revolving", F2:F21)

(all revolving)

 

 

 

 

 

 

=AVERAGEIFS(F2:F21, H2:H21, "Open", G2:G21, "Revolving")

(open revolving)

 

 

 

 

 

 

 

 

 

 

 

=MAX(C2:C21)

Oldest (in months)

 

 

 

 

 

 

=C22/COUNT(C2:C21)

AAoA (in months)

 

 

 

 

 

 

=C27/12

Oldest (in years)

 

 

 

 

 

 

=C28/12

AAoA (in years)

 

 

 

 

 

 

NOTE: For the overall util formulas to calculate correctly, the value under the "Type" column should be either "Revolving" or "Installment" and the value under the "Status" column should read either "Open" or "Closed".

 

I thought this would be helpful to others so I'm just passing it along.  Let me know if you have any problems getting it to work.

 

~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
EQ FICO 750 | TU FICO 761 (Walmart) | EX FAKO 767 | Goal: 800+

Edits, funky spacing and spelling due to my iPad not getting along with the forum editor!