Part I: The Exercise

$1.$

Pick

two stocks

that are of $$

different risk characteristics

$.$

$2.$

Find their tickers $($i$.$e$.,$ stock exchange ID$)$ and locate them on Yahoo! Finance $($or any other

reliable source of data based on your preference$).$

$3.$

For both companies:

a$.$

Download their

monthly price data

for five years $($be sure to use the same date

range for both$).$ Use the adjusted close prices, as they adjust for dividends, splits,

etc. You should have $60$ prices for each stock $(5$ years $\backslash$times $12$ months per year$).$

b$.$

In Excel $($or any spreadsheet program of your choice, such as Calc in Apache

OpenOffice$),$ do the following:

i$.$

Calculate the

annualized average return

for each stock over the five$-$year

period.

$1.$

Every month

t

$,$ using adjusted close prices, calculate the monthly

returns as $($P

t

$-$P

t$-1$

$)/$P

t$-1$

$.$ You should have $59$ of these monthly

returns, since for each return you need two prices:

a$.$

The $2$

nd

month$$s return in the series would be MRET

$2$

$=($P

$2$

$-$

P

$1$

$)/$P

$1$

$.$

b$.$

The $60$

th

month$$s return in the series would be MRET$60=$

$($P

$60$

$-$P

$59$

$)/$P

$59$

$.$

c$.$

Calculating MRET

$2$

through MRET

$60$

requires using up all $60$

months of returns.

d$.$

If you wish to have $60$ months of returns, you$$ll need $61$

prices.

$2.$

Use the Excel function AVERAGE$()$ to calculate the average of the

monthly returns; $$ denotes the monthly returns series.

$3.$

Annualize this average by multiplying $12.$

$4.$

The resulting number is the annualized average return of the stock

over the five$-$year period.

ii$.$

Calculate the

annualized standard deviation

of the returns of each stock

over the five$-$year period.

$1.$

Use the Excel function STDEV.S$()$ to calculate the standard

deviation on a monthly return basis; again, $$ denotes the monthly

returns series.

FIN$2000\_09\_$Investments$\_$YAHOOFINCWORKOUT.pdf

$2.$

Annualize this by multiplying

$$

$12$

$.$

$$

The square root of $12$ is not

a typo. We$$re annualizing a square$-$rooted second$-$moment statistic.

$3.$

The resulting number is the annualized standard deviation of the

stock over the five$-$year period.

$4.$

Calculate the

annualized average return

and

annualized standard deviation

of a $50-50$

portfolio over the five$-$year period. The $50-50$ portfolio consists of $50\%$ of the portfolio

value allocated to the first stock and the remaining $50\%$ allocated to the second stock.

a$.$

For each month in your series, calculate the weighted average monthly return as

$0\mathrm{.}5\backslash$times MRET

$1$st STK

$+0\mathrm{.}5\backslash$times MRET

$2$nd STK

$.$

b$.$

By now you should have three series of $59$ monthly returns: One for your first

stock, one for you second stock, and now one more for your $50-50$ portfolio.

c$.$

As in part $3.$b$.$i and $3.$b$.$ii above, calculate the

annualized average return

and

annualized standard deviation

using the Excel functions AVERAGE$()$ and

STDEV.S$()$ and multiplying the function outputs by $12$ and

$$

$12$

$,$ respectively.

Part II$.$ Discussions

After the above exercise, submit your answers to the following questions on Canvas:

$1.$

What are the two stocks of your choice? In what way do they have different risk

characteristics? Explain.

$2.$

What are the

annualized average return

and the

annualized standard deviation of the

first stock

$?$

$3.$

What are the

annualized average return

and the

annualized standard deviation of the

second stock

$?$

$4.$

Find two numbers:

a$.$

What is the average of the

annualized average returns of the two stocks

$?$

b$.$

What is the

annualized average return of the $50-50$ portfolio

$?$

c$.$

Which one is larger? Or are they the same?

$5.$

Find two numbers:

a$.$

What is the average of the

annualized standard deviations of the two stocks

$?$

b$.$

What is the

annualized standard deviation of the $50-50$ portfolio

$?$

c$.$

Which one is larger? Or are they the same