Must be done in Excel

We have monthly price data for twelve months on two shares: one of Stock A and one of Stock B. The data look as follows:

Month	Stock A Price	Stock A Price
0	25	45
1	24.125	44.875
2	23.375	46.875
3	24.75	45.25
4	26.625	50.875
5	26.5	58.5
6	28	57.25
7	28.875	62.75
8	29.75	65.5
9	31.375	74.375
10	36.25	78.5
11	37.125	78
12	36.875	78.125

These data give the closing price at the end of each month for each stock. The month-0 price is the initial price of the stock. We wish to calculate the relevant return statistics for each stock.

Calculate the monthly return for each stock

Calculate the variance for Stock A and Stock B

Calculate the covariance of the returns

Calculate the correlation coefficient

Now form a portfolio composed of half of Stock A and half of Stock B.

Calculate the monthly expected return of your portfolio

Calculate the mean portfolio return and portfolio variance

Now construct a table varying the proportions of Stock A and Stock B from 0 to 100% in increments of 5%. For each proportion find the expected return and standard deviation. Graph your table.

MUST BE DONE IN EXCEL