Financial Modelling Group Assignment

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