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
20240911 15:30:12
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:

BH

BI

BJ

BK

BL

2


XOM

CVX

COP

PSX

3

Average Monthly Return

0.007551

0.005646

0.006545

0.001253

4

Monthly Variance

0.002992

0.004452

0.001916

0.006986

5

Monthly Standard Deviation

0.054701

0.066723

0.043773

0.083580

6






7

Average Annual Return

0.090615

0.067753

0.078535

0.015036

8

Annual variance

0.035907

0.053423

0.022993

0.083827

9

Annual Standard Deviation

0.189491

0.231134

0.151634

0.289530

2/ We calculate the covariance of each stock with the three others:
We are going to present those with a matrix:
σ_{22} = variance of stock 2
σ_{13} = 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:
BN

BO

BP

BQ

BR

BS

2


Variance Covariance Matrix


3


0.002992

0.001090

0.0007256

0.0018139

4


0.00109

0.004452

0.000518

0.0024497

5


0.000726

0.00052

0.001916

0.001649

6


0.001814

0.00245

0.001649

0.006986

7






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:
BN

BO

BP

BQ

BR

BS


BU

BV

BW

BX

2


Variance Covariance Matrix




E( R )



3


0.002992

0.001090

0.000726

0.00181


XOM

0.007551

0.15

0.14

4


0.00109

0.004452

0.000518

0.00245


CVX

0.005646

0.35

0.25

5


0.000726

0.000518

0.001916

0.001649


COP

0.006545

0.38

0.16

6


0.001814

0.00245

0.001649

0.006986


PSX

0.001253

0.12

0.45

7











8


XOM

CVX

COP

PSX






9

Portfolio A

0.15

0.35

0.38

0.12






10

Portfolio B

0.14

0.25

0.16

0.45






11











12


E( R )

Variance

St Dev







13

Portfolio A

0.005746

0.001746

0.041789







14

Portfolio B

0.00408

0.002968

0.054479







15











16

Covariance (A,B):

0.0021








17

Correlation (A,B):

0.922631








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^{T}E(R) =MMULT(MMULT(BP9:BS9,BP3:BS6),BW3:BW6)
Which is w^{T}Vw
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 WhatifAnalysis. 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 yaxis, 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.
CJ

CK

CL

CM

CN

CO

CP

CQ

CR

2

Variance Covariance Matrix




E( R )0

E( R )  k

3

0.002992

0.001090

0.000726

0.001814


XOM

0.007551

0.107551

4

0.001090

0.004452

0.000518

0.002450


CVX

0.005646

0.105646

5

0.000726

0.000518

0.001916

0.001649


COP

0.006545

0.106545

6

0.001814

0.002450

0.001649

0.006986


PSX

0.001253

0.101253

7









8









9

Example “risk free rate” k=

0.1





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:
CJ

CK

CL

CM

11

With k=0



12

z


x_{a}

13




14




15




16




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

CK

CL

CM

11

With k=0



12

z


x_{a}

13

=



14




15




16




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:
CJ

CK

CL

CM

11

With k=0



12

z


x_{a}

13

2.2063859



14

1.2332439



15

3.7122338



16

1.702461



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
CJ

CK

CL

CM

11

With k=0



12

z


X_{c}

13

2.2063859

XOM

0.404886

14

1.2332439

CVX

0.226308

15

3.7122338

COP

0.681219

16

1.702461

PSX

0.31241

17




18

5.4494023


1

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
CJ

CK

CL

CM

11

With k=0



12

z


X_{c}

13

2.2063859

XOM

0.404886

14

1.2332439

CVX

0.226308

15

3.7122338

COP

0.681219

16

1.702461

PSX

0.31241

17




18

5.4494023


1

19




20

With k=0.1


21

z


X_{d}

22

23.158527

XOM

0.284498

23

17.446671

CVX

0.214329

24

50.311435

COP

0.618067

25

9.51537

PSX

0.11689

26




27

81.401263


1

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).
100% Plagiarism Free & Custom Written, Tailored to your instructions