Consider two stocks, stock A and B$.$ We have annual return data for these two stocks along with the T Bill and market portfolio returns over the past $25$ years.

OBS. STOCK A STOCK B MARKET T BILL

$16\mathrm{.}49\%10\mathrm{.}48\%11\mathrm{.}93\%3\mathrm{.}61\%$

$24\mathrm{.}04\%11\mathrm{.}36\%9\mathrm{.}80\%4\mathrm{.}11\%$

$37\mathrm{.}37\%8\mathrm{.}28\%10\mathrm{.}20\%4\mathrm{.}39\%$

$42\mathrm{.}84\%1\mathrm{.}06\%1\mathrm{.}69\%2\mathrm{.}50\%$

$5-3\mathrm{.}37\%-1\mathrm{.}86\%-5\mathrm{.}87\%2\mathrm{.}21\%$

$64\mathrm{.}65\%13\mathrm{.}81\%11\mathrm{.}00\%4\mathrm{.}45\%$

$74\mathrm{.}48\%6\mathrm{.}22\%6\mathrm{.}19\%3\mathrm{.}69\%$

$82\mathrm{.}06\%2\mathrm{.}87\%2\mathrm{.}66\%2\mathrm{.}63\%$

$92\mathrm{.}70\%21\mathrm{.}18\%13\mathrm{.}74\%3\mathrm{.}48\%$

$10-0\mathrm{.}32\%12\mathrm{.}52\%4\mathrm{.}60\%3\mathrm{.}78\%$

$118\mathrm{.}18\%4\mathrm{.}83\%8\mathrm{.}95\%4\mathrm{.}00\%$

$12-0\mathrm{.}24\%19\mathrm{.}64\%10\mathrm{.}94\%3\mathrm{.}03\%$

$134\mathrm{.}88\%-11\mathrm{.}59\%-5\mathrm{.}40\%3\mathrm{.}95\%$

$145\mathrm{.}89\%7\mathrm{.}60\%8\mathrm{.}74\%3\mathrm{.}06\%$

$152\mathrm{.}70\%-2\mathrm{.}63\%-2\mathrm{.}19\%3\mathrm{.}84\%$

$163\mathrm{.}35\%3\mathrm{.}24\%3\mathrm{.}82\%3\mathrm{.}40\%$

$178\mathrm{.}92\%4\mathrm{.}76\%11\mathrm{.}16\%2\mathrm{.}86\%$

$18-0\mathrm{.}69\%11\mathrm{.}05\%4\mathrm{.}44\%2\mathrm{.}75\%$

$191\mathrm{.}75\%11\mathrm{.}07\%6\mathrm{.}92\%3\mathrm{.}14\%$

$200\mathrm{.}02\%15\mathrm{.}77\%6\mathrm{.}89\%4\mathrm{.}69\%$

$210\mathrm{.}15\%4\mathrm{.}57\%0\mathrm{.}53\%3\mathrm{.}41\%$

$225\mathrm{.}55\%8\mathrm{.}26\%7\mathrm{.}58\%4\mathrm{.}26\%$

$235\mathrm{.}26\%-1\mathrm{.}64\%2\mathrm{.}26\%3\mathrm{.}59\%$

$247\mathrm{.}52\%-0\mathrm{.}44\%5\mathrm{.}12\%3\mathrm{.}22\%$

$251\mathrm{.}59\%-0\mathrm{.}01\%-0\mathrm{.}67\%3\mathrm{.}29\%$

Part $2$

Consider CAPM. Estimate systematic risks, namely betas, of stocks A and B using both the slope function and regression command in Excel.

The Capital Asset Pricing Model $($CAPM$)$ describes the trade$-$off between systematic risk $($beta$)$ and expected return. The Security Market Line $($SML$)$ is

rit $=$ rft $+$i $*($rmt $-$ rft $),$

where rft is the risk$-$free rate, rmt is the market return, and rit is the stock return. Market risk premium is $($rmt $-$ rft $)$ and excess return on the stock is $($ rit $-$ rft $).$ You can use Excel$$s slope function or regression command to compute beta. The dependent $($Y$)$ variable is the excess return on the stock and the independent $($X$)$ variable is the market risk premium.

PROCEDURE :

Calculate the excess returns for stocks A and B$,$ and for market index.

CAPM:

Tools$/$Data Analysis

Select Regression

Specify the X and Y data ranges

Output Options : New Worksheet Ply

It will put the results of the regression analysis on a new worksheet.

Repeat the produre above for stocks A and B$.$

Comment on the questions:

$-$ Are the estimated beta coefficients for stocks A and B statistically significant?

$-$ Based on the estimated betas and standart deviations, which stock is riskier?

can you solve this question by using excel and also give the exact answer that i do not use excel by my self?