ICT704 Non Relational Database Systems Assignments
Delivery in day(s): 4
a. Step by step procedure for calculating expected returns and VarCovar matrix
The first step is to compute the weekly returns, this is calculated using log formula. LN(Dayt+1/Dayt)
After calculating the returns for each cell, average returns are calculated using Average formula. It should be noted that the formula will provide the average weekly returns. In order to calculate the annualized returns it should be multiplied by 52
Similarly standard deviation – risk is calculated using Stdev formula
After computing the returns and standard deviation, Covariance is computed using the Data analysis function. Choose Data Analysis in Excel – Covariance option – select the input range of the returns computed
Annualize the covariance by multiplying it by 52
0.166857 
0.035064 
0.024339 
0.035802 
0.035064 
0.044358 
0.017568 
0.025471 
0.024339 
0.017568 
0.023585 
0.017833 
0.035802 
0.025471 
0.017833 
0.068683 
Calculate the correlation using data analysis tool.
After computing the returns, standard deviation and correlation the data are entered in a separate sheet – Question 1 (a)
The variance – covariance matrix is computed by multiplying the corresponding standard deviation times correlation coefficient. The data is generated as follows
JPMorgan Chase and Co 
Exxon Mobil 
Johnson & Johnson 
Microsoft 

JPMorgan Chase and Co 
0.32% 
0.12% 
0.11% 
0.19% 
Exxon Mobil 
0.12% 
0.09% 
0.05% 
0.08% 
Johnson & Johnson 
0.11% 
0.05% 
0.05% 
0.07% 
Microsoft 
0.19% 
0.08% 
0.07% 
0.13% 
The expected returns are computed based on the assumed weights for each of the security, it should be noted that the total weightage should be equal to 100%.
For different set of weightage, the returns and standard deviation are calculated.
These are then used to plot the meanvariance graph
b. This part requires to compute optimal portfolio for 3 scenarios – standard deviation at 5%; standard deviation at 10% and expected return of 4%
The first step is to get the expected return, variancecovariance and correlation data from part a)
The given data stated that the lending can be at 1%  so we assumed that this is risk free rate
Compute one + expected return i.e., adds 100% to the return. This is shown is column E5 to E9
Then the next step is to compute the values of A, B, C, Delta and Gamma using varrcovar matrix and returns. This is shown in column C37 to C41
Once all the data is calculated, we have to construct the efficient frontier and compute the optimal portfolio – which is the weightage of each security.
The standard deviation and expected return of the efficient frontier curve is computed using A, B, C, Delta and Gamma which was calculated in Step for various tradeoff curve between 0 to 20.
The optimal combination of the standard deviation is set as the weightage of 4 securities times varcovar matrix.
Since we do not know the weightage of 4 securities, but we need standard deviation at 5%, we will use the function called Solver in MS Excel
For using Solver, we need to provide conditions. The conditions which is stated is keep Cell D& (standard deviation) at 5% and change the cells I44 to I47 (which is the weights of 4 assets) with a condition that the sum of i44 t I47 is equal to 100%.
The solver will generate the combination of weights for Asset 1 to Asset 4
Repeat the steps for standard deviation at 10%
However, for expected return of 4%, in solver the cell should be chosen as F70.
We have created 3 excel tabs for each scenarios Q1(b)5%,Q1(b)10% and Q1(b)4%
c. Suppose if there is no risk free rate then
d. Zero correlation
In order for any portfolio, is there any portfolio with zero correlation
Let’s consider a portfolio A and minimum variance portfolio B
We can consider the following
xA + (1X)B
The covariance will be with A is stated as
xVar(A) + (1x)Covariance (A,B)
However, by altering the value of x we can generate 0. So, we may possess a value in the efficient frontier and lets label it as C. Considering the portfolio C as inefficient.
It can also be stated that A, C and B will be in the same straight line
By considering the minimum variance of A and C we get the value of B because the weights stated in the formula with minimal variance for the combination of the assets will always be positive.
The given information is about the standard deviations for 3 assets, Std A = 0.15, Std B = 0.20 and Std C = 0.25. The covariance between AB, BC, CA is 0. Therefore the correlation is also 0, it should be noted that the correlation with the same asset is always 1. So , the correlation matrix is
Correlation 
A 
B 
C 
A 
1 
0 
0 
B 
0 
1 
0 
C 
0 
0 
1 
From the correlation matrix and standard deviation of each stock, we can compute covariance matrix as,
Variance  Covariance 
A 
B 
C 
A 
0.0225 
0 
0 
B 
0 
0.04 
0 
C 
0 
0 
0.0625 
In order to compute the minimum variance portfolio we have to take the matrix method, from the available information the matrix can be written as
Matrix A 
W 
Matrix B 

0.0225 
0 
0 
1 
w1 
0 

0 
0.04 
0 
1 
w2 
= 
0 

0 
0 
0.0625 
1 
w3 
0 

1 
1 
1 
0 
Lambda 
1 
Matrix A is sourced from the VarCovar matrix, with a dummy column and dummy row added so that the total is 0. W is the weightage which needs to be computed and Matrix B is the constant
To calculate weightage we have to compute Inverse of matrix A, which can be calculated in excel using MINVERSE function, the values will return as
Inverse of Matrix A 

21.3264 
13.0039 
8.322496749 
0.520156 
13.0039 
17.68531 
4.681404421 
0.292588 
8.3225 
4.6814 
13.00390117 
0.187256 
0.52016 
0.29259 
0.187256177 
0.011704 
To compute the weightage multiply Inverse of matrix A and matrix B, which will return the values as: 0.520156047 0.292587776, 0.187256177, these values are assigned to w1, w2 and w3 respectively.
In order to compute the standard deviation of portfolio, we need to first calculate the variance. This is computed by multiplying the inverse of weightage matrix, varcovar matrix and weightage matrix.
Using matrix multiplication, the variance is computed as 0.011703511
Taking the square root , we get the standard deviation which is at 0.108182767
Covariance (XA,XP) = E(WA^{T}(x x ) (x x )^{T}WP)
= WA^{T} (E(x x ) (x x )^{T}WP)
= WA^{T} Sig(WP)
The above formula can be simplified by
WA^{T }=( i^{T} Sig1)/C; C is the Variance
=( i^{T} Sig1)/C Sig(WP)
= i^{T} WP / C
= 1/C
It should be noted that 1/C is the variance of MVP.
If it appreciates by 50%, then the price will be 120 and if it depreciates by 25% then it will be 60
At the end of 1 year, the value of the put option is 40, if the price is 60 (10060) and the value of put option will be 20, if the price is 120 (100120)
The value of the portfolio can be expressed as
60x+40 = 120x20
120x60x = 2040
60x = 60
X = 1
The value of the portfolio can be stated as
120 x 20
120 (1)20
2. It is stated that the risk free rate is 5%; therefore the value of the option based on replication is expressed as
= (80x1 + f)x1.05 = 100
(80+f)x 1.05 = 100
84 + 1.05f = 100
1.05f = 10084
1.05f = 16
f = 16/1.05
15.23
Risk neutral valuation can be stated as
120x+60(1x) = 80 x 1.05
60x+60 = 84
60x = 24
X = 0.4
The expected value of the risk neutral option is
20X0.40 + 40X0.60
=16
The present value of the option is derived as = 16 / 1.05
= 15.23
It should be noted that the value of option based on replication and Risk neutral option is same as there is noarbitrage
From the above the value of put option is 15.23, the strike price is 100
If the current price is 10o, then
P < Strike price x e^{rt} – Current price
15.23 < (100)xe^{0.05x1} – 100
15.23 < 100 x (2.718)^{0.05} – 100
15.23 < 105.1266  100
There is no arbitrage.
If the current price is 60, then
P < Strike price x e^{rt} – Current price
15.23 < (100)xe^{0.05x1} – 60
15.23 < 100 x (2.718)^{0.05} – 60
15.23 < 105.1266  60
The above equation is violated, The conclusion is that the arbitrageur can borrow the money 75.23 (60+15.23) at 5% per annum and use the money to buy the stock and simultaneously he can purchase the put option. This will generate profits at all circumstances
In case if the stock goes above 100, the put option become worthless, except for the premium paid, but the stock can be sold at 100. A sum of 100 which is received after one year will have the present value of 100 / 1.05 = 95.23. Whereas the strike price will generate 105.12, which will earn a profit of 9.88 (105.12 – 95.23)
Similar case if for the current price when it is 10.
The value of call option is 10; K = 90 and if the price is 20 and 30
C < Current price – Strike price e^{rt}
10 < 20 – 90x(2.718)^{0.05}
10 < 20 – 85.61
In all the cases there is no opportunity.