About random numbers =RAND()

[marshal]

Hello, I'm a new comer. I got a problem estimating values of stock prices. That is, is there anyone who knows how to generate the same random numbers when doing a stock price simulation?

For example, when i want to calculate how the value of stock will change in year 3 when initial stock price is $100, we can do a simulation by applying the following formula (including stochastic process)

S = current stock price
R = annual return of stock
Sigma = annual volatility of stock
Random samples (ei) = NORMSINV(RAND())
dt = 1

So, the stock price in year 1 is

S1 = S*EXP[(R-0.5*Sigma^2)dt+SQRT(dt)*Sigma*(e1)]

and the stock price in year 2 is

S2 = S1*EXP[(R-0.5*Sigma^2)dt+SQRT(dt)*Sigma*(e2)]

..............

say if by doing these steps, we get

S S1 S2 S3
100 115 124 118

Now, how about changing S=101 from 100? and how the value of S3 will be?

The value could be quite different due to Random samples if doing another simulation.

So what i want to know is

is there any way to generate the same random samples as describe in this example.

If (e1)=0.3, (e2)=1.5, (e3)=-0.06, then

I want to use these figures when doing the second simulation. So i think there should be some way to generate the same random numbers but i'm quite out of this field so i hope if there's anyone who can let me know how to write the code of generating the same random numbers or is there any other way to do the same kind of simulation.

Thanks.

 

[Ceduljko]

Use RND(<Alwas The Same Negative Number>).

When RND is called with negative argument, it's used as a seed. Sequence that follows will always be identical. That's why it's pseudo-random, anyway...

 

[marshal]

Thanx a lot. though i still have no idea how to generate the same random numbers, i'll be trying to figure it out.

 

[Ceduljko]

Just use the following line before you start using random numbers:

Debug.print RND(-123)

If you generate 100 random numbers after that line, all of them are going to be the same 100 each time you run the program.