Final Exam REE6045 - Excel file

Question 1: Rule of 72

To complete this final examination, you will need to answer the 15 questions listed below. All questions must be answered in the Excel spreadsheet titled “Final Exam REE6045 – Excel file” that is provided to you. Moreover, your answers must fit within the designated space (highlighted in yellow) for each answer within each of the Excel tabs.  

 1.  (5 points, 5*1)

a. According to the rule of 72, how many times your money will be doubled over a 36year period if it earns a rate of return of 8% per year? Show your work.

b. Using the rule of 72, estimate the value of an initial investment of $100K at the end of a 36-year period if it earns a rate of return of 8% per year? Show your work.

c. According to the 4% rule, what the size of your investment portfolio needs to be in order for you to withdraw an initial annual amount of $250K? Show your work.
d. Referring to part c, what will be your 3^rd annual withdrawal amount if the inflation rate during the 2 years since your initial withdrawal averaged 2% per year? Show your work.

2. (8 points, 4+4) Consider the “buy vs. rent” Excel spreadsheet provided to you in tab A2.

a. According to the assumptions made in that spreadsheet, should the average individual buy or rent? Briefly explain.

b. Using “Goal Seek” alter the “buy vs. rent” Excel spreadsheet so that it shows the minimum rate of price appreciation the homeowner must receive in order to be better off

buying than renting. Highlight in yellow the cell that includes this price appreciation rate.

3. (7 points, 4+3) Consider the levered DCF model provided to you in tab A3 of the Excel spreadsheet.

a. Calculate the maximum price that an investor with the assumptions made in the model should be willing to pay for that property. Show this maximum price in cell C8.

b. If you expect a general increase in the risk premium for real estate investments over the next 8 years, how would this affect the expected levered return on this property? Briefly Explain.  

4. (7 points, 3+2+2) Using the headers in tab A4 of the Excel spreadsheet and the input values included in the green “box”:

a. Create a monthly amortization schedule for a fixed rate 15-year mortgage. Make sure that any value that the user (me in this case) changes in the green “box” will be reflected in

Question 2: Buy vs. Rent Analysis

your amortization schedule. Your monthly payment calculation should be included in cell J5 and should also automatically change with any changes to the values in the green

“box”.  

b. Next to the amortization schedule, create an Excel graph that shows the remaining mortgage balance overtime. Make sure that your graph is labeled appropriately.

c. Include a vlookup function in cell L7 that will reflect the remaining mortgage balance associated with the end of the month entered by the user into cell J7. For example, if the

user enters 34 into cell J7, cell L7 will automatically show the remaining mortgage balance after 34 months.

5. (8 points, 0.5*16) Calculate the nominal and real annual rate of housing price appreciation rate for Miami and for Houston for the requested different time periods using the

housing price index data and the inflation index data provided to you in tab A5 of the Excel spreadsheet. In total, you need to calculate 16 values that will appear in the 16 yellow

cells of that Excel tab.

6. (6 points, 3+3) Given your results from the previous problem:

a. What can you say about the magnitude and the volatility of housing price appreciation in the short, medium and long run?

b. Are the results from question 5 consistent with the theory of price appreciation we discussed in the beginning of this course? Briefly explain.

7. (8 points, 4+4) Consider a REIT that holds high quality office buildings in some of the best locations in the US. The REIT is currently traded at a price of $64/share and there

are 130 million shares outstanding. Using the information below answer the following questions:

Expected next year total revenue: $750M

Expected next year total expenses (including interest and depreciation): $380M

Expected next year depreciation: $90M

Expected next year interest: $70M

Total debt:  $1.6B

Current office CAP in the US: 4.5% to 6.0% depending on quality and location.

a. What is your estimation for a fair market value for a share of the REIT described? Show your work!

b. What is your estimation for a fair price to pay for a share of the REIT described, if you require a 7.5% rate of return on an unlevered basis and expect the REIT to increase NOI

at an average rate of 2.5%? Should you buy shares of that REIT? Show your work!

Question 3: DCF Modeling

8. (6 points) 4 years ago you bought a home for $350,000, which at the time was 4.5% below the home’s fair market value. Since then, the market value of your home appreciated

at a compounded rate of 2.3% annually, on average. What is your current equity in the home if you were to sell it at a fair market value today? Assume that when you purchased

the home you took a $270,000, 4.5%, interest-only mortgage and always made the minimum required payments.

9. (7 points, 1*7) For each of the factors listed below indicate whether the factor, independently, is likely to cause a particular income producing property to trade for a lower or

higher CAP rate compared with an average property. For this question, no explanation is needed. Indicating “higher”, “lower” or “irrelevant” for factors a through g is sufficient.

a. Lower volatility in rent prices and occupancy rates.  

b. Worse location

c. High inflation environment

d. High risk premium environment

e. Market general higher than normal expected NOI growth

f. Lower construction quality

g. High quality tenants  

10. (8 points, 4+4) Consider an income producing property that according to your assumptions and estimations is currently worth $4M on an unlevered basis when a 7.5% required

rate of return is applied. One of the assumptions that you have made when arriving at that estimate is that you will sell the property in 6 years for a CAP of 8%, which translates to

$4.4M at that future point in time.

a. At what price will you sell the property in 6 years if all your assumptions materialized except that you will sell the property for a CAP of 7% instead of 8%? Show your

calculations.

b. All other things equal, by how much the situation described in part a affects the current value of the property. Show your calculations.   

11. (6 points, 2*3) What is likely to happen to the average CAP rate in the market under each of the following conditions. Briefly explain.

a. Rising interest rate environment.  

b. Growing perception that real estate is becoming riskier than previously viewed.

c. Expectations that future inflation will increase

12. (6 points, 2*3) Real estate market inefficiency:

a. Briefly describe 5 factors that cause the stock market to be more efficient than the real estate market.   

b. Can investors make money in an efficient market? Briefly explain.

c. Do educated and informed investors rather operate in an efficient or inefficient market? Briefly explain.

13. (7 points, 3+2+2) DCR:

a. Calculate the DCR for an income producing property to be acquired at a price of $7M and a CAP of 5.5%. The down payment on the property is 30% of the property value and

the mortgage on the remaining balance is a fixed-rate interest only loan at a rate of 4%.

b. What is the meaning of a DCR of 1.40, for example? Please explain.

c. List and briefly explain three different factors that are likely to cause the lender to require a higher DCR from investors?

14. (6 points, 2*3) Four years ago, when you were 24, you graduated from college and landed a good paying job. At that time you purchased your “starter home” for $200K. Since

then, the housing market in the city where your home is located experienced unusually high rate of price appreciation and a local real estate agent informed you that if you were to

put your home on the market today, you will be able to sell it for about $350K.

a. Did the recent abnormal housing price appreciation benefited you? Explain in 3-4 sentences.

b. What kind of individuals benefited the most from the recent price appreciation described in this question? Explain in 2-3 sentences.

c. What kind of individuals suffered the most from the recent price appreciation described in this question? Explain in 2-3 sentences.

15.(5 points) Which single real estate topic covered in this course you found to be most interesting and/or informative? Please explain why in a couple sentences. Is there a particular topic we covered that you found irrelevant? Is there a particular topic that wasn’t covered, which you expected to be covered in this intro course? Please explain.