About random numbers =RAND()

marshal

New Member
Joined
Dec 15, 2005
Messages
2
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.
 

Some videos you may like

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
Joined
Dec 28, 2004
Messages
58
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
Joined
Dec 15, 2005
Messages
2
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
Joined
Dec 28, 2004
Messages
58
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,069
Messages
5,570,014
Members
412,304
Latest member
citrus
Top