You must calculate the average monthly return and standard deviation for each of the four stocks. You calculated them already for the first two stocks, so you must do the same also with stock 3 and stock 4

BUS350: Investment Analysis 

HOMEWORK  2:

In this exercise, you will have to calculate the returns and corresponding standard deviation of portfolios of four stocks from your industry. Try again to select stocks with the highest market capitalization, starting as far as possible and liquid.

Again, contact me if you encounter some difficulties to select your stocks.

You must send me the Excel spreadsheet otherwise it is zero. 

Part 1 Calculation of returns and standard deviations of portfolio of four stocks

Following the process on Excel described below:

1/ You must calculate the average monthly return and standard deviation for each of the four stocks. You calculated them already for the first two stocks, so you must do the same also with stock 3 and stock 4.

2/ You have to calculation the covariance of each stock with each of the three other stocks.

3/ Calculate the return, variance, and standard deviation of the following two portfolios (A and B):

                        w1                    w2                    w3                    w4

Portfolio A        0.25                 0.25                 0.4                   0.1

Portfolio B        0.10                 0.20                 0.20                 0.5

4/ Find the return and standard deviation of the portfolio composed of portfolio A and portfolio B for each of the cases when the proportion of portfolio A in the portfolio varies from -50% to 150% (increments of 10%) like you did in homework 2.

5/ Draw the graph showing the different portfolio in the Markowitz graph.

Incorporate the four stocks in the graph.

6/ Looking at that chart, can you conclude if the set of possible portfolio is on the Markowitz frontier, meaning if the superior part of that curve is the series of portfolio that are efficient using the four stocks you have?  

Let us continue with the example with Exxon (XOM) and Chevron (CVX) and this time we add two other stocks, COP and PSX.

Suppose that here you are asked to use two portfolio A and B with the following weights:

                        w1                    w2                    w3                    w4

Portfolio A        0.15                 0.35                 0.38                 0.12

Portfolio B        0.14                 0.25                 0.16                 0.45

1/ Calculation of return, variance, and standard deviation of the return of the four stocks:

We found already in homework 1, the return, variance, and standard deviation of XOM and CVX. We add the results for COP and PSX:

2/ We calculate the covariance of each stock with the three others: 

We are going to present those with a matrix:

     σ₂₂ = variance of stock 2

                                    σ₁₃ = Covariance between stocks 1 and 3

With     σ_ii being the variance for stock i

            σ_ij being the covariance between stock i and stock j

Notice that σ_ij = σ_ji

You know how to calculate the covariance of the return of two stocks, so you can create that matrix on Excel. 

The order is conformed to the order in the matrix above:

3/ and 4/ We calculate the return and variance of portfolio A and B. We also find the covariance and correlation between these two portfolios.We are going to express the different elements for calculation in terms of matrices so we can use some specific functions on Excel that will facilitate calculations.We then write the weights and returns as vectors:

and   

Where w1 is the weight of stock 1 (XOM) in the portfolio, etc.

E(R1) is the average return of stock 1 (XOM), etc.

We have also to understand the notion of transpose of matrices. The transpose of a matrix A is the symmetric of the matrix A.

So, we get: 

       and     

We know that the formula for the return of the portfolio of the four stocks we have is:

   with n=number of stocks in the portfolio

So, if we want to express that formula in matrix term, we get:

The same way, we know the formula for the variance for the return of the portfolio:

So, if we want to express it in matrix term, we get:

In Excel, we are going to use the function MMULT that will multiply matrices.

We get then:

The line 9 from BP to BS shows w^T

The column BW from 3 to 6 shows w

The column BV from 3 to 6 shows E(R)

You can find the return, variance, and standard deviation for portfolio A for example.

            =MMULT(BP9:BS9,BV3:BV6)

 Which is w^TE(R) =MMULT(MMULT(BP9:BS9,BP3:BS6),BW3:BW6)

 Which is w^TVw                                                                                            

You should now be able to find out how to find the covariance between the two portfolios A and B as you can proceed as in homework 2.

Or you can use =MMULT(MMULT(BP9:BS9,BP3:BS6),BX3:BX6)

5/ Proceed the same way as homework 2: show the graph representing the portfolios composed of portfolio A and portfolio B.

Do not forget to incorporate on that chart the four stocks.

In order to get a better graph, expand the range of possible portfolios in the What-if-Analysis. Use for example the range from -3 to 5 instead of -0.5 to 1.5.

In your case, are all stocks within the Markowitz envelop?

Part 2 Calculation of Markowitz envelop

In part 1 you came up with an arbitrary choice of two portfolios A and B to construct an envelope following the separation theorem. The problem is that these two portfolios were probably not in the Markowitz efficient frontier, so when you constructed the envelope it does not represent the Markowitz one.

In this exercise, you will have to construct the Markowitz envelope. There is a specific method to do that.

Methodology:

You must find first two portfolios that are on the efficient frontier. Then, by applying the separation theorem you will be able to construct the Markowitz envelop.

The basic idea is that we are going to find a portfolio M for two different “risk free rate” k such as:

You are then going to find the solution z of the equation E(r) – k = Sz where S is the variance covariance matrix of the four stocks from homework 3.

Then we find:  which are the proportion of the different stocks in the portfolio M.

Using two different k we find two different portfolios C and D and following this procedure we know that they are in the Markowitz efficient frontier. Using the separation theorem, we can then find the other portfolios from the efficient frontier: the combinations of these two portfolios will be on the efficient frontier.

Following the process on Excel described below:

1/ You have to calculate the z that are the solutions of the equation E(r) – k = Sz where k is an arbitrary “risk free rate” (more exactly a constant on the y-axis, so it can be negative). Considering the fact that some of your curve are partially on the negative part for expected return, you must choose a low level of k (see chart above) that can be even negative. You do that for two different k so we can find two different portfolios C and D.

2/ You must calculate then the proportion x of the stocks in those portfolios.

3/ Then you proceed the same way than for homework 3. This time you use those two portfolios C and D and you can find the Markowitz envelop.

Let’s continue with the example with XOM, CVX, COP and PSX like in homework 3. 

1/ Calculation of z for two different k: 

You rewrite the covariance matrix.

Then on the columns on the right of that matrix (here columns CQ and CR) you write the returns for the different stocks minus a constant k.

In this example, the column CQ shows the return of the different stocks minus zero. In column CR we have the return of the different stocks minus a constant k that you write in a cell below the covariance matrix.

Then, to find the solution z you must use the concept of array in Excel.

An array is a collection of items. In our case the covariance matrix is an array with four columns and four rows. Another array would be the returns in column CQ, so it has one column and four rows.

Once you use the idea of “array”, then array formulas allow to find easily results of extremely complicated equations.

In our example, we want to find a solution z for a specific k.

We start by selecting a k; here we choose a k=0.

Then you select the cells CK13:CK16 in our example:

Then you click on “=” and the following will appear:

Then you type:

MMULT(MINVERSE(CK3:CN6),CQ3CQ6) and immediately after you do CTRL+SHIFT+ENTER

Meaning CTRL, SHIFT and ENTER at the same time

Covariance matrix returns minus zero

Then the solutions for z will appear:

 2/ After that you can find the proportions of stocks into portfolio C you are going to use:=CK13/CK18=CK14/CK18

 Proportions of stocks for portfolio C

Sum of the z: SUM(CK13:CK16)=CK16/CK18=CK15/CK18

You do the same thing with k=-0.1 to get the proportions of the different stocks for portfolio D and you get overall:

Portfolio C

 Portfolio D

3/ With portfolios C and D you proceed the same way as you did with portfolios A and B in part 1 to get an envelope. This time, it will be the Markowitz envelope because we know that C and D are on this envelope.

Show then the calculation and graph like in homework 3.

However, instead of using a range of -0.5 to 1.5 for the proportion of portfolio C for example, then it is better to use a wider range such as -3 to 5 (still increment of 0.1).