Question

6. Stock price simulation: A stock’s price is lognormally distributed with mean μ = 15%. The current stock price is S0 = 35. Following the template on the spreadsheet, create 60 static standard normal deviates using Data|Data Analysis|Random Number Generation. Use these random numbers to simulate the stock price path over 60 months. Create price paths for σ = 15%, 30%, and 60% and graph these three paths on the same axes.

Answer 1

So = 35

S1 (stock price at t = 1 month) = So * exp(m*t + s*(t^0.5)*normsinv((rand))

where m mean return = 15%

s: standard deviation = 15%/30%/60%

normsinv(rand) is the function of excel to generate random number with mean = 0 & st dev = 1

t is time step = 1month = (1/12) year =  0.0833 year

So	35
mean	15%
st dev	15%	30%	60%
t	0.083333333
Time	Stock price
	St dev 15%	St dev 30%	St dev 60%
0	35	35	35
1	34.05871315	38.235964	25.114935
2	33.12472905	33.2556	26.182407
3	31.70016938	35.499846	29.587652
4	32.48240613	39.680284	33.73532
5	31.85401552	42.227174	39.631523
6	31.73831071	42.011274	31.685845
7	31.76151016	40.118972	28.957209
8	33.25013908	37.99756	39.521421
9	34.36656679	39.804365	41.009884
10	32.52716959	39.120481	39.505082
11	31.74623971	42.367612	42.059135
12	29.41892653	49.273718	44.086278
13	30.4144073	47.509566	59.921701
14	30.50269625	44.822616	49.245574
15	32.92601963	41.970747	64.711836
16	34.32203046	43.200686	56.882936
17	32.64634491	49.572277	66.496539
18	29.95534159	45.244158	44.302638
19	30.82355398	48.005018	58.528636
20	30.91379833	49.148773	57.048767
21	29.51843266	44.603775	63.819395
22	28.08424133	41.78027	69.632984
23	28.63646711	38.673414	79.854193
24	28.55438899	38.076871	72.182536
25	26.96757013	35.293359	75.147966
26	29.62092457	35.028011	74.057808
27	29.07722651	34.854926	60.493612
28	30.82047787	38.330172	66.991345
29	33.96572629	41.852521	77.652873
30	34.22596317	43.821564	93.811274
31	34.55680557	48.31805	98.233101
32	35.08827239	55.054239	97.218152
33	34.69492721	70.07988	89.349185
34	33.83736747	70.127023	84.242177
35	34.0686382	88.018638	101.18712
36	34.45633748	79.982748	109.03706
37	36.41739095	89.922925	77.546074
38	36.09818458	82.446882	104.57024
39	34.72308499	94.455608	102.79117
40	36.57246273	93.590664	94.574887
41	38.96880155	88.742372	108.10375
42	38.63051185	89.318953	160.86283
43	34.53948587	91.495112	166.5158
44	36.16013688	93.283284	152.25234
45	35.70822842	94.241807	157.32944
46	38.2630797	99.079416	209.25571
47	38.02713047	109.77444	215.03793
48	35.1322744	110.3505	232.46741
49	35.91633065	116.14431	145.21724
50	37.15539495	107.04139	149.31158
51	36.28210946	120.62454	144.3996
52	33.6852591	120.01039	139.11702
53	34.86310436	122.72142	149.70213
54	36.11235101	129.64918	135.40792
55	35.16913502	148.3704	111.96552
56	37.62799605	141.92656	101.23847
57	38.14273595	148.17864	110.66695
58	37.58311739	174.08932	174.4183
59	37.7673879	173.4685	124.48869
60	37.52643223	164.42029	149.11894