Excel HW final

To complete this final examination you will need to answer the 15 questions listed below. The first four questions are to be answered in Excel using the associated tabs included in the “Final Exam REE6045 – Excel file” that is provided to you. Questions 5-15 are to be answered on a Word document in the order in which they appear.

This final exam is due on or before March 3rd at 12 p.m. EST. By that time you should submit an electronic copy (via email) of your assignment. If you turn your assignment late, 10% will be deducted from your grade for every calendar-day delay.

All the work on this final exam must be 100% your own work. You are not allowed to discuss the exam with any of your classmates or any other person prior to the deadline. A failing grade in the course will automatically be assign to a student that seeks help from another person while working on the exam. You are allowed, however, to use the textbook, classroom notes, review lectures and use any “non-interactive” websites while working on the exam.

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

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

   b. 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.

2. (8 points, 4+4) Consider the levered DCF model provided to you in tab Q2 of the Excel spreadsheet.

   1. Calculate the maximum price that an investors with the assumptions made in

      the model should be willing to pay for that property. Show this maximum

      price in cell C2.

   2. 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? Explain in 2-3 sentences and include your explanation in the yellow box of tab Q2.

3. (8 points, 4+2+2) Using the headers in tab Q3 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 your amortization schedule. Your monthly payment calculation should be included in cell J5 and should also change with any changes to the values in the green “box”.

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

2. 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.

1. (8 points, 0.5*16) Calculate the nominal and real annual rate of housing price appreciation rate for Las Vegas 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 Q4 of the Excel spreadsheet. In total, you need to calculate 16 values that will appear in the 16 yellow cells of that Excel tab.

2. (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 4 consistent with the theory of price appreciation

we discussed in the beginning of this course? Briefly explain.

1. (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 70 million shares outstanding. Using the information below answer the following questions:

   Expected next year total revenue: $580M
   Expected next year total expenses (including interest and depreciation): $320M Expected next year depreciation: $80M
   Expected next year interest: $60M
   Total debt: $2.0B
   Current office CAP in the US: 4.5% to 5.5% depending on quality and location.

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

   2. What is your estimation for a fair price to pay for the REIT described, if you require an 8.5% rate of return on an unlevered basis and expect the REIT to increase NOI at an average rate of 2%? Should you buy shares of that REIT? Show your work!

2. (5 points, 2+3) PLAM and ARM:

a. What is the main difference between a PLAM and an ARM? Briefly explain. b. Under which economic circumstances a PLAM type loan is greatly needed?

Briefly explain.

8. (7 points, 1*7) For each of the factors listed below indicate whether the factor, independently, is likely to increase or decrease the CAP rate on a particular income producing property compared with an average property. For this question, no explanation is needed. Indicating increase or decrease for factors a through g is sufficient.

1. Lower volatility in rent prices and occupancy rates.

2. Worse location

3. High inflation environment

4. High risk premium environment

5. High expected NOI growth

6. Lower construction quality

7. High quality tenants

1. (6
   assumptions and estimations is currently worth $4M on an unlevered basis when an 8.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.8M at that future point in time.

   1. 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 9% instead of

      8%? Show your calculations.

   2. All other things equal, by how much the situation described in part a affects

      the current value of the property. Show your calculations.

2. (6 points, 2*3) According to the Truth-in-Lending-Act (TILA) lenders must provide borrowers with an APR in addition to the interest rate to be charged on

the loan.

a. b. c.

What is the purpose of providing borrowers with an APR? Briefly explain. What is the main problem with APR? Briefly explain.
Consider a potential borrower who evaluates two mortgages options. One with higher upfront fees and the other with lower upfront fees, but the two options have the same APR. Which option would you advise the borrower to take if the borrower expects to stay in the home for a relatively short time period? Briefly explain.

points, 3+3) Consider an income producing property that according to your

11. (6
a. Briefly describe 5 factors that cause the stock market to be more efficient than

points, 3+3) Real estate market inefficiency:

the real estate market.

1. Can investors make money in an efficient market?

2. Do educated and informed investors rather operate in an efficient or

   inefficient market? Briefly Explain.

1. (6 points, 2*3) DCR:
   a. Calculate the DCR for an income producing property to be acquired at a price

   of $8M and a CAP of 6%. 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.30, for example? Please explain.
   c. List and briefly explain three different factors that are likely to cause the

   lender to require a higher DCR?

2. (6 points, 3+3) Cash-on-cash yield and positive vs. negative leverage:
   a. Calculate cash-on-cash yield on the property described in the previous

   question. Show your calculations.
   b. All other things equal, what is the “turning point” interest rate at which the

   property described in the previous question will no longer generate a positive leverage? In other words, what is the interest rate that any rate above that rate will yield a negative leverage? Show your work.

3. (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 $160K. 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 $280K.

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

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

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

4. (6 points) Which single real estate topic covered in this course you found to be most interesting and/or informative? Please explain why in 3-4 sentences.

Deliverables:
ONE Excel file named “FirstName_LastName_Final-Word” and ONE Word file named ”FirstName_LastName_Final-Excel”.