My Cash Tracking Spreadsheet
In my previous blog post I explained three simple rules to make the stock market work for you. But what about managing your cash? I have solution for that too, a spreadsheet of my own invention that I update twice a month and go over with my wife. I get paid basically the first and fifteenth of every month, so to try to get representative snapshots of our finances I add up all of our cash assets and liabilities — our checking and savings accounts and credit card balances — the seventh and twenty-first of each month. This means the snapshots should be comparable, allowing us to see basic trends. The spreadsheet itself looks like this and has a built-in chart, which we will get to:
Here is the link one more time. The values are completely hypothetical to protect the innocent. Up top you can see totals for Assets — checking account, savings account, Venmo balance, and PayPal balance. I always transfer over any money I receive in Venmo but checking is smart and as for PayPal, one time very randomly I found at least $300 in there, maybe $380! So check it.
Next to Assets we of course have Liabilities, the sum of our credit card balances, which we automatically pay off so we never owe interest. This makes our credit card rewards basically free money.
Side note: Credit card rewards are funded by interchange fees, according to “The Points Guy” in this Vox video, which merchants pay to credit card issuers but really pass on to consumers as higher prices. So people paying with cash or debit are basically subsidizing credit card rewards. Dave Ramsey’s personal financial system apparently revolves around paying cash so you feel every cent, which is fine, but I like credit card rewards and we are quite frugal. In fact I abhor debt.
A couple of final notes about the credit cards, starting with why we have so many: Each has a different cash back policy favoring certain types of purchases. Chasing cash back can feel a little like being paid to shop, which is obviously dangerous. I mostly disregard cash back in our calculations, as well as the gift cards we got for our infant son, but one card’s cash back can be applied continuously to the balance so I do and subtract it out from the balance number I record on the spreadsheet.
Now we get to formulas. Total Assets and Liabilities are simple sums, while the difference between them yields “Net.” So “Net Sans Savings” is Assets minus Liabilities and Savings. This figure is mostly pointless but I like seeing it. At the bottom is a historical table of our balances over time. Note the dates. I recommend getting into the habit of going over your cash at least once a month, probably twice to be safe. We have caught fraudulent credit card charges we might have otherwise missed by looking up our balances during a regular financial check.
From left to right in the table we have “Date,” “Net,” “Net Sans Savings,” and my personal favorite, average daily change in net cash. This gives us a clear picture of just how costly a major expense was or when we have spent too quickly. The column is color-coded — green is good, red is bad. “Gain Rank” comes next, ranking how much money we lost or gained on average each day. “Net Rank” answers the basic question, ‘Do we have more cash than ever before?!’ “Debt” is a fairly simple calculation of Liabilities over time — glad I was able to figure that out from the other columns since I did not always record it in the table! Finally we have the moving average of Net Rank for six periods, which for us is a month-and-a-half. This is an abuse of ordinal data, but again gives us a decent general picture of our recent financial health. Each time I add a new row to the spreadsheet I have to tweak the ranking formulas to make sure they cover the correct range, but I find it pretty easy to fill out.
You can easily modify the table or spreadsheet to subtract out your monthly rent or mortgage payment, which is most people’s biggest expense. My wife’s favorite part is the chart. In her words, “The visual depiction of the money.”
The chart automatically updates with each new row added to the table. My closing thought is on how much money to keep in checking and savings. The conventional guidance I found from Googling recommends keeping more money in your checking and savings accounts than honestly makes sense to me, given the opportunity cost of not investing it, but what do I know? My spreadsheet is not currently set up to track our investments, but I would not recommend fixating on how your stocks are doing, for reasons covered in my other post. I hope you find this helpful and welcome your feedback.
