Problem: (Excel) Answer Question using discounted cash flow (DCF) analysis in Excel instead of the shortcut perpetuity formula approach (i.e., convince yourself that the shortcut formula works). Set up a basic 10-year cash flow projection in which property income (NOI) grows by 3% each year and the year 10 sale price is calculated as year 11 property income divided by the 9.50% cap rate (the perpetuity formula assumes that the ratio of property income to value is constant, and hence the ‘‘terminal'' or ‘‘sale'' cap rate is identical to the ‘‘going in'' or first year cap rate). Determine the NPV assuming an asking price of $25 million. [Use the ‘‘ ¼ NPV(...)'' financial function to estimate the property value (i.e., PV) based on year 1 through year 10 cash flows. Then subtract the asking price.]
Question: An apartment complex has 1,000 units of which on average 100 are vacant at any given time. Per unit, the rent is $400 per month, and the operating expenses are $1,800 per year (per occupied unit). If you expect both rents and expenses to grow at 3% per year, the required return is 12.5%, and the building value is expected to remain a constant multiple of its net income, then what is the NPV of a deal to buy the property for $25 million?