Assume that your cousin holds just one stock, Eastman Chemical Bonding (ECB), which he thinks has very little risk. You agree that the stock is relatively safe, but you want to demonstrate that his risk would be even lower if he were more diversified. You obtain the following returns data for Wilder's Creations and Buildings (WCB). Both companies have had less variability than most other stocks over the past 5 years. Using the standard deviation of returns, by how much would your cousin's risk have been reduced if he had held a portfolio consisting of 50% in ECB and the remainder in WCB?
|
Year |
ECB |
WCB |
|
|
2007 |
40.00% |
40.00% |
|
|
2008 |
-10.00% |
15.00% |
|
|
2009 |
35.00% |
-5.00% |
|
|
2010 |
-5.00% |
-10.00% |
|
|
2011 |
15.00% |
35.00% |
*Show your computations and/or excel functions you used in your solution
Using excel formula and functions to calculate standard
deviation of individual stocks
| A | B | C | |
| 1 | Year | ECB | WCB |
| 2 | 2007 | 40.00% | 40.00% |
| 3 | 2008 | -10.00% | 15.00% |
| 4 | 2009 | 35.00% | -5.00% |
| 5 | 2010 | -5.00% | -10.00% |
| 6 | 2011 | 15.00% | 35.00% |
| Standard Deviation | 22.638% | 22.638% | |
| Excel Formula | STDEV.S(C2:C6) | STDEV.S(B2:B6) |
Using excel functions to calculate standard deviation of portfolio
| A | B | C | D | ||
| 1 | Year | ECB | WCB | Portfolio Return | Excel Formula |
| 2 | 2007 | 40.00% | 40.00% | 40.0% | 50%*B2+50%*C2 |
| 3 | 2008 | -10.00% | 15.00% | 2.5% | 50%*B3+50%*C3 |
| 4 | 2009 | 35.00% | -5.00% | 15.0% | 50%*B4+50%*C4 |
| 5 | 2010 | -5.00% | -10.00% | -7.5% | 50%*B5+50%*C5 |
| 6 | 2011 | 15.00% | 35.00% | 25.0% | 50%*B6+50%*C6 |
| Standard Deviation | 18.625% | ||||
| Excel Formula | STDEV.S(D2:D6) | ||||
The Cousin's risk would be reduced by =22.638%-18.625%
=4.01%
Get Answers For Free
Most questions answered within 1 hours.