Real Estate Pro Forma

Real Estate Pro Forma

Real Estate Pro Forma: Income Statement, Loan Sizing, Returns, Tax Estimate and Waterfall

The yearly income statement

The model is annual. Potential gross income (PGI) is every unit or every unit of rentable area at its market rent: count × monthly rent × 12 for a unit mix, area × yearly rent for space let by area. Each rent row grows at its own rate, PGI_t = Σ count × rent × (1 + g)^{t − 1}. A vacancy and credit-loss allowance, a percent of PGI, is taken off and other income (laundry, parking, fees) added to give the effective gross income (EGI), the term the OCC Comptroller's Handbook defines as the revenue "after the application of a vacancy rate and deductions for expected credit losses". Operating expenses are entered on four bases: a fixed yearly amount, an amount per unit, an amount per unit of area, or a percent of EGI (the management fee is of this last kind). The property tax and the replacement reserve are entered on their own lines. Net operating income (NOI) is EGI less all of these. The reserve is counted as an expense, as the HUD MAP Guide (Section 7.8) does when it sums fixed expenses, variable expenses and the reserve for replacements into total operating expenses; NOI is before debt service, depreciation and income tax. All amounts sit at the end of their year.

Year-1 ratios: the going-in capitalization rate NOI₁/price; the gross rent multiplier price/PGI₁; the operating expense ratio expenses/EGI; and the break-even ratio (expenses + debt service)/PGI. The break-even occupancy is solved exactly: with EGI = occupancy × PGI + other income and a share p of EGI taken by the percent-of-income expenses, the cash flow before tax is zero at occupancy = [(fixed expenses + debt service)/(1 − p) − other income]/PGI. The MAP Guide (Section 3.1.25) calls this level, a debt service coverage of 1.0, "breakeven" or sustaining occupancy.

Loans

A loan L at the nominal rate r with m payments a year (12 or 1) has the periodic rate i = r/m. During any interest-only years the payment is L i. After them the loan amortizes over n = m × (amortization years) level payments A = L i/(1 − (1 + i)⁻ⁿ), and the balance after k of them is L[(1 + i)ⁿ − (1 + i)^k]/[(1 + i)ⁿ − 1], which is also the present value of the payments still to come. The tool walks the periods, so a yearly row is the sum of that year's interest and principal. A loan whose term ends before the sale is repaid in that year as a balloon out of the owner's cash (the tool does not model a refinancing and says so); otherwise the balance is repaid from the sale. Points are a percent of the loan paid at closing, so they raise the equity needed. The OCC handbook's own illustration is reproduced by these formulas: 1,000,000 at 6 percent on a 25-year schedule pays 6,443 a month and owes 899,000 after five years.

The loan constant is the yearly amortizing debt service per unit of loan, k = m A/L. Three lender measures follow the handbook's definitions: the debt service coverage ratio DSCR = NOI/debt service, the debt yield = NOI/loan, and loan-to-value = loan/value (the purchase price here). Each gives a largest loan: NOI₁/(DSCR_min k) by coverage, LTV_max × price by value, and NOI₁/(debt yield)_min by debt yield; the smallest governs, and the tool names it. The coverage test uses the amortizing constant even when the loan begins interest only, because the handbook asks that a property "meet the bank's repayment capacity (debt service coverage) requirements as though the loan were amortizing". The minimums are entered. For orientation, HUD's Section 223(f) criteria for market-rate apartments are debt service of 85 percent of NOI (a DSCR of 1.176) and 85 percent of value (MAP Guide 3.7.11.1), and the banking agencies' supervisory loan-to-value limit for improved property is 85 percent; conventional lenders set their own, usually tighter, figures.

Leverage

With V the price and L the debt, the first-year cash return on the equity V − L is (NOI − k L)/(V − L), which exceeds the capitalization rate NOI/V exactly when that rate exceeds the debt service per unit borrowed. The tool reports this as positive, neutral or negative leverage. Over the whole holding period the equivalent statement is about rates of return: the levered IRR is above the unlevered IRR when the debt costs less than the unlevered IRR and equal to it when the debt costs exactly that rate (the equity series is the unlevered series less the loan's own cash flows, whose present value at the loan's rate is zero). The tool's test script verifies this identity with annual payments and no points.

Sale and returns

The sale price at the end of year N is the following year's income capitalized, NOI_N+1/(exit capitalization rate): direct capitalization of a stabilized income, in the handbook's words. Selling costs are a percent of the price, and the loan balances are repaid from it. Two cash-flow series are formed. Unlevered: −(price + closing costs + improvements) at year 0, NOI each year, plus the net sale price in year N. Levered: −equity at year 0, NOI less debt service each year, plus the net proceeds after the loan payoff. The net present value is Σ CF_t/(1 + d)^t at the entered discount rate, and the internal rate of return is the rate at which it is zero. The tool scans the NPV from −99 to 1,000 percent on a grid uniform in ln(1 + r) and bisects every sign change, so it finds every root; when the series changes sign more than once (a balloon paid in mid-holding, for example) it says so and reports the lowest root. Cash-on-cash is the year's cash flow before tax over the equity; the equity multiple is all cash received over the equity.

The equity build-up table values the property at the end of each year as the next year's NOI over a capitalization rate that moves linearly from the going-in rate, which returns the purchase price at year 0, to the exit rate at year N. It is an illustration of how amortization and income growth build equity, not an appraisal.

Sensitivity

The levered IRR is recomputed on two 5 × 5 grids: exit capitalization rate (±0.5 and ±1 point) against a shift of every rent growth rate (±1 and ±2 points), and purchase price (±5 and ±10 percent) against a shift of the loan rates (±0.5 and ±1 point). A loan sized by loan-to-value follows the price. The tornado chart moves one input at a time (rent growth ±1 point, vacancy ±2 points, all operating expenses ±10 percent, exit rate ±0.5 point, price ±5 percent, loan rates ±1 point) and ranks the inputs by the swing of the levered IRR.

Investor tax estimate

The optional tax block is a simplified estimate for comparing cases; it is not tax advice. Buildings are depreciated straight line with the mid-month convention over 27.5 years (residential rental property) or 39 years (nonresidential real property); land is not depreciable, so the entered land share of the price and closing costs is left out of the basis, and the improvements are added to it. The property is taken as placed in service in the first month of year 1, which gives 11.5 months of depreciation that year (3.485 percent for 27.5-year property, IRS Publication 946 Table A-6), a full year's amount after, and 11.5/12 of a year's amount in the year of sale (Publication 946, dispositions under the mid-month convention). For 39-year property the tool uses the percentages of Table A-7a as printed (2.461 percent in the first year and 2.564 percent after), which rest on a monthly rate rounded to 0.214 percent and so differ slightly from the exact 1/39.

Taxable income is NOI less interest, depreciation and the loan points spread evenly over the loan term (what is left is written off when the loan is repaid), and the tax is that income times the marginal rate; a negative figure is treated as a saving in the same year. On the sale, the gain is the net sale price less the adjusted basis (cost less depreciation taken). The part of the gain equal to the depreciation taken is taxed at the entered recapture rate (unrecaptured section 1250 gain, capped at 25 percent by the Internal Revenue Code) and the rest at the entered capital gain rate; a loss is taken at the marginal rate. Not modeled: passive-activity loss limits, the net investment income tax, state taxes, cost segregation and bonus depreciation, like-kind exchanges, and the timing of reserve spending (the reserve deposit is treated as a deductible expense of its year).

Two-tier waterfall

The optional waterfall divides the levered cash flows between investors, who put up the entered share of the equity, and a sponsor. A hurdle balance starts at the equity, grows each year by the preferred return and is reduced by what is distributed. Tier 1: cash goes to both parties in proportion to their capital until the hurdle balance is zero, which is the point at which the capital has been returned with the preferred return compounded annually (the tier-1 distributions discounted at the preferred return equal the equity). Tier 2: the cash beyond that is split, the promote share to the sponsor and the remainder again in proportion to capital. If the property's levered IRR does not exceed the preferred return, nothing reaches tier 2. There is no catch-up tier, fee or clawback; partnership agreements differ, and theirs governs.

Limits

Yearly periods with amounts at year end; one vacancy rate; no lease-by-lease rollover, tenant improvements, leasing commissions or lease-up; no refinancing, prepayment penalty or floating rate; value for loan-to-value is the purchase price. The default case, a 24-unit apartment building bought with a 75 percent loan and held ten years, is illustrative and not taken from a published example.