Financial Economics: Problem Set
Econ 136: Financial Economics
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” 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.
225
CLOSING PRICE (USD)
200
175
150
125
100
75
50
25
0
90
95
00
05
10
15
TIME (year)
Figure 1: No Excel functions beyond graphics functions were used to make this graph.
1
(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: VLOOKUP() may be helpful here. For dates with no dividends, a #N/A will likely appear. You can eliminate the #N/A with the
IF() and ISNA() 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 + τ ) − x(t) + income − costs x(t) which for our problem becomes x(t + τ ) − x(t) + dividend during period r(t) = x(t) r(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
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” 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.
225
CLOSING PRICE (USD)
200
175
150
125
100
75
50
25
0
90
95
00
05
10
15
TIME (year)
Figure 1: No Excel functions beyond graphics functions were used to make this graph.
1
(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: VLOOKUP() may be helpful here. For dates with no dividends, a #N/A will likely appear. You can eliminate the #N/A with the
IF() and ISNA() 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 + τ ) − x(t) + income − costs x(t) which for our problem becomes x(t + τ ) − x(t) + dividend during period r(t) = x(t) r(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