Question

Can you please use excel to answer this question. Please

1. The annual returns of three stocks for the past seven years are given in the following table:

Year	Stock X	Stock Y	Stock Z
2011	3.00%	14.00%	19.00%
2012	9.00%	7.00%	11.00%
2013	12.00%	19.00%	5.00%
2014	19.00%	13.00%	12.00%
2015	23.00%	-7.00%	11.00%
2016	5.00%	-3.00%	7.00%
2017	10.00%	9.00%	14.00%

1. Determine the average return and the standard deviation of returns for each stock. Which stock has the highest expected return and which one has the highest risk?
2. Determine the correlation coefficient and the covariance between each pair of stocks.

Answer 1

a. Below are the calculations made using excel

Highest Average return is from Stock X, which is 11.57%.

The most risky stock is Stock Y, as its standard deviation is the highest, that is 0.0868 or 8.68%.

_______________________________________

b. Calculating Correlation Coefficient and Covariance between Stock X and Stock Y

Step 1: Go to Data --> Data Analysis -->Correlation

Step 2: Put the input rate as returns of stocks X and Y , and the output range as a blank cell in the same sheet, and press OK, as shown below.

Step 3: The result is the yellow marked cell.

Correlation Coefficient of X and Y is -0.26558.

Similarly, we will calculate the Covariance between X and Y

Data --> Data Analysis --> Covariance

The orange highlighted cell show the covariance between X and Y

So, the Covariance between X and Y is -0.00154.

_________________________________________________

For your quick revision, Coefficient of Correlation always ranges between -1 and 1. It shows the linear relationship between the two variables (stocks). +1 is strong possitive correlation, which means that both increase or decrease at the same rate in a given scenario, and the converse is also true.

The covariance gives a directional relationship between two assets (risky assets). If it is positive, it means the two assets move in the same direction in a given scenario. If it is negative, the two assets move in an opposite direction. Thus, covariance tell the direction, but not the extent to which the assets will move

___________________________________________________

Similarly, I have calculated both the measures for the pair of Stock Y and Z below, and posted the final result.

___________________________________________________

Similarly, I have calculated both the measures for the pair of Stock X and Z below, and posted the final result.

(Excel tip: the data has to be contiguous, that is it has to be side by side, while making the calculations for these two measures. For this reason, I have removed the values of Stock Y in the above snip)

I hope you will be able to use excel to calculate all these measures after reading these instructions and studying the snips.