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

    Year 1 NOI
    $988,540
    Going-in cap rate
    6.59%
    Cash on cash
    6.76%
    Levered IRR
    10.84%
    • checkNegative leverage. The going-in cap rate of 6.59% is 80 basis points below the loan constant of 7.39% 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.

    Size the debt

    hands the deal and loan terms over

    Year one NOI of $988,540, a lending value of $15,000,000 (the lower of price and value at the market cap rate), a basis of $15,300,000 and NOI growth of 3.37% 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 →

    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.
    Gross potential rentyear 1$1,800,000
    Less: vacancy5% of GPR($90,000)
    Less: credit loss1% of GPR($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 expenses7 line items($710,000)
    Less: management fee3% of EGI($53,460)
    Less: replacement reserve$300/unitreserve 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 costs2% of price$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 rate6.5% capyear 1 NOI capitalised at the market cap rate$15,208,308
    Price per unit100 units$150,000
    Price per square foot85000 sqft$176.47
    Loan at close60% of price$9,000,000
    Equity at close$15,300,000 - $9,000,000 = $6,300,000$6,300,000
    Year 1 debt serviceinterest only$562,500
    Year 1 cash flow after debt service$426,040
    Cash on cash$426,040 / $6,300,000 = 6.76%6.76%
    Cash on cash, first amortising yearyear 3that year's cash flow after debt service / $6,300,000 = 6.21%6.21%
    Monthly cash flow, year 1$426,040 / 12 = $35,503$35,503
    Monthly rent to price1% rule$1,800,000 / 12 / $15,000,000 = 1.00%1.00%
    Break-even occupancy(total operating expenses + debt service) / ($1,800,000 + $90,000) = 71.74%71.74%
    Reversion NOIyear 5$1,128,665
    Gross sale price6.75% exit capreversion NOI capitalised at the exit cap rate$16,720,970
    Implied price growthper year($16,720,970 / $15,000,000) ^ (1 / hold) - 1 = 2.20%2.20%
    Less: cost of sale2% of price($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 IRRon total basis8.34%
    Levered IRRon equity10.84%
    Equity multiplegross, levered1.57x

    Operating table

    5 year hold
    Forecast by year, Y1 to Y2 to Y3 to Y4 to Y5, containing net operating income build.
    Line itemY1Y2Y3Y4Y5
    Net operating income build
    Gross potential rent3%/yr$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 fee3% of EGI($53,460)($55,064)($56,716)($58,417)($60,170)
    Less: replacement reserve$300/unit($30,000)($30,750)($31,519)($32,307)($33,114)
    Less: total operating expenses2.5%/yr($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 ratio44.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
    DSCR1.76x1.82x1.59x1.64x1.70x
    Loan balance$9,000,000$9,000,000$8,894,538$8,782,293$8,662,828

    Sensitivity

    rent growth × exit cap rate
    Sensitivity table. Rows vary Rent growth, columns vary Exit cap rate. The base case cell is marked "base case".
    Rent growth downExit cap rate across6.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 case9.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.