Reference no: EM131695167
Assignment
Refer to the S&P 500 data below and copy into an Excel spreadsheet:
Date Price
|
Date
|
Price
|
|
|
|
| 1-Jan-17 |
2,275.32
|
| 1-Jan-16 |
1,918.60
|
| 1-Jan-15 |
2,028.18
|
| 1-Jan-14 |
1,822.36
|
| 1-Jan-13 |
1,480.40
|
| 1-Jan-12 |
1,300.58
|
| 1-Jan-11 |
1,282.62
|
| 1-Jan-10 |
1,123.58
|
| 1-Jan-09 |
865.58
|
| 1-Jan-08 |
1,378.76
|
| 1-Jan-07 |
1,424.16
|
1. Compute the simple holding period returns without the dividend. Compute the arithmetic mean holding period return.
2. The geometric mean is the nth root of the product of a series. It won't work with negative numbers. Using the simple holding period returns from above, create a column that adds 1 to each return. Use the "=GEOMEAN" function in Excel to compute the geometric mean for that column. Subtract one and you have the geometric mean return.
3. Compute the continuously compounded return and the average return using logs (see online lectures). What do you notice about the continuously compounded average return and the geometric average return versus the arithmetic average return?
For the next 11questions, you will need to collect some data and transfer them to a spreadsheet. Get the year-end (or Jan 1) prices for your stock (the company you have been following) for the last 20 years or the life of the company whichever is shorter. Get the year-end value of the Dow Jones Industrial Average for the same period. Yahoo Finance has what you need for both. Get the BofA Merrill Lynch US Corp AAA Bond Total Return Index Value and the 3-month Treasury bill rate for the same period. These can be found at the Federal Reserve website: Be nice to FRED, he's a friend.
4. Find the continuously compounded annual returns for each asset. T-bills are already listed as returns so don't bother with them.
5. Find the descriptive statistics for each asset return.
6. Are the means and medians the same for each asset? If not, what may account for the difference?
7. What were the returns for the best and worst years for each asset?
8. Create a histogram for each asset using six, equally-spaced bins with three above the mean and three below.
9. Comment about the return distribution and how it relates to a normal distribution for each of the four assets. Make sure you discuss your histogram as well as skewness and kurtosis in your comments.
10. Using Chebyshev's inequality and a little algebra (find k) and a little arithmetic and your knowledge of the definition of the standard deviation, what would be the upper and lower values that would contain 2/3 of the returns for each asset.
11. Find the Sharpe ratio for each asset. Use the 3-month Treasury bill rate as the risk-free interest rate.
12. Find the coefficient of variation for each asset.
13. Comment about the risk/reward of each asset. How did your stock compare to the other assets?
14. How do the skewness and range of each asset relate to risk reward?