Question

All answers were generated using 1,000 trials and native Excel functionality.) Suppose that the price of a share of a particular stock listed on the New York Stock Exchange is currently $39. The following probability distribution shows how the price per share is expected to change over a three-month period: Stock Price Change ($) Probability –2 0.05 –2 0.10 0 0.25 +1 0.20 +2 0.20 +3 0.10 +4 0.10 (a) Construct a spreadsheet simulation model that computes the value of the stock price in 3 months, 6 months, 9 months, and 12 months under the assumption that the change in stock price over any three-month period is independent of the change in stock price over any other three-month period. For a current price of $39 per share, what is the average stock price per share 12 months from now? What is the standard deviation of the stock price 12 months from now? Based on the model assumptions, what are the lowest and highest possible prices for this stock in 12 months?

Answer 1

We calculate the cumulative probability and the intervals of random numbers as below

			Random number intervals
Stock Price Change ($)	Probability	Cumulative probability	From	Less than
-2	0.05	0.05	0	0.05
-1	0.1	0.15	0.05	0.15
0	0.25	0.4	0.15	0.4
1	0.2	0.6	0.4	0.6
2	0.2	0.8	0.6	0.8
3	0.1	0.9	0.8	0.9
4	0.1	1	0.9	1

To simulate the price change for any 3 month period we do the following

• generate a uniform random number in the interval (0,1) using =RAND()
• Check the interval in which this random number lies and get the corresponding price change.
  • For example if the random number generated is 0.6312, it lies in the interval 0.6 to 0.8 which corresponds to a price change of +2.

We set up the following

Copy the rows to get 1000 trails

paste the random numbers as values to avoid changes

get these

The the average stock price and per share 12 months from now, the standard deviation of the stock price 12 months from now and the highest and lowest possible prices are calculated as below

get these

The the average stock price and per share 12 months from now is $43.36, The standard deviation of the stock price 12 months from now is 3.1956

The lowest and highest prices possible as per this simulation is $35 and $54 respectively

But in reality the stock can lose a maximum of $2 in any given 3-month period. If the stock losses $2 in each of the 4, 3-month periods, then it can lose a maximum of $8. That means the lowest possible price of this stock in 12 months is $39-8=$31

Similarly the stock can gain a maximum of $4 in any 3-month period. If the stock gains $4 in each of the 4, 3-month periods, then it can gain a maximum of $16. That means the highest possible price of this stock in 12 months is $39+16=$55