1. In cell F19, enter the ticker for Home Depot ("HD"). After entering the ticker, access the Data tab and define this cell as a Stocks data type. 2. In cell G21, use the STOCKHISTORY function to display the close date and monthly closing prices for Home Depot. Other than the first input for the STOCKHISTORY function (the reference to your new stock ticker), all other inputs should be exactly the same as the example command provided in cell E21. This will autofill cells G23:H143 with historical monthly stock prices. 3. In cells J24 through J143, calculate the return each month for stock 2. 4. In cell G19, calculate the stock's average monthly return over the last 10
1. In cell F19, enter the ticker for Home Depot ("HD"). After entering the ticker, access the Data tab and define this cell as a Stocks data type. 2. In cell G21, use the STOCKHISTORY function to display the close date and monthly closing prices for Home Depot. Other than the first input for the STOCKHISTORY function (the reference to your new stock ticker), all other inputs should be exactly the same as the example command provided in cell E21. This will autofill cells G23:H143 with historical monthly stock prices. 3. In cells J24 through J143, calculate the return each month for stock 2. 4. In cell G19, calculate the stock's average monthly return over the last 10 years using the AVERAGE function, and in cell H19, calculate the effective annual rate (EAR) of return over the time period using the average monthly return from G19. 5. In cell I19, calculate the sample standard deviation (using the STDEV.S function) of the stock's monthly returns. In cell J19, annualize this value by multiplying 119 by the square root of 12 . 6. In cell B24, calculate the correlation between the monthly returns of the two stocks using the CORREL function. 7. In cell N18, calculate the return of a portfolio with 0% weight in MCD and 100% weight in HD using the EAR calculated in step 4. Fill in cells N19 through N38 by repeating this calculation for the given weights in the table. 8. In cell 018 , calculate the variance of a portfolio with 0% weight in MCD and 100% weight in HD using the individual stock's correlation, annual standard deviations, and weights. Fill in cells 019 through 038 by repeating this calculation for the given weights in the table. 9. In cells P18 through P38, calculate the portfolio standard deviations that correspond to the portfolio variances you found in the previous step. 10. In cells Q18 through Q38, calculate the Sharpe ratio of each portfolio in the table. Observe the maximum Sharpe ratio in the table, and examine the included graph. You should see that the point of tangency in the graph has nearly the same return and risk and as the portfolio with the maximum Sharpe ratio from your table. 1. In cell F19, enter the ticker for Home Depot ("HD"). After entering the ticker, access the Data tab and define this cell as a Stocks data type. 2. In cell G21, use the STOCKHISTORY function to display the close date and monthly closing prices for Home Depot. Other than the first input for the STOCKHISTORY function (the reference to your new stock ticker), all other inputs should be exactly the same as the example command provided in cell E21. This will autofill cells G23:H143 with historical monthly stock prices. 3. In cells J24 through J143, calculate the return each month for stock 2. 4. In cell G19, calculate the stock's average monthly return over the last 10 years using the AVERAGE function, and in cell H19, calculate the effective annual rate (EAR) of return over the time period using the average monthly return from G19. 5. In cell I19, calculate the sample standard deviation (using the STDEV.S function) of the stock's monthly returns. In cell J19, annualize this value by multiplying 119 by the square root of 12 . 6. In cell B24, calculate the correlation between the monthly returns of the two stocks using the CORREL function. 7. In cell N18, calculate the return of a portfolio with 0% weight in MCD and 100% weight in HD using the EAR calculated in step 4. Fill in cells N19 through N38 by repeating this calculation for the given weights in the table. 8. In cell 018 , calculate the variance of a portfolio with 0% weight in MCD and 100% weight in HD using the individual stock's correlation, annual standard deviations, and weights. Fill in cells 019 through 038 by repeating this calculation for the given weights in the table. 9. In cells P18 through P38, calculate the portfolio standard deviations that correspond to the portfolio variances you found in the previous step. 10. In cells Q18 through Q38, calculate the Sharpe ratio of each portfolio in the table. Observe the maximum Sharpe ratio in the table, and examine the included graph. You should see that the point of tangency in the graph has nearly the same return and risk and as the portfolio with the maximum Sharpe ratio from your table
