Monthly Archives: May 2016

You are browsing the site archives by month.

The Guts of the FIRE Spreadsheet

In my previous post I introduced a spreadsheet (download link here) that allowed you to calculate what number you need to hit in order to safely retire for the rest of your life. In this post I’ll dig into how the spreadsheet works, so that you can understand where these numbers are coming from, and how you can incorporate the techniques into your own modelling. There’s a number of different steps involved in the simulation, so let’s start from the bottom and work up!

1) Model the Market

Making an accurate model of future financial market behavior is a fool’s errand, making a statistical model only slightly less so. So we’re going to do just that.

I pulled historical returns from the S&P 500 (a total stock index fund) and 10-year Treasury Bonds, as well as historical inflation. From these I calculated means, medians, and standard deviations, and modelled future returns as normal distributions. The distributions of this data are not symmetrical, which is to say they are slightly skewed towards one side. My poor-man’s method of correcting for this was to use the average of the data’s mean and median as the normal distribution’s mean. You can see below the models I used in black overlaid on the historical data in gray.

For the curious, here are the real-world stats:

Mean ReturnMedian ReturnSkew(Mean + Median)/2Standard Deviation
S&P 50011.4%13.9%-0.1212.6%19.8%
T. Bonds5.2%3.5%0.234.3%7.8%
Inflation3.3%2.7%0.113.0%5.1%

The stocks and bonds are decent fits, but going back to 1914 means that our inflation model includes two world wars and the 1970s recession that throws off our distribution quite a bit. As pictured, restricting the inflation data to the past 30 years gives an almost perfect fit. However I think that given that we have had several periods of high inflation in the past century, a simulation that extends a further 100 years out should hold the possibility for the same.

Normal distributions do well enough for our purposes, but I the weakest point of this entire simulation is in these models – I’d like to be able to incorporate skewed distributions that hug the data better, and autocorrelation (esp. for inflation) would be nice. If anyone knows some simple ways to do this please leave a comment.

2) Model spending and income

Spending and side-income will also be modelled as (surprise!) normal distributions. The only difference from the market models is that we include a spending minimum threshold (user-set), as well as an income lower threshold ($0). For example, below is the spending probability curve for a scenario where our minimum expenses are $20k, our target spending is $25k, and our 3σ (three standard deviations) variation in spending is ±$15k. Any part of the bell curve that lands to the left of $20k is brought up to this minimum.

Spending model

Spending model

This simulation accounts for inflation in all these, so your spending and income will rise over time to account for cost-of-living increases.

3) Simulate a long and (sometimes) prosperous life

Now that the models are built and we have our inputs, it’s time to simulate our lifetime financial performance. Looking at the screenshot below should make these calculations obvious.

The full 100 year simulation

A full 100 year simulation

Kidding. There are a lot of columns in there that are nothing more than either shared inputs for other columns, or alternate ways at looking at the same numbers. So let’s hide everything but the relevant columns, and zoom in to the first two years.

Essential columns of the simulation

The simulation is straightforward. You start a year with a given net worth. You spend and earn a certain amount that year, as randomly chosen based on the probability models discussed earlier and adjusted by the cumulative inflation up until that point in time. Market percent returns are randomly chosen, and those percent returns are applied to your beginning balance (minus half your spending and plus half your other income, which is an assumption that you have earned and spent half of the year’s total at the halfway point in the year). This give you your market dollar returns. Then your year-end balance is simply found by taking your starting balance, subtracting your spending, adding your income, and adding in your dollar market returns.  Repeat for the next 100 years.

4) Live 1000 lives

Since our 100-year simulation is based off of randomly-drawn numbers, it is only one of an infinite number of possible outcomes. Finding all of these and computing an exact success rate is a bit beyond my available computing power. However looking at 1000 of them and determining an approximate success rate is completely doable. This approach is called a Monte Carlo simulation, and it is used for this sort of analysis in a wide range of industries.

Excel is a slow tool to be using for Monte-Carlo simulations, and the 1000 number was arbitrarily chosen as something that gives decent results while not melting my laptop in a pool of white hot compounding interest. However, it is small enough though that we’ll see statistical noise in the end results. Ideally you want to scrap this whole spreadsheet and write it up in a proper programming language so that instead of 1000 cases, you can run a million.

How to do this? I’m going to cheat and refer you to the youtube video that showed me how to implement Monte-Carlos in Excel. Only the first few minutes are relevant.

To distill the video into a sentence, make a top row with the formulas you want to apply to the 100-year simulation and extend it down 1000 rows using a slightly-hacked data table. For our analysis we are monitoring whether we ever hit a net worth lower than our minimum income, for different time horizons. Setting this as our first row, we end up with the table below.

The results of the Monte Carlo runs

The success rates at the top is counting the number of successes as a proportion of the total number of Monte Carlo runs.

5) Repeat the above for nest eggs of varying size

There is no good way to automatically run multiple Monte Carlo analyses in Excel, and so this has to be done by copying and pasting a one-row group of results multiple times. This is tedious, so the copy and pasting is done using a dirt-simple Excel macro that I whipped up.

The table that feeds the monte-carlo analysis and pulls the results back

The table that feeds the monte-carlo analysis and pulls the results back. We copy from here

I didn’t have any experience doing Excel macros/VBA before this, and it was surprisingly easy – I highly recommend trying to do it yourself so you gain that experience. For education and for those wary to enable macros on unknown spreadsheets they download from the internet (generally a smart idea!), here’s what’s executed when you click the ‘Recalculate Results’ button.

Private Sub CommandButton1_Click()
Dim i As Integer
Dim pasteRow As Integer
Dim numSteps

Sheet2.Range("F8:J10000").ClearContents
numSteps = Sheet7.Range("C9").Value - 1
For i = 0 To numSteps
    Application.ScreenUpdating = False
    pasteRow = 8 + i
    Sheet7.Range("B4").Value = i
    Sheet7.Range("C4:G4").Copy
    Sheet2.Cells(pasteRow, 6).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
Next i
End Sub

And so finally, the calculated results are all copied into a single table, and we have the data we are looking for. From there it’s a simple process of graphing these results and looking up the recommended target net worth/withdrawal rate for your given acceptable chance of success.

The analysis inputs on the left, the calculated results in the center, and the processed results on the right.

In the graph of the results, we can see the statistical noise that I mentioned earlier. If I were generating results to publish I would bring the number of Monte Carlo runs up to 10,000, which would smooth those lines out a bit and let us take a smaller step size (at the cost of taking 20 minutes to finish). For trying out different scenarios though, this is fine.

Some other implementation notes

One input that isn’t quite clear is the ‘Spending Correlation to Market’. This variable is meant to tie your spending to the market, so that you spend less in a down market and more in an up market. Say that your spending for that year randomly comes out to +2σ. Setting this correlation to 100% means that if the total market movement (i.e. movement of stocks and bonds as weighted by your investment ratio) is -2σ, then spending will adjusted to -2σ. If the correlation is set to say 75%, then in this scenario spending will be adjusted to -1σ (which is 75% of the distance between the two variables). On the other far end, setting the correlation to -100% will mean that you tend to spend more in a down market and less in an up market. (I don’t recommend living this way).

There are several different basic bond-stock allocation strategies I’ve built in, and these are implemented by populating a few tables and deciding which one to use through a series of nested ‘if’ statements that check the strategy the user has selected. There’s a ‘custom’ option if you want to throw in your own strategy without messing with the defaults.

Closing

I hope this was helpful in explaining what goes into this financial simulation, and perhaps helpful for building your own. Finance is not my field, and if I’ve made some major mistakes then please let me know so I can correct them, but otherwise I encourage you to download the spreadsheet and play around with it.

A FIRE Number Targeter Using Monte Carlo Simulations

I spent last Saturday and Sunday putting together a spreadsheet to help me determine my FIRE number, which is the amount of money you have to accumulate to be fully Financially Independent and Retire Early from the invested returns alone. (If you’re new to the concept of early retirement, here is a post that explains the idea.) This post will describe the basics of how to use this spreadsheet, and my next post explains how it works.

Overview

The standard way of finding your FIRE number is the ‘4% rule’, which is the result of the Trinity study. The authors of that study determined that shooting for a 4% withdrawal rate (or in other words, making sure that your nest egg is at least 25x your yearly expenses), will nearly always guarantee that you wouldn’t run out of funds over any 30-year time period in history. However while the 4% rule is a good rule of thumb, the Trinity study lacks a lot of considerations that are relevant to someone pursuing FIRE:

  • A 30 year time horizon is not nearly long enough. 50-90 year time horizons are more realistic.
  • There is no consideration for earning money in retirement.
  • There is no consideration for variability in spending.
  • There is no consideration for adjusting spending to market behavior.
  • There is no consideration for lifestyle inflation.
  • Social security is completely ignored.
  • Only one asset allocation strategy is looked at (75% stocks/25% bonds).
  • The study relies solely on historical returns. This is ok for its 30-year spans of market behavior, but there are not enough 80-year spans in history to build up an accurate picture of potential scenarios for us. This is also a weakness of the popular tool FIREcalc. We need a predictive model.

So I decided that I needed a way to address these considerations, and the result is this Excel spreadsheet. It uses Monte Carlo analysis to run 1000 test cases of a lifetime of financial performance. I am releasing it under the Creative Commons BY-SA license, which basically means that you are more than welcome to use it for anything, tear it apart, and post it anywhere, as long as you credit here as the source. You can download the spreadsheet here from google drive, and note that you’ll have to enable content to run it.

Inputs

Open up the spreadsheet and this is what you see. The cells you need to edit are the light yellow ones on the left. All values here are in current year dollars, and will be adjusted for inflation as the years go on so that purchasing power stays constant.

The analysis inputs on the left, the calculated results in the center, and the processed results on the right.

The analysis inputs on the left, the calculated results in the center, and the processed results on the right.

For the basic inputs, fill in your current age and the age you want to hit FIRE. Your minimum expenses are your basic housing, food, hygiene, medical, and transportation expenses, and this is the value that below which the simulation considers the outcome a failure. Your target spending is how much you want to spend each year, and the spending variability is how much your actual spending may vary on either side of that. Your average yearly side income is how much you expect to make in retirement, and that has variability as well. Finally, your acceptable chance of success is how sure you want to be that this will all work out in the end. No strategy is absolutely 100% successful, but if you are cautious enough, there are many scenarios that will give you 99.9% success. Personally I’m more of a risk taker and am shooting for around 75-80%.

The investment allocations section is fairly straightforward. I built in several asset allocation strategies: ‘Fixed Ratios’, ‘Age in Bonds‘, ‘Age-10 in Bonds’, and ‘Age-20 in Bonds’. There is also a ‘Custom’ option for which you’ll need to fill out the values for manually in the ‘Scratch Calcs’ tab of the spreadsheet.

Below this we find the option to include social security payouts. You can estimate your yearly social security payout using this government tool, and feel free to be as pessimistic as you like in guessing whether social security will be around by the time you hit the official ‘retirement age’.

Your spending correlation to market is a variable that lets you adjust your spending to how the market is doing. So if the market is having a bad year and your correlation is 50%, you will be making moderate adjustments to lower your spending accordingly. This value can range between -100% and +100%, where negative numbers mean that your spending increases if the market is down (for whatever reason).

Lifestyle inflation is bad. But you can account for it here as well.

And finally at the bottom we have simulation inputs that define what starting values we want to run the simulation for. Because we are only running 1000 test cases, a step size less than 0.25% is essentially worthless because it will get lost in the statistical noise.

Simulation inputs.

That’s it for inputs! If you want to use the spreadsheet as-is, just hit the ‘Recalculate Results’ button and let it chug away. It will take about a minute to run.

Reading the Results

As the spreadsheet works away, you will start to see the middle table fill up with results. These are the raw outputs from the simulation, and give the success rates for different time horizons (by default I chose 20, 40, and 100 years), for different starting sizes of your investment portfolio.

If you want time horizons others than those listed, you can change where the cells in the ‘Table to Calculate On’ in the ‘Scratch Calcs’ tab of the spreadsheet are pointing to.

Table of raw results.

These raw results are then graphed and compared to your acceptable chance of success, to give you your recommended target net worth, both in current year dollars and in the inflation-adjusted dollars of your starting retirement age. You can see in the graph the success curve as a function of time horizon and starting withdrawal rate. Note that these curves are slightly noisy (ie not smooth), which is a result of the statistical variation that is inherent to Monte Carlo simulations.

Processed results that give you your personal recommendation.

And that’s everything! If you’re interested in how these work or if you want to modify or extend this simulation, check out the post on how the internals of the simulation works. Otherwise, I hope this is a useful tool for you in planning for your own future, and let me know if it helped you in determining your own FIRE number.