ZZBU8701 Project 2 Markowitz and SIM-Based Portfolio Optimization

PART A: 5 STOCK MARKOWITZ OPTIMIZATION

A1 Allocated Stocks
You are evaluating a portfolio of 5 U.S. equities drawn from the S&P500. The five allocated stocks are randomly drawn and are unique to you. Each student will have a different combination of stocks. Your allocated stocks will be posted on Moodle in the Project 2 Stock Allocation Lookup.xls excel file in the Project II assessment tab.
This allocated stocks used here are an EXAMPLE only (because your allocated stocks will be different), and will be used for the instructional videos. The five stocks used in this EXAMPLE have the FactSet identifiers (i) APPL-US, (ii) DIS-US, (iii) GS-US, (iv) JNJ-US, and (v) V-US.

A2 Data Download and Validation
▪ 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."
▪ We will firstly compute some basic statistics.
▪ Given that you can multiply monthly average returns by 12 to annualise them, what is the average
annualised return for...
QA1. ...Stock 1?
QA2. ...Stock 2?
QA3. ...Stock 3?
QA4. ...Stock 4?
QA5. ...Stock 5?

▪ Given that you can multiply monthly standard deviations by √12 to annualise them, what is the annualised standard deviation of monthly returns for...
QA6. ... Stock 1?
QA7. ... Stock 2?
QA8. ... Stock 3?
QA9. ... Stock 4?
QA10. ..Stock 5?
▪ Given that you can multiply the covariances of monthly returns by 12 to annualise them, what is the annualised covariance of monthly returns between...
QA11. ... Stock 1 and Stock 2 (Example: APPL-US and DIS-US)?
QA12. ... Stock 3 and Stock 4 (Example: GS-US and JNJ-US)?
QA13. ... Stock 1 and Stock 5 (Example: APPL-US and V-US)?

A3 Efficient Frontier
▪ Now we will proceed to portfolio optimisation.
▪ We will firstly derive the Minimum Variance Frontier (MVF) using the Solver tool in Excel.
▪ 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 10%).
▪ What is the minimum attainable standard deviation of annual returns for...
QA14. ... an expected return level of 0%?
QA15. ... an expected return level of 10%?
QA16. ... an expected return level of 20%?
QA17. ... an expected return level of 30%?
QA18. Plot the MVF, clearly label it, and include it in your Excel spreadsheet submission (as per
Question 38).
▪ Next, we will derive the portfolio weightings for the Global Minimum Variance Portfolio (GMVP) – the portfolio weightings that result in the portfolio having the lowest possible variance (without any constraint on expected portfolio return) – by using Solver.

▪ GMVP: What is the GMVP portfolio weight in ...
QA19. ... Stock 1
QA20. ... Stock 3
QA21. ... Stock 5
▪ Compute the annualised expected return and annualised standard deviation of the GMVP. What is
its ...
QA22. ... annualised expected return?
QA23. ... 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)
QA24. Plot the Efficient Frontier, clearly label it, and include it in your Excel spreadsheet submission (as per Question 38).

A4 Capital Allocation Line and the Optimal Risky Portfolio P*
▪ Use a risk-free rate of 3.00% APR (i.e. fixed at 0.25% monthly) for all parts of this Project.
▪ The Optimal Risky Portfolio (P*) is the point on the Efficient Frontier that has the highest possible Sharpe Ratio. We will derive the portfolio weightings for P* by using Solver.
▪ P*: What is the portfolio weight in P* of ...
QA25. ... Stock 1
QA26. ... Stock 3
QA27. ... Stock 5
▪ Compute the annualised expected return and annualised standard deviation for P*. What is its ...
QA28. ... annualised expected return?
QA29. ... annualised standard deviation?
▪ Now, we can derive the Capital Allocation Line by joining the risk-free rate (the y-intercept) with P* in a linear 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).

QA30. Plot the Capital Allocation Line, clearly label it, and include it in your Excel spreadsheet submission (as per Question 38).

A5 Optimal Complete Portfolio
▪ Assume the optimal allocation to risky assets y
∗
for an investor is given by:

y
∗ =
E(rp
∗ ) − rf
A × σp∗
2

▪ The Optimal Complete Portfolio (C*) is the portfolio combination of risky assets (composed of P*) and risk-free assets that provides an investor the highest possible utility, given their level of risk aversion. We can determine an investor’s risk aversion if we have information on C*. QA31. What is the risk aversion coefficient, A, for Investor I, whose optimal allocation to risky assets (y ∗) is 100%?

QA32. What is investor I’s Optimal Complete Portfolio Sharpe Ratio?
▪ C*: Investor J’s Optimal Complete Portfolio has an annualised standard deviation of 10%. What is Investor J’s...
QA33. ... optimal allocation to risky assets y
∗
?
QA34. ... risk aversion coefficient, A
QA35. ... Optimal Complete Portfolio annualised expected return?
QA36. ... Optimal Complete Portfolio annualised Sharpe ratio?
QA37. ... Optimal Complete Portfolio utility score - using the conventional utility function:

Uc = E(rC
∗) −
1
2
AσC∗
2

QA38. Submit your excel spreadsheet through the Moodle Assignment link (marked on Moodle as “Project 2 Excel Submission”) with your graphs for A18, A24 and A30 well labelled.
▪ (Optional Question) (Optional - you are not required to complete this question) Plot investor J ’s indifference curve at the utility score derived in 37, with the Capital Allocation Line and efficient frontier overlaid, and showing C* as the point of tangency between the indifference curve and the CAL.