waqqasfarooq.com

To explain Monte Carlo simulation method, I’ll give a quick introduction/motivation of monte-carlo method, then I’ll walk through an example. I will give details the technical concepts as per need, i.e. I won’t just list down definitions/explanations all at once and then use them in an example.

What does the monte carlo method do?

Ever heard someone say: “I bet that I can reach the destination in 10 to 15 minutes”? Or have you ever heard someone say: “I am very confident that the convertable will cost between $60K to $70K”? Monte carlo simulations tries to capture the thought process that goes into generating these ranges: “10 to 15 minutes” or “$60K to $70K”.

If we think about it: the more knowledge we have in a domain, the more precise we can be in producing the range, e.g. if I have a year’s worth of experience in selling cars then I would be confident in giving out the price range: “$50K to $80K” but if I have more experience then I would easily come up with a range of: “$60 – $70K”, notice how the range becomes narrow with more knowledge. In the monte carlo concept this “more knowledge” is the number of simulations/iterations we perform. In other words, the greater the number of simulations, the more precise I can be and this creates a narrower range.

Monte Carlo Example

(Note: this is not a real world example with the intent to clearly teach the process and steps involved in the monte carlo simulation method)
Imagine that you run an options-trading firm with many options traders. Each options trader is given $500,000 to invest in 5 positions with $100,000 in each position. The outcome of each $100,000 position could be either: in-the-money, at-the-money or out-of-the-money at expiration. For simplicity, assume that the probability of each outcome is 1/3 and outcomes of different positions are independent. For the term end performance reporting a trader is successful trader if for the 5 positions (at option expiry) there are one or both of the following:

1. two successive in-the-money
2. three successive at-the-money

Now, let’s use monte carlo simulations to extract useful results from the above scenario:

Let’s build a 99% confidence interval that a trader will be successful using 100 simulations.

We can model this scenario easily in excel. The following images give a step by step guide to do this in excel.

1. We know that the probability of each outcome (in-the-money, at-the-money and out-of-the-money) is 1/3. We can use the rand function in excel to setup the columns at shown in the image below. We setup the the 5 columns to generate a 1/3 probability for each column. Notice that we have a lookup table setup to have 0 = in (in-the-money), 1 = at (at-the-money) and 2 = out (out-of-the-money). All we are doing is substituting the values for 0, 1, and 2 from the lookup table into the columns using the excel VLOOKUP function. So from columns B - F, we have a setup that generates equal probability out of in, at or out-of-the-money samples.

2. Next we want a way to tally the successive in-the-money outcomes. For this we add 4 more columns each column comparing the result of of successive outcome columns. This is shown in the image below.

3. Very similar to step 2 we want to tally the successive at-the-money outcomes. Because our problem statement says that at-the-money should have at least 3 successive outcomes, we add only 3 columns to compare the outcomes.

4. The 'Trader Successful' column is a simple OR function. The reason is that we have handled the problem conditions partially in the 'Successive in-the-money' and 'Successive 'at-the-money' columns. Now all we have to do is mark the trader successful if there is at least 1 'yes' in the column range: G - M.

5. We want to have 100 simulations that is why we just copy the row over to 100 rows and hence get 100 simulations.

6. This is the fun part where we start getting results. Notice the 'Analytics' section on the right side of the page. In this image all we are doing is getting the mean of the outcomes. This 'mean' is the number of successful traders divided by the total number of traders (i.e. 100 in our example).

7. In this step we just calculated the standard deviation of the successful traders. Both mean (average) and standard deviations are used in the monte-carlo formula.

8. This is where we get the z value. Fortunately excel provides us with a function to get the value as shown in the image. A detail to notice is that we are using the value .995 (or 99.5%) when we are trying to get a 99% confidence interval.

9. Now, we calculate the upper and lower range values. This formula is the gist of monte carlo concept. To get the upper value we use the formula: mean - ( std-dev * z / sqrt(100) ).

10. To get the lower value we use the formula: mean + ( std-dev * z / sqrt(100) ). By this point we have created the confidence interval and we can state our result from the simulations as: 'i can say with 99% confidence that the probability of a successful trader is between .236 and .484'.

The spread of the result

Now, let’s think about extending our results. Let’s say that we want to quantify the precision of our results. The following images show how to get the precision:

Using the results from the above simulations: “i can say with 99% confidence that the probability of a successful trader lies between .341 and .599 with .129 precision”

Number of simulations for desired precision result

Furthermore we can extend this problem. Say, we want to find out the number of simulations that are needed to get a precision of .02. We already have that with a 100 simulations we have a precision of .128, now to improve the precision from .128 to .02 we have increase the number of simulations. This total number of simulations to get a precision of .02 is found using the following formula.

Appendix A - Downloads

• Click here for the complete monte carlo simulation excel file