|
Stock
Returns |
|
|
|
|
|
|
|
|
|
|
|
|
| ROW/COL |
B |
C |
D |
E |
F |
G |
H |
I |
|
| 3 |
|
| 4 |
As you know many things
in this world are normally distributed. This is very convenient for
statistical analysis. |
|
| 5 |
|
| 6 |
Unfortunately stock
returns are not normally distributed. You can described the reason in a
variety of ways but the basic reason is that stock prices cannot be negative. |
| 7 |
|
| 8 |
However,
there is a fair amount of evidence that stock returns are approximately
normally distributed if you take the log (natural) of the return. |
|
| 9 |
To do this is fairly
simple. |
|
| 10 |
|
| 11 |
|
|
|
| 12 |
Below is the
data for IBM from Feb, 1998 to May, 2003 |
|
|
|
| 13 |
The
blue numbers are the ln returns which are in general the ones you want to use
for analysis such as computing Beta's and it is also the data needed to
implement the Option Pricing models. |
|
|
| 14 |
Date |
Adjusted Close |
Cash Dividend |
1+
Return = (Pt+Dt)/Pt-1 |
ln(1+Return) |
|
|
|
| 15 |
|
|
|
|
=(+C17+D17)/C16 |
|
| 16 |
Feb-98 |
51.81 |
|
|
|
|
|
|
| 17 |
Mar-98 |
51.53 |
|
0.99460 |
-0.542% |
<=LN(E17) |
|
| 18 |
Apr-98 |
57.48 |
|
1.11547 |
10.927% |
|
| 19 |
May-98 |
58.29 |
|
1.01409 |
1.399% |
|
| 20 |
Jun-98 |
56.96 |
|
0.97718 |
-2.308% |
|
|
|
|
| 21 |
Jul-98 |
65.73 |
|
1.15397 |
14.321% |
|
| 22 |
Aug-98 |
55.87 |
|
0.84999 |
-16.253% |
|
| 23 |
Sep-98 |
63.75 |
|
1.14104 |
13.194% |
|
| 24 |
Oct-98 |
73.67 |
|
1.15561 |
14.463% |
|
| 25 |
Nov-98 |
81.92 |
|
1.11199 |
10.615% |
|
| 26 |
Dec-98 |
91.47 |
|
1.11658 |
11.027% |
|
| 27 |
Jan-99 |
90.91 |
|
0.99388 |
-0.614% |
|
| 28 |
Feb-99 |
84.21 |
|
0.92630 |
-7.656% |
|
| 29 |
Mar-99 |
87.93 |
|
1.04418 |
4.323% |
|
| 30 |
Apr-99 |
103.78 |
|
1.18026 |
16.573% |
|
| 31 |
May-99 |
115.09 |
|
1.10898 |
10.344% |
|
| 32 |
Jun-99 |
128.24 |
|
1.11426 |
10.819% |
|
| 33 |
Jul-99 |
124.71 |
|
0.97247 |
-2.791% |
|
| 34 |
Aug-99 |
123.59 |
|
0.99102 |
-0.902% |
|
| 35 |
Sep-99 |
120.06 |
|
0.97144 |
-2.898% |
|
| 36 |
Oct-99 |
97.48 |
|
0.81193 |
-20.834% |
|
| 37 |
Nov-99 |
102.26 |
|
1.04904 |
4.787% |
|
| 38 |
Dec-99 |
107.03 |
|
1.04665 |
4.559% |
|
| 39 |
Jan-00 |
111.37 |
|
1.04055 |
3.975% |
|
| 40 |
Feb-00 |
101.95 |
|
0.91542 |
-8.838% |
|
| 41 |
Mar-00 |
117.45 |
|
1.15204 |
14.153% |
|
| 42 |
Apr-00 |
110.63 |
|
0.94193 |
-5.982% |
|
| 43 |
May-00 |
106.47 |
|
0.96240 |
-3.833% |
|
| 44 |
Jun-00 |
108.71 |
|
1.02104 |
2.082% |
|
| 45 |
Jul-00 |
111.37 |
|
1.02447 |
2.417% |
|
| 46 |
Aug-00 |
130.99 |
|
1.17617 |
16.226% |
|
| 47 |
Sep-00 |
111.74 |
|
0.85304 |
-15.895% |
|
|
|
| 48 |
Oct-00 |
97.73 |
|
0.87462 |
-13.397% |
|
|
|
| 49 |
Nov-00 |
92.77 |
|
0.94925 |
-5.209% |
|
|
|
| 50 |
Dec-00 |
84.34 |
|
0.90913 |
-9.527% |
|
|
|
| 51 |
Jan-01 |
111.13 |
|
1.31764 |
27.584% |
|
|
|
|
| 52 |
Feb-01 |
99.12 |
|
0.89193 |
-11.437% |
|
| 53 |
Mar-01 |
95.43 |
|
0.96277 |
-3.794% |
|
| 54 |
Apr-01 |
114.24 |
|
1.19711 |
17.991% |
|
| 55 |
May-01 |
110.93 |
|
0.97103 |
-2.940% |
|
| 56 |
Jun-01 |
112.61 |
|
1.01514 |
1.503% |
|
| 57 |
Jul-01 |
104.39 |
|
0.92700 |
-7.580% |
|
| 58 |
Aug-01 |
99.17 |
|
0.95000 |
-5.130% |
|
| 59 |
Sep-01 |
91 |
|
0.91762 |
-8.598% |
|
| 60 |
Oct-01 |
107.23 |
|
1.17835 |
16.412% |
|
| 61 |
Nov-01 |
114.69 |
|
1.06957 |
6.726% |
|
| 62 |
Dec-01 |
120.02 |
|
1.04647 |
4.543% |
|
| 63 |
Jan-02 |
107.05 |
|
0.89193 |
-11.436% |
|
| 64 |
Feb-02 |
97.36 |
|
0.90948 |
-9.488% |
|
| 65 |
Mar-02 |
103.19 |
|
1.05988 |
5.816% |
|
| 66 |
Apr-02 |
83.11 |
|
0.80541 |
-21.641% |
|
| 67 |
May-02 |
79.82 |
|
0.96041 |
-4.039% |
|
| 68 |
Jun-02 |
71.44 |
|
0.89501 |
-11.092% |
|
| 69 |
Jul-02 |
69.85 |
|
0.97774 |
-2.251% |
|
| 70 |
Aug-02 |
74.96 |
0.15 |
1.07530 |
7.260% |
|
| 71 |
Sep-02 |
57.98 |
|
0.77348 |
-25.686% |
|
| 72 |
Oct-02 |
78.5 |
|
1.35392 |
30.300% |
|
| 73 |
Nov-02 |
86.59 |
0.15 |
1.10497 |
9.982% |
|
| 74 |
Dec-02 |
77.21 |
|
0.89167 |
-11.466% |
|
| 75 |
Jan-03 |
77.91 |
|
1.00907 |
0.903% |
|
| 76 |
Feb-03 |
77.81 |
0.15 |
1.00064 |
0.064% |
|
| 77 |
Mar-03 |
78.29 |
|
1.00617 |
0.615% |
|
| 78 |
Apr-03 |
84.74 |
|
1.08239 |
7.917% |
|
| 79 |
May-03 |
86.18 |
|
1.01699 |
1.685% |
|
| 80 |
|
|
|
| 81 |
|
Monthly Average
Return = |
0.817% |
<=AVERAGE(F17:F79) |
|
| 82 |
|
Monthly
Standard Deviation = |
11.317% |
<=STDEV(F17:F79) |
|
| 83 |
|
|
|
|
| 84 |
|
Annual Average
Return = |
9.800% |
<=(F81)*12 |
|
| 85 |
|
Annual
Standard Deviation = |
39.203% |
<=F82*(12)^0.5 |
|
| 86 |
|
| 87 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|