Build Your Own Excel MonteCarlo Simulation to Project Drawdowns in a Trade System

I have covered MonteCarlo simulation in the past, but want to roll it out again.  Remember, a MonteCarlo simulation is simply a way to introduce random results into a trade system’s parameters.  By doing so, you can get a range of values that give statistically significant results.

For this example, I built an Excel spreadsheet that simulated 500 trades and ran 1000 simulations.  This is like making 500 trades of a given trading system 1000 times.  I generally like to use 10,000 simulations but the computer was locking up with numbers that big, so I had to limit it.  However, you can work around this by re-running the simulation and adding up the results.  By using the F9 key in Excel, it will re-calculate the results of the Rand() function.  Do this 10 times and add up the results and you can approximate 10,000 simulations without blowing a processor.

Trading System Parameters

For this first system, I used the following parameters:

Starting Account Size:                     10,000

Average Win:                                     100

Average Loss:                                     -50

Winning Percentage:                       40%

This gives us a reward to risk profile of 2 (average win/average loss), a reasonable winning percentage and a nice sized account balance to start.

You can add in any other parameters that you feel are relevant to your trading system.  Ideally, you would have a trading system idea, back test it on in-sample data in your charting system and then walked it forward with out-of-sample data.  You would then have the parameters described above.

Building the Excel Template

Across the top row in my Excel spreadsheet, I numbered each column from 0 to 500.  Down the left hand column, I numbered each row from 1 to 1000.

For the first simulation (Simulation “0”), I linked the first column to the account size (10,000) in cell $E$5.  This way you can change your starting account size moving forward.

For the first trade, I input the following formula:

=If(Rand()<=Winning Percentage, Average Win, Average Loss) + Starting Account Size

Remember that the “Rand()” function in Excel returns a random number from 0 to 1.  The formula above tells Excel that if the random number generator is equal to or less than “0.4” then it will be counted as a win and the average win amount should be added to the Starting Account Size.  If the random number is greater than 0.4, then the average loss is subtracted from the Starting Account Size.

By copying the formula across 500 columns, you get your first trade simulation.  Copy this down 1000 rows and you have now simulated 1000 different random outcomes for the same trading system.

Here is a screen shot from Excel:

For Simulation No. 1 and Trade No. 1, we had a random number greater than 0.4, so it was counted as a loss.  This dropped our account from 10,000 to 9,950.  Trade No. 2 & No. 3 were also a loss, but Trade No. 4 was a win.

If we were to chart the equity curve of Simulation No. 1, it looks like this:

Along the bottom x-axis, the number of trades are plotted.  Your equity or gain/loss is plotted down the y-axis.  It’s not really important what one simulation gives us but rather what all of the simulations together give us.

You would then find the ending account value for all simulations.

Determining the Maximum Drawdown

Drawdown in any trading system is an important parameter to look at.  It is (almost) equally painful to have a trading system that goes from 10,000 to 5,000 as it is to go from 15,000 to 10,000.  In both cases, you have to ride out a 5,000 loss.

By running this trading system 1000 times, we can start to get an idea of what the maximum, minimum, average and standard deviation of the system will look like.  Standard Deviation measures the volatility of the drawdown from the average.  In an ideal world, it will give us the bounds that we can expect if we trade this system over 500 trades.

The drawdown I wanted in this simulation was the maximum drawdown.  That is, what is the most amount that you can lose from a peak equity point?

I did this by first calculating the maximum drawdown on each trade from the most recent peak equity point.  Obviously, for the first trade this is the difference between the starting account size versus the current account size for Trade No. 1.  For example, if your first trade is a loss of $50 from a $10,000 account, then your peak drawdown after 1 trade is - 0.5%. 

But what happens at say the 150th trade, when your account had peaked at $15,000 and your current account balance is $12,500.  You are still up from your starting account size of $10,000 but down $2,500 from the most recent peak if $15,000.

This can be solved in Excel by using the following formula:

Maximum Drawdown = ((CAV/(Max(SAV:CAV))-1)

Where,

CAV = Account Value at Current Trade

SAV = Starting Account Value

This tells Excel to divide the current account value at the current trade by the greater of either the starting account value or the highest equity point between the starting account value and the current trade.

You would then use the min() function in Excel to find the highest drawdown.

Statistics on Ending Account Value

Now that we have built our MonteCarlo simulation for ending account value and maximum drawdown, we can run descriptive statistics on all 1000 of our simulations.

For ending Account Value, the statistics looks like this:

Ending Account Value
Minimum	9,900
Maximum	20,100
Average	15,026
Median	15,000
Mode	15,900
Standard Deviation	1,641
	Low	High
1-SD (68% Confidence)	13,385	16,667
2-SD (95% Confidence)	11,745	18,307

This tells us that for all 1000 simulations, the minimum ending account value was 9,900 and the maximum ending account value was 20,100.  The average ending account value was 15,026 with a standard deviation of 1,641.  Assuming a normal distribution, we have a 68% confidence level (1 standard deviation from the average) of 13,385 to 16,667.  We have a 95% confidence level (2 standard deviations from the average) of 11,745 to 18,307. 

It is also useful to determine the distributions of the ending account values.  This can be done by using the histogram function under the descriptive statistics tab in Excel or by manually doing it using the Countifs() function in Excel.

By doing so, we get a table like this:

Bins	Occurrences	Ind %	Cum %
10,000	1	0.10%	0.10%
11,000	7	0.70%	0.80%
12,000	23	2.30%	3.10%
13,000	77	7.71%	10.81%
14,000	170	17.02%	27.83%
15,000	228	22.82%	50.65%
16,000	204	20.42%	71.07%
17,000	182	18.22%	89.29%
18,000	79	7.91%	97.20%
19,000	21	2.10%	99.30%
20,000	7	0.70%	100.00%

Read the table like this:  there was 1 time in 1000 simulations of the system where the ending account value was less than $10,000, 7 occurrences where the ending account value was between $10,000 and $11,000 etc.  2.30% of the time, the ending account value was between $11,000 and $12,000 (the Ind% column).  89.29% of the time the account value was not greater than $17,000 (the Cum% column).

The histogram looks like this:

As noted by the standard deviations above, the picture can easily tell you that the majority of ending account values fall between $12,000 and $17,000.  Is this a good ending trade system for you after 500 trades?  You can now decide.

Statistics on Maximum Drawdown

Again, by compiling the statistics of all 1000 simulations, we get the following for our maximum drawdown:

Maximum Drawdown
Minimum	-2.93%
Maximum	-23.36%
Average	-7.12%
Median	-6.51%
Mode	-6.25%
Standard Deviation	2.47%
	Low	High
1-SD (68% Confidence)	-4.65%	-9.58%
2-SD (95% Confidence)	-2.19%	-12.05%

The minimum drawdown was 2.93%; maximum was 23.36% with an average of 7.12%.  Assuming a normal distribution, we can conclude that 95% of the time, the drawdown falls between 2.19% and 12.05%.  Would you trade this system?

Here is the distribution chart and the Histogram:

Bins	Occurrences	Ind %	Cum %
-25%	0	0.00%	0.00%
-24%	0	0.00%	0.00%
-23%	1	0.10%	0.10%
-22%	0	0.00%	0.10%
-21%	0	0.00%	0.10%
-20%	0	0.00%	0.10%
-19%	0	0.00%	0.10%
-18%	0	0.00%	0.10%
-17%	1	0.10%	0.20%
-16%	1	0.10%	0.30%
-15%	4	0.40%	0.70%
-14%	7	0.70%	1.40%
-13%	13	1.30%	2.70%
-12%	20	2.00%	4.70%
-11%	35	3.50%	8.20%
-10%	40	4.00%	12.20%
-9%	73	7.30%	19.50%
-8%	101	10.10%	29.60%
-7%	134	13.40%	43.00%
-6%	191	19.10%	62.10%
-5%	197	19.70%	81.80%
-4%	144	14.40%	96.20%
-3%	36	3.60%	99.80%
-2%	2	0.20%	100.00%
-1%	0	0.00%	100.00%

Ulcer Index:  Another Useful Drawdown Measure

One of the most useful tools to measure drawdown is the Ulcer Index.  The Ulcer Index was created by Peter Martin.  It's designed as a measure of volatility, but only volatility in the downward direction, i.e. the amount of drawdown or retracement occurring over a period.  See the Wikipedia entry.  In sum (from this website):

Ulcer Index measures the depth and duration of percentage drawdowns in price from earlier highs. The greater a drawdown in value, and the longer it takes to recover to earlier highs, the higher the UI. Technically, it is the square root of the mean of the squared percentage drawdowns in value. The squaring effect penalizes large drawdowns proportionately more than small drawdowns (the SD calculation also uses squaring).

In effect, UI measures the "severity" of drawdowns

I calculated the Ulcer Index on all 1000 simulations by squaring all of the drawdown calculation described above for each of the 500 trades.  I then summed each of these up and divided them by 500 to get the average.  Then take the square root of this to find the UI.  For example, for Simulation 1, the UI is 2.05%.

The final UI number is a measure of the pain that was felt in the strategy over the period because it reflects all of the drawdown experience - not just the maximum drawdown but the frequency, magnitude and duration of drawdowns.  A lower UI is preferred over a higher UI.

Running the statistics on all of the simulations gives the following table:

Ulcer Index
Minimum	0.83%
Maximum	9.80%
Average	2.41%
Median	2.15%
Standard Deviation	1.06%
	Low	High
1-SD (68% Confidence)	1.35%	3.47%
2-SD (95% Confidence)	0.29%	4.53%

Putting It All Together

You now have the tools to evaluate the trading system that you designed.  If you want to be more confident, re-run the simulation a couple of times and compile the results by using the F9 key in Excel.  Change some of the parameters and see what that does.  Using Excel, you can simulate all of this.  3 examples are given below:

What happens if your winning percentage goes from 40% to 35%? 

Ending Account Value
Minimum	5,850
Maximum	17,850
Average	11,275
Median	11,250
Mode	12,450
Standard Deviation	1,659
	Low	High
1-SD (68% Confidence)	9,617	12,934
2-SD (95% Confidence)	7,958	14,593

Maximum Drawdown
Minimum	-4.13%
Maximum	-41.50%
Average	-13.36%
Median	-12.03%
Mode	-11.11%
Standard Deviation	5.82%
	Low	High
1-SD (68% Confidence)	-7.54%	-19.18%
2-SD (95% Confidence)	-1.72%	-25.00%

Ulcer Index
Minimum	1.24%
Maximum	26.66%
Average	6.14%
Median	5.13%
Standard Deviation	3.71%
	Low	High
1-SD (68% Confidence)	2.43%	9.84%
2-SD (95% Confidence)	-1.28%	13.55%

What if your R/R profile drops from 2:1 to 1.5:1? 

Ending Account Value
Minimum	5,500
Maximum	14,375
Average	9,996
Median	10,000
Mode	10,000
Standard Deviation	1,356
	Low	High
1-SD (68% Confidence)	8,639	11,352
2-SD (95% Confidence)	7,283	12,708

Maximum Drawdown
Minimum	-4.67%
Maximum	-50.69%
Average	-15.57%
Median	-14.22%
Mode	-12.50%
Standard Deviation	6.67%
	Low	High
1-SD (68% Confidence)	-8.90%	-22.24%
2-SD (95% Confidence)	-2.23%	-28.90%

Ulcer Index
Minimum	1.27%
Maximum	30.01%
Average	7.81%
Median	6.70%
Standard Deviation	4.29%
	Low	High
1-SD (68% Confidence)	3.52%	12.11%
2-SD (95% Confidence)	-0.77%	16.40%

Comparing this to our original 2:1 R/R profile, our average ending account value drops from $15k to less than $10k, the average maximum drawdown goes to 15.5% from 7% and the ulcer index average is at 7.8% from 2.4%.  You can see that just by changing one parameter, you have radically different outcomes.

What if you start with a lower account balance - $5k rather than $10k? 

Ending Account Value
Minimum	4,150
Maximum	15,700
Average	9,933
Median	10,000
Mode	10,300
Standard Deviation	1,657
	Low	High
1-SD (68% Confidence)	8,276	11,590
2-SD (95% Confidence)	6,619	13,247

Maximum Drawdown
Minimum	-3.67%
Maximum	-42.86%
Average	-12.42%
Median	-11.31%
Mode	-12.50%
Standard Deviation	4.84%
	Low		High
1-SD (68% Confidence)	-7.58%		-17.27%
2-SD (95% Confidence)	-2.74%		-22.11%
Ulcer Index
Minimum		1.02%
Maximum		25.49%
Average		4.19%
Median		3.62%
Standard Deviation		2.17%
		Low		High
1-SD (68% Confidence)		2.02%		6.36%
2-SD (95% Confidence)		-0.14%		8.53%