Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due

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