Assignment $2$

a$.$ Use the year$-$end stock price data given to calculate annual returns for Bartman, Reynolds, and the

Market Index, and then calculate average returns over the five$-$year period. Remember, returns are

calculated by subtracting the beginning price $($previous year year$-$end price$)$ from the ending price $($current

year year$-$end price$)$ to get the capital gain or loss, adding the dividend to the capital gain or loss, and

dividing the result by the beginning price $($previous year year$-$end price$).$ Assume that dividends are already

included in the index., so in your calculation of index return, you don't need to add the dividend. Also, you

cannot calculate the rate of return for $2002$ because you do not have

$2001$ data.$)$

Data as given in the problem are shown below:

We now calculate the rates of return for the two companies and the index:

Note: To get the average, you could get the column sum and divide by $5,$ but you could also use the function

wizard, $fx.$ Click $fx,$ then statistical, then Average, and then use the mouse to select the proper range. Do this for

Bartman and then copy the cell for the other items.

b$.$ Calculate the standard deviation of the returns for Bartman, Reynolds, and the Market Index. $($Hint: Use

the sample standard deviation formula given in the chapter, which corresponds to the STDEV function

in Excel.$)$

Use the function wizard to calculate the standard deviations.

Standard deviation of returns

c$.$ Now calculate the coefficients of variation Bartman, Reynolds, and the Market Index.

Coefficient of Variation

Bartman

Reynolds

Index

Coefficient of Variation

d$.$ Estimate Bartman's and Reynolds's betas as the slope of a regression with stock return on the vertical

axis $($y$-$axis$)$ and market return on the horizontal axis $($x$-$axis$).($Hint: use Excel's SLOPE function.$)$

Bartman's beta $=$

Reynolds' beta $=$

e$.$ The risk$-$free rate on long$-$term Treasury bonds is $6\mathrm{.}04\%.$ Assume that the market risk premium is $5\%.$

What is thereturn on the market? Now use the SML equation to calculate the two companies required

returns.

Market risk premium $(R{P}_M)=5\mathrm{.}000\%$

Risk$-$free rate $=,6\mathrm{.}040\%$

Expected return on market $=$ Risk$-$free rate $,+,$ Market risk premium$]$

Bartman:

Required return $=+$

Reynolds:

Required return $,=$

$x$

f$.$ If you formed a portfolio that consisted of $50\%$ Bartman and $50\%$ Reynolds, what would be its beta and

its required return?