Quick Summary
A retirement withdrawal calculator spreadsheet uses the 4 percent rule with configurable rates. Includes safe withdrawal rate research, dynamic spending models, and a downloadable template.
Quick answer. The 4 percent rule says you can withdraw 4 percent of your starting retirement balance in year 1, then increase that dollar amount by inflation each year, with high probability of the portfolio lasting 30 years. A retirement withdrawal calculator spreadsheet implements this with a year-by-year balance projection. Our Retirement Financial Planning Projections template has the formula built in with a configurable rate and a dynamic spending toggle.
The 4 percent rule is the most-cited number in retirement planning. It’s also widely misunderstood. This post covers what the rule actually says, how to implement it in a spreadsheet, when to use a different rate, and how to model dynamic (variable) withdrawals if a static 4 percent feels too rigid.
What the 4 percent rule actually says
Three precise claims, often summarized incorrectly.
- Withdraw 4 percent of your starting balance in year 1. If you retire with $1,000,000, the year 1 withdrawal is $40,000.
- Adjust the dollar amount for inflation in subsequent years. Year 2 withdrawal is $40,000 times (1 + inflation). Year 3 is year 2 times (1 + inflation). And so on.
- High probability the portfolio lasts 30 years. Based on the Trinity Study and follow-on research, around 95 percent of historical 30-year periods would have supported this withdrawal pattern from a 60/40 stock/bond portfolio.
What the rule doesn’t say:
- It doesn’t say withdraw 4 percent of the current balance each year. The 4 percent applies once to determine the starting amount.
- It doesn’t guarantee the portfolio survives. It’s a probability statement, not a certainty.
- It doesn’t account for fees. If you pay 1 percent in fund fees and another 1 percent in advisor fees, the safe rate drops meaningfully.
- It doesn’t apply to retirements longer than 30 years. For 40 plus year horizons (early retirees), 3.25 to 3.5 percent is more honest.
The formula in a spreadsheet
Three columns plus a starting balance.
Column A: Year (1, 2, 3, …) Column B: Beginning balance (B2 = starting portfolio; B3 = D2; B4 = D3; etc.) Column C: Withdrawal
- C2 = StartingBalance * 0.04 (year 1)
- C3 = C2 * (1 + Inflation) (year 2 and beyond) Column D: Ending balance = (B - C) * (1 + Return)
Year by year, drag down for 30 rows. The Ending balance column should stay positive at year 30.
In Google Sheets:
| Year | Beginning | Withdrawal | Ending |
|---|---|---|---|
| 1 | 1,000,000 | 40,000 | 1,028,000 |
| 2 | 1,028,000 | 41,200 | 1,055,008 |
| 3 | 1,055,008 | 42,436 | 1,081,331 |
| … |
Assuming 7 percent nominal return and 3 percent inflation, the table walks year by year. By year 30, the portfolio at these assumptions is around $2,200,000 in nominal dollars (about $900,000 in today’s dollars after inflation). The 4 percent rule survives this scenario.
Of course, returns aren’t constant. If years 1 to 5 produce negative real returns, the picture changes dramatically. This is sequence-of-returns risk; the deterministic spreadsheet doesn’t capture it.
Configurable rate
The “4 percent” is approximate. Different planning horizons and risk tolerances suggest different rates.
| Horizon | Common SWR |
|---|---|
| 20 years | 5.0 to 5.5 percent |
| 30 years | 4.0 percent (the classic rule) |
| 40 years | 3.25 to 3.5 percent |
| 50 years | 3.0 percent |
| 60+ years | Below 3 percent (effectively perpetual) |
The spreadsheet should let you set the rate as an input cell. Type 0.04 for the classic rule; type 0.035 for a conservative 40-year approach.
Our template has a Withdrawal Rate cell that drives the year 1 amount. Change one cell, the entire projection updates.
Dynamic vs static spending
The classic 4 percent rule assumes constant inflation-adjusted spending year after year. Two reasonable variations.
Variable percentage withdrawal (VPW). Each year, withdraw a percentage of the current balance, increasing slightly with age. Years with high returns produce higher withdrawals; bad years produce lower. The portfolio is more volatile but never runs out by definition.
Guardrails (Guyton-Klinger). Start at 4.5 to 5 percent. If the portfolio drops 20 percent below target, cut the withdrawal by 10 percent. If it rises 20 percent above target, increase by 10 percent. Catches sequence-of-returns risk without requiring full annual flexibility.
Both produce higher average withdrawals than the static 4 percent rule because they accept volatility. Both require more spending discipline than retirees often have.
The spreadsheet supports both with a “withdrawal style” dropdown. The static 4 percent is the default; VPW and Guardrails are options for users who want them.
A worked example
Marcus retires at 65 with $1,400,000. Wants to spend $56,000 in year 1 (about 4 percent of starting balance, in today’s dollars).
Static 4 percent:
Year 1: $56,000 withdrawal. Portfolio: $1,400,000 starting, withdraw $56,000, grow remainder at 7 percent. End year 1 balance: $1,438,080.
Year 2: $57,680 withdrawal (3 percent inflation adjustment). End year 2 balance: $1,476,948.
Year 30: Withdrawal is $135,800 (in nominal dollars). End balance: $1,950,000 (nominal) or about $800,000 in today’s dollars.
The plan survives. With margin to spare in this base case.
Stress test: 5 percent real return instead of 4 percent (7 percent nominal minus 3 percent inflation).
Year 30 end balance drops to about $1,100,000 nominal, or about $450,000 today’s dollars. Still positive. Still works.
Stress test: 4 percent real return.
Year 30 end balance drops to about $640,000 nominal, or about $260,000 today’s dollars. Tight. Plan still holds.
Stress test: 3 percent real return.
Year 30 end balance is approximately $300,000 nominal, or $120,000 today’s dollars. Below comfort margin. The rule starts breaking down.
The exercise is more useful than the base case answer. A plan that survives 4 percent real and gets tight at 3 percent real is “reliable to most scenarios” rather than “guaranteed.”
Sequence-of-returns risk
The single biggest gap in deterministic withdrawal calculators. The calculator above assumes a smooth return rate. Real markets aren’t smooth.
If your first 5 retirement years produce negative real returns (a common pattern in market drawdowns), withdrawing a fixed inflation-adjusted dollar amount means selling more shares at lower prices. The portfolio recovers from a smaller base. The plan can fail even if the long-run average return is fine.
The 4 percent rule already accounts for this in its 95 percent historical success rate. But “5 percent of historical sequences would have failed” means roughly 1 in 20 retirees following the rule strictly would have run out. Not zero risk.
Mitigations:
- Hold a cash buffer (1 to 3 years of spending) so you don’t sell stocks during a drawdown.
- Reduce withdrawals 10 percent in years following major drawdowns (informal guardrail).
- Delay Social Security to provide a larger inflation-adjusted floor late in life.
- Use a slightly lower starting rate (3.5 percent) to build margin.
The spreadsheet can model the cash-buffer approach (separate cash allocation that gets drawn first in down years) but can’t model probability without Monte Carlo. For full Monte Carlo retirement modeling, ProjectionLab is the dominant tool; see our ProjectionLab Alternative post for the comparison.
Where the calculator lives
The Retirement Financial Planning Projections template ($49) ships with the withdrawal calculator built in. Inputs:
- Starting balance per account type
- Withdrawal rate (default 4 percent)
- Withdrawal style (static, VPW, guardrails)
- Inflation assumption
- Real return assumption per account type
- Social Security start age and benefit
- Pension start age and benefit
- One-time inflows or outflows
Output: year-by-year balance per account, total portfolio, real (inflation-adjusted) and nominal views, projected end-of-life balance.
If you’d rather build it, the structure above is the entire formula. About 60 minutes from blank sheet to working calculator.
Get the template
- Retirement Financial Planning Projections — 40-year accumulation and withdrawal projection with configurable assumptions.
- Retirement Planning Bundle — Retirement Projections plus Net Worth plus Annual Tax Planner.
- Retirement Planning Bundle — Retirement Projections plus Net Worth plus Annual Tax Planner.
- Retirement Calculator Ultimate ($19) — Multi-account retirement modeling with withdrawal sequencing and Roth-conversion scenarios.