Reference no: EM133193230

Excel Exercise

Use the Excel spreadsheet posted on the Canvas site as a template for completing this exercise.

Objective

Diversification is the risk reduction that occurs from creating portfolios of investments with less than perfect correlation. Diversification results when the unique or unsystematic risk of one investment offsets the unique or unsystematic risk of another investment. We should be able to identify the benefit of diversification by comparing the risk reduction that occurs from combing two investments in common stock that are highly correlated with the risk reduction that occurs from combining two investments in common stock that are less correlated.

For this exercise you will use the monthly returns from owning Apple Computer (AAPL), Walmart (WMT) and Microsoft (MSFT) common stock. Intuitively it seems that Apple and Microsoft would share more risks than Apple and Walmart. If this is true, the correlation coefficient between Apple and Microsoft should be higher than the correlation coefficient between Apple and Walmart.

1 -In the posted template you should calculate the average monthly return, median monthly return, standard deviation of monthly returns, and variance of monthly returns for each of the three stock investments.

I have also included the daily returns for a portfolio that is invested in equal dollar amounts of Apple & Walmart and the daily returns for a portfolio that is invested in equal dollar amounts of Apple & Microsoft each month.

2 - In the posted template you should calculate the average monthly return, median monthly return, standard deviation of monthly returns, and variance of monthly returns for each of these two portfolios.

If there is no reduction of risk from diversification the average standard deviation of the two stocks should be the same as the standard deviation of the portfolio combining the two stocks.

3 - Use the average standard deviation of the returns of the pairs of stock investments compared to the actual computed standard deviation (not the analytical standard deviation) of each of the pairs of stocks to measure the percentage reduction in risk achieved through diversification.

4 - Use Excel to calculate the correlation coefficient (=CORREL(array 1, array2)) between Apple and Walmart and the correlation coefficient between Apple and Microsoft.

5 - Using your calculated correlation coefficients, variances and standard deviations I have entered the formula found on page 377 in Chapter 12 of the your textbook to calculate the Analytical Variance of these two portfolios. Use this value to calculate the Analytical Standard Deviation and compare it to the actual standard deviation you calculated in #2 above.

Attachment:- Template-for-Excel-Exercise-Correlations-and-Risk.rar