iLAB FINS5513
Markowitz and SIM-Based Portfolio Optimisation
iLAB PROJECT SUMMARY
This iLab project has 3 parts:
▪ Parts A and B – Portfolio Construction (Excel based) – 12 marks
▪ Part C – Portfolio Analysis (Word based including presenting Excel derived graphs) – 8 marks
Each of the parts are described below. Please attempt ALL parts.
PART A: 5 STOCK MARKOWITZ OPTIMISATION
PARTS A AND B ARE WORTH 12 MARKS
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 emailed to you from bankfinexams@unsw.edu.au. You should have received
this email already – if you haven’t, please check your junk file and all other inboxes. If you still cannot
find it, please email robert.bishop@unsw.edu.au.
The allocated stocks used in the iLab session are an EXAMPLE only (because your allocated stocks
will be different). The five stocks used in the iLab session 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 first verify that you have downloaded the correct data for your assigned companies and
that you are able to correctly 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?
▪ Before you continue, please check whether you have downloaded the data correctly from
FactSet by opening the Excel file “FactSet Download Check” on Moodle. Search for your zID
(use the Excel Home menu, go to the magnifying glass, select Find, and then enter your zID).
Check that the answers for QA1-10 that you have derived above correspond exactly with the
answers provided in the Excel file for QA1-10. If they do not, do not proceed as it means you
have downloaded the data incorrectly from Factset. Go back and try downloading the data
again, following the iLab instructions exactly. If your answers for QA1-10 do exactly match
those in the Excel file, enter them in the Quiz QA1-10, and proceed with the following
questions.
▪ 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%?
▪ 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 ...
QA18. ... Stock 1
QA19. ... Stock 3
QA20. ... Stock 5
▪ Compute the annualised expected return and annualised standard deviation of the GMVP. What is
its ...
QA21. ... annualised expected return?
QA22. ... 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)
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 task.
▪ 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 ...
QA23. ... Stock 1
QA24. ... Stock 3
QA25. ... Stock 5
▪ Compute the annualised expected return and annualised standard deviation for P*. What is its ...
QA26. ... annualised expected return?
QA27. ... annualised standard deviation?
▪ Now, we can derive the Capital Allocation Line (CAL) 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).
A5 Optimal Complete Portfolio
▪ Assume the optimal allocation to risky assets ∗ for an investor is given by:
∗ =
(∗) −
× ∗
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*.
QA28. What is the risk aversion coefficient, A, for Investor I, who invests on the CAL and whose
optimal allocation to risky assets (∗) is 100%?
QA29. What is investor I’s Optimal Complete Portfolio Sharpe Ratio?
▪ C*: Investor J’s Optimal Complete Portfolio has an annualised standard deviation of 10% and is
located on the CAL. What is Investor J’s…
QA30. … optimal allocation to risky assets ∗?
QA31. … risk aversion coefficient, A
QA32. … Optimal Complete Portfolio annualised expected return?
QA33. … Optimal Complete Portfolio annualised Sharpe ratio?
QA34. … Optimal Complete Portfolio utility score - using the conventional utility function:
= (∗) −
1
2
∗
2
PART B: 10 STOCK MARKOWITZ AND SIM-BASED
OPTIMISATION
PARTS A AND B ARE WORTH 12 MARKS
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 "% 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.
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?
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 ...
QB5. ... annualised expected return?
QB6. ... 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 ...
QB7. ... annualised expected return?
QB8. ... annualised standard deviation?

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 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.
B4.2 Single Index Model (SIM) Regression
▪ Estimate the SIM beta , for each stock in your portfolio using the regression equation:
= + +
QB9. What was the highest beta out of your 10 stocks?
QB10. 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 estimates.
▪ For the matrix diagonal, simply use the individual variances for each stock
2 derived in B2.
▪ 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) =
2 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 B4.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 ...
QB11. ... annualised expected return?
QB12. ... 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 ...
QB13. ... annualised expected return?
QB14. ... 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 ...
QB15. ... annualised expected return?
QB16. ... 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 ...
QB17. ... annualised expected return?
QB18. ... annualised standard deviation?
PART C: GRAPH AND REPORT YOUR RESULTS
C1 Graphs Part A (C1 and C2 are jointly worth 2 marks)
▪ In your Excel spreadsheet:
a) Plot the Minimum Variance Frontier (MVF) for all target annualised expected returns
between 0% and 30% in increments of 10%, and clearly label it.
b) Plot the Efficient Frontier and clearly label it.
c) Plot the Capital Allocation Line (CAL), showing where it intersects the y-axis and the
efficient frontier and clearly label it.
▪ (Optional Question) (Optional - you are not required to complete this question) Plot
investor J ’s indifference curve at the utility score derived in QA34, with the Capital
Allocation Line and efficient frontier overlaid, and showing C* as the point of tangency
between the indifference curve and the CAL.
C2 Graphs Part B
▪ In your Excel spreadsheet:
d) For the 10-stock Markowitz portfolio without any position-size constraints only, derive
the Minimum Variance Frontier (MVF) for all target annualised expected returns between
0% and 30% in increments of 10%, and clearly label it.
e) For the 10-stock Markowitz portfolio without any position-size constraints only, plot
the Efficient Frontier and clearly label it.
f) For the 10-stock Markowitz 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 clearly label it.
▪ NOW PASTE IN THE SIX GRAPHS a) – f) ABOVE INTO YOUR SUBMITTED WORD
DOCUMENT (SEE C3 next page)
▪ If you do not paste in these graphs to you Word file (C3) you will receive 0 / 2 for this graphical
part
▪ Add the optional graph to your Word document if you have done this
C3 Written Submission (6 marks)
▪ In your Word submission:
▪ Paste in the six graphs a) – f) above (plus the optional graph if you have done this)
▪ Prepare a 500-word report commenting on your results in a manner that demonstrates a
conceptual understanding of Modern Portfolio Theory.
o 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 results – in particular the additional
assumption that the SIM makes on the variance-covariance matrix construction.
o Compare your long only and your long/short portfolio results. Explain why short
selling constraints may affect the optimisation procedure e.g., why your GMVP
will have equal or higher variance and your optimal risky portfolio will have an
equal or lower Sharpe ratio for the long only portfolio.
o In the context of your results, briefly discuss the limitations and applicability of
both the Markowitz and SIM models.
▪ Appendices (e.g. tables comparing your Markowitz and SIM results) are not included in
the word limit
SUBMISSION
▪ You must submit your answers to the 34 Questions in Part A and the 18 Questions in Part B through
the Moodle Quiz (marked on Moodle as “iLab Parts A and B Submission”). If you only submit
your Excel spreadsheet and not the quiz answers, you will not receive any marks for this task.
Similarly, if you only submit the quiz answers and not your Excel spreadsheet, you will not receive
any marks for this task.
▪ Enter all your quiz answers as decimals to 4 decimal places. For example, if your answer is
56.24% enter 0.5624; if your answer is -104.78% enter -1.0478.
▪ This PDF and the email from bankfinexams@unsw.edu.au containing your allocated stocks are all
that is required to complete the iLab. As the first 5 stocks allocated are different for every student,
each student’s answers will also be unique and different, even though the second lot of 5 stocks are
in common.
▪ Please complete the quiz AND submit your Excel file (include your six Excel graphs in your Word
submission as well as your Excel spreadsheet). Please only submit ONE Excel file for both Parts
A and B. Submit your Excel spreadsheet through the Moodle Assignment link – marked on Moodle
as “iLab Excel Submission”.
▪ Submit your answer to Part C as ONE Word file, through the Moodle Assignment link – marked
on Moodle as “iLab Part C Submission”.
▪ Submission deadline: The assessment links will be available on Moodle from 10:00 AM, Monday
16th October until 11:55 PM, Sunday 5th November, Sydney Time. You can attempt the
assessment anytime within this time window, but the assessment will close at 11:55 PM, 5th
November sharply. For example, if you start your attempt at 11:35 PM, on 5th November, you will
only have 20 mins to finish the task. Please plan accordingly and do not leave it to the last minute.
Failure to submit due to any technical issues near the deadline will not be considered as a valid
reason for special consideration application. Any late submission without special consideration
approval will incur a 5% penalty per day (including weekends) from the due date and time. The
assessment will not be accepted after 5 days (120 hours) from the above original deadline.
▪ The iLab is worth 20 marks as indicated above and worth 20% of your course mark.
▪ ONLY SUBMIT WHEN YOU ARE SATISFIED WITH THE ANSWERS