代写Markowitz Portfolio Optimization 2024 Term 1代写Processing

UNSW iLab 1 Assessment Instructions

Markowitz Portfolio Optimization

2024 Term 1

Scenario

You are evaluating a portfolio of U.S. equities drawn from the S&P500. The five unique, randomly drawn stocks in your portfolio have the FactSet identifiers

•    STOCK1: C-US

•    STOCK2: CSCO-US

•    STOCK3: CVX-US

•    STOCK4: EBAY-US

•    STOCK5: GOOGL-US

To complete the assessment, you must answer all the response fields via MoodleiLab1 Questionnaire Link and submit your Excel worksheet as outlined in the "Excel Instructions". Report your answers as decimals (not percentages) EXACTLY following the example provided in the Moodle questionnaire link.

In addition, the quiz contains items marked as "Excel Instructions". You must follow these instructions and create the indicated plots in an Excel workbook. This workbook must also contain the worksheets necessary to answer the numerical questions. You must SUBMIT this workbook as required for your assignment to be complete.

Note that  if  you  only  submit  the  Excel  spreadsheet  and  do  not  enter  the  solution  into  Moodle questionnaire, you will not receive any marks for this assessment. Similarly, if you only answer the questionnaire but do not submit the spreadsheet you will not receive any marks for this assessment.

Data Download and Summary Statistics

▪ For the period from January 2014 through December 2018, download the monthly returns for each stock in your portfolio from FactSet (60 observations). All returns should be inclusive of dividends - in

the FactSet dropdown box "Total Return" select "% Return." You can either choose to download the

data from Factset by yourself OR directly find your assigned stocks from the Excel “iLab assessment

Data pool” provided under MoodleiLab section.

▪ Given that you can multiply monthly average returns by 12 to annualise them, what is the average annualised return for...

1. STOCK1?

2. STOCK2?

3. STOCK3?

4. STOCK4?

5. STOCK5?

▪ Given that you can multiply monthly standard deviations by √12 to annualise them, what is the annualised standard deviation of monthly returns for...

6. STOCK1?

7. STOCK2?

8. STOCK3?

9. STOCK4?

10. STOCK5?

▪ Given that you can multiply the covariances of monthly returns by 12 to annualise them, what is the annualisedcovariance of monthly returns between...

10. STOCK1 and STOCK2?

11. STOCK3 and STOCK4?

12. STOCK1 and STOCK5?

Efficient Frontier Construction

▪ We will first derive the Minimum Variance Frontier (MVF): For a portfolio constructed from your assigned  securities,  find  the   portfolio  weightings  that  would   minimise   its  annualised  standard deviation/variance of returns at each expected annual portfolio return level between 0% and 30% (in increments of 5%).

▪ What is the minimum attainable standard deviation of annual returns for...

13. an expected return level of 0%?

14. an expected return level of 5%?

15. an expected return level of 10%?

16. an expected return level of 15%?

17. an expected return level of 20%?

18. an expected return level of 25%?

19. an expected return level of 30%?

20. Excel Instructions: Plot  the  MVF,  clearly   label  it,  and   include  it  in  your   Excel  spreadsheet submission.

▪ Next, we will derive the portfolio weightings for the Global Minimum Variance Portfolio (GMVP): For a portfolio constructed from your assigned securities, find the GMVP security weights that results in the  portfolio  having the  lowest  possible  variance  without  any  constraint on  expected  portfolio return. What is the GMVP portfolio weight in ...

22. STOCK1?

23. STOCK2?

24. STOCK3?

25. STOCK4?

26. STOCK5?

▪ Compute the annualised expected return and annualised standard deviation of the GMVP. What is its ...

27. annualised expected return?

28. annualised standard deviation?

▪ Now, we can derive the Efficient Frontier by discarding any portfolio that is inefficient - that is, any portfolio on the MVF that has a return lower than the GMVP

29. Excel Instructions: Plot the Efficient Frontier, clearly label it, and include it in your Excel spreadsheet submission.

Capital Allocation Line and the Optimal Risky Portfolio P*

▪ We will derive the portfolio weightings for the Optimal Risky Portfolio P*. This portfolio is the point on the Efficient Frontier that has the highest possible Sharpe Ratio. Use a risk-free rate of 3.00% APR (i.e. fixed at 0.25% per month) for this analysis. For your assigned stocks, find the security find the security weights for the Optimal Risky Portfolio using solver. What is the portfolio weight in P* of …

30. STOCK1?

31. STOCK2?

32. STOCK3?

33. STOCK4?

34. STOCK5?

▪ Compute the annualised expected return and annualised standard deviation for P*. What is its ...

35. annualised expected return?

36. annualised standard deviation?

▪ Now, we can derive the Capital Allocation Line by joining the risk-free rate (they-intercept) with P* in a line. Note: this should be tangent to your efficient frontier – if it is not, then extend your efficient frontier target level expected returns beyond 30% until you have at least one return level greater than the P* expected return and they should now be tangent to each other.

37. Excel Instructions: Plot the Capital Allocation Line, clearly label it, and include it in your Excel spreadsheet submission.

Optimal Complete Portfolio

▪ Assume the optimal allocation to risky assets y∗ for an investor with utility function U = E(r) − 2/1Aσ2 is given by:

The Optimal Complete Portfolio (C*) is the portfolio combination of risky assets (composed of P*) and risk-free assets that  provides an investor with the  highest  possible  utility, given their  level  of risk aversion.

38. What is the risk aversion coefficient, A, for Investor I, whose optimal allocation to risky assets y∗ is 100%?

39. What is investor I’s Optimal Complete Portfolio annualised Sharpe Ratio?

▪ Investor J’s Optimal Complete Portfolio has an annualised standard deviation of 10%. What is Investor J’s …

40. optimal allocation to risky assets y∗?

41. risk aversion coefficient, A?

42. Optimal Complete Portfolio annualised expected return?

43. Optimal Complete Portfolio annualised Sharpe ratio?

44. What is Investor J’s Optimal Complete Portfolio utility score?

▪ Excel Instructions: (Optional - you are not required to complete this question) Plot investor J ’s indifference curve with the utility score derived in the previous question. Overlay the Capital Allocation Line and efficient frontier, and show C*as the point of tangency between the indifference curve and the CAL.

Excel File

▪ Excel Instructions: Submit your Excel file via the related link on Moodle. Make sure you've included the three required plots described above inside your Excel Workbook.