please answer the file add excel with formula and provide references
Risk Management” Standard deviation and Beta .
Problem: 1 Calculate Standard Deviation for S&P 500 and the four funds listed : 40 points
Problem: 2 Calculate Beta for the four funds 40 points
PROVIDE EXCEL FILE
Percent (%) changes in values in years 2003 through 2012 |
|
| Fund-1 | Fund-2 | Fund-3 | Fund-4 |
year |
| S&P index | small cap stock fund | Large cap Bond fund | Large cap stock fund | Hybrid Fund : Includes Both stocks and bonds |
1 | 2003 | 28.39 | 46.44 | 3.72 | 30.26 | 10.77 |
2 | 2004 | 8.75 | 15.82 | 1.6 | 13.7 | 6.01 |
3 | 2005 | 3.06 | 3.15 | -3.39 | 6.37 | 3.23 |
4 | 2006 | 13.64 | 16.82 | -0.96 | 14.1 | 7.93 |
5 | 2007 | 3.33 | -1.73 | -0.08 | -0.74 | 6.7 |
6 | 2008 | -37.89 | -34.08 | -5.57 | -34.79 | -10.53 |
7 | 2009 | 22.52 | 23.46 | 3.43 | 15.29 | 12.08 |
8 | 2010 | 12.93 | 25.3 | 4.11 | 13.82 | 9.22 |
9 | 2011 | -0.09 | -5.5 | 4.9 | 3.23 | 3.77 |
10 | 2012 | 12.5 | 18.04 | 5.96 | 19.12 | 6.54 |
|
|
|
|
|
| |
|
|
|
|
|
| |
Problem 1 | Standard Dev | ? | ? | ? | ? | ? |
Problem 2 | BETA | Base line | ? | ? | ? | ? |
Problem 3: For each scenario explain, based on the data above and risk parameters assessment (STD deviation and beta) which funds would you recommend and explain why? 40 points
Jack would like to make on average 8 % return but is uncomfortable with major drops. Any drop over 15 % in one year makes him nervous.
Jill is very nervous investor and even a 10 % drop makes her very uncomfortable.
Jane is young and comfortable riding the market over next 30 years.