Okay folks, so I added more onto the previous Excel spreadsheet I posted here previously to keep track of CC data. In this one I added columns for "Statement Date" (when your new statement is generated, thanks to clownfico for the suggestion), "Due Date" (when your next payment is due), and "DOFD" (Date of First Delinquency, if there is one). The three "Reminder" columns correspond to the three columns just mentioned. That is, for statement and due date reminders, once the current date is 5 days from the statement/due date, text will appear under the corresponding column and TL reminding you that the statement will be released soon or that your payment will be due soon. The "Derog Reminder" column simply reminds you when your Date of First Delinquency is 1 year or less from being dropped off your CRs (assuming 7 year length). The last little bit of information I added was another overall util calculation labeled "all FICO scored." Unlike the other four util formulas, the FICO scored util is calculated based on all open revolving CCs and all closed revolving CCs that have a balance greater than $0 (someone please correct me if this is not how FICO calculates util). I think I'm having too much fun with Excel...
Anyway, please refer to my original post here regarding how to easily copy the below formulas into Excel so you can use the same spreadsheet without me having to email it to anyone (the below is a template based on my info, use it as a guide to fill yours in). I did confirm however, that this spreadsheet is fully functional only in Excel 2007 due to some new formulas Microsoft added in 2007 that weren't in 2003.
| Account Name | Opened | Age | Balance | Limit | Util | Type | Status | Statement Date | Due Date | DOFD | Statement Reminders | Payment Reminders | Derog Reminders (assume 7 yr length) |
| American Express Blue Cash Everyday | 40645 | =IF(B2<>"",(TODAY()-B2)/31, "") | 60 | 3900 | =IF(E2<>"", D2/E2, "") | Revolving | Open | 40751 | 40746 | =IF(I2<>"", IF(I2-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D2>0, J2<>""), IF(J2-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K2<>"", IF(TODAY()-K2>2190,"Derog to drop off in 1 or less years", ""), "") | |
| Chase Amazon Rewards Visa #1 | 38443 | =IF(B3<>"",(TODAY()-B3)/31, "") | 0 | 12500 | =IF(E3<>"", D3/E3, "") | Revolving | Closed | =IF(I3<>"", IF(I3-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D3>0, J3<>""), IF(J3-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K3<>"", IF(TODAY()-K3>2190,"Derog to drop off in 1 or less years", ""), "") | |||
| Chase Amazon Rewards Visa #2 | 39173 | =IF(B4<>"",(TODAY()-B4)/31, "") | 0 | 5500 | =IF(E4<>"", D4/E4, "") | Revolving | Open | 40766 | 40764 | =IF(I4<>"", IF(I4-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D4>0, J4<>""), IF(J4-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K4<>"", IF(TODAY()-K4>2190,"Derog to drop off in 1 or less years", ""), "") | |
| Citi Bank Dell Financial Services | 39753 | =IF(B5<>"",(TODAY()-B5)/31, "") | 0 | 4000 | =IF(E5<>"", D5/E5, "") | Revolving | Closed | =IF(I5<>"", IF(I5-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D5>0, J5<>""), IF(J5-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K5<>"", IF(TODAY()-K5>2190,"Derog to drop off in 1 or less years", ""), "") | |||
| Sam's Club GEMB Discover | 39814 | =IF(B6<>"",(TODAY()-B6)/31, "") | 0 | 5850 | =IF(E6<>"", D6/E6, "") | Revolving | Open | 40766 | =IF(I6<>"", IF(I6-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D6>0, J6<>""), IF(J6-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K6<>"", IF(TODAY()-K6>2190,"Derog to drop off in 1 or less years", ""), "") | ||
| Schools First FCU #1 | 38657 | =IF(B7<>"",(TODAY()-B7)/31, "") | 0 | 2900 | =IF(E7<>"", D7/E7, "") | Revolving | Open | 40751 | 40776 | =IF(I7<>"", IF(I7-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D7>0, J7<>""), IF(J7-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K7<>"", IF(TODAY()-K7>2190,"Derog to drop off in 1 or less years", ""), "") | |
| Schools First FCU #2 | 38657 | =IF(B8<>"",(TODAY()-B8)/31, "") | 0 | 2900 | =IF(E8<>"", D8/E8, "") | Revolving | Open | 40751 | 40776 | =IF(I8<>"", IF(I8-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D8>0, J8<>""), IF(J8-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K8<>"", IF(TODAY()-K8>2190,"Derog to drop off in 1 or less years", ""), "") | |
| Schools First FCU #3 | 38657 | =IF(B9<>"",(TODAY()-B9)/31, "") | 0 | 3900 | =IF(E9<>"", D9/E9, "") | Revolving | Open | 40751 | 40776 | =IF(I9<>"", IF(I9-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D9>0, J9<>""), IF(J9-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K9<>"", IF(TODAY()-K9>2190,"Derog to drop off in 1 or less years", ""), "") | |
| Sear's Citi Bank Discover | 40026 | =IF(B10<>"",(TODAY()-B10)/31, "") | 0 | 6000 | =IF(E10<>"", D10/E10, "") | Revolving | Closed | =IF(I10<>"", IF(I10-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D10>0, J10<>""), IF(J10-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K10<>"", IF(TODAY()-K10>2190,"Derog to drop off in 1 or less years", ""), "") | |||
| Student Loan #1 | 40391 | =IF(B11<>"",(TODAY()-B11)/31, "") | 8500 | 8500 | =IF(E11<>"", D11/E11, "") | Installment | Open | =IF(I11<>"", IF(I11-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D11>0, J11<>""), IF(J11-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K11<>"", IF(TODAY()-K11>2190,"Derog to drop off in 1 or less years", ""), "") | |||
| =IF(B12<>"",(TODAY()-B12)/31, "") | =IF(E12<>"", D12/E12, "") | =IF(I12<>"", IF(I12-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D12>0, J12<>""), IF(J12-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K12<>"", IF(TODAY()-K12>2190,"Derog to drop off in 1 or less years", ""), "") | |||||||||
| =IF(B13<>"",(TODAY()-B13)/31, "") | =IF(E13<>"", D13/E13, "") | =IF(I13<>"", IF(I13-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D13>0, J13<>""), IF(J13-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K13<>"", IF(TODAY()-K13>2190,"Derog to drop off in 1 or less years", ""), "") | |||||||||
| =IF(B14<>"",(TODAY()-B14)/31, "") | =IF(E14<>"", D14/E14, "") | =IF(I14<>"", IF(I14-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D14>0, J14<>""), IF(J14-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K14<>"", IF(TODAY()-K14>2190,"Derog to drop off in 1 or less years", ""), "") | |||||||||
| =IF(B15<>"",(TODAY()-B15)/31, "") | =IF(E15<>"", D15/E15, "") | =IF(I15<>"", IF(I15-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D15>0, J15<>""), IF(J15-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K15<>"", IF(TODAY()-K15>2190,"Derog to drop off in 1 or less years", ""), "") | |||||||||
| =IF(B16<>"",(TODAY()-B16)/31, "") | =IF(E16<>"", D16/E16, "") | =IF(I16<>"", IF(I16-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D16>0, J16<>""), IF(J16-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K16<>"", IF(TODAY()-K16>2190,"Derog to drop off in 1 or less years", ""), "") | |||||||||
| =IF(B17<>"",(TODAY()-B17)/31, "") | =IF(E17<>"", D17/E17, "") | =IF(I17<>"", IF(I17-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D17>0, J17<>""), IF(J17-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K17<>"", IF(TODAY()-K17>2190,"Derog to drop off in 1 or less years", ""), "") | |||||||||
| =IF(B18<>"",(TODAY()-B18)/31, "") | =IF(E18<>"", D18/E18, "") | =IF(I18<>"", IF(I18-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D18>0, J18<>""), IF(J18-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K18<>"", IF(TODAY()-K18>2190,"Derog to drop off in 1 or less years", ""), "") | |||||||||
| =IF(B19<>"",(TODAY()-B19)/31, "") | =IF(E19<>"", D19/E19, "") | =IF(I19<>"", IF(I19-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D19>0, J19<>""), IF(J19-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K19<>"", IF(TODAY()-K19>2190,"Derog to drop off in 1 or less years", ""), "") | |||||||||
| =IF(I20<>"", IF(I20-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D20>0, J20<>""), IF(J20-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K20<>"", IF(TODAY()-K20>2190,"Derog to drop off in 1 or less years", ""), "") | |||||||||||
| =IF(B21<>"",(TODAY()-B21)/31, "") | =IF(E21<>"", D21/E21, "") | =IF(I21<>"", IF(I21-TODAY()<=5,"Statement cuts in 5 or less days", ""), "") | =IF(AND(D21>0, J21<>""), IF(J21-TODAY()<=5,"Payment due in 5 or less days", ""), "") | =IF(K21<>"", IF(TODAY()-K21>2190,"Derog to drop off in 1 or less years", ""), "") | |||||||||
| =SUM(C2:C21) | =SUM(D2 21) | =SUM(E2:E21) | =AVERAGE(F2:F21) | (all accounts) | |||||||||
| =AVERAGEIF(H2:H21,"Open",(F2:F21)) | (all open accounts) | ||||||||||||
| =AVERAGEIFS(F2:F21, G2:G21, "Revolving", D221, ">0") | (all FICO scored) | ||||||||||||
| =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) | ||||||||||||
| =C28/12 | Oldest (in years) | ||||||||||||
| =C29/12 | AAoA (in years) |
If anyone has any other suggestions about what else they'd like to see in this spreadsheet please let me know and I will try to accommodate. Your feedback is much appreciated!
Starting Scores (lender pull 3/27/10): 759 (EQ), 752 (TU), 749 (EX)Well it appears that I might need to send the file to those who are interested after all because after posting the formula information, I tried recopying it into Excel and got a circular reference error. The spreadsheet automatically updates the age of each of your accounts, the util on each of your accounts, reminds you of due dates (as long as you input the right dates) and automatically generates different util calculations and AAoA information to make it very easy to compare changes in AAoA when you add/delete TLs. Sorry I couldn't get it to work right from the post like I did before (I got some message that the HTML in my post had to be edited so that might have screwed things up).
Mind if I make a suggestion? 
You could submit your awesome template to Microsoft Office as a "community template", and link to it. That way, even more people would have access to it, and it'd be searchable in this forum (if you post the link). You wouldn't have to keep PMing it, that way.
Just a thought!
Wow, infinitely awesome, tpatterson2k9!
@tpatterson2k9: If you have a GMAIL account, you could simply copy it there or upload it, then make the spreadsheet publicly available.
Thanks for the suggestions of how to get this template to you guys. I went with a file hosting site (MediaFire) so here is the link to download the spreadsheet:
http://www.mediafire.com/?b5la46o2l4l56r1
Let me know if anyone has problems accessing it.
@tpatterson2k9 wrote:Well it appears that I might need to send the file to those who are interested after all...
If you don't mind, can you email it to me at ____. I think this spreadsheet is great. Thank you for sharing it with us.
EDIT: Oops never mind...I see we can download from a file sharing site...removed email address. Guess I should have read the entire thread first :-)
Thanks again for the template. This has helped me tremendously. I feel like I have an edge over the credit system.