Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

RED 510: Real Estate Practice and Processes

Homework 4 & 5: Cash Flow Analysis

Instructions: Use the attached pre-formatted Excel cash flow pro forma

Office Property Investment:

Property characteristics and cash flow assumptions:

• Office property with rentable space of 460,000 square feet (SF)

• First-year annual rent of $24.70/SF

• Projected annual rent increase: 3%

• Projected vacancy rate: 5%

• Other income in year 1 from cellphone tower rental:  $65,000

• Projected annual increase in other income: 6%

• Annual operating expenses per SF of total leasable space in year 1: $12.50/SF

• Projected annual increase in operating expenses: 4%

• Annual CAPX reserves: $850,000/year

Valuation and Financing Assumptions:

• Asking price for the property: $56,000,000

• Investment holding period: 5 years

• Going-out (exit) cap rate at the end of year 5: 8%

• Sale commission at the end of year 5: 2.5%

• Loan LTV: 75%

• Interest rate: 5.25%

• Financing commissions: 1.75%

• Amortization period: 30 years

• Maturity: 5 years

Questions:

Analyze the proposed office investment on behalf of an investor, who has agreed to purchase the property at the asking price of $56,000,000.

1. Find the loan amount, net loan proceeds, amount of equity investment required, annual debt service, and expected loan balance at the end of year 5. (15 points)

2. Develop a 5-year flow pro forma cash showing annual PGI, EGI, NOI, and before-tax cash flow (BTCF) and net sale price (NSP) and before-tax equity reversion (BTER) at the end of the year 5. (45 points)

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

4. Using the 5-year cash flows and the asking price, find the annual return that the property would generate over the investment period – this is the annual unlevered return that the buyer would earn. (10 points)

5. Using the before-tax cash flows, find the net present value (NPV) of the buyer if her required annual equity return is 15%. What will be the buyer’s expected return – this is the buyer’s levered return? Should she undertake this investment? (15 points)