##### ITC556 Database Systems Assignment Help

Delivery in day(s): 4

**a. Step by step procedure for calculating expected returns and Var-Covar 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 mean-variance 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, variance-covariance 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 varr-covar 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 trade-off curve between 0 to 20.

The optimal combination of the standard deviation is set as the weightage of 4 securities times var-covar 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**

- Use the sample calculation from above part.
- In the last scenario – we need to compute the optimal portfolio at 3.5%, however without the risk free rate.
- So, delete the column C5 (which has risk free rate of 1%) and use solver by changing the cell F70 to 3.5% and again run the optimal portfolio.

**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 + (1-X)B

The covariance will be with A is stated as

xVar(A) + (1-x)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 Var-Covar 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, var-covar 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

- For part b) we do the same as above, however the standard deviation for each asset is assumed to be Std A = 0.05; Standard deviation of B = 0.10 and Standard deviation of C = 0.15. Repeat all the steps as above, we get standard deviation as 0.069006556
- The basic objective is to prove that
*Cov(P*= Variance of MVP_{M}, P_{A})

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} Sig-1)/C; C is the Variance

=( i^{T} Sig-1)/C Sig(WP)

= i^{T} WP / C

= 1/C

It should be noted that 1/C is the variance of MVP.

Get more information **Finance Assignment Help**

- Price = 80, K = 100

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 (100-60) and the value of put option will be -20, if the price is 120 (100-120)

The value of the portfolio can be expressed as

-60x+40 = -120x-20

120x-60x = -20-40

60x = -60

X = -1

The value of the portfolio can be stated as

-120 x -20

-120 (-1)-20

- The current value of the portfolio is = -80x + f; in this f is considered as the value of the option

2. It is stated that the risk free rate is 5%; therefore the value of the option based on replication is expressed as

= (-80x-1 + f)x1.05 = 100

(80+f)x 1.05 = 100

84 + 1.05f = 100

1.05f = 100-84

1.05f = 16

f = 16/1.05

15.23

**Risk neutral valuation can be stated as**

120x+60(1-x) = 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 no-arbitrage

**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.

*Francis, Jack. (2013). Modern Portfolio Theory: Foundations, Analysis, and New Developments. John Wiley & Sons**Hull, John. (2014). Options, Futures and Other Derivatives. Prentice Hall Publications**Joshi, Mark (2013). Introduction to mathematical portfolio theory. Cambridge University Press*