Excel capstone

Due Friday by $11$:$59$ pm

Points $100$

Submitting an external tool

Available after Jul $26$ at $8$am

The table below shows historical end$-$of$-$week adjusted close prices $($including dividends$)$ for a stock and the S&P $500.$

$\backslash$table$[[,$A$,$B$,$C$,$D$],[1,$Week,Stock,S&P $500,],[2,0,36\mathrm{.}6,2,787,],[3,1,37\mathrm{.}24,2,729,],[4,2,40\mathrm{.}17,2,779,],[5,3,39\mathrm{.}54,2,876,],[6,4,36\mathrm{.}71,2,929,],[7,5,39\mathrm{.}01,2,855,],[8,6,40\mathrm{.}78,2,916,],[9,7,36\mathrm{.}5,2,855,],[10,8,36\mathrm{.}83,2,953,],[11,9,37\mathrm{.}71,3,106,],[12,10,38\mathrm{.}88,3,069,],[13,11,37\mathrm{.}02,2,987,],[14,12,39\mathrm{.}41,3,064,],[15,$Sum,$496\mathrm{.}4,37,905,=$ SUM$($C$2$:C$14)]]$

Copy and paste all data into your own spreadineet. Calculate the sum of the prices for both assets to check that you copied all values correctly. If your sums match those shown above, you should delete row $15$ in your spreadsheet.

Attempt $1/1$

Assume the risk$-$free rate $($Treasury bill yield$)$ was and is $5\%.$ What was the $($annualized$)$ Sharpe ratio of the stock?

Hint: Use the annualized return and standard deviation. The variance of returns over N weeks is N times the weekly variance. The standard deviation of returns over N weeks is $N^{0\mathrm{.}5}$ times the weekly standard deviation.

$2+$ decimals

Attempt $\frac{1}{1}$

For the next few parts, assume a portfolio of $80\%$ stock and $20\%$ S&P $500.$ If you rebalanced such a portfolio every week to keep the weights at $0\mathrm{.}8/0\mathrm{.}2,$ what was the holding period return over the $12$ weeks for the portfolio? Enter your answer as a decimal number $($not in percent$).$