Calculate the followings statistics for stock return, market index return, and T-bills return during period 1927-2016 by using the Excel functions.

1. Average return

2. Variance

3. Standard deviation

Part B: Calculate the following statistics for stock and market returns each subperiod (1 to 4) and T-bills average return for each period by using the Excel functions.

1. For 1927-2016 period, average return, standard deviation

2. For 1927-1956 period, average return, standard deviation

3. For 1957-1987 period, average return, standard deviation

4. For 1988-2016 period, average return, standard deviation

5. Calculate the T-bills average returns for each period

Part C: Calculate the correlation between stock returns and market returns and then calculate the Beta for each period.

1. For 1927-2016 period, correlation , and Beta

2. For 1927-1956 period, correlation, and Beta

3. For 1957-1987 period, correlation, and Beta

4. For 1988-2016 period, correlation, and Beta

Part D: Calculate the expected rate of return from CAPM for each period by using the followings

1. Market average return for each period that you calculated in Part B

2. T-bills average return for each period ( proxy for the risk free rate) that you calculated in Part B

3. Beta that you calculated for each period in Part C.

Part E: 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 changessignificantly from period to period? Why?

Part F: Summary

Summarize what you learned from the history based on your analysis?

  
 
 
 

Transcribed Image Text:

YEAR 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 MKT INDEX 30.85 34.88 -17.9 -30.77 -44.8 -9.45 56.08 4.42 44.35 32.44 -34.83 27.95 2.95 -6.93 -10.14 15.75 28.23 21.12 37.72 -6.08 3.22 1.33 19.23 28.26 19.09 11.89 -1.15 49.28 23.76 5.83 -13.27 43.46 T-BILLS 3.12 3.56 4.75 2.41 1.07 0.96 0.30 0.16 0.17 0.18 0.31 -0.02 0.02 0.00 0.06 0.27 0.35 0.33 0.33 0.35 0.50 0.81 1.10 1.20 1.49 1.66 1.82 0.86 1.57 2.46 3.14 1.54 STOCK 33.97 38.44 -13.15 -28.36 -43.73 -8.49 56.38 4.58 44.52 32.62 -34.52 27.93 2.97 -6.93 -10.08 16.02 28.58 21.45 38.05 -5.73 3.72 2.14 20.33 29.46 20.58 13.55 0.67 50.14 25.33 8.29 -10.13 45.00 YEAR 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 MKT INDEX 30.85 34.88 -17.9 -30.77 -44.8 -9.45 56.08 4.42 44.35 32.44 -34.83 27.95 2.95 -6.93 -10.14 15.75 28.23 21.12 37.72 -6.08 3.22 1.33 19.23 28.26 19.09 11.89 -1.15 49.28 23.76 5.83 -13.27 43.46 T-BILLS 3.12 3.56 4.75 2.41 1.07 0.96 0.30 0.16 0.17 0.18 0.31 -0.02 0.02 0.00 0.06 0.27 0.35 0.33 0.33 0.35 0.50 0.81 1.10 1.20 1.49 1.66 1.82 0.86 1.57 2.46 3.14 1.54 STOCK 33.97 38.44 -13.15 -28.36 -43.73 -8.49 56.38 4.58 44.52 32.62 -34.52 27.93 2.97 -6.93 -10.08 16.02 28.58 21.45 38.05 -5.73 3.72 2.14 20.33 29.46 20.58 13.55 0.67 50.14 25.33 8.29 -10.13 45.00

Answer rating: 100% (QA)

To calculate the statistics requested you can use the following Excel functions Part A 1 Average Return AVERAGE function 2 Variance VARS function 3 St... View the full answer