My Tools: Credit Card & Bank Bonus Spreadsheet for Tracking All of the Information

One of the keys to churning credit cards is organization. When is your payment due? Have you hit the minimum spend? It’s easy to keep track of three cards, but ten or twenty is far more difficult. I created a spreadsheet to help me track all non-category-related information associated with my credit cards, and I think you may find it useful.

There are four separate sheets: credit cards, better bonus tracking, bank bonuses, and credit score. Most information is fabricated, so don’t fret about that 37% utilization! There is no one-size-fits-all approach, so you will likely want to change things to suit your personal aesthetic or needs: make a copy (File > Make a copy) and go to town!

Direct link to spreadsheet (Google Spreadsheet).

Most of it is pretty self-explanatory (notes are included in the spreadsheet), but it does make heavy use of conditional formatting, whose workings are not immediately obvious. Below is a detailed explanation of how it works.

Credit Cards Sheet

This sheet is both the most complex, the best designed, and the most useful of the four sheets. You input information in rows 3 and below. Row 1 is for titling & row 2 is for some calculations (explained in detail later). The detailed structure is very detailed, and is only worth skimming if you don’t understand something on the spreadsheet. You likely do not need to read it at all.

Detailed Structure

• Columns A thru F: card information.
  • Columns A & B are for the issuing bank and the name of the card. I prefer to include the full name without abbreviations, so column B is much wider than you might otherwise make it. If you have a small screen, I recommend reducing the width.
  • Column C is for the card network: M for MasterCard, V for Visa, A for American Express, etc.
  • Column D is for the credit limit.
  • Column E is for what statement closing amount would be under 20% utilization. (This number is calculated as 19.44% of the credit limit.)
  • Column F is for what the utilization would be if the statement closed right now, which is calculated based on the current balance from column I.
  • Columns E & F do not display a utilization for charge cards or business cards. (Business cards are defined as those with “business” in the name; for business cards that do report on your personal credit report, you will need to adjust either the card name or search parameter.)
• Columns G thru L: statement & payment information.
  • Columns G & J, respectively, contain the closing date and payment due date of the card, entered as a number (for the day). Normally, these columns will be hidden, but you will likely need to unhide them to change your closing date occasionally (especially if you have American Express cards).
  • Columns H & K, respectively, smartly display the day + month of the closing and payment due date based on the information in columns G & J, using the information from cells A2, B2, and C2. The day + current month is displayed until the statement closes, and then it rolls over to the day + next month.
  • Columns I & L, respectively, are for the current balance and current statement balance. I find this useful for tracking my overall balance without having to open Mint. I usually delete the value in column L once my payment has posted, in order to help me better keep track of payment.
• Columns M thru N: payment scheduling.
  • Column M is the amount of the payment you have scheduled. I delete this value once my payment has posted.
  • Column N is the date of the payment you have scheduled. I change this to the day after the next statement closing date once payment has posted. I prefer the aesthetic of day + month (abbreviated to 3 letters), but you can format this as anything you wish. 91.66% of the year you can just type “6 Feb” and have it automatically counted, but be careful when scheduling payments in December: you’ll need to include the next year or it will default to the current year.
• Columns O thru R: pricing & fees information.
  • Columns O & P, respectively, are for the APR and the date that the APR ends (if it is a promotional APR).
  • Columns Q & R, respectively, are for the annual fee (if any) and the date the annual fee has been assessed (or will be assessed, if it is waived the first year). I like to bold annual fees that have already been assessed.
• Columns S thru V: minimum spend information, if any.
  • Column S is the signup bonus. If it is a multi-state signup bonus, I condense it into one. For example, I signed up for the 50k MR / $2k spend Premier Rewards Gold card and put 50k MR in this column. After meeting minimum spend, I did the authorized user bonus of 5k MR / $500 spend and put 55k MR in this column. Notes are helpful here.
  • Column T is whether the signup bonus has been met. Y is for yes, N is for no, and N/A is for not applicable.
  • Column U is the minimum spend required for the signup bonus. This is also condensed into one for multi-stage bonuses; using the previous, it was $2,000 to start and then changed to $2,500 once I did the AU bonus. Notes are helpful here.
  • Column V is the amount of spend that counts towards minimum spend (i.e. excluding annual fees), manually tracked. It is deleted once the signup bonus is met. This is also condensed into one for multi-stage bonuses; using the previous, it was $0 to start, variable amounts while meeting the signup bonus, deleted once I received the signup bonus, changed to $2,000 once I started the AU bonus, variable amounts while meeting the signup bonus, and then deleted once I received the AU bonus. Notes are helpful here.
• Columns W thru Z: card open date & bonus posting date.
  • Column W is the card open date.
  • Column X is the date the bonus posted on the card, if any.
  • Column Y is the date the card was closed, if any.
  • Column Z is the date of the last charge. Conditional formatting is used to highlight dates more than a certain number of days in the past, as defined by cell Z2.

Row 2

Row 2 is where many important calculations are kept. Normally, this row will be hidden, but you may find that there is some information in there worth keeping visible at all times. You can keep it unhidden, or insert a row below and move the information you find useful there.

• Cells A2 thru C2 are used to smartly display the statement closing date & payment due date. A2 is the current month, abbreviated to 3 letters, B2 is the next month, abbreviated to 3 letters, and C2 is the current day, as a number. A2 & B2 are formatted using the TEXT() function. The format “M” corresponds to a month’s number, with no leading zero. The format “MM” corresponds to a month’s number, with a leading zero. The format “MMM” (which I use) corresponds to a month’s name abbreviated to 3 letters. The format “MMMM” correspond to a month’s full name.
• Cell D2: total credit limit.
• Cells E2 & H2: respectively, used to conditionally format columns F & G and columns I & J. See Conditional Formatting.
• Cells G2, J2, & K2: respectively, total current balance, total current statement balance, and total amount of scheduled payments.
• Cell M2: total current balance subject to 0% APR.
• Cell N2: used to conditionally format column N. See Conditional Formatting.
• Cell R2: percent of cards you have met minimum spend on. It excludes cards without signup bonuses (N/A).
• Cell S2: amount of minimum spend remaining across all cards.
• Cells U2 & V2: respectively, the number of cards and number of bonuses you have received in the last 24 months. This uses the EDATE() function; changing “-24” to some other number will change the amount of months the calculation looks back.

Conditional Formatting & Coloring

Let it be known: I love conditional formatting. I encourage you to change the coloring to fit your personal aesthetic. I also happen to like borders, so I use them to visually break up information. You may wish to dump them entirely or just to start, since they can be a PITA to work with when inputting lots of new information.

• Columns A thru D: not conditionally formatted, but colored based on the issuing bank. I feel that it helps to visually segment the sheet and make it easier to scan.
• Columns F & G: conditionally formatted as light yellow 3 if the statement closes within the number of days defined by cell E2 & as light green 3 if the statement closed within the number of days defined by cell E2.
• Columns I & J: conditionally formatted as light red berry 3 if payment is due within the number of days defined by cell H2.
• Column L: conditionally formatted as light red berry 1 if the payment schedule date is in the past.
• Column M: conditionally formatted as light green 3 if the APR is 0%.
• Column N: conditionally formatted as dark yellow 1 if the 0% APR end-date is within the number of days defined by cell N2 & as light red berry 1 if the end-date has passed.
• Column O: conditionally formatted as light red berry 3 if there is an annual fee.
• Column Q: conditionally formatted as light green 3 if minimum spend has been met or isn’t applicable (any text but “N” in column R).
• Column R: conditionally formatted as light red berry 1 if minimum spend has not been met (text “N” in column R).

&fin.

And that’s all there is to it!

Better Bonus Tracking Sheet

This sheet is rather self-explanatory and there are a few notes that cover things. I manually track everything by category, which is overkill, and it’s certainly not the greatest design. In my personal spreadsheet, I’ve added a table for fees & credits to give me a better idea of how much I’ve lost or gained on the year; it works the exact same as the rest of the tables. It’s called “Better Bonus Tracking” because the first method I used was even worse!

Bank Bonuses Sheet

This sheet is a distant second in complexity, but I find it very useful. While I like the way information is displayed, there is one downside: direct deposit and fee-free conditions are kept in notes instead of immediately visible. You may wish to take a different approach if you are churning many bank accounts.

Detailed Structure

• Columns A & B, respectively, are for the bank and account name.
• Columns C, D, & E, respectively, are for the date of application, the date of account approval, and the date of account closure.
• Columns F & G, respectively, are for the amount of the bonus and the date the bonus posted. I like to bold the values in these columns once the bonus has been met.
• Columns H, I, & J, respectively, are for whether the bonus has been met, the direct deposit amount (if any), and the amount of direct deposits to the account. A note details the direct deposit requirements in column I.
• Columns K is for the amount of the monthly service fee. A note in this column details all the ways to keep the account fee-free.
• Column L & subsequent columns are per-month and are whether the fee-free condition has been met for the statement cycle ending in that month.

Conditional Formatting & Coloring

Again, I encourage you to change the coloring to fit your personal aesthetic.

• Columns A thru B: not conditionally formatted, but colored based on the bank.
• Column L & subsequent columns: conditionally formatted as black if there is no service fee (“N/A” in column K) or if there is an “X” in the cell (I use this for accounts that weren’t open) & light red berry 3 if the fee-free condition has not been met for the month (by placing “Y” in the cell). Nothing is conditionally formatted if there is no text in the cell, so manual tracking is important.

Credit Score Sheet

This sheet is also rather self-explanatory and certainly won’t win any awards for graphic design. I keep track of my monthly (FICO) credit score here in addition to the graph-over-time tools that issuers have, so that I always have the information. In my personal spreadsheet, I have added extra columns for “random” credit scores—i.e. the credit scores I get when I am denied or approved for a credit card. I like to format dates in this sheet as month + year.

 

Questions? Comments? Feedback? Have your own tools to share? Reply below!