Question: Calculate the summary statistics Arithmetic average, standard deviation, Min, Max, and geometric average. Calculate the end-of-year values for each asset class (RF, T-Bond and Equity)
- Calculate the summary statistics Arithmetic average, standard deviation, Min, Max, and geometric average.
- Calculate the end-of-year values for each asset class (RF, T-Bond and Equity) with $1 investment at the end of 1927. And plot them in a Chart. Make sure that you use the log scale on the y-axis.
- Calculate annual excess returns time series for T-bond and Equity. Present the average excess returns, standard deviations, and Sharpe ratio.
- Present the covariance and correlation matrix of the excess returns of T-bond and Equity.
| Value from $1 dollars | Excess Returns | |||||||||||||
| Year | RF | T-Bond | Equity | |||||||||||
| Year | Inflation | RF | T-Bond | Equity | 1927 | 1 | 1 | 1 | T-Bond | Equity | ||||
| 1928 | -0.01 | 0.0356 | 0.008354709 | 0.3895 | 1928 | |||||||||
| 1929 | 0.01 | 0.0475 | 0.042038042 | -0.1479 | 1929 | |||||||||
| 1930 | -0.06 | 0.0241 | 0.045409314 | -0.2882 | 1930 | |||||||||
| 1931 | -0.09 | 0.0107 | -0.02558856 | -0.4404 | 1931 | |||||||||
| 1932 | -0.10 | 0.0096 | 0.08790307 | -0.0843 | 1932 | |||||||||
| 1933 | 0.01 | 0.003 | 0.018552721 | 0.5735 | 1933 | |||||||||
| 1934 | 0.02 | 0.0016 | 0.079634426 | 0.0318 | 1934 | |||||||||
| 1935 | 0.03 | 0.0017 | 0.044720477 | 0.4513 | 1935 | |||||||||
| 1936 | 0.01 | 0.0018 | 0.050178754 | 0.3225 | 1936 | |||||||||
| 1937 | 0.03 | 0.0031 | 0.013791461 | -0.3465 | 1937 | |||||||||
| 1938 | -0.03 | -0.0002 | 0.042132485 | 0.2846 | 1938 | |||||||||
| 1939 | 0.00 | 0.0002 | 0.044122614 | 0.0272 | 1939 | |||||||||
| 1940 | 0.01 | 0 | 0.054024816 | -0.0714 | 1940 | |||||||||
| 1941 | 0.10 | 0.0006 | -0.020221976 | -0.1047 | 1941 | |||||||||
| 1942 | 0.09 | 0.0027 | 0.022948682 | 0.1647 | 1942 | |||||||||
| 1943 | 0.03 | 0.0035 | 0.0249 | 0.2831 | 1943 | |||||||||
| 1944 | 0.02 | 0.0033 | 0.025776112 | 0.213 | 1944 | |||||||||
| 1945 | 0.02 | 0.0033 | 0.038044173 | 0.3871 | 1945 | |||||||||
| 1946 | 0.18 | 0.0035 | 0.031283745 | -0.0638 | 1946 | |||||||||
| 1947 | 0.09 | 0.005 | 0.009196968 | 0.0345 | 1947 | |||||||||
| 1948 | 0.03 | 0.0081 | 0.019510369 | 0.0188 | 1948 | |||||||||
| 1949 | -0.02 | 0.011 | 0.046634852 | 0.2022 | 1949 | |||||||||
| 1950 | 0.06 | 0.012 | 0.004295957 | 0.3002 | 1950 | |||||||||
| 1951 | 0.06 | 0.0149 | -0.002953139 | 0.2071 | 1951 | |||||||||
| 1952 | 0.01 | 0.0166 | 0.022679962 | 0.1346 | 1952 | |||||||||
| 1953 | 0.01 | 0.0182 | 0.041438403 | 0.0077 | 1953 | |||||||||
| 1954 | -0.01 | 0.0086 | 0.032898035 | 0.5021 | 1954 | |||||||||
| 1955 | 0.00 | 0.0157 | -0.013364391 | 0.2532 | 1955 | |||||||||
| 1956 | 0.03 | 0.0246 | -0.022557738 | 0.0836 | 1956 | |||||||||
| 1957 | 0.03 | 0.0314 | 0.067970128 | -0.1002 | 1957 | |||||||||
| 1958 | 0.02 | 0.0154 | -0.020990182 | 0.4499 | 1958 | |||||||||
| 1959 | 0.02 | 0.0295 | -0.026466313 | 0.1271 | 1959 | |||||||||
| 1960 | 0.01 | 0.0266 | 0.116395037 | 0.012 | 1960 | |||||||||
| 1961 | 0.01 | 0.0213 | 0.020609208 | 0.2694 | 1961 | |||||||||
| 1962 | 0.01 | 0.0273 | 0.056935441 | -0.1017 | 1962 | |||||||||
| 1963 | 0.02 | 0.0312 | 0.016841621 | 0.2096 | 1963 | |||||||||
| 1964 | 0.01 | 0.0354 | 0.037280649 | 0.1608 | 1964 | |||||||||
| 1965 | 0.02 | 0.0393 | 0.007188551 | 0.1445 | 1965 | |||||||||
| 1966 | 0.03 | 0.0476 | 0.029079409 | -0.0875 | 1966 | |||||||||
| 1967 | 0.03 | 0.0421 | -0.01580621 | 0.287 | 1967 | |||||||||
| 1968 | 0.05 | 0.0521 | 0.032746197 | 0.14 | 1968 | |||||||||
| 1969 | 0.06 | 0.0658 | -0.050140493 | -0.1096 | 1969 | |||||||||
| 1970 | 0.06 | 0.0652 | 0.167547372 | 0.0003 | 1970 | |||||||||
| 1971 | 0.03 | 0.0439 | 0.097868966 | 0.1617 | 1971 | |||||||||
| 1972 | 0.03 | 0.0384 | 0.028184491 | 0.1689 | 1972 | |||||||||
| 1973 | 0.09 | 0.0693 | 0.036586646 | -0.1925 | 1973 | |||||||||
| 1974 | 0.12 | 0.08 | 0.019886087 | -0.2775 | 1974 | |||||||||
| 1975 | 0.07 | 0.058 | 0.036052536 | 0.3824 | 1975 | |||||||||
| 1976 | 0.05 | 0.0508 | 0.159845607 | 0.2699 | 1976 | |||||||||
| 1977 | 0.07 | 0.0512 | 0.012899606 | -0.0314 | 1977 | |||||||||
| 1978 | 0.09 | 0.0718 | -0.007775807 | 0.0821 | 1978 | |||||||||
| 1979 | 0.13 | 0.1038 | 0.006707203 | 0.2347 | 1979 | |||||||||
| 1980 | 0.13 | 0.1124 | -0.029897443 | 0.3337 | 1980 | |||||||||
| 1981 | 0.09 | 0.1471 | 0.081992153 | -0.0342 | 1981 | |||||||||
| 1982 | 0.04 | 0.1054 | 0.328145495 | 0.212 | 1982 | |||||||||
| 1983 | 0.04 | 0.088 | 0.032002094 | 0.2255 | 1983 | |||||||||
| 1984 | 0.04 | 0.0985 | 0.137333643 | 0.038 | 1984 | |||||||||
| 1985 | 0.04 | 0.0772 | 0.257124882 | 0.3263 | 1985 | |||||||||
| 1986 | 0.01 | 0.0616 | 0.242842151 | 0.1628 | 1986 | |||||||||
| 1987 | 0.04 | 0.0547 | -0.049605089 | 0.016 | 1987 | |||||||||
| 1988 | 0.04 | 0.0635 | 0.082235958 | 0.179 | 1988 | |||||||||
| 1989 | 0.05 | 0.0837 | 0.176936472 | 0.2886 | 1989 | |||||||||
| 1990 | 0.06 | 0.0781 | 0.062353753 | -0.0614 | 1990 | |||||||||
| 1991 | 0.03 | 0.056 | 0.1500451 | 0.3478 | 1991 | |||||||||
| 1992 | 0.03 | 0.0351 | 0.093616373 | 0.0974 | 1992 | |||||||||
| 1993 | 0.03 | 0.029 | 0.142109576 | 0.1111 | 1993 | |||||||||
| 1994 | 0.03 | 0.039 | -0.080366556 | -0.002 | 1994 | |||||||||
| 1995 | 0.03 | 0.056 | 0.234807801 | 0.3682 | 1995 | |||||||||
| 1996 | 0.03 | 0.0521 | 0.014286078 | 0.2117 | 1996 | |||||||||
| 1997 | 0.02 | 0.0526 | 0.099391303 | 0.3122 | 1997 | |||||||||
| 1998 | 0.02 | 0.0486 | 0.149214319 | 0.2432 | 1998 | |||||||||
| 1999 | 0.03 | 0.0468 | -0.082542148 | 0.2525 | 1999 | |||||||||
| 2000 | 0.03 | 0.0589 | 0.166552671 | -0.1171 | 2000 | |||||||||
| 2001 | 0.02 | 0.0383 | 0.055721812 | -0.1138 | 2001 | |||||||||
| 2002 | 0.02 | 0.0165 | 0.151164004 | -0.2111 | 2002 | |||||||||
| 2003 | 0.02 | 0.0102 | 0.003753186 | 0.3177 | 2003 | |||||||||
| 2004 | 0.03 | 0.012 | 0.044906837 | 0.1192 | 2004 | |||||||||
| 2005 | 0.03 | 0.0298 | 0.02867533 | 0.0607 | 2005 | |||||||||
| 2006 | 0.03 | 0.048 | 0.019610012 | 0.154 | 2006 | |||||||||
| 2007 | 0.04 | 0.0466 | 0.102099219 | 0.057 | 2007 | |||||||||
| 2008 | 0.00 | 0.016 | 0.201012799 | -0.3674 | 2008 | |||||||||
| 2009 | 0.03 | 0.001 | -0.111166953 | 0.2836 | 2009 | |||||||||
| 2010 | 0.01 | 0.0012 | 0.084629339 | 0.1749 | 2010 | |||||||||
| 2011 | 0.03 | 0.0004 | 0.16035335 | 0.0048 | 2011 | |||||||||
| 2012 | 0.02 | 0.0006 | 0.02971572 | 0.1633 | 2012 | |||||||||
| 2013 | 0.02 | 0.0002 | -0.091045688 | 0.3522 | 2013 | |||||||||
| 2014 | 0.01 | 0.0002 | 0.107461805 | 0.1173 | 2014 | |||||||||
| 2015 | 0.01 | 0.0002 | 0.012842997 | 0.0011 | 2015 | |||||||||
| 2016 | 0.02 | 0.002 | 0.006905505 | 0.135 | 2016 | |||||||||
| 2017 | 0.02 | 0.008 | 0.028017163 | 0.2231 | 2017 | |||||||||
| 2018 | 0.02 | 0.0183 | -0.000166924 | -0.0512 | 2018 | |||||||||
| 2019 | 0.02 | 0.0215 | 0.096356307 | 0.3043 | 2019 | |||||||||
| 2020 | 0.01 | 0.0045 | 0.113318976 | 0.2411 | 2020 | |||||||||
| 2021 | 0.07 | 0.0004 | -0.044160344 | 0.236 | 2021 | |||||||||
| T-Bond | Equity | |||||||||||||
| Average | Average Excess Return | |||||||||||||
| Standard deviation | Standard deviation | |||||||||||||
| Min | Sharpe Ratio | |||||||||||||
| Max | Covariance Matrix | |||||||||||||
| Geomean | ||||||||||||||
| If you using Mac, you may need to enter Ctrl+Shift+Enter to use the array formula | Correlation Matrix | |||||||||||||
Step by Step Solution
There are 3 Steps involved in it
1 Expert Approved Answer
Step: 1 Unlock
Question Has Been Solved by an Expert!
Get step-by-step solutions from verified subject matter experts
Step: 2 Unlock
Step: 3 Unlock
Study Help