topic Re: More Excel Spreadsheet Fun for CC Data in Credit Cards

More Excel Spreadsheet Fun for CC Data

tpatterson2k9 — Sat, 23 Jul 2011 08:15:56 GMT

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, "")

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, "")

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, "")

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, "")

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, "")

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, "")

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, "")

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, "")

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, "")

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

=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(D221)

=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!

Re: More Excel Spreadsheet Fun for CC Data

tpatterson2k9 — Sat, 23 Jul 2011 08:22:05 GMT

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).

Re: More Excel Spreadsheet Fun for CC Data

Anonymous — Sat, 23 Jul 2011 09:13:36 GMT

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!

Re: More Excel Spreadsheet Fun for CC Data

mark925 — Sat, 23 Jul 2011 09:25:09 GMT

Why not just upload the spreadsheet to a free file hosting service like MegaUpload or FileDropper and just post the link to it here?

Re: More Excel Spreadsheet Fun for CC Data

clownfico — Sat, 23 Jul 2011 10:47:43 GMT

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.

Re: More Excel Spreadsheet Fun for CC Data

tpatterson2k9 — Sat, 23 Jul 2011 14:43:00 GMT

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.

Re: More Excel Spreadsheet Fun for CC Data

Anonymous — Sat, 23 Jul 2011 15:13:38 GMT

For my excel spreadsheet I have different tabs : "Accounts" which is similar to your post, Inquiries, AAoA which runs a macro to calculate my average age based on todays date, Inquires vs. AAoA which just compares the two tabs side by side, Loan Amortization, Utilization an idea i got from another poster, and Total Debt so I can see the whole picture installment + credit card.

Thanks for the post OP! you've inspired me even more.. lol.. i'm going to design a new tab for due date stuff... I'm thinking like a check box in one cell so I will check it on the day it's paid and it will populate the date in "date paid" column.

Re: More Excel Spreadsheet Fun for CC Data

DI — Sat, 23 Jul 2011 15:58:09 GMT

@tpatterson2k9 wrote:
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.

Thank you very much!! You are a genius. I'm creating a spreadsheet to match each of my credit reports. I just completed EQ since it has less data to input.

Re: More Excel Spreadsheet Fun for CC Data

blaknnj — Sat, 23 Jul 2011 18:41:57 GMT

@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 :-)

Re: More Excel Spreadsheet Fun for CC Data

DI — Sun, 24 Jul 2011 19:05:59 GMT

Thanks again for the template. This has helped me tremendously. I feel like I have an edge over the credit system.

Re: More Excel Spreadsheet Fun for CC Data

tpatterson2k9 — Sun, 24 Jul 2011 19:11:41 GMT

@DI wrote:
Thanks again for the template. This has helped me tremendously. I feel like I have an edge over the credit system.

No problem. The next task I have set for myself is to create a regression equation to predict credit scores based on the same information that makes up FICO scores. It makes me wonder how close I could get to actual FICOs more so than many of the FAKO scoring systems out there.

Re: More Excel Spreadsheet Fun for CC Data

DI — Sun, 24 Jul 2011 19:14:34 GMT

@tpatterson2k9 wrote:
@DI wrote:
Thanks again for the template. This has helped me tremendously. I feel like I have an edge over the credit system.

No problem. The next task I have set for myself is to create a regression equation to predict credit scores based on the same information that makes up FICO scores. It makes me wonder how close I could get to actual FICOs more so than many of the FAKO scoring systems out there.

Oh wow! If you do that, you may receive job offers from Innovis!