For those interested in how I got the probability numbers for reaching retirement goals, a little fun with excel.

I used the random number generator =RAND() for each possible outcome to simulate the randomness of the return of our investments for both stocks and bonds.  My example used a 30 year old in 2018, investing for 35 years until age 65 (year 2053).  Stock and bond returns for each year are generated based on our random number and the mean and standard deviation we have chosen to use, using the excel function =NORM.INV(A1, 10.4, 18.3) and NORM.INV(B1, 4.0, 2.8) assuming the random number for stocks and bonds is in cell A1 and B1 respectively, and you are using the 10 year averages for mean and standard deviation I mentioned in an earlier post for stocks and bonds.

I used an allocation following my recommended asset allocation based on age.  The overall return for stocks and bonds is calculated by using the randomly assigned returns from above and adding in our deposits made that year.  I also rebalanced at the end of the year to get us back into the proper allocation for the upcoming year and use that value when calculating my return for the next year.  This is repeated for every year until retirement.

Running this calculation once represents one potential future, or one run of the Monte Carlo simulation.  We really want to run 1000’s of simulations, but for this case, I’ve just run 100 for simplicity.  Each time I recalculate the sheet (hint, press F9), I record that bottom line number.  Note, you want to copy and paste the value of this cell into another cell, not just do a strict copy and paste, otherwise all your numbers will be changing.  For 100 runs, I got the following results:

For our purposes, these represent the universe of outcomes.  The randomness of returns keeps things far from guaranteed, but we can get a sense of how good or bad things can be.  I then use this list and the COUNTIF function in excel to determine how many “futures” get above certain thresholds.  For example if the data above is in cells M1 to M100 and I’m looking for how many cases were above $1.2M, that is =COUNTIF(M1:M100, “>1200000”).  For various thresholds, the results are below

There are still a bunch of assumptions here, most notably that the return for stocks and bonds for the next 35 years will look a lot like the past 10, but this philosophy gives some insight for making decisions in a very unsure world.  For instance, from the above you can feel good, but not great, about getting to at least $1.2M, but if you’re counting on $4M, you better be a very lucky person.  I know, Han Solo said “Never tell me the odds!”  I’m guessing Han had a substandard retirement plan.  You want to know the odds.