A new 10-year loan on this apartment property costs 6.80%. The property earns 6.43% on the purchase price. Debt that costs more than the going-in cap rate is negative leverage, and the model flags it. The same model returns a 15.6% levered IRR against 11.5% unlevered, because the renovation plan lifts net operating income from $2.67 million to $3.97 million by Year 4.
You are on the acquisitions team of an institutional apartment investor and you want a value-add pro forma on a real building before a seller has sent anything. We asked Compound to pick a property from the CMBS loan-level filings and build the model from public data. It ran on November 15, 2026 and returned one Excel workbook and a short summary.
Compound picked Autumn Run Apartments in Naperville, Illinois, in the Chicago metro. The property secures loan 13 of BBCMS Mortgage Trust 2021-C12, and the trust’s Form ABS-EE reports 320 units built in 1986. The request set the purchase price at the latest appraised value, and the latest appraisal in the filing is $41.6 million, dated Sep 3, 2021. That price is an assumption of the exercise. The appraisal was made when the loan was securitized, and nothing here 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:
- The base case is a 15.6% levered IRR and a 3.61x equity multiple over ten years. The unlevered IRR is 11.5%, and peak equity is $17.1 million.
- One of the three flags trips. The new loan’s 6.80% rate is 37 basis points above the 6.43% going-in cap rate. The DSCR and yield-on-cost flags both read OK.
- Rent is in three layers. In-place rent is $1,373 a month, unrenovated market rent is $1,548 and renovated rent is $1,773.
- The loan in place costs 3.565%. It is interest-only until Nov 1, 2031. A new loan at 65% of the price carries $1,864,258 of annual debt service against $818,247 today.
Each input in the workbook names its source or says “analyst assumption”. Property income, occupancy, the appraisal and the existing loan come from CMBS loan-level filings on SEC EDGAR. Rents and vacancy come from Zillow’s ZORI index, Apartment List and HUD Fair Market Rents. The per-unit cost check uses Schedule III of a REIT’s 10-K. Debt is priced off the Daily Treasury Par Yield Curve and the SOFR forward curve, which Compound builds from New York Fed SOFR and SOFR swap trades.
“I am underwriting a value-add apartment acquisition and want a multifamily value-add pro forma built on a real property. From the CMBS loan-level filings, pick one U.S. garden or mid-rise apartment property of roughly 150 to 350 units, built before 2005, whose latest report gives units, occupancy, net operating income, revenue, operating expenses, appraised value and the loan terms, in a metro where asking rents are rising. Use that property as the subject and its latest appraised value as the purchase price. Set in-place rent from its reported revenue and occupancy. Set market rent for unrenovated and renovated units from Zillow ZORI and Apartment List rent and vacancy for its metro and HUD Fair Market Rents for its ZIP code or metro. Check the all-in cost per unit against what public apartment REITs carry buildings at in the same metro (Schedule III). Price new acquisition debt off today's 10-year Treasury yield and the SOFR forward curve, and show it next to the loan the property has now. Build: sources and uses; a renovation schedule (units per month, downtime, cost per unit); rent tracked separately as loss-to-lease burn-off and renovation premium; a 10-year monthly cash flow rolled up to annual; the debt schedule with interest-only period and DSCR by year; sale at the year-11 NOI over an exit cap rate; levered and unlevered IRR, equity multiple, cash-on-cash, yield on cost and peak equity; and a sensitivity table of levered IRR to renovation rent premium and exit cap rate. Flag negative leverage, a DSCR below 1.25x in any year, and a yield on cost less than 100 basis points above the going-in cap rate. 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.”
The subject property and what the CMBS filing reports
The filing gives a half year of income, so in-place NOI is an annualized figure. Compound annualized the first half of 2026 with the property’s own 2025 seasonal pattern and got $2,673,773, a 6.43% cap rate on the price.
The Form ABS-EE for the Oct 13, 2026 reporting period, filed Oct 29, 2026, reports the property for January 1 to June 30, 2026: 95.94% occupancy, revenue of $2,626,146, operating expenses of $1,261,798 and NOI of $1,364,348.
Compound pulled two earlier filings from the same trust to measure how the two halves of a year differ. Full-year 2025 revenue was $4,933,903 and first-half 2025 revenue was $2,382,585, so the full year was 1.0354x twice the first half. The same ratio for expenses is 1.0955x. Applying both factors gives annualized revenue of $5,438,275, expenses of $2,764,502 and in-place NOI of $2,673,773. A plain doubling of first-half NOI would give $2,728,696 (our arithmetic).
| B | C | D | E | |
|---|---|---|---|---|
| 5 | Input | Value | Unit | Source / rationale |
| 9 | Units | 320 | units | BBCMS Mortgage Trust 2021-C12, Form ABS-EE Ex-102, Oct 13, 2026 reporting period, loan 13 |
| 11 | Latest appraised value (MAI) | 41,600,000 | $ | BBCMS Mortgage Trust 2021-C12, Form ABS-EE Ex-102, Oct 13, 2026 reporting period, loan 13 |
| 12 | Appraisal date | Sep 3, 2021 | date | BBCMS Mortgage Trust 2021-C12, Form ABS-EE Ex-102, Oct 13, 2026 reporting period, loan 13 |
| 16 | Physical occupancy, latest report | 95.94% | % | BBCMS Mortgage Trust 2021-C12, Form ABS-EE Ex-102, Oct 13, 2026 reporting period, loan 13 |
| 17 | Revenue, latest period (H1 2026) | 2,626,146.46 | $ | BBCMS Mortgage Trust 2021-C12, Form ABS-EE Ex-102, Oct 13, 2026 reporting period, loan 13 |
| 18 | Operating expenses, latest period (H1 2026) | 1,261,798.38 | $ | BBCMS Mortgage Trust 2021-C12, Form ABS-EE Ex-102, Oct 13, 2026 reporting period, loan 13 |
| 19 | Net operating income, latest period (H1 2026) | 1,364,348.08 | $ | BBCMS Mortgage Trust 2021-C12, Form ABS-EE Ex-102, Oct 13, 2026 reporting period, loan 13 |
| 20 | Revenue, FY2025 | 4,933,903.30 | $ | BBCMS 2021-C12 ABS-EE Ex-102, Mar 11, 2026 reporting period, loan 13 |
| 21 | Operating expenses, FY2025 | 2,735,653.74 | $ | BBCMS 2021-C12 ABS-EE Ex-102, Mar 11, 2026 reporting period, loan 13 |
| 22 | Revenue, H1 2025 | 2,382,585.20 | $ | BBCMS 2021-C12 ABS-EE Ex-102, Aug 11, 2025 reporting period, loan 13 |
| 23 | Operating expenses, H1 2025 | 1,248,631.32 | $ | BBCMS 2021-C12 ABS-EE Ex-102, Aug 11, 2025 reporting period, loan 13 |
| 24 | Revenue annualization factor (FY2025 / 2 x H1 2025) | 1.0354x | x | Calculated: second-half seasonality seen in 2025, applied to the H1 2026 run rate |
| 25 | Opex annualization factor (FY2025 / 2 x H1 2025) | 1.0955x | x | Calculated: expenses run heavier in the second half (FY2025 vs H1 2025) |
| 26 | In-place revenue, annualized | 5,438,275 | $ | Calculated: H1 2026 revenue annualized with the 2025 seasonality factor |
| 27 | In-place operating expenses, annualized | 2,764,502 | $ | Calculated |
| 28 | In-place NOI, annualized | 2,673,773 | $ | Calculated; going-in NOI used for the cap rate and loan sizing |
| 29 | Other income share of revenue | 7.00% | % | Analyst assumption: pet, parking, utility reimbursement and fees typically run 5-10% of revenue at suburban garden assets; seller T-12 needed |
| 30 | In-place rent per occupied unit | 1,372.82 | $/unit/mo | Calculated from reported H1 2026 revenue (seasonality-adjusted, row 24), months and occupancy, net of other income |
| 31 | Other income per occupied unit | 103.33 | $/unit/mo | Calculated |
First-half 2026 revenue is 10.2% above first-half 2025, while expenses rose 1.1% (our arithmetic). Full-year 2025 NOI was $2,198,250, and the annualized in-place figure is 21.6% above it (our arithmetic). The 6.43% cap rate depends on the 2026 run rate holding.
In-place rent is a derived figure. The filing reports revenue and occupancy and no rent roll, so Compound backed into rent per occupied unit: annualized revenue, less a 7.00% share for other income, divided by occupied units. The result is $1,373 a month. It is collected revenue per occupied unit, after concessions and bad debt, so contract rent is likely higher and the loss-to-lease smaller. The 7.00% is an analyst assumption, and the sheet gives the reason: pet, parking, utility reimbursement and fee income typically runs 5-10% of revenue at suburban garden properties.
In-place rent, market rent and loss-to-lease
The model sets unrenovated market rent at $1,548 and renovated rent at $1,773, against $1,373 in place. Both market figures are built from HUD Fair Market Rents and checked against Zillow and Apartment List, and each step is a labeled assumption.
Public rent indexes describe the typical unit in an area, which is newer than a 1986 building. ZORI for multifamily in the Chicago metro was $2,063 in September 2026, up 4.9% in a year. ZORI for all homes in ZIP 60563 was $2,095, up 1.7%. Apartment List’s median rent for Naperville was $2,107 in November 2026, up 1.6%.
Compound started from HUD’s FY2027 Fair Market Rents for DuPage County, $1,780 for a one-bedroom and $2,011 for a two-bedroom. It blended them at a 55% one-bedroom share and took off a $125 utility allowance, because Fair Market Rent includes utilities, to reach $1,759. A 12% discount for 1986 finishes and small units gives $1,548. The unit mix, the allowance and the discount are analyst assumptions. As a check, a current listing for the property found on the web asks $1,400 for a one-bedroom.
| B | C | D | E | |
|---|---|---|---|---|
| 70 | MARKET RENT AND VACANCY | |||
| 71 | ZORI multifamily, Chicago-Naperville-Elgin CBSA, Sep 2026 | 2,063.25 | $/mo | Zillow Observed Rent Index (ZORI), smoothed, seasonally adjusted, multifamily |
| 73 | ZORI multifamily YoY, Chicago CBSA | 4.85% | % | Calculated |
| 74 | ZORI all homes, ZIP 60563, Sep 2026 | 2,095.26 | $/mo | Zillow Observed Rent Index (ZORI), smoothed, seasonally adjusted, all homes plus multifamily (no multifamily series at ZIP level) |
| 76 | ZORI YoY, ZIP 60563 | 1.74% | % | Calculated |
| 80 | Apartment List median rent, Naperville, Nov 2026 | 2,107 | $/mo | Apartment List Naperville rent report (Nov 2026), apartmentlist.com |
| 81 | Apartment List rent growth YoY, Naperville | 1.60% | % | Apartment List Naperville rent report (Nov 2026) |
| 87 | Apartment List vacancy index, DuPage County, Oct 2026 | 5.93% | % | Apartment List vacancy index (stored public data) |
| 89 | Apartment List concession rate, DuPage County, Oct 2026 | 15.99% | % | Apartment List concession rate (stored public data) |
| 91 | HUD Fair Market Rent FY2027, DuPage County, 1BR | 1,780 | $/mo | HUD Fair Market Rents FY2027 (gross rent incl. utilities) |
| 92 | HUD Fair Market Rent FY2027, DuPage County, 2BR | 2,011 | $/mo | HUD Fair Market Rents FY2027 (gross rent incl. utilities) |
| 94 | Subject asking rent, 1BR (current listing) | 1,400 | $/mo | Rentable.co listing for Autumn Run Apartments (web; corroborates the unrenovated rent) |
| 95 | Unit mix: 1BR share | 55.00% | % | Analyst assumption: property is 1BR and 2BR only per listings; seller rent roll needed |
| 96 | Tenant-paid utility allowance | 125 | $/mo | Analyst assumption: FMR is gross rent incl. utilities; tenants pay electric here |
| 97 | HUD FMR blended to unit mix, net of utilities | 1,759 | $/mo | Calculated |
| 98 | Vintage and finish discount to FMR, unrenovated | 12.00% | % | Analyst assumption: 1986 finishes and small units; puts unrenovated rent near the subject's own $1,400+ 1BR asking rent |
| 99 | Unrenovated market rent (at close) | 1,548 | $/unit/mo | Calculated from HUD FMR |
| 100 | Renovated market rent (at close) | 1,773 | $/unit/mo | Calculated: unrenovated market rent plus renovation premium |
| 101 | Naperville market anchor (average of ZORI ZIP and Apartment List) | 2,101 | $/mo | Calculated |
| 102 | Renovated rent as % of Naperville anchor | 84.38% | % | Calculated check: renovated units still price below the typical Naperville rental |
| 103 | Loss-to-lease at close | 11.31% | % | Calculated: in-place rent vs unrenovated market rent |
| 104 | Loss-to-lease burn-off period | 18 | months | Analyst assumption: 12-month leases roll to market with renewal caps over about 1.5 lease cycles |
| 105 | Economic vacancy | 5.93% | % | Linked to the Apartment List DuPage vacancy index (above the 4.1% physical vacancy in place) |
| 106 | Concessions and bad debt (% of rent) | 1.50% | % | Analyst assumption: 16% of DuPage listings offer a free month (row 89) |
| 107 | Market rent growth, Year 1 | 1.67% | % | Calculated: average of ZORI ZIP 60563 and Apartment List Naperville YoY |
| 108 | Market rent growth, Years 2-11 | 3.00% | % | Analyst assumption: between Naperville (1.6-1.7%) and Chicago metro multifamily ZORI (4.9%) growth |
Renovated rent is the unrenovated rent plus a $225 premium, or $1,773. Compound tested that against a Naperville anchor of $2,101, the average of the ZIP-level ZORI and the Apartment List median. Renovated rent is 84% of the anchor.
Monthly rent: the subject's three rent levels against the public benchmarks
Dollars per unit per month. Model rents are in today's dollars
Compound workbook, Assumptions sheet: CMBS filing, HUD, Zillow ZORI, Apartment List and a current listing
Loss-to-lease is one minus in-place rent over unrenovated market rent: 11.3%, or $175 a unit a month (our arithmetic). The premium is 14.5% of unrenovated rent (our arithmetic). Loss-to-lease is rent a new owner collects by renewing leases at market, with no capital. The premium has to be bought with a renovation. A model that merges them credits the renovation with rent the building would have reached anyway. For that tie-out on a real rent roll, see rent roll analysis.
Economic vacancy is 5.93%, linked to Apartment List’s vacancy index for DuPage County and above the 4.1% physical vacancy in place. Concessions and bad debt are 1.50%. Market rent grows 1.67% in Year 1 and 3.00% a year after that. Apartment List’s concession rate for DuPage County is 15.99% against 4.87% for the metro, which is a reason to watch the 1.50% line.
Sources and uses and the all-in cost per unit
Total uses are $47,332,800, and the purchase price is 87.9% of that. Equity at close is $15,870,400, and the renovation is paid after close from operating cash flow and equity calls.
| Line | Amount | Per unit | Share of total |
|---|---|---|---|
| Purchase price | $41,600,000 | $130,000 | 87.9% |
| Closing costs | $624,000 | $1,950 | 1.3% |
| Acquisition fee | $416,000 | $1,300 | 0.9% |
| Loan fees and rate cap | $270,400 | $845 | 0.6% |
| Renovation budget (interior, contingency, exterior) | $4,422,400 | $13,820 | 9.3% |
| Total uses | $47,332,800 | $147,915 | 100.0% |
| New acquisition loan | $27,040,000 | $84,500 | 57.1% |
| Equity at close | $15,870,400 | $49,595 | 33.5% |
| Renovation funded after close (operating cash flow and equity calls) | $4,422,400 | $13,820 | 9.3% |
| Total sources | $47,332,800 | $147,915 | 100.0% |
Source: Compound workbook, Summary sheet, rows 20 to 30.
The renovation budget is both a use and a source because none of it is funded at close. The building pays for the work, and the model calls equity in any month where cash flow after debt service is negative. Those calls total $1,258,422 (our arithmetic) and bring equity contributed to $17,128,822. Peak equity outstanding is $17,111,315.
The model carries two cost figures. Total uses come to $147,915 a unit. The all-in cost on the Annual sheet, used for yield on cost and the REIT comparison, is $47,062,400 or $147,070 a unit. The $270,400 difference is the loan fees and rate cap (our arithmetic).
For the check against public REITs, Compound used the Schedule III in Aimco’s FY2025 10-K, which lists seven Chicago-area properties with 1,495 units. Their gross carrying cost is $196,047 a unit, and the subject’s $147,070 is 75% of it. Their net carrying value after depreciation is $78,522 a unit. The 10-K also reports the seven properties under contract for a gross sales price of $455 million, or $304,348 a unit, and the subject’s all-in cost is 48% of that.
Cost per unit: the subject against a public REIT's Chicago-area properties
Dollars per unit. REIT figures cover seven properties and 1,495 units
Compound workbook, Summary and Assumptions sheets: CMBS filing and Aimco FY2025 10-K, Schedule III
We read the gross carrying cost as the useful line. Five of the seven properties were acquired between 1997 and 2004, so net carrying value mostly measures depreciation, and the contract price covers properties in Evanston, Elmhurst and Chicago that differ from a 1986 garden community.
The renovation schedule: units per month, downtime and cost
The plan renovates 256 of 320 units at 8 starts a month from month 3, with one month of downtime per unit. Interior cost is $14,000 a unit plus a 10% contingency, and the last renovated unit comes back in month 35.
Every number in the renovation program is an analyst assumption with a reason beside it. The 80% share leaves 64 units unrenovated. The pace of 8 a month is below natural turnover, which the sheet puts at about 12 units a month at 45% annual turnover, so units are renovated as they turn. Exterior and common-area work is $1,500 a unit across all 320 units, spent over the first 12 months.
Interior cost with contingency is $15,400 a unit (our arithmetic). A $225 monthly premium on that cost is a 17.5% annual return before vacancy (our arithmetic). The total budget is $4,422,400.
| B | D | E | F | G | H | O | U | V | AA | AK | AL | AM | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 4 | Month | 1 | 2 | 3 | 4 | 5 | 12 | 18 | 19 | 24 | 34 | 35 | 36 |
| 5 | Month ending | Jan-27 | Feb-27 | Mar-27 | Apr-27 | May-27 | Dec-27 | Jun-28 | Jul-28 | Dec-28 | Oct-29 | Nov-29 | Dec-29 |
| 9 | RENOVATION | ||||||||||||
| 10 | Units started | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 0 | 0 |
| 11 | Cumulative units started | 0 | 0 | 8 | 16 | 24 | 80 | 128 | 136 | 176 | 256 | 256 | 256 |
| 12 | Units offline (downtime) | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 0 | 0 |
| 13 | Renovated units online | 0 | 0 | 0 | 8 | 16 | 72 | 120 | 128 | 168 | 248 | 256 | 256 |
| 14 | Unrenovated units in service | 320 | 320 | 312 | 304 | 296 | 240 | 192 | 184 | 144 | 64 | 64 | 64 |
| 15 | RENT | ||||||||||||
| 17 | Unrenovated market rent ($/unit/mo) | 1,548 | 1,550 | 1,552 | 1,554 | 1,556 | 1,572 | 1,595 | 1,599 | 1,619 | 1,659 | 1,663 | 1,667 |
| 18 | Renovation premium ($/unit/mo) | 225 | 225 | 226 | 226 | 226 | 228 | 232 | 232 | 235 | 241 | 242 | 242 |
| 19 | Loss-to-lease (% of market) | 10.7% | 10.1% | 9.4% | 8.8% | 8.2% | 3.8% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% |
| 20 | REVENUE | ||||||||||||
| 21 | Gross potential rent at unrenovated market | 495,320 | 496,005 | 496,691 | 497,377 | 498,065 | 502,904 | 510,392 | 511,651 | 517,992 | 530,909 | 532,219 | 533,531 |
| 22 | (+) Renovation premium | 0 | 0 | 0 | 1,807 | 3,620 | 16,448 | 27,822 | 29,750 | 39,530 | 59,809 | 61,891 | 62,043 |
| 23 | (-) Loss-to-lease | (52,906) | (49,863) | (45,641) | (41,563) | (37,631) | (14,219) | 0 | 0 | 0 | 0 | 0 | 0 |
| 24 | (-) Renovation downtime | 0 | 0 | (12,417) | (12,434) | (12,452) | (12,573) | (12,760) | (12,791) | (12,950) | (13,273) | 0 | 0 |
| 25 | (-) Economic vacancy | (26,238) | (26,459) | (26,014) | (26,403) | (26,783) | (29,212) | (31,163) | (31,350) | (32,297) | (34,247) | (35,235) | (35,322) |
| 26 | (-) Concessions and bad debt | (6,243) | (6,295) | (6,189) | (6,282) | (6,372) | (6,950) | (7,414) | (7,459) | (7,684) | (8,148) | (8,383) | (8,404) |
| 27 | Net rental income | 409,933 | 413,388 | 406,429 | 412,503 | 418,447 | 456,398 | 486,877 | 489,800 | 504,591 | 535,051 | 550,492 | 551,849 |
The formula in the bar is the schedule: a month starts the lesser of 8 units and the units still to do. That is 80 units in Year 1, 96 in Year 2 and 80 in Year 3 (our arithmetic). A unit started in one month is offline that month and online as renovated the next.
Three things in these rows are worth checking in any value-add model.
- Downtime is a revenue line. A unit under renovation earns nothing, and the model deducts its unrenovated market rent. Downtime costs $409,527 over the program (our arithmetic).
- The premium grows with the market. The $225 is in today’s dollars. It is $226 in month 3 and $242 by month 35, because the premium row grows at the market rent growth rate.
- Loss-to-lease applies only to unrenovated units. A renovated unit is re-leased at renovated market rent, so it carries no loss-to-lease.
If you only need to know whether a renovation clears a return hurdle, the value-add multifamily screen treats it as one lump sum on one sheet. The monthly schedule earns its place when the timing of capital and rent matters: for equity calls, for DSCR in the first two years and for an interest-only period.
The 10-year cash flow: loss-to-lease burn-off apart from the renovation premium
Loss-to-lease burns off in 18 months and costs $393,230 in Year 1 and $32,967 in Year 2. The renovation premium builds for three years and adds $756,566 in Year 4, the first full year after the program.
The Monthly sheet runs 132 months so that the sale in month 120 can be priced on the next twelve months of NOI. The Annual sheet rolls the months up with SUMIFS on the year row.
| B | D | E | F | G | H | I | J | K | L | M | N | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 4 | Year | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | Year 9 | Year 10 | Year 11 |
| 5 | Year ending | Dec-27 | Dec-28 | Dec-29 | Dec-30 | Dec-31 | Dec-32 | Dec-33 | Dec-34 | Dec-35 | Dec-36 | Dec-37 |
| 7 | Renovated units online (year-end) | 72 | 168 | 256 | 256 | 256 | 256 | 256 | 256 | 256 | 256 | 256 |
| 8 | Unrenovated market rent, December ($/unit/mo) | 1,572 | 1,619 | 1,667 | 1,717 | 1,769 | 1,822 | 1,877 | 1,933 | 1,991 | 2,051 | 2,112 |
| 9 | Gross potential rent at unrenovated market | 5,989,243 | 6,132,476 | 6,316,450 | 6,505,944 | 6,701,122 | 6,902,156 | 7,109,221 | 7,322,497 | 7,542,172 | 7,768,437 | 8,001,490 |
| 10 | (+) Renovation premium | 81,938 | 346,079 | 629,971 | 756,566 | 779,263 | 802,641 | 826,720 | 851,521 | 877,067 | 903,379 | 930,480 |
| 11 | (-) Loss-to-lease | (393,230) | (32,967) | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 12 | (-) Renovation downtime | (124,948) | (153,312) | (131,268) | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 13 | (-) Economic vacancy | (329,332) | (373,176) | (404,186) | (430,717) | (443,639) | (456,948) | (470,656) | (484,776) | (499,319) | (514,299) | (529,728) |
| 14 | (-) Concessions and bad debt | (78,355) | (88,787) | (96,165) | (102,477) | (105,551) | (108,718) | (111,979) | (115,339) | (118,799) | (122,363) | (126,034) |
| 15 | Net rental income | 5,145,317 | 5,830,314 | 6,314,804 | 6,729,316 | 6,931,195 | 7,139,131 | 7,353,305 | 7,573,904 | 7,801,121 | 8,035,155 | 8,276,209 |
| 16 | (+) Other income | 368,261 | 375,475 | 388,412 | 408,555 | 420,811 | 433,436 | 446,439 | 459,832 | 473,627 | 487,836 | 502,471 |
| 17 | Effective gross income ($) | 5,513,578 | 6,205,789 | 6,703,216 | 7,137,870 | 7,352,006 | 7,572,567 | 7,799,744 | 8,033,736 | 8,274,748 | 8,522,990 | 8,778,680 |
| 21 | Total operating expenses ($) | (2,867,214) | (2,969,034) | (3,067,443) | (3,166,473) | (3,261,467) | (3,359,311) | (3,460,091) | (3,563,893) | (3,670,810) | (3,780,934) | (3,894,362) |
| 22 | Net operating income ($) | 2,646,364 | 3,236,754 | 3,635,773 | 3,971,397 | 4,090,539 | 4,213,255 | 4,339,653 | 4,469,843 | 4,603,938 | 4,742,056 | 4,884,318 |
| 23 | NOI margin | 48.0% | 52.2% | 54.2% | 55.6% | 55.6% | 55.6% | 55.6% | 55.6% | 55.6% | 55.6% | 55.6% |
| 26 | (-) Interior renovation capex | (1,232,000) | (1,478,400) | (1,232,000) | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 27 | (-) Exterior and common-area capex | (480,000) | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 28 | Cash flow after capex ($) | 837,051 | 1,658,122 | 2,300,534 | 3,865,061 | 3,981,013 | 4,100,443 | 4,223,456 | 4,350,160 | 4,480,665 | 4,615,085 | 4,753,537 |
Gross potential rent is all 320 units at unrenovated market rent. The premium line adds the renovated units, and loss-to-lease, downtime, vacancy and concessions come off. Because the burn-off and the premium are separate rows, you can see that most of the first-year gain is from leases rolling to market and most of the later gain is from renovated units.
Loss-to-lease falls in a straight line to zero at month 18. The 18 months is an analyst assumption, and this is the one we would test first. Renewal increases of this size can bring turnover with them, and the base case holds the vacancy line flat through the burn-off.
In-place expenses grow 3.00% a year, the management fee is 3.00% of effective gross income, and Year 1 adds $64,000 of property tax for reassessment toward the purchase price. NOI margin rises from 48.0% in Year 1 to 55.6% in Year 4 and then holds.
Net operating income by year
Year 4 is the first full year after the renovation. The sale in Year 10 is priced on Year 11
Compound workbook, Annual sheet row 22 and Assumptions sheet
Year 1 NOI is $2.65 million, slightly below the in-place figure, because downtime, the vacancy allowance and the tax add-on offset the first year of burn-off. Stabilized NOI in Year 4 is $3,971,397, which is 48.5% above in-place NOI (our arithmetic).
Acquisition debt next to the loan in place
The new loan is $27,040,000 at 6.80% fixed, sized by the 65% loan-to-value limit, with 36 months interest-only. The loan the property has now is $22,700,000 at 3.565%, interest-only to its maturity on Nov 1, 2031.
Compound priced the fixed loan at the 10-year Treasury yield of 5.15% at the close of November 13, 2026 plus a 165 basis point spread. The Treasury yield is sourced. The spread and the three sizing limits are analyst assumptions. The loan is the lowest of three tests: $27,040,000 on loan-to-value, $27,342,329 on a 1.25x DSCR and $33,422,161 on an 8.00% debt yield.
| B | C | D | |
|---|---|---|---|
| 53 | DEBT: EXISTING LOAN VS NEW ACQUISITION LOAN | Existing loan | New loan |
| 54 | Lender / pricing basis | BBCMS 2021-C12 (SGFC), fixed | Fixed: 10Y UST + spread |
| 55 | Loan amount | 22,700,000 | 27,040,000 |
| 56 | Interest rate (Year 1) | 3.57% | 6.80% |
| 57 | Interest-only period (months) | 120 | 36 |
| 58 | Amortization (years; 0 = interest only) | 0 | 30 |
| 59 | Maturity / term end | Nov 1, 2031 | Jan 1, 2037 |
| 60 | Annual debt service (latest period annualized / Year 1) | 818,247 | 1,864,258 |
| 61 | DSCR on in-place NOI | 3.27x | 1.43x |
| 62 | LTV on purchase price | 54.6% | 65.0% |
| 63 | Debt yield on in-place NOI | 11.8% | 9.9% |
| 64 | New-loan pricing options (Year 1) | ||
| 65 | Fixed: 10Y UST (Nov 13, 2026) + spread | 6.80% | |
| 66 | Floating: 1M SOFR forward (first reset after close) + spread, floor and cap | 7.06% |
The existing loan’s annual debt service is $818,247. The new loan’s is $1,864,258 while it is interest-only, 2.3 times as much (our arithmetic). On in-place NOI the existing loan covers 3.27x by the model’s calculation, and the new loan 1.43x.
The floating option is a switch on the Assumptions sheet. It prices at the 1-month SOFR forward, capped at a 4.50% strike, plus 275 basis points. The forward for January 2027 is 4.31%, so the Year-1 rate is 7.06%. Compound’s summary reports that the floating loan lowers the levered IRR to 14.8%.
Debt service coverage: the existing loan and the new loan by year
NOI divided by debt service. Amortization on the new loan starts in Year 4
Compound workbook, Summary sheet row 61 and Annual sheet row 34
The lowest DSCR on the new loan is 1.42x in Year 1, above the 1.25x threshold from the request. Coverage steps down from 1.95x in Year 3 to 1.86x in Year 4, when the 30-year amortization starts. A purchase with coverage below one and a floating bridge loan has a different debt problem, and the bridge-to-perm multifamily model covers it.
Returns, the three flags and the sensitivity table
The base case returns 15.6% levered and 11.5% unlevered, with a sale at $72,360,263 in Year 10. The negative leverage flag trips and the other two do not.
| Measure | Base case |
|---|---|
| Unlevered IRR (property level) | 11.5% |
| Levered IRR (equity) | 15.6% |
| Unlevered equity multiple | 2.48x |
| Levered equity multiple | 3.61x |
| Peak equity | $17,111,315 |
| Levered profit | $44,661,283 |
| Cash-on-cash, Year 1 | 4.0% |
| Cash-on-cash, average Years 1-10 | 10.7% |
| Stabilized NOI (first full year after renovation) | $3,971,397 |
| Yield on cost (stabilized NOI / all-in cost) | 8.44% |
| Exit cap rate | 6.75% |
| Gross sale price | $72,360,263 |
| Sale price per unit | $226,126 |
Source: Compound workbook, Summary sheet, rows 34 to 46. Both IRRs are XIRR on the monthly cash flows.
The sale price is Year-11 NOI of $4,884,318 over a 6.75% exit cap rate, an analyst assumption that is 32 basis points above the going-in cap rate (our arithmetic). The sale price is 1.74 times the purchase price (our arithmetic), so most of the levered profit arrives in the last month.
The model computes it as NOI less reserves less debt service, over peak equity, and reports 4.0% in Year 1. Renovation capital is outside that measure, and cash flow after capex and debt service is negative in Years 1 and 2.
The workbook tests each of the three flags in a cell.
| Flag | Status | Metric | Test |
|---|---|---|---|
| Negative leverage | FLAG | 0.37% | Year-1 debt rate minus going-in cap; flag if positive |
| DSCR below 1.25x in any year | OK | 1.42x | Minimum DSCR, Years 1-10 |
| Yield on cost under going-in cap + 100 bps | OK | 2.01% | Yield on cost minus going-in cap |
Source: Compound workbook, Summary sheet, rows 49 to 51.
The negative leverage flag trips by 37 basis points. Compound’s summary adds that leverage still raises the IRR, because the 11.5% unlevered return is above the cost of debt. Both are true. Negative leverage describes Year 1, when each borrowed dollar costs more than the property yields on it. The IRR describes ten years in which NOI grows and the rate is fixed.
Yield on cost is 8.44%, which is 201 basis points above the going-in cap rate against a threshold of 100. The spread combines the loss-to-lease burn-off, three years of market rent growth and the renovation premium. The premium alone returns 17.5% on its cost (our arithmetic).
| B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|
| 282 | Premium ↓ / Exit cap → | 15.6% | 6.25% | 6.50% | 6.75% | 7.00% | 7.25% |
| 283 | $125 | 14.6% | 14.0% | 13.5% | 13.0% | 12.5% | |
| 284 | $175 | 15.6% | 15.1% | 14.6% | 14.1% | 13.6% | |
| 285 | $225 | 16.6% | 16.1% | 15.6% | 15.1% | 14.6% | |
| 286 | $275 | 17.5% | 17.0% | 16.5% | 16.1% | 15.6% | |
| 287 | $325 | 18.5% | 18.0% | 17.5% | 17.0% | 16.6% |
Each $50 of premium is worth about 1.0 point of IRR and each 25 basis points of exit cap about 0.5 point (our arithmetic). The worst corner, a $125 premium and a 7.25% exit, is 12.5%, and the best is 18.5%. Neither axis moves the going-in cap rate or the loan rate, so no cell in the table clears the negative leverage flag.
What the public data cannot tell you
Public filings give this property’s income in four lines and its rent in none. The seller’s documents replace the model’s largest assumptions, and Compound’s summary lists them.
- A T-12 and a rent roll. These give in-place rent by unit type, the real loss-to-lease, the unit mix and other income. They replace the 7.00% other income share and the 55% one-bedroom share.
- Unit-by-unit condition and capex history. The model assumes 256 units need the full interior scope. The seller’s history shows which units are already done and what premium they achieved.
- Tax bills. The model adds $64,000 a year for reassessment.
- Operating expense detail, utility billing and payroll. The filing reports total operating expenses only.
- The existing loan documents. The prepayment or defeasance terms and any assumption rights are there. Compound’s summary notes that assuming the 3.565% loan would remove the negative leverage, at a 55% loan-to-value.
- An updated appraisal. The price in this model is a September 2021 appraisal, and every return on this page moves with the price.
The public rent data has its own limit. The ZIP-level ZORI series covers all home types and the multifamily series is for the metro, so neither is a rent for garden apartments in Naperville.
Our view, and what would change it
On these inputs the renovation earns its cost, and the return comes from NOI growth and the sale more than from leverage. We would spend diligence on the loss-to-lease and the price before the renovation budget.
The yield-on-cost spread of 201 basis points is the strongest number in the workbook. It rests on a $225 premium that leaves renovated rent at 84% of the Naperville anchor, and the sensitivity table shows 13.5% at a $125 premium with the base exit cap.
The loss-to-lease has less support. The 11.3% gap comes from a derived in-place rent and a market rent built with three assumptions, and the property’s own one-bedroom asking rent of $1,400 is close to the $1,373 in place. If the two-bedrooms ask about $1,729, the $1,548 holds (our arithmetic on the 55% mix). If they ask less, part of the first-year rent gain is not there.
At the 2021 appraisal as the price, a buyer gives up a 3.565% interest-only loan and borrows at 6.80%, so the first question for the seller is whether the loan in place can be assumed.
What would change our view: a rent roll showing in-place rent within a few percent of market, a price above the 2021 appraisal, or renovation history showing premiums below $125. For the structure of an acquisition model without a renovation program, see the real estate pro forma page.
Multifamily value-add pro forma (Excel)Compound’s workbook from this run: a Summary with sources and uses, returns, flags and the debt comparison, an Assumptions sheet with a source or reason beside every input, the Annual pro forma, the 132-month Monthly cash flow with the renovation schedule, and a Sources sheet.Download · FreeFAQ
What is the difference between loss-to-lease and a renovation premium in a value-add pro forma?
Loss-to-lease is the gap between rent in place and market rent for the unit as it is, and it closes as leases renew. A renovation premium is added rent that requires capital. In this workbook they are separate rows: loss-to-lease is 11.3% and burns off over 18 months, and the premium is $225 a month on 256 renovated units.
How do you calculate yield on cost for a multifamily value-add deal?
Divide stabilized NOI by all-in cost. Here stabilized NOI is the first full year after the renovation, Year 4, at $3,971,397. All-in cost is price, closing costs, acquisition fee and renovation, $47,062,400. The result is 8.44%. Compare it with the going-in cap rate, 6.43% here, to see what the renovation adds.
Does negative leverage rule out a value-add acquisition?
It is a warning about the first years. In this model debt at 6.80% exceeds the 6.43% going-in cap rate and the flag trips, while leverage still raises the ten-year IRR from 11.5% to 15.6% because NOI grows. Check that DSCR holds while NOI is low: the minimum here is 1.42x in Year 1.
Should the exit cap rate apply to year-10 or year-11 NOI?
A buyer at the end of year 10 pays for the income ahead, so the convention is the next twelve months. This model runs 132 months for that reason and prices the sale on Year-11 NOI of $4,884,318 at a 6.75% exit cap rate, 1.50% selling costs off the top.
Where does market rent for unrenovated and renovated units come from without a rent survey?
From public sources and stated assumptions. Compound built unrenovated rent from HUD Fair Market Rents by bedroom, less utilities and a discount for age, and checked it against the property’s own asking rent. Renovated rent adds a premium and is tested against Zillow ZORI and Apartment List rents for the area. Seller rent rolls and leasing comps replace both.





