A Monte Carlo Simulator Made of One Formula

[Fairman]

A Monte Carlo Simulator Made of One Formula

Goal: simulate hundreds of random trade sequences with your own win rate and payoff, and see the spread of outcomes.

Setup. In a block of cells, set the win rate in B1, the average win in B2, and the average loss in B3. Say 0.40, 2, and minus 1. The expectancy is 0.4 times 2 minus 0.6, which is plus 0.2R.

Column A holds the trade number, 1 to 100. Column B simulates each trade:

=IF(RAND()<$B$1,$B$2,$B$3)

Column C is the cumulative sum, and column D the running peak, as in the drawdown drill. Column E holds the drawdown. Summary cells: final result =C101, and maximum drawdown =MIN(E2:E101).

Every time the sheet recalculates, which you can force with the F9 key, a new random sequence appears. Record the final result and the maximum drawdown for twenty or thirty recalculations, or use the sheet's data table feature to automate hundreds of runs.

What do you see? The expected final result is 20R, but individual runs vary widely. Some end near zero, and some above 40R. Maximum drawdowns typically range from about 6R to 19R, with a median near 10R.

The takeaway is the distribution, not the average. If your account can tolerate the 90th percentile drawdown from these runs, your sizing is probably sound.

Use realistic payoffs. If your wins vary between 1R and 4R, sample from your actual list of results with INDEX and RANDBETWEEN.

Exercise: run it for your own numbers, and write down the worst drawdown in thirty runs.

[Fairman]

The point that the distribution matters more than the average is the key lesson. With an expectancy of plus 0.2R, the average final result over 100 trades is 20R, but the standard deviation of a single trade at a 40 percent win rate with these payoffs is about 1.47R. Over 100 trades, the standard deviation of the total is about 14.7R. So roughly one run in six will end below about 5R, and a few runs will end near zero or below. A trader who expects 20R and gets 3R might think the strategy failed, when it's well within the normal range.

[Amanhasnoname]

I built this exact sheet last winter and the most useful result was a humbling one. My real numbers were a 48 percent win rate and an average win of 1.3R, and in the simulated runs about one in five ended 100 trades below zero even with a positive expectancy. Seeing that in black and white made my own losing months much easier to tolerate.

One improvement I made was sampling from my actual list of trade results with INDEX and RANDBETWEEN, as you suggest at the end, because my wins are not a clean 1.3R. A few outliers of 3R or more were doing most of the work, and when I removed the best two trades from the list, the whole distribution shifted down quite a bit.

I now use the 90th percentile drawdown to set my risk per trade, and I rerun the sheet every quarter with the newest data. It takes five minutes and keeps my sizing from drifting.