Details of this Paper

Basic Data Manipulation and Computation Exercises




Question;1. Obtain quarterly S&P500. Go to to download the monthly index for the period of 12/30/1977 ? 12/31/2007. After downloading the data to Excel, keep only the dates and the adjusted closing (far right column). Download the data to a spreadsheet and then sort the data series by Date from oldest to newest. To convert the monthly S&P index to quarterly, you can keep data points for the months of January, April, July, and October. Delete all other months for each year. Note the data points are for the first trading day of each month, so Q1 (first quarter) is Jan-April, Q2 is April ? July, and so on. Save the resulted quarterly index.2. Obtain NCREIF real estate data. Go to, this is the quarterly returns of the national real estate market. Download data for 1978Q1 ? 2007Q4 to a separate spreadsheet. Put the data into a single column, each corresponding to its quarter-year (The data now should in the same layout like the S&P500 you did in step 1. Note that this is a return series, to convert it into an index, assume the index is 100 on 01/01/1978, then the index for 1978Q1 is 100*(1+2.9%)=102.9, for 1978Q2 is 102.9*(1+3.07%)=106.06, for 1978Q3 is 106.06*(1+3.39%)=109.65, so on and so forth.3. Using the quarterly indexes of S&P500 and NCREIF, compute historic return assuming these different holding periods: 1 qtr, 4 qtrs, 8 qtrs, 12 qtrs, 16 qtrs, 20qtrs, 24 qtrs, 28 qtrs, and 32 qtrs. Now you shall have a return series for each of these holding periods.4. Compute the mean and variance of the return series for each holding-period.5. Dividing the means, variances obtained in step 4 by their corresponding holding periods (number of quarters) to obtain these measures on a per-quarter basis. Take the square root of the variance per quarter to obtain standard deviation per quarter.6. Compute the Sharpe ratio based on numbers of Step 5, assuming the quarterly risk-free rate is 0.57%.7. Plot the Sharpe ratios of S&P and NCREIF into a column chart in which the X-axis is the holding period (quarters) and for each holding period (1, 4, 8, 12, 16, 20, 24, 28, 32 quarters) show the two Sharpe ratios in side-by-side columns.8. Make any charts you deem interesting for comparison purposes, and briefly comment on your findings with regard to how the returns and risks of the two assets behave under different holding period scenarios.


Paper#48409 | Written in 18-Jul-2015

Price : $32