Term Project 

The Term Project has 2 parts:

• Part A: Portfolio construction using Markowitz optimisation (Excel) 
• Part B: Portfolio construction using SIM-based optimisation (Excel)

Each of the parts are described below. Please attempt ALL parts.

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 emailed to you from [email protected]. You will receive this email shortly, and an announcement will be made when it has been sent. Once the email is sent, please check your junk file and all other inboxes. If you cannot find it, please email [email protected]. The allocated stocks used in the Term Project tutorials are an EXAMPLE only (because your allocated stocks will be different). The five stocks used in the Term Project tutorials have the FactSet identifiers (i) C-US, (ii) FDS-US, (iii) IEX-US, (iv) META-US, and (v) XEL-US.

A2 Data Download and Validation

• For the period from January 2020 through December 2024, 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 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: C-US and FDS-US)? QA12. … Stock 3 and Stock 4 (Example: IEX-US and META-US)? QA13. … Stock 1 and Stock 5 (Example: C-US and XEL-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%.
• 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%?

A4 Capital Allocation Line and the Optimal Risky Portfolio

• Use a risk-free rate of 4.50% APR (i.e., fixed at 0.375% 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
• Now, we can derive the Capital Allocation Line (CAL) by joining the risk-free rate (the yintercept) with P* in a linear line. (Note: if you choose to graph the CAL, it 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: ???? ∗ = ????(???????? ∗ ) − ???????? ???? × ????????∗
• 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?

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 2020 through December 2024, 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, with no duplicates. 
• For the period from January 2020 through December 2024, 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

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

B4 Single Index Model (SIM) Portfolio Optimisation 10 Stocks

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 riskfree rate given above). 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. 

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)?

Summary of Assessment Requirements

The Term Project requires students to apply portfolio optimisation techniques using Markowitz Modern Portfolio Theory (MPT) and the Single Index Model (SIM). The assessment is divided into two major parts, each containing multiple analytical tasks to be completed using Excel.

How the Academic Mentor Guided the Student Step-by-Step Approach

The Academic Mentor supported the student through a structured, analytical workflow that aligned with the technical and conceptual demands of the project.

Step 1: Understanding the Assessment Framework

The mentor first ensured the student clearly understood:

• The difference between Markowitz optimisation and SIM-based optimisation
• How data must be downloaded, validated, and prepared
• The importance of annualising returns, variances, and covariances
• How Solver operates in constrained optimisation

This established the groundwork for the technical tasks ahead.

Step 2: Data Collection and Verification (Part A: A1–A2)

The mentor guided the student to:

1. Access FactSet
2. Download monthly total returns inclusive of dividends
3. Validate data by checking:
   • Correct date ranges
   • Correct stock identifiers
   • 60 observations present
4. Calculate:
   • Monthly averages → annualised returns
   • Covariances → annualised covariance matrix

This step ensured the dataset was accurate before optimisation began.

Step 3: Building the Efficient Frontier (Part A: A3)

The mentor explained how to:

1. Define objective function (minimise portfolio variance)
2. Apply constraints (weight sum = 1, target return achieved)
3. Iterate Solver over target returns from 0% to 30%
4. Collect results into a smooth Minimum Variance Frontier

The mentor demonstrated how varying expected return levels directly influences optimum standard deviation.

Step 4: Constructing CAL & Identifying Optimal Risky Portfolio P* (Part A: A4)

The mentor provided guidance on:

1. Using Sharpe Ratio = (Return – Risk-free rate) / Std. Dev.
2. Running Solver again to maximise the Sharpe Ratio
3. Extracting weights of Stock 1, 3, 5 for reporting
4. Plotting CAL and ensuring it is tangent to the Efficient Frontier

The student learned how risk-free assets combine with risky portfolios to create efficient investment options.

Step 5: Building the Complete Portfolio (C*) (Part A: A5)

The mentor clarified how investor risk preferences influence portfolio allocation:

• Understanding risk aversion coefficient (A)
• Calculating utility-maximising mix of risky vs. risk-free assets
• Computing Sharpe Ratio of C*

This reinforced the conceptual link between theoretical optimisation and investor behaviour.

Step 6: Expanding to 10 Stocks (Part B: B1–B2)

The student was guided to:

1. Add the remaining 5 assigned stocks
2. Combine them with the initial 5
3. Download S&P500 returns as the market benchmark
4. Compute full descriptive statistics for all assets

The mentor emphasised maintaining data consistency and avoiding duplicates.

Step 7: Markowitz GMVP for 10 Stocks (Part B: B3)

The mentor helped the student:

1. Build a 10×10 covariance matrix
2. Use unconstrained (long/short) optimisation
3. Identify GMVP return and standard deviation

This strengthened the student’s understanding of variance minimisation in larger universes.

Step 8: SIM-Based Optimisation (Part B: B4)

The mentor demonstrated the proper steps to implement the Single Index Model:

1. Compute excess returns for each stock
2. Compute excess return for S&P500
3. Run regressions to estimate betas
4. Report highest and lowest betas

This introduced the student to regression-based risk modelling and factor-based ret