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” link at the bottom of the page. Also download the dividends for this period (the dividends are in a separate file). (a) Create a graph of the Close price of SPY (not the adjusted close price) as a function of time. Label the axes and give it a title (e. G. SPY). This is simply a graph using the data you have downloaded.

CLOSING PRICE (LED) TIME (year) Figure 1: No Excel functions beyond graphics functions were used to make this graph. I (b) Create a graph Of the SPY returns as a function Of time using the Close price and dividends. Begin by adding a “dividends” column to assign dividends for each date: PLOCK() may be helpful here. Gore dates With no dividends, a #N/A Will likely appear. You can eliminate the #N/A with the IF() and SINS() functions. In the next column calculate the returns including the dividends. Graph these results. Label the graph.

To calculate the return we use the equation from the lecture slides * ) – x(t) * income – costs which for our problem becomes x(t+ 1) -x(t) * dividend during period (t) Now while this equation is intuitive for the returns over a year, month or week (e. G. The return for the year 2001 begins on January 1, 2001), it is less intuitive for the daily close-to-close data of this problem. Some students effectively changed this equation by replacing r(t) with r(t For purposes of this problem set this change is acceptable. RETURN -10 Figure 2: The VILLOUS() function was used With the date as a key to align the dividends with the appropriate dates.

The ASIAN function was used together with the function to filter the #WAs returned by PLOUGH. C) Create a graph of the cumulative distribution function (CDC and 1 CDC) of the SPY returns, Copy the values return data to the next column using the “Copy’ & “Paste Special” commands so that on the return values are copied. Order the returns using the “Sore command. In the column to the right of the ordered returns calculate the CDC using CDC(xx ) – (Ink*l) where k is the running index of the return k = I . N and N is the number of returns. The COUNT() function is useful for calculating k.

In the column to the right of your CDC results, use the tenement to create a column of CA) and I-CDC depending on whether the return is negative. Graph these results with a linear x-axis and a logarithmic y- axis. Label the graph. Cog, I-CDC 10-1 RETURN (96) Figure 3: The COUNT() function was used to generate the running index k. (d) Add a Gaussian distribution to the graph of the CDC of the SPY returns. Begin by calculating the arena AVERAGE() and standard deviation of the ordered returns. Highlight these in your homework so your GIS can see them.

Else them to calculate the cumulative Gaussian distribution (INNERMOST() and 1 – INNERMOST()) in the column next to your last cumulative distribution calculation, Make a copy of the graph from the part (LLC) above and add the cumulative Gaussian distribution to the copy. 100 Gaussian I-CDC Figure 4: The INNERMOST() function with a mean of. 0425 and a standard deviation of I . 2046 was used to generate the Gaussian distribution. 2. The return profile and risk Of the shares Year Treasury Bond ET (TTL). In this exercise you will generate the graphs presented in class for a bond index.

You Will use the infrastructure you developed above in problem (I) for this exercise: the only difference is the data. Each part of the exercise is a repeat of what we did above with the SPY data. Go to Yahoo Finance (finance. Yahoo. Com) and search for the ticker symbol TTL, 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 07/30/2002 to 09/02/2014. Also download the dividends for this period (the dividends are in a separate file). (a) Create a graph of the Close price total as a function to time.

Label the axes and give it a title (e. . T L T). This is simply a graph using the data you have downloaded. CLOSING PRICE (USED) 08 10 12 14 Figure 5: No Excel functions beyond graphics functions were used to make this graph. (b) Create a graph Of the T ALT returns as a function Of time using the Close price and dividends. TO calculate the return we use the equation from the lecture slides * ) -? x(t) * income -? costs 1) – x(t) * dividend during period Figure 6: The VOLITION() function was used with the date as a key to align the dividends With the appropriate dates.

The ASIAN function was used together With he IF() function filter the #N/As returned by PLOUGH. (c) Create a graph of the CDC of the TTL returns. 10-2 Figure 7: The COUNT() function was used to generate the running index k. (d) Add a Gaussian distribution to the graph of the CDC of the TTL returns cog, I-CNN Figure 8: The INNERMOST() function with a mean of. 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 (b) and (b). State which index is riskier and explain why.

SPY is riskier than TTL 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 (1 d) and (ad). State which index is riskier and explain why. TTL data TTL Gaussian spy data SPY Gaussian cop, I-CDC Figure 9: The SPY is riskier than the TTL because it usually has a wider distribution; both empirically and in the Gaussian approximation. Note: a graph was not requested for this problem in the problem set.