ProblemSet1 Solutions v1
Problem Set #1
Due Date: September 11, 2014
1. The return profile and risk of the S&P 500. In this exercise you will reproduce the graphs presented in class. The goal of this exercise is (i) to expand your datahandling skills, (ii) test your understanding of basic probability concepts using real data and (iii) develop an appreciation for the use of replicating a result to ensure that you understand it.
Go to Yahoo Finance (finance.yahoo.com) and search for the ticker symbol SPY. On the left-hand side of the page you will see a link to “Historical Prices”. Click on the link to get to the Historical Prices page and download the daily prices from 01/29/1993 to 08/28/2014. You will find a “Download to Spreadsheet” …show more content…
Figure 6: The VLOOKUP() function was used with the date as a key to align the dividends with the appropriate dates. The ISNA() function was used together with the IF() functionto filter the #N/As returned by VLOOKUP().
(c) Create a graph of the CDF of the TLT returns.
Figure 7: The COUNT() function was used to generate the running index k.
(d) Add a Gaussian distribution to the graph of the CDF of the TLT returns
Figure 8: The NORMDIST() function with a mean of 0.0318 and a standard deviation of 0.8705 was used to generate the Gaussian distribution.
3. Risk Analysis:
(a) Compare the time-series of return shown in the graphs generated above in items
(1b) and (2b). State which index is riskier and explain why.
SPY is riskier than TLT from this perspective because the SPY return series has a greater dynamic range (i.e. difference between maximum and minimum) of returns.
(b) Compare the cumulative distribution functions shown in the graphs generated above in items (1d) and (2d). State which index is riskier and explain why.