Tuesday, August 28, 2012

“To Scale Out or Not to Scale Out?:” Build Your Own Fixed Risk Money Management Monte Carlo Simulator in Excel


What we will attempt to do is build a Monte Carlo Simulation simulating 1000 runs of 500 trades each with multiple contracts, multiple profit targets, multiple stop losses and multiple probabilities that each is hit/not hit.  We will be all in on the initial purchase but pyramid or “scale out” based on specific profit targets being hit.

First, determine how many profit targets you are going to have.  Divide the total number of contracts you are trading by this number.  For this example, assume that you have 3 contracts with 3 separate profit targets.  We will define these as profit targets #1, #2 and #3.

Second, determine your initial “drop dead” stop loss.  This will be the amount of money you will lose per contract assuming that profit target #1 is not hit.  You would be “all out” at this point.

Third, determine your initial risk to reward ratio for each of your profit targets.  For this example, we will assume the following:

Profit Target #1:  Initially risking 10 ticks for a Reward of 20 ticks (1:2 R/R Profile)

Profit Target #2:  Initially risking 10 ticks for a Reward of 30 ticks (1:3 R/R Profile)

Profit Target #3:  Initially risking 10 ticks for a Reward of 40 ticks (1:4 R/R Profile)

Fourth, determine how you will scale out or move your stop loss up if and when Profit Targets #1 and #2 are hit.  For this example, we will do the following:

Scale Out #1:  If Profit Target #1 is hit, we will exit the first contract then we will move the stop loss on the 2 remaining contracts to breakeven plus a tick.

Scale Out #2:  If Profit Target #2 is hit, then we will move the stop loss on the 1 remaining contract to Profit Target #1.

Fifth, determine the Winning Percentage (WP) for each of the individual scenarios that you could have for your scale outs and profit targets.  In this example, we basically have 4 scenarios:

Scenario #1:  Call this the “Worst Case Scenario.”  None of the profit targets are hit and we are stopped out on all 3 contracts.  Our loss is equal to 10 ticks times 3 contracts.

Scenario #2:  Profit Target #1 is hit on 1 contract, but we are stopped out on the remaining 2 contracts at breakeven plus a tick (Scale Out #1).  If Scenario #2 occurs, then we will have 1 contract at a 1:2 R/R and 2 contracts at a breakeven plus a tick for a total profit of 22 ticks.

Scenario #3:  Profit Target #1 is hit on 1 contract and Profit Target #2 is hit on 1 contract, but we are stopped out on the remaining 1 contract at Profit Target #1 (Scale Out #2). If Scenario 3 occurs, then we will have 1 contract at a 20 Ticks, 1 contract at 30 ticks and 1 contract at 20 ticks.

Scenario #4:  Call this the “Best Case Scenario.”  Profit Targets #1, #2 and #3 are all hit.  If Scenario 4 occurs, then we will have 1 contract at a 20 Ticks, 1 contract at 30 ticks and 1 contract at 40 ticks.

Assume for a second that on any given trade, the Stop Loss and Profit Targets happen the following percentage of the time.  Think of the table below as the average that, on a given trade, all 3 contracts would have reached the ultimate profit target or been stopped out:

Scenario        
Probability
Total Profit/(Loss)
#1
50%
(30) ticks
#2
25%
+22 Ticks
#3
15%
+70 Ticks
#4
10%
+90 Ticks

Fifth, we will also be using fixed risk money management strategies.  If you recall, this is a type of money management strategies that does 2 things:

1.         It limits the risk on any one trade to a fixed percentage of the account equal to the Account Value divided by your Average Total Loss (across all contracts traded).  For example, if you had an initial account value of $10,000 and never wanted to risk more than 1%, then your fixed risk on any one trade would be $100.  Essentially, you would have 100 capital units to begin with.

2.         It allows the account to compound with the same initial risk on each trade.  If your account grows to $20,000 after 100 trades and you still want to risk 1% of your account, then you should be willing to risk $200 on the next trade. 

One problem that I ran into last time is that what happens when the account value dips below a certain threshold where the individual trade risk is greater than the fixed risk.  To illustrate, take the above example – we have a 1% fixed risk parameter on a $10,000 account.  That means that no more than $100 could be risked on a given trade.  If the first trade in the series is a loss, then our account is now at $9,900.  Our fixed risk is then 1% or a maximum of $99 on the next trade.  If our average loss or individual trade risk is $100, then technically you can’t take the next trade – you would have to risk $0.  This results in no additional trades being taken in the series.  One way to fix this is to delineate between the individual trade risk and the fixed risk and simply reduce the number of contracts.  I will show you how these two work together below.  Another way would be to put a minimum contract specification in your simulation.  This would specify that, no matter the fixed risk on the next trade, you would always trade 1 contract.

Another problem that was encountered last time is the situation where you reach an unrealistic number of contracts traded.  In one instance, the simulation returned that we should trade 2,000 contracts on the next trade.  This is unrealistic for most retail traders.  A simple fix for the simulation is to place a limit on the number of contracts traded. 

Building Our Excel Monte Carlo Simulator

The first step is to create your input page and call it “INPUT” like this:


In this example, we will be simulating 500 trades in the account and repeating this 1000 times.  We will assume that we will trade the NQ futures contract with a tick value of $5 per tick.  We will initially trade 3 contracts with a risk per contract (Scenario #1 above) of 10 ticks.  We never want to risk more than .5% of our account on any given trade.  Finally, we assume that the Scenarios outlined above have the given winning percentages, resulting in a gain/loss per contract outlined above.
Under the 2nd tab, create a random number generator for each simulation and call it “Rand”.  We want a random number between 0 and 1 that is rounded to 2 decimal places.  You can generate a random number with these parameters by entering the following:

=ROUND(Rand(),2)

The 2nd Tab then looks like this and is copied over 500 columns (trades are illustrated in each column) and down 1000 rows (simulations are contained in each row):


Under the 3rd tab, we create another sheet that returns the profit or loss from the random number generation.  Call the 3rd tab “Result”.  We then apply the following rules based on the winning percentages (“WP”) we entered into the Input tab:

For random numbers less than the WP contained in Scenario 1, return a 1 (this would be a loss). 

For random numbers greater than the WP of Scenario 1 but less than the WP of (Scenario 1 + Scenario 2), return a 2.

For random numbers greater than the WP of (Scenario 1 + Scenario 2) but less than the WP of (Scenario 1 + Scenario 2 + Scenario 3), return a 3.

For random numbers greater than the WP of (Scenario 1 + Scenario 2 + Scenario 3) but less than the WP of (Scenario 1 + Scenario 2 + Scenario 3 + Scenario 4), return a 4.

You can do this by creating a nested “IF” function in Excel.  For cell B2 in the “Results” tab enter the following:

=IF(Rand!B2<Input!$C$17,1,IF(Rand!B2<(Input!$C$17+Input!$C$18),2,IF(Rand!B2<(Input!$C$17+Input!$C$18+Input!$C$19),3,4)))

Our Results tab looks like this:


For instance, in cell b2 under the Rand tab, it returned the random number 0.6 for our first trade in our first simulation.  We judge this to mean that for trade 1 simulation 1, we achieved Scenario 2.  You can see in the picture above, the Result tab returned a 2.

The step will help us determine whether the trade was a win (by achieving either Scenarios 2, 3 or 4) or a loss (by achieving Scenario 1).

For the 4th Tab called WinLoss, enter the following formula in cell c2:

=IF(Result!B2=1,"L","W")

This tells Excel that if the output in the Results tab is equal to 1, return “L” and otherwise return a “W.”  Your WinLoss tab should look like this:


We then want to calculate the maximum number of losers or “L” that we had in a row.  You can do this by creating a 5th tab on your workbook and calling it WLStreak.  Three calculations are required.

In cell c2 of WLStreak, enter the following:

=IF(WinLoss!C2="L",1,0)

This tells Excel that if the outcome of the first trade in the first simulation was a loss or an “L”, enter a 1 and otherwise enter a 0.  Copy this formula down Column C of WLStreak.

In cell d2 of WLStreak, enter the following:

=IF(WinLoss!D2="L",C2+1,0)

This tells Excel that if the outcome of the second trade in the first simulation was a loss or an “L”, add 1 to cell C2 and otherwise enter a 0.  This little formula will calculate your losses in a row and reset them if you have a win.  Copy this over to the last trade and down to the last simulation.

Finally, in cell a2 of WLStreak, enter the following:

=MAX(C2:SH2)

This tells Excel that, for the first simulation, what is the maximum number of losses or “L” that you had in a row.  Cell C2 corresponds to the first trade in the first simulation and cell SH2 corresponds to the last trade in the first simulation.  Copy what you have in cell A2 down each row for the 1000 simulations.

WLStreak tab now looks like this:


You can see in the picture above that the 10th simulation had 17 losses in a row (see cell A11).

Now that we now the outcome of each simulation and trade, we need to construct a 6th tab and call it AccountValue.  We will apply the fixed risk money rules we talked about and allow us to simulate how the account grows and contracts.

The AccountValue tab looks like this:




For column C, reference the starting account value on the input page.  This is where every simulation will start out.  In cell C2, I entered the following formula and copied it down for each simulation:

=Input!$C$3

Next, create a lookup table called “Scen” using the formula tab.  This will be used to lookup the profit or loss per contract on each trade.  Recall that a “1” in the far left column tells us to return Scenario 1.


Back to the Account Value tab, for the first trade in the first simulation, enter the following:

=VLOOKUP(Result!B2,Scen,6)*Input!$C$7+C2

This tells Excel the following:
1.         Lookup the result of the first trade in the Results tab,
2.         Go to the Scen table, find the corresponding row based on the Result,
3.         Return the 6th column over which is the profit/loss per contract,
4.         Multiply it by the number of contracts we started with on the Input page
5.         Add it to our starting account value contained in cell c2.

Next, create a second lookup table and call it NoCon or Number of Contracts.  This is where our FRMM comes into play.  Here is a picture of my NoCon:


Back to the Account Value tab, for the second trade in the first simulation, enter the following:

=VLOOKUP(D2*Input!$F$8/Input!$C$6,NoCon,2)*VLOOKUP(Result!C2,Scen,6)+D2

This formula tells Excel the following:

1.         Multiply the previous trade’s account value by your fixed risk percentage on the   Input page.
2.         Divide it by the points per contract.  This result is the maximum number of            contracts you can trade on this trade.
3.         Go to our NoCon table and lookup this value, returning the number in the second column.  For example, if the result of Step 2 is a 5, the maximum number      of contracts you could trade would be 3.  If Step 2 returns a 28, the maximum number of contracts you could trade would be 27.
4.         Multiply the numbers of contracts times the resulting profit/loss per contract and             add it to the previous trades account value.

You have now created a simulation of this system, scaling out at three different profit targets.  You can add tabs for maximum drawdown, maximum losses and the ulcer index by following the steps here.

Interpreting the Results

One simulation of the above gives the following results:

Ending Account Value
Minimum
42,590
Maximum
298,840
Average
127,341
Median
121,585
Mode
132,270
Standard Deviation
43,688
Low
High
1-SD (68% Confidence)
83,652
171,029
2-SD (95% Confidence)
39,964
214,717
Maximum Drawdown
Minimum
-5.86%
Maximum
-28.56%
Average
-12.26%
Median
-11.68%
Mode
-9.19%
Standard Deviation
3.43%
Low
High
1-SD (68% Confidence)
-8.83%
-15.69%
2-SD (95% Confidence)
-5.40%
-19.12%
Ulcer Index
Minimum
1.58%
Maximum
10.67%
Average
3.63%
Median
3.41%
Standard Deviation
1.16%
Low
High
1-SD (68% Confidence)
2.48%
4.79%
2-SD (95% Confidence)
1.32%
5.95%
Max Losing Streak
Minimum
4
Maximum
17
Average
8.22
Median
8.00
Standard Deviation
1.91
Low
High
1-SD (68% Confidence)
6.30
10.13
2-SD (95% Confidence)
4.39
12.05

This gives a very nice, tradable system with an average ending account value of around 127k, a drawdown of around 12%, UI of 3.63% and max losing streak of 8.  The full report in PDF format can be found here.

The better comparison is whether or not scaling out results in a higher ending account value with a similar risk profile.  If we were to assume that you could get your 1:2 Risk/Reward on all 3 contracts, rather than scaling out, what would the results look like?  Remember, we assumed we hit profit target #1 with all 3 contracts but held on for targets #2 and #3.  Here are the results:

Ending Account Value
Minimum
89,550
Maximum
418,500
Average
240,823
Median
240,150
Mode
285,900
Standard Deviation
50,059
Low
High
1-SD (68% Confidence)
190,764
290,882
2-SD (95% Confidence)
140,705
340,941
Maximum Drawdown
Minimum
-4.65%
Maximum
-21.55%
Average
-9.74%
Median
-9.28%
Mode
-9.09%
Standard Deviation
2.55%
Low
High
1-SD (68% Confidence)
-7.20%
-12.29%
2-SD (95% Confidence)
-4.65%
-14.83%
Ulcer Index
Minimum
1.14%
Maximum
5.71%
Average
2.45%
Median
2.33%
Standard Deviation
0.65%
Low
High
1-SD (68% Confidence)
1.80%
3.09%
2-SD (95% Confidence)
1.16%
3.74%
Max Losing Streak
Minimum
5
Maximum
18
Average
8.19
Median
8.00
Standard Deviation
1.84
Low
High
1-SD (68% Confidence)
6.36
10.03
2-SD (95% Confidence)
4.52
11.87

This looks a lot more promising, since the average account value is almost double with a lower average drawdown and lower UI.  The full report can be found here.

However, what if we accepted more fixed dollar risk on each trade – instead of .5% of the account, we increased it to 1%?  The table is below and the full report can be found here.

Ending Account Value
Minimum
45,590
Maximum
456,000
Average
247,422
Median
247,015
Mode
147,150
Standard Deviation
53,386
Low
High
1-SD (68% Confidence)
194,036
300,808
2-SD (95% Confidence)
140,651
354,194
Maximum Drawdown
Minimum
-7.77%
Maximum
-51.69%
Average
-20.07%
Median
-18.78%
Mode
-15.04%
Standard Deviation
6.63%
Low
High
1-SD (68% Confidence)
-13.44%
-26.70%
2-SD (95% Confidence)
-6.81%
-33.34%
Ulcer Index
Minimum
1.78%
Maximum
25.14%
Average
5.40%
Median
4.77%
Standard Deviation
2.48%
Low
High
1-SD (68% Confidence)
2.92%
7.89%
2-SD (95% Confidence)
0.43%
10.37%
Max Losing Streak
Minimum
5
Maximum
18
Average
8.27
Median
8.00
Standard Deviation
1.84
Low
High
1-SD (68% Confidence)
6.43
10.11
2-SD (95% Confidence)
4.60
11.94


Here is the full report if we can reduce our probability of Scenario 1 (all out stop loss) to 40% and increase Scenario #2 to 35%.

Hope this helps.  Keep ya mind right.