Class Project

In this exercise, we will apply what we learned in chapters $10$ and $11$ to real$-$world data.

Part I.

Copy and paste the Adjusted Close prices from each of the individual stock data files onto the Excel

template provided for this project.

Part II$.$ Answer the following questions by filling in the appropriate cell in the Excel template. All the

inputs $($stock prices, beta, expected return on the market, and the risk$-$free rate$)$ must be hard coded $($i$.$e$.,$ typed in as a number$).$ To get full credit, your answers to #$1,$ #$3,$ and #$6$ must be typed in as formulas and

not hard coded $---$ this means you need to reference the cells containing your inputs.

$1.$ Calculate the monthly return for each of the three stocks. $(10$ points$).$ For each stock,

a$.$ What is the arithmetic average monthly return? $(5$ points$)$

b$.$ Using your answer to $1.$a above, what is the EAR? $(5$ points$)$

c$.$ What is the standard deviation? $(5$ points$)$

d$.$ What range of returns should you expect to see with a $95$ percent probability? $(5$ points$)$

$2.$ What is the beta of each stock? $(5$ points. Note: You can get beta from the Summary page on

Yahoo!Finance.$)$

$3.$ Assuming an expected return on the market of $8\mathrm{.}198\%$ and a risk$-$free rate of $3\mathrm{.}768\%,$ what is the

expected return for each stock according to the CAPM? $(15$ points$)$

$4.$ Which of the three stocks has the highest total risk? $(2$ points$)$ Why? $(8$ points. Hint: the correct

reason is based on one of your answers from #$1$ through #$3$ above. You need to cite that measure

in your answer to this question. Read sections $11\mathrm{.}5$ and $11\mathrm{.}6$ in the e$-$textbook for more

information.$)$

$5.$ Which of the three stocks has the highest systematic risk? $(2$ points$)$ Why? $(8$ points. Hint: the

correct reason is based on one of your answers from #$1$ through #$3$ above. You need to cite that

measure in your answer to this question. Read sections $11\mathrm{.}5$ and $11\mathrm{.}6$ in the e$-$textbook for more

information.$)$

$6.$ If you form a portfolio with $40\%$ invested in TROW, and the remainder split equally between

CAT and NDSN$,$

a$.$ What is the expected return on your portfolio? $(5$ points$)$

b$.$ What is the beta of your portfolio? $(5$ points$).$