Home Depot senior management is preparing for a Board of Directors meeting. At the meeting management is presenting options for using exces is to invest all or part of available cash in new assets to their marketable securities portfolio. Board will be interested in the past performance of stock relative to the market as a whole, thus, your job is integrate and interpret the risk-return relationship by computing and assesing the return a financial recommendation. a. Use the data given to calculate annual returns for Home Depot and the Market Index, and then calculate average returns over the five-year period. (Hint: Remember, returns are calculated by subtracting the beginning price from the ending 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. Assume that dividends are already included in the index. Also, you cannot calculate the rate of return for 2014 because you do not have 2013 data.) Home Depot S&P 500 Index Year 2017 2016 2015 2014 Stock Price $77.13 $76.45 $71.13 $83.88 Dividend $4.50 $4.35 $4.13 Including Dividends 2,839.09 2,321.05 1,962.15 2,068.08 Option 1: iShares Non-US Option 2: Schwab US Dividend Equities ETF Equity ETF ETF Price ETF Price $66.60 $45.98 $52.56 $42.48 $46.70 $36.51 $53.05 $38.90 Option 3: Pimco Active Bond ETF Price $106.83 $107.95 $105.46 $108.58 Home Depot (Current price - purchase price) + Total of all dividends received = Total return in dollars = Total return in dollars = Home Depot Total return in percentage = Total return in percentage = Total return in percentage = Capital gains yield + (Current price - purchase price)/purchase price + Dividend Yield (Total dividends received/purchase price) Now calculate the rates of return for the company, the index, and each of the investment options: Home Depot Annual Return 1+ GW Return Index Annual Return 1 +Index Return iShares Non-US Annual Return 1+ iShares Return Schwab Annual Retur 2017 2016 2015 Geometric mean return Home Depot Index iShares Schwa Note: To get the geometric mean return, utilize the GEOMEAN formula, select the values in the 1+ Goodman Return, then close the parathesis and insert"-1". Repeat process for Index b. Calculate the standard deviation of the returns for Home Depot, the Market Index, and all options. (Hint: Use the sample standard deviation formula, which corresponds to the STDEV function in Excel.) Use the function wizard to calculate the standard deviations. Home Depot Index iShares Schwab Pimc Standard deviation of returns c. Standard deviation is a measure of unsystematic or company/industry specific risk. To measure systematic risk, or risk relative to entire market, we compute beta. Beta is the slope of the regression line between the returns of the market and the returns of a specific stock. Compute the beta for Home Depot and the other options against the Market Index. Home Depot Schwab Dividend iShares Non-US Pimco Bond Home Depot senior management is preparing for a Board of Directors meeting. At the meeting management is presenting options for using exces is to invest all or part of available cash in new assets to their marketable securities portfolio. Board will be interested in the past performance of stock relative to the market as a whole, thus, your job is integrate and interpret the risk-return relationship by computing and assesing the return a financial recommendation. a. Use the data given to calculate annual returns for Home Depot and the Market Index, and then calculate average returns over the five-year period. (Hint: Remember, returns are calculated by subtracting the beginning price from the ending 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. Assume that dividends are already included in the index. Also, you cannot calculate the rate of return for 2014 because you do not have 2013 data.) Home Depot S&P 500 Index Year 2017 2016 2015 2014 Stock Price $77.13 $76.45 $71.13 $83.88 Dividend $4.50 $4.35 $4.13 Including Dividends 2,839.09 2,321.05 1,962.15 2,068.08 Option 1: iShares Non-US Option 2: Schwab US Dividend Equities ETF Equity ETF ETF Price ETF Price $66.60 $45.98 $52.56 $42.48 $46.70 $36.51 $53.05 $38.90 Option 3: Pimco Active Bond ETF Price $106.83 $107.95 $105.46 $108.58 Home Depot (Current price - purchase price) + Total of all dividends received = Total return in dollars = Total return in dollars = Home Depot Total return in percentage = Total return in percentage = Total return in percentage = Capital gains yield + (Current price - purchase price)/purchase price + Dividend Yield (Total dividends received/purchase price) Now calculate the rates of return for the company, the index, and each of the investment options: Home Depot Annual Return 1+ GW Return Index Annual Return 1 +Index Return iShares Non-US Annual Return 1+ iShares Return Schwab Annual Retur 2017 2016 2015 Geometric mean return Home Depot Index iShares Schwa Note: To get the geometric mean return, utilize the GEOMEAN formula, select the values in the 1+ Goodman Return, then close the parathesis and insert"-1". Repeat process for Index b. Calculate the standard deviation of the returns for Home Depot, the Market Index, and all options. (Hint: Use the sample standard deviation formula, which corresponds to the STDEV function in Excel.) Use the function wizard to calculate the standard deviations. Home Depot Index iShares Schwab Pimc Standard deviation of returns c. Standard deviation is a measure of unsystematic or company/industry specific risk. To measure systematic risk, or risk relative to entire market, we compute beta. Beta is the slope of the regression line between the returns of the market and the returns of a specific stock. Compute the beta for Home Depot and the other options against the Market Index. Home Depot Schwab Dividend iShares Non-US Pimco Bond