Eco 4000
Prof. Piskula
Dataset Creation Homework
to reproduce Table 1 in the paper, the data summary, on page 8. Note that the months involved are January 1994 through September 2000.Create an excel spreadsheet that has a column of months ranging from Jan 1994 to Sep 2000. Divide the risk free rates by 12 to get an adequate monthly number. Divide them again by 100 to match the format of the S&P and the hedge fund indexes. Align those numbers next to the dates.
Turn to the hedge fund data. We need the RORs not the NAVs. The left-most columns on your download are likely the Hedge Fund Index. This is what is labelled "Aggregate Hedge Fund Index" in the paper. Align that column with the dates. Then in the nearby columns find the RORs for the nine strategies and align them with the dates. It will be easier if you do this in the order that these strategies are listed in the paper, not the order they appear in the download. (We will not use the columns that may appear farther to the right in the download for some users.) The download contains sub-categories for the Event-Driven strategy (Distressed, Multi-Strategy, Risk Arbitrage) which will also will not use.
Turn to the S&P data. Use the One-Month Total Return data (the January 1994 value is 3.40%). Following the authors, subtract 20 basis points from all the monthly total return values from January 1994 to September 2000. This is a proxy for the expenses associated for mimicking an index. Recall that 20 basis points is one fifth of a percentage point. In excel this would be 0.002 or 0.2%. Because the 20 basis points is an annual number and we are using monthly data, we must actually subtract .002/12 from each monthly return. Align this adjusted S&P 500 data alongside the other data in your dataset.
We need the excess returns, not the absolute returns. We will do this by subtracting off the risk-free rate. First note that some values may be in a regular format, so something like 3.40 is simply a value of 3.40, and some values may be in a percentage format, so 3.40% may really have a cell value of .0340. You must get these into the same format, using your favorite method.
We need excess returns rather than absolute returns. Create a section of your worksheet, or perhaps a different sheet, and subtract the risk-free rate from all of the return values.When you do this calculation, remember to lock the column indicator for the risk-free rate with a $ sign in order to always refer to that column. Copy and paste these excess returns as values onto a new sheet, so that you are not working with formulas.
On the sheet with the excess returns, create a row for the average values for each strategy times 12. Compare this row to the column in exhibit 1 for annual excess return to insure you are on the right track.
Send in your spreadsheet with the excess returns. I will not be able to thoroughly check the detail on all of them, but I will do as many as I can.