Monte Carlo simulation in Excel: drawdown range from your trades

Contents

1. How do you run a Monte Carlo simulation in Excel?
2. Why do you need the trade list, not the equity curve?
3. How do you set up the reshuffle with RAND and SORTBY?
4. How do you repeat the shuffle hundreds of times?
5. How do you read the result: median or bad-case drawdown?
6. Should you shuffle the trades or resample them with replacement?
7. What are the limits of doing it in a spreadsheet?
8. When should you move from a spreadsheet to research software?
9. Frequently asked questions
10. References

How do you run a Monte Carlo simulation in Excel?

To run a Monte Carlo simulation in Excel, paste your backtest's per-trade results into one column. Give each trade a random number with RAND, reorder the trades with SORTBY, and measure the maximum drawdown. Repeat this a few hundred times with a data table. The spread of those drawdowns shows how deep a bad sequence could go.

A Monte Carlo simulation is a method that repeats a random experiment many times and studies the spread of the outcomes. Think of your trades as a deck of cards. The backtest dealt them in one order. A different deal of the same cards could put five losers in a row near the start, or spread them out. Shuffling the deck hundreds of times shows how long and how deep the bad runs could be.

Drawdown is the drop from the highest equity reached so far to the current equity. The maximum drawdown is the largest such drop in the whole test. The free drawdown calculator gives you the maximum drawdown and recovery of one equity series. A Monte Carlo run gives you hundreds of plausible maximum drawdowns from the same trades.

Why do you need the trade list, not the equity curve?

You need the result of each trade, because only individual trades can be reordered. An equity curve is a running total. Each point already contains every trade before it, so shuffling the points produces nonsense.

Prepare the input like this:

1. One row per closed trade, in the order the backtest took them.
2. Net result in money, after commission and slippage. Gross results make every simulated drawdown too small. See trading costs in backtests for how to set realistic costs.
3. Fixed position size. If the backtest traded one contract every time, the money results can be shuffled directly. If the size changed, convert each trade to a one-contract result first.
4. Pasted as values, not as live links to another file, so recalculation stays fast.

If you only have daily equity, take the difference between consecutive days and shuffle the daily results instead. That works, but it shuffles days, not trades.

How do you set up the reshuffle with RAND and SORTBY?

One shuffle takes six columns on one sheet, and pressing F9 creates a new one. SORTBY is available in Excel for Microsoft 365 and in Excel 2021 and later. The steps below assume 200 trades in cells A2 to A201. Change the row numbers to match your list.

Step	Cell	Enter	What it does
1	A2:A201	Your trade results	The input list, one trade per row.
2	B2, filled down to B201	=RAND()	Gives every trade a random number between 0 and 1. It changes on every recalculation.
3	D2	=SORTBY(A2:A201,B2:B201)	Lists the trades in the order of their random numbers. The result spills down to D201. This is one shuffled sequence.
4	E2, then E3 filled down	E2: =D2 E3: =E2+D3	Running equity of the shuffled sequence, starting from zero.
5	F2, then F3 filled down	F2: =MAX(0,E2) F3: =MAX(F2,E3)	The highest equity reached so far. It starts at zero, the starting equity.
6	G2, filled down	=F2-E2	Drawdown on each trade: highest equity so far minus current equity.
7	J2	=MAX(G2:G201)	Maximum drawdown of this shuffle.

Step 6 is the only formula you need to understand: drawdown = highest equity so far − current equity. "Highest equity so far" is column F, and "current equity" is column E. Both are in money.

A shortcut for steps 2 and 3 is =SORTBY(A2:A201,RANDARRAY(200)) in D2. RANDARRAY creates 200 random numbers at once, so you do not need column B.

How do you repeat the shuffle hundreds of times?

An Excel data table recalculates the sheet once per row and stores each result, so it records hundreds of shuffles for you. Each recalculation draws new random numbers, so each row holds a new maximum drawdown.

1. In L3 to L502, enter the run numbers 1 to 500. They are only labels.
2. In M2, enter =J2, the maximum drawdown cell.
3. Select L2:M502.
4. Go to Data, What-If Analysis, Data Table.
5. Leave "Row input cell" empty. For "Column input cell", choose any empty cell on the same sheet, for example K1. Click OK.
6. Copy M3:M502 and paste it as values in another column. The data table recalculates whenever the sheet changes, and the pasted copy keeps one fixed set of results.

If the workbook becomes slow, set Formulas, Calculation Options, to "Automatic Except for Data Tables". The table then updates only when you press F9.

500 runs is enough to see the shape of the drawdown spread for one strategy. More runs make the bad-case figure steadier. Add more rows for a steadier figure; calculation speed depends on CPU performance.

How do you read the result: median or bad-case drawdown?

Plan your account around the bad-case drawdown, not the median. The median is a typical sequence. The bad case is the sequence you must be able to sit through without stopping the strategy.

Figure	Formula on the pasted results	What it tells you
Median drawdown	=MEDIAN(range)	Half of the shuffles had a smaller drawdown, half a larger one.
95th percentile	=PERCENTILE.INC(range,0.95)	Only 1 shuffle in 20 was worse. A common bad-case figure.
Worst run	=MAX(range)	The deepest drawdown found. It grows as you add runs, so use it only as a rough guide.
Share above your limit	=COUNTIF(range,">"&limit)/500	How often the drawdown passed the amount at which you would stop trading.

Also compare the original backtest's drawdown with the median. If the original sits well below the median, the backtest order was kinder than most shuffles. Your first year of live trading has no reason to be that kind.

Should you shuffle the trades or resample them with replacement?

Use a shuffle to study the path of the trades you have. Use resampling with replacement when you also want the final profit to vary. A shuffle uses every trade exactly once. Resampling with replacement draws trades at random, so some trades appear twice and others not at all. Statisticians call this the bootstrap, and it is used in research on technical trading rules (Sullivan, Timmermann and White, 1999).

To resample in the sheet, replace step 3 with =INDEX($A$2:$A$201,RANDBETWEEN(1,ROWS($A$2:$A$201))) in D2, filled down to D201. ROWS counts the trades in the list, so RANDBETWEEN picks a random row number from the first trade to the last. Add a second output column in the data table that points to E201, the final equity. Then count how many runs end below zero.

Resampling also lets you match the planning horizon. Maximum drawdown grows with the number of trades. If you take about 100 trades a year and want next year's risk, fill the INDEX formula down 100 rows instead of 200.

What are the limits of doing it in a spreadsheet?

A spreadsheet simulation works well for one finished strategy, but the method has three limits you should plan around.

1. Speed. Every run recalculates the whole sheet. A few hundred runs on a few hundred trades are fine. Doing the same for many candidate strategies, or for many thousands of runs each, gets slow, and how slow depends on CPU performance. It is like checking every car in a large car park by hand: possible for one row, impractical for the whole lot.
2. Trade dependence. A shuffle treats every trade as independent of the one before. Real trades often cluster: losers bunch up in choppy or very volatile months. Shuffling breaks the clusters apart, so the bad-case drawdown can come out too small. A block shuffle keeps clusters together. Put each trade's exit month in column C, list the months with UNIQUE, give each month a RAND value, and look that value up for every trade. Then use SORTBY with the month's random value as the first sort key and the original trade order as the second.
3. The same trades, reused. Every run uses only trades from your backtest. The simulation cannot show a market condition the history did not contain. If the strategy was picked as the best of many variants, the trades already carry that selection bias, and every shuffle inherits it (Bailey, Borwein, López de Prado and Zhu, 2014). Monte Carlo measures the risk of the path. It does not test whether the edge exists.

For the third limit, use tests on data the strategy did not see. Walk-forward analysis checks whether the way you build and tune a strategy holds up out of sample. Backtest overfitting research explains why the best of many backtests is usually inflated.

When should you move from a spreadsheet to research software?

Research software is easier once you check more than a handful of strategies, or want the same settings every time. The spreadsheet makes you repeat the setup for each strategy. Results also depend on which cells you remembered to change.

Situation	Spreadsheet	Research software with standard validation
One or two finished strategies	Good fit. You see every step.	Works too.
Dozens of candidates from a search	Slow and repetitive.	Runs with the same settings on each.
Block or horizon-matched resampling	Possible, but needs extra helper columns.	Depends on the software.
Combined with out-of-sample checks	Done separately.	Out-of-sample checks such as walk-forward analysis run with the same settings on each strategy.

In AlphaPrime (Valynox), the trades and drawdown of each strategy appear in the Performance Report, which is the information a Monte Carlo run starts from. Monte Carlo cannot tell you whether the edge holds on unseen data. For that question, AlphaPrime's strategy validation applies Basic, Standard or Extensive validation, including walk-forward analysis, with custom thresholds you set, the same way for every strategy.

What this means if you use AlphaPrime: let strategy validation filter the candidates on out-of-sample behaviour first. Then run a spreadsheet Monte Carlo on the few finalists to size the account around their bad-case drawdown.

Frequently asked questions

Can I run a free Monte Carlo simulation without special software?

Yes. A spreadsheet with RAND, SORTBY and a data table is enough to reshuffle a trade list and record the drawdown of each run. SORTBY needs Excel for Microsoft 365 or Excel 2021 and later. Older versions can sort by a column of RAND values by hand, one run at a time.

Why does every shuffled run end with the same profit?

A shuffle uses every trade exactly once, and a sum does not depend on order. Only the path changes, so only the drawdown changes. If you want the final profit to vary too, resample the trades with replacement instead.

How many Monte Carlo runs do I need for a trading strategy?

A few hundred runs show the shape of the drawdown spread for one strategy. More runs make the 95th percentile steadier. The worst single run keeps growing as you add runs, so plan around a percentile rather than the maximum.

Does a good Monte Carlo result prove a strategy works?

No. Monte Carlo reuses the backtest's own trades, so it measures how bad the order of those trades could be. It cannot show whether the edge is real or overfitted. Out-of-sample tests such as walk-forward analysis answer that question.

References

• Sullivan, R., Timmermann, A., & White, H. (1999). Data-Snooping, Technical Trading Rule Performance, and the Bootstrap. The Journal of Finance, 54(5), 1647–1691.
• Bailey, D. H., Borwein, J., López de Prado, M., & Zhu, Q. J. (2014). Pseudo-Mathematics and Financial Charlatanism: The Effects of Backtest Overfitting on Out-of-Sample Performance. Notices of the American Mathematical Society, 61(5), 458–471.

What AlphaPrime does here

AlphaPrime's strategy validation runs Basic, Standard or Extensive validation, including walk-forward analysis, with custom thresholds. The Performance Report shows equity, drawdown and individual trades for each strategy.

See plans and pricing