UNSW iLab 2 Assessment Instructions
Markowitz and SIM-Based Portfolio Optimization
2024 Term 1
Group A
Please use following 10 stocks and S&P500 index for the portfolio construction:
• STOCK1: ABT-US
• STOCK2: ADBE-US
• STOCK3: AMZN-US
• STOCK4: AXP-US
• STOCK5: BLK-US
• STOCK6: C-US
• STOCK7: CSCO-US
• STOCK8: CVX-US
• STOCK9: EBAY-US
• STOCK10: GOOGL-US
Market Index: S&P 500
To complete the assessment, you must answer all the response fields via Moodle iLab2 Questionnaire
Link and submit your Excel worksheet. Report your answers as decimals (not percentages) EXACTLY
following the example provided in the Moodle questionnaire link.
Note that if you only submit the Excel spreadsheet but do not enter the solution into Moodle
questionnaire, you will not receive any marks for this assessment. Similarly, if you only answer the
questionnaire but do not submit the spreadsheet you will not receive any marks for this assessment.
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 10 stocks above AND the monthly
returns for the S&P 500 Index. 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)". You can either choose to download the data from Factset by
yourself OR directly find your assigned stocks from the Excel “iLab assessment Data pool”
provided under Moodle iLab section. Assuming 3% annualised riskfree rate (i.e., 0.25%
monthly).
• Compute the annualised average return, standard deviation, and variance
2. Markowitz Portfolio Optimisation 10 Stocks
2.1. Global Minimum Variance Portfolio Under Markowitz
• Calculate the sample variance-covariance matrix for the 10 stocks
• 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?
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 ...
5. annualised expected return?
6. 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 ...
7. annualised expected return?
8. Annualised standard deviation?
3. Single Index Model (SIM) Portfolio Optimisation 10 Stocks
3.1. Calculate Excess Returns
• For each stock and S&P 500 index, calculate monthly excess return: = − where is the
return on stock for month , and is the risk-free rate. Compute the annualised average excess
return over the sample period.
3.2. Single Index Model Regression
• Estimate the Single Index Model and , for each stock in your portfolio using the regression
equation:
= + +
where and are the excess return for individual stocks and S&P 500 index
9. What was the highest beta out of your 10 stocks?
10. What was the lowest beta out of your 10 stocks?
• Pick one stock to verify the variance decomposition formula using EXCEL regression tool:
2 =
2
2 +
2
Then, decompose the total variance for all 10 stocks
11. What was the highest
2 out of your 10 stocks?
12. What was the lowest
2 out of your 10 stocks?
3.3. SIM Variance-Covariance Matrix
• Calculate the variance-covariance matrix for the 10 stocks under SIM. For the matrix diagonal, use
the individual variances for each stock:
2. For the off-diagonal covariances, assume no residual
covariance between stocks (the standard assumption of the SIM), and apply the following
equation: (, ) =
2
3.4. Global Minimum Variance Portfolio and Optimal Risky Portfolio Under SIM
• For the portfolio without any position-size constraints (long/short portfolio), identify the Global
Minimum Variance Portfolio (GMVP) under the SIM. What is its ...
13. annualised expected return?
14. 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 ...
15. annualised expected return?
16. 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 ...
17. annualised expected return?
18. 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 ...
19. annualised expected return?
20. annualised standard deviation?
21. Submit your Excel spreadsheet through the Moodle Assignment link (“iLab Task 2 Excel
Submission”)
4. Optional Questions for you to think about
• (Optional Question) Compare your Markowitz and SIM results. Think about why you believe the
Markowitz and SIM models may produce different answers.
• (Optional Question) Compare portfolios with/without short selling constraints. Think about the
potential costs and benefits when you add additional constraints on your portfolio construction.