Retirement Wealth & Sequence-of-Returns Simulator

simulation
Wealth Management
Personal Finance
Multi-period retirement wealth decumulation simulation using Latin Hypercube Sampling to model sequence-of-returns risk, portfolio ruin probabilities, and Trinity Study safe withdrawal rates.

Overview

Traditional retirement planning models assume a constant average return (e.g., 7% every year). In reality, market returns vary wildly from year to year. Experiencing negative returns in the first 3 to 5 years of retirement—known as sequence-of-returns risk—forces retirees to liquidate equities at depressed valuations to fund living expenses, permanently destroying capital and leading to premature portfolio exhaustion even when long-term average returns meet historical targets.

The Retirement Wealth & Sequence-of-Returns Simulator models 30-year wealth decumulation under stochastic equity returns, bond returns, and CPI inflation using Latin Hypercube Sampling (LHS). It simulates 5,000 retirement paths across customizable asset allocations, evaluates the Trinity Study 4% withdrawal rule, generates full wealth trajectory fan charts, and computes safe withdrawal rate recommendation ladders.

Retirement Wealth & Sequence-of-Returns Simulator workbook with task pane open

Free Excel alternative baseline

Financial planners traditionally evaluate retirement survival using manual =RAND() formulas or third-party web calculators. Native Excel formulas lack Latin Hypercube Sampling and multi-period path recursion, volatile RAND() spins on every keystroke, and third-party web calculators cannot embed directly into private client spreadsheets or run offline.

This template delivers: - Latin Hypercube Sampling (LHS): Efficient multi-dimensional stratification across 60 annual return and inflation intervals. - Dynamic path recursion: Models beginning-of-year withdrawals and inflation escalation: \(W_{t+1} = \max(0, W_t - C_t) \times (1 + R_{p, t})\). - Trinity Study benchmarking: Validates simulated 4% rule survival rates directly against Cooley, Hubbard, & Walz (1998) historical benchmarks. - Safe Withdrawal Rate (SWR) ladder: Evaluates 30-year portfolio exhaustion probabilities across initial withdrawal rates from 3.0% to 5.0%.

Workbook tour

Sheet and range Description Role
Retirement Model (A6:G14) Asset class return and inflation drivers: mean returns, volatility, min/max bounds. Editable input
Retirement Model (A18:B23) Portfolio and withdrawal policy: starting wealth ($1,000,000), 30-year horizon, equity/bond weights, initial withdrawal ($40,000). Editable input
Retirement Model (A27:B30) Simulation controls: trials (5,000), random seed (2026), target success rate, sampling method. Editable input
Retirement Model (I5:J16) 30-year decumulation summary: ruin probability, success rate, median terminal wealth, P10/P90 wealth, blended portfolio returns. Output
Retirement Model (L5:P12) Wealth trajectory fan ladder: mean, P10, median (P50), and P90 portfolio wealth at 5-year milestones (Year 0 through Year 30). Output
Retirement Model (R5:U10) Safe withdrawal rate ladder: ruin probabilities and success rates across 3.0% to 5.0% initial withdrawal rates. Output
Validation (A5:F11) Closed-form benchmark checks against exact analytical moments (Fisher real return equation, Trinity Study 4% rule). Verification
Guide (A4:B9) Practitioner guide explaining sequence-of-returns risk, Trinity Study methodology, and inflation indexation. Documentation

Using the template

1. Inspect the baseline retirement simulation

  1. Open the workbook. Python for Excel automatically executes 5,000 Latin Hypercube trials across the 30-year retirement horizon.
  2. Review the 30-year decumulation summary (Retirement Model!I5:J16):
    • 30-year Success rate: 87.5% (Ruin probability: 12.5%)
    • Median terminal wealth: $2,037,187
    • Mean terminal wealth: $2,905,438
    • 80% Confidence Interval (P10–P90): $0 to $6,888,982
    • Blended portfolio return (Mean): 7.50% (Std dev: 10.82%)
    • Initial withdrawal rate: 4.00%
  3. Inspect the Wealth trajectory fan ladder (Retirement Model!L5:P12):
    • Year 0: $1,000,000 starting capital.
    • Year 10: Median wealth grows to $1,302,612; P10 drops to $771,535.
    • Year 20: Median wealth reaches $1,656,124; P10 declines to $524,064.
    • Year 30: Median wealth finishes at $2,037,187; in the worst 10% of trials (P10), wealth reaches $0 before Year 30 due to poor early return sequences.
  4. Review the Safe withdrawal rate ladder (Retirement Model!R5:U10):
    • 3.0% withdrawal rate: 98.1% success rate (1.9% ruin probability).
    • 3.5% withdrawal rate: 94.5% success rate (5.5% ruin probability).
    • 4.0% withdrawal rate: 87.5% success rate (12.5% ruin probability).
    • 4.5% withdrawal rate: 76.9% success rate (23.1% ruin probability).
    • 5.0% withdrawal rate: 64.4% success rate (35.6% ruin probability).

2. Adjust portfolio asset allocation

  1. On the Retirement Model sheet, adjust the equity allocation weight in Retirement Model!B20 from 60% (0.60) to 80% (0.80).
  2. Observe the automatic recalculation:
    • Median terminal wealth increases due to higher expected equity returns.
    • Tail dispersion widens, reflecting higher equity volatility.
    • The Validation sheet updates to show "n/a (model edited)" reflecting departure from the baseline literature benchmark.

Customizing

Add a spending floor or ceiling

  1. To model dynamic spending rules (such as Guyton-Klinger guardrails), modify the annual spending update in notebook.py.
  2. Re-running the simulation reveals how flexible spending rules reduce ruin probabilities compared to rigid inflation-adjusted withdrawals.

Developer reference

  • Module coordinates: Authored in templates/simulation/retirement-risk/.
  • Shared engine: Inlines tested routines from templates/simulation/engine/ (sampler.py, correlation.py, metrics.py, sensitivity.py).
  • Benchmarking: Validated against Cooley, Hubbard, & Walz (1998) The Trinity Study historical success rates matching within 0.6% and Fisher equation exact real returns.

Changelog

  • 2026-10-28: Initial release of Phase 2 Retirement Wealth & Sequence-of-Returns Simulator with Latin Hypercube Sampling, 30-year dynamic path recursion, and Trinity Study validation.