iLab Task II
Markowitz and SIM-Based Portfolio Optimisation
FINS2624: 2022 Term 1


Allocated Stocks – 10 Stock Portfolio
For iLab task I, you were given a portfolio of 5 U.S. stocks drawn from the S&P500 that was unique to
you. For iLab II, 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.

1 - 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 "% Return.". For the S&P500 "Total Return", select "% Return (Gross, Unhedged)"
▪ Compute the annualised average return, standard deviation and variance for each stock.
▪ Compute the annualised average return, standard deviation and variance for the S&P 500.
▪ Use a risk-free rate of 3.00% APR (i.e. fixed at 0.25% monthly) for all questions (parts 2 and 3).

2 Markowitz Portfolio Optimisation 10 Stocks
2.1 Global Minimum Variance Portfolio Under Markowitz
▪ Calculate the sample variance-covariance matrix for the 10 stocks using any method demonstrated
in iLab I.
▪ For the portfolio without any position-size constraints (long/short portfolio), identify the Global
Minimum Variance Portfolio (GMVP). What is its ...
1. ... annualised expected return?
2. ... 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 ...
3. ... annualised expected return?
4. ... 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 5%.

5. Plot the Minimum Variance Frontier, clearly label it, and include it in your submission for
Question 22.
▪ Discard any portfolio that is inefficient (returns below the GMVP).
6. Plot the Efficient Frontier, clearly label it, and include it in your submission for Question 22.
2.2 Optimal Risky Portfolio ∗ Under Markowitz
▪ For the portfolio without any position-size constraints (long/short portfolio), identify the
Optimal Risky Portfolio (∗). What is its ...
7. ... annualised expected return?
8. ... annualised standard deviation?
▪ For the portfolio with the constraint that no stock can be shorted (long only portfolio), identify
the Optimal Risky Portfolio (∗). What is its ...
9. ... annualised expected return?
10. ... annualised standard deviation?
11. 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 22.

3 Single Index Model (SIM) Portfolio Optimisation 10 Stocks
3.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 rate. (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 period.
3.2 Single Index Model Regression
▪ Estimate the Single Index Model alpha (note that the alpha is not necessary for this task but is
always useful to know) and beta , for each stock in your portfolio using the regression equation:
= + +
12. What was the highest beta out of your 10 stocks?
13. What was the lowest beta out of your 10 stocks (including negative values)?
▪ To familiarise yourself with the Excel regression tools, pick one stock in your portfolio and derive
its variance using the following equation:

2 =
2
2 +
2
To do this, first determine the residual variance (
2 ) by obtaining the monthly residuals for this
one stock using Excel’s regression tools, and calculate their variance (annualise by multiplying by
12). Then add the stock’s systematic variance (
2
2 ) and verify the residual variance (
)
plus the systematic variance (

) is consistent with the total variance of the stock.
▪ Note that after you have verified the equality above for one stock you may continue using the above
method for all stocks, or as a shortcut, simply use the Excel sample variance function to calculate
annualised
2 directly for each stock.
3.3 SIM Variance-Covariance Matrix
▪ Calculate the variance-covariance matrix for the 10 stocks in your portfolio using these Single Index
Model regression estimates.
▪ For the matrix diagonal, simply use the individual variances for each stock
2 derived in 3.2.
▪ For the off-diagonal covariances, assume no residual covariance between stocks (the standard
assumption of the SIM), and apply the following equation (using annualised
2 ) :
Cov(ri , rj) =
2 for all , ,…. = 10 stocks
3.4 Global Minimum Variance Portfolio and Optimal Risky Portfolio Under SIM
▪ Use the (already annualised) sample variance-covariance matrix derived in 3.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 ...
14. ... annualised expected return?
15. ... 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 ...
16. ... annualised expected return?
17. ... annualised standard deviation?
▪ For the portfolio without any position-size constraints (long/short portfolio), identify the
Optimal Risky Portfolio (∗) under the SIM. What is its ...
18. ... annualised expected return?
19. ... annualised standard deviation?
▪ For the portfolio with the constraint that no stock can be shorted (long only portfolio), identify
the Optimal Risky Portfolio (∗) under the SIM. What is its ...
20. ... annualised expected return?
21. ... annualised standard deviation?
22. Submit your Excel spreadsheet through the Moodle Assignment link (marked on Moodle as
“iLab Task 2 Excel Submission”) with your graphs for 5, 6 and 11 well labelled.

▪ (Optional Question) Compare your Markowitz and SIM results. Write down the reasons why you
believe the Markowitz and SIM models may have produced different answers.
▪ (Optional Question) Compare your long only and your long/short portfolios. Can you 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?

Submission
▪ Note that this PDF is the only information needed to complete iLab task II. There is no further
personalised or other information required to complete it. As all students have been allocated
different stocks for iLab I, your answers to the above questions will also be unique and different,
even though the 5 new stocks are in common.
▪ You must submit your 22 answers through the Questionnaire in the Moodle iLab tab (marked on
Moodle as “iLab Task 2 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 at least to the number of decimal places required in each question in the questionnaire
(generally 4 decimal places).
▪ Please complete this questionnaire AND submit your Excel file. You are required to submit your
Excel files so that your graphs may be viewed. If you only answer the questionnaire and do not
submit your Excel file, you will not receive any marks for this task.
▪ All submissions are due by 11pm Sunday 24th April 2022 (end of Week 10). No late submissions
will be accepted.
▪ ONLY SUBMIT WHEN YOU ARE SATISFIED WITH THE ANSWERS. THERE IS ONLY
ONE OPPORTUNITY TO SUBMIT.