How I track my finances using spreadsheets Part 6: Historical Income and Expenses, and Time to Broke

 <<–Part 1 <– Part 5 | Part 7 –>

Hi RIP readers,

we have another lightweight “on the beach” post today.

I know, my public spreadsheet is getting way too big (and a bit slow), and I should probably pull something out.

Not today though.

I’ve been playing a bit too much with my Net Worth Spreadsheet lately, and ended up adding a few extra sheets that I’d like to show you today. I’ve added sheets to monitor income and expenses across multiple years (I’m tracking them in detail since beginning of 2016), and a mini retirement calculator.

Let’s look at them one by one

Income Sheet

(link)

Ok, this is not a new sheet. It’s been around for a while, but I’ve never shown you the details in this spreadsheet post series. Even though there’s not much to say about the formulas (pretty basics), I think it’s worth taking a look at the visualizations. This sheet tracks our income streams since January 2016.

Here’s a data screenshot from December 2019 (all time high income month) to June 2021, which is still WIP at the time of writing:

Data are pulled from the Net Worth sheet of the appropriate year, with some manual attribution for some of the categories like “Other“, which is after tax extras like grocery coupons, monetary gifts, tax returns and so on.

I track expected net income, i.e. after expected income tax, in CHF currency. The monthly totals differ from the total income reported in the income row in the cashflow section of yearly NW sheets because of expected wealth tax that I consider as income reduction but I have had hard time attributing to each income streams (and maybe I shouldn’t even try). At our current level of wealth we’re talking about a “low 3 digits” monthly misalignment. We can live with that.

As you can see, the main problem with this approach is that rows can’t be deleted. Over time we keep carrying obsolete entries in the sheet, like “rent” which was meaningful while I owned a flat near Milan and rented it out.

For that reason I gave up with the idea of also splitting “salary” by source (Hooli, Unemployment, AcademicInstitute and so on): that would have caused even more rows/entries so… “Mr Salary” would suffice.

“Blog” is also a collapsed down entry, where I sum up money coming from services, affiliate, donations, and other sources. At least I’ve extracted “video” as a category of its own where I track YouTube and Twitch income.

Here’s the main chart since 2016:

Yeah, the years of amazing Hooli stocks vesting and bonuses are gone, but I can’t complain 🙂

In the income sheet you can also extract pie charts of “split by income streams” in percentage and absolute values using a data validation cell and HLOOKUP functions.

Expenses Sheet

(link)

“Wait RIP, don’t you already have 2 different expenses sheets/spreadsheets for each year? Why do you need yet another expenses sheet?”

Good question!

In fact in my public spreadsheet set I keep both a (deprecated) expenses sheet for each year in my main spreadsheet (check 2020 Exp, 2019 Exp,…), and a full expenses spreadsheet for each year starting from year 2021 (check 2021 RIP Expenses).

Both the old yearly sheets and the new yearly spreadsheets are yearly resources. What was missing was a global overview over time. Something similar to the Income sheet, but for expenses.

Data are pulled from the relative expenses sheets/spreadsheet, either manually (cut & paste for old, immutable expenses) or via IMPORTRANGE google sheets function, which lets you pull cell data from another spreadsheet.

The challenge here is to find a consistent set of expenses categories. So far it’s been a manageable challenge. I’m forcing myself to adhere to the 12 expenses categories model I started in 2017 (that’s why you don’t see categorization data for 2016) but this might change in the future.

For example all blog related expenses have been put into “fees” category. Yeah, you can see that there’s something wrong going on.

Revisiting categories has its own set of challenges too, so I need to find the right tradeoff. When/if the blog (or content creation / business in general) expenses would grow and/or became more regular, I’ll find a way to classify them accordingly. It might actually be that I want to create a GmbH  (limited liability company) around my business activities, which would send “blog” income and expenses into a separate balance sheet, not to be mixed up with personal or family expenses.

With a logic similar to “income pie charts per month” from the income sheet, in the expenses sheet you can pick a single one or a few categories and track them individually over time:

Here you can see housing (in blue) and baby (in red) categories. Yeah, you can cry if you feel like. I appreciate your empathy.

Income and Expenses Sheet

(link)

“Come on RIP, another sheet about expenses? I got the previous one, ok, it kind of makes sense… but please, just stop now!”

My dear friend, don’t you want to see historical income and expenses together? I do! 😀

Income and Expenses data since 2016 are pulled from the previous two sheets of course.

The new data being produced here are rolling “12 months income and expenses averages”, “passive income SWR”, and “years of expenses”:

I don’t think you need to be told what a rolling 12 months average is, right?

Here you can see income and expenses (and rolling 12 months monthly averages) on the same chart:

Looking at this graph is a refreshing experience for me. I keep worrying and complaining, but income is still flying 2x higher than expenses. Good. But also not good enough.

Passive Income SWR represents the monthly income I could theoretically extract from my Net Worth assuming the SWR I feel confident using in Early Retirement (pi, 3.1415…% at the moment).

In theory if/when Passive Income becomes greater than current expenses I can say we’re Financially Independent. In the I&E sheet there’s a chart for that called Expenses and Passive Income:

Passive Income (in Yellow). is growing, but so are expenses 🙁

The chart titled Years of Expenses Accumulated shows the total net worth divided by rolling 12 months expenses average. That’s the number of years of expenses accumulated.

This is the most accurate wealth metric I can think of. When/if this metric reaches 25 I’d be FI according to the broken 4% Rule. To be FI according to the pi% Rule I need this metric to reach 31.83, i.e. 100/pi. We’re dramatically far from being FI in Switzerland…

This is what I mean when I say I don’t feel richer compared to 5 years ago even though our NW tripled in the meantime.

We created real wealth during 2016 and 2017, moving the multiple from 8 to 16, i.e. doubling our relative wealth (Net Worth divided by Average Expenses). Since then we’re stagnating. Starting in 2018 Net Worth growth has been eaten by lifestyle inflation, which means “kids” and a larger flat for our family.

Actually, the growth continued until late 2019 (the 2018 dip is due to the 12 months expenses average digesting the December 2017 expense spike, i.e. Maldives trip), when we sent our BabyRIP to child care, and changed flat. Baseline monthly expenses moved from 4.5k to 7k CHF over time.

Time to Broke

(link)

This is my favorite sheet! But it’s also expensive to keep up to date and to run, I might move it out of the main spreadsheet in the near future.

What’s being measured here? Few things along the line of “how long would our wealth last if X happens?”

First question I wanted to answer was “1) How long would our wealth last assuming I quit my job today, we keep spending like we did in the last 12 months forever, and we have no other income at all including investment income?”

“But that’s obviously the Years of Expenses accumulated you calculated in the previous sheet…”

Not exactly. I took inflation into account here, which means money would last less than Years of Expenses Accumulated.

And there are few more variables that will have a role in answering the other questions:

The answer (in June 2021) was: we will be broke in September 2035. Assuming 2% expenses growth with inflation, no income, no wealth growth… as if we’d sell everything we own, cash CHF bills out, and store them under the mattress.

It’s an extremely unrealistic scenario, and it’s still reassuring to think that I could (but I can’t) postpone thinking about money for the next 14 years.

What next? “2) How long would our wealth last assuming I quit my job today, we keep spending like we did in the last 12 months forever, but our wealth grows by our desired SWR% per year thanks to investments?”

This the “Early Retirement on the beach” scenario, where we’d enjoy our wealth while keep profiting from investments. We know we’re not FI in Switzerland, so we expect the simulation to still send us into Brokeland, but I suspect a bit later than scenario 1: in fact we will be broke in August 2039.

Cool, 4 more years!

Ok, time to draw some graph, even though it contains some spoiler about the next questions:

Here’s the full Time to Broke chart, starting from January 2016 to April 2077.

“Whaat? Why April 2077?”

Well, it’s when I’ll turn 100 years old. I guess I should cover as much longevity risk as possible, shouldn’t I?

Btw, I’m doing my best to eat unhealthy and stress my ass off so we could cut a couple of decades on the right end, and enjoy a shorter but successful retirement 😉

“Now I see why you want to be called R.I.P…”

Ok ok jokes apart, let’s explain the chart above.

The dark blue line is past Net Worth evolution in CHF, from 2016 to today (June 2021). Yeah, more than tripled in 5.5 years, we all know that. There’s no special reason it’s here in the chart, maybe just to flex.

The light blue line is the scenario #1, i.e. the no income and no wealth growth scenario (money under the mattress). It’s not a line segment, it’s actually going to zero faster than linearly due to expenses growing with inflation.

Actually, at 2% yearly inflation rate expenses would grow to 23k CHF/month by year 2077. Scary…

The red line is the scenario #2, i.e. no income but wealth snowballing at SWR% (pi in my case) per year. As we can see, the wealth destruction at the beginning is less prominent because passive income is proportional to current wealth. While wealth gets destroyed the two scenarios look more alike.

The orange line is the answer to the following question: “3) How long would our wealth last assuming I quit my job today, we keep spending like we did in the last 12 months forever, but our wealth grows by our desired SWR% per year thanks to investments, and I’ll keep earning some Passion money with my blog and YT/Twitch channels?”

This is also called the Passive + Passion income model, as popularized by Miss Montana (Jillian Johnsrud) few years ago:

I had to make some extra assumptions here of course. For example that my net passion income will start at 2500 CHF/month, growing only with inflation. I’m more or less at that level now (not in June 2021 though, I’m on vacation), and I know that if I push a bit I could grow my passion income a bit more.

Anyway, scenario #3 is rosier: we will be broke in February 2050, when I’ll be 73 years old. Well, it should be even better because starting on May 2042 Pension Pillar 1 will kick in, and I haven’t modelled it here (yet).

Not bad, I could keep working on my projects, and even if I don’t push for them to grow in revenues (but I know I will), and even if we don’t adjust our spending levels to cope with lower income, we’ll be putting food on the table until February 2050. Baby #1 will be 33 years old by then (and as every Italian parent I’ll still call here “baby”), and her not born yet sisters will be 30 y.o. in 2050. I assume I should improve expenses modelling to better align with expected costs of raising kids for the next 20 years, and then… gg my dear daughters, you can take care of yourselves now 🙂

The green line is the One More Year Syndrome, i.e. “4) How long would our wealth last assuming I work for one more year and then I’ll keep earning some Passion money with my blog and YT/Twitch channels?”

Assuming next 12 months of income will be equal to last 12 months adjusted for inflation, then just passion plus passive income we will be broke in July 2053. Almost 3.5 extra years of nonbrokeness for one more year of working a traditional job.

The black line is the Ravenna Threat, as explained in the StupidiFI post. In case we want to adhere to that model, i.e. moving back to Italy once our wealth drops below the Ravenna Threshold, we’d move back in 3 years according to scenario #1, 5 years according to scenario #2, 7.5 years in the passion+passive income scenario #3, and more than 11 years in the future if I keep working one more year. This is reassuring. A lot of room for experiments while having a B plan in place where we’d be more or less FI in Italy. Awesome!

I’m not going to explain the formulas I’ve used in these simulations, they’re pretty easy and you can check them out by yourself 😉

It’s a Retirement Calculator, essentially

What I’ve built with the Time to Broke sheet is essentially a small “retirement calculator” software, which should probably be moved into code and out of spreadsheets.

I’m forecasting 100-age years of data with a time step of a single month. My sheet has 736 columns and most of the formulas can’t be parallelized so it’s slowing down a bit.

There are also many missing features, like:

• One-off predictable large income events: inheritances for example.
• One-off predictable large expense events: college for kids, home purchases, car purchase, large vacation… sadly there are many more large expense events than income ones – and they’re usually associated with sad and griefy events like inheritances.
• Non linear modelling of future expenses and income streams. Examples: “Pension/Annuity of X per month starting on date Y”, “Child care payments until year Z”, “Moving into a smaller flat on date W”, and so on.
• Maybe run a Monte Carlo Simulation to emulate several sequence of returns (or random sporadic unexpected expenses) scenarios instead of assuming linear returns for the next 100 years.
• Easy way to play with parameters and output different curves for different parameters set. I’m going to show you few “what ifs” scenarios in the following paragraph, but it would be awesome to not have to manually change variables and have it all easily cooked by the main algorithm.

Maybe I’ll improve it and build my own Retirement Calculator software in the future… just to add another Idea to the giant pile of things that I could do given enough time 🙂

What ifs

Let’s play with some variables in this final paragraph.

What if I assume 1% instead of 2% as inflation rate in Switzerland?

Data show (source) that since 2008 inflation in Switzerland has ever been above 1%. Maybe assuming 2% inflation is too pessimistic, or at least it is pessimistic to assume wealth growth of 3.1415% before inflation.

Anyway, what would happen with a 1% inflation rate?

Nothing changes in kind, but everything changes in degree.

Now the Scenario #3 (passion plus passive) would send me broke in year 2056, at age 79, living in Switzerland, and keeping expenses at current level for the next 35 years… This is reassuring!

Working one more year means moving the brokeline 5 years later, at age 84. The chances of experiencing brokeness before having done with lifeness drop dramatically.

What if I increase the passion income to 4k CHF per month?

That’s very much possible. Maybe not in the short term, but given a couple of years I might increase the amount of money my small passion bets would generate, still keeping an attitude of not pushing that pedal compromising ethics and quality. Check my transparency page for more info.

Anyway… drum roll:

Of course the scenarios #1 and #2 didn’t move compared to the base hypothesis (they’re not considering passion income), but scenario #3 would now be sustainable until almost age 90!

Well, I know, it will be hard to earn 4k CHF/month (+inflation) at age 70+, but I also expect expenses to go down a lot. We all know the limitations of these simulations, we already talked about that. Plus, I don’t plan to ever stop being productive, for me productivity means being alive.

Anyway, for almost 10 years wealth would just stay flat (losing some purchasing power thanks to 2% modelled inflation), then the deficit between expenses and income – which is also growing with inflation – would start eating some principal and then as we know by now it’s just a matter of time.

The Ravenna threat would trigger in 2034, when the threshold would have almost reached our current wealth level.

This is all good news, but I was a bit surprised to find out that even with 4k CHF/month passion income we would not be ok forever. That sucks.

Ok, maybe wealth inertia of 3.1415% nominal yearly growth (with a 2% inflation rate) has been modelled too conservatively, but still…

What if we move back to Italy and assume 4k CHF/month expenses?

Now both the OMY and Passive+Passion (2.5k CHF/month growing with 2% yearly inflation) lead to infinite growth, i.e. FI.

The scenario #2, i.e. just passive income, won’t make our nest egg last forever, which means we wouldn’t be FI in Italy at 4k CHF/month (3.5k EUR/month) spending level and pi% nominal yearly investment growth. This is not fun, but as I said several times I should model wealth inertia more generously, maybe pi% after inflation.

Anyway, I hope you enjoyed the post and got inspired to copy my spreadsheets and play with them, or wait for me to lunch the RIP retirement calculator, E.T.A. 2060 🙂

That’s all for today!