My Path Part 3: Spreadsheet Day!

My Path Part 3: Spreadsheet Day!

After learning lots of little tips here and there on /r/personalfinance, and now that I had my investing setup to be automated and simple, I wanted to know what the next step was.  On the first of the month, on a daily q/a thread on /r/financialindependence, lots of people were talking about “spreadsheet day”.  Intrigued, I read on to learn that many people keep track of all of their spending (usually monthly) and then compile all of the data to do some analysis.  Some people posted pictures of what they had built themselves and they varied wildly and this immediately sparked a fire in me. For you see, my whole job revolves around excel and there are few things I can think of more scandalous than a big fat sumifs or vlookup formula doing sexy calculations outside of work hours (my heart is racing just typing it out!).  Using the inspiration of some posted on reddit combined with ones I’d done for work, I was able to throw together a pretty snazzy summary page where I’d add in data monthly from Mint and it would summarize my monthly savings, investments, market gains, spending, and net worth/portfolio value. I’d like to tell you I did this entirely in my spare time at home, but sometimes when an idea came to my head and it was a slower day in the office, I may or may not have done a bit of my spreadsheet construction on company time.

What started out as a 2 tab simple workbook has slowly evolved into a behemoth.  Tabs of charts, tabs for different data sources like Mint aggregating credit/debit card transactions, paycheck withholdings, mileage on my car, etc.  I don’t have too much data yet, 18 months worth of snapshots as of writing this, but I’m already able to see trends and averages. Before this I was in college, and at the time I only really spent money on food and registrations for various races, but now that I’m out and on my own it’s really helpful to keep myself on track.  Obviously I could find a way to spend every penny I brought in, but I’m using this as a way to curb that. I’m not depriving myself by any means, but I’ve read a lot about how avoiding lifestyle inflation is almost always the right move.

As Brad from ChooseFI would say “how does this work on a granular level?”  Well I’m glad you asked, hypothetical version of Brad that knows about me and my spreadsheet.  I’ll walk you through how my spreadsheet update process works step-by-step, as well as some of the fun things I like to look at and bits I’m proud of because what’s the point of keeping a financials spreadsheet without sharing it with strangers on the internet?

First I take a quick stop by my Mint account on the browser.  I go to my spending trend section, and at the very bottom there is a button to export all transactions as a .csv.  I’ll open that in excel and then simply copy the rows of transactions from the previous month and plop them in the “Transactions” tab of my Google Sheet that my spreadsheet lives in.  I’m diligent about keeping my spending categories accurate in Mint so that once I do this, my formulas take care of the rest. It’ll calculate my total spending and how it breaks down into ~20 distinct categories.  Next, I’ll load open the HR system that I have access to through my work where it’ll show me all of my paychecks and I’ll time in the dollar amounts for all different withholdings (fed/state/401k/ESPP/etc.). I’ll also plug in the number of miles I drove the previous month.  I do this to track if I’m biking more than I’m driving as that is an annual goal for me this year in 2019. Lastly, from Mint I’ll plug in the “Investments” and “Networth” values from the home page. After these quick steps, in my data snapshotting tab, there are formulas that calculate savings, savings rate, take home income, spending, investment contributions broken into pre-tax, Roth, and after-tax, market gains, and net worth delta.  This all takes a grand total of MAYBE 10 minutes.

 

Once I’ve done this, it’ll power some interesting (to me) visuals and data tables.  Below is my main tab which tracks my spending and net worth over time. It has my spending lumped into a few categories and their relative deltas from my all time average month average (if you’re wondering why “Necessary” is so far from average, the average was low because for 15 months of tracking I was living at my parents house rent-free).  The top left table is probably my favorite bunch of KPIs. Very simple, minimum average and maximum monthly values for income, spending, savings, savings rate, and investment contributions. The top right table feeds off of this. My min/avg/max FI date projections assume 4%/6%/8% growth, my current investments, and my min/avg/max monthly investment contributions.  I’ve also got some fun stats in the middle like how tall a stack of pennies would be if it had the same values as my net worth. Fun fact: a mile high stack of pennies is roughly $10,000 (just in case you were wondering).

I also have one final chart that I look at regularly, and this is my monthly cash flow.  I saw a screenshot of this once on /r/financialindependence and thought it would be worthwhile to make myself.  I have this all built out in a Google Sheet that I share with my girlfriend (Everything you see is also toggle-able between mine/hers/our combined data.  This is very fun to play with as we look ahead to plan our future!). The cash flow chart is actually 2 charts overlaid because I couldn’t get it to look how I wanted in one fell swoop.  As you can see from the labels, the greens are incoming cash, and the red/gray portion is outgoing. The reason I have investment contributions in grey is simply because while it is outgoing cash, it’s not gone forever like spending and taxes.  And lastly, the white bar is simply the difference between the two. Some fun trends you can see is January for this year and last is big dips due to maxing out my Roth IRA, and July through september had very high cash flow because I was building up a larger emergency fund before moving out of my parents house.  Obviously now that my e-fund is built to a level I am comfortable with, the white bar should hover around the $0 mark.

Anyways this is my spreadsheet and I hope you enjoyed it!  I haven’t attached images of all of it as some tabs aren’t too interesting.  I’ll likely post a follow up to this in the future as this sheet evolves and morphs over time into a bigger and badder beast.

 

How do you track your finances?  Do you have a spreadsheet that you created yourself, or did you find one online?  What do you think I should add to mine? I’d like to have more “fun metrics” on my main tab, let me know if you have any in mind!

Leave a Reply

Close Menu