REAL 380 - Development Pro Forma Assignment Questions 1A Create a pro forma for Years 0-7 from Potential Gross Income through Unlevered Cash Flows. 1B Calculate the Total Cost of Development. 1C Calculate the annual debt service and loan constant for the construction loan. 1D Calculate the annual debt service and loan constant for the permanent loan. 1E Calculate the Equity Returned at Resale (End of Year 6). 1F Extend the proforma to Levered Cash Flows. Calculate the Debt Coverage Ratio for each year with the Permanent Loan. 1G Show the in/out investment cash flows (equity, debt, and total) by year (e.g., upfront investment, resale, etc.) 1H Calculate the Unlevered IRR. 1I Calculate the Levered IRR and Levered NPV. 1J Calculate the Return on Investment for Years 1-6. 1K Calculate the Cash on Cash Return for Years 1-6. 1L If the property could be sold for a 6.5% cap rate once stabilized (End of Year 1) how much would it be worth? 1M What observations can you draw by comparing your answer in 1L to the total cost of development? 1N Based on the Value at Stabilization calculated in 1L, what opportunity might you have to pull equity out of this project? 1O Given what you know, would this be a worthwhile development to pursue? Analyze the following real estate development opportunity by answering the questions below. 36-units Rents in the first year of operations are $2,200 per unit per month Rents increase 3% each year Construction will take 12 months (All of Year 0). After construction is complete 6 units will be leased at the beginning of each month until fully occupied (e.g, 6 units will be leased in Month 1, 12 units in Month 2, etc.) (Hint: use this to calculate vacancy in Year 1) Assume 5% vacancy every year once the property is stabilized. (Stabilization is the first year after lease-up is complete.) Operating expenses will be $250,000 for Year 1, $325,000 for Year 2, and will grow by 3% annually thereafter. (Capital reserves are included in operating expenses so there are no additional capital expenses below NOI.) The site can be acquired for $648,000 Hard costs are $190,000 per unit. Soft costs (including all lending fees, reserves, and accrued interest during construction) are 15% of hard costs. The lender will provide an interest only construction loan for 65% of the total cost of development including acquisition (Loan-toCost). The interest rate will be 6.0%. (Since interest during construction is included in soft costs, debt service for this loan should start in Year 1.) Once the property is stabilized (end of Year 1), the developer will convert the construction loan to a permanent loan at a 5.5% interest rate and 20-year amortization. The property will be sold at the end of Year 6 with an exit cap rate of 6.5% and 4% cost of sale. Required Levered Yield is 18%. Required Unlevered Yield is 10%.

Questions

1A Create a pro forma for Years 0-7 from Potential Gross Income through Unlevered Cash Flows.

1B Calculate the Total Cost of Development.

1C Calculate the annual debt service and loan constant for the construction loan.

1D Calculate the annual debt service and loan constant for the permanent loan.

1E Calculate the Equity Returned at Resale (End of Year 6).

1F Extend the proforma to Levered Cash Flows. Calculate the Debt Coverage Ratio for each year with the Permanent Loan.

1G Show the in/out investment cash flows (equity, debt, and total) by year (e.g., upfront investment, resale, etc.)

1H Calculate the Unlevered IRR.

1I Calculate the Levered IRR and Levered NPV.

1J Calculate the Return on Investment for Years 1-6.

1K Calculate the Cash on Cash Return for Years 1-6.

1L If the property could be sold for a 6.5% cap rate once stabilized (End of Year 1) how much would it be worth?

1M What observations can you draw by comparing your answer in 1L to the total cost of development?

1N Based on the Value at Stabilization calculated in 1L, what opportunity might you have to pull equity out of this project?

1O Given what you know, would this be a worthwhile development to pursue?