Under hypothetical income restrictions, three of the four rent ceilings on this 264-unit apartment property sit below market rent from Year 1, and at the end of the hold the restricted value is 20.1% below the value at market rents. Priced at its 2019 appraised value, the model returns a 1.3% levered IRR over ten years. Year-1 net operating income is $1,270,814 against $1,429,035 reported for the last twelve months, and a new loan costs 6.80% against a 4.94% going-in cap rate.
You are on the acquisitions team of an institutional investor and you want to test an income-restricted apartment purchase 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 in November 2026 and returned one Excel workbook and a short summary.
Compound picked Edgewood Apartments at 4949 Stumberg Lane in Baton Rouge, Louisiana. The property secures loan 11 of CSAIL 2019-C18 Commercial Mortgage Trust, and the trust’s Form ABS-EE reports 264 units built in 1985. The request set the purchase price at the latest appraised value, and the latest appraisal in the filing is $25,700,000, dated 08/20/2019. That price is an assumption of the exercise.
One point governs how to read everything below. The filing reports the property’s income, occupancy, appraisal and loan. It does not report an income restriction, a voucher tenant or a Section 8 contract at this property. The income tiers, the rent in each tier, the voucher units and the Section 8 units are assumptions the request asked Compound to make, and the workbook labels each one. The model answers a hypothetical question: what this property would return at this price under those restrictions. 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:
- Three of four rent ceilings bind from Year 1. The ceilings at 30%, 50% and 60% of area median income are $465, $855 and $1,050 a month. Market rent is $1,077.
- At the 2019 appraised value, the base case is a 1.3% levered IRR and a 1.12x equity multiple. The unlevered IRR is 3.83%, so borrowing at 6.80% lowers the return.
- The loan is sized on the stress case and comes to 47.0% of the price. The stress case supports $12,090,151 of debt. The loan in place has a balance of $16,082,010.
- The restricted value at sale is below the purchase price. Year-11 restricted NOI at a 6.25% exit cap rate is $23,510,121. With every unit at market rent the same cap rate gives $29,430,899.
- Three flags trip. Ceilings below market rent in three tiers, voucher and Section 8 income at 26.7% of in-place revenue, and a stress-case DSCR that falls to 0.53x by Year 10.
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. Rent ceilings come from HUD’s income limits, and the voucher payment standard from HUD Fair Market Rents. Rent burden and income growth come from the Census Bureau’s American Community Survey. Market rent comes from Zillow’s ZORI index and Apartment List. The loan is priced off the Daily Treasury Par Yield Curve.
“I am underwriting the purchase of an existing apartment community that serves lower-income renters and want an affordable housing acquisition model that tests the deal with rent growth capped by income limits. From the CMBS loan-level filings, pick one U.S. apartment property of roughly 100 to 300 units whose average in-place rent, worked out from reported revenue, units and occupancy, is at or below the HUD Fair Market Rent for its ZIP code or metro, in a county where the Census ACS data shows that more than half of renter households pay over 30% of income in rent, and whose latest report gives net operating income, operating expenses, appraised value and the loan terms. Use it as the subject and its latest appraised value as the purchase price. Build a rent roll by income tier (30%, 50%, 60% and 80% of area median income) with unit counts and current rents per tier as analyst assumptions that tie to reported revenue, and the rent ceiling per tier from HUD's published income limits for the county. Each unit's rent is the lower of market rent, from Zillow ZORI and Apartment List for the metro, and its ceiling. Keep voucher and project-based Section 8 income on its own lines, with a renewal probability. Grow the ceilings at the county's five-year median income growth from the ACS data. Carry compliance cost per unit (recertification, file audits, inspections, consultants) as its own expense line with its own escalation. Price agency debt off today's 10-year Treasury yield plus a spread, sized on a stress-case NOI, and show it next to the loan the property has now. Build: the tiered rent roll; the subsidy panel; a 10-year pro forma with rent by tier capped each year; the expense ratio with and without compliance cost; loan sizing; a sale that shows restricted value beside unrestricted value and the gap; levered IRR, equity multiple and cash-on-cash; and sensitivity tables to income-limit growth, voucher renewal and compliance cost growth. Flag a tier whose ceiling binds from year one, voucher income above 25% of revenue, and a DSCR below 1.20x in the stress case. 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.”
That is the request as we sent it, without its closing line, which asks that no name or email be put in the workbook.
Choosing the property: three screen tests on public data
Edgewood Apartments passes all three tests the request set. Its reported rent is below the HUD Fair Market Rent, more than half of the parish’s renters are rent burdened, and it has 264 units.
The Form ABS-EE for the reporting period ended Oct 13, 2026 covers the twelve months from Jul 1, 2025 to Jun 30, 2026. It reports revenue of $3,153,915, operating expenses of $1,724,880, NOI of $1,429,035 and occupancy of 96.6%.
The filing has no rent roll, so Compound derived the rent. Revenue divided by twelve months, 264 units and 96.6% occupancy gives $1,031 per occupied unit per month. The filing has no unit mix either. Compound assumed an average of 1.5 bedrooms and blended the FY2027 Fair Market Rents for East Baton Rouge Parish, $1,014 for a one-bedroom and $1,149 for a two-bedroom, to $1,082. The in-place figure is below it, and the test reads PASS.
The rent-burden test uses ACS table B25070 for 2020-24. Of 72,194 renter households in the parish, 6,809 have no computed ratio. Of the rest, the households paying 30% or more of income number 5,767, 4,899, 6,004 and 20,481 across the four brackets, which is 56.8%.
Which inputs are sourced and which are analyst assumptions
The property, the loan, the HUD limits, the Census data and the Treasury yield are sourced. The tier mix, the tier rents, the subsidy units, the compliance cost and the debt spread are analyst assumptions, and they drive the result.
| Input | Value | Source or basis in the workbook |
|---|---|---|
| Units, year built, revenue, expenses, NOI, occupancy | 264 units, 1985, $1,429,035 NOI | CSAIL 2019-C18 Form ABS-EE, loan 11 |
| Appraised value and date | $25,700,000, 08/20/2019 | Same filing |
| Existing loan balance, rate, maturity | $16,082,010, 4.09%, 10/06/2029 | Same filing |
| HUD Fair Market Rents, FY2027 | $1,014 one-bedroom, $1,149 two-bedroom | HUD Fair Market Rents |
| Income limit at 60% of AMI | $45,360 for 2 persons, $51,060 for 3 | HUD FY2026 MTSP Income Limits, Baton Rouge HMFA, from huduser.gov |
| Rent-burden share | 56.8% | ACS 2020-24, table B25070 |
| Rent ceiling growth | 2.80% a year | Calculated from ACS median household income, $54,948 to $63,071 |
| Market rent inputs | $1,253 and $1,141 | Zillow ZORI multifamily, Baton Rouge metro; Apartment List Baton Rouge Rent Report, from apartmentlist.com |
| 10-year Treasury par yield | 5.15% | Treasury par yield curve, close of Nov 13, 2026 |
| AMI tiers, hold period, flag thresholds, price at appraised value | 30%, 50%, 60%, 80%; 10 years; 25% and 1.20x | Request |
| Units in each tier | 26, 79, 106 and 53 | Analyst assumption |
| Current rent in each tier | $455, $830, $1,000 and $1,040 | Analyst assumption |
| Voucher units and Section 8 units | 40 and 24 | Analyst assumption |
| Submarket adjustment to metro rent | -10.0% | Analyst assumption |
| Utility allowance | $120 a month | Analyst assumption |
| Compliance cost | $375 a unit a year | Analyst assumption |
| Agency spread and exit cap rate | 1.65% and 6.25% | Analyst assumption |
Source: Compound workbook, Assumptions sheet, columns C and E. We chose the rows and shortened the wording.
Compound set the tier mix and tier rents so that modeled revenue ties to the filing. Restricted rents of $2,332,800, voucher rent of $544,320 and Section 8 rent of $326,592 give gross potential rent of $3,203,712. After vacancy at the reported occupancy and $59,400 of other income, modeled revenue is $3,153,865, which is $50 below the reported $3,153,915. Many other mixes would tie as well, so the tie-out shows the assumed rent roll is consistent with the filing and no more than that.
Rent ceilings by AMI tier: how the maximum rent is calculated
The rent ceiling in each tier is 30% of the income limit for the unit’s imputed household, divided by twelve, less a utility allowance. At 60% of AMI that is $1,050 a month here, which is $28 below market rent (our arithmetic on the unrounded cells).
Compound followed the rent rule used for tax-credit housing, which the Assumptions sheet cites as Section 42. A unit is assumed to house 1.5 persons per bedroom, so the 1.5-bedroom average implies 2.25 persons. HUD publishes limits for whole household sizes, and the workbook interpolates between the 2-person and 3-person limits. At 60% of AMI the limit at 2.25 persons is $46,785. Thirty percent of that over twelve months is a maximum gross rent of $1,170, and the $120 utility allowance leaves $1,050 for the owner.
| B | C | D | E | F | G | |
|---|---|---|---|---|---|---|
| 25 | Income tier (% AMI) | 30% AMI | 50% AMI | 60% AMI | 80% AMI | Total |
| 26 | Units | 26 | 79 | 106 | 53 | 264 |
| 27 | Share of units | 9.8% | 29.9% | 40.2% | 20.1% | 100.0% |
| 28 | of which HCV voucher units | 0 | 40 | 0 | 0 | 40 |
| 29 | of which PBS8 units | 24 | 0 | 0 | 0 | 24 |
| 30 | Restricted units without subsidy | 2 | 39 | 106 | 53 | 200 |
| 32 | MTSP income limit, 2-person | 22,680 | 37,800 | 45,360 | 60,480 | |
| 33 | MTSP income limit, 3-person | 25,530 | 42,550 | 51,060 | 68,080 | |
| 34 | Income limit at imputed household size | 23,393 | 38,988 | 46,785 | 62,380 | |
| 35 | Maximum gross rent (30% of income / 12) | 585 | 975 | 1,170 | 1,560 | |
| 36 | Less: utility allowance | (120) | (120) | (120) | (120) | |
| 37 | Rent ceiling, Year 1 | 465 | 855 | 1,050 | 1,440 | 1,012 |
| 38 | Market rent, Year 1 | 1,077 | 1,077 | 1,077 | 1,077 | 1,077 |
| 39 | Year-1 rent (lower of market and ceiling) | 465 | 855 | 1,050 | 1,077 | 939 |
| 40 | Ceiling binds from Year 1 | BINDS | BINDS | BINDS | no | 3 of 4 bind |
| 41 | Current in-place rent | 455 | 830 | 1,000 | 1,040 | 903 |
| 42 | Year-1 rent less current rent | 10 | 25 | 50 | 37 | 36 |
The Year-1 rent in each tier is the lower of the ceiling and market rent. Market rent is the average of the metro multifamily ZORI, $1,253, and Apartment List’s Baton Rouge median, $1,141, less a 10.0% submarket adjustment. Compound based that discount on ZIP code 70816, where the all-homes ZORI is 22.8% below the metro’s. The result is $1,077.
Monthly rent ceilings by income tier against market rent
Dollars per unit per month in Year 1. Ceilings are net of a $120 utility allowance
Compound workbook, Rent Roll sheet rows 10, 17 and 37: HUD FY2026 MTSP income limits, HUD Fair Market Rents, Zillow ZORI and Apartment List
Only the 80% tier, with a ceiling of $1,440, is priced by the market. In the other three tiers the owner cannot charge what the market would pay, and the weighted Year-1 rent across the 264 units is $939. In the multifamily value-add pro forma, rent can rise to whatever a renovated unit commands. Here the ceiling moves with the income limits, and the model grows it at 2.80% a year, the five-year growth rate of the parish’s median household income in the ACS.
Year-1 rents are $10, $25, $50 and $37 above the assumed current rents in the four tiers, or $36 on average. There is little rent to pick up at closing.
Voucher and Section 8 income in its own panel
The model assumes 40 Housing Choice Voucher units and 24 project-based Section 8 units and tracks their income separately. That income is 23.5% of gross revenue in Year 1 and declines every year as assistance is expected to lapse.
A voucher belongs to the tenant. The model places the 40 voucher units in the 50% tier and pays them the lower of the payment standard and market rent, $1,077 in Year 1. It assumes 90% of voucher units stay on a voucher each year, so the expected count is 36.0 units in Year 1 and 13.9 by Year 10. The model adds no new voucher tenants, so the count only falls. That is a conservative simplification, because a new voucher holder can lease a unit that another has left. A unit that loses its voucher is re-rented at the 50% ceiling of $855, which is $223 a month less (our arithmetic).
Project-based assistance belongs to the property under a contract. The model places the 24 units in the 30% tier at a contract rent of $1,082, grows it 3.0% a year, and assumes the contract expires in Year 5 with an 85% chance of renewal. The expected count falls from 24.0 to 20.4 units in that year. A unit that leaves the contract falls to the 30% ceiling of $465, a loss of $617 a month (our arithmetic).
| B | C | D | E | F | G | H | M | |
|---|---|---|---|---|---|---|---|---|
| 5 | HOUSING CHOICE VOUCHERS (HCV) | |||||||
| 6 | HCV units | 40 | ||||||
| 9 | HUD FMR FY2027 at average bedrooms (Year-1 basis) | 1,082 | ||||||
| 11 | HCV contract rent, Year 1 (capped at market) | 1,077 | ||||||
| 12 | Annual renewal probability, base | 90% | ||||||
| 15 | PROJECT-BASED SECTION 8 (PBS8) | |||||||
| 16 | PBS8 units | 24 | ||||||
| 19 | PBS8 contract rent, Year 1 | 1,082 | ||||||
| 21 | HAP contract expiry (year of hold) | 5 | ||||||
| 22 | Renewal probability at expiry, base | 85% | ||||||
| 25 | SUBSIDY BY YEAR, BASE CASE | |||||||
| 26 | Year | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 10 | |
| 27 | Expected HCV units | 36.0 | 32.4 | 29.2 | 26.2 | 23.6 | 13.9 | |
| 28 | Expected PBS8 units | 24.0 | 24.0 | 24.0 | 24.0 | 20.4 | 20.4 | |
| 29 | HCV voucher income | 465,471 | 429,397 | 396,118 | 365,419 | 337,099 | 225,211 | |
| 30 | PBS8 HAP income | 311,472 | 320,816 | 330,441 | 340,354 | 297,980 | 345,440 | |
| 31 | Total subsidy income ($) | 776,943 | 750,213 | 726,559 | 705,773 | 635,079 | 570,651 | |
| 32 | Subsidy share of gross revenue | 23.5% | 22.1% | 20.9% | 19.8% | 17.6% | 13.9% | |
| 34 | Subsidy share, stress case | 21.2% | 18.6% | 16.6% | 15.1% | 4.0% | 1.0% |
Voucher and Section 8 income as a share of gross revenue
Base case and stress case. The stress case assumes the Section 8 contract is not renewed in Year 5
Compound workbook, Subsidy sheet rows 32 and 34
Total subsidy income is $776,943 in Year 1 and $570,651 in Year 10. The request asked for a flag when this income exceeds 25% of revenue. In the tie-out to reported revenue the share is 26.7% and the flag trips. In Year 1 it is 23.5% and reads OK. Two things move it between those dates. HUD’s Fair Market Rents for the parish fell from FY2026 to FY2027, by $50 for a one-bedroom and $55 for a two-bedroom (our arithmetic), and four voucher units are expected to lapse in the first year.
In the stress case, with voucher renewal at 75% and no contract renewal, the share drops from 21.2% in Year 1 to 4.0% in Year 5.
The 10-year pro forma: capped rent growth and compliance cost
Year-1 NOI is $1,270,814, which is $158,221 or 11.1% below the reported twelve months (our arithmetic). Most of the step is the compliance cost the request asked for, and NOI does not pass the reported figure until Year 10.
The step from reported to Year 1 has three parts, all our arithmetic on the Pro Forma sheet. Effective gross income is $7,475 lower, because a 5.0% vacancy and credit loss allowance replaces reported occupancy of 96.6% and the Fair Market Rents fell. Operating expenses before compliance are $51,746 higher after one year of 3.0% growth. Compliance cost adds $99,000.
| B | C | D | E | F | G | H | M | N | |
|---|---|---|---|---|---|---|---|---|---|
| 4 | Year | T12 actual | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 10 | Year 11 |
| 8 | Market rent | 1,077 | 1,104 | 1,132 | 1,160 | 1,189 | 1,346 | 1,379 | |
| 11 | Rent ceiling, 60% AMI | 1,050 | 1,079 | 1,109 | 1,140 | 1,172 | 1,345 | 1,383 | |
| 15 | Rent charged, 60% AMI | 1,050 | 1,079 | 1,109 | 1,140 | 1,172 | 1,345 | 1,379 | |
| 19 | Ceiling binds, 60% AMI (1 = yes) | 1 | 1 | 1 | 1 | 1 | 1 | 0 | |
| 40 | Gross potential rent | 3,249,516 | 3,327,843 | 3,409,292 | 3,493,868 | 3,551,418 | 4,032,840 | 4,134,239 | |
| 41 | Vacancy and credit loss | (162,476) | (166,392) | (170,465) | (174,693) | (177,571) | (201,642) | (206,712) | |
| 42 | Other income | 59,400 | 60,885 | 62,407 | 63,967 | 65,566 | 74,182 | 76,037 | |
| 43 | Effective gross income ($) | 3,153,915 | 3,146,440 | 3,222,336 | 3,301,235 | 3,383,142 | 3,439,414 | 3,905,380 | 4,003,564 |
| 46 | Operating expenses excl. compliance | (1,724,880) | (1,776,627) | (1,829,925) | (1,884,823) | (1,941,368) | (1,999,609) | (2,318,095) | (2,387,638) |
| 51 | Total compliance cost | (99,000) | (102,960) | (107,078) | (111,362) | (115,816) | (140,908) | (146,544) | |
| 52 | Total operating expenses ($) | (1,724,880) | (1,875,627) | (1,932,885) | (1,991,902) | (2,052,729) | (2,115,425) | (2,459,003) | (2,534,182) |
| 53 | Net operating income ($) | 1,429,035 | 1,270,814 | 1,289,451 | 1,309,333 | 1,330,413 | 1,323,989 | 1,446,377 | 1,469,383 |
| 55 | Expense ratio incl. compliance | 54.7% | 59.6% | 60.0% | 60.3% | 60.7% | 61.5% | 63.0% | 63.3% |
| 56 | Expense ratio excl. compliance | 56.5% | 56.8% | 57.1% | 57.4% | 58.1% | 59.4% | 59.6% | |
| 57 | Compliance cost per unit ($) | 375 | 390 | 406 | 422 | 439 | 534 | 555 | |
| 62 | Agency loan debt service | (945,825) | (945,825) | (945,825) | (945,825) | (945,825) | (945,825) | 0 | |
| 63 | Cash flow after debt service ($) | 245,789 | 262,050 | 279,485 | 298,044 | 289,024 | 397,215 | 1,362,944 | |
| 64 | DSCR (NOI / debt service) | 1.34x | 1.36x | 1.38x | 1.41x | 1.40x | 1.53x | n/a |
Compliance cost is the line a market-rate model does not have. Compound built it from four items per unit per year: $150 for tenant income recertification, $60 for file audits, $75 for inspections and $90 for consultants. The total is $375 a unit, growing 4.0% a year to $534 in Year 10. The request asked for the expense ratio both ways. With compliance it is 59.6% in Year 1, and without it 56.5%, a difference of 3.1 points (our arithmetic). The reported twelve months ran at 54.7%.
The ratio rises through the hold, to 63.0% in Year 10 with compliance. The cause is in the growth rates. Ceilings grow 2.80% and market rent 2.5%, while operating expenses grow 3.0% and compliance cost 4.0%. A restricted property cannot raise rent to cover a tax or insurance increase.
Net operating income: reported, base case and stress case
The stress case grows ceilings and market rent at 1.0% a year and ends the Section 8 contract in Year 5
Compound workbook, Pro Forma sheet row 53 and Stress sheet row 53
Base-case NOI dips once, from $1,330,413 in Year 4 to $1,323,989 in Year 5, when the Section 8 contract comes up for renewal. Year 10 is $1,446,377. The stress case starts at $1,182,281 and falls every year, to $497,348 in Year 10.
One detail in the ceiling rows matters for the sensitivities. The 60% ceiling starts $28 below market rent (our arithmetic) and grows faster than it. By Year 10 it is $1,345 against a market rent of $1,346, and in Year 11 it passes market rent and stops binding. Faster income-limit growth helps only until the ceiling reaches the market.
Sizing the agency loan on stress NOI, next to the loan in place
Sized at a 1.25x DSCR on stress-case Year-1 NOI, the new loan is $12,090,151, or 47.0% of the price. That is $3,991,859 less than the balance of the loan in place (our arithmetic).
The rate is the 10-year Treasury par yield of 5.15% at the close of Nov 13, 2026 plus an assumed 1.65% spread, 6.80% in all. With 30-year amortization the annual debt constant is 7.82%. Stress-case Year-1 NOI of $1,182,281, divided by the 1.25x sizing DSCR and by the constant, supports $12,090,151. The other limit, 80% of the price, would allow $20,560,000, so coverage is the binding constraint.
| B | C | D | |
|---|---|---|---|
| 5 | NEW AGENCY LOAN | ||
| 6 | 10-year Treasury par yield | 5.15% | |
| 7 | Agency spread | 1.65% | |
| 8 | Interest rate | 6.80% | |
| 11 | Annual debt constant | 7.82% | |
| 12 | Stress-case Year-1 NOI | 1,182,281 | |
| 13 | Sizing DSCR | 1.25x | |
| 14 | Loan supported by stress NOI | 12,090,151 | |
| 17 | Loan allowed by LTV | 20,560,000 | |
| 18 | Agency loan amount ($) | 12,090,151 | |
| 19 | Binding constraint | DSCR on stress NOI | |
| 22 | LTV at close | 47.0% | |
| 40 | EXISTING LOAN VS NEW AGENCY LOAN | ||
| 41 | Existing CMBS loan | New agency loan | |
| 42 | Loan amount (existing: current balance) | 16,082,010 | 12,090,151 |
| 43 | Interest rate | 4.09% | 6.80% |
| 44 | Amortization (months) | 360 | 360 |
| 45 | Maturity date | 10/06/2029 | 12/01/2036 |
| 46 | Annual debt service | 1,074,309 | 945,825 |
| 47 | DSCR on T12 NOI | 1.33x | 1.51x |
| 48 | DSCR on Year-1 base NOI | 1.18x | 1.34x |
| 49 | DSCR on Year-1 stress NOI | 1.10x | 1.25x |
| 50 | Loan to purchase price | 62.6% | 47.0% |
| 51 | Debt yield on Year-1 base NOI | 7.9% | 10.5% |
| 52 | Months to maturity at closing | 34 | 120 |
The filing reports that loan at a fixed 4.09%, maturing 10/06/2029, with annual debt service of $1,074,309 and a reported DSCR of 1.33x. On the model’s Year-1 base NOI the same debt service is covered 1.18x, and on stress NOI 1.10x. Both figures rest on the hypothetical restrictions and are not the loan’s coverage, which the filing reports. The loan in place is 62.6% of the price. The new loan is smaller and costs more per dollar, so the buyer writes an equity check of $14,116,251.
The stress case meets the 1.25x sizing test in Year 1 by construction. Compound’s summary says coverage falls below the 1.20x flag threshold from Year 2, and the Debt sheet shows 0.84x in Year 5 and 0.53x in Year 10. Cash flow after debt service in the stress case is $2,219 in Year 4 and negative from Year 5. The sizing test covers the first year of a stress that compounds, so read the whole coverage row.
Restricted value against unrestricted value at sale, and the returns
At the same 6.25% exit cap rate, the restricted value at the end of Year 10 is $23,510,121 and the unrestricted value is $29,430,899. The gap is $5,920,777, or 20.1% of the unrestricted value.
Compound priced the sale on Year-11 NOI. Restricted NOI in that year is $1,469,383. The unrestricted figure, $1,839,431, puts every unit at market rent and removes the compliance cost. The workbook uses one cap rate for both so that the gap measures the NOI effect alone.
| B | C | |
|---|---|---|
| 5 | SOURCES AND USES | |
| 6 | Purchase price (latest appraised value) | 25,700,000 |
| 7 | Closing costs | 385,500 |
| 8 | Financing costs | 120,902 |
| 9 | Total uses ($) | 26,206,402 |
| 10 | Agency loan | 12,090,151 |
| 11 | Equity | 14,116,251 |
| 14 | Going-in cap rate (Year-1 NOI / price) | 4.94% |
| 17 | SALE AT END OF HOLD | |
| 18 | Forward-year restricted NOI | 1,469,383 |
| 19 | Exit cap rate, restricted | 6.25% |
| 20 | Restricted value ($) | 23,510,121 |
| 21 | Forward-year unrestricted NOI | 1,839,431 |
| 22 | Exit cap rate, unrestricted | 6.25% |
| 23 | Unrestricted value ($) | 29,430,899 |
| 24 | Value gap: unrestricted less restricted ($) | 5,920,777 |
| 25 | Gap, % of unrestricted value | 20.1% |
| 27 | Sale costs | (470,202) |
| 28 | Loan payoff | (10,325,515) |
| 29 | Net sale proceeds to equity ($) | 12,714,404 |
| 40 | RETURNS | |
| 41 | Levered IRR | 1.27% |
| 42 | Equity multiple | 1.12x |
| 43 | Year-1 cash-on-cash | 1.7% |
| 44 | Average cash-on-cash, Years 1-10 | 2.2% |
| 45 | Levered profit ($) | 1,733,040 |
| 46 | Unlevered IRR | 3.83% |
Purchase price against value at the end of Year 10
Both values use Year-11 NOI at a 6.25% exit cap rate
Compound workbook, Sale & Returns sheet, cells C6, C20 and C23
The restricted value is $2,189,879 below the purchase price (our arithmetic). The price is $97,348 a unit and the restricted value ten years later is $89,053 a unit. After sale costs and the loan payoff, net proceeds to equity are $12,714,404, which is $1,401,847 less than the equity invested (our arithmetic). Ten years of cash flow after debt service add up to $3,134,887 (our arithmetic), and the two together are the levered profit of $1,733,040.
The levered IRR is 1.27%, the equity multiple 1.12x, the Year-1 cash-on-cash return 1.7% and the ten-year average 2.2%. The going-in cap rate on Year-1 restricted NOI is 4.94%, which is 186 basis points below the loan rate (our arithmetic), and the unlevered IRR of 3.83% is also below it.
The price explains more of this return than the restrictions do. The appraisal dates from August 2019, the reported NOI is a 5.56% cap rate on it, and the filing reports the value without its basis. For new tax-credit construction, where the credits fund part of the cost, see the LIHTC pro forma.
Sensitivity tables and flags
Ceiling growth moves the return more than voucher renewal or compliance cost. One point less growth takes the IRR from 1.3% to a loss of 2.5%, and one point more takes it to 2.5%.
Compound built the three two-way tables the request asked for on the Assumptions sheet as live Excel data tables, so they recalculate when an input changes.
| B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|
| 147 | Table 1: ceiling growth (rows) vs HCV annual renewal probability (columns) | ||||||
| 148 | Ceiling growth (ACS + shift) | shift | 1.27% | 50% | 70% | 80% | 90% | 100% |
| 149 | 0.80% | -2.0% | (10.2%) | (9.9%) | (9.3%) | (7.6%) | (4.0%) |
| 150 | 1.80% | -1.0% | (3.8%) | (3.7%) | (3.3%) | (2.5%) | (0.5%) |
| 151 | 2.80% | 0.0% | 0.5% | 0.6% | 0.8% | 1.3% | 2.4% |
| 152 | 3.80% | +1.0% | 2.0% | 2.1% | 2.2% | 2.5% | 3.2% |
| 153 | 4.80% | +2.0% | 3.4% | 3.5% | 3.6% | 3.7% | 3.9% |
| 156 | Table 2: ceiling growth (rows) vs compliance cost growth (columns) | ||||||
| 157 | Ceiling growth (ACS + shift) | shift | 1.27% | 2.0% | 3.0% | 4.0% | 6.0% | 8.0% |
| 158 | 0.80% | -2.0% | (6.9%) | (7.2%) | (7.6%) | (8.6%) | (9.8%) |
| 159 | 1.80% | -1.0% | (2.0%) | (2.2%) | (2.5%) | (3.1%) | (3.8%) |
| 160 | 2.80% | 0.0% | 1.6% | 1.5% | 1.3% | 0.8% | 0.3% |
| 161 | 3.80% | +1.0% | 2.8% | 2.7% | 2.5% | 2.1% | 1.7% |
| 162 | 4.80% | +2.0% | 4.0% | 3.8% | 3.7% | 3.3% | 2.9% |
| 165 | Table 3: HCV annual renewal probability (rows) vs compliance cost growth (columns) | ||||||
| 166 | HCV renewal probability | 1.27% | 2.0% | 3.0% | 4.0% | 6.0% | 8.0% |
| 167 | 50% | 0.8% | 0.7% | 0.5% | 0.0% | (0.5%) | |
| 168 | 70% | 1.0% | 0.8% | 0.6% | 0.2% | (0.4%) | |
| 169 | 80% | 1.2% | 1.0% | 0.8% | 0.4% | (0.2%) | |
| 170 | 90% | 1.6% | 1.5% | 1.3% | 0.8% | 0.3% | |
| 171 | 100% | 2.7% | 2.6% | 2.4% | 2.0% | 1.6% |
The first table is asymmetric, and the pro forma explains why. Lower ceiling growth cuts rent in three tiers for ten years and cuts the sale value. Higher growth pushes the 60% tier, the largest with 106 units, into market rent, where the extra growth is lost. Two points less growth gives a loss of 7.6% a year, and two points more gives 3.7%.
Voucher renewal matters at the top of its range. Full renewal lifts the IRR to 2.4%, and 50% renewal gives 0.5%. Compliance cost growth of 8% a year cuts the IRR to 0.3%. The highest cell in the three tables is 4.0%, so no combination tested here reaches the 6.80% cost of the debt.
| Flag on the Summary sheet | Value | Status |
|---|---|---|
| Rent ceiling binds from Year 1: 30% AMI | 465 | FLAG: ceiling below market rent |
| Rent ceiling binds from Year 1: 50% AMI | 855 | FLAG: ceiling below market rent |
| Rent ceiling binds from Year 1: 60% AMI | 1,050 | FLAG: ceiling below market rent |
| Rent ceiling binds from Year 1: 80% AMI | 1,440 | OK |
| Voucher and Section 8 income share of revenue, in place (T12 tie-out) | 26.7% | FLAG: above 25% |
| Voucher and Section 8 income share of revenue, Year 1 | 23.5% | OK |
| Voucher and Section 8 income share, highest year of hold | 23.5% | OK |
| Stress DSCR, Year 1 (agency loan) | 1.25x | OK |
| Stress DSCR, lowest year of hold (agency loan) | 0.53x | FLAG: below 1.20x |
Source: Compound workbook, Summary sheet, rows 54 to 62.
What the public data cannot tell you
Public filings give this property’s income in a few lines and say nothing about restrictions. The seller’s documents replace the largest assumptions in the model, and Compound’s summary lists what to ask for.
- A current rent roll with tenant incomes and set-asides. It gives the unit mix, the rent and income tier of each unit, the voucher tenants and the lease dates. It replaces the assumed tiers of 26, 79, 106 and 53 units and the 1.5-bedroom average. The rent roll analysis page covers reading one.
- Any recorded regulatory agreement or land use restriction agreement. It states which units are restricted, at what income levels and until when. Whether one exists for this property is not in the filing.
- The housing authority’s utility allowance schedule. The $120 allowance is a placeholder, and each dollar comes off the ceiling.
- Any housing assistance payments contract. Its term, contract rents and renewal history replace the assumed 24 units, the Year 5 expiry and the 85% renewal probability.
- A trailing twelve-month statement by line, with tax and insurance bills, and the capital needs. The filing reports total operating expenses only, and the model carries reserves of $300 a unit a year.
- A current appraisal. The price in this model is a 2019 value.
- The existing loan documents. They hold the prepayment and defeasance terms and say whether the 4.09% loan can be assumed.
- Compliance history. Audits, inspections and any findings show what compliance really costs.
The public rent data has limits too. The multifamily ZORI series covers the metro, the ZIP-level series covers all home types, and Apartment List publishes its median for the city. The FY2026 income limits came from HUD’s website. The next set of income limits will move every ceiling in the model.
Our view, and what would change it
On these inputs the 2019 appraised value is more than the hypothetical restricted income supports. We would treat the model as a pricing tool and solve for the price at which the debt works.
The strongest part of the workbook is the structure. Rent is tested against the ceiling tier by tier and year by year, subsidy income has its own rows and renewal odds, and the loan is sized on a named stress case. A market-rate model such as the real estate pro forma has none of these.
The weakest part is what public data cannot supply. The tier mix decides how much of the rent roll is capped, and it is an assumption fitted to one revenue number. If the property has fewer deeply restricted units than assumed, or none, the ceilings bind less and the return is higher.
We would not read the 1.3% as a verdict on restricted housing. The price is a 2019 appraisal applied to a hypothetical rent roll, and the loan costs more than the going-in yield.
What would change our view: a rent roll showing fewer capped units, a price below the restricted value of $23,510,121, an assumable loan at 4.09%, or a contract that has already renewed for a long term.
Affordable housing acquisition model (Excel)Compound’s workbook from this run: a Summary with flags, an Assumptions sheet with a source or reason beside every input and three live sensitivity tables, the tiered Rent Roll, the Subsidy panel, the 10-year Pro Forma, the Stress case, Debt, Sale & Returns and Sources.Download · FreeFAQ
How do you calculate the maximum rent for a unit at 60% of AMI?
Take HUD’s 60% income limit for the household size imputed to the unit, multiply by 30% and divide by twelve. That is the maximum gross rent. Subtract the utility allowance for utilities the tenant pays. In this workbook the limit at 2.25 persons is $46,785, the maximum gross rent is $1,170, and the ceiling after a $120 allowance is $1,050.
How should an acquisition model treat Housing Choice Vouchers and project-based Section 8?
Keep them apart from tenant-paid rent and from each other. A voucher belongs to the tenant and can leave with the tenant, so this model applies a 90% annual renewal probability to 40 voucher units. Project-based assistance belongs to the property under a contract, so the model applies one renewal test in the contract’s expiry year.
What growth rate should rent ceilings use in an affordable housing pro forma?
Ceilings follow HUD’s income limits, which follow area incomes. This model uses the five-year growth of ACS median household income in the parish, 2.80% a year, and a stress case of 1.00%. The sensitivity table shows why to test it: one point less growth moves the levered IRR from 1.3% to a loss of 2.5%.
Why is the expense ratio higher on an income-restricted property?
Rent is capped while expenses are not, and the owner pays for compliance. This model adds $375 a unit a year for income recertification, file audits, inspections and consultants. The Year-1 expense ratio is 59.6% with that cost and 56.5% without it, and it reaches 63.0% by Year 10 because expenses grow faster than the ceilings.
How much less is an income-restricted property worth than the same building at market rents?
It depends on how far the ceilings sit below market rent and on the cap rate a buyer applies to each income stream. In this model, with one 6.25% exit cap rate for both, the restricted value is $23,510,121 and the unrestricted value is $29,430,899. The gap is 20.1% of the unrestricted value.





