Question
Calculate the followings statistics for stock return, market index return, and T-bills return during period 1927-2016 by using the Excel functions. (could you please provide
Calculate the followings statistics for stock return, market index return, and T-bills return during period 1927-2016 by using the Excel functions.
(could you please provide the excel formulas use to solve the problem? 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?
| RATE OF RETURNS(%) | |||
| YEAR | MKT INDEX | T-BILLS | STOCK |
| 1927 | 30.85 | 3.12 | 33.97 |
| 1928 | 34.88 | 3.56 | 38.44 |
| 1929 | -17.9 | 4.75 | -13.15 |
| 1930 | -30.77 | 2.41 | -28.36 |
| 1931 | -44.8 | 1.07 | -43.73 |
| 1932 | -9.45 | 0.96 | -8.49 |
| 1933 | 56.08 | 0.30 | 56.38 |
| 1934 | 4.42 | 0.16 | 4.58 |
| 1935 | 44.35 | 0.17 | 44.52 |
| 1936 | 32.44 | 0.18 | 32.62 |
| 1937 | -34.83 | 0.31 | -34.52 |
| 1938 | 27.95 | -0.02 | 27.93 |
| 1939 | 2.95 | 0.02 | 2.97 |
| 1940 | -6.93 | 0.00 | -6.93 |
| 1941 | -10.14 | 0.06 | -10.08 |
| 1942 | 15.75 | 0.27 | 16.02 |
| 1943 | 28.23 | 0.35 | 28.58 |
| 1944 | 21.12 | 0.33 | 21.45 |
| 1945 | 37.72 | 0.33 | 38.05 |
| 1946 | -6.08 | 0.35 | -5.73 |
| 1947 | 3.22 | 0.50 | 3.72 |
| 1948 | 1.33 | 0.81 | 2.14 |
| 1949 | 19.23 | 1.10 | 20.33 |
| 1950 | 28.26 | 1.20 | 29.46 |
| 1951 | 19.09 | 1.49 | 20.58 |
| 1952 | 11.89 | 1.66 | 13.55 |
| 1953 | -1.15 | 1.82 | 0.67 |
| 1954 | 49.28 | 0.86 | 50.14 |
| 1955 | 23.76 | 1.57 | 25.33 |
| 1956 | 5.83 | 2.46 | 8.29 |
| 1957 | -13.27 | 3.14 | -10.13 |
| 1958 | 43.46 | 1.54 | 45.00 |
| 1959 | 9.79 | 2.95 | 12.74 |
| 1960 | -1.73 | 2.66 | 0.93 |
| 1961 | 24.88 | 2.13 | 27.01 |
| 1962 | -12.28 | 2.73 | -9.55 |
| 1963 | 17.92 | 3.12 | 21.04 |
| 1964 | 12.66 | 3.54 | 16.20 |
| 1965 | 10.58 | 3.93 | 14.51 |
| 1966 | -13.54 | 4.76 | -8.78 |
| 1967 | 24 | 4.21 | 28.21 |
| 1968 | 9.02 | 5.21 | 14.23 |
| 1969 | -17.2 | 6.58 | -10.62 |
| 1970 | -5.65 | 6.52 | 0.87 |
| 1971 | 11.65 | 4.39 | 16.04 |
| 1972 | 12.97 | 3.84 | 16.81 |
| 1973 | -24.69 | 6.93 | -17.76 |
| 1974 | -35.7 | 8.00 | -27.70 |
| 1975 | 32.35 | 5.80 | 38.15 |
| 1976 | 21.9 | 5.08 | 26.98 |
| 1977 | -8.19 | 5.12 | -3.07 |
| 1978 | 1.04 | 7.18 | 8.22 |
| 1979 | 13.33 | 10.38 | 23.71 |
| 1980 | 22.1 | 11.24 | 33.34 |
| 1981 | -18.05 | 14.71 | -3.34 |
| 1982 | 10.78 | 10.54 | 21.32 |
| 1983 | 14.41 | 8.80 | 23.21 |
| 1984 | -4.76 | 9.85 | 5.09 |
| 1985 | 24.63 | 7.72 | 32.35 |
| 1986 | 10.41 | 6.16 | 16.57 |
| 1987 | -3.51 | 5.47 | 1.96 |
| 1988 | 11.55 | 6.35 | 17.90 |
| 1989 | 20.51 | 8.37 | 28.88 |
| 1990 | -13.84 | 7.81 | -6.03 |
| 1991 | 29.1 | 5.60 | 34.70 |
| 1992 | 6.41 | 3.51 | 9.92 |
| 1993 | 8.37 | 2.90 | 11.27 |
| 1994 | -4.11 | 3.90 | -0.21 |
| 1995 | 31.04 | 5.60 | 36.64 |
| 1996 | 16.25 | 5.21 | 21.46 |
| 1997 | 26.07 | 5.26 | 31.33 |
| 1998 | 19.41 | 4.86 | 24.27 |
| 1999 | 20.21 | 4.68 | 24.89 |
| 2000 | -16.71 | 5.89 | -10.82 |
| 2001 | -14.78 | 3.78 | -11.00 |
| 2002 | -22.91 | 1.63 | -21.28 |
| 2003 | 30.74 | 1.02 | 31.76 |
| 2004 | 10.69 | 1.20 | 11.89 |
| 2005 | 3.21 | 2.96 | 6.17 |
| 2006 | 10.58 | 4.79 | 15.37 |
| 2007 | 0.83 | 4.67 | 5.50 |
| 2008 | -38.39 | 1.47 | -36.92 |
| 2009 | 29.05 | 0.10 | 29.15 |
| 2010 | 17.68 | 0.12 | 17.80 |
| 2011 | 0.97 | 0.04 | 1.01 |
| 2012 | 16.01 | 0.06 | 16.07 |
| 2013 | 35.15 | 0.03 | 35.18 |
| 2014 | 11.35 | 0.02 | 11.37 |
| 2015 | -0.2 | 0.01 | -0.19 |
| 2016 | 13.22 | 0.19 | 13.41 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Health Care Finance Basic Tools For Nonfinancial Managers
Authors: Judith J. Baker, R.W. Baker, Neil R. Dworkin
5th Edition
1284118215, 978-1284118216
