RED 510 Case Analysis

Mixed Retail-Residential Development Project

Instructions: This assignment covers various aspects of cash flow analysis learned this semester. It is due by May 2^nd. You can use the attached Excel spreadsheet.

This mixed residential-retail development consists of 50 two-bedroom units and 50,000 square feet (SF) of retail space. This assignment consists of structuring the financing of the project, developing a ten-year pro forma cash flow, and analyzing its profitability. The information you need to complete the assignment is in the appendix tables summarized below.

· Table 1: This table includes year 1 rent, vacancy, and collection loss for the residential and retail areas. Rents are monthly per unit for the apartments and annually per SF for the retail space. The retail vacancy and collection loss apply to income from inline tenants only.

· Table 2: Total development costs include land acquisition costs, hard costs, soft costs, financing costs, and lease-up reserves.

· Table 3: Operating expenses and CAPEX reserves for the apartments and the retail space are for Year 1. These expenses are monthly per unit for the apartments and annually per SF for the retail space – there is no need to account for vacancy when computing operating expenses because variable and fixed expenses are lumped together.

· Table 4: This table includes annual expense and rent growth rates after Year 1.

· Table 5: This table contains the mortgage financing terms: interest rate, origination fee, amortization period, maturity date, and LTV. The final loan amount will be based on the value of the property at the beginning of Year 1.

· Table 6: It contains the project’s income capitalization rates at the beginning of Year 1 and at the end of Year 10.

Questions:

1. Calculate the project’s 10-year NOIs and net sale price at the end of year 10. (40 points)

2. Use the Year 1 NOI to compute the value of the project and the loan amount that will be available for the project. What is the required amount of equity to fund the development? (15 points)

3. Using the year 1 cash flow, compute the project’s effective gross income multiplier (EGIM), operating expense ratio (OER), debt coverage ratio (DCR), cash-on-cash (COC) return, and debt yield ratio (DYR). (15 points)

4. The project’s annual before-tax operating cash flows and before-tax equity reversion at the end of year 10. (12 points)

5. Compute the project’s unlevered and levered NPV using the DCF method if the unlevered and levered discount rates are 10% and 15%, respectively, under the assumption that the entire investment occurs instantaneously at the beginning of Year 1. Should the developer go ahead with this project? Explain. (18 points)

Appendix Tables: