Hello-

I could use help on the attached HW assignment for Finance. I have attached the questions and the excel spreadsheet with data details. Thank you for the help!

NORTHWESTERN UNIVERSITY Kellogg School of Management FINC 430 (Finance 1) Quiz 6b: The Capital Asset Pricing Model The capital asset pricing model is the most common method of calculating a cost of capital for use in a discounted cash flow. We will discuss both the mechanics of CAPM and the intuition that lies behind CAPM in class. In this quiz, I want you to use your knowledge of regression to calculate the parameters of CAPM and show me what they mean. I have entered the monthly returns for three assets in the companion spreadsheet. The three assets are the return on a very successful and very exclusive investment fund, the return on the entire US stock market, and the return on short term government bonds (T-bills). Think of T-bills as a risk free asset. The other two assets have risk. 1) Expected return: hedge fund. What is the average annual (historic) return on the hedge fund? Remember the reported returns are monthly. Multiply by 12 to get annual returns.1 Round to three decimals. (5) 2) Expected return stock market. What is the average annual (historic) return on the stock market? Remember the reported returns are monthly. I am asking for annual returns. Round to three decimals. (5) 3) Expected return: risk-free asset. What is the average annual (historic) return on the risk-free asset? Remember the reported returns are monthly. I am asking for annual returns. Round to three decimals. (5) Hedge Fund Stock Market Risk-Free Average Return 4) Risk: hedge fund. What is the annualized standard deviation of the hedge fund return? Remember, you have been given monthly, not annual, returns, but I am asking for the annual standard deviation. You should use the Excel function STDEV.S( ). (10) 5) Risk: stock market. What is the annualized standard deviation of the stock market return? (10) 6) Risk: risk-free asset. What is the annualized standard deviation of the risk-free return? (10) Hedge Fund Standard Deviation 1 This is an approximation which works well enough for our purpose. 1 Stock Market Risk-Free 7) Asset allocation. Given that investors like expected return and dislike risk, if standard deviation of return was the correct measure of risk, which of the three assets would you expect investors to choose (if they can choose only one)? (10) \u0000 \u0000 \u0000 8) Hedge Fund Stock Market Risk-free asset Capital Asset Pricing Model: . A regression is a statistical method of creating a straight line that best fits the data. It tells us how two variables are related. The CAPM regression is used to partition the risk of an asset (the standard deviation you measured above) into the systematic component and the idiosyncratic component. Run the following regression in Excel for the hedge fund returns: What is the of the hedge fund? Enter your number to two decimals (e.g. the market has a of 1.00). The tells you how much systematic risk the asset has. It will also determine the discount rate we would use in a DCF (for an all-equity company) and is used to calculate the expected return in an efficient market. If you find it useful, you can graph the excess return on the hedge fund against the excess return on the stock market and then draw the regression line through the data. (10) 9) Capital Asset Pricing Model: . The stock market was up 2.27% in February of 2005. Given your CAPM regression how did the hedge fund perform? Hint: The expected return according to the CAPM just uses beta and assumes that alpha is zero. (10) \u0000 \u0000 \u0000 \u0000 Better than expected As expected Worse than expected We can't tell given the data 10) Capital Asset Pricing Model: . What is the annualized (i.e. the regression coefficient multiplied by 12)? Express it as a decimal (i.e. if the annualized is 1%, enter your answer as 0.010). In an efficient market and a world without noise, it will be zero. In practice is not zero because of noise in the data, because money managers have investment skill (positive ), they are lucky (positive ) or unlucky (negative ) or because money managers are incompetent (negative ). (10) 11) Asset allocation revisited. Given your CAPM estimates, what fraction of your financial assets would you allocate to the hedge fund, if you were able to invest in it? Remember, it is an exclusive fund and it not open to all investors. You will get full credit for any reasonable answer. I just want to know what you think. (15) 2 Monthly Returns 1990 December 1991 January February March April May June July August September October November December 1992 January February March April May June July August September October November December 1993 January February March April May June July August September October November December 1994 January February March April May June July August Hedge Fund 2.83% 3.08% 1.46% 0.59% 1.39% 1.88% 0.37% 2.04% 1.07% 0.80% 2.82% 0.06% 1.63% 0.49% 2.79% 1.01% 2.86% -0.19% 1.29% 0.00% 0.92% 0.40% 1.40% 1.42% 1.43% 0.00% 1.93% 1.86% 0.06% 1.72% 0.86% 0.09% 1.78% 0.35% 1.77% 0.26% 0.45% 2.18% -0.36% 1.52% 1.82% 0.51% 0.29% 1.78% 0.42% Stock Market 2.95% 4.91% 7.58% 2.89% 0.33% 4.07% -4.40% 4.68% 2.68% -1.10% 1.78% -3.73% 10.68% -0.12% 1.34% -2.37% 1.34% 0.64% -1.93% 3.99% -2.08% 1.24% 1.10% 4.02% 1.78% 1.26% 0.54% 2.51% -2.54% 2.96% 0.54% -0.08% 3.95% 0.06% 1.81% -1.76% 1.95% 3.15% -2.42% -4.58% 0.95% 0.94% -2.79% 3.06% 4.26% Riskfree 0.46% 0.46% 0.45% 0.49% 0.45% 0.45% 0.43% 0.46% 0.44% 0.42% 0.39% 0.33% 0.32% 0.29% 0.31% 0.33% 0.27% 0.30% 0.29% 0.25% 0.26% 0.22% 0.21% 0.24% 0.23% 0.21% 0.23% 0.23% 0.24% 0.25% 0.23% 0.24% 0.25% 0.20% 0.23% 0.24% 0.24% 0.22% 0.24% 0.28% 0.30% 0.32% 0.29% 0.32% 0.35% RM-RF RHF-RF September October November December 1995 January February March April May June July August September October November December 1996 January February March April May June July August September October November December 1997 January February March April May June July August September October November December 1998 January February March April May June July 0.82% 1.88% -0.55% 0.66% 0.92% 0.76% 0.84% 1.69% 1.72% 0.50% 1.08% -0.16% 1.70% 1.60% 0.51% 1.10% 1.49% 0.73% 1.23% 0.64% 1.41% 0.22% 1.92% 0.27% 1.22% 1.10% 1.58% 0.48% 2.45% 0.73% 0.86% 1.17% 0.63% 1.34% 0.75% 0.35% 2.39% 0.55% 1.56% 0.42% 0.91% 1.29% 1.75% 0.42% 1.76% 1.28% 0.83% -1.84% 1.45% -3.72% 1.26% 2.04% 3.96% 2.70% 2.50% 3.40% 3.12% 4.08% 0.93% 3.64% -1.13% 4.27% 1.52% 2.81% 1.63% 1.09% 2.55% 2.68% -0.83% -5.38% 3.25% 5.30% 1.36% 6.55% -1.14% 5.34% -0.11% -4.48% 4.23% 7.16% 4.41% 7.65% -3.63% 5.84% -3.45% 3.05% 1.78% 0.45% 7.32% 5.13% 1.09% -2.58% 3.20% -2.33% 0.36% 0.36% 0.38% 0.30% 0.43% 0.42% 0.48% 0.48% 0.46% 0.44% 0.45% 0.44% 0.43% 0.42% 0.46% 0.38% 0.39% 0.39% 0.43% 0.40% 0.41% 0.40% 0.40% 0.41% 0.42% 0.41% 0.41% 0.41% 0.41% 0.42% 0.44% 0.41% 0.37% 0.42% 0.41% 0.42% 0.40% 0.41% 0.42% 0.38% 0.40% 0.43% 0.44% 0.38% 0.38% 0.39% 0.39% August September October November December 1999 January February March April May June July August September October November December 2000 January February March April May June July August September October November December 2001 January February March April May June July August September October November December 2002 January February March April May June 0.28% 1.04% 1.93% 0.84% 0.33% 2.06% 0.17% 2.29% 0.36% 1.51% 1.76% 0.43% 0.94% 0.73% 1.11% 1.61% 0.39% 2.20% 0.20% 1.84% 0.34% 1.37% 0.80% 0.66% 1.32% 0.25% 0.92% 0.68% 0.43% 2.21% 0.14% 1.13% 1.32% 0.32% 0.23% 0.44% 1.01% 0.73% 1.28% 1.21% 0.19% 0.03% 0.60% 0.46% 1.16% 2.12% 0.26% -15.77% 6.38% 7.44% 6.20% 6.31% 3.83% -3.81% 3.79% 4.90% -2.07% 5.10% -3.06% -1.00% -2.29% 6.20% 3.69% 8.39% -3.97% 3.18% 5.36% -5.95% -3.90% 5.16% -1.71% 7.58% -5.11% -2.46% -10.25% 2.03% 3.95% -9.93% -7.03% 8.39% 1.06% -1.75% -1.83% -5.91% -9.15% 2.80% 7.87% 1.78% -1.61% -2.17% 4.47% -4.96% -1.05% -7.02% 0.39% 0.26% 0.29% 0.37% 0.35% 0.37% 0.38% 0.39% 0.37% 0.37% 0.35% 0.37% 0.37% 0.36% 0.36% 0.38% 0.40% 0.45% 0.44% 0.49% 0.44% 0.39% 0.47% 0.49% 0.50% 0.48% 0.50% 0.49% 0.48% 0.40% 0.40% 0.37% 0.31% 0.28% 0.28% 0.30% 0.28% 0.20% 0.18% 0.16% 0.14% 0.14% 0.15% 0.14% 0.15% 0.14% 0.14% July August September October November December 2003 January February March April May June July August September October November December 2004 January February March April May June July August September October November December 2005 January February March April May Annual Average Annual Std Dev 3.36% -0.06% 0.13% 0.73% 0.16% 0.06% -0.27% 0.40% 1.97% 0.10% 0.95% 1.00% 1.44% 0.22% 0.93% 1.32% -0.08% 0.32% 0.94% 0.50% 0.05% 0.43% 0.66% 1.28% 0.08% 1.33% 0.53% 0.03% 0.79% 0.24% 0.51% 0.37% 0.85% 0.14% 0.63% -8.11% 0.80% -10.00% 7.50% 6.13% -5.33% -2.34% -1.54% 1.03% 8.28% 6.35% 1.63% 2.31% 2.49% -0.91% 6.03% 1.66% 4.55% 2.31% 1.55% -1.07% -2.42% 1.41% 2.16% -3.77% 0.27% 2.06% 1.78% 4.82% 3.52% -2.66% 2.27% -1.69% -2.52% 3.79% 0.14% 0.14% 0.13% 0.12% 0.10% 0.09% 0.10% 0.10% 0.10% 0.09% 0.09% 0.07% 0.07% 0.08% 0.07% 0.08% 0.08% 0.07% 0.07% 0.08% 0.08% 0.07% 0.08% 0.09% 0.10% 0.12% 0.12% 0.14% 0.17% 0.15% 0.18% 0.21% 0.22% 0.23% 0.23%