Monte Carlo Simulation for Options

After most of a day, I will venture a response to this. I will say that I am not an expert, and am not sure I fully understand the procedure. I can "translate" each statement into basic English, but I don't fully understand what the algorithm is doing.

what is the purpose of the variable A and P

It appears to me that a and p are mostly being used as variant (integer) variables and are mostly used in different combinations as array indices for the SimVar() array. I have not tried to understand exactly how the algorithm works and why they are combined in those ways to get the array indices.

'm also not really sure about this: If (j - 1) / 250 - lnt((j - 1) / 250) = 0 And j > 1 Then p = p + 1

Is there something specific about this If...Then statement you don't understand? It is a fairly basic If...Then statement. There is a boolean expression (expression=0 and j>1) if the combined boolean expression is True (when both expressions on either side of the And operator are True), then it will add 1 to p. If either expression is false, then it will leave p unchanged. As before, I do not understand how this particular statement fits into the overall algorithm.

Is that enough, or are you trying to get deeper into the algorithm? If I were to dig deeper into the algorithm, I would probably put the code into a module, put a Stop statement at the top of the procedure, and execute. VBA will stop at the Stop statement and enter debug mode. Then I can step through the code and follow the execution to see what it is doing to those variables with each statement. By stepping through the code, I would expect to come to better understand what the procedure is doing. If you are unfamiliar with VBA's debugging tools: http://www.cpearson.com/Excel/DebuggingVBA.aspx

Difficult to debug on this side of the internet. If I copy the code into a VBA module and attempt to call the function (from the spreadsheet), I get a compile error that the function lnt(...) is not defined (see If..then statement three statements after the Randomize statement). I guessed that this should be an Int() function, and then the function executed just fine. At this point, I have no idea what suitable values for all of the inputs would be, so I get a 0 result. But the function executes just fine without error.

I do not understand why you cannot enter debug mode when calling it as a function procedure. Did you add the Stop statement as I suggested? How are you calling the function? Are you calling it from another VBA procedure, or calling it from a spreadsheet cell?

No value or 0 value? a and p are both variants, and there is a difference, however slight, between an empty variant and a variant/integer that is 0.

Scanning the code for places where a and p are changed, and I see:

1) a is set to 0 at the start of the procedure.
2) p is set to 0 at the top of the for i...next i loop
3) At the top of the for j...Next j loop, p may increase by 1 depending on the result of the condition in the If..Then statement. Is the boolean test here ever True?
4) At the bottom of the For i...Next i loop, a and p are combined and stored in a. a starts as 0 and p starts as 0. If p is never changed by the If..Then statement, then p will still be 0 and a+p is 0 and a is still 0.

I would probably focus my debugging on that If..Then statement that is supposed to change p.

When your udf returns #Value to the spreadsheet, that means that it encountered an untrapped error. Since your code contains no error trapping, there are many possible errors that could cause the code to return #Value. Since a is mostly used to locate elements in the SimVar() array, I suspect that a=1 is causing a "subscript out of range" type error -- say when i=nlter.

I agree that, if a and p will always be 0, then they almost seem unnecessary. Which brings me back to the If..Then statement -- when can p increment? The first condition (j-1)/250-Int((j-1)/250)=0 will only be true when the loop counter variable j=1, 251, 501, 751.... The other condition specifically blocks the j=1 condition from incrementing p, so it appears that the author of the code was trying to do something different with the SimVar() array at every 250 iterations. I don't yet understand what. As I said earlier, I have no idea what typical values you are passing to the UDF. It appears that a and p are indeed unnecessary unless n will ever be greater than 250. What value are you using for n? What are the possible values for n?

You might try this. add

Please Login or Register  to view this content.

With that change, whenever the function encounters an error, it will find the "timeout" label and stop at that stop statement and enter debug mode. FWIW, I tested with some random inputs (making sure that n was greater than 250). When it hit an error and stopped, I could see that a was 14, p was 1, and i was 15, and SimVar()'s first dimension went from 0 to 28. With those values for a, p, and i, it would have been trying to access element 29 or 30, which did not exist. I don't understand what the author was trying to do with a and p, but it appears to have the strong possibility of triggering subscript out of range errors.

At this point, I would continue to spend some time with a and p and nlter and n and see if I could figure out what a and p are trying to do.

That explains n, and that suggests that p is somehow trying to keep track of trading years? What is A doing? A seems to keep a running tally of p (number of years), but I don't see what the purpose is? What is nlter (other than the upper limit of the For i...Next i loop)?

When I run the code, before it errors, SimVar() is a very large array that is mostly empty. It seems like the goal was to have a 2D array where one dimentions would have something to do with "trading year" and the other dimension would have something to do with each trading day. I'm not sure the author of the code quite succeeded in this, but I am still not sure exactly what he/she was trying to do.

Can you explain in words/math statements (without getting into too much detail) what this procedure is trying to do? I can see:

1) A random number generated (it looks like you are wanting a normally distributed random number rather than a uniformly distributed random number).
2) You compute a dlns value from that number
3) Then you choose a location (using a and p) inside of the 2D SimVar() array to store a result based on that value.
4) Repeat n times (n is number of trading days and it appears that a and p will keep track of trading years based on the number of trading days).
5) Compute PayVar() values based on the SimVar() results.
6) final price is a summation of the PayVar() values.

That is a simple overview of the procedure as I see it. I don't understand how the 2D SimVar() array was intended to store the results of each daily computation. Something about the current code structure seems to make the procedure susceptible to subscript our of range type errors. I am thinking that we need a better understanding of how the author intended to use SimVar() to store and retrieve results.

A quick internet search tells me that a Wiener process is like a random walk or Brownian motion type simulation, so that helps me understand what is going on inside of the loop.

I modified the code to this:

Please Login or Register  to view this content.

I called the UDF from the spreadsheet with the following inputs:
C is "C"
S is a random double from 0 to 1000
x is a random double from 0 to 1
T is ROUNDUP(n/250,0) (so T will actually be an integer that never includes a fractional portion)
z is a random double from 0 to 1
r is a random double from 0 to 0.1
q is a random double from 0 to 1
n is a random double from 200 to 700 (seems like it maybe should be an integer because we are not really considering fractional trading days).
nlter =1 because I feel like I need to get one random walk to work correctly before I worry about getting 1000 random walks to work correctly.

As noted previously, it seems that a lot of the problem is when there is more than 1 year (when n is greater than 250), and several runs through seem to confirm. Whenever n<250, I get an error free run and some kind of price output (I did not check to see if it was the correct price). Whenever n>250, I get an error 9 subscript out of range. I'm still not sure how the author of this code was intending to keep track of trading years inside of SimVar(), but it seems that something is not quite correct. The way SimVar() is dimensioned, it looks like he/she was intending each "row" (first dimension) would represent a single random walk, and each "column" (second dimension) would represent a trading day. However, inside of the loop, it is almost like the programmer was thinking that each "row" represents a trading year and each "column" represents a trading day. I don't know how he/she intended to represent a random walk (maybe thinking there should be a third dimension to SimVar()??). That seems to me to be where the problem lies. How are you wanting to use SimVar()?

First of all many thanks for your support. That helps me a lot, not only for this issue also especially to understand how to find mistakes/bugs in general. (As I mentioned before, I'm a total greenhorn in coding)

Just for understanding C/P in the function stands for Call (betting on increasing stock prices) or Put (betting on decreasing stock prices)
So X is the Strike Price when I have a Call my stock price must be bigger then the strike price to make money and vise versa for the put.

Please Login or Register  to view this content.

You can see how the X switch from one site to the other. When you have a Call, X is usually bigger than S. So it is a relatively huge number.

r the interest rate can have any number but on average the number is not bigger than 3% p.a..

q is the dividend of the stock which is also a percentage number

What do you exactly mean with how I want to use SimVar()?
The only think which is important is that the result is not less than zero. The only number we need is the developed stock price on the last day. And then we subtract the Strike price (X) with our developed Stock price (necessary that it is not less than zero) and discount it to the current date. So, the price of the Option is the discounted difference of the expected stock price and the strike price.

What do you exactly mean with how I want to use SimVar()?

As I tried to explain, it seems that the errors are "subscript out of range" errors -- meaning that our code is trying to access elements/dimensions of the array that do not exist (are outside of the dimensioned bounds). Solutions to these errors are to figure out how many elements the array should hold in how many dimensions and to get a good idea in our head of what each dimension "means." For example:

1) As dimensioned [ReDim SimVar(nlter,n+1)], it looks like the author was expecting a 2D array where the first dimension ("rows" if it helps) represents each "random walk" and the second dimension ("columns" if it helps) represent each day/"step" along the random walk.

2) It seems that the loop is trying to use SimVar() as a 2D array where dimension 1 represents each year and dimension 2 represents each day of that year. Under this structure, the results of a single simulation/random walk are discarded at the end/overwritten by the next simulation/random walk. Or maybe the author was planning to use a 3D array where there's a dimension for simulations/walks, one for years, and one for trading days.

3) In your latest reponse, you say that the only important result is the last day's result -- suggesting that you don't need a permanent record of the entire path of each random walk. Maybe a 1D array would be more appropriate for this -- storing only the final result of each random walk.

Which of those array structures do you like? Or do you have a different array structure in mind? Once you have chosen an array structure to use for SimVar(), then we can work on editing the code to consistently reflect that array structure.

Time to make some decisions, then. A few additional considerations:

1) SimVar() will have a scope and lifespan. Called as a UDF from the spreadsheet like I did it means that SimVar() will be "forgotten" as soon as the procedure finishes and sends its result back to the spreadsheet. If you intend to call the procedure from other VBA procedures, SimVar()'s scope is only within this procedure. How are you intending to use this procedure -- both now and in the future? If you will need every step at some later time, how will you end up using that information? Some scenarios I imagine make me wonder if it would be easier to just use spreadsheet formulas and abandon VBA as the programming language for this?

2) If you decide to store every step of each random walk, what is the point to keeping track of trading year? The basic sim formula looks the same to me (today's result = yesterday's result * exp(dlns)). Does the daily formula change for each year? How does it change? What is the necessity of keeping track of trading year during the random walk?

3) The easiest to me seems to be to only store the final result. It may need to be changed in the future in order to store/output every single trading day, but maybe cross that bridge when you get to it. For this the procedure might look like (pseudocode):

Please Login or Register  to view this content.

How do you want to proceed?

Glad you got it to work out.