iLab Task II: Markowitz and SIM-Based
Portfolio Optimisation
Overview
For the first iLab task, you were given a portfolio of large capitalisation United States equities that was
unique to you. In today’s session, please add the following 5 stocks to your portfolio: (vi) HD-US, (vii)
IBM-US, (viii) JPM-US, (ix) WMT-US (x) CVX-US. If any of these stocks 5 are already contained in your
allocated 5 stock portfolio, then replace it with APPL-US instead. If a second one of these 5 stocks is
already contained in your allocated 5 stock portfolio, then replace it with DIS-US instead. If a third one,
then replace it with GS-US instead. You should now have 10 stocks in your portfolio with no duplicates.
PART 1: Markowitz Portfolio Optimisation 10 Stocks
1. Data
• Download from FactSet monthly ‘total’ returns inclusive of dividends for the period from
January 2014 to December 2018 for each of the 5 new stocks that you have been assigned
above. Combine this data with the data you have downloaded for your previously
allocated 5 stocks so that you have a spreadsheet covering 10 stocks.
• Download monthly ‘total’ returns inclusive of dividends for the S&P 500 Index (FactSet
identifier: SP50) from FactSet covering the period from January 2014 to December 2018
(60 observations).
• For all the questions below, use 3% per annum APR as the risk-free rate.
• Compute the annualised average return and annualised standard deviation of each stock
and the S&P 500’s monthly returns.
2. Global Minimum Variance Portfolio under Markowitz
• Calculate the sample variance-covariance matrix for the 10 stocks using any method
demonstrated in the previous iLab session.
• Find the security weights, annualised expected return, and annualised standard deviation
of the Global Minimum Variance Portfolio (GMVP) without any position-size constraints.
• Find the security weights, annualised expected return, and annualised standard deviation
of the Global Minimum Variance Portfolio (GMVP) with the constraint that no stock can
e shorted (long-only portfolio).
• For the portfolio without any position-size constraints, find the minimum attainable
annualised standard deviation of returns for all target annualised expected returns
etween 0% and 30% in increments of 5%. Then, plot the minimum variance frontier in
mean-standard deviation space (E(r) − σ).
• For the portfolio without any position-size constraints, plot the efficient frontier.
3. Optimal Risky Portfolio under Markowitz
• Find the security weights, annualised expected return, and annualised standard deviation
of the Optimal Risky Portfolio (P*) without any position-size constraints.
• Find the security weights, annualised expected return, and annualised standard deviation
of the Optimal Risky Portfolio (P*) with the additional constraint that no stock can be
shorted (long-only portfolio).
• For the portfolio without any position-size constraints, plot the Capital Allocation Line
(CAL) showing where it intersects the y-axis and the efficient frontier.
PART II: Single Index Model (SIM) Portfolio Optimisation
4. Excess Returns
• For each stock in your portfolio, calculate monthly excess return: Ri = ri − rf where ri is
eturn on stock i, and rf is the risk-free rate. (Make sure you use the monthly risk-free
ate). Compute the annualised average excess return for each stock in your portfolio.
• For the S&P 500 index, calculate monthly excess return: RM = rM − rf where, rM is return
on S&P 500. Compute the annualised average excess return for the S&P 500.
5. Regression
• Estimate the Single Index Model alpha αi, beta βi and residual variance ?2?i for each stock
in your portfolio using the regression equation:
Rit = αi + βi RMt + ?it
• Estimate the market variance ??
2 using the S&P 500 index monthly returns.
• Compute the total annualised variance for one stock in your portfolio. Using the
annualised variance of the residuals for this stock, confirm the following equality holds:
Var(ri) = ??
2 = ??
2 ??
2 + ?2?i
Note that after you have verified the equality above for one stock - by calculating the
variance of the residuals ?2?i using the Excel regression tools and applying the above
equation to calculate total risk - you may continue using the above method for all stocks
or simply use the Excel sample variance function to calculate ??
2 directly.
• Calculate the variance-covariance matrix for the 10 stocks in your portfolio using these
Single Index Model regression estimates. Assume no residual covariance between stocks
y applying the following equation:
Cov(ri , rj) = ?? ?? ??
2
6. GMVP and Optimal Risky Portfolio under SIM
• Find the security weights, annualised expected return, and annualised standard deviation
of the Global Minimum Variance Portfolio (GMVP) under the SIM without any position-
size constraints.
• Find the security weights, annualised expected return, and annualised standard deviation
of the Global Minimum Variance Portfolio (GMVP) under the SIM with the additional
constraint that no stock can be shorted (long-only portfolio).
• Find the security weights, annualised expected return, and annualised standard deviation
of the Optimal Risky Portfolio (P*) under the SIM without any position-size constraints.
• Find the security weights, annualised expected return, and annualised standard deviation
of the Optimal Risky Portfolio (P*) under the SIM with the additional constraint that no
stock can be shorted (long-only portfolio).
SUBMISSION
Note that this PDF is the only information needed to complete iLab task 2. There is no further
personalised or other information required to complete the second iLab task. As all students have
een allocated different stocks for their first iLab, your answers to the above questions will also be
unique and different even though the 5 new stocks are in common.
The submission links for the iLab are now available. Under the iLab tab see the "iLab Task 2
Submission" link where you will be asked to submit 21 answers based on the questions above.
Please complete this questionnaire AND submit your Excel file in the separate "iLab Task 2
Spreadsheet Submission" link. You will be required to submit your Excel files so that your graphs may
e viewed.
All submissions are due by 10pm Sunday November 22nd 2020.
ONLY SUBMIT WHEN YOU ARE SATISFIED WITH THE ANSWERS.