How I track my finances using spreadsheets Part 5: FIRE Metrics

<– Part 4 | Part 6 –>

Hi dear RIP readers,

This is Part 5 of my Spreadsheets series. I recommend you to take a look at the previous episodes before reading this one. So far we’ve seen:

• Why I think tracking your finances is important, what to track, my tracking history, and why you don’t need any app but spreadsheets for that (Part 1)
• How I structure my spreadsheets for tracking (Part 2)
• How I track my Net Worth (Part 3)
• How I track my Cash Flow / income Statement (Part 4)

Today we’re going to deep dive into how I track my FIRE Metrics.

Well, today I don’t care much about those metrics, my perception of FIRE has changed a lot. I now see Financial Independence and Early Retirement as a continuous, a spectrum.

I do think that if you’re at the beginning, or even midway along the way, that modeling your financial “end goal” as a finish line has a lot of motivational value. Keep going!

We’re going to zoom in into the FIRE Metrics section(s) of the current Yearly Net Worth sheet, within the “RIP Net Worth” spreadsheet. This deep dive happened in September 2020, so I’m using the “2020 NW” sheet, populated up to column L: “Sep”, whose Fire Metrics section(s) spans from row 127 to row 164 at the moment.

Here’s “RIP Net Worth” Spreadsheet embedded for fun:

Enjoy!

FIRE Metrics Section

The FIRE metrics section is a bit complicated, and essentially I’m not using it… which makes a good candidate section to be skipped after ~15k words already written in this series.

So… it’s been a very short post today.

Have fun, enjoy your Sunday… What?

“But RIP, I’m young and want to be millionaire in 5 years… I want to track my progresses toward FIRE! please, show me how you do it <3 ”

Okay.

I have two sets of FIRE Metrics, the Ideal (a.k.a. Italian) and Current (a.k.a. Swiss) ones.

Let’s talk about the Italian metrics now.

Italian FIRE Metrics

There are two input variables (green rectangles in the picture above):

• Monthly target allowance: how much you plan to spend per month.
• Desired withdrawal rate: how much you feel confident withdrawing from your principal on retirement year #1, adjusting it for inflation over the following years. It’s the base of the 4% rule, and the “Constant Dollar” withdrawal strategy. Since you’re on my blog, I think you know about 4% rule (of thumb), SWR, FU Number and so on.

The FU Number (or FI Number, or FIRE Number for the grown-ups) is calculated assuming the desired WR and monthly/yearly target allowance. That’s you financial target!

As you can see (purple rectangle) in April 2020 I “reached” my Italian FIRE Number, and that broke the months left formula 🙂

Of course this is meaningless, because on one hand I now think that the 4% Rule is dying, and on the other hand I don’t think there’s a sharp line that divide non-FI from FI and non-RE from RE, i.e. FIRE is a Spectrum.

Anyway, to keep the carrot ahead of the stick I lowered my ideal WR to PI (3.14159265359…%) in May 2020 (red rectangles).

Months Left and FIRE Date (rows 134 and 135) romantically track he “FI Day”, and how far I am from that day… I beg your pardon, I was just a kid with half a million when I started tracking FIRE progresses 🙂

Maybe it’s useful to take a look at the Months Left function together. If you get it, then the FIRE Date is just a call to Google Sheets function DATE with current year, current month, and months left.

Let’s take a look at he Months Left function for February 2020 for example:

=roundup((E130-E63) / ((sum($D64:E64) + sum(‘2019 NW’!F55:$O55))/12))

A lot to unpack…

The idea is: how long would it take to reach FU Number assuming current NW keeps growing at the same linear pace of last 12 months?

The formula:

• X = sum($D64:E64) = total NW Delta (in EUR) YTD excluding current month. Mind that the “$” before the D means that when I copy the formula over at snapshotting time the D is going to stay a D, while the E moves to F. in fact, the formula for March 2020 contains the range $D64:F64 and it keeps working as intended.
• Y = sum(‘2019 NW’!F55:$O55) = last year total NW delta (in EUR) from current month to December. In this case total NW delta between Feb and Dec 2019. Note that the formula holds when copied over at snapshotting time.
• X+Y = total NW delta in last 12 months.
• A = (X+Y)/12 = average monthly NW delta over last 12 months.
• Z = E130-E63 = FU Money minus current NW, i.e. how much extra money we need to be FI.
• Z / A = number of months left, assuming linear growth.
• roundup(Z / A) = need an integer for number of months 🙂

You’re welcome!

“Cool RIP, thanks… and what’s coast date?”

Thanks for asking!

Coast Date answers the question: “when will our NW reach FU Number on its own, without us contributing a single EUR to it?“, or, similarly, “when will we reach FI if we decided to spend all our income and save ZERO from now on?”

“Wait, is this a thing? I suppose if I spend EVERYTHING I earn I will NEVER be FI. what’s the trick?”

The trick is that your NW grows on its own if properly invested. If you discover your old uncle – whose only heir is you – died back in 2009 with 10k in the bank, you now have 10k. If your old fart died with 10k invested in S&P500 (with dividends reinvested), you now have 60k. if your caveman great-great-…-great-grandfather died in a cave with the equivalent of today 10k Euro invested in a stock index (there were plenty available in Pleistocene!) you’d be trillionaire today!

Unlike Fry, you need something better than “interests” to keep up with inflation 🙂

To predict a coast date I assume linear growth of our NW at a rate equal to our desired Withdrawal rate. I consider the WR as a good estimate of future expected returns of my NW, after inflation.

“But RIP…”

I KNOW, it’s an approximation – and probably an optimistic one given that we don’t invest our whole NW in assets with high expected returns (especially in September 2020).

“But if you consider expected returns after inflation shouldn’t you also move the target with inflation?”

You’re right, I should. 1.2M EUR 4 years ago was more than 1.2M today. This makes the formulas more complicated though. I’d rather change the target every once in a while. I started with a target of 3k EUR/Month 4 years ago, and moved up to 3.5k recently. also lowered SWR a couple of times during these 4 years.

I do consider growing the target/threshold with Inflation in my StupidiFI metrics – that I’m not going to discuss here. Take a look at the formulas in the Ravenna Threat sheet if you’re interested.

“Did you account for Italian taxes in your Ideal FU Number?”

Kind of. I consider the “gross” target allowance. But this is outside the scope of this book post series.

“Are you sure 3.5k EUR gross will be enough to retire in Italy with your family?”

I don’t know. Probably yes. This is not the best time to discuss this.

Let’s move on.

The Yearly/Monthly/Daily Current Allowance (rows 138-140) monitor how much we could withdraw from our NW if we retired today, according to our desired WR.

When this number equals the Target Allowance we are supposed to be FI.

if we decide to move somewhere where we can live with X per month, we’re FI today.

This is a nice graph in the “2020 Exp” sheet that shows current allowance, ideal expenses, and current (real) expenses.

As you can see, the red line mostly overlaps with the yellow one (we’re almost “ideally FI”).

Real expenses (blue line) are there to depress me 🙁

Current WR (row 142) tells us what’s the Withdrawal Rate we should accept if we want to call us FI today. We’re well below the 4% in 2020. According to US standards (4% Rule) we’re already FI if we move back to Italy. According to Ben Felix (2.2% SWR), we’re more or less halfway. According to Financial Samurai (0.5% SWR), we’re somewhere around 15% FI…

Year of Expenses (row 143) measures our NW in terms of how many years of expenses we saved. It’s the inverse of current WR. If you save 25 years of expenses, you’re FI according to the 4% rule. We saved almost 30 years of (ideal) Italian expenses, but we need ~31.83 (100/pi).

The last three rows (145-147) are there for reference. A recurring (inflation adjusted) expense and a monetary lump sum are equivalent in this model. The yearly conversion factor is the inverse of WR.

With 31.83 EUR you can build a machine that generates the purchasing power of today 1 EUR/Year forever, assuming investment returns of “pi” + inflation.

With 343 EUR you can build a machine that generates 1 EUR/Month forever.

With 10429 EUR you can generate 1 EUR/Day forever.

The formula can be flipped over: what’s the lifetime cost of your Netflix subscription? 12 EUR/Month? The real cost in term on extra NW needed to pay for it for life is 12*343 = 4116 EUR.

This is my best way to picture the “Time is Money” equivalence: one one side you have Money, the Netflix subscription you want. On the other side you have Time, the 4116 EUR you need to accumulate in 1, 2, 10, 100 months of work to allow yourself to include this subscription in your lifestyle forever.

Powerful!

Swiss FIRE Metrics

The Swiss FIRE Metrics are similar to the Italian ones.

The only difference is that instead of having monthly allowance as an input, I use current spending levels. Actually, last 12 months of expenses (row 150). The formula is similar to the X+Y in the “months left” above.

As you can see our progresses are a bit demoralizing 🙁

Both because we lowered our WR to “pi” for Swiss metrics in May as well, and because yearly expenses are growing faster than our NW.

We’re above 80k CHF/Year of expenses, mostly driven by November flat move, and also an expensive July… Maybe in few months we can put the bar below 80k/Year or even 75k/Year. We’ll see.

That’s all for the FIRE Metrics, and the Net Worth sheet.

Have a nice day!

<– Part 4 | Part 6 –>

 

P.S.

The spreadsheet series is taking a break.

There are at least another three chapters I want to write about: one about Investing and tracking your AA, one about detailed Expense Tracking, and a last one about Snapshot time and conclusions but there are so many other things I want to explore, and time is scarce now that I started a new job.

If there’s enough demand I’ll resume the series. Please let me know 🙂