Show all of your work for full points (and partial credit if you make mistakes). You may modify the Excel templates used in class to complete this assignment or develop your own, new pro forma. If you chose to use the template, make sure to customize it appropriately for this underwriting task.

You are embarking on your journey as a real estate investor and want to make your first investment. Since this is your first deal, you want to stick with what you know, so your target is a small-scale multifamily residential property. The target property contains 4 units and is located in Santa Clara, CA.

The property for sale contains an offering memorandum (OM) that outlines the revenue and expenses for owning the property. Assume you will hold the property for 5 years and the discount rate is 5.75%.

Your first due diligence task to assess the deal is to replicate the sellers underwriting in the OM. This will help you back-out the underlying assumptions.

• What are the underlying assumptions for rent growth, vacancy, expense growth, and NOI growth? (Note: each years growth rate might differ slightly because of rounding, but each value will be pretty close. Report the assumed growth rates rounded to 1 decimal place.)
• Build an amortization table based on the debt assumptions in the OM. Provide an assessment of the debt service coverage ratio (DSCR) for the sellers underwriting.
• Calculate the NPV and IRR of the deal using the given assumptions. Interpret your findings.

Now you must vet the assumptions and come up with your own estimates.

• Assess the rent growth assumption using data from Zillow and/or ApartmentList.com.

Find the relevant data, calculate rent growth, plot both rent level and rent growth over time, and fitting regression lines to the graphs. Explain your findings. What adjustments, if any, would you make to the rent growth assumptions?

• In a few sentences, provide a qualitative assessment of the vacancy and operating expense assumptions. Are they reasonable? Would you make any adjustments to these assumptions? Explain your reasoning.
• Use data from the American Community Survey Demographic Profile 03 (Eco-

nomic Characteristics) to assess the demographic data in the OM (page 32). Use data for the zip code (ZCTA5: 95126) and the county (Santa Clara) in your analysis.

Now that you have underwritten using your own rent, vacancy, and expense assumptions, we need to assess the use of debt using the previously built amortization table.

• Determine the maximum amount of debt needed to get DSCR = 1.2.
• Using your assumptions from above and the debt at a DSCR = 1.2, what is your targeted purchase price for this property?
• Vary the LTV ratio between 30% to 90% in 10% increments to assess the impact of changing the amount borrowed on the projects DSCR, NPV, and IRR. Plot the NPV and IRR profiles vs LTV on a graph. Explain your findings.

You are going to raise equity for this deal from outside investors. Assume that you get one LP that will provide all of the equity. Calculate the IRR and EM for the LP and the GP (i.e. you) using a cash flow waterfall for a deal underwritten with your assumptions, purchase price, and a DSCR = 1.2 using the below:

• LP gets all capital returned first
• LP earns a 10% pref
• LP:GP carry of 80:20
• Are the returns sufficient for the LP and the GP? Explain.