Not long ago, we built a model in Excel that looked like this:
The problem with this model was that it assumed a static return of 7.5%, and at the end of the video associated with this post, I adjusted the returns for the first 2 years to be -10%, just to see the ramifications.
In this post, we are going to adjust this model so that it can generate random returns based on the average return and standard deviation that we choose. The function that we are going to use to do this in excel 2003 looks like this:
=NORMINV(RAND(),Average Return, Standard Deviation)
In newer versions of Excel a more accurate version of this function is available and it looks like this:
=NOR.MINV(RAND(),Average Return, Standard Deviation)
Very subtly, there is a period or dot between NORM and INV. An egghead could get really caught up in how accurate the NORMINV function is, and although, we are fine in using it for our own modeling purposes, it probably would not cut the mustard if we were going to build an enterprise level retirement income calculator.
So what does the NORMINV function do? It returns a value based on a probability, a mean, and a standard deviation. We are using the RAND function to create a number between 0 and 1, which acts as our probability. We will add our own average return and standard deviation in conjunction with this. The result is that a random return will be produced based our average return and standard deviation, and assuming a normal distribution as explained in the previous post.
There are also plenty of really smart people that will find problems using the Rand function, and you can find discussions on the internet about pseudo-random numbers and reseeding the starting value, but for our purposes, this function also will do nicely.
So, let’s get started. You must begin with the spreadsheet entitled:
Retirement-Planning-Spreadsheet-2
which is available in this post:
Building A Histogram To Understand Stock Market Volatility/
If you don’t like these sorts of static screenshots, please scroll ahead to the video link!
Highlight the cell for the first return:
And type in: =NORMINV(RAND(),0.075,.16)
Then drag this cell down through the rest of the Rate column. You will see the returns have changed and may look something like this:
If you highlight a balnk cell somewhere on the spreadsheet and repeatedly hit the delete button on your keyboard, the returns will change each time. Don’t worry if you don’t have red fonts for the negative values. This can be accomplished with conditional formatting, and I will address it in the accompanying video for this post.
Finally, highlight a blank cell in the spreadsheet and then repeatedly press the delete button. You should see the returns changing for each year, and subsequently, the length of time that the income lasts changes as well.
You can run through a number of iterations, in other words, keep pressing that delete button to create another set of possible returns, and get a feel for how long the income lasts, but the real genius of statistics is that we will eventually let the computer capture a number of results for us and crunch some averages. We are going to add inflation to the model before we do this.
But first, I think it might be fun to perform a little stress test on the current model. To do that, hardcode returns for the first 2 years of -20%.
Back-to-back double digit negative returns are very possible, and your retirement income stream is going to have to be able to withstand the possibility of this sort of onslaught. Once you’ve hardcoded these values, again highlight a blank cell and repeatedly hit the delete key. The difference in the survivability rate just went through the floor did it not?
Wait until we add inflation!
In the meantime, here is the spreadsheet!:
Retirement-Planning-Spreadsheet-3
Finally, here is the video showing this spreadsheet being modified:
Let me know what you think and if you would like to see anything specific added, or have any questions. Thanks!
