I promised a quick lesson in the do-it-yourself Monte Carlo analysis so here goes.

First, some supplies:?

MCSim Excel Add-In: Just?drop this in Application Data\Microsoft\AddIns under your Documents and Settings directory.? Then, in Excel, Tools/Add-Ins… to install.

Resample Demo: A demo worksheet to show how to create a random sample from your raw data.

To begin, look at the column labeled Resampled in the Excel demo.? Here you can see a formula that you can use to create a random data set with replacement from your actual raw data.? Note that the data in the first column are meant to represent PL numbers of some system or daily stock price changes.? After resampling, I can create a series that represents the chart for my phantom stock or whatever this is.? Now every time you press F9, you see a new random sample based on your real data.

Now for MCSim.? Select the cell for the max drawdown.? Go to Tools/MCSim…(assuming you installed it), change the reps to something low like 100 and click Proceed.? After a while, you’ll have your results.? You’ll want to use more reps when you want to do a serious analysis but it will take much longer.? Your results will show you the average, SD, Max and Min of the variable you measured, in this case the Maximum Drawdown.? The answer I got (your result might be slightly different with so few reps) was about 26% with a SD of 12.6%.? The max was 88%.? So quite a volatile set of data but you can begin to see how this is useful.? This would tell you that if you wanted to limit drawdowns to no more than 10% you’d probably want to trade this system or stock at about 40% of the size that was used to create this data set and probably less since you should consider the SD too.? Say that you wanted to base it off of +2 SDs.? You’d then need to limit things to 20% of the size (10/(26+12.6*2)) to control your risk appropriately.? In fact, I included a cell called Scale that you can use to adjust the leverage of the sample series and then rerun your MC simulation.? Think of it as the percent of equity you’d use to purchase this stock.

Hopefully, you can imagine many uses of the MCSim.? Basically it works for any formula result you want to test as long as somewhere on the page, the formula result is linked to a RAND() function so its value changes on recalculation.? I think this tool is invaluable and should be used by investors of all types to help shed light on the all important question of risk and position sizing.? It’s not a perfect tool and doesn’t erase risk but it certainly helps you understand it.

P.S.? There is another Excel Add-In that contains both a resample function and and Monte Carlo simulator among hundreds of other obscure things called PopTools that seems very good.? Give it a try as an alternative to the above tools.? I thought it would be easier to share MCSim but PopTools is probably a more powerful tool and having a Resample function that doesn’t require writing that complicated formula I gave you is nice.? It’s also light years faster, completing the 100 reps in less than 1 second.? Also, Jason, the PopTools simulator will allow you to put in a test criteria like “How many times did it exceed 10%” which should be perfect for your drawdown analysis techniques.