Purpose

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 not only extremely beneficial for intermediate & advanced economics and finance courses, but it is also essential for future work after graduation.

Background

Congratulations on your new position as a portfolio analyst at HL Capital! Your boss, Hongyi, has assigned you to your first task: you’ll have to construct and optimize a stock portfolio.

After conducting extensive research,  Hongyi  has shortlisted the following stocks: IGO Ltd (IGO), Aristocrat Leisure Ltd (ALL), and Xero Ltd (XRO).  You will construct a stock portfolio by choosing weights for each of the three stocks. Open the excel data file for this assignment on Moodle.

•        The “weights” matrix is currently blank. Your job will be to fill in the “weights” matrix (w) with the weights of each of the three assets in your chosen portfolio.  The weights must sum up to 100%.

Hongyi has collected data about the historical monthly returns of the three stocks, based on their stock prices from October 1st 2018 to September 1st  2023. Based on this data, Hongyi has calculated the following matrices:

•        The “average  returns”  matrix (r)  provides the average monthly returns of the three assets.

•        The “variance-covariance” matrix (V) provides the variances of the monthly returns of each of the three assets, as well as covariances between the assets’ monthly returns. The variance of an asset’s  return  is  a  measure  of  how  much  its  return  fluctuates around the asset’s average return. A larger variance implies higher risk (in the sense that there  is  more  variation  around  the  average  return)  while  a  smaller  variance indicates lower risk. The covariance between two assets measures the extent to which the two assets’ returns move together. A positive covariance indicates that the two assets’ returns tend to move in the same direction, whereas a negative covariance implies that the two assets’ returns move in opposite directions. 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.

The “returns” and “variance-covariance” matrices have already been calculated for you. Do not modify them further.

Questions [20 marks in total]

1.   As an initial task, Hongyi seeks to create a portfolio where the weight of Xero is twice that of Aristocrat and half that of IGO. Calculate the monthly expected return of this   portfolio using the following formula: TP  =  WTT

(w, r are the weights and returns matrices, respectively, described above;  TP  is the portfolio return)

Report the weights of the 3 stocks in cells G3:G5 and the monthly expected return of the portfolio in cell G20 in the spreadsheet. [4 marks]

2.   Calculate the variance of the above portfolio using this formula:

ⅤaTP  = WT ⅤW

(V: the variance-covariance matrix, and Varp : portfolio variance)

Report the answer in cell G23 in the spreadsheet. [3 marks]

3.   Calculate the determinant of Ⅴ 一1 .  Report the answer in cell L27. Is V singular or non- singular? [2 marks]

4.   Hongyi  has  changed  his  mind;  now  he wants to create a  portfolio with the lowest possible risk. To achieve this, you need to use the “Solver” add-in to find the optimal weights  of  the  three  stocks  that  minimize  the  variance  of  the  portfolio.  The optimization problem is described as follows:

Report the optimal weights in cells G30:G32, and the minimum variance in cell J32.  [4 marks]

5.   Hongyi has just read a finance textbook, and now understands that he faces a

tradeoff between return and risk when constructing his portfolio. He now seeks to   create a portfolio that maximises the ratio of return to risk:  . However, he does not like investing in gambling industry stocks like Aristocrat as these stocks conflict   with his ethical beliefs. Therefore, he wants to restrict the weight of Aristocrat to a maximum of 10%. Keep in mind that the weights must sum up to 100%.