# Financial Modelling Group Assignment

Topics: Modern portfolio theory, Variance, Covariance matrix Pages: 5 (339 words) Published: February 7, 2015
﻿Portfolio 2 Method:

Once, the simple returns have been calculated for all the stocks of choice in the portfolio; average returns should also be computed. It is essential to compute the covariance matrix for the selected stocks. This matrix can be used to determine the relatively consider the amounts of different assets that one should choose to hold in a context of diversification. The Matrix can be evaluated using MMULT and TRANSPOSE functions in excel. Furthermore, using known risk-free rate we can solve the optimal weightage (including short-selling) for each stock in the portfolio in accordance to the covariance matrix. By multiplying the above matrix yields expected return on this passive portfolio as well as other statistical tools like variance and standard deviation. Hence, Sharpe ratio is computed and indicates the risk-adjusted return on the portfolio 2.

STEPS FOR FRONTIER TO BE ADDED

Result: The covariance matrix is as follows:

VCV Matrix
MMM
T
CAT
UTX
WFC
PFE
KO
ORCL
INTC
MRK
MMM
0.0042
0.0020
0.0054
0.0025
0.0037
0.0017
0.0014
0.0026
0.0024
0.0010
T
0.0020
0.0034
0.0038
0.0019
0.0010
0.0012
0.0014
0.0025
0.0020
0.0013
CAT
0.0054
0.0038
0.0137
0.0052
0.0071
0.0034
0.0031
0.0046
0.0049
0.0019
UTX
0.0025
0.0019
0.0052
0.0041
0.0050
0.0024
0.0013
0.0034
0.0032
0.0023
WFC
0.0037
0.0010
0.0071
0.0050
0.0156
0.0045
0.0022
0.0033
0.0026
0.0010
PFE
0.0017
0.0012
0.0034
0.0024
0.0045
0.0039
0.0014
0.0022
0.0015
0.0024
KO
0.0014
0.0014
0.0031
0.0013
0.0022
0.0014
0.0026
0.0012
0.0012
0.0014
ORCL
0.0026
0.0025
0.0046
0.0034
0.0033
0.0022
0.0012
0.0061
0.0042
0.0025
INTC
0.0024
0.0020
0.0049
0.0032
0.0026
0.0015
0.0012
0.0042
0.0062
0.0028
MRK
0.0010
0.0013
0.0019
0.0023
0.0010
0.0024
0.0014
0.0025
0.0028
0.0059

It is worth noticing how MMM and CAT vary with each other and also the relationship between CAT and WFC.

The next table does the final evaluation of the weights reaching its optimal level when short selling is allowed using the above covariance matrix. MMM
-56.29%
T
-116.34%
CAT
19.48%
UTX
85.49%
WFC
-20.45%
PFE
-174.90%
KO
286.49%
ORCL
165.72%
INTC
-65.48%
MRK
-23.72%