ECON1202 – Quantitative Analysis for Business & Economics
ECON1202 – Excel Assignment (10%)
T3, 2024
(Due Thursday 4.00pm, Week 11 (21/11/2024))
Purpose
In this assignment, you will apply some of the quantitative methods covered in this course using Excel. Read the entire assignment carefully, and follow all instructions, including the submission instructions at the bottom of this assignment.
Background
Congratulations on your new role as a Portfolio Analyst at 1202 Capital! Your supervisor, Phuc, has assigned your first task: optimising an investment portfolio. You have already completed a preliminary task: identifying the three stocks that delivered the highest annual returns over the past year. After thorough research, you determined the top performers: Alphabet Inc. (GOOGL) with a return of 78.2%, The Toro Company (TTC) with an impressive 85.4% return, and FirstService Corporation (FSV) with a 60.8% return.
To begin, you’ll work with the “Stock Data” file on Moodle, which includes monthly returns for these companies over the past five years. This information is captured in two matrices:
• The “ returns” matrix (r) contains the average monthly returns of the three stocks.
• The “Variance-Covariance” matrix (V) provides the monthly variances of the three stocks as well as covariances between the stocks’ returns. The variance of asset returns is a measure of how much an asset’s return varies with respect to its average returns. A large variance implies higher risk (in the sense that there is more variation around the average return) while a small variance indicates lower risk. Covariance in the context of stock market indicates how any two assets’ returns move together. A positive covariance indicates that the two assets’ returns move in the same direction whereas a negative covariance implies that the two assets’ returns move in the opposite direction. For a portfolio of 3 assets (say, A, B and C), the variance-covariance matrix will look like this:
σA2, σB(2), σC(2) : variances of the returns of assets A, B, and C.
σA,B : covariance of the returns of assets A and B. Other covariances can be interpreted similarly.
Both the “returns” matrix and the “variance-covariance” matrix have already been filled out, so please do not modify them further.
Refer to the “Q1-Q4” tabs in the spreadsheet to answer questions 1 through 4.
Question 1 [3 marks]
Phuc has decided to allocate half of the available capital to Alphabet Inc. (GOOGL). The remaining capital will be split equally between The Toro Company (TTC) and FirstService Corporation (FSV). Your task is to calculate the portfolio's monthly expected return, using the following formula:
rp = wrr
w: the “weight” matrix (G17:G19). This matrix contains the portfolio’s weights for the three companies. You will need to determine these weights based on the capital distribution outlined above.
r: the “ returns” matrix (G3:G5) described earlier in the assignment.
rp : the portfolio’s monthly expected return.
Report the answer in cell G21 in the spreadsheet.
Question 2 [3 marks]
Using the weights from Question 1, you will now calculate the variance of the portfolio. To do so, apply the following formula:
varp = wr vw
w: the “weight” matrix (as calculated in Question 1)
V: the variance-covariance matrix of the portfolio’s returns, described earlier in the assignment.
varp : the portfolio’s variance.
Report the answer in cell G24 in the spreadsheet.
Question 3 [3 marks]
Calculate the determinant of matrix V-1. Determine whether V is singular or non-singular.
Report the determinant of matrix V-1 in cell G31 and report V-1 in G27:I29 in the spreadsheet.
Question 4 [4 marks]
After realising that the initial weights may not be optimal, Phuc has tasked you with using Excel's Solver to determine the optimal portfolio. The goal is to maximise the reward-to-risk ratio, also known as the Sharpe ratio, which is the ratio of excess return to risk.
In this context, excess return is defined as the difference between the portfolio's monthly expected return and the monthly return on a risk-free investment. The risk-free rate (rf) is currently 0.3% per month, based on the 10-year Commonwealth government bond yield, according to the latest data from Bloomberg.
The portfolio risk is measured by the standard deviation, which is the square root of the portfolio's variance. Your objective is to use Solver to optimise the weight allocation to maximise the Sharpe ratio, effectively balancing return with the risk taken.
The optimisation problem is outlined below:
Report the optimal weights in cells G37:G39, and the Sharpe ratio (sp) in cell G45.
Note: if the weight of an asset is negative, leave it as negative. This is known as short-selling.
Question 5 [7 marks]
In addition to stocks, cryptocurrencies like Bitcoin (BTC) have also generated impressive returns over the past year. However, due to Bitcoin's significant volatility, Phuc is uncertain whether including BTC in the portfolio will improve the reward-to-risk ratio (i.e. the Sharpe ratio, Sp ) from Question 4. Navigate to the "Q5" tab in the spreadsheet and redo the optimisation, this time incorporating Bitcoin alongside GOOGL, TTC, and FSV in the portfolio. Your task is to determine if the inclusion of BTC enhances the reward-to-risk ratio.
Report the optimal weights in cells H17:H20, and the Sharpe ratio (Sp ) in cell H26.
Answer the following questions in the quiz on Moodle: Compared to the portfolio with 3 stocks in Question 4:
• Does including Bitcoin lower the overall risk of the portfolio?
• Does including Bitcoin improve the reward-to-risk ratio of the portfolio?
Note: Please upload the completed Excel file using the link provided in Moodle. Ensure that the file includes all formulas used in matrix calculations [rp, varp, V-1, det(V)]. Failure to upload the Excel file will result in a zero score for this assignment.
Submission instructions
(1) Answer submission: Enter your responses via the “Excel Assignment – Answer Submission” quiz in Moodle. You are allowed only one attempt to submit the quiz, so please ensure that you are confident in your answers before doing so.
(2) Excel file submission: Upload your completed Excel file through the “Excel file submission” link on Moodle. Name your file using the following format: FirstName_LastName_StudentID
(3) Penalty for incomplete submission: If you upload the Excel file without completing the quiz, a 50% penalty will be applied.
(4) Late submission penalty: a penalty of 20% per day (or part thereof), including weekends, will be applied for late submissions.
The assignment is due by 4pm on Thursday of Week 11 (21/11/2024).