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 Return | Median Return | Skew | (Mean + Median)/2 | Standard Deviation | |

S&P 500 | 11.4% | 13.9% | -0.12 | 12.6% | 19.8% |

T. Bonds | 5.2% | 3.5% | 0.23 | 4.3% | 7.8% |

Inflation | 3.3% | 2.7% | 0.11 | 3.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.

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.

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.

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 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.

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.

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.