Problem Set 1: Due April 18, 2016 at the beginning of class, late assignments are not accepted. YOU SHOULD TURN IN ONE HARD COPY PER GROUP AND EACH MEMBER SHOULD HAVE A COPY TO FOLLOW ALONG IN CLASS AS WE GO OVER THE HIGHLIGHTS.
This should be done in groups of up to 4 students (3 is ideal). Please put all group member names on the first page. You should have the same groups for the problem sets and the group project. Please turn in a neat, typed, easy to follow set of answers. When I ask for explanations or comments, only brief explanations are necessary. This problem set is worth 40 points total, with the points listed in parentheses.
Data380ps1.XLS is available from Canvas. This spreadsheet contains monthly returns for ten Vanguard index funds and five actively managed portfolios from January 2011 to December 2015. The units are in decimals. If the return is -0.00123, this means -0.123%.
For your convenience, here are some examples of useful Excel commands (An = sign always precedes a command in Excel):
=A2*B2 Multiplies cell A2 and B2
=A2^.2 Calculates the Fifth Root of A2(Raises A2 to the 1/5 power)
=1+A2 Adds 1 to A2
=AVERAGE(A2:A10) Calculates the sample mean of rows A2 through A10
=STDEV(A2:A10) Calculates the sample standard deviation (A2-A10)
=PRODUCT(A1:A10) Multiplies all the cells A1-A10
For the ten Vanguard index funds and for each actively managed mystery portfolio (i.e., portfolio A, B, C, D, E). Note the full names of the Vanguard index funds are included in the second row below the ticker symbols:
1. Calculate the returns requested in i) and ii) below for all 15 of the funds.
i) The net holding period return for an investor who bought the fund on January 1, 2011 and sold it on December 31, 2015.
ii) The annualized return (also known as the geometric average annual return) over the 5 year period from January 2011 to December 2015.
Label your answers with the name of the Vanguard/portfolio A through E. Do not print out your entire spreadsheet. Briefly explain the steps/Excel commands you used to arrive at your answer.
2. Complete this exercise for only one fund of your choice and for the Vanguard Index 500 (on the same graph):
You purchased $10,000 worth of the fund on January 1, 2011. Calculate and plot the dollar value of your investment over the period. (Don’t just calculate the annual return for the five years and plot five points. You should basically have a continuous line connecting 60 data points. For an example see the graph “Growth of Hypothetical $10,000” available at:
https://fundresearch.fidelity.com/mutual-funds/summary/316184100
Also see sample calculations in first lecture notes and second daily assignment.) Include your graph in your completed problem set answers.
3. Calculate and report the annualized sample mean return and sample standard deviation over the 5 year sample period. In this case you should annualize the mean and standard deviation of the monthly returns by multiplying the monthly mean return by 12 and the monthly standard deviation by the square root of 12. Again label your answers with the Index or portfolio name.
4. Calculate and report the pairwise correlations for all 15 of the funds. Include labels. (This can all be done in one step using all the monthly returns. Go to Tools/Data Analysis/Correlation and choose all the data. If Data Analysis does not appear under the Tools menu, this means that you must add it in under “Add Ins.” It is called “Analysis Toolpack”. Then you need to copy over the fund labels to replace the generic labels.)
5. Using the historical annualized mean and standard deviation in number 3, and the pairwise correlation from number 4, graph the investment opportunity set of portfolios combining the Vanguard Short Term Bond index and the Vanguard Small Cap Stock index on an expected return-standard deviation graph like the one on in Figure 6.4 in the text (in this example the relevant opportunity set curve is the one associated with the correlation equal to 0.2). You should be using Excel to make this graph. Note that you will be using historical data on these two portfolios to obtain estimates of expected return, expected standard deviation, and correlation.
6. Assume that monthly returns on these 15 portfolios are drawn from normal distributions. What is the worst return you’re likely to see in a one year holding period with 95% confidence for each index and actively managed fund?
7. Although I have not told you what type of actively managed fund portfolio A through E in the spreadsheet are, you should be able to tell with all the statistics you generated. Determine what type of fund (e.g., large cap domestic stocks...) they are and briefly describe how you arrived at your answer. What do you suspect about Portfolio E and how did you arrive at this?
8. Compare statistics for the Vanguard 500 Index (e.g., using estimates produced for questions above) from other historical periods listed in your text page 138 in Table. List out all the statistics in Table 5.4 and include estimates from 2011 to 2015. Note that the return on t-bills over this period is effectively 0. Does 2011 to 2015 look like a typical period? Why or why not?
Attachment:- data380ps1.xlsx