The list of questions and answers below probably supply most of the information people need to use the Red Portfolio Black Portfolio spreadsheet. However, if you find yourself with a question not answered here? Use the Nelson CPA contact form to send us your question. We’ll try to get it answered ASAP.

Getting started:
Where can I download current Red Portfolio Black Portfolio spreadsheet?
Why can’t I see spreadsheet formula?

Using spreadsheet:
How do I simulate accumulations?
How do I simulate withdrawals?
Does the spreadsheet calculate withdrawal failure rates?
Will spreadsheet estimate safe withdrawal rates?
How does the spreadsheet estimate returns?

Technical issues:
Can I use geometric means for simulations?
Where can I get arithmetic means and standard deviations?
What do Excel error messages mean and how do I fix?

Where Can I Download Current Spreadsheet?

You can grab the most up-to-date version of the spreadsheet here: RedPortfolioBlackPortfolio

A related comment: We do occasionally update the spreadsheet as we discover bugs or usability issues. Thus, you may want to check back for a more up-to-date version later.

Why Can’t I See the Spreadsheet Formulas?

Okay, an awkward topic: But one of the aggravations of publishing free content and free downloads via a blog? People steal your material.

To minimize that risk, then, I encrypted the spreadsheet with a password and then added copyright notice to the top of the page. I also hid the two worksheets that calculate the Red Portfolio Black Portfolio simulated returns and balances and the internal rates of return. Sorry if this step causes you any inconvenience.

How Do I Simulate Accumulations?

You take a handful of simple steps to simulate how much money you’ll accumulate in the two portfolios:

Enter your initial investment, or current investment portfolio balance, into cell B4.
Enter the annual addition you plan to add to your savings into cell B5.
If you will increase your savings amount over time—such as for inflation—enter the percentage growth into cell B6.
You describe the Red Portfolio’s characteristics by entering your current or planned portfolio’s standard deviation into cell B8 and its arithmetic mean into cell B9.
You describe the “benchmark” Black Portfolio’s characteristics by entering the “benchmark” portfolio’s standard deviation into cell D8 and its arithmetic mean into cell D9.
Press F9 to have Excel recalculate the workbook’s formulas and redraw the line chart. (If Excel doesn’t seem to fully redraw the line chart—which is problem we’ve spotted on some displays—try saving the workbook to force Excel to redraw the chart.)

How Do I Simulate Withdrawals?

I didn’t initially plan to support withdrawal simulations. It turns out though that you can do this simply by entering a negative value into cell B5. The specific steps you take are as follows:

Enter the investment portfolio balance you anticipate at the start of your retirement into cell B4.
Enter the annual withdrawal amount you plan to add to your savings into cell B5. The value entered should be a negative value.
If you will increase your withdrawal amount over time—such as for inflation—enter the inflation into cell B6.
You describe the Red Portfolio’s characteristics by entering your retirement portfolio’s standard deviation into cell B8 and its arithmetic mean into cell B9.
You describe the “benchmark” Black Portfolio’s characteristics by entering the “benchmark” portfolio’s standard deviation into cell D8 and its arithmetic mean into cell D9.
Press F9 to have Excel recalculate the workbook’s formulas and redraw the line chart. (If Excel doesn’t seem to fully redraw the line chart—which is problem we’ve spotted on some displays—try saving the workbook.)

Does the Spreadsheet Calculate Withdrawal Failure Rates?

No. Not really. But it visually shows you how many simulations fail when you’re modeling withdrawal scenarios. The trick? Be sure you use the Red Portfolio inputs for the portfolio you want to analyze failures for. When do that, each line that drops to zero represents a failure. With 100 simulations and 5 failures, for example, you can say that five percent of the simulations modeled “failed.”

You can also spot when a simulation fails. If a red line plummets to zero in year 12, that means retiree runs out of money in year 12. And I mention that for a reason. Probably early failures matter more than later failures. (A retiree is more likely to be living at the time an early failure occurs.) Also consider the effect of the long forecasting horizon of 40 years. Failures occurring far into the future may not matter.

Will Spreadsheet Estimate Safe Withdrawal Rates?

Sort of. The spreadsheet uses historical estimates of portfolio standard deviations and arithmetic means. That approach should mean you’re running simulations within the realm of possibility.

But that said, we’d describe the Red Portfolio Black Portfolio spreadsheet more as tool you use to visualize portfolio variability. And both upside and downside risk.

And then this thought. Using historical standard deviations and arithmetic means? Sure. Probably useful. But many analysts warn returns going forward will be dramatically. Thus you might want to experiment with lower arithmetic means. (As I was originally working on this FAQ, I looked up the returns the mutual fund company I use gives. And those numbers are well below the historical average. Yikes.)

How Does the Spreadsheet Estimate Returns?

The spreadsheet uses two Microsoft Excel functions to calculate returns that show a normal distribution and reflect the standard deviation and arithmetic mean you input. The actual building block formula shows below:

NORMINV(RAND(),StandardDeviation,ArithmeticMean)

An earlier blog post, Stock Market Monte Carlo Simulation, provides working examples of using that basic formula to create a Monte Carlo simulation spreadsheet. You might also want to look at this blog post: Small Business Monte Carlo Simulation.

One other note: The spreadsheet actually estimates two sets of returns: It calculates 100 scenarios for the Red Portfolio standard deviation and arithmetic mean. (Those scenarios get plotted in the chart’s red lines.) And it also calculates 100 separate scenarios for the Black Portfolio standard deviation and arithmetic mean. (Only the worst and best of those get plotted with the two, thick, dashed lines.)

Can I Use Geometric Means for Simulations?

You should use arithmetic means for Monte Carlo simulations. If an investment portfolio delivers 4% one year and then 12% the next year, for example, the arithmetic average equals 8% because (4%+12%)/2 equals 8%.

A geometric mean—also known as an internal rate of return and also known as the compound average growth rate—shows the compounding effects of time. Investment companies by the way report geometric means. When an online calculator tells you some mutual fund or exchange-traded fund earned X percent over two decades, yeah, that too is a geometric mean. But geometric means don’t work for Monte Carlo simulations. Geometric mean returns are slightly lower. And they already effectively adjust for the risk and bouncing about. With an investment that alternates between a 4% and 12% arithmetic return and so delivers an 8% arithmetic average, for example, the geometric average equals 7.93%.

Where Can I Get Arithmetic Means and Standard Deviations?

For the blog posts here and the example inputs, I used estimates of the standard deviation and arithmetic mean return that come from Aswath Damodaran’s web pages at New York University’s Stern Business School: Historical Returns on Stocks, Bonds and Bills. Using Professor Damodaran’s data, for a “100% stocks” portfolio I got a standard deviation of about 19.5% and an arithmetic average return of about 11.5%. For a balanced portfolio of “70% stocks and 30% bonds,” I calculated a standard deviation of about 14% and an arithmetic average of about 9.5%.

You can also get a great table of annual already-calculated arithmetic returns for several asset classes (from 1928 through 2022) from Wade Pfau’s excellent resource: Historical Market Returns.

A tool like Portfolio Visualizer’s Backtest Asset Class Allocation calculates standard deviations and geometric means of existing portfolios. But you can then convert the geometric mean to an arithmetic mean, and then use that for your simulations.

One other note: You can roughly estimate the equivalent arithmetic mean for a given geometric mean using a back Michael Kitces described in one of his blog posts about volatility. Specifically, you square the standard deviation and then adding half of this result to the geometric mean. If the geometric mean equals 10%, or .1, and the standard deviation equals 20%, or .2, the arithmetic mean equals approximately 12% as calculated with this formula:

10%+(20%*20%)/2

What do Excel Error Messages Mean and How Do I Fix?

Two situations in our testing produce errors in workbook calculations. First, if the internal rate of return for a scenario is so extreme–either good or bad–that Excel can’t calculate it? Excel will return the #NUM error. Your recourse in this case is to throw out that set of simulations and recalculate another set.

A second error also occasionally appears due to the a catastrophically bad sequence of returns at the very start of a scenario. Excel may return the #NA error in one or more of its simulation results. And that error will ripple through some of the formulas. Again, your record here is throw out the simulations just calculated and recalculate another set.