Purpose
ECON1202 – Excel Assignment (10%)
Summer, 2023
(Due Friday 11.59 pm, Week 5 (3/2/2023))
The idea of this assignment is to give you an opportunity to apply the quantitative methods taught in the course, using Excel. Knowing how to use Excel is extremely beneficial not only for intermediate & advanced economics and finance courses, but also for future work after you graduate.
Background
Congratulations! You have been employed as a portfolio analyst at AK Capital. Your boss, Anton, has given you the first task involving portfolio optimization. Anton has a preference for low-risk assets. However, low-risk assets such as high-quality corporate bonds are not doing well at the moment, and this has adversely affected the overall return of his portfolio. Therefore, Anton decided to add more risky assets (such as cryptocurrencies) to his portfolio. After hours of research, Anton shortlisted the following: iShares Core ETF (IOZ.AX), Ethereum (ETH-USD), and Mineral Resources Limited (MIN.AX). The data file on Moodle comprises monthly returns of the three assets from February 2018 to December 2022. Three matrices have been set up: weights, returns and variance-covariance.
- The “weights” matrix (w) contains the weights of the three assets in the portfolio that you will need to construct later. The weights must sum up to 100%. These weights are intentionally left blank for you to complete the assignment.
- The “returns” matrix (r) contains the average monthly returns of the three assets. This matrix has been filled out. Please do not modify.
- The “Variance-Covariance” matrix (V) provides the monthly variances of the three assets as well as covariances between the assets’ 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:
| 𝐴 |
𝜎2 𝜎𝐴,𝐵 𝜎𝐴,𝐶
| 𝐵 |
!𝜎𝐵,𝐴 𝜎2 𝜎𝐵,𝐶#
| 𝐶 |
𝜎𝐶,𝐴 𝜎𝐶,𝐵 𝜎2
𝜎2, 𝜎2, 𝜎2: variances of the returns of assets A, B, and C.
𝐴 𝐵 𝐶
𝜎𝐴,𝐵: covariance of the returns of assets A and B. Other covariances can be interpreted similarly.
The variance-covariance matrix has been filled out, so please do not modify.
Questions [20 marks in total]
- Currently, Anton wants to invest 20% of the capital in iShares Core while the remaining capital will be evenly split between Ethereum and Mineral Resources Calculate the monthly expected return of this portfolio using the following formula: 𝒓𝒑 = 𝒘𝑻𝒓
(w, r are the weights and returns matrices described above; 𝑟𝑝 is the portfolio return) Report the answer in cell G20 in the spreadsheet. [2 marks]
- Calculate the variance of the above portfolio using this formula:
𝑽𝒂𝒓𝒑 = 𝒘𝑻𝑽𝒘
(V: the variance-covariance matrix Varp: portfolio variance)
Report the answer in cell G23 in the spreadsheet. [3 marks]
- Calculate the determinant of 𝑽)𝟏. Is V singular or non-singular? [2 marks]
- Upon realising that the weights in part 1 are not optimal, Anton wants you to use Excel “Solver” to figure out the optimal weights that maximise his utility (U). The optimization problem is described below:
𝑀𝑎𝑥 𝑈 = 𝒓𝒑 − 0.5 ∗ 𝑽𝒂𝒓𝒑
3
𝑠𝑢𝑏𝑗𝑒𝑐𝑡 𝑡𝑜: > 𝑤+ = 1
+-1
Report the optimal weights in cells G30:G32, and the maximum utility in cell G35. [4 marks] Note: if the weight of an asset is negative, leave it as negative. This is known as short-selling1.
- In light of recent negative news surrounding cryptocurrencies, Anton is not very optimistic about the future of cryptocurrencies. To him, the investment in Ethereum is purely for speculative purposes. As such, Anton decided to limit the capital invested in Ethereum to 10% at most. In addition, the firm is no longer permitted to short sell any assets, so the weights of all assets must be non-negative. Essentially, Anton wants to construct a portfolio that maximises his utility, considering the above constraints. Do not forget that the weights must sum up to 100%.
Report the optimal weights in cells G38:G40, and the maximum utility in cell G43. [4 marks]
- Upload the completed Excel file using the link provided in Moodle. The file must include all formulae used in matrix calculations. You will receive zero on this assignment if your spreadsheet does not include any formula. The upload is worth 5 marks.
Submission instructions
- You will need to enter the answers via the “Excel Assignment – Answer Submission” quiz in Moodle. You can only submit the assignment once so please only attempt the quiz when you are confident.
- Submit the Excel file via the “Excel file submission” link in Moodle. Please name the file as follows: LastName_FirstName_StudentID
- Content coverage: Matrices & Multivariate Optimisation
The assignment is due Friday 11.59 pm, Week 5 (3/2/2023). No late submission will be accepted.
Get expert help for ECON1202 – Quantitative Analysis and many more. 24X7 help, plag free solution. Order online now!
