You will find historical data (rates of return) for major assets classes from 1927-2016. (Stock, Market Index, and T-bills) in the Excel Spreadsheet that provided to you.
You need to analysis the following steps:
Part A: Calculate the followings statistics for stock, market index, and T-bills during period 1927-2016 using the Excel functions.
1.Average return
2.Variance
3.Standard deviation
Part B: Calculate the following statistics for each subperiod using the Excel functions.
1.For 1927-2016 period,average return, standard deviation, correlation , and Beta
2.For 1927-1956 period,average return, standard deviation, correlation, and Beta
3.For 1957-1987 period,average return, standard deviation, correlation,and Beta
4.For 1988-2016 period,average return, standard deviation, correlation, and Beta
Part C: Calculate the expected rate of return from CAPM for each period by using the Betas that you calculate in Part B.
Part D:Based on your calculation in Part A, B, and C, answer the following questions.
1.How stable Beta for each subperiod? Explain?
2.If it does, why expected rates of return from CAPM changes significantly from period to period? Why?
Part E: Summary
Summarize what you learned from the history based on your analysis?
historical data YEARMKT INDEXT-BILLSSTOCK 192730.853.1233.97 192834.883.5638.44 1929-17.94.75-13.15 1930-30.772.41-28.36 1931-44.81.07-43.73 1932-9.450.96-8.49 193356.080.3056.38 19344.420.164.58 193544.350.1744.52 193632.440.1832.62 1937-34.830.31-34.52 193827.95-0.0227.93 19392.950.022.97 1940-6.930.00-6.93 1941-10.140.06-10.08 194215.750.2716.02 194328.230.3528.58 194421.120.3321.45 194537.720.3338.05 1946-6.080.35-5.73 19473.220.503.72 19481.330.812.14 194919.231.1020.33 195028.261.2029.46 195119.091.4920.58 195211.891.6613.55 1953-1.151.820.67 195449.280.8650.14 195523.761.5725.33 19565.832.468.29 1957-13.273.14-10.13 195843.461.5445.00 19599.792.9512.74 1960-1.732.660.93 196124.882.1327.01 1962-12.282.73-9.55 196317.923.1221.04 196412.663.5416.20 196510.583.9314.51 1966-13.544.76-8.78 1967244.2128.21 19689.025.2114.23 1969-17.26.58-10.62 1970-5.656.520.87 197111.654.3916.04 197212.973.8416.81 1973-24.696.93-17.76 1974-35.78.00-27.70 197532.355.8038.15 197621.95.0826.98 1977-8.195.12-3.07 19781.047.188.22 197913.3310.3823.71 198022.111.2433.34 1981-18.0514.71-3.34 198210.7810.5421.32 198314.418.8023.21 1984-4.769.855.09 198524.637.7232.35 198610.416.1616.57 1987-3.515.471.96 198811.556.3517.90 198920.518.3728.88 1990-13.847.81-6.03 199129.15.6034.70 19926.413.519.92 19938.372.9011.27 1994-4.113.90-0.21 199531.045.6036.64 199616.255.2121.46 199726.075.2631.33 199819.414.8624.27 199920.214.6824.89 2000-16.715.89-10.82 2001-14.783.78-11.00 2002-22.911.63-21.28 200330.741.0231.76 200410.691.2011.89 20053.212.966.17 200610.584.7915.37 20070.834.675.50 2008-38.391.47-36.92 200929.050.1029.15 201017.680.1217.80 20110.970.041.01 201216.010.0616.07 201335.150.0335.18 201411.350.0211.37 2015-0.20.01-0.19 201613.220.1913.41