Monte Carlo Retirement Simulation Excel Guide (No VBA)

Morningstar puts the safe starting withdrawal rate at 3.9%. That's the rate at which money is still left in 90% of simulated 30-year retirements. Notice the test it uses. It doesn't ask whether an average return works out. It asks how many futures stay funded. A Monte Carlo retirement simulation in Excel asks the same question about your own numbers. (Source: Morningstar, The State of Retirement Income 2025, Dec 2025.)

The short answer is yes, you can build one in Excel. It takes no VBA and no add-ins. You need one random-return formula, a year-by-year ledger, and Excel's Data Table to run the ledger a thousand times. Below is the full build, followed by the parts most tutorials leave out: how to stop the results from shifting, which return distribution to use, and what counts as a "good" success rate.

What a Monte Carlo simulation actually does

Monte Carlo is a fancy name for a simple idea. You can't know what markets will return, so you make up a lot of plausible futures and count how many of them work.

Each future is a random string of yearly returns. Some start with a crash. Some start with a boom. Your plan withdraws money every year in every one of them. At the end you count the futures that still have money left. That share is your success rate.

Why not just assume 7% a year? Because the order of returns matters. Two retirees can earn the same average return and get very different results. The one who hits bad years early is selling investments at low prices to cover spending, and those shares never get to recover. This is called sequence-of-returns risk, and a single-average spreadsheet can't show it.

Monte Carlo retirement simulation Excel build: the layout

Set up an inputs block and a 30-row ledger. Here's one simple layout:

Enter every rate as a decimal. Type 0.05, not 5.

Step 1: Draw a random return

In D10, enter:

=NORM.INV(RAND(),$B$4,$B$5)

RAND() returns a random probability between 0 and 1. NORM.INV converts that probability into a return from a normal distribution with your chosen mean and volatility. MarketXLS uses this approach. A lognormal alternative comes later.

Step 2: Build the yearly ledger

Starting in row 10, use these columns:

• A: Year. 1 through 30.
• B: Starting balance. B10 =$B$1, then B11 =E10.
• C: Withdrawal. C10 =$B$2, then C11 =C10*(1+$B$3) so spending rises with inflation.
• D: Return. The random formula from Step 1, filled down.
• E: Ending balance. =MAX(0,(B10-C10)*(1+D10))

The MAX(0, …) keeps a depleted portfolio at zero so it can't go negative and then recover. Fill everything down to row 39.

Step 3: Flag success

In H1, enter =IF(E39>0,1,0). A 1 means this future lasted all 30 years. A 0 means it ran out.

Press F9 a few times and watch H1 switch between 1 and 0. Each press is a new future.

Step 4: Run 1,000 futures with a Data Table

This step replaces the VBA loop.

1. Put the numbers 1 to 1,000 in J2:J1001.
2. In K1, enter =$H$1.
3. Select J1:K1001.
4. Go to Data > What-If Analysis > Data Table.
5. Leave the row input blank. For the column input, choose any empty, unused cell.

Excel recalculates the whole sheet once for each row, so each row gets new RAND() draws. Microsoft's What-If Analysis guide covers how Data Tables work.

Step 5: Read the result

=AVERAGE(K2:K1001) gives your success rate. If it shows 0.87, then 870 of 1,000 simulated futures stayed funded.

Why your Excel success rate keeps moving (and how to stop it)

Every tutorial mentions this, but few explain what to do about it. Any edit anywhere in the workbook triggers a recalculation, and that draws a fresh set of 1,000 futures. Your 87% turns into 85%, then 88%.

There are two fixes:

• Freeze a run. Copy the Data Table results and use Paste Special > Values. The numbers stop changing.
• Control when it recalculates. Go to Formulas > Calculation Options > Automatic Except for Data Tables. The table then only refreshes when you press F9. Large tables also run much faster this way.

How many trials are enough? That's basic statistics, not opinion. The standard error of a success rate is √(p × (1−p) ÷ n). At an 85% success rate:

At 100 trials, a plan can look like 78% on one run and 92% on the next without anything real changing. At 1,000 trials, the number is stable enough to make decisions with. More trials give you a bit more precision, which is why freefincal's Excel calculator runs 50,000 trials and varies inflation and returns together.

Normal or lognormal returns?

Most tutorials pick one without saying why. Here's the tradeoff.

Normal (NORM.INV) is simple and symmetric. It can produce a return below −100%, which can't happen in reality. With normal stock-like volatility this almost never occurs, but it is possible.

Lognormal treats growth as compounding, so returns can't drop below −100% and the curve leans slightly toward big up years. Kevin Bracker's build uses:

=LOGNORM.INV(RAND(),mean,SD)-1

One catch: Excel's LOGNORM.INV expects the mean and standard deviation of the logarithm of growth, not of the return itself. If you have an expected return m and volatility s, convert them first:

• σ = SQRT(LN(1+s^2/(1+m)^2))
• μ = LN(1+m)-σ^2/2

Then use μ and σ in the formula.

The fat-tails debate

Real markets have more extreme days than a bell curve predicts. Does that matter for a 30-year plan? Experts disagree.

In a guest post on Kitces.com, Derek Tharp argues that fat tails show up in daily and monthly data but largely disappear in annual returns, and that Monte Carlo overstates long-term risk compared with rolling historical returns. Justin Fitzpatrick of Income Lab makes the opposite argument in How Monte Carlo Can Understate Retirement Income Risk Compared to Historical Simulation.

What to take from this: your distribution choice is an assumption, not a fact. Try both. If your plan only passes under one of them, the margin is thin.

What success rate is "good enough"?

There isn't one correct number, but there are useful reference points.

• Morningstar's 90% bar. The 3.9% safe withdrawal rate is set where 90% of simulated 30-year futures stay funded. That's a reasonable benchmark to compare against.
• Your horizon matters as much as the percentage. The Social Security Administration says about one in three 65-year-olds may live to at least 90 (Retirement Information for Medicare Beneficiaries, 2026). A 30-year plan that starts at 65 ends at 95. A plan that ends at 85 is betting against a third of retirees.
• Longevity works in both directions. In What's Your Longevity Assumption?, Kitces looks at whether planners pile caution on caution by assuming a very long life and also demanding a very high success rate.
• A failed future rarely means hitting zero overnight. It usually means you'd have to cut spending at some point. Rob Berger's breakdown of "chance of success" is worth reading on this.

Treat the result as a probability, not a promise.

Where the Excel model falls short

The five-step build is a real Monte Carlo engine. It's also a simple one. Here's what it leaves out:

• Fixed spending. Real retirees cut back after bad years. Guardrail rules are possible in Excel but make the formulas much more complex.
• Health care. Fidelity estimates $172,500 in Medicare premiums, deductibles and prescriptions over retirement for a 65-year-old retiring in 2025, and that figure excludes long-term care (2025 Retiree Health Care Cost Estimate, Jul 2025). One inflation-adjusted spending line hides that cost.
• Social Security timing and taxes. Each needs its own columns and its own assumptions.
• Multiple asset classes. Stocks and bonds that move together need correlated random draws, and that's where Excel gets fragile.
• Comparing scenarios. "Retire at 62 or 65?" means copying the whole model and then keeping both copies in sync.

None of this is a reason to skip the spreadsheet. Building one is the best way to understand what a success rate means. It is a reason to check your result against a more complete engine before you decide anything big.

The faster alternative

If you'd rather get an answer before setting up a Data Table, the Monte Climb retirement calculator needs three numbers: your age, your savings, and your monthly spending. It runs them through 1,000 simulated futures in your browser and gives you a success rate. Nothing is saved until you choose to save it.

The plan you build there runs to age 95 by default, and you can extend it. Medicare premiums and income-based surcharges are charged year by year instead of as a flat guess. If you want to know what's behind the number, ask William, our AI planner, questions like "Am I on track?" or "Can I retire at 62?" He shows his work.

It's planning software, not advice. And it's a fast way to see whether your spreadsheet and a full engine agree.

Build the Excel version to learn how it works. Then show my answer to check it against 1,000 futures.

Frequently asked questions

Can you run a Monte Carlo retirement simulation in Excel without VBA?

Yes. Draw a random return with NORM.INV(RAND(),mean,sd), build a 30-year ledger, add a cell that flags whether the final balance is above zero, and run that flag 1,000 times with Data > What-If Analysis > Data Table. The average of the flags is your success rate.

Why does my Excel Monte Carlo result change every time I edit the sheet?

Every recalculation draws new RAND() values, so you get a new set of futures each time. To freeze a run, copy the results and paste them as values. You can also set Calculation Options to 'Automatic Except for Data Tables' and press F9 only when you want a new run.

How many trials does a retirement Monte Carlo simulation need?

At an 85% success rate, 100 trials can swing by about ±3.6 points from run to run, while 1,000 trials narrow that to about ±1.1 points. For most personal planning, 1,000 or more is enough to make the number stable.

Should I use a normal or lognormal distribution for returns?

Normal is simpler but can produce impossible returns below −100%. Lognormal compounds correctly and can't fall below −100%, but Excel's LOGNORM.INV needs the mean and standard deviation of log growth, so you have to convert your inputs first. Try both. If your plan passes under only one, your margin is thin.

What is a good Monte Carlo success rate for retirement?

There's no single correct number. Morningstar's 2025 safe withdrawal rate research uses a 90% success bar over 30 years, which makes a useful reference point. How long your plan runs matters too: about one in three 65-year-olds may live to 90, so a plan should run well past that age.