Rental pro forma
Every line from gross potential rent to net operating income is on screen and every one is editable. Rent and expenses grow at separate rates, the replacement reserve is a field rather than an omission, and the exit is a reversion with a cost of sale rather than a second appraisal. When the NOI is right, hand it to the debt tool.
Assumptions
Property and basis
Closing costs, diligence and financing fees. Capitalised into basis, so the equity funds them and the returns carry them.
Income, year one
Every unit leased every day at the asking rent.
Parking, storage, laundry, RUBS, pet fees. Grows with rent and is not reduced by vacancy.
Bad debt and concessions. Struck against gross potential rent, not against rent net of vacancy.
Operating expenses, year one
A percentage of collections, so it escalates with revenue rather than with expenses.
Roofs, boilers, parking lots and appliances. Real annual costs that arrive in lumps, and the line most pro formas leave out.
Hold and growth
Separate from rent growth on purpose. The gap between the two is the operating leverage in the deal.
Valuation and exit
What the market would pay for this income today. Year one NOI capitalised at this rate is the value, against which the purchase price is a bid.
A buyer at the end of the hold owns the years after it, so the forward figure is what they underwrite. The difference is one year of NOI growth on the whole reversion.
Debt
Sized by hand here. Size it against all four lender tests in the debt tool.
The payment steps up the month this ends, which is the cliff a flat pro forma hides.
Model
Year 1 NOI
$988,540
Going-in cap rate
6.59%
Cash on cash
6.76%
Levered IRR
10.84%
Size the debt
Year one NOI of $988,540, value at the market cap rate of $15,208,308, a basis of $15,300,000 and NOI growth of 3.37% a year. The growth rate is the one NOI actually compounded at, not the rent growth assumption, because expenses grow at their own rate.
Open in debt sizing →Build
| Gross potential rent | $1,800,000 |
|---|---|
| Less: vacancy | -$90,000 |
| Less: credit loss | -$18,000 |
| Plus: other income | $90,000 |
| Effective gross income$1,800,000 - $90,000 - $18,000 + $90,000 = $1,782,000 | $1,782,000 |
| Less: operating expenses | -$710,000 |
| Less: management fee | -$53,460 |
| Less: replacement reservereserve per unit x units, escalated with expenses | -$30,000 |
| Net operating income$1,782,000 - $710,000 - $53,460 - $30,000 = $988,540 | $988,540 |
| Operating expense ratiototal operating expenses / $1,782,000 = 44.53% | 44.53% |
| Purchase price | $15,000,000 |
| Plus: acquisition costs | $300,000 |
| Total basis$15,000,000 + $300,000 = $15,300,000 | $15,300,000 |
| Going-in cap rate$988,540 / $15,000,000 = 6.59% | 6.59% |
| Value at market cap rateyear 1 NOI capitalised at the market cap rate | $15,208,308 |
| Price per unit | $150,000 |
| Price per square foot | $176.47 |
| Loan at close | $9,000,000 |
| Equity at close$15,300,000 - $9,000,000 = $6,300,000 | $6,300,000 |
| Year 1 debt service | $562,500 |
| Year 1 cash flow after debt service | $426,040 |
| Cash on cash$426,040 / $6,300,000 = 6.76% | 6.76% |
| Reversion NOI | $1,128,665 |
| Gross sale pricereversion NOI capitalised at the exit cap rate | $16,720,970 |
| Less: cost of sale | -$334,419 |
| Net sale proceeds$16,720,970 - $334,419 = $16,386,550 | $16,386,550 |
| Less: loan balance at exit | -$8,662,828 |
| Net equity proceeds$16,386,550 - $8,662,828 = $7,723,722 | $7,723,722 |
| Unlevered IRR | 8.34% |
| Levered IRR | 10.84% |
| Equity multiple | 1.57x |
Operating table
| Line item | Y1 | Y2 | Y3 | Y4 | Y5 |
|---|---|---|---|---|---|
| Net operating income build | |||||
| Gross potential rent | $1,800,000 | $1,854,000 | $1,909,620 | $1,966,909 | $2,025,916 |
| Less: vacancy | -$90,000 | -$92,700 | -$95,481 | -$98,345 | -$101,296 |
| Less: credit loss | -$18,000 | -$18,540 | -$19,096 | -$19,669 | -$20,259 |
| Plus: other income | $90,000 | $92,700 | $95,481 | $98,345 | $101,296 |
| Effective gross incomeWorking, Y1: $1,800,000 - $90,000 - $18,000 + $90,000 = $1,782,000 | $1,782,000 | $1,835,460 | $1,890,524 | $1,947,240 | $2,005,657 |
| Less: real estate taxes | -$210,000 | -$215,250 | -$220,631 | -$226,147 | -$231,801 |
| Less: insurance | -$65,000 | -$66,625 | -$68,291 | -$69,998 | -$71,748 |
| Less: utilities | -$95,000 | -$97,375 | -$99,809 | -$102,305 | -$104,862 |
| Less: repairs and maintenance | -$120,000 | -$123,000 | -$126,075 | -$129,227 | -$132,458 |
| Less: payroll and contract services | -$145,000 | -$148,625 | -$152,341 | -$156,149 | -$160,053 |
| Less: administrative and marketing | -$55,000 | -$56,375 | -$57,784 | -$59,229 | -$60,710 |
| Less: other operating expenses | -$20,000 | -$20,500 | -$21,013 | -$21,538 | -$22,076 |
| Less: management fee | -$53,460 | -$55,064 | -$56,716 | -$58,417 | -$60,170 |
| Less: replacement reserve | -$30,000 | -$30,750 | -$31,519 | -$32,307 | -$33,114 |
| Less: total operating expenses | -$793,460 | -$813,564 | -$834,178 | -$855,316 | -$876,991 |
| Net operating incomeWorking, Y1: $1,782,000 - $793,460 = $988,540 | $988,540 | $1,021,896 | $1,056,346 | $1,091,923 | $1,128,665 |
| Operating expense ratio | 44.53% | 44.32% | 44.12% | 43.92% | 43.73% |
| Less: debt service | -$562,500 | -$562,500 | -$664,975 | -$664,975 | -$664,975 |
| Cash flow after debt serviceWorking, Y1: $988,540 - $562,500 = $426,040 | $426,040 | $459,396 | $391,371 | $426,949 | $463,691 |
| DSCR | 1.76x | 1.82x | 1.59x | 1.64x | 1.70x |
| Loan balance | $9,000,000 | $9,000,000 | $8,894,538 | $8,782,293 | $8,662,828 |
Sensitivity
| Rent growth downExit cap rate across | 6.25% | 6.50% | 6.75% | 7.00% | 7.25% |
|---|---|---|---|---|---|
| 2.00% | 10.68% | 9.07% | 7.49% | 5.93% | 4.38% |
| 2.50% | 12.31% | 10.74% | 9.20% | 7.69% | 6.19% |
| 3.00% | 13.88% | 12.34% | 10.84% base case | 9.37% | 7.91% |
| 3.50% | 15.39% | 13.89% | 12.42% | 10.98% | 9.56% |
| 4.00% | 16.85% | 15.37% | 13.93% | 12.53% | 11.14% |
Cells are the levered IRR over the hold.