Email a copy prior to the start of class. Hold on to a copy to use as we discuss the answers in class.
- Use the NCREIF database to get the historical quarterly returns for the major property types (office, retail, industrial and apartment). That is, the returns from the 1st quarter of 1978 through the current quarter.
- What has the average quarterly time weighted return been for each property type (office, retail, industrial, apartment) since the beginning of the index? (Use the geometric mean approach discussed in class.)
- What is the quarterly standard deviation of these returns? Note that your answer may differ slightly depending on whether you use the population or sample standard deviation function in Excel. You can use either one at this point. We can discuss the difference in class.
- Plot the average return and standard deviation from parts a and b for the four property types on a graph. (Returns on the vertical axis and standard deviation on the horizontal axis.) Note that you will have four points on the graph, one for each property type. Is there a property type that you think performed the best considering risk and return? Is there a property type that you think performed the worst considering risk and return?
- What is the correlation between the retail and warehouse indices. (Calculate the correlation coefficient – no graph needed.) How has the correlation changed if you just use the past 5 years of data?
- Returns for a fund and a benchmark for the past 20 years are as follows:
The spreadsheet Fund_Returns.xls on Canvas has the above returns.
Assume the risk-free rate over the same time period was 2% each year.
What is the average annual return (geometric mean) for the fund?
- What is the average annual return (geometric mean) for the benchmark?
- What is the beta of the fund? (Hint: Do a simple linear regression in Excel. Or just create a trend line and have the formula displayed to get the beta. Be sure that the fund return is the dependent variable and on the Y axis and the benchmark the independent variable on the Y axis.)
- Based on the fund beta, what rate of return would you have expected the fund to earn each year using the capital asset pricing model?
- On average, do you think the fund earned an excess return most years after adjusting for risk?
- Use the three asset (stocks, bonds and real estate) Excel portfolio optimizer on Blackboard that was illustrated in class:
Assume that you want to construct a portfolio that consists of stocks, bonds and real estate. You found that the historical correlations between these asset classes were as follows:
Real Estate Bonds Stocks
Based on recent performance of each asset class, you think the following estimates of the expected (mean) return and standard deviation are reasonable:
Construct an efficient frontier for these three asset classes.
- What (approximately) is the lowest risk (standard deviation) that can be achieved?
- What is the expected return for the lowest level of risk?
- How much of the portfolio is in stocks, bonds and real estate at this level of return and risk?
- Supposed your target return is 10%. What is the level of risk? What are the weights between stocks, bonds and real estate?