#### marshal

##### New Member
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.

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### Ceduljko

##### Board Regular
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

##### New Member
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

##### Board Regular
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.

Replies
11
Views
349
Replies
3
Views
103
Replies
2
Views
231
Replies
4
Views
304
Replies
1
Views
187