Question

An investment analyst prepares the following distribution of the prices of two equity stocks, A and B, she expects to see at the end of the coming financial year in each of five states. The probability that each state might occur has also been estimated and is noted below. The current prices in the market are £45 and £38 per share for A and B respectively.

State	1	2	3	4	5
Probability	0.10	0.15	0.25	0.25	0.25
Stock A
Prices (£)	39.53	40.78	43.63	43.88	49.56
Stock B
Prices (£)	23.50	33.11	38.58	44.02	44.59

1. For both stocks A and B, calculate the expected return, the variance of the returns and the inter-quartile range of the returns.

2. Calculate the covariance between the returns of A and B.

3. Calculate the correlation between the returns of A and B, using the correlation function in Excel, as well as, directly using formulae. Explain the difference in the two correlation values.

4. For both stocks A and B, calculate the negative semi-variance of the return at the end of the year. Why might investors take account of the negative semi-variance of the returns as a measure of risk?

Answer 1

I.For Stocks A and B, Returns are derived Difference between Current value of Stock and value of stock from each state .

For Ex: Return A= 45- 39.53= 5.47

Return B= 38- 23.5= 14.5

refer below Screen shot where 1.52 and 3.24 are Expected Returns which taken by using average of column of return A and return B

Variance of Return taken by using Excel function VAR.S of Columns Return A and BInter-quartile Range: INT (A)= 3.1 and INT (B)= 20.52

II. Covariance between the returns of A and B

III. Correlation between the returns of A and B, using the correlation function in Excel

Correlation between the returns of A and B, using mathematical Formula

There is no much difference in Correlation by Excel i.e. 0.842 and by Formula 0.844. Since Correlation close to 1 we can say that Returns of A and B are positively Correlated to each other.

IV. negative semi-variance of the return:

Refer below screenshot where neg Semi-variance derived for returns of A and B which is Sem(A)= 12.29 and Sem(B)= 65.65

Since Semi-variance is use to estimate the downside risk of investment in that stock, here for stock A Neg Semivariance is less than stock B which means There is more downside risk for Stock B than Stock A.