The underwriting model

How it works

The model runs month by month and reports by year. It closes on 1 January of year 1, runs 12 × (hold + 1) months, and sells at the end of the hold, priced on the NOI of the following year. Cash flows are annual and fall at the end of each year; the IRR is solved by bisection between -99.99% and 500%, and shows n/a when the flows never change sign. A month is 30.4375 days (365.25 ÷ 12), and weeks convert to months as weeks × 7 ÷ 30.4375.

Rents

Market rent in year y: market × (1 + g₂) × … × (1 + gᵧ). The first growth rate takes effect at the start of year 2. The renovation premium grows with it.

Leases roll evenly, a twelfth of each unit type in each month. A lease that rolls resets to that month's market rent, so the in-place rent of an unrenovated unit is the average of 12 cohorts, each at the market rent of its last roll, or at the rent roll's in-place figure until its first. loss to lease = classic units × (market - average in-place). It burns off over 12 months and turns negative, a gain to lease, when market falls below in-place.

Renovations start at the pace you set from the first renovation month, split across the unit types by their share of the units so every type finishes in the same month. A unit is offline for the downtime months, then leases at market plus the premium. No unit starts after the sale: when the hold ends first, the program stops there and the forward NOI carries only the units started by then. GPR = (classic + offline) × market + renovated × (market + premium), downtime = offline × market, and the in-place potential is GPR - loss to lease - downtime. Vacancy, concessions and bad debt are each a percentage of that potential.

Expenses

Each line is a year 1 figure per unit, grown each year: per unit × units × (1 + growth)^(year - 1). Property taxes are reassessed on the purchase: price × effective rate, grown at their own rate. Management is a percentage of EGI. Replacement reserves sit above NOI, the lender's convention, so the NOI here is the NOI a lender underwrites.

Debt

The loan constant is 12 × pmt(rate ÷ 12, amortization months, 1). The 3 ceilings are price × max LTV, T-12 NOI ÷ (min DSCR × constant) and T-12 NOI ÷ min debt yield; the loan is the smallest. Interest is balance × rate ÷ 12; principal is zero in the interest-only months and payment - interest after. A floating loan resets its rate each year to the index plus the spread and re-computes the payment on the remaining balance and amortization.

Returns

Equity is price + closing + loan fee + CapEx funded at close - loan. The sale: forward NOI ÷ exit cap, less selling costs, the loan payoff and the prepayment cost. The equity multiple is the positive flows over the equity plus any negative years. Cash on cash is each year's cash flow before the sale over the equity. Yield on cost is the NOI of the first full year after the renovations finish over price + closing + all CapEx. Break-even occupancy is (expenses + debt service) ÷ (GPR + other income).

Taxes

Depreciable basis is (price + closing) × (1 - land share). The cost segregation carve-out goes to 5 and 15-year property, with bonus depreciation in year 1 and the half-year MACRS tables for the rest; the building is 27.5-year straight line, mid-month, from month 1, and each CapEx dollar is 27.5-year straight line from the month it is spent. Taxable income is NOI + reserve deposits - interest - depreciation - loan fee amortization; the deposits are depreciated like CapEx. The loan fee is amortized over the loan term, the rest deducted at the sale with the prepayment cost. A passive investor carries losses forward and uses them against later income; what is left at the sale is released against ordinary income. At the sale the 5-year depreciation, and the 15-year depreciation above straight line, is ordinary recapture; the rest of the depreciation is unrecaptured 1250 gain at up to 25%; the remaining gain is capital gain. The 3.8% net investment income tax applies unless you are a real estate professional.

A check you can do by hand

Set every growth rate to 0, in-place rents to market, the renovation share, exterior CapEx, concessions, bad debt, closing, loan fee, prepayment and selling costs to 0, and interest-only to 120 months. Rent is $231,300 a month, $2,775,600 a year; less 5% vacancy, plus $201,600 of other income, EGI is $2,838,420. Expenses are $998,760 of per-unit lines, $336,000 of taxes, $121,800 of insurance and $85,152.60 of management: NOI is $1,296,707.40 every year. The loan is $13,650,000 at 6.00% interest only, $819,000 a year, so equity of $7,350,000 earns $477,707.40 a year, and year 5 adds a sale at $21,611,790 less the loan. The levered IRR is 7.92% and the unlevered 6.68%.

The most common mistake is underwriting the seller's T-12 expenses as year 1. On a purchase the taxes reassess to the price and the insurance reprices to today's market; in the invented deal those 2 lines alone take $54,600 out of year 1 NOI.