Excel MonteCarlo Simulation of Money Management Strategy #1: Fixed-Risk Money Management

Most of the strategy simulations I have reviewed up to this point have not applied any money management rules.  No matter the account size, I have continued to trade 1 contract (assuming we trade futures).  If the account doubled in the simulation, we continued to trade one contract.  If the account had a 50% drawdown, we continued to trade one contract.

For this simulation, I am going to use a fixed risk money management (“FRMM”) strategy. Fixed risk money management limits each trade to a predefined, or fixed, dollar risk. Initially, the fixed dollar risk per trade can be calculated by dividing the starting account by the number of units of money you wish to begin trading with.

FRMM = Account Balance / Number of Units of Money

As the account expands and contracts, the fixed risk strategy will adjust accordingly.

Previously, we were only concerned with Risk to Reward Ratio (as defined by the system’s Average Win divided by the systems Average Loss), Winning Percentage (the percentage of profitable trades) and starting account balance (your starting equity).

However, for the FRMM we need to introduce the following variables:

1.  Individual Trade Risk (“ITR”) – this is the amount of money that we are risking on a given trade.  For example, if through back testing you find that the average loss on a trade is $200, then the Individual Trade Risk is $200.  This may vary based on different signals that the system gives you.  For long entries you may have an ITR of $300 and for short entries an ITR of $200.  The ITR is solely a function of the individual signals of your trading system

2.  Fixed Dollar Risk (“FDR”) -  this is the maximum amount of money that you are willing to lose on any one trade.  Generally, this is based on your current account balance.  If you start with a $20,000 account, the maximum you may be willing to lose on a given trade may be $500. 

3.  Number of Contracts (“NOC”) – this is the FDR divided by the ITR rounded down to the nearest integer.  For an ITR of $200 and an FDR of $500, then the NOC to trade is $500/$200 = 2.5 rounded down to 2.

4.  Capital Units – This is our starting account value divided by our Fixed Dollar Risk.

Example System Parameters

For this simulation, I will use the following parameters:

Average Win:                             $300

Average Loss:                             $100

Starting Account Size:              $20,000

Winning Percentage:                40%

ITR:                                               $100 (Average Loss)

FDR:                                              $300

Using the FRMM principles discussed above, we define our Initial Trade Risk (“ITR”) as our average loss.  This may not always be the case but when back testing a strategy, it is a good number to start with.  I have initially set the fixed dollar risk at $300.  Based on our starting account balance of $20,000, this gives around 66.66 Initial Units of Capital to start with. 

Building Our Spreadsheet

So we still use our Rand() function to determine if the current trade is a win or a loss:

= if(Rand()<=Winning Percentage, Average Win + Prior Account Balance , Average Loss + Prior Account Balance)

However, because we are not using a single contract on each trade, we need to change what the Average Win and Average Loss amounts will be.  This requires us to determine how many contracts we want to trade based on our prior account balance.

Number of Contracts = Available Capital / Fixed Units / Trade Risk

Therefore, the Average Win for a given trade will be:

= (RoundDown(Prior Account Balance / Initial Capital Units / Initial Trade Risk)) * Average Win

The Average Loss for a given trade will be:

= (RoundDown(Prior Account Balance / Initial Capital Units / Initial Trade Risk)) * Average Loss

These formulas tell us how many contracts to trade based on our most recent account balance. 

For example, for Trade No. 1 and Simulation No. 1 above, our prior account balance is 20,000.  The number of contracts is determined by:

=   FDR/ITR

=   Account Value / Units of Money / ITR

=   20,000 / 66.66 Units of Money / 100 Trade Risk

=   3 contracts

Since Excel randomly returned a value greater than .4, it was counted as a loss.  So 3 contracts * -100 average loss now gives us a new account value after one trade of $19,700.

But what happens as our account grows?

After 137 trades (see the top row in the table above), our account value has grown to $117,200.  How many contracts do we trade on the next trade to implement our Fixed Risk Money Management strategy?  For the 138^th trade:

=  117,200/66.66/100

=  17.58 contracts rounded down to 17 contracts

Since Excel randomly determined that our 138^th trade was a win:

=  17 Contracts * $300 Average Win

=  $5,100

The $5,100 was added to $117,200 giving us a new account balance of $122,300.

For the 137^th trade we were never risking more than 1/66^th of our account.  This was as true on our first trade as it was on our 137^th trade.

Interpreting the Results of Fixed Risk Money Management Strategy

Now we can look at our results in through the lenses of ending account value, maximum drawdown and Ulcer Index.

Using the parameters described above and running 1000 simulations of 500 trades, we get the following results:

Ending Account Value
Minimum	139,500
Maximum	14,265,400
Average	1,540,864
Median	1,218,300
Mode	603,000
Standard Deviation	1,210,294
	Low	High
1-SD (68% Confidence)	330,571	2,751,158
2-SD (95% Confidence)	-879,723	3,961,452
Maximum Drawdown
Minimum	-9.69%
Maximum	-37.37%
Average	-16.77%
Median	-16.12%
Mode	-14.29%
Standard Deviation	3.96%
	Low	High
1-SD (68% Confidence)	-12.81%	-20.73%
2-SD (95% Confidence)	-8.85%	-24.68%
Ulcer Index
Minimum	2.71%
Maximum	10.63%
Average	4.67%
Median	4.47%
Standard Deviation	1.06%
	Low	High
1-SD (68% Confidence)	3.60%	5.73%
2-SD (95% Confidence)	2.54%	6.80%

All of the numbers are substantially larger than what we had with our single contract simulation.  Let’s compare Ending Account Value and Maximum Drawdown:

Ending Account Value
	FRMM	Single Contract
Minimum	139,500	32,800
Maximum	14,265,400	64,800
Average	1,540,864	49,793
Median	1,218,300	49,600
Mode	603,000	49,200
Standard Deviation	1,210,294	4,387

Maximum Drawdown
	FRMM	Single Contract
Minimum	-9.69%	-1.97%
Maximum	-37.37%	-13.81%
Average	-16.77%	-4.35%
Median	-16.12%	-4.14%
Mode	-14.29%	-3.45%
Standard Deviation	3.96%	1.42%

A few things to note:

1.  It is a way to compound your account.  We started with the same amount but ended up with a vastly larger account balance using FRMM.  This is a result of adding contracts as our account size grew.  We didn’t do this with the single contract simulation.

2.  Your drawdown will be much larger with FRMM.  The biggest difference is the maximum drawdown.  With FRMM, your average drawdown was 4 times that of a single contract.  The standard deviation was also 4 times larger.  With bigger wins come bigger losses.

3.  The Simulation doesn’t consider Margin Requirements or Limit the Number of Contracts Traded.  FRMM ignores the fact that, sometimes, it is impossible to trade the required number of contracts required of it.  Can you put on a 100 lot trade?  It is possible in some markets but not all.  Our biggest winner in FRMM traded more than 2,000 contracts.  This is likely impossible for even the largest retail traders.  But don’t ignore the real effects that FRMM can have.  One way to limit this is to say that, once you reach a specified number of contracts, you won’t trade any more contracts.

4.  In Reality, You Can Also Increase or Decrease the Fixed Dollar Risk as You Continue to Trade.  One adjustment you can make as your account grows is to increase the fixed dollar risk after a certain number of trades.  You can also increase the number of units of money to reduce your risk of ruin.

Changing Parameters

Below I change some of the parameters to see what effect it has.

What if we had a 1.5 R/R Ratio (Avg Win of 150 and Avg Loss of 100), a 40% WP, $20k Account, ITR of $100 and FDR of $200 (therefore, we never risk more than 1/100^th or 1% of our Account on a given trade)?

Ending Account Value
Minimum	11,550
Maximum	44,800
Average	19,977
Median	19,100
Mode	18,900
Standard Deviation	3,866
	Low	High
1-SD (68% Confidence)	16,112	23,843
2-SD (95% Confidence)	12,246	27,708
Maximum Drawdown
Minimum	-7.47%
Maximum	-44.67%
Average	-20.48%
Median	-19.43%
Mode	-25.00%
Standard Deviation	6.66%
	Low	High
1-SD (68% Confidence)	-13.81%	-27.14%
2-SD (95% Confidence)	-7.15%	-33.81%
Ulcer Index
Minimum	2.70%
Maximum	27.74%
Average	11.14%
Median	10.39%
Standard Deviation	4.72%
	Low	High
1-SD (68% Confidence)	6.41%	15.86%
2-SD (95% Confidence)	1.69%	20.58%

What if we had the same scenario above, but increased our Avg Win to 200 giving us a R/R ratio of 2:1?

Ending Account Value
Minimum	18,500
Maximum	102,500
Average	45,464
Median	43,600
Mode	40,500
Standard Deviation	13,372
	Low	High
1-SD (68% Confidence)	32,092	58,836
2-SD (95% Confidence)	18,719	72,209
Maximum Drawdown
Minimum	-6.26%
Maximum	-36.90%
Average	-13.61%
Median	-12.93%
Mode	-12.50%
Standard Deviation	3.76%
	Low	High
1-SD (68% Confidence)	-9.85%	-17.37%
2-SD (95% Confidence)	-6.09%	-21.12%
Ulcer Index
Minimum	2.03%
Maximum	21.70%
Average	4.92%
Median	4.51%
Standard Deviation	1.90%
	Low	High
1-SD (68% Confidence)	3.02%	6.82%
2-SD (95% Confidence)	1.12%	8.73%

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%