Strong organizational skills are essential to success in this game we play, and while useful apps like AwardWallet exist to help us log our points balances in various programs, there’s really no better way to track your credit card portfolio than a good old-fashioned Excel spreadsheet.
Over the years, many readers have emailed me to ask how I keep track of all the credit cards that I have open (especially when they’re first starting out along the process), and I received another flood of such questions after publishing last week’s post on my credit card application strategies.
I therefore thought I’d share the spreadsheet I use to track my credit cards as a follow-up to that post, in order to help you stay organized as you conquer the many sweet signup bonuses out there.
By no means is this the most advanced spreadsheet out there, but I think it does a fine job of keeping track of my credit card accounts, how many bonuses I’ve received, how much I’ve paid in annual fees, and which cards I have open and closed at a given moment in time.
Click here to download the spreadsheet (desktop only), and read on below to find out exactly how it works. (I’ve modified some of the information in the spreadsheet for privacy reasons, of course.)
Credit Card Tracking Spreadsheet: Columns
Each row on the spreadsheet represents a separate credit card account, while each column provides information of a certain type.
Let’s run through the various columns of the spreadsheet and the information that they display, starting from Column B at the left. The first 10 columns require you to input information relating to each credit card you have:
-
Date Opened (Column B): The month and year in which I opened the credit card
-
Issuer (Column C): The financial institution that’s issuing the credit card
-
Name (Column D): The name of the credit card (e.g., Business Platinum, Aeroplan Infinite, etc.)
-
Rewards Currency (Column E): The rewards currency associated with the credit card (e.g., Membership Rewards, Aeroplan, etc.)
-
Bonus (Column F): The number of points, in the rewards currency specified in Column D, that’s earned as a signup bonus as part of this credit card application
-
Referral (Column G): The number of points, in the rewards currency specified in Column D, that’s earned as a referral bonus for referring TO this card (typically only used for Amex self-referrals)
-
Minimum Spend (Column H): The minimum spending threshold within a specified period (typically the first three months) that’s required in order to attain the signup bonus
-
Annual Fee (Column I): The annual fee on the credit card
-
FYF (Column J): Whether or not there’s a first-year fee waiver on the credit card offer; if so, a “Y” is entered for “yes”
-
Credits (Column K): Any credits or cash-back that you earn as part of applying for the credit card, such as Great Canadian Rebates, the Amex Platinum $200 travel credit, etc. (details are specified in the “Notes” column at the end)
Then, the next two columns are automatically populated based on your inputs:
-
Net Fee (Column L): The net out-of-pocket spend as a result of applying for the credit card. If you entered “Y” in Column I (indicating a First Year Free offer), this column will simply reflect any credits or cash-back you earned in Column J. If the card wasn’t First Year Free, then this column will take the annual fee in Column H and subtract from it any credits indicated in Column J.
-
Months (Column M): How many months your credit card has been open? A rather complex Excel formula calculates the difference in months between when you opened the credit card and the present day.
Moreover, if you’ve closed the account (as specified in the next column over) then this column will stop counting, and will reflect how many months you had the credit card open over the course of its life.
The formulas for these two columns should populate automatically when you add a new row, but in the event they don’t, you can simply drag them down from the rows above.
When you close a credit card, you enter the date in Date Closed (Column N), and the month and year of the closure date will be displayed here. This value is then used to calculate the number of months that the credit card has been open back in Column M.
There’s also conditional formatting set up here, so that if a value is entered in Column N (i.e., the credit card is closed), then the entire row becomes greyed out to signify a closed account. If there is no value entered in Column N (i.e., the credit card remains open), then the row is displayed normally.
Lastly, there’s Notes (Column O). This is the place to put any notes that are relevant to the account, such as the source of the credits or cash-back you entered in Column K. You could also use this column to record any details about product-switching, retention offers, or really any details that you think are relevant to keep track of.
Credit Card Tracking Spreadsheet: Features
Up top, there are a few “quick facts” about your overall credit card history, including the number of Open Accounts, the number of Closed Accounts, and the total Net Fees Paid. These cells simply draw upon the information you’ve provided in the input field below.
-
Open Accounts: Takes the total number of accounts (based on the number of values in Column B, Date Opened) and subtracts the number of closed accounts from below.
-
Closed Accounts: Takes the number of values in Column N, Date Closed.
-
Net Fees Paid: Takes the sum of values in Column L, Net Fee, and thus captures all the First Year Free offers and cash-back / credits you’ve earned
Note that for the purposes of the Net Fee column and the Net Fees Paid indicator, the spreadsheet assumes you’re cancelling the card after the first year, and thus doesn’t capture any annual fees you pay beyond the first year.
You can also use the drop-down filters in the title row of the table (Row 8) to filter the list of credit cards based on certain criteria. For example, you could filter for all the bonuses you earned in 2017:
Or you could filter for all the bonuses of a specific rewards currency you’ve earned over the years:
Or you could filter out all the closed accounts for a clean view of all the credit cards you currently have open – perhaps in order to keep track of which ones you’ll need to be closing soon:
Lastly, you’ll notice that the spreadsheet is all on one tab (which is labelled “Canada”). Indeed, I like to keep separate tabs for different credit files, you could duplicate the tab for your US credit card portfolio, or for keeping track of your partner’s cards in two-player mode, etc.
Conclusion
That’s basically all the features in the spreadsheet, but I encourage you to download the file and play around with it in order to get a feel for how it works. Feel free to adapt the spreadsheet (add extra columns or delete unnecessary ones) to suit your own needs as well. I hope some of you find this helpful as you look to stay focused and organized while pursuing the many excellent signup bonuses out there.
This spreadsheet has served me well over the years as a clear and easy-to-maintain picture of my credit card portfolio, although I’m sure there are Excel whizzes out there who keep far more comprehensive spreadsheets than my own.
I’d love to hear about your own spreadsheets if you do keep one yourself, and if you have any ideas on features that would be useful if added to my spreadsheet, I’d love to hear those as well – who knows, if there’s demand for it, we could turn this into a nice new resource for Prince of Travel readers!
