$4$ Historical returns

Download the Excel file $$returns$.$xls$$ from the Assignments tab in Cyber Campus.

The file contains the following data in its various columns: $($A$)$ years; $($B$)-($F$)$: annual

nominal simple net returns for portfolios of growth stocks, value stocks, small stocks,

large stocks, as well as a broad market portfolio.

$($a$)$ Use Excel to calculate the average simple return for all five of these portfolios

$($hint: use the AV ERAGE function in the $98$th row$).$ Rank the returns. Explain.

$($b$)$ In H$3,$ write $=$ ln$(1+$ B$3/100)$ which computes the log return in year $1927$ for

the growth portfolio. By copying the cell H$3,$ compute the log return for all five

portfolios and all years. What is the average log return for the five portfolios?

$($c$)$ Our next goal is to compute the cumulative log return for all five portfolios. In N$3,$

write $=$ SUM$($H$$3$ : H$3).$ The $ sign ensures that when you copy this cell, the

number following $ $(3$ in this case$)$ will not change. Copy the cell N$3$ to compute

the cumulative log return for all five portfolios and all years.

$($d$)$ To compute the value of $$1$ invested at the beginning of $1927,$ in cell T$3$ write

$=$ EXP$($N$3).$ Fill in the cells for the other portfolios and years by copying this.

What is the value of this investment in $2021$ for each of the five portfolios? Rank

the five portfolios by their performance during $1927-2021.$

$($e$)$ Create a plot for the performance of all five portfolios. Do this by highlighting

columns T$-$X$.$ Then $($in the most recent version$),$ click Insert tab and choose a Line

chart. By right$-$clicking the chart and choose $$Select Data$,$ you have the option

to rename the series, and also to add labels for the X axis $($from column A$).$ Create

the plot as a new sheet. Right click on the vertical axis, choose format axis, and

check $$Logarithmic scale$.$ Turn in screen shot of your figure in your solution.

$($f$)$ Use Excel to compute the standard deviation of the simple net return for all

five portfolios $($hint: in $99$th row, use the COVARIANCE.S function, note that

cov$($X$,$ X$)=$ var$($X$),$ and that SQRT computes the square root.$).$ What do you

find? Does it make sense?

$($g$)$ In column Z$,$ compute the net simple return for an equal weighted portfolio invested

in growth and value stocks. Use Excel to compute the mean and standard deviation

of this return. How do these numbers compare to the mean and standard deviation

of the value and growth portfolios? Does this make sense?