How I track my finances using spreadsheets Part 1: Why and What

Part 2 –>

Hi dear RIP readers,

Today’s post has been sitting in my “Blog Post Ideas” list for a year now. I wanted to deep dive into my 2020 Net Worth Spreadsheet (and more) to help you following me in my journey, and (more importantly) to give you some hints in how to track your own financials.

Ideally, I wanted to write that post in a timely manner at the end of 2019, or in early January 2020.

Well, I didn’t. I procrastinated for months and months, giving precedence to other posts. Do not worry, the list of ideas is never ending. I don’t risk draining my creativity. I’ve ideas ‘stashed for the next 10 years at least.

Anyway, more than 4 years ago I wrote a blog post about my Net Worth document. Of course reading it today embarrasses me a lot. Both for the poor quality of my English back then (don’t know if it got better, but today I enjoy my “voice” much more), and for the lack of useful information for the readers. I was at the beginning of my blogging activity, I beg your pardon.

Before we dig into the “how”, let’s tackle the “why” and the “what”.

… and we stop there for today!

I wanted to write a single post about my spreadsheets, but I reached 13k words and I was still halfway thru it.

I decided to split the post into a series, the Spreadsheet Series <3

This is the first part, about the why and the what of tracking your finances. A deep dive into Financial Awareness / financial Intelligence.

Next posts will be about the how. The real meat 🙂

Why track your finances?

Measuring is good.

It brings data. It brings awareness.

It’s much easier to improve something if you have data, metrics, and clear goals. It’s easier to experiment new strategies if you have data that give you feedback. Decision making is easier if it’s data driven and not (only) based on instincts and heuristics.

I’m not saying it’s the only way to improve something (I don’t want to fall into the metrification trap). For example it’s hard to use metrics to improve “your relationship with your parents”. But wherever it makes sense to measure, you should. Most of the times, the mere fact of measuring brings improvements in whatever you want to optimize. Start tracking your calories and you end up eating better, without a conscious choice. Start tracking your steps count and you begin walking and being outdoor more. Start tracking your expenses and you’ll be more diligent with your money. It’s how we’re wired.

Awareness brings attention. Attention might bring questions.

Why did we spend 30% more than normal on groceries this month?“, “Why our saving rate is raising?“, “Why is this investment losing money?

Questions might find answers. Answers might trigger behaviors, actions.

We’re using the car sharing service once per quarter and paying a fixed annual fee. That doesn’t make sense, let’s cut it!

Actions might become habits.

I prepare my lunch everyday, and avoid going to restaurants for lunch breaks at work

Habits become identity.

I’m a person who buys second hand clothes 🙂

When something reaches Identity level, it sticks. It becomes unconscious. No more paying for it in willpower points. No more decision fatigue. It’s who you are. “I’m a man that doesn’t give a fuck about cars”.

And all started with awareness.

While I’m sure tracking and measuring is good in so many fields, in personal finance it’s a no brainer.

I’m pretty sure I wouldn’t have accumulated such a good fortune if I hadn’t been tracking my financials with spreadsheets since early 2000s.

Plus, if you track your numbers publicly like I do with my blog, you also get accountability. If you’re millennial enough to love challenges and gamification, you can find a buddy/opponent/team to get some extra motivation to improve your numbers. Like many bloggers did on MustachianPost Blog, with the yearly Bloggers Saving Index.

I think a not so small portion of our Net Worth can be attributed to my blog. Having to show my numbers made me much more disciplined with spending and investing. I owe you a beer! 🙂

Anyway, writing down what you own assets, your liabilities, your income streams, your expenses is the first step in the financial freedom ladder, the so called Financial Intelligence step. Made popular by the amazing book Your Money or Your Life, probably the best book in personal finance ever published.

When did I start tracking? Good question…

My tracking history

I have some vague memories about the why I started, or what inspired me to start tracking… I don’t think I’ve read YMOYL before 2010. Maybe my father played a role here, but I’m not sure. He never tracked his numbers like I do.

Maybe I just wanted to play with spreadsheets before it was cool, or maybe I realized that it would have been useful or fun, without knowing that literature has been written on the importance of financial intelligence/awareness. I don’t know.

Anyway, when did I start tracking my finances?

I used to have a kind of virtual checking account with my father when I was a teenager. I opened a real bank account in 1996, when I started University.

Then in the early 00s my financial situation became slightly more complex (which looks like a kid’s piggy bank compared to today) with some investments in local bonds, a prepaid debt card, cash, checking and saving account… In January 2004, age 26, I was burning out from my WIP academic career, and I wanted to take a break. Yep, I’ve always been unemployable, essentially 😀

In order to quit and take a mini-sabbatical (that happened in March 2004) I needed to keep a closer look at my financials. I needed to track my wealth, income, and expenses down to the newly introduced EUR currency.

I never stopped since.

I used spreadsheets before the cloud was cool. I had many xls and xlsx files on my hard drives and floppy disks. Once the cloud became a thing, I switched over Google Sheets. No regrets! I can’t even touch an offline spreadsheet anymore. I created my first centralized Net Worth Google Sheet on December 16th 2012, with the intention to start tracking year 2013 NW on Google Sheets. Later the same year, I imported all the excel files I had on my hard disks (2004-2012) into the same spreadsheet, and kept appending new yearly sheets (plus some bonus sheet here and there) until… well, I still use that spreadsheet.

In 2016 things changed a bit, because I started blogging and wanted to publish my data. Since then, I have a “private spreadsheet set”, and a public spreadsheet (just a single one).

In my private spreadsheet set, I have one spreadsheet to track historical Net Worth, investments, income, forecasts. A single spreadsheet that contains data from 2004 to 2020 (and counting). Well, actually 1991-2020 and counting. Plus I keep an expenses spreadsheet for each calendar year since 2016, i.e. since I started blogging. Before that, I hadn’t tracked expenses for a while and I couldn’t find the excel files I used back in the early 00s. What a pity!

I did find an income spreadsheet for years 2002-2005 though. It was the beginning of my earning career. Well, I actually started earning money at age 14, tutoring kids in math, but I didn’t track that. Again, what a pity!

My public spreadsheet, the one you can access, is a copy of my private Net Worth spreadsheet, plus I copied over yearly expenses overview sheets from my private expenses spreadsheets since 2016.

We’ll take a closer look in the next episodes of this series, do not worry.

During the process of writing this post series I’ve cleaned and moved a lot of former private data into the public Net Worth Spreadsheet, like 2004-2015 Net Worth tracking sheets (2004-2012, 2013, 2014, 2015), and 2002-2005 Income. It’s so romantic to see my numbers back when the Euro was being introduced. Enjoy 🙂

Before we move on, a reminder that I never used any software/app to track my numbers.

No, you don’t need you need a budget, personal capital, or any other personal finance software.

You don’t need to pay for a SaaS, you don’t need to receive ads, you don’t need to jump from a software to another when they discontinue their service, you don’t need to receive emails and push notifications, you don’t need to be upsold more expensive products and services, you don’t need to invest a lot of energy to learn a new product to discover it’s lacking the feature you absolutely want (financial tracking is so personal!), you don’t need to have 10 different apps installed, and you definitely don’t need to give away your privacy!

… says the man who puts all his numbers online 😀

Yes, I know, but it’s still much safer to share my numbers with the entire world online as long as I do it when I want, for how long I want, as anonymized as I want, not connected to my real bank/brokerage/credit-card accounts than giving all my financial data (and maybe more) to a set of corporation craving for them.

And no, Google doesn’t have access to my accounts: I enter the numbers manually.

What to track

The possibilities are endless, and it mostly depends on what you want to achieve/improve/monitor.

As I said, personal finance is very personal, and so it is financial awareness.

But if you read quite a lot of resources, and if you take a look at what other people who are smart with money do, some common patterns emerge.

Net Worth

Your Net Worth (NW from now on) is your most important financial document!

It measures what you own and what you owe. Your assets, and your liabilities. I’ve never seen a financially smart person who doesn’t track their NW. Take a look at this post by Trent Hamm (The Simple Dollar), and this one by Mr Money Mustache on the same topic.

There are slightly different theories on what to include/exclude from your NW, but don’t let this paralyze you. It’s a mostly irrelevant argument. Just stick with a model, and keep tracking it over time.

I personally use the Trent Hamm approach: include every asset above a certain monetary threshold at their immediate resell value.

Having a threshold means that I don’t put the estimated value of my bicycles on my NW, nor the individual value of each book and boardgame I own – it would be a hell of a task to update my total NW according to the change in valuation of each individual entry over time, and a mostly futile exercise in perfectionism. A different approach would be to give an estimated value to my entire boardgame collection though. Maybe it’s in the low 4 digits…

You got the point: it’s very personal. Use your own judgement.

Some suggest to not include your primary home (if you own it) in your NW. I’m a bit religious on this: you MUST include it in your NW. In the end you could sell it tomorrow and you’d have the money in your account. You don’t want huge spikes in your total NW in response of simple and neutral changes in your asset allocation, assuming you sold your flat at the expected market value.

Some suggest to not include pension funds and expected social security payments into your NW because “the government can always decide to change the rules”. I have mixed feelings about this. I include my pension funds (Pillar 2 and 3) but I don’t include social security (Pillar 1) into my NW, even though there’s a way to account for it (given current rules don’t change), and I also used it in my first Reader Case Study. Maybe the closer I get to statutory retirement age – i.e. the more likely it becomes that I see the money – the safer it will be to add this asset to our NW.

Using the resell value means you don’t put unrealistic values based on purchase price, or even market value if the market is not liquid enough.

Most of your assets will have an explicit “resell value” though, which is their real time financial value. Bank accounts, investments, mortgages, cash, loans, credit card debts are all in this category. They’re just numbers.

Other assets might require some sort of estimate. Real estates, cars, art pieces, jewelry, collections… they have a market value (maybe), but you can’t liquidate them with a click, at a predictable value. My mental rule is that I should assume worst case scenario, but nor worse. Put the market value of your car, minus 10% safety margin. Or minus the expected resell expenses. Or both.

Anyway, since I don’t own “stuff” worth tracking, at the moment I don’t have any “to be estimated” asset in my NW, except maybe “taxes due”.

About tracking granularity, I personally track my family’s NW almost in real time, but I take monthly snapshots.

Your mileage may vary.

Here’s my year 2004 month by month:

Cute, isn’t it? I almost doubled my money during 2004 \o/

Like I do since forever, along with individual assets you should track your Total NW, and maybe the rate of change over time. It will give you a motivation boost when things go well, but also anxiety when they don’t.

What the hell happened in March 2004? I took an unpaid sabbatical month and I purchased a 10 years old used car for 1k EUR that I didn’t plan to ever resell, so it didn’t appear in my NW.

The result? -21%. Ouch! And my 1994 Ford Fiesta was stolen two years later 🙁

Anyway…

You might want to add graphs and tables. Go ahead, help yourself. A picture is worth more than a thousand words.

This is our historical NW over time, tracked in three currencies since 2012. Click to enlarge, or visit the Historical Net Worth sheet.

We’ll take a look at how I track my Net Worth in part 3 of this series.

Income And Expenses

The second must-have ingredient for a complete financial awareness experience is tracking income streams and expenses.

Ok, I must admit I didn’t track my expenses with the same religious attitude in the past. But that’s because I never had unhealthy spending problems. My NW has almost always gone up. My saving rate has always been positive, and I never had a real debt in my life. No loans, no mortgages…

Personal Finance is personal, time is limited, and what to monitor should reflect your personal needs. I started tracking expenses more as a hobby, in 2016, when I started blogging.

If you spend more than you earn, have troubles reaching the end of the month, live paycheck to paycheck, are drowning in debts then I strongly recommend you to track your expenses down to the Cent/Penny/Rappen.

It’s a healthy habit even if you’re not in financial trouble.

If you ever want to achieve Financial Independence, or advance in the FIRE Spectrum, tracking your expenses and your income streams is a must.

investments and Asset Allocation

Once you get your head above water, without immediate financial troubles, you might want to plan for the future. That means probably writing an Investor Policy Statement (mine needs an update!), defining a target asset allocation, opening brokerage accounts, start investing, and so on.

Don’t start investing before you know where you stand financially (NW), and how fast are you moving (Income/Expenses). Before you know your Financial Momentum (position and speed).

Having said that, let’s assume you start investing and want o track your investments in your NW document.

A simple solution would be a single entry for your entire brokerage account. Unless you’re investing in a single all-encompassing solution (a single world stock ETF, a Roboadvisor, a target retirement fund), this simple approach doesn’t allow you to monitor if your current asset allocation is on par with your ideal one. You might want to know when it’s time to rebalance your portfolio, when it’s time to liquidate some of your assets, when it’s time to accumulate more cash.

You might also want to be able to slice and dice your historical numbers by asset class, or even by sector, geographic areas, individual securities, and currency exposure.

There are two approaches for fine grained monitoring:

  • You create separate sheets (or a new spreadsheet) for your investments – as fine grained as you want – and report just the totals on your NW document
  • You pollute your NW document with individual entries.

First approach works better if you’re an active investor who opens and closes many positions over a single year. Second approach works better if you’re a passive investor that only rebalances with low frequency among a small set of securities.

I picked a polluted approach (an entry for each investment in my NW), with some exceptions for individual stocks, which have been collected in a single entry. If my investing volatility (the rate of change of my investing strategy and chosen securities) will grow over time, I might need to switch to a different approach.

I also have several sheets to track ideal vs real asset allocation, graph of NW by currency, NW by asset class and more. We’ll take a look at them in the next episodes of this series.

Metrics and Forecasting

So far we’ve seen the importance of monitoring the present (current NW), and remembering the past (previous monthly NW snapshots).

What about the future?

When will you get rid of debts given current trajectory and assuming X, Y and Z? When will you be able to afford the down payment for your dream house? When will you become financially independent? What will be my budget in retirement?

This is non standard. Few books or bloggers talk about that. People go to retirement planners to get answers to these questions. Which is not a bad idea, but it’s much better to seek advice from a financial planner after you’ve done your homework, as a way to vet your strategy and conclusions, without being vulnerable to what they will inevitably try to sell you.

What and how to track/monitor/estimate precisely?

This is really personal.

You first have to define your goals, and your intermediate milestones. Monitor your progresses toward them. Use some interpolation (linear or exponential or a mix of both), make some assumptions, write formulas with many parameters, play with different values of your parameters.

Be moderately pessimistic.

I have sections of my NW document that monitor my progresses toward FIRE, and we’ll dig deeper into that in future episodes. Mind that I now consider those plans deprecated, with no replacement in sight. I also came up with a new metric for my semi retirement of April-September 2020. That’s also deprecated. Things change too fast in our life!

I’ll show you my financial goal tracking formulas, but none of them makes sense today.

There is no favorable wind for the sailor who doesn’t know where to go

― Seneca

I wish you a future less foggy than mine 😉

Budgeting

A widely accepted cornerstone of financial intelligence is budgeting.

By budgeting I mean the attempt to plan your expenses over a given time frame. You can have weekly budget, monthly budget, yearly budget.

Someone includes new investments, voluntary pension contributions, even “savings” to their budget… I’m not a fan of budgeting for monthly expenses, so I’m not the best person around to help you with this.

I actually never used a monthly budget. Like expense tracking, I would have set up a budget if I had found myself spending more than I earned.

I got close to that while working for VideoGameCompany in Milano, Italy. A mediocre salary, an expensive city, an unemployed girlfriend living under my roof. My saving rate has been in the single digit for a couple for years, and the NW stagnated. Maybe I shouldn’t complain much though, it was during the Great Recession 😀

NW grew by 7k EUR in two years… like a normal daily swing in 2020

Anyway, I’m not going to show you how to define a budget. I’m not an expert on this matter, but it’s not rocket science either. If I had to budget, I’d probably add a sheet in my yearly expenses spreadsheet with a monthly goal per category, and surface remaining balances somehow visually in the monthly expenses sheets and/or in the yearly fine grained summary. Easy.

Note that while I don’t budget for the ordinary monthly expenses, I do budget for extraordinary events like our flat move (and the furnishing of the new flat), our daughter’s startup kit (crab, stroller, car seat, clothing…), our holidays, our wedding and so on. Those budgets get a spreadsheet on their own, and their lifetime is supposed to be limited.

That’s all for this first chapter!

Have a nice day 🙂

Part 2 –>

13 comments

  1. “Google doesn’t have access to my accounts: I enter the numbers manually” … on a Google spreadsheet. Doesn’t that mean that Google has access to your data? I am curious about your point of view here.

    1. Google has no access to my bank account, brokerage account, credit cards. That’s what I meant.

      Google (whatever you think it means) has of course access to the spreadsheet itself, which is a grid on numbers without any automatically retrievable semantic.

  2. Hi Mr. RIP,

    I welcome and thank you for this new blog series. I used some of the information in your sheets as an inspiration for mine but is always better to have the reasoning for all the data you show by the author himself.

    Nevertheless I’m happy with my way of controlling my NW although is a little more complicated that I like. Somehow I managed to get that since before 2004 and is really nice to see the evolution ones had.

    Keep the posts coming.

    Regards,
    Luis Sismeiro

  3. Dear Mr. RIP,
    Love this blog, a late discovery to me. I’m a bit slow, so no surprises there. Rather late than never also comes to mind. Anyway, time to pick up speed. Looking at your public spreadsheet, I must say it looks helluva complicated, to my unfinancial unspreadsheet kind of guy eyes. Is there a ‘blank’ tweak and plug in the numbers’ version? I know so many variables goes in there, but when you start from scratch, what to do? The YNAB app crossed my path awhile back, but I could not get into it. Any sage advice for this padawan?
    Yours,
    J Dan Dee

    1. Thank you JDD for your kind words 🙂
      My spreadsheet is complicated because my financial situation evolved over time and it’s complicated at the moment 🙂
      I don’t have at the moment an easier version. Well, you an take one of my sheets from previous years, complexity was lower before 2012.

  4. Hi Mr RIP!

    I have been procrastinating making my own spreadsheet for tracking finances for a few months and your posts gave me just the kick in the backside I needed, thank you!

    To any other readers considering using the spreadsheet: the initial set-up does take up a bit of time, but monthly maintenance is straightforward and quick, probably quite comparable to budget apps. And for the record, I do not work in IT and do not have the same proficiency as Mr RIP.

    RIP, I have a few questions (and let me know if I am getting ahead of myself and should just wait for chapters 6, 7 and 8):

    1.) Why do you take a snapshot at the end of the month rather than beginning? To me, it seems more intuitive to label the column 1.1, but I wanted to check if you had given the subject some thought and I am missing a crucial piece of information before I make the changes. It makes very little difference, but the formula in Q2 below is a bit easier.

    2.) For past values of portfolio, I am using the following code instead of hard-coded values:
    =INDEX(GOOGLEFINANCE($A40; “PRICE”; DATE(2020;1;31)); 2; 2)
    The reasons are that: a) I am currently establishing the sheets, meaning I have a backlog to work through and it is easier than looking up values and b) I will inevitably miss capture time at some point and this is again easier than looking up past values. Is there a drawback to this method?

    3.) I have some assets in Crypto and GOOGLEFINANCE does not work with Crypto values. I found third party work-arounds, but I don’t have IT background and do not understand how API works. Maybe this is a stupid question, but given the sensitive data in the sheets, is there any security risk in using sheets to 3rd party API to get the crypto values in real-time? Work-around I am considering: https://blog.coingecko.com/import-coingecko-cryptocurrency-data-into-google-sheets/

    4.) Why is your NW sheet annual? Would it not provide more data if it were continuous?

    Thanks for posting this series. I found it super helpful and look forward to chapters 6, 7 and 8.

    1. Hi MH, welcome to my blog 🙂

      Answering your questions:
      1) end of the month is the same of beginning of the month. It’s just a formality, a convention. When I ask myself “what did my wealth look like in April?”, I’d like to answer with End-Of-Month numbers. It’s my preference. Think about years. Think about balance sheets of companies. When they publish 2019Q4 balance sheets they refer to the closing values. It’s more natural. P.S. wht you mean by labeling the column 1.1?

      2) Yes, I know I can use GOOGLEFINANCE for past values, but I also know that having a spreadsheet with thousands o queries to external services is expensive, slow, risky. What’s the point? It costs me 0.38 seconds to CTRL+C and CTRL+SHIFT+V 🙂

      3) track manually, do not rely too much on external services. I have almost 30 years of tracking history of my Net Worth because I do things manually. Companies disappear, APIs become deprecated, clouds disappear. Do it manually, and back things up every month at least.

      4) This is a good point but I think I’ll stick to annual for the following reasons
      – Having it annually lets me remove lines that become obsolete for the following year (if I has a single, continuous spreadsheet with rows for each asset I’ve owned since 2002 the sheet would be 1000 rows, unreadable)
      – Any data can be pulled from more sheets by queries, ranges and so on. It’s a bit painful, but it can be done.

      … not sure chapter 6, 7 and 8 are coming anytime soon

  5. Hey RIP,

    Found your blog yesterday and, all I can say is: GMTA 🙂
    – I’m also ’77
    – I joined Hooli on the same month as you (!) (we might have chatted in the Hooligan class!)
    – I got hardcore about finance tracking around same time (’09), for the same reasons (data is power!)
    – I even have a NW sheet that looks just like yours. Including numbers, roughly, and a drastic change in 2012. Mine comes more from “career” (cough) though rather than good investments. Or, in other words, my cage is thicker 🙂

    Enjoying your reading a lot, keep up the good work!

    1. Hi Ovis, yes we know each other (if I guessed your name right from your email address) and we started at Hooli on the very same day 🙂

      Thanks for stopping by, and thanks for the good words!

  6. Buonasera Mr. Rip è possibile fare la stessa cosa con il programma Excel? Oppure ci sono delle differenze con il quale google sheet è piu consigliato?
    Grazie

  7. Hi, I also believe that spreadsheets are the best tools for monitoring your finances.

    In any case, is there a way to automatically extract your current account data on Google Sheet? I’m willing to give up my data to automate the process and spend more time tracking what I spend instead of manually feeding the spreadsheet

Leave a Reply to Ovis Cancel reply

Your email address will not be published. Required fields are marked *

Comment Spam Blocking by WP-SpamShield