代写FINA2320 Fall 2024 – Assignment 2调试数据库编程

日期：2024-11-25 05:22

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?