Monte Carlo Simulation in a Spreadsheet
Retirement projections that assume a constant 7% annual return hide a dangerous truth: markets don't cooperate. Some years you gain 20%; other years you lose 15%. A downturn early in retirement—when your portfolio is largest and you're making withdrawals—is catastrophic. A downturn near the end, after you've already drawn most of your funds, barely stings. This vulnerability is sequence of returns risk, and Monte Carlo simulation quantifies it.
Instead of asking "Will my plan succeed with average returns?", Monte Carlo asks "What's the probability my plan succeeds across 10,000 randomly sequenced market scenarios?" This article shows you how to build a Monte Carlo simulation in Excel or Google Sheets, then interpret the results to stress-test your retirement plan.
Quick definition: Monte Carlo simulation tests a plan against thousands of randomly generated market return sequences to estimate the probability of success—the chance you won't run out of money in retirement.
Key Takeaways
- Sequence of returns risk: poor returns early in retirement are far worse than identical returns late in retirement.
- Monte Carlo simulation generates thousands of possible market return sequences and runs your retirement plan through each one.
- A 90–95% success rate is typically considered safe; below 85% is risky; above 95% may indicate overconservative assumptions.
- Spreadsheet-based Monte Carlo is simpler than most people think: use RANDBETWEEN or RAND to generate returns, then copy formulas down.
- Sensitivity analysis reveals which variables—return volatility, withdrawal rate, contribution, or longevity—drive success or failure.
Understanding Sequence of Returns Risk
Imagine two investors with identical 30-year records:
Portfolio A: Years 1–10: +15% annually. Years 11–20: +7%. Years 21–30: +1%. Portfolio B: Years 1–10: +1% annually. Years 11–20: +7%. Years 21–30: +15%.
Both have an average return of 7.67% annually. But if you're withdrawing in retirement:
- Portfolio A suffers withdrawals during the boom (years 1–10), drains cash during the lean years (21–30). Final balance: moderate.
- Portfolio B endures withdrawals during the slump (years 1–10), allows compounding to work in years 11–30. Final balance: much higher.
This is sequence of returns risk. Monte Carlo quantifies it by testing your plan against random sequences, not just the average.
The Conceptual Framework
Flowchart
Monte Carlo simulation for retirement planning follows this logic:
- Set parameters: starting portfolio, annual contribution/withdrawal, investment time horizon.
- Define return distribution: What's the expected annual return? The volatility (standard deviation)? Use historical data or reasonable assumptions.
- Generate random returns: Create thousands of return sequences, each drawn from your distribution.
- Run the portfolio forward: For each sequence, compound your portfolio year by year, applying contributions and withdrawals.
- Count successes: How many sequences end with a non-empty portfolio? That's your success rate.
Building a Simple Monte Carlo in Excel
Here's a manageable approach for Excel:
Step 1: Set up your parameters in cells at the top of your spreadsheet:
Mean Annual Return: 7.0%
Return Volatility (StDev): 12.0%
Starting Portfolio: $1,000,000
Annual Withdrawal: $40,000
Years in Retirement: 30
Number of Simulations: 1,000
Step 2: Create a simulation matrix. Build a table with:
- Columns representing each year (1–30).
- Rows representing each simulation (1–1,000).
Example (first few cells):
Simulation | Year 1 | Year 2 | Year 3 | ... | Final Balance
1 | $1,032,000 | $1,045,820 | $981,004 | ... | $142,530
2 | $998,000 | $1,087,120 | $1,156,293 | ... | $2,340,821
3 | $1,087,000 | $989,340 | $1,001,230 | ... | -$45,000 (FAILED)
...
Step 3: Generate random returns. In cell B2 (Year 1, Simulation 1), use:
=NORMINV(RAND(), $B$1, $B$2)
This generates a random return from a normal distribution with mean B$1 (7%) and standard deviation B$2 (12%). RAND() produces a random number between 0 and 1; NORMINV converts it to a return value.
Copy this formula across all years and down all simulations.
Step 4: Build the portfolio sequence. In cell C2, create the ending balance for Year 1:
=($C$1 - $C$4) * (1 + B2)
Where:
- $C$1 = starting portfolio.
- $C$4 = annual withdrawal ($40,000).
- B2 = the random return generated in Step 3.
In cell D2 (Year 2), use:
=MAX(0, (C2 - $C$4) * (1 + C2))
The MAX(0, ...) ensures the balance never goes below zero (once you're out of money, you're out).
Copy this formula rightward for all years.
Step 5: Count successes. At the bottom, add:
Failed (Negative Balance): =COUNTIF(final_balance_column, "<0")
Success Rate: =1 - (Failed / Number_of_Simulations)
For example, if 47 of your 1,000 simulations end with a negative balance, your success rate is 95.3%.
Step 6: Run iterations. Excel doesn't auto-recalculate random numbers by default. Press F9 (Windows) or Command+Shift+F9 (Mac) to recalculate. Each press runs a new set of 1,000 simulations. Run several times to ensure stability (results should cluster around the same percentage, e.g., 92–96% if true success is ~94%).
A Practical Excel Example
Let's build a concrete example. You're retiring with a $1 million portfolio, plan to withdraw $40,000/year (adjusted for 3% inflation annually), and expect 7% returns with 12% volatility over 30 years.
Parameters:
Mean Return: 7.0%
Volatility: 12.0%
Starting Portfolio: $1,000,000
Annual Withdrawal: $40,000 (Year 1, grows 3% annually)
Years in Retirement: 30
Simulations: 1,000
Your spreadsheet looks like:
A | B | C | D | E | ...
Simulation | Year 1 | Year 2 | Year 3 | Year 4 | ...
1 | Return: | (Random) | (Random) | (Random) | ...
| Balance: | $1,032k | $1,045k | $981k | ...
2 | Return: | (Random) | (Random) | (Random) | ...
| Balance: | $998k | $1,087k | $1,156k | ...
...
After running 1,000 simulations (pressing F9 repeatedly), you might find that 924 simulations end with a positive balance. Success rate: 92.4%.
This means: "Across 1,000 random 30-year market scenarios consistent with 7% average returns and 12% volatility, your plan succeeds 92.4% of the time." Most financial advisors consider 90%+ safe, so you're good.
Monte Carlo in Google Sheets
Google Sheets' NORMINV function works identically to Excel. However, Google Sheets recalculates automatically (sometimes excessively), which can be slow with 1,000 rows.
Tip: Add a manual trigger button using Google Apps Script:
- Create a button (Insert → Drawing, then save).
- Go to Tools → Script editor.
- Paste this code:
function runMonteCarlo() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("A1").setValue(new Date()); // Force recalculation
}
- Save and return to the sheet. Click the button to manually trigger recalculation.
Alternatively, disable automatic calculation:
- Go to Tools → Spreadsheet settings → Iterative calculation → Maximum iterations: 1.
Then press Ctrl+Shift+F9 (or Cmd+Shift+F9 on Mac) to recalculate manually.
Interpreting Success Rates
- 95%+ success rate: You're likely overconservative. Could you withdraw more, retire earlier, or contribute less?
- 90–95% success rate: Comfortable zone. You have a strong buffer against bad luck.
- 85–90% success rate: Acceptable but risky. Small changes in assumptions could push you below 85%.
- <85% success rate: High risk. Consider delaying retirement, reducing spending, or increasing contributions.
These thresholds are conventions, not rules. Your risk tolerance matters. Some retirees accept 80% success; others demand 98%.
Sensitivity Analysis: What Drives Success?
Run your simulation multiple times, varying one assumption each time:
Base case (7% return, 12% volatility, $40k withdrawal): 92.4% success.
Change return to 8%: 96.1% success (4% improvement). Change return to 6%: 87.2% success (5% decline). Conclusion: Return assumption is critical. Each 1% change shifts success by ~5%.
Change volatility to 10%: 93.8% success (minimal change). Change volatility to 15%: 91.1% success (minimal change). Conclusion: Volatility (risk) matters less than the mean return for your plan.
Change withdrawal to $35k: 98.7% success (6% improvement). Change withdrawal to $45k: 85.1% success (7% decline). Conclusion: Withdrawal rate is critical. A 12% difference ($5k on $40k) shifts success by ~7%.
Change time horizon to 35 years (living to 100): 89.5% success (3% decline). Conclusion: Longevity risk is real but modest (each extra 5 years drops success by ~3%).
This analysis reveals your plan's fragility. If success depends heavily on a 7% return and market conditions drop to 5%, you're in trouble. If success is robust across a range of assumptions, you're safer.
Advanced: Correlation and Asset Class Splits
In a real portfolio, stocks and bonds don't move together. During stock crashes, bonds often gain, providing a cushion. Monte Carlo can model this:
Assume 60% stocks, 40% bonds. Stock returns: Mean 10%, StDev 18%. Bond returns: Mean 4%, StDev 6%.
For each simulation year, generate:
Stock return: NORMINV(RAND(), 10%, 18%)
Bond return: NORMINV(RAND(), 4%, 6%)
Portfolio return: 0.60 * stock_return + 0.40 * bond_return
This is more realistic than assuming a single 7% return. The portfolio return will be smoother (lower volatility) than stocks alone, improving your success rate.
For advanced correlation modeling, use a covariance matrix, but that's beyond typical spreadsheet capability. For most purposes, the above blend is sufficient.
Common Mistakes to Avoid
Mistake 1: Assuming a normal distribution. Market returns aren't perfectly normal; they have "fat tails" (extreme events are more common than normal distribution predicts). Your simulation may underestimate catastrophic loss. Use historical return sequences instead (see below).
Mistake 2: Forgetting to include inflation in withdrawals. If your withdrawal is $40,000 today, it should grow 3% each year. Use:
Withdrawal in Year Y = $40,000 * (1.03)^(Y-1)
Mistake 3: Not running enough simulations. 1,000 is the minimum; 10,000 is better. With fewer, randomness introduces noise. With 1,000 simulations, your success rate might vary ±2% between runs; with 10,000, it stabilizes to ±1%.
Mistake 4: Confusing volatility (standard deviation) with downside risk. A portfolio that swings ±15% annually (standard deviation: 15%) has high volatility. But volatility includes upside swings too. For Monte Carlo, both matter; the random nature captures both.
Mistake 5: Ignoring sequence risk entirely. Many retirement calculators report "average case" outcomes. Monte Carlo reveals the distribution: best case, worst case, and everything in between. Ignoring this leads to false confidence.
Alternative: Historical Sequence Simulation
Instead of generating random returns from a distribution, use actual historical return data:
- Pull historical annual S&P 500 returns (available from sources like FRED, https://fred.stlouisfed.org).
- Randomly select a starting year (e.g., 1980, 1987, 2000).
- Use that year's actual returns forward for your entire retirement period.
- Repeat for 1,000 random starting years.
- Count successes.
This approach avoids assumption about distribution and captures real-world fat tails and correlations. It's more robust but requires historical data.
FAQ
Q: What volatility number should I use? A: U.S. stocks historically average 18% volatility; a 60/40 stock/bond portfolio averages ~10%. Use 12–15% for a balanced portfolio if you don't have specific data.
Q: Does success rate increase if I live for fewer years? A: Yes. A 25-year retirement (age 65–90) has higher success than 35 years (65–100). Each additional decade requires more portfolio preservation.
Q: What if my retirement plan includes a pension or Social Security? A: Reduce your annual withdrawal from portfolio by the amount of guaranteed income. If you get $25k/year from Social Security and need $60k, your portfolio withdrawal is only $35k. This typically increases success rate by 10–20%.
Q: Can I use Monte Carlo for a 10-year plan? A: Yes, but sequence risk is lower over short horizons. The payoff of Monte Carlo increases with time horizon. For 10 years, a simple deterministic model is usually sufficient.
Q: Should I run one massive simulation (10,000 rows) or multiple smaller runs (10 × 1,000)? A: Both give similar results, but multiple runs let you see variability. If 5 runs show 91%, 93%, 92%, 94%, 93%, your true success is ~92.6%, with ±1.5% confidence bands.
Q: How often should I update my Monte Carlo? A: Annually, especially after a market crash or recovery. Real results will deviate from your assumptions; recalibrate based on actual portfolio performance.
Q: Can I model a market crash in year 1? A: Monte Carlo naturally includes this possibility (it's one of the 1,000 scenarios). If your success rate is 92% with an average return, one subset of scenarios is "market crash in year 1, then recovery"—and some of those fail. That's the whole point.
Related Concepts
Understanding Monte Carlo's role in comprehensive financial planning:
- Value at Risk (VaR): Estimates the maximum loss you might face in a given timeframe (e.g., "10% chance of losing >20% in a year").
- Confidence intervals: Ranges around your success rate; e.g., "92% ± 2%" means true success is 90–94% with high probability.
- Stress testing: Running specific scenarios (e.g., 1987 crash) rather than random ones.
- Stochastic modeling: General term for probability-based projections; Monte Carlo is one method.
Summary
Monte Carlo simulation transforms retirement planning from a single "what-if" into a probability-based strategy. Rather than hoping that average returns materialize, you stress-test your plan across thousands of realistic market scenarios—some favorable, some hostile—and quantify your true probability of success. The simulation reveals sequence of returns risk, shows which assumptions matter most, and provides the confidence (or warning) you need to retire with conviction.
Building Monte Carlo in a spreadsheet is more accessible than most believe. A few hundred lines of formulas deliver insights that generic calculators cannot. As you approach retirement, running your plan through thousands of random markets isn't paranoia—it's wisdom.