Monte Carlo simulation in excel excel

MythosHF

Hi guys,

I have a practical problem for you.

There are 255 daily qoutations of a stock, and the distribution of the qoutes changes is normal.
I want to construct a Monte Carlo simulation of the stock qoutes changes using rand() function in excel generating 5000 records using the exact function min+rand()*(max-min).
The random numbers are in the range 0-1, so my expectation is that the generated 5000 otcomes are normally distributed as the input (the initial qoutes) but in fact it is not.
Am I wrong in steps taken or my expectation is corrupted?

An on the real topic:
Level I - too many pages, nothing difficult
Level II - started "THE BIG BOOKS" yesterday. Ethics the same as level I by now.

Thanks for answers in advance.

willsucceed

What are "min" and "max"?

Houjichasan

ohai Wrote:
-------------------------------------------------------
> What are "min" and "max"?


The minimum and maximum value of the variable.

3%
2%
-1%
5%
-3%

Max=5%
Min = -3%

Range=8%

Formula:
-0.03+rand()*(0.05-(-0.03)) = output 1
-0.03+rand()*(0.05-(-0.03)) = output 2
-0.03+rand()*(0.05-(-0.03)) = output 3

...4997 times...

chandsingh

You're generating uniform random variables between -3 and 5%. If you want normal random variables, you won't be able to use that exact function.

What is the variance of this normal curve (which will be necessary to generate normal rvs)? You could relate it to the min/max values by saying, perhaps, the minimum observed value (here -3%) is the 5th percentile, and the maximum observed movement (5%) is the 95% percentile, or whatever percentiles you want. The mean is 1%. Let me know more and I can help you generate normal rvs, unless I have completely misunderstood what you're asking for.

AndyNZ

chaddy is right, you're generating uniform random numbers this way.

This is what I would do:
-Define the mean (probably 1% here)
-Define the standard deviation
-Generate normally distributed random numbers using the RAND and NORMINV function

therecruit

Just to help you out a little more. I would pick a standard dev of around 2%, with a mean of 1.2%. Recall that 67% of results are within 1 std dev of mean, 96-ish% are within 2. From your limited results above, I think you want 3% and -1% within the first deviation band, and probably <5% outside of 4% from mean.

Your excel formula is then: =NORMINV(rand(),0.012,0.02) for your simulation.

WarrenB1

What are you actually trying to achieve Bravo? Are you trying to generate a random walk or trying to simulate random changes to the bid-ask spread?

If you're trying to generate a random walk, then there are lots of examples of how to generate a simple random walk in excel or matlab on the web.