Excel Spreadsheet for CC Data

[tpatterson2k9]

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
American Express Blue Cash Everyday	40645	=IF(B2<>"",(TODAY()-B2)/31, "")	284	3900	=IF(E2<>"", D2/E2, "")	Revolving	Open
Chase Amazon Rewards Visa #1	38443	=IF(B3<>"",(TODAY()-B3)/31, "")	0	12500	=IF(E3<>"", D3/E3, "")	Revolving	Closed
Chase Amazon Rewards Visa #2	39173	=IF(B4<>"",(TODAY()-B4)/31, "")	0	5500	=IF(E4<>"", D4/E4, "")	Revolving	Open
Citi Bank Dell Financial Services	39753	=IF(B5<>"",(TODAY()-B5)/31, "")	0	4000	=IF(E5<>"", D5/E5, "")	Revolving	Closed
Sam's Club GEMB Discover	39814	=IF(B6<>"",(TODAY()-B6)/31, "")	0	5850	=IF(E6<>"", D6/E6, "")	Revolving	Open
Schools First FCU #1	38657	=IF(B7<>"",(TODAY()-B7)/31, "")	0	2900	=IF(E7<>"", D7/E7, "")	Revolving	Open
Schools First FCU #2	38657	=IF(B8<>"",(TODAY()-B8)/31, "")	0	2900	=IF(E8<>"", D8/E8, "")	Revolving	Open
Schools First FCU #3	38657	=IF(B9<>"",(TODAY()-B9)/31, "")	0	3900	=IF(E9<>"", D9/E9, "")	Revolving	Open
Sear's Citi Bank Discover	40026	=IF(B10<>"",(TODAY()-B10)/31, "")	0	6000	=IF(E10<>"", D10/E10, "")	Revolving	Closed
Student Loan #1	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.

[clownfico]

How cool! Could you add close(statement) dates? Are you accepting donations? Great stuff, tpatterson2k9!

[unlucky_hove]

Nice!!!!!

[DI]

This is exactly what I need.  Thanks!

[MS00000000]

Impressive, thank you!

[GregB]

Mine also has:

Color Code the row of the account: Bright Red if I'm paying over 10%; Dull Red if I'm paying any interest (carrying a balance); Black if I pay the statment balance on the due date; Blue if I PIF before Statement; Green if it is unused.

Column for Interest Rate and (Interest Rate x Balance) to use in average interest rate calculation

Column for each CRAs latest report with the amount they show. Also the various scores from the various CRAs for those amounts.

How long it takes for the Lender to report to the CRAs

Last Statement Date, Next Statement Date, Date I last updated that account.

 Edited because my new glasses aren't ready yet and I can't tell the difference between a colon and a semicolon

[HikingFICOMountain]

This is awesome. Does anyone have any updates to this?

[kjm79]

Mine also has colums for date reported and for the closed accounts, the estimated fall off date. 

[Anonymous]

Make sure you change the formula for your AAoA and oldest account age, etc.  You need to you "365.25/12" instead of 31.  Not much of a difference for young accounts, but makes quite a difference on very old accounts. (about a full year difference at 30+ years, give or take).

Sorry, Type-A math/science geek.

[Anonymous]

Excel is so much fun, hey good luck with your goal to 800 I'm on the same track!