Investment Analysis Assignment Part I

25503 Investment Analysis
Assignment - Part I
Summer 2018
Name of the Student
Name of the University

Task 1: Portfolio with 5 asset classes [when short
sells allowed]
1[a] Simple weekly return
In order to convert index values into weekly return, the below mentioned excel function is
used:
This return values have been used later on to perform further analysis.
1[b] Vector of Means and Variance Covariance Matrix
The first step of analysis is calculating annualised return of individual stocks and variance
covariance matrix. While calculating annualised return, the analyst first identified the average
return of all 5 assets over the 357 weeks period using the excel average formula
‘=AVERAGE(G4:G360) [for Australian Equity] and then multiplied the value by 52 to get
annualised return. The below table is showing the annualised return of all 5 assets:
Now to find out the vector of return, excel transpose array function
‘{=TRANSPOSE(C2:G3)} is used. The vector of return is as mentioned below:
The annualised variance and covariance is calculated using excel covariance function and the
matrix is as followed:

1[c] Efficient and inefficient assets:
The return matrix is showing the fact that US equity is the most dominant one among the five
assets. In other words, US equity is the efficient asset followed by Australian real estate. On
the other hand, European Equity, Australian Equity, and Asian Equity can be considered as
inefficient asset classes for this specific case.
Now, if Australian equity and Australian real estate are taken into comparison, then it can be
said that Australian real estate dominates Australian equity. Similarly, Australian real estate
dominates European equity. Again, US equity dominates all other 4 assets.
1[d] Values of A, B, C and ∆
In order to calculate A, B, C and ∆, the following excel functions are used:
A = {=MMULT(TRANSPOSE(D7:D11),MMULT(MINVERSE(C15:G19),C7:C11))}
B = {=MMULT(TRANSPOSE(C7:C11),MMULT(MINVERSE(C15:G19),C7:C11))}
C = {=MMULT(TRANSPOSE(D7:D11),MMULT(MINVERSE(C15:G19),D7:D11))}
And ∆ = BC – A^2

1[e] MVS plot with short sales allowed with expected return
0% to 20%
0.50% 1.00% 1.50% 2.00% 2.50% 3.00% 3.50% 4.00% 4.50% 5.00%
0%
5%
10%
15%
20%
25%
MVS with Short Sales Allowed
MVS with Short Sales Allowed Australian Equity Aus Real Estate European Equity
US Equity Asian Equity
1[f] Global Minimum Variance Portfolio [MVP]
The global minimum variance portfolio is the main proficient stock portfolio whose weights
don't rely upon the normal returns. Along the efficient frontier line, one can stay away from