[Disclaimer – Apologies in advance, this has turned into a bit of a beast. There is a photo of a dog wearing boots and a hat at the end, so focus.]

We all love a good Financial Independence projection spreadsheet, even if it is one created on the sly whilst at work.

My projections come in a variety of complexities. From the simplest that projects a single amount forward with a fixed rate of return to the more complicated that build in different asset classes, monthly contributions, annual payrises, one off expenditures and bonuses.

They are good vehicle for quickly testing an investment strategy and projecting towards a desired fund value at some future date. What they do miss is the effects of the volatility that is inherent in any investment strategy (even investing just in cash will have **some volatility**, through varying interest and inflation rates and perhaps the, albeit small, credit risk on banks).

**Volatility**

We all know that money invested in the markets is subject to it’s whims. We accept the short term volatility with the expectation that in the long run, with sufficient volatility, we should reap a positive benefit from the market.

The volatility is hopefully just noise as the market randomly jumps about some actual long term return. The more volatile a market is (or any system) the more it will appear to move about it’s long term average return.

So, it would be good to include some kind of volatility in our own projections, whilst also keeping things as simple as possible so we don’t lose sight of what we are trying to achieve.

For example it would be good to assign a different expected return and volatility (based on historic data) to each part of our asset allocation and then see how this effects it volatility. I’m aware that there are plenty of calculators out there that do this for you on the ‘web, but playing around with the things from the ground up is a sure fire way to confuse yourself and learn something at the same time.

**We is gonna need us one of them Random variables**

To include volatility in our projections we will need to introduce a ** random variable** into the mixer.

If we wanted to make things complicated we could have multiple random variables (interest rates, yield curves, inflation, return premiums etc). But the finance zombie likes simplicity, so lets only look at introducing a random variable as our expected return. So what is a random variable;

Some dangerous math talk there. What this means is we can use some function to generate a random number… and we can then use this to define our expected rate of return each month. So instead of a smooth return, we will see something that jumps around a bit more like the market does.**So how can we generate random variables?**

Well, we can use distributions to generate a random variables. A distribution allows us to generate random variables within a specific set of parameters. The most useful to us will be the mean of the distribution, which can be used for our expected return. And the Standard Deviation (or the Variance) of the distribution, which is a proxy for the expected volatility of our returns.

I.e. if we expect a return on our portfolio of 5% then we can use a distribution to generate a random variable that has a mean value of 5%. So although every value won’t be 5%, if we took a sample across LOTS of values the mean (the average) would be 5%.

To keep things simple this post will just look at using the uniform distribution. I know that it is wrong on quite a few levels for projecting growth of a portfolio/stock, but it is a very simple distribution and makes the whole thing a little more transparent and easier to understand. Hopefully 🙂**Multiple runs**

We can run our projection model multiple times concurrently and each time it would come to a different end fund value, because the expected return each month is inherently random.

This can give us an idea of a spread of likely outcomes, for example our fund value in 30 years. If we were to use a random variable to define our monthly return (so that it was different each and every month) and perform 10 different runs across 30 years of this we would actually be using 3,600 different estimations for the in month return. 360 (12 * 30) different monthly returns in each of the 10 runs of the model.

It’s helpful to remember that it ** is only a model**, i.e. a simplified version of reality. I don’t think we can currently hope to model the fucking enormous complexity of the markets. Indeed, each layer of complexity in our own models can lead to dangerous spurious accuracy, especially if we don’t understand the model, or more importantly the limitations of the model.

But it can be useful to help visualise actual volatility on a projection of a portfolio and help us to gain a better understanding of what this means for our portfolio.

So let’s have a look, from the ground up, how we can use Excel to bring in some volatility to our projections.

**Rand() function**

Excel has it’s own built in random number generator;

**Randbetween function**

To get over the first hurdle we could use another of Excel’s random number generating functions;

This would give us a random number between 0.15000 and -0.15000 for our expected return, i.e. between +15% and -15%. I have used numbers well into the thousands as the parameters because this Excel function will only return integers.

**, across 30 years.**

*every month*Random Monthly return – 30 years – Run 1 |

And to show how different it can be, a second time with the exact same starting conditions;

Random Monthly return – 30 years – Run 2 |

Ignoring my horrible formatting, this time it looks a less volatile and ends up around £1,150 after 30 years.

We could run mutltiple simulations and see what they look like side by side, for example here are 10 simulations;

Random Monthly return – 30 years – 10 runs |

So we can see some lovely random variation there. One simulation has reached for the stars (go on son) while the other 9 have struggled and all ended up below the starting position. So each of these runs, could represent a possible path for our £1,000 invested.

But there is a couple of problems with this, with respect to projecting our Early Retirement funds.

1 – The mean of our random variable is 0. The random number function in Excel is really a uniform distribution, i.e. each possible value between the upper and lower limits is just as likely to occur as the other. So in my case above +15% is just as likely as -7.99%. A bit of statistics (or common sense) tells us that the mean will be halfway between these limits, so in our case of +15% and -15%, it will work out as 0%.

2 – The model will tend towards 0 as the number of time steps we introduce increases. Not a great property! This is because we are applying % movements to a scenario that is equally likely to go up or down in each time step. A 10% decrease followed by a 10% reduction will not come back to the starting point. E.g. 100 * 90% * 110% = 99. As this happens over and over the fund value will slowly decrease.

**Adding an expected long term mean or a drag**

In an attempt to address both of the problems above we can build in an expected return.

By moving the upper and lower limits so the that mean of the distribution is our expected return. Let’s say we expect our portfolio to generate a ** real** return of 5%. We could then try setting the mean of our uniform distribution to be 5%. We can do this in our example by changing the upper bound to 20,000 and the lower bound to -10,000. Our formula then becomes;

This will then generate a random number between +20% and -10%. The middle of this will be 5%, so the mean annual return from the formula is 5%.

So as before we can turn this into a monthly return for use in projecting our portfolio;

Doing a similar exercise and comparing 10 runs we see the following results;

Uniform Random with expected return of 5% – 10 runs |

WHY HELLO. This looks a bit more promising. Again, we can see volatility in each of the runs and also a variety of returns, with the lowest fund at £3,177 (with an annual return of 3.9%) and the largest at £4,790 (with an annual return of 5.4%). But we are now seeing a general upwards trend in each of the runs, representative of our 5% expected return.

The average return is 4.7% or a final value of £3,950. This is compared to our expected return of 5% that we think our fund will achieve, or a value of £4,322.

In the first example, where we had a mean monthly return of 0% but the fund value would tend towards 0 as the number of time periods tended towards infinity. In this updated model, although the mean from the uniform distribution is 5%, our average return seen tends not be 5% but slightly lower across the 360 months we have projected.

But, not bad for a very simple model 🙂**Volatility of projections**

You can play around with the volatility of the projections. The standard deviation (a good proxy for volatility) of a uniform distribution is;

This still has a mean of 5% but it is much more volatile, which is a bit intuitive as now the annual return can take a value between 40% and -30% rather than +20% and -10%. So we would expect more variation from month to month;

Uniform Random with expected return of 5% and increased volatility- 10 runs |

Bloody hell, that’s volatile. The spread of values after 30 years is from a lower limit of £1,503 up to £3,047.

Again we reach another limitation of a model this simple, as we increase the volatility we increase the chance of big downward jumps in the projection from which the model struggles to recover. We will see an overall positive return but as we increase the volatility it will get less and less than the mean, i.e. further away from our expectation of 5%.**So what is the point?**

You can use simple formula, like the above, to introduce some volatility into your projections. For example we could use

– ** Equity** expected return each month =(1+Randbetween(-30000,40000)/100000)^(1/12). So an mean return of 5% per year.

–

**expected return each month =(1+Randbetween(-500,2000)/100000)^(1/12). So a mean return of 0.75% and much lower volatility.**

*Bonds*And then project our fund forward with different asset allocations to get a feel for how much of an impact the asset allocation actually has.

Last two graphs, I promise.

First with equity allocation at 90% and bonds at 10% and the second with 10% allocated to equity and 90% to bonds;

90% Equity, 10% Bonds |

10% Equity, 90% Bonds |

Pretty much what we would expect to see, right? The portfolio with more allocated to the volatile equities shows a much larger variance in returns, but also the possibility for greater returns. While the portfolio with a heavier allocation in bonds is a much more placid affair.**Limitations**

But, the fact is that this is a pretty limited model (stupid uniform distribution). The uniform distribution implies that each return between the limits is as likely as the other, when really the majority of returns will be close to the mean but there will also be some extreme movement, e.g. a sudden crash.

We can add a Normal distribution or a LogNormal distribution to affairs that will make it a better approximation, but that is for Part 2 🙂

Then… we can add our monthly savings amount and get ourselves an FI projector with some in built volatility. But that is for Part 3.

Christ, I’m tired after that. *Any questions or glaring errors, let me know. If anyone wants the spreadsheet I used, then I can post it.*

Mr Z

As promised;

CerridwenHi,

I cannot pretend to understand how you got where you did with all this, but me and the dog both say "hats off" to you 🙂

The graphs look as if they are doing the same kind of thing as those produced by using FireCalc – but maybe coming at it from a slightly different angle?

(btw I'm not surprised you're tired. Hope you've had a good long lie down in a dark room :-))

Mr ZombieHaha, thanks! 🙂

Just having a quick a look at FireCalc, it looks far more sophisticated! I think that uses actual market data, and then runs a simulation assuming you started in 1871, then 1872, then 1873 and so on. And you can allocate your actual portfolio to that matched assets. Pretty clever 🙂

So where as Firecalc is based on actual historic returns, mine is completely random.

It looks pretty good, I shall probably spend the next hour or so playing with it 🙂

Thanks,

Mr Z

London RobIn the theme of dogs in costumes, you may also enjoy…

🙂