Use EXCEL formula to complete the question

1.What is the annual Sharpe ratio of a portfolio with 60% invested in the stock and 40% in the S&P 500? The T-bill yield is still 2%.

Assume that the stock has an expected return of 14% and the S&P 500 of 7% (both EARs), and that the annualized variances and covariance stay the same as in the past.

Hint: The covariance of returns over N weeks is N times the weekly covariance.

Hint: Since we're looking at only one period (of one year), the distinction between rebalancing and not rebalancing is irrelevant here.

2.What is the annual Sharpe ratio of the optimal risky portfolio?

\begin{tabular}{|c|c|r|r|l|} \hline & A & B & \multicolumn{1}{|c|}{ C } & D \\ \hline 1 & Week & Stock & S\&P 500 & \\ \hline 2 & 0 & 36.07 & 2,630 & \\ \hline 3 & 1 & 36.71 & 2,572 & \\ \hline 4 & 2 & 39.64 & 2,590 & \\ \hline 5 & 3 & 39.01 & 2,623 & \\ \hline 6 & 4 & 36.73 & 2,676 & \\ \hline 7 & 5 & 39.03 & 2,602 & \\ \hline 8 & 6 & 40.8 & 2,689 & \\ \hline 9 & 7 & 36.01 & 2,628 & \\ \hline 10 & 8 & 36.34 & 2,726 & \\ \hline 11 & 9 & 37.22 & 2,879 & \\ \hline 12 & 10 & 38.39 & 2,842 & \\ \hline 13 & Sum & 415.95 & 29,457 & = SUM(C2:C12) \\ \hline \end{tabular} \begin{tabular}{|c|c|r|r|l|} \hline & A & B & \multicolumn{1}{|c|}{ C } & D \\ \hline 1 & Week & Stock & S\&P 500 & \\ \hline 2 & 0 & 36.07 & 2,630 & \\ \hline 3 & 1 & 36.71 & 2,572 & \\ \hline 4 & 2 & 39.64 & 2,590 & \\ \hline 5 & 3 & 39.01 & 2,623 & \\ \hline 6 & 4 & 36.73 & 2,676 & \\ \hline 7 & 5 & 39.03 & 2,602 & \\ \hline 8 & 6 & 40.8 & 2,689 & \\ \hline 9 & 7 & 36.01 & 2,628 & \\ \hline 10 & 8 & 36.34 & 2,726 & \\ \hline 11 & 9 & 37.22 & 2,879 & \\ \hline 12 & 10 & 38.39 & 2,842 & \\ \hline 13 & Sum & 415.95 & 29,457 & = SUM(C2:C12) \\ \hline \end{tabular}