How do I figure out whether I’m saving enough for retirement? What’s the impact of one bad year in the stock market? How much can inflation erode my nest egg?

These are the types of questions that can be addressed by financial modeling. I’ve written a program that lets you enter data for two people (presumably you and your significant other), and it calculates the probability that you will have enough money to last both of you.

You can use Quicken or other retirement calculators, but they usually do not include a Monte Carlo simulation. Monte Carlo does not mean you take your life’s savings to Monaco and let it ride on roulette red. A Monte Carlo simulation is used to model systems that have some random elements and for which the outcome can’t be expressed by a simple formula. The return on your investments fluctuates from year to year. Low investment returns are worse if you are close to retirement than if you are just starting out. So the timing of the market highs-and-lows relative to your time-to-retirement is an important factor. A Monte Carlo simulation calculates a lifetime of savings and expenses, letting the investment returns vary. Then it repeats that calculation many times, simulating many possible lifetimes.

By reviewing the outcome of a wide range of possible scenarios, you can estimate the probability that you will not outlive your money. The outcome of this program is the amount of money left over when the last member of the couple dies. If you have $0 left over, you have planned exactly correctly. If you have a large shortfall, then you ran out of money. If you have a lot left over, your heirs are happy.

The graph on the top of this post shows a typical result. It is read as “there is about a 25% probability that Pat and Alex will run out of money.” Alternatively stated, there is an 75% probability that they will be ok. (That sounds better, doesn’t it?). Pat and Alex can then experiment to see what is the impact on that probability if they increase or decrease their current savings rate by 1% or change any of the other input parameters. Note that it is almost impossible that you can drive the answer to 100% certainty (unless you zero out the variability).

To run the calculation, download this Excel file, enter your data, and run the macro with CNTRL-r (“r” for retirement).

The input data is on the Input sheet, the calculations are done on the Output sheet, and the resulting graphs are copied back onto the Input sheet — so you can enter the data, start the macro, and in a moment, the result appears before you. Voila! The screen flickers while it calculates, which is annoying. You might want to walk away if you set it up for a long calculation.

To start, enter your name and your significant other’s, replacing “Pat” and “Alex”. Then for each person, enter the:

  • Current age.
  • Expected Retirement Age.
  • Life Expectancy. You can guestimate based upon family history, or you can use the actuarial tables at the CDC. You might want to add a few extra years (5 or 10) to ensure you don’t outlive your money.
  • Current Salary.
  • Percent Salary to Retirement Fund. The current percentage you allocate to retirement funding including 401(k)’s, IRA’s and other investments intended to help fund retirement.
  • Expected Salary Increase. Enter your expected annual percentage salary increase (raise).
  • Social Security Benefits. To be conservative, you can enter $0. After all, who knows if Social Security will be there when we’re ready to retire. You can look up the amount you would expect to receive, according to today’s rules, at the Social Security website.

Enter the financial modeling parameters:

  • Wage Replacement Ratio. This is the fraction of your current income that you would need/want if you retired today. It is usually something like: (Current income) – (Amount you put towards retirement savings) – (Amount you expect to slow down your lifestyle). A typical number is 70-80%. You can use the lower number if your retirement funds are in post-tax savings (like Roth IRA’s).
  • Reduction After First Death. I don’t mean to be grim, but one of you will outlive their partner. This number is the percentage of retirement income that the survivor will need to maintain their standard of living. This number will be greater than 1/2. A reasonable placeholder is 75%.
  • Mean Return on Retirement Fund and Sigma Return on Retirement Fund. This is the average (mean) rate of return on your retirement investments. It is averaged over all of your investments — CD’s, mutual funds, stocks, bonds, gold bouillon, and Pez dispenser collection. The variation in the investment return is in the “Sigma” value. “Sigma” is a bit misleading, because I’m assuming a linear distribution, not a gaussian (Bell) curve. Every year the rate of return can be the “Mean plus or minus Sigma.” For example, if the mean is 6% and sigma 4%, then for any year, the return can range from 2% to 10%.
  • Mean Inflation Rate and Sigma Inflation Rate. Similarly to the rate of return, this is the annual rate of inflation and the expected variation in inflation. The program uses a five-year moving average of the random rate, since historically inflation is less volatile than stock market returns.
  • Number of iterations. This is the number of life scenarios the program will run. The higher the number, the more situations it tests, but it takes longer to calculate. A good large number is 300 -1000. Start with smaller numbers first to get ballpark results, then increase the number of iterations.

When the calculation ends, the Output sheet contains the result of the last scenario and the cumulative results. In the Output sheet, you can see Pat’s and Alex’s ages each year, as well as the amounts they’ve contributed to retirement this year. There is the inflation rate for the year and the moving average. Pat’s retirement need is 0, if Pat is still working. If Pat is retired, then it is the amount Pat will withdraw from the retirement fund. The last column in this section is the Retirement fund, which is the amount in the account at the end of the year. Columns A-N are overwritten with each scenario. Columns P-R contain the results of all simulations (all the “lifetimes”) of the current run. Columns T-U are used to generate the probability curve.

This program is missing a number of real-life factors. If there is interest (no pun intended), I could modify the program. Presently, it does not include:

  • Taxable vs. post-tax savings.
  • Moving to a more conservative investment strategy as you near retirement. This reduces the uncertainty significantly, and decreases the spread in the probability of success.
  • The value of real estate.

Warnings and Disclaimers:

  • In general, you should be cautious about running macros downloaded from the internet. Unscrupulous coders can spread computer viruses through Excel macros. I’ve included the macro source code as a pdf so you can see exactly what it does. You can also see the code from within Excel by following the menu items: “Tools, Macro, Macros, Edit”. To step through the program execution, press F8 repeatedly.
  • I program to get an answer, not to write beautiful code. I’m sure there are better ways of writing this program. I do include a lot of comments so that you can (hopefully) make sense of it. If someone out there who actually writes code for a living wants to collaborate — let me know.
  • Do not rely on this program (or any other) for your actual retirement planning. This code is for educational purposes only. I think it’s useful to experiment by changing variables and seeing the impact to your finances, but it has limitations (see above). Serious retirement planning should be done with the help of a financial professional.

By day, Helen engineers new materials to make computer chips cheaper, better, and faster. When the son goes down (pun intended), she writes about personal finance at Affine Financial Services.