Valuedesk.us
Real estate

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.

as entered

    $14,893
    5.96%
    -0.11%
    9.17%
    • checkNegative leverage. The going-in cap rate of 5.96% is 203 basis points below the loan constant of 7.98% once interest only ends, so each borrowed dollar costs more in debt service than it earns in NOI and the debt lowers cash on cash.
    • checkCoverage falls to 0.99x over the hold. Most lenders covenant at 1.20x, so this loan is at or through the covenant in the model's own base case. Size it in the DSCR tool.

    Maximum offer

    year one targets, every other input held

    Offer no more than $194,228 to hit your targets. The cash on cash target is the one that sets the limit.

    • Fail at $250,000: cash on cash -0.11% against 6.00%
    • Fail at $250,000: debt coverage 0.99x against 1.20x

    Taxes are held at the typed bill at every price. Turn on the tax reset to have them move with the price.

    Size the debt

    hands the deal and loan terms over

    Year one NOI of $14,893, a lending value of $250,000 (the purchase price, since a house is appraised on comparable sales), a basis of $257,500 and NOI growth of 3.00% a year, plus your rate, amortisation, interest only period and loan to value. 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 →

    Split the cash flows

    hands the yearly cash flows over

    Opens the waterfall with this deal's cash flow by year: the equity going in, each year's cash flow after debt service, and the sale proceeds in the last year. Use it to see how a partner split shares the return.

    Send cash flows to the waterfall →

    Build

    year one, every input is in the rail
    Calculation build. Each row is one step of the workings, the arithmetic that produced it where there is any, and its value.
    year 1$25,200
    5% of GPR($1,260)
    1% of GPR($252)
    Plus: other income$0
    $25,200 - $1,260 - $252 + $0 = $23,688$23,688
    Less: operating expenses7 line items($5,700)
    Less: management fee8% of EGI($1,895)
    $1200/unitreserve per unit x units, escalated with expenses($1,200)
    $23,688 - $5,700 - $1,895 - $1,200 = $14,893$14,893
    Operating expense ratiototal operating expenses / $23,688 = 37.13%37.13%
    Purchase price$250,000
    Plus: acquisition costs3% of price$7,500
    Total basis$250,000 + $7,500 = $257,500$257,500
    $14,893 / $250,000 = 5.96%5.96%
    Price per unit1 units$250,000
    Price per square foot1600 sqft$156.25
    Loan at close75% of price$187,500
    Equity at close$257,500 - $187,500 = $70,000$70,000
    Year 1 debt serviceamortising$14,969
    Year 1 cash flow after debt service($76)
    ($76) / $70,000 = -0.11%-0.11%
    Monthly cash flow, year 1($76) / 12 = ($6)($6)
    1% rule$25,200 / 12 / $250,000 = 0.84%0.84%
    (total operating expenses + debt service) / ($25,200 + $0) = 94.30%94.30%
    year 7$17,783
    Gross sale price3% a year appreciation$250,000 x (1 + appreciation) ^ hold = $307,468$307,468
    Implied price growthper year($307,468 / $250,000) ^ (1 / hold) - 1 = 3.00%3.00%
    Less: cost of sale6% of price($18,448)
    Net sale proceeds$307,468 - $18,448 = $289,020$289,020
    Less: loan balance at exit($170,901)
    Net equity proceeds$289,020 - $170,901 = $118,119$118,119
    on total basis7.88%
    on equity9.17%
    gross, levered1.82x

    Operating table

    7 year hold
    Forecast by year, Y1 to Y2 to Y3 to Y4 to Y5 to Y6 to Y7, containing net operating income build.
    Line itemY1Y2Y3Y4Y5Y6Y7
    Net operating income build
    3%/yr$25,200$25,956$26,735$27,537$28,363$29,214$30,090
    ($1,260)($1,298)($1,337)($1,377)($1,418)($1,461)($1,505)
    ($252)($260)($267)($275)($284)($292)($301)
    Plus: other income$0$0$0$0$0$0$0
    Working, Y1: $25,200 - $1,260 - $252 + $0 = $23,688$23,688$24,399$25,131$25,885$26,661$27,461$28,285
    Less: real estate taxes($3,000)($3,090)($3,183)($3,278)($3,377)($3,478)($3,582)
    Less: insurance($1,500)($1,545)($1,591)($1,639)($1,688)($1,739)($1,791)
    Less: utilities($0)($0)($0)($0)($0)($0)($0)
    Less: repairs and maintenance($1,200)($1,236)($1,273)($1,311)($1,351)($1,391)($1,433)
    Less: payroll and contract services($0)($0)($0)($0)($0)($0)($0)
    Less: administrative and marketing($0)($0)($0)($0)($0)($0)($0)
    Less: other operating expenses($0)($0)($0)($0)($0)($0)($0)
    Less: management fee8% of EGI($1,895)($1,952)($2,010)($2,071)($2,133)($2,197)($2,263)
    $1200/unit($1,200)($1,236)($1,273)($1,311)($1,351)($1,391)($1,433)
    Less: total operating expenses3%/yr($8,795)($9,059)($9,331)($9,611)($9,899)($10,196)($10,502)
    Working, Y1: $23,688 - $8,795 = $14,893$14,893$15,340$15,800$16,274$16,762$17,265$17,783
    Operating expense ratio37.13%37.13%37.13%37.13%37.13%37.13%37.13%
    Less: debt service($14,969)($14,969)($14,969)($14,969)($14,969)($14,969)($14,969)
    Cash flow after debt serviceWorking, Y1: $14,893 - $14,969 = ($76)($76)$370$831$1,305$1,793$2,296$2,814
    0.99x1.02x1.06x1.09x1.12x1.15x1.19x
    Loan balance$185,595$183,553$181,363$179,015$176,497$173,797$170,901

    Sensitivity

    monthly rent × loan rate
    Sensitivity table. Rows vary Monthly rent, columns vary Loan rate. The base case cell is marked "base case".
    Monthly rent downLoan rate across6.00%6.50%7.00%7.50%8.00%
    $1,890($65)($126)($188)($252)($316)
    $1,995$26($35)($97)($161)($226)
    $2,100$117$56($6) base case($70)($135)
    $2,205$208$147$84$21($44)
    $2,310$299$238$175$112$47

    Cells are monthly cash flow in year one, after the mortgage payment.