Use the Excel file and perform the tasks that are specified on the next page. Please calculate everything in Excel and use the corresponding Excel formulas (manual calculation using a calculator might be possible, but it is not feasible in the given time frame).

To do (1: 15%; 2: 10%; 3: 10%; 4: 20%; 5: 25%; 6: 20%)

1. Calculate the average return and the volatility of the market index and separately of each of the stocks. Plot the results in a return-volatility graph; also include the market index.

2. Calculate the correlation and covariance of each stock separately with the market index.

3. Calculate the stock betas of each stock separately. Use the results for the average return from Part 1 and the betas to plot a return-beta graph; also include the market portfolio.

4. Assume a risk-free rate of 2% p.a. and a market risk premium of 6% p.a. Use the CAPM to determine the expected return for each stock. Use the betas from Part 3 and the expected returns to plot an expected return-beta graph; also include the market index. Use the volatility from Part 1 and the expected returns to plot an expected return-volatility graph; also include the market portfolio.

5. Briefly discuss your findings and interpret each graph from Parts 1, 3 and 4.

6. (Difficult.) Determine the beta, expected return, and volatility of (i) an equally weighted portfolio of the 19 stocks and (ii) a market value-weighted portfolio of the 19 stocks. Briefly discuss your results in the context of your findings in the other parts. Hint: Use the most recent market capitalization of the companies for the value-weighted approach.

Can someone tell me the formulas in excel if we are given share prices, market capitalization and market index?