Funny story, I lost about a week to this endeavor so far, but it’s fine. I still might have a functional neuron or two left. Links of note will be listed under Further Reading.
I’m at an age where people in my circles are starting to buy houses. Yeah, houses. When did I get old enough to have that be a thing? I realized that’s going to be me soon, too. It stoked the boilers enough to start professionally lurking in r/personalfinance.
The first order of business was tracking down a budget template. Folks on the finance subreddits have some sexy spreads, complete with forecasts, donut charts, the whole shebang. I jumped in thinking I could modify one for my own uses and call it a day. Easy peasy, right? Oh, you sweet summer’s child. I became overwhelmed almost immediately, quelle surprise.
What should have been the actual first step before any of this was writing down what I want to track. In the beginning, I only wanted to see inflow versus outflow. Then I decided it would be interesting to break down my spending by category. I wrote out some basic categories and subcategories I felt were noteworthy.
Typical of many things I start, it wasn’t long before scope creep crept in. How much money am I dumping in per category monthly? Yearly? How much was I using one credit card vs. the other, and for what? How many subscription services do I have exactly, and how much are they leeching? Using these questions as a launchpad, I got to work.
As it turns out, Excel and Google Sheets have some pretty decent starter templates. I began with Excel’s Simple Monthly Budget template, but ultimately decided I was going to outgrow it fast. I also decided I wanted my workbook to cover the whole year, versus twelve individual tabs or workbooks.
Google Sheets has annual and monthly budget templates as well. I nixed those because I’m trying to limit how much new data I’m feeding into cloud environments, which is a post for another time. Moving on.
I went through at least four attempts to hack an existing template. Alas, it became clear I would have to start from scratch. I settled on the happy medium of modeling my workbook after the Google Sheets Annual Budget template but built in Excel.
I have four active worksheets currently: income, expenses, transactions, and a dashboard for data visualization. I have a few other worksheets that are serving as placeholders for table data and notes, so they’re hidden for now to reduce clutter.
Income is self-explanatory. Here I’ll track my paystubs and any additional sources of income. One trick I’ll deploy is I’m going to dock my own pay. It’s already adjusted to accommodate a 403b and an investment account, what’s a couple more deductions? To start, I’m taking away 30%, which seems to be an acceptable rule of thumb to account for my imaginary home payments. I’m also accounting for my annual subscriptions, so I’ll be taking out 1/12th of those fees as well. To better illustrate, I have Disney+ as an annual bill for ~$70. Every month I’ll take ~$6 out of my paycheck to save for when it rolls around (I fudged these amounts a bit; I expect to adjust over time).
Skipping over to transactions, it’s a checkbook ledger. Here I record every purchase I make, how I paid for it, and what category it’s filed under. Is it more efficient than a paper ledger? Hard to say. This is an important tab though because my expenses tab pulls information from here.
Onwards to the beast that is the expenses tab. This is the meat and potatoes of the entire operation.
I couldn’t help myself, apologies to Sean!
Across the top I have each month. To the left is each category, and their companion subcategories. Underneath each month, there is a total box for each category. Just for fun, there’s also a total box at the end for the entire year, as well as an average per month. Time will tell if that’s useful information or just being extra.
Here’s the 10,000-foot view:
- I record a purchase in the transactions tab.
- Based on how it’s filed, the expenses tab will extract the amount, the category/subcategory, the payment method, and the date.
- It will add the calculations accordingly to the specified category based on the payment method used and the date of the purchase.
Ugh, it makes sense in my head, but I feel like I’m sucking at explaining it. Here’s an example using a purchase I made recently. On 5/12/2020, I bought two CDs for $15.90 using my Mastercard. I filed it under “Entertainment-Music.”
Over in the expenses tab, $15.90 appears under the May column, and the Music row of the Entertainment category. It also counts towards May’s monthly total.
Further down, this charge is added to May’s Mastercard row because I used that specific card within its billing cycle. In theory I can use this section to predict my monthly statement before it’s even posted. Oh yay…
The biggest slap in the face for me was telling Excel how to calculate transactions based on certain criteria between two dates. I used the SUMIFS formula, although I’m certain there is a more efficient method out there. You would think some variables don’t need to be in a certain order, but they do, otherwise it doesn’t work. It’s coding all over again. IT WORKS! I don’t know why. IT DOESN’T WORK! I don’t know why. I can’t tell you how many hours I lost Googling error messages and how the hell to correctly build this so Excel would understand what I wanted.
Last but certainly not least, there’s the dashboard tab. At the time of this writing I only have a chart for total monthly expenditures, and a chart comparing usage between my two credit cards. I haven’t decided how much more data visualization I want to do yet.
Looking forward, here’s a wish list of what I want to do next:
- Either add a flat line or another chart to track if my expenditures are beneath the threshold of my adjusted take-home.
- Determine how to best calculate monthly totals. Should they all be tied to the credit card cycles? They’re close to each other regarding timing, but one is 28 days and one is 30 days. They also start closer to the end of the month versus the beginning. I feel using proper calendar days for the category calculations would better accommodate using a check or cash.
- On the topic of cash, I haven’t quite dialed in how to account for cash withdrawals. Granted, I haven’t done that in a hot minute, but historically I have pulled out chunks of cash for the odd occasion that a vendor doesn’t take plastic.
- I would love to get a functional calendar together to track my billing cycles, so I’m not taken by surprise.
- Someday I might want to track my total net worth. If I continue to stick with Excel, that will also require manual input, unless I want to use a service like Mint to export that data to a CSV file.
This project thus far has been humbling, in a word. I don’t recall using Excel much beyond high school, so this was a crash course in how to do, well, everything. And of course, I went right for the deep end instead of keeping it simple. I’ve seen it said many times over, but finances are a metric unique to every individual. Not everyone cares about the same data. There is no one size fits all solution. Doing is believing for me apparently. All I can say is I better use this thing since I put so much effort into it so far.
