PART A: 5 STOCK MARKOWITZ OPTIMISATION

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
• 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 ..

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
• We will firstly derive the Minimum Variance Frontier (MVF) using the Solver tool in
• 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 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 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 𝑦^∗ for an investor 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 the highest possible utility, given their level of risk 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 (𝑦^∗) 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 𝑦^∗? 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:

𝑈𝑐 = 𝐸(𝑟𝐶∗) − 2 𝐴𝜎𝐶∗
1       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

PART B: 10 STOCK MARKOWITZ AND SIM-BASED OPTIMISATION

B1     Allocated Stocks – 10 Stock Portfolio

Now, please add the following 5 stocks to your portfolio: (vi) HD-US, (vii) IBM-US, (viii) JPM-US,

(ix) WMT-US (x) CVX-US. You should now have 10 stocks in your portfolio with no duplicates.

B2     Data Download and Basic Portfolio Statistics

• For the period from January 2014 through December 2018, download from FactSet the monthly returns (inclusive of dividends) for each of the 5 new stocks that you have been assigned above (60 observations). Combine this new data with the data you have downloaded for your previously allocated 5 stocks so that you have a spreadsheet covering 10 stocks.
• For the period from January 2014 through December 2018, download from FactSet the monthly returns for the S&P 500 Index (FactSet identifier: SP50) (60 observations).
• All returns should be total returns inclusive of dividends - in the FactSet dropdown box "Total Return" select "% ". For the S&P500 "Total Return", select "% Return (Gross, Unhedged)"
• Compute the annualised average return, standard deviation and variance for each
• Compute the annualised average return, standard deviation and variance for the S&P

B3     Markowitz Portfolio Optimisation 10 Stocks

B3.1     Global Minimum Variance Portfolio Under Markowitz

• Derive the 10-stock sample variance-covariance matrix using any method demonstrated in Part A.
• For the portfolio without any position-size constraints (long/short portfolio), identify the Global Minimum Variance Portfolio (GMVP). What is its ...

QB1.... annualised expected return?

QB2.... annualised standard deviation?

• For the portfolio with the constraint that no stock can be shorted (long only portfolio), identify the Global Minimum Variance Portfolio (GMVP). What is its ...

QB3.... annualised expected return?

QB4.... annualised standard deviation?

• For the portfolio without any position-size constraints only, derive the Minimum Variance Frontier for all target annualised expected returns between 0% and 30% in increments of 10%. Discard any portfolio that is inefficient (returns below the GMVP).

QB5. Plot the Efficient Frontier, clearly label it, and include it in your submission for Question 21.

B3.2     Optimal Risky Portfolio P* Under Markowitz

• For the portfolio without any position-size constraints (long/short portfolio), identify the Optimal Risky Portfolio (P*). What is its ...

QB6.... annualised expected return?

QB7.... annualised standard deviation?

• For the portfolio with the constraint that no stock can be shorted (long only portfolio), identify the Optimal Risky Portfolio (P*). What is its ...

QB8.... annualised expected return?

QB9.... annualised standard deviation?

QB10. For the portfolio without any position-size constraints only, plot the Capital Allocation Line (CAL) showing where it intersects the y-axis and the efficient frontier, and include it in your submission for Question 21.

B4     Single Index Model (SIM) Portfolio Optimisation 10 Stocks

B4.1     Derive Excess Returns

• For each stock in your portfolio, calculate monthly excess return: 𝑅𝑖𝑡 = 𝑟𝑖𝑡 − 𝑟𝑓 where 𝑟𝑖𝑡 is the return on stock 𝑖 for month 𝑡, and 𝑟𝑓 is the risk-free (Make sure you use the fixed monthly risk- free rate). Compute the annualised average excess return for each stock in your portfolio over the sample period.
• For the S&P 500 index, calculate monthly excess return: 𝑅𝑀𝑡 = 𝑟𝑀𝑡 − 𝑟𝑓 where, 𝑟𝑀𝑡 is the return on the S&P 500 for month 𝑡. Compute the annualised average excess return for the S&P 500 over the sample

B4.2     Single Index Model (SIM) Regression

• Estimate the SIM beta 𝛽𝑖, for each stock in your portfolio using the regression equation:

𝑅𝑖𝑡 = 𝛼𝑖 + 𝛽𝑖𝑅𝑀𝑡 + 𝜀𝑖𝑡

QB11. What was the highest beta out of your 10 stocks?

QB12. What was the lowest beta out of your 10 stocks (including negative values)?

B4.3     SIM Variance-Covariance Matrix

• Calculate the variance-covariance matrix for your 10-stock portfolio using these SIM

• 
  For the matrix diagonal, simply use the individual variances for each stock 𝜎² derived in

• For the off-diagonal covariances, assume no residual covariance between stocks (the standard assumption of the SIM), and apply the following equation:

Cov(ri , rj) = 𝛽_𝑖 𝛽_𝑗 𝜎²               for all 𝑖, 𝑗,….𝑛 = 10 stocks

B4.4     Global Minimum Variance Portfolio and Optimal Risky Portfolio Under SIM

• Use the (already annualised) sample variance-covariance matrix derived in 3 for all SIM optimisations.
• For the portfolio without any position-size constraints (long/short portfolio), identify the Global Minimum Variance Portfolio (GMVP) under the SIM. What is its ...

QB13.... annualised expected return?

QB14.... annualised standard deviation?

• For the portfolio with the constraint that no stock can be shorted (long only portfolio), identify the Global Minimum Variance Portfolio (GMVP) under the SIM. What is its ...

QB15.... annualised expected return?

QB16.... annualised standard deviation?

• For the portfolio without any position-size constraints (long/short portfolio), identify the Optimal Risky Portfolio (P*) under the SIM. What is its ...

QB17.... annualised expected return?

QB18.... annualised standard deviation?

• For the portfolio with the constraint that no stock can be shorted (long only portfolio), identify the Optimal Risky Portfolio (P*) under the SIM. What is its ...

QB19.... annualised expected return?

QB20.... annualised standard deviation?

QB21. Submit your Excel spreadsheet through the Moodle Assignment link (marked on Moodle as “Project 2 Excel Submission”) with your graphs for B5 and B10 well labelled.

PART C: REPORT YOUR RESULTS

• Prepare an 800-word report to your portfolio manager commenting on your results in a manner that demonstrates a conceptual understanding of Modern Portfolio Theory. Compare your Markowitz and SIM results for the GMVP and optimal risky portfolio (weightings, returns and standard deviations) and provide commentary on the reasons for the differences in the two sets of
• Compare your long only and your long/short portfolio results. Explain why they are different – in particular, why is your GMVP likely to have higher variance and your optimal risky portfolio likely to have a lower Sharpe ratio for the long only portfolio.
• In the context of your results, discuss the limitations and applicability of both the Markowitz and SIM models.

SUBMISSION

• You must submit your answers to the 38 Questions in Part A and the 21 Questions in Part B through the Moodle Questionnaire (marked on Moodle as “Project 2 Parts A and B Submission”. If you only submit your spreadsheet and not the questionnaire answers, you will not receive any marks for this task. Report your answers as decimals EXACTLY to the number of decimal places required in each question in the questionnaire.
• Note that this PDF and the Project 2 Stock Allocation Lookup.xls excel file are the only information needed to complete Project 2. As the first 5 stocks allocated are different for every student, the answers to the questions above will also be unique and different, even though the second lot of 5 stocks are in common.
• Please complete this questionnaire AND submit your Excel file. You are required to submit your Excel files so that your graphs may be Please only submit ONE Excel file for both Parts A and B. For clarity, please ensure your graphs for Part A (Questions A18, A24 and A30) are on a different worksheet to your graphs for Part B (Questions B5 and B10). Submit your Excel spreadsheet through the Moodle Assignment link – marked on Moodle as “Project 2 Excel Submission”.
• Submit your answer to Part C as ONE Word file through the Moodle Assignment link – marked on Moodle as “Project 2 Part C Submission”.
• All submissions are due by 5pm Monday 29^th November 2021.
• ONLY SUBMIT WHEN YOU ARE SATISFIED WITH THE ANSWERS.