Your client is obsessed with technology stocks and wants to invest only in Apple, Google (GOOGL) and Microsoft stock. Download weekly price data for those stocks from Yahoo Finance for 2012. For each stock, you should have one tab (named something like Apple, Google, MSFT) that looks like this (Apple example):

1)Add a column labeled "Weekly return" to each tab and calculate the total return for each week, using the adjusted close. What was the return for Google from 12/23/12 to 12/30/12?

2)On a new tab, create the covariance matrix using Excel's COVARIANCE.S() formula. What is the weekly covariance of returns for Google and Microsoft based on the historical data (report 7 digits)?

3)On the same tab, create the annualized covariance matrix. Covariance, just like variance, is additive: the covariance of 2-weekly returns is twice the covariance of weekly returns.

What is the annualized covariance of returns for Apple and Google?

4)Create the border-multiplied covariance matrix. What is the variance of the equally-weighted portfolio?

5)

Will leave positive feedback if it is correct!

A BCDE F G 1 Date Open High Low Close Adj. Close Volume 58.49 60.39 58.43 60.34 2 1/1/12 40.55 287951300 54 12/30/12 72.93 76.48 72.71 76.02 54.39 164873100 Depending on the date you downloaded the data, your adjusted close prices may be different, but that is irrelevant. Hint: Select cell B2 and choose Window->Freeze Panes to always keep the date and header row in view. You did extensive security research and came up with the following annual expected returns: E(rApple) = 0.07 E(rGoogle) = 0.06 E(TMSFT) = 0.05 What is the expected return of the equally-weighted portfolio? Use Excel's SUMPRODUCT() function. 3+ decimals Submit Part 6 Attempt 1/10 for 3.5 pts. The yield on treasury bills is 2%. What is the Sharpe ratio of the equally-weighted portfolio? 3+ decimals Submit Part 7 Attempt 1/10 for 3.5 pts. Determine the optimal risky portfolio (ORP) using Excel's Solver tool. What is the Sharpe ratio of the portfolio? 3+ decimals Submit Part 8 Attempt 1/10 for 3.5 pts. You have $40,000 to invest, and want to achieve an expected return of 5% on the complete portfolio, including treasury bills. How much money should you put into Apple stock? A BCDE F G 1 Date Open High Low Close Adj. Close Volume 58.49 60.39 58.43 60.34 2 1/1/12 40.55 287951300 54 12/30/12 72.93 76.48 72.71 76.02 54.39 164873100 Depending on the date you downloaded the data, your adjusted close prices may be different, but that is irrelevant. Hint: Select cell B2 and choose Window->Freeze Panes to always keep the date and header row in view. You did extensive security research and came up with the following annual expected returns: E(rApple) = 0.07 E(rGoogle) = 0.06 E(TMSFT) = 0.05 What is the expected return of the equally-weighted portfolio? Use Excel's SUMPRODUCT() function. 3+ decimals Submit Part 6 Attempt 1/10 for 3.5 pts. The yield on treasury bills is 2%. What is the Sharpe ratio of the equally-weighted portfolio? 3+ decimals Submit Part 7 Attempt 1/10 for 3.5 pts. Determine the optimal risky portfolio (ORP) using Excel's Solver tool. What is the Sharpe ratio of the portfolio? 3+ decimals Submit Part 8 Attempt 1/10 for 3.5 pts. You have $40,000 to invest, and want to achieve an expected return of 5% on the complete portfolio, including treasury bills. How much money should you put into Apple stock