Please use the excel template sheet with all the data here: https://drive.google
ID: 2753067 • Letter: P
Question
Please use the excel template sheet with all the data here:
https://drive.google.com/file/d/0BzawUzMQcadNMU9CNXRtam5LNXc/view?usp=sharingObjective
or if that doesnt work try here:
http://www66.zippyshare.com/v/iRIU1hbo/file.html
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 daily returns from owning Apple Computer (AAPL), Google (GOOG) and Walmart (WMT) over the calendar year of 2011. Intuitively it seems that Apple and Google would share more risks than Apple and Walmart. If this is true the correlation coefficient between Apple and Google should be higher than the correlation coefficient between Apple and Walmart.
1 -In the posted template you should calculate the average daily return, median daily return, standard deviation of daily returns, and variance of daily 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 & Google.
2 - In the posted template you should calculate the average daily return, median daily return, standard deviation of daily returns, and variance of daily 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 compared to the actual standard deviation of each of the pairs of stocks to measure the percentage reduction in risk achieved through diversification.
Use Excel to calculate the correlation coefficient between Apple and Walmart and the correlation coefficient between Apple and Google.
4 - Using the correlation coefficient and the formula for the “analytical variance” of the combination of 50% weights in each of the stocks found on page 31 of the Risk & Return Notes, calculate the “analytical” variance and standard deviation of the portfolios.
5 - Comment on whether or not the risk reduction is consistent with the measures correlation coefficients.
Explanation / Answer
Name & Student #: Correlation and Risk Reduction #1 AAPL WMT GOOG #2 AAPL & WMT AAPL & GOOG Average Daily Return 0.0957% 0.0418% 0.0435% Average Daily Return 0.07% 0.07% Median Daily Return 0.1008% 0.1048% 0.0481% Median Daily Return 0.10% 0.07% Std Deviation of Daily Return 1.6521% 1.0568% 1.8583% Std Deviation of Daily Return 0.42% 0.15% Variance of Daily Return 0.0273% 0.0112% 0.0345% Variance of Daily Return 0.00% 0.00% Annual Return 22.89% 9.53% 6.88% Annual Return 16.21% 14.88% Correlation Coefficient Between AAPL & WMT 35.810% Analytical Variance 6.12 #4 2.09 #4 Analytical Standard Deviation 2.47 1.45 Correlation Coefficient Between AAPL & GOOG 62.714% Average Standard Deviation 1.35% 1.76% #5 Average Return 16.21% 14.88% Comment on Risk Reduction & Correlation: AAPL and GOOG is better than the other portfolio. Risk Reduction as % of Average Std Dev 8.356% #3 11.795%