Consider the buy vs. rent Excel spreadsheet provided to you intab Q1.a. According to the assumptions made in that spreadsheet should the averageindividual buy or rent? Briefly explain in 1 sentence.b. Alter the buy vs. rent Excel spreadsheet so that it shows the minimum rateof price appreciation the homeowner must receive in order to be better offbuying than renting. Highlight in yellow the cell that includes this priceappreciation rate.2. (8 points, 4+4) Consider the levered DCF model provided to you in tab Q2 of theExcel spreadsheet.a. Calculate the maximum price that an investors with the assumptions made inthe model should be willing to pay for that property. Show this maximumprice in cell C2.b. If you expect a general increase in the risk premium for real estateinvestments over the next 8 years, how would this affect the expected leveredreturn on this property? Explain in 2-3 sentences and include your explanationin the yellow box of tab Q2.3. (8 points, 4+2+2) Using the headers in tab Q3 of the Excel spreadsheet and theinput 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 greenbox will be reflected in your amortization schedule. Your monthly paymentcalculation should be included in cell J5 and should also change with anychanges to the values in the green box. b. Next to the amortization schedule, create an Excel graph that shows theremaining mortgage balance overtime. Make sure that your graph is labeledappropriately.c. Include a vlookup function in cell L7 that will reflect the remaining mortgagebalance 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 showthe remaining mortgage balance after 34 months.4. (8 points, 0.5*16) Calculate the nominal and real annual rate of housing priceappreciation rate for Las Vegas and for Houston for the requested different timeperiods using the housing price index data and the inflation index data provided toyou in tab Q4 of the Excel spreadsheet. In total, you need to calculate 16 valuesthat will appear in the 16 yellow cells of that Excel tab.5. (6 points, 3+3) Given your results from the previous problem:a. What can you say about the magnitude and the volatility of housing priceappreciation in the short, medium and long run?b. Are the results from question 4 consistent with the theory of price appreciationwe discussed in the beginning of this course? Briefly explain.6. (8 points, 4+4) Consider a REIT that holds high quality office buildings in someof 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 informationbelow answer the following questions:Expected next year total revenue: $580MExpected next year total expenses (including interest and depreciation): $320MExpected next year depreciation: $80MExpected next year interest: $60MTotal debt: $2.0BCurrent office CAP in the US: 4.5% to 5.5% depending on quality and location.a. What is your estimation for a fair market value for a share of the REITdescribed? Show your work!b. What is your estimation for a fair price to pay for the REIT described, if yourequire an 8.5% rate of return on an unlevered basis and expect the REIT toincrease NOI at an average rate of 2%? Should you buy shares of that REIT?Show your work!7. (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 particularincome producing property compared with an average property. For this question,no explanation is needed. Indicating increase or decrease for factors a through g issufficient.a. Lower volatility in rent prices and occupancy rates.b. Worse locationc. High inflation environmentd. High risk premium environmente. High expected NOI growthf. Lower construction qualityg. High quality tenants9. (6 points, 3+3) Consider an income producing property that according to yourassumptions and estimations is currently worth $4M on an unlevered basis whenan 8.5% required rate of return is applied. One of the assumptions that you havemade when arriving at that estimate is that you will sell the property in 6 years fora CAP of 8%, which translates to $4.8M at that future point in time.a. At what price will you sell the property in 6 years if all your assumptionsmaterialized except that you will sell the property for a CAP of 9% instead of8%? Show your calculations.b. All other things equal, by how much the situation described in part a affectsthe current value of the property. Show your calculations.10. (6 points, 2*3) According to the Truth-in-Lending-Act (TILA) lenders mustprovide borrowers with an APR in addition to the interest rate to be charged onthe loan.a. What is the purpose of providing borrowers with an APR? Briefly explain.b. What is the main problem with APR? Briefly explain.c. Consider a potential borrower who evaluates two mortgages options. One withhigher upfront fees and the other with lower upfront fees, but the two optionshave the same APR. Which option would you advise the borrower to take ifthe borrower expects to stay in the home for a relatively short time period?Briefly explain.11. (6 points, 3+3) Real estate market inefficiency:a. Briefly describe 5 factors that cause the stock market to be more efficient thanthe real estate market.b. Can investors make money in an efficient market?c. Do educated and informed investors rather operate in an efficient orinefficient market? Briefly Explain.12. (6 points, 2*3) DCR:a. Calculate the DCR for an income producing property to be acquired at a priceof $8M and a CAP of 6%. The down payment on the property is 30% of theproperty value and the mortgage on the remaining balance is a fixed-rateinterest 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 thelender to require a higher DCR?13. (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 previousquestion. Show your calculations.b. All other things equal, what is the turning point interest rate at which theproperty described in the previous question will no longer generate a positiveleverage? In other words, what is the interest rate that any rate above that ratewill yield a negative leverage? Show your work.14. (6 points, 2*3) Four years ago, when you were 24, you graduated from collegeand 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 locatedexperienced unusually high rate of price appreciation and a local real estate agentinformed you that if you were to put your home on the market today, you will beable to sell it for about $280K.a. Did the recent abnormal housing price appreciation benefited you? Explain in3-4 sentences.b. What kind of individuals benefited the most from the recent price appreciationdescribed in this question? Explain in 2-3 sentences.c. What kind of individuals suffered the most from the recent price appreciationdescribed in this question? Explain in 2-3 sentences.