Share
Explore BrainMass

Calculate Correlation Coefficient of Stock Returns

The data to be used is included in the sheet named as Data. The data consist of the closing prices for each month for the five-year period from June 1998 through June 2003.

What is correlation coefficient? Compute the correlation coefficient of the (1) two stock returns and (2) the S&P 500 index returns with each stock's returns.
All values are prices, except the one-year Treasury, which is stated as a yield.

Stocks Index Risk-Free
MONTH ATT MICROSOFT S&P500 1-YR TREASURY
Jun-98 190.42 27.09 1133.84 5.41
Jul-98 202.08 27.48 1120.67 5.36
Aug-98 167.08 23.98 957.28 5.21
Sep-98 194.79 27.52 1017.01 4.71
Oct-98 208.33 26.47 1098.67 4.12
Nov-98 207.29 30.50 1163.63 4.53
Dec-98 252.50 34.67 1229.23 4.52
Jan-99 302.50 43.75 1279.64 4.51
Feb-99 273.75 37.53 1238.33 4.70
Mar-99 266.04 44.81 1286.37 4.78
Apr-99 252.50 40.66 1335.18 4.69
May-99 277.50 40.34 1301.84 4.85
Jun-99 279.06 45.09 1372.71 5.10
Jul-99 260.63 42.91 1328.72 5.03
Aug-99 225.00 46.28 1320.41 5.20
Sep-99 217.50 45.28 1282.71 5.25
Oct-99 233.75 46.28 1362.93 5.43
Nov-99 279.38 45.52 1388.91 5.55
Dec-99 254.06 58.38 1469.25 5.84
Jan-00 263.75 48.94 1394.46 6.12
Feb-00 246.88 44.69 1366.42 6.22
Mar-00 281.56 53.13 1498.58 6.22
Apr-00 233.13 34.88 1452.43 6.15
May-00 173.44 31.28 1420.60 6.33
Jun-00 158.13 40.00 1454.60 6.17
Jul-00 154.69 34.91 1430.83 6.08
Aug-00 157.50 34.91 1517.68 6.18
Sep-00 146.88 30.16 1436.51 6.13
Oct-00 115.94 34.44 1429.40 6.01
Nov-00 98.13 28.69 1314.95 6.09
Dec-00 86.25 21.69 1320.28 5.60
Jan-01 119.95 30.53 1366.01 4.81
Feb-01 115.00 29.50 1239.94 4.68
Mar-01 106.50 27.34 1160.33 4.30
Apr-01 111.40 33.88 1249.46 3.98
May-01 105.85 34.59 1255.82 3.78
Jun-01 110.00 36.50 1224.42 3.58
Jul-01 101.05 33.10 1211.23 3.62
Aug-01 95.20 28.53 1133.58 3.47
Sep-01 96.50 25.59 1040.94 2.82
Oct-01 76.25 29.08 1059.78 2.33
Nov-01 87.45 32.11 1139.45 2.18
Dec-01 90.70 33.13 1148.08 2.22
Jan-02 88.50 31.86 1130.20 2.16
Feb-02 77.70 29.17 1106.73 2.23
Mar-02 78.50 30.16 1147.39 2.57
Apr-02 65.60 26.13 1076.92 2.48
May-02 59.85 25.46 1067.14 2.35
Jun-02 53.50 27.35 989.82 2.20
Jul-02 50.90 23.99 911.62 1.96
Aug-02 61.10 24.54 916.07 1.76
Sep-02 60.05 21.87 815.28 1.72
Oct-02 65.20 26.74 885.77 1.65
Nov-02 28.04 28.84 936.31 1.49
Dec-03 26.11 25.85 879.82 1.45
Jan-03 19.48 23.73 855.70 1.36
Feb-03 18.54 23.70 841.15 1.30
Mar-03 16.20 24.21 848.18 1.24
Apr-03 17.05 25.56 916.92 1.27
May-03 19.49 24.61 963.59 1.18
Jun-03 19.25 25.64 974.50 1.01

Attachments

Solution Summary

This solution provides a detailed explanation of the given accounting problem.

$2.19