Set three inputs: starting portfolio, withdrawal rate, and retirement length. Withdrawals rise with actual CPI inflation. The workbook replays that plan through every start year with a full retirement of data behind it, 125 of them at 30 years, using Shiller's S&P 500 data, and counts how many ran out of money.
Works in Excel, Google Sheets, and LibreOffice
What's Included
How to Use the Spreadsheet
Every tab uses the same cell colors: gold cells are inputs you edit, grey cells calculate, and slate values come from other tabs. You only ever type in gold cells.
1. Set the three inputs
On the Inputs tab, enter your starting portfolio, the withdrawal rate you want to test, and how many years of retirement to fund. Each year's withdrawal then rises with actual CPI inflation. Retirement length accepts 1 to 50 years.
2. Read the Results tab
Start with the success rate and the count of start years tested. Then look at the worst start year and the year its money ran out, and at the success-by-decade table, which shows which eras were survivable and which were not. The best start year, 1943, ends at roughly $29.9M nominal; the median start year ends near $6.9M.
3. Scan the Backtest matrix for the years that hurt
Each row is one retiree. Follow a failing row across the columns to watch the balance drain year by year, and compare it with a neighboring row that made it. That is sequence-of-returns risk in one screen.
4. Change the rate and compare
Run 4%, then 5%, then 6%, writing the success rate down each time. At 30 years, those three rates survive 125, 111, and 93 of the 125 start years.
What this backtest assumes
- 100% S&P 500 with dividends reinvested. No bonds, no international, no cash buffer.
- No fees and no taxes. A real portfolio pays both, so treat these results as a ceiling.
- Nominal returns, with withdrawals raised by Shiller's CPI from January to January. The Shiller dataset carries both series.
- Shiller's prices are monthly averages of daily closes, not month-end closes.
- One withdrawal rate per run, raised each year by actual inflation and never cut. This is the classic 4% rule setup, not a flexible-spending strategy.
- Start years without a full retirement of data behind them are skipped, which is why a 30-year run tests 125 start years rather than all 154.
No Macros, Pure Formulas
Standard spreadsheet formulas only. No macros, no VBA. Full transparency, works identically in Excel, Google Sheets, and LibreOffice, and no security prompts.
Frequently Asked Questions
It's the share of retirement start years where the portfolio never hit zero. At 4% and 30 years the spreadsheet reports 100%: all 125 start years with a full 30 years of data behind them made it. Push the rate to 5% and 14 of them fail, led by 1969 and 1929.
1929 and the late 1960s. A 1929 retiree took the crash in the first months, and at 4% on $1,000,000 the portfolio still ends 30 years later with about $329,000, the lowest of any start year. The deflation of the early 1930s helped: withdrawals follow actual CPI, so they fell with prices. A 1969 retiree had the opposite problem, weak markets while 1970s inflation pushed every withdrawal up. At 5% both of those run out, 1969 in year 18 and 1929 in year 19.
With measured CPI, not an assumption. Each year's withdrawal is the first-year amount scaled by Shiller's CPI for January of that year over January of the start year, taken at year end after that year's return. That's the Trinity Study method, and the same model as the survival heatmap in the Fire Planner, so the two agree start year by start year. The CPI series is on the Monthly Data tab and in the Shiller dataset.
Yes. Change the withdrawal rate on the Inputs tab and the whole matrix recalculates, one rate per run. Over 30 years: 4% survives all 125 start years (100%), 5% survives 111 (88.8%), and 6% survives 93 (74.4%).
The Fire Planner runs your whole plan: several assets with their own growth rates, per-expense inflation, income streams, debts, and life events. This workbook tests one portfolio at one withdrawal rate, and every number in it is a cell you can click on and audit.
Yes. It's formulas only, no macros, so it opens in Excel, Google Sheets, and LibreOffice. Sheets takes a few seconds to recalculate the roughly 7,700 matrix cells the first time you open it.
Robert Shiller's ie_data.xls at Yale, the same file behind the site's backtesting engine: monthly S&P 500 prices with dividends reinvested, January 1872 through December 2025 in this workbook. Shiller's prices are monthly averages of daily closes, so the returns are a little smoother than close-to-close figures. The dataset page has the CSV and JSON.
Other Free Templates
The FIRE Planning Workbook covers the accumulation side and the drawdown: your FIRE number, Coast and Barista FIRE, a 40-year projection, and a year-by-year retirement drawdown. The Shiller dataset gives you the same monthly returns as CSV and JSON if you'd rather build your own model.
Want the full plan, not one portfolio?
The Fire Planner backtests your actual assets, income, and expenses month by month, with Monte Carlo and stress tests on top.
Open Fire Planner →