Small multifamily pro forma: an assumed 70% loan sizes to 61%

A buyer of small apartment buildings asks Compound for a lender-ready pro forma on a real 21-unit Brooklyn walk-up from the New York City sale records, with a unit-by-unit rent roll, an expense check against CMBS filings and a switch between a Freddie Mac small-balance loan, a bank loan and a DSCR loan priced on November 13, 2026.

Compound Research · Updated Nov 15, 2026 · 14 min read

Short answer

A small multifamily pro forma projects a building's rents, expenses and debt unit by unit to show a lender the loan it supports. In Compound's model of a 21-unit Brooklyn walk-up at its $2,600,000 recorded sale price, the 1.20x coverage test caps the largest loan at $1,588,135, 61.1% of the price.

Try it now
On this page (10)
  1. 1. The subject and its price against five neighborhood sales
  2. 2. The rent roll: 14 stabilized units and 6 that can turn
  3. 3. The revenue build: one gap burns off and the other grows
  4. 4. Operating expenses, checked against CMBS filings
  5. 5. Debt sizing: the largest loan each lender would make
  6. 6. Returns under each loan: the exit case and the refinance case
  7. 7. The sensitivity grid and the flags
  8. 8. What public records cannot tell you
  9. 9. Our view: read it as a leverage test
  10. 10. FAQ

A 21-unit walk-up in Crown Heights, Brooklyn, modeled at its recorded sale price of $2,600,000, supports a first mortgage of $1,588,135. That is 61.1% of the price, against a 70% loan-to-value target, an analyst assumption. Each of the three lenders in the model is limited by debt service coverage, and none of them reaches its loan-to-value ceiling.

You are on an acquisitions team that buys small apartment buildings, and a $2.6 million deal still needs a package a lender will read. We asked Compound to build that package on a real building from public records. It ran on November 15, 2026 and returned one Excel workbook and a short summary.

Compound picked 489 Prospect Place from the New York City Department of Finance Rolling Sales file. That record gives 21 residential units, 10,752 gross square feet, a 1931 year built, building class C1 (a walk-up with more than six families) and a sale recorded on January 16, 2026 for $2,600,000. The request set the purchase price equal to that recorded price, so the price is an assumption of the exercise. The sale record has no rents and no expenses. Every in-place rent, lease date and expense line other than real estate taxes is an analyst assumption that Compound labeled as one. Nothing on this page is a current value for the property, a forecast of what it will earn, or advice to buy or sell it.

What the workbook shows:

  • Coverage sets the loan under every lender. The Freddie Mac small-balance loan sizes to $1,588,135, the bank loan to $1,496,248 and the DSCR loan to $1,471,840. A loan at the assumed 70% would be $1,820,000.
  • The building yields less than any of the loans cost. The going-in cap rate is 6.09%. The three fixed rates are 6.98%, 7.40% and 8.65%.
  • Only one loan improves on the unlevered return. The unlevered IRR is 8.3%. The bank loan returns 8.7%, the Freddie Mac loan 7.7% and the DSCR loan 6.7%.
  • The rent roll assumption drives the result. Compound assumed 14 of the 21 units are rent-stabilized at rents 46.3% below the HUD Fair Market Rent.
  • Price per unit and price per square foot disagree. The subject sold for 26.2% less per unit than five nearby walk-up sales and 48.0% more per square foot.

The subject and the five comparable sales come from New York City Department of Finance sale records on NYC Open Data. Market rents come from HUD’s Fair Market Rents and Zillow’s ZORI index. The expense check uses CMBS loan-level filings on SEC EDGAR. The fixed rates are priced off the Daily Treasury Par Yield Curve, and the bank loan’s floating years use Compound’s SOFR forward curve. Freddie Mac’s loan terms come from its public small-balance loan term sheet.

Prompt to try

“I work for a family office that buys small apartment buildings and want a small multifamily pro forma I can take to a lender, built on a real building. From the New York City property sale records, pick one walk-up rental apartment building of 8 to 40 units in Brooklyn, Queens or the Bronx that sold in the last 18 months at an arm's-length price, with its address, unit count, gross square feet, year built and sale price on record. Use it as the subject and its recorded sale price as the purchase price, and compare its price per unit and per square foot with five other sales of similar buildings in the same neighborhood from the same records. Build a unit-by-unit rent roll: set the unit mix, in-place rents, lease status and expected turn dates as analyst assumptions with a note on each, and market rent per unit from the HUD Fair Market Rent by bedroom count for the building's ZIP code and Zillow ZORI for the metro. Set line-item expenses and reserves per unit as analyst assumptions, checked against operating expenses per unit at small New York City apartment properties in the CMBS loan-level filings. Include an optional renovation budget: per-unit turn cost, common areas and deferred maintenance. Build a debt module with a switch between three loans: a Freddie Mac small-balance loan with 30-year amortization and step-down prepayment, priced off today's Treasury yield for its term; a local bank loan fixed for five years, then floating over the SOFR forward curve, with a balloon; and a DSCR loan priced above the agency loan. Build: the revenue build with loss-to-lease burning off as each unit turns; a five-year annual projection; an exit case and a refinance case; unlevered yield, levered IRR, equity multiple, cash-on-cash by year and DSCR by year under each loan; the largest loan each lender would make; and a sensitivity table of levered IRR to exit cap rate and rent growth. Flag a DSCR below 1.20x under any loan, a price per unit more than 15% above the neighborhood sales, and market rents set above the Fair Market Rent without a stated reason. Use the stored public data wherever it has the figure, and web search only for what it lacks. Put every input on an Assumptions sheet with one column that says where it came from: a cited source, or "analyst assumption" for a figure I would set myself, with the reason for the value you chose. Every output must be a live formula that traces back to the Assumptions sheet. Add a Sources sheet listing each data source, what it gave and its date. Deliver one Excel workbook and a short summary of the result, the flags, and what I would need from the seller to firm it up. Cite every sourced figure.”

Try this prompt free →

That is the request as we sent it, without its closing line, which asks that no name or email be put in the workbook.

The subject and its price against five neighborhood sales

The subject’s $123,810 a unit is 26.2% below the average of five Crown Heights walk-up sales, and its $242 a square foot is 48.0% above their average. Both are true because its units average 512 square feet, about half the size of the units in the comparable buildings.

489_Prospect_Place_Lender_ProForma.xlsx
H18fx=H6/H14-1
BCDEFGHIJ
5PropertyUnitsGross sq ftYear builtSale price ($)Sale datePrice per unit ($)Price per sq ft ($)Sq ft per unit
6Subject: 489 Prospect Place, Brooklyn, NY 112382110,7521931$2,600,00001/16/2026$123,810$242512
8394 Montgomery Street, 112254230,7001925$3,600,00007/08/2025$85,714$117731
91512 Park Place, 112133228,2001928$2,300,00009/29/2025$71,875$82881
10447 Prospect Place, 1123888,8841907$2,500,00004/30/2026$312,500$2811,111
11487 Prospect Place, 1123888,8841907$1,250,00007/23/2026$156,250$1411,111
12420 Eastern Parkway, 112251516,3001926$3,194,52008/10/2026$212,968$1961,087
14Average of comparables$167,861$163984
15Median of comparables$156,250$1411,087
16Aggregate (total price / total units or sq ft)$122,329$138
18Subject premium / (discount) to average(26.2%)48.0%
19Subject premium / (discount) to median(20.8%)71.9%
20Flag threshold (Request)15%
21Price per unit more than 15% above neighborhood averageOK
SummaryAssumptionsCompsRentRollProFormaDebtReturnsSensitivityOpexCheckSources
Compound's Comps tab, without its title and selection note. Addresses, units, square feet, year built, price and date are as the Department of Finance sale records give them. Negative values are in parentheses.

Compound wrote its selection rule at the top of the sheet: the same Department of Finance neighborhood, building class C1, 8 or more units, single-lot sales at arm’s-length prices between July 2025 and August 2026. One comparable, 394 Montgomery Street, has 42 units, just above the 8 to 40 unit range the request set for the subject.

The five sales run from $71,875 to $312,500 a unit, and building size explains the opposite signs. The two larger buildings, with 42 and 32 units, sold for $85,714 and $71,875 a unit. The three smaller ones, with 8 to 15 units, sold for $156,250 to $312,500 a unit. Two of those, 447 and 487 Prospect Place, match in the records at 8 units, 8,884 square feet and a 1907 year built, and they sold within three months of each other for $2,500,000 and $1,250,000. One price is exactly half the other, which is how a part-interest sale or a price split across two deeds appears in the records. Treat the $1,250,000 sale as unconfirmed until the deed is read.

Price per unit: the subject against five Crown Heights walk-up sales

Recorded sale price over residential units, sales from July 2025 to August 2026

Subject: 489 Prospect Place (21 units)$123,810394 Montgomery Street (42 units)$85,7141512 Park Place (32 units)$71,875447 Prospect Place (8 units)$312,500487 Prospect Place (8 units)$156,250420 Eastern Parkway (15 units)$212,968Average of the five comparables$167,861

Compound's Comps tab, column H: NYC Department of Finance Rolling Sales and Annualized Sales files on NYC Open Data.

The request asked for a flag when the price per unit is more than 15% above the neighborhood sales, and the flag reads OK. We would not stop there. On the comparables’ median the subject is 20.8% lower per unit and 71.9% higher per square foot. With five sales this far apart, the average is a weak benchmark, and a lender’s appraiser will pick the sales closest in unit size.

The rent roll: 14 stabilized units and 6 that can turn

Compound set 14 of the 21 units as rent-stabilized, 6 as free-market and 1 as a rent-free unit for the superintendent. Each row is an analyst assumption with a note, and the Assumptions sheet heads the block “pending seller rent roll”.

489_Prospect_Place_Lender_ProForma.xlsx
H9fx=CHOOSE(C9+1,Assumptions!$C$33*(1+Assumptions!$C$37),Assumptions!$C$34*(1+Assumptions!$C$38),Assumptions!$C$35*(1+Assumptions!$C$39),Assumptions!$C$36*(1+Assumptions!$C$40))
BCDEGHIJK
5UnitBedroomsRegulationIn-place rentExpected turnMarket rent Y1Post-turn rent Y1Loss-to-lease ($)Loss-to-lease (%)
6B11SUP$0$2,729$0$00.0%
71A1RS$1,425$2,729$1,425$1,30447.8%
81B0RS$1,180$2,593$1,180$1,41354.5%
91C1FM$2,25004/01/2027$2,729$2,729$47917.6%
101D1RS$1,610$2,729$1,610$1,11941.0%
111E0RS$1,240$2,593$1,240$1,35352.2%
122A2RS$1,890$2,971$1,890$1,08136.4%
132B1RS$1,51507/01/2029$2,729$1,515$1,21444.5%
142C1FM$2,32508/01/2027$2,729$2,729$40414.8%
152D0RS$1,295$2,593$1,295$1,29850.1%
162E1RS$1,560$2,729$1,560$1,16942.8%
173A2FM$2,70012/01/2027$2,971$2,971$2719.1%
183B1RS$1,480$2,729$1,480$1,24945.8%
193C1RS$1,64501/01/2030$2,729$1,645$1,08439.7%
203D0FM$2,05001/01/2027$2,593$2,593$54320.9%
213E1RS$1,390$2,729$1,390$1,33949.1%
224A1FM$2,37504/01/2028$2,729$2,729$35413.0%
234B1RS$1,535$2,729$1,535$1,19443.8%
244C0RS$1,310$2,593$1,310$1,28349.5%
254D1FM$2,40007/01/2028$2,729$2,729$32912.1%
264E0RS$1,225$2,593$1,225$1,36852.8%
27Total / weighted average$34,400$56,977$36,780$19,84836.6%
SummaryAssumptionsCompsRentRollProFormaDebtReturnsSensitivityOpexCheckSources
Compound's RentRoll tab, with nine of its sixteen columns and shortened headings. Rents are dollars a month. SUP is the superintendent's unit, RS is rent-stabilized and FM is free-market. The rows are illustrative: every unit type, status, in-place rent and turn date is an analyst assumption, and this is not the building's rent roll.

Market rent has a source. Compound used the HUD Fair Market Rent for fiscal 2027 for the New York, NY HUD Metro FMR Area, which covers ZIP 11238: $2,593 for a studio, $2,729 for a one-bedroom and $2,971 for a two-bedroom. Zillow’s ZORI for multifamily units in the New York metro was $3,516 in September 2026, which is 28.8% above the one-bedroom Fair Market Rent. Compound set market rent equal to the Fair Market Rent with no premium and wrote the reason beside the input: the lower figure is the conservative anchor. The formula in the bar picks each unit’s market rent by bedroom count from those inputs.

In-place rents are the assumption. The six free-market units average $2,350 a month, 14.4% below market. The fourteen stabilized units average $1,450, which is 46.3% below. The whole building collects $34,400 a month in place against $56,977 at market.

The turn dates decide when income grows. Unit 3D is vacant at closing, units 1C, 2C and 3A turn at lease expiry during 2027, and 4A and 4D turn in 2028. Two stabilized units, 2B and 3C, are assumed to move out in 2029 and 2030 and to re-let at the legal rent. A stabilized turn costs a month of downtime and adds no rent.

This is a different job from a rent roll analysis, which audits a seller’s rent roll against the leases. Here there is no seller’s rent roll yet, and the sheet is the frame the real one drops into.

The revenue build: one gap burns off and the other grows

Gross potential rent at market is $683,724 in Year 1 and scheduled rent is $414,896. Most of the difference is the regulatory gap on the stabilized units, $209,616, which widens every year, while the free-market loss-to-lease of $15,442 is gone by Year 3.

489_Prospect_Place_Lender_ProForma.xlsx
E20fx=-(SUMPRODUCT(RentRoll!$L$6:$L$26,RentRoll!$H$6:$H$26,RentRoll!E42:E62)*E10-SUMPRODUCT(RentRoll!$L$6:$L$26,RentRoll!$E$6:$E$26,RentRoll!E42:E62)*E11)
BEFGHI
5YearYear 1Year 2Year 3Year 4Year 5
18Gross potential rent at market (all units)683,724704,236725,363747,124769,537
20(-) Loss-to-lease, free-market units before turn(15,442)(3,127)000
21(-) Regulatory rent gap, stabilized units(209,616)(218,340)(226,056)(235,495)(246,418)
22(-) Non-revenue unit (superintendent)(32,748)(33,730)(34,742)(35,785)(36,858)
23(-) Turn downtime vacancy(11,022)(5,622)(2,895)(2,982)0
24Scheduled rent414,896443,416461,669472,862486,261
25(-) General vacancy(12,447)(13,302)(13,850)(14,186)(14,588)
26(-) Credit loss(4,149)(4,434)(4,617)(4,729)(4,863)
27(+) Other income5,0405,1415,2445,3485,455
28Effective gross income403,340430,820448,446459,296472,266
29Loss-to-lease plus regulatory gap, % of GPR32.9%31.4%31.2%31.5%32.0%
32Real estate taxes(88,665)(92,212)(95,900)(99,736)(103,726)
42Total operating expenses(244,999)(248,701)(254,620)(263,113)(269,158)
43Net operating income158,341182,120193,826196,183203,108
44Operating expenses per unit$11,667$11,843$12,125$12,529$12,817
45Expense ratio (% of EGI)60.7%57.7%56.8%57.3%57.0%
49Replacement reserves(5,250)(5,250)(5,250)(5,250)(5,250)
52Cash flow before debt service153,091176,870188,576190,933197,858
SummaryAssumptionsCompsRentRollProFormaDebtReturnsSensitivityOpexCheckSources
Compound's ProForma tab, Years 1 to 5: the revenue build, then real estate taxes, total operating expenses, net operating income, reserves and cash flow before debt service. The nine other expense lines are on the sheet. Costs are in parentheses.

The build starts with every unit at market rent and subtracts what the building does not collect. The formula in the bar is the free-market loss-to-lease for Year 1. It multiplies each free-market unit’s market rent by the months before that unit turns, and subtracts the in-place rent for the same months. Four free-market units turn in Year 1 and two in Year 2, so the line falls from $15,442 to $3,127 and then to zero.

The regulatory gap moves the other way. Stabilized renewals get 0% in Year 1 under New York City Rent Guidelines Board Order #58. From Year 2 Compound assumed 2% a year, against 3% for market rent. The gap grows from $209,616 in Year 1 to $246,418 in Year 5. Together the two gaps take 32.9% of gross potential rent in Year 1 and 32.0% in Year 5.

Net operating income is $158,341 in Year 1, $182,120 in Year 2 and $193,826 in Year 3. After the free-market units have turned, growth slows to $196,183 in Year 4 and $203,108 in Year 5. That shape matters for the debt, because the lender sizes on Year 1.

Operating expenses, checked against CMBS filings

Year-1 operating expenses are $244,999, which is $11,667 a unit and 60.7% of effective gross income. Per unit that is 19.6% above the $9,755 median of 25 prewar Brooklyn walk-ups in CMBS filings, and inside the middle half of that set.

Real estate taxes are the largest line and the only expense with a source for this building. Compound multiplied the assessed value of $712,800, from the city’s PLUTO file, by the 12.439% Class 2 tax rate published by the Department of Finance. That gives $88,665, or $4,222 a unit and 36.2% of operating expenses. The other lines are analyst assumptions, each with a reason on the Assumptions sheet, such as insurance at $1,300 a unit and management at 4% of effective gross income.

The request asked for a check against small New York City apartment properties in the CMBS loan-level filings. Compound pulled the latest twelve-month operating statements for 25 prewar Brooklyn walk-ups of 8 to 40 units and listed each one on the OpexCheck sheet with its SEC filing number.

Operating expenses per unit: the model against 25 CMBS-financed Brooklyn walk-ups

Dollars per unit per year, taxes and management included. CMBS statements end between June 2024 and June 2026

Model, Year 1$11,667CMBS set: 25th percentile$8,151CMBS set: median$9,755CMBS set: average$13,371CMBS set: 75th percentile$17,207

Compound's OpexCheck tab, from SEC Form ABS-EE asset data files.

The model’s $11,667 sits at the 61st percentile of the set, between the median and the 75th percentile of $17,207. On expenses per unit the assumptions hold up.

The same sheet shows something the expense check was not asked to find. The model’s revenue is $19,207 a unit, and the CMBS set’s 25th percentile is $31,502. The model’s expense ratio is 60.7%, and the set’s median is 23.8%. Brooklyn buildings that secure CMBS loans collect far more rent per unit than this rent roll assumes. The per-unit expense check says little about the income side, and the income side is where the model is most exposed.

Debt sizing: the largest loan each lender would make

Each lender’s largest loan is the smallest of three tests: loan-to-value, debt service coverage and the program cap. On Year-1 net operating income of $158,341, coverage is the smallest for all three lenders, and the largest loan is Freddie Mac’s $1,588,135.

489_Prospect_Place_Lender_ProForma.xlsx
C19fx=C16/(C15*C13)
BCDE
5LoanFreddie Mac SBLLocal bankDSCR loan
6Fixed-rate index5.15%4.98%7.40%
7Spread over index2.25%2.00%1.25%
9Fixed interest rate7.40%6.98%8.65%
10Loan term (years)101030
12Amortization (years)302530
13Annual debt constant at fixed rate8.31%8.47%9.35%
14Maximum LTV80%70%75%
15Minimum DSCR1.20x1.25x1.15x
16Underwriting NOI (Year 1)$158,341$158,341$158,341
17Purchase price$2,600,000$2,600,000$2,600,000
18Maximum loan – LTV test$2,080,000$1,820,000$1,950,000
19Maximum loan – DSCR test$1,588,135$1,496,248$1,471,840
20Maximum loan – program cap$6,000,000$1,820,000$1,950,000
21Largest loan the lender would make$1,588,135$1,496,248$1,471,840
22Binding constraintDSCRDSCRDSCR
24Requested loan (requested LTV x price)$1,820,000$1,820,000$1,820,000
25Loan amount$1,588,135$1,496,248$1,471,840
26LTV at closing61.1%57.5%56.6%
29Year-1 DSCR at loan amount1.20x1.25x1.15x
30Debt yield (Year-1 NOI / loan)10.0%10.6%10.8%
SummaryAssumptionsCompsRentRollProFormaDebtReturnsSensitivityOpexCheckSources
The loan sizing block from Compound's Debt tab, without the fee and recording tax rows. For the bank and DSCR loans the Assumptions sheet has no program cap, so that row repeats the loan-to-value test. Below this block the sheet carries an annual schedule for each loan.

The three loans are priced from the market close of November 13, 2026.

  • Freddie Mac small-balance loan. The 10-year Treasury par yield of 5.15% plus an assumed 225 basis point spread gives 7.40%, fixed for 10 years. The 80% maximum loan-to-value and the 1.20x minimum coverage come from Freddie Mac’s public term sheet, which is dated October 2016.
  • Local bank loan. The 5-year Treasury yield of 4.98% plus 200 basis points gives 6.98%, fixed for five years and floating over SOFR after that. The 70% loan-to-value and 1.25x coverage are analyst assumptions.
  • DSCR loan. The request asked for a loan priced above the agency loan. Compound set it 125 basis points over the Freddie Mac rate, at 8.65%, with 75% loan-to-value and 1.15x coverage, all analyst assumptions.

The formula in the bar is the coverage test. It divides net operating income by the product of the minimum coverage and the annual debt constant, which is twelve monthly payments on a dollar of loan. Freddie Mac’s constant is 8.31%, so $158,341 supports $1,588,135. The loan-to-value tests would allow $2,080,000, $1,820,000 and $1,950,000.

Largest loan under each lender's coverage test, against the assumed 70% loan

Year-1 NOI of $158,341 over minimum coverage times the loan's annual debt constant

Loan at the assumed 70% of price$1,820,000Freddie Mac SBL, 1.20x test$1,588,135Local bank, 1.25x test$1,496,248DSCR loan, 1.15x test$1,471,840

Compound's Debt tab. Rates: Daily Treasury Par Yield Curve, close of November 13, 2026, plus analyst-assumed spreads.

The loans close at 61.1%, 57.5% and 56.6% of the price. The DSCR loan has the loosest coverage test and still makes the smallest loan, because its 9.35% constant is the highest.

The Freddie Mac loan is $231,865 short of the $1,820,000 that 70% of the price would be. To reach that loan at 1.20x, the building would need net operating income of $181,459, and the model’s Year-2 figure is $182,120. A lender sizes on income in place at closing, and the buyer carries the difference in equity until the free-market units have turned.

A multifamily acquisition pro forma for a large stabilized asset carries one loan. A buyer at this size chooses among lender types with different tests, fees and prepayment terms, so the model needs all three side by side and a switch.

Returns under each loan: the exit case and the refinance case

Unlevered, the model returns an 8.3% IRR and a 1.43x multiple over five years. The bank loan lifts the IRR to 8.7%, the Freddie Mac loan lowers it to 7.7% and the DSCR loan lowers it to 6.7%.

489_Prospect_Place_Lender_ProForma.xlsx
C35fx=Returns!C31
BCDE
27LoanFreddie Mac SBLLocal bankDSCR loan
28Interest rate (fixed period)7.40%6.98%8.65%
29Largest loan the lender would make ($)$1,588,135$1,496,248$1,471,840
30Binding constraintDSCRDSCRDSCR
31Loan amount, lesser of maximum and requested LTV ($)$1,588,135$1,496,248$1,471,840
32LTV at closing61.1%57.5%56.6%
33Equity at closing ($)$1,124,214$1,208,868$1,243,449
34Year-1 DSCR1.20x1.25x1.15x
35Exit case – levered IRR7.7%8.7%6.7%
36Exit case – equity multiple1.42x1.48x1.36x
37Exit case – average cash-on-cash4.4%4.5%3.5%
38Exit case – minimum DSCR1.20x1.25x1.15x
39Refinance case – levered IRR6.7%8.1%6.0%
40Refinance case – equity multiple1.32x1.38x1.28x
41Refinance case – average cash-on-cash3.5%3.5%2.9%
42Refinance case – minimum DSCR1.20x1.25x1.15x
43Refinance case – net cash returned at refinance ($)$334,941$499,861$456,822
SummaryAssumptionsCompsRentRollProFormaDebtReturnsSensitivityOpexCheckSources
The loan comparison from Compound's Summary tab, unedited. Each cell is a formula that reads the Debt or Returns tab.

The exit case sells at the end of Year 5 at a 6.75% cap rate on Year-6 net operating income of $205,682. That gives a gross price of $3,047,141 and $2,894,784 after 5% selling costs. The exit cap rate and the selling costs are analyst assumptions.

The Freddie Mac rate of 7.40% is below the 8.3% unlevered IRR, and the levered IRR still comes out lower. The costs of the loan make the difference. The 1.0% fee is $15,881, and the mortgage recording tax, which Compound assumed at 2.8% of the loan, is $44,468. A sale in year 5 pays a 3% prepayment premium, which is $45,035 on the remaining balance (our arithmetic). The bank loan has a lower rate, a 0.75% fee and a 1% premium in year 5.

The refinance case replaces each loan at the end of Year 3. Compound valued the building at a 6.5% cap rate on Year-4 income, or $3,018,204, and sized a new loan at 7.00%. Coverage binds again: the 1.25x test allows $1,965,855 where 70% loan-to-value would allow $2,112,743. After paying off the old loan and $29,488 of costs, the refinance returns $334,941 under the Freddie Mac loan, $499,861 under the bank loan and $456,822 under the DSCR loan.

IRR over a five-year hold: unlevered, and each loan in the exit and refinance cases

Exit at a 6.75% cap rate on next-year NOI. The refinance case replaces the loan at the end of Year 3

Unlevered8.3%Freddie Mac SBL: exit case7.7%Freddie Mac SBL: refinance case6.7%Local bank: exit case8.7%Local bank: refinance case8.1%DSCR loan: exit case6.7%DSCR loan: refinance case6.0%

Compound's Summary tab. Model output on analyst assumptions, not a forecast for the property.

The refinance lowers every IRR: to 6.7%, 8.1% and 6.0%. Debt service rises from $131,951 to $156,947 under the Freddie Mac case. Compound’s summary gives the cause in one line: “the new debt costs more than the property yields.”

The sensitivity grid and the flags

With the Freddie Mac loan selected, the levered IRR runs from (0.5%) to 15.0% across the grid. The base case of 7.7% sits in the center, at a 6.75% exit cap rate and 3% market rent growth.

489_Prospect_Place_Lender_ProForma.xlsx
P50fx=XIRR(D50:O50,$D$21:$O$21)
CDEFGH
8Exit cap / growth1.0%2.0%3.0%4.0%5.0%
96.25%6.1%8.5%10.8%12.9%15.0%
106.50%4.5%6.9%9.2%11.4%13.6%
116.75%2.8%5.3%7.7%10.0%12.1%
127.00%1.2%3.8%6.2%8.5%10.7%
137.25%(0.5%)2.2%4.7%7.1%9.4%
SummaryAssumptionsCompsRentRollProFormaDebtReturnsSensitivityOpexCheckSources
The IRR grid from Compound's Sensitivity tab: exit cap rate down the side and market rent growth across the top. The formula shown is the engine row behind the center cell. Each grid cell points at one of 25 such rows.

The grid does not use Excel’s data table feature. Below it, Compound wrote 25 rows of five-year cash flows, one for each pair of exit cap rate and rent growth, and a check cell confirms the center matches the Returns tab. The growth axis moves only the free-market units after they turn, so a point of market growth reaches six units of the 21.

The Summary sheet ends with the flags the request asked for.

  • Coverage below 1.20x. The DSCR loan is flagged at 1.15x. That lender’s own minimum is 1.15x, so the flag follows from the loan’s terms. The Freddie Mac loan reads OK at exactly 1.20x, and Compound’s summary says it “has no cushion”.
  • Price per unit more than 15% above neighborhood sales. Not flagged, at 26.2% below.
  • Market rent above the Fair Market Rent without a stated reason. Not flagged. A second line notes 1 input above it with a reason given: the 5% premium for renovated units.

That premium belongs to the optional renovation budget, which is switched off in the base case. Compound’s summary reports that switching it on lowers the Freddie Mac IRR to 4.0%. A renovation plan with a larger rent premium is a different model, and the multifamily value-add pro forma page works one through.

What public records cannot tell you

Public records give the building, its sale price, its assessed value and the market around it. They do not give a single rent, lease or bill, and those come from the seller.

  • The rent roll and regulatory status. A certified rent roll, the leases, and the state rent registration history for every unit. The model’s count of 14 stabilized units is an assumption, and Compound’s summary calls it “the key thing to confirm”.
  • Operating history. A trailing twelve months and two prior years of statements, the tax bills, and the water, fuel and insurance bills. These replace nine assumed expense lines.
  • Arrears and disputes. An arrears list, housing court cases, and any rent overcharge or capital improvement filings.
  • Condition. Open violations, the status of boiler, gas and facade inspections, and a property condition report.

Two inputs have limits of their own. The Fair Market Rent is a figure for the metro area that covers the ZIP code, and the ZORI index covers the whole New York metro, so neither is a rent survey of Crown Heights. A current Freddie Mac quote would replace the 2016 terms and the assumed spread.

Our view: read it as a leverage test

We would take this model to a lender as a statement of how much debt the in-place income carries, which is 56.6% to 61.1% of the price. We would not read its returns as a view on the building until the rent roll is real.

The debt result is sturdier than the return result. Sizing depends on Year-1 net operating income, the rate and the coverage test. If the seller’s rent roll shows income close to the assumed $34,400 a month, a buyer should not expect leverage to add much. Of the three loans, the bank loan has the lowest rate, the lowest fee and the lightest prepayment terms. The return result rests on one assumption that public data cannot check. If more than six units are free-market, income, loan size and returns all rise, and if fewer are, they fall. The sensitivity grid varies rent growth and exit cap rate and holds the unit mix fixed.

What would change our view is the seller’s package: the registration history first, then the trailing statements, then a lender’s term sheet. Each one replaces a block of inputs on the Assumptions sheet, and the loan comparison updates from there. For the general method, see the real estate pro forma page.

Small multifamily lender pro forma (Excel)Compound’s workbook from this run: a Summary with the loan comparison and flags, an Assumptions sheet with a source or reason beside every input, the sale comparables, a 21-unit rent roll, the annual pro forma, the three-loan debt module, returns for exit and refinance cases, the IRR sensitivity, the CMBS expense check and a Sources sheet.Download · Free

FAQ

What is a small multifamily pro forma?

It is a projection of income, expenses, debt service and returns for an apartment building small enough that each unit is modeled on its own row. Lenders use it to size the loan, and buyers use it to see what equity the deal needs.

How does a lender size a loan on a small apartment building?

The lender applies several tests and lends the smallest result. The loan-to-value test multiplies the price or appraised value by a maximum percentage. The coverage test divides net operating income by the minimum DSCR times the annual debt constant. Here the first would allow the Freddie Mac loan $2,080,000 and the second allows $1,588,135.

What is a DSCR loan?

It is a loan underwritten mainly on the property’s income coverage, usually from a non-bank lender and at a higher rate than agency debt. Compound assumed 125 basis points over the Freddie Mac rate, which gives 8.65%, with 1.15x minimum coverage.

How is loss-to-lease different for rent-stabilized units?

Loss-to-lease on a free-market unit is the gap between its in-place rent and market rent, and it closes when the unit turns. A rent-stabilized unit re-lets at its legal rent, so its gap does not close on a turn. The workbook keeps the two on separate lines.

Why can a loan lower the return on an apartment purchase?

Borrowing lowers the return when the loan costs more than the building earns. Here the going-in cap rate is 6.09% and the lowest annual debt constant is 8.31%, so each borrowed dollar reduces first-year cash flow.

CoatueCentri ConsultingAvra783 Capital PartnersOctahedron CapitalArix ResearchCoatueCentri ConsultingAvra783 Capital PartnersOctahedron CapitalArix ResearchCoatueCentri ConsultingAvra783 Capital PartnersOctahedron CapitalArix Research

See this workflow on your own deal

Try it now