代写FINA2320 Fall 2024 – Assignment 2调试数据库编程
- 首页 >> Algorithm 算法FINA2320 Fall 2024 -Assignment 2
Due: 6pm Sunday, November 24, 2024
Please hand in an Excel sheet with your answers
Please make sure that your Excel sheet is clear and well-structured!
Be creative in the way you present you answers to improve clarity (Creativity to present results and clarity are part of the grade)
Assignment goal: The goal of the assignment is to show you how you can use Excel to build an optimal portfolio, implement CAPM, and implement the market index model. The assignment also aims at helping you learn Excel and develop your Excel skills as solid knowledge of Excel is important in many jobs.
Instructions:
Cindy is seriously considering hiring your team even before analyzing the performance of your portfolio. However, she would like to make sure that you have chosen the appropriate stocks and portfolio weights. Therefore, she has decided to ask you to use Markowitz portfolio theory to find the optimal weights in the assets that you had selected for the investment competition. Please proceed as follows:
Data
1) Data:
a. Use the Excel formula “=STOCKHISTORY()” to download the monthly close
prices for the two stocks you selected for the investment competition as well as for Meta. Platforms (ticker: META), HSBC (ticker: HSBC), and iShares MSCI World ETF (ticker: URTH). Download the data for the Sept. 1, 2019-Sept. 1, 2024 period.
b. Assume the monthly risk-free rate is constant and equal to 0.15%.
2) Compute the monthly simple returns and the monthly excess returns of the five stocks.
Markowitz Portfolio Selection with 2 risky assets and 1 risk-free asset
3) Compute the following statistics for each of the two stocks that you selected for the competition:
a. The expected monthly return, E[RMontℎly]. (Hint: use the arithmetic mean)
b. The annualized expected return
(Hint: the annualized expected return of stock A is given by Annualized E[RA] = (E[RA,N ] + 1)N − 1 where N is the frequency. For example, to annualize the expectation of daily returns, one could use Annualized E[RA] = ( E[RA,Daily ] + 1)365 − 1))
c. The annualized risk premium (Hint: The annualized risk premium of stock A is given by Annualized RPA = (E[ExcessReturnA,N ] + 1)N − 1 where N is the frequency. For example, to obtain the annualized daily risk premium, one could use Annualized E[RPA] = ( E[ExcessReturnA,Daily ] + 1)365 − 1)))
d. The annualized standard deviation of the excess returns
(Hint: the annualized standard deviation of the excess return of stock A is given by
Annualizedstd. Dev. (ExcessReturnA) = std. Dev. (ExcessReturnA,N ) × √N where N is the frequency. For example, to annualize the standard deviation of daily excess returns, one could use Annualizedstd. Dev. (ExcessReturnA) = std. Dev. (ExcessReturnDaily ) × √365 )
e. The annualized variance of the excess returns (i.e. the square of the annualized standard deviation)
f. The Sharpe ratio. According to the Sharpe Ratios, which of the two stocks is the best? (Hint: use your results from question 3.c and 3.d).
g. The annualized covariance between the two stocks’ excess returns
(Hint: the annualized covariance between ExcessReturnA and ExcessReturnB is given by
Annualized Cov(ExcessReturnA, ExcessReturnB ) =
Cov(ExcessReturnA,N, ExcessReturnB,N ) × N where N is the frequency. Therefore, to annualize the covariance of daily returns, one could use
Annualized Cov(ExcessReturnA, ExcessReturnB ) =
Cov(ExcessReturnA,Daily, ExcessReturnB,Daily ) × 365).
4) Using the annualized expected returns, risk premia, and standard deviations of excess returns that you computed in question 3, compute the Sharpe ratio of an equally- weighted risky portfolio (WA = 50% and WB = 50%).
5) Using the annualized expected returns, risk premia, and standard deviations that you computed in question 3, find the weights of the Markowitz optimal risky portfolio (tangent portfolio) when short-selling is not allowed. (Hint: these are the weights that maximize the Sharpe ratio. You will need to use the Excel Solver, see appendix).
6) Using the annualized expected returns, risk premia, and standard deviations that you computed in question 3, find the weights of the Markowitz optimal risky portfolio (tangent portfolio) when short-selling is allowed. (Hint: these are the weights that maximize the Sharpe ratio. You will need to use the Excel Solver, see appendix).
7) Using the annualized expected returns, risk premia, and standard deviations that you computed in question 3, find the weights of the Markowitz optimal risky portfolio (tangent portfolio) when short-selling is allowed but the weights need to be between - 400% and 400%. (Hint: these are the weights that maximize the Sharpe ratio. You will need to use the Excel Solver, see appendix).
8) Using the annualized expected returns, risk premia, and standard deviations that you computed in question 3, find the weights of the Global Minimum Variance risky portfolio when short-selling is allowed. (Hint: these are the weights that minimize the variance of the portfolio returns. You will need to use the Excel Solver, see appendix).
9) Draw the Mean-Variance Frontier and the Capital Allocation Line. Proceed as follows:
a. Using the annualized expected returns and annualized standard deviations of excess returns that you computed in question 3, compute the annualized standard deviation of excess returns and the annualized expected return for 31 risky portfolios in which a stock A has the following weights: wA = −1, wA = −0.9, wA = −0.8, … , wA = 2
b. Draw the Efficient Frontier using the Excel “Scatter with Smooth Lines and Markers” plot. (see appendix)
c. Using the annualized expected returns and annualized standard deviations of excess returns of the tangent portfolio (that you computed in question 6), compute the annualized standard deviation ofexcess returns and the annualized expected return for 31 “complete” portfolios (i.e. portfolios that may contain the two risky assets and the risk-free asset) where the weights of the optimal risky portfolio are as follows: wrisky pf. = 0, wrisky pf. = 0.1, wrisky pf. = 0.2 , … , wrisky pf. = 2.8, wrisky pf. = 2.9, wrisky pf. = 3.
d. Add the CAL to the “Scatter with Smooth Lines and Markers” plot that you have drawn.
e. Does the tangent portfolio seem to be the portfolio that you computed in question 6?
10) Compute (in Excel) the weights of the optimal risky portfolio in the optimal “complete” portfolio if the risk aversion of the investor equals 8 when short-selling is allowed.
11) Compute (in Excel) the risk aversion of a mean-variance investor who invests 95% in the optimal risky portfolio when short-selling is allowed.
12) Given the above results, are you satisfied with the weights that had selected for the investment competition? What would you have done differently? (Explain briefly, max.
100 words)
Capital Asset Pricing Model (CAPM)
While Cindy is now convinced that you can find optimal weights, she is still critical of the stocks you selected for the competition. To convince her, you have decided to use the CAPM. Proceed as follows:
13) Compute the following statistics:
a. The annualized expected return of the market (use iShares MSCI World ETF as a measure of the market portfolio)
b. The annualized variance of the market excess returns
c. The annualized covariances between the market excess returns and the excess returns of each of the two stocks you selected for the investment competition
d. The market risk premium
e. The betas of the two stocks. Interpret the betas (max. 100 words)
14) Use the betas of the two stocks to compute their annualized expected returns (under CAPM). Compare these beta-implied annualized expected returns to the annualized expected returns that you computed in question 3. Based on this comparison, was your investment competition strategy consistent with the CAPM (in other words, were you right to buy/sell these stocks according to the CAPM)? Do the stocks that you had selected lie on the security market line?
15) Critical thinking: Researchers often find that well-diversified portfolios have an alpha that is different from zero, which implies that CAPM does not work. Why do you think that CAPM performs poorly in practice? (max. 100 words)
Market Index Model
Remember that if your portfolio had more assets, the number of covariance terms to estimate could grow very quickly. Therefore, you would like to test whether using the market index model would help you solve this issue. Please proceed as follows:
16) Use the betas and the variance of the market excess returns that you computed in questions 13.b. and 13.e. to obtain an estimate of the covariance between the two stocks that you selected for the investment competition. Is this estimate of the covariance different from the estimate of the covariance that you computed in question 3.g.? If yes, briefly explain why you think this is the case (max. 100 words) (Hint: question 17 may help you)
17) Compute the R2 of the market index model for the two stocks. Interpret and compare the two R2 (max. 100 words)
Markowitz Portfolio Selection with 4 risky assets and 1 risk-free asset
18) Most of the time, portfolios include more than two risky assets. Therefore, compute the weights of the optimal risky portfolio that includes the two stocks that you selected for the investment competition as well as Meta and HSBC. Like in question 6, do not include constraints on the weights, beside their sum being 1. When computing the covariance terms, please do not use the market index model. Is the Sharpe ratio of this new optimal risky portfolio higher than the Sharpe ratio of the optimal risky portfolio found in question 6?