Affordable housing acquisition model: three of four rent ceilings bind

An acquisitions analyst asks Compound for an affordable housing acquisition model on a real 264-unit apartment property from the CMBS loan filings, with rents capped by HUD income limits, voucher income in its own panel and a loan sized on stress-case NOI.

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

Short answer

An affordable housing acquisition model underwrites an apartment purchase with rents capped at HUD income limits. Compound's model puts hypothetical income tiers on a 264-unit Baton Rouge property: three of four rent ceilings sit below market rent, restricted value is 20.1% below unrestricted value, and the levered IRR at the 2019 appraised value is 1.3%.

Try it now
On this page (11)
  1. 1. Choosing the property: three screen tests on public data
  2. 2. Which inputs are sourced and which are analyst assumptions
  3. 3. Rent ceilings by AMI tier: how the maximum rent is calculated
  4. 4. Voucher and Section 8 income in its own panel
  5. 5. The 10-year pro forma: capped rent growth and compliance cost
  6. 6. Sizing the agency loan on stress NOI, next to the loan in place
  7. 7. Restricted value against unrestricted value at sale, and the returns
  8. 8. Sensitivity tables and flags
  9. 9. What the public data cannot tell you
  10. 10. Our view, and what would change it
  11. 11. FAQ

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.

Prompt to try

“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.”

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.

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.

InputValueSource or basis in the workbook
Units, year built, revenue, expenses, NOI, occupancy264 units, 1985, $1,429,035 NOICSAIL 2019-C18 Form ABS-EE, loan 11
Appraised value and date$25,700,000, 08/20/2019Same filing
Existing loan balance, rate, maturity$16,082,010, 4.09%, 10/06/2029Same filing
HUD Fair Market Rents, FY2027$1,014 one-bedroom, $1,149 two-bedroomHUD Fair Market Rents
Income limit at 60% of AMI$45,360 for 2 persons, $51,060 for 3HUD FY2026 MTSP Income Limits, Baton Rouge HMFA, from huduser.gov
Rent-burden share56.8%ACS 2020-24, table B25070
Rent ceiling growth2.80% a yearCalculated from ACS median household income, $54,948 to $63,071
Market rent inputs$1,253 and $1,141Zillow ZORI multifamily, Baton Rouge metro; Apartment List Baton Rouge Rent Report, from apartmentlist.com
10-year Treasury par yield5.15%Treasury par yield curve, close of Nov 13, 2026
AMI tiers, hold period, flag thresholds, price at appraised value30%, 50%, 60%, 80%; 10 years; 25% and 1.20xRequest
Units in each tier26, 79, 106 and 53Analyst assumption
Current rent in each tier$455, $830, $1,000 and $1,040Analyst assumption
Voucher units and Section 8 units40 and 24Analyst assumption
Submarket adjustment to metro rent-10.0%Analyst assumption
Utility allowance$120 a monthAnalyst assumption
Compliance cost$375 a unit a yearAnalyst assumption
Agency spread and exit cap rate1.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.

Edgewood_Affordable_Acquisition_Model.xlsx
C39fx=MIN(C37,C38)
BCDEFG
25Income tier (% AMI)30% AMI50% AMI60% AMI80% AMITotal
26Units267910653264
27Share of units9.8%29.9%40.2%20.1%100.0%
28of which HCV voucher units0400040
29of which PBS8 units2400024
30Restricted units without subsidy23910653200
32MTSP income limit, 2-person22,68037,80045,36060,480
33MTSP income limit, 3-person25,53042,55051,06068,080
34Income limit at imputed household size23,39338,98846,78562,380
35Maximum gross rent (30% of income / 12)5859751,1701,560
36Less: utility allowance(120)(120)(120)(120)
37Rent ceiling, Year 14658551,0501,4401,012
38Market rent, Year 11,0771,0771,0771,0771,077
39Year-1 rent (lower of market and ceiling)4658551,0501,077939
40Ceiling binds from Year 1BINDSBINDSBINDSno3 of 4 bind
41Current in-place rent4558301,0001,040903
42Year-1 rent less current rent1025503736
SummaryAssumptionsRent RollSubsidyPro FormaStressDebtSale & ReturnsSources
The tiered rent roll on the Rent Roll sheet, as Compound wrote it. We chose the rows. Tier levels are from the request, income limits are from HUD, and the unit counts and current rents are analyst assumptions.

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

30% AMI rent ceiling$46550% AMI rent ceiling$85560% AMI rent ceiling$1,050Subject market rent$1,077HUD Fair Market Rent, FY2027, at 1.5 bedrooms$1,08280% AMI rent ceiling$1,440

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).

Edgewood_Affordable_Acquisition_Model.xlsx
C11fx=MIN(C9*C10,'Rent Roll'!$C$10)
BCDEFGHM
5HOUSING CHOICE VOUCHERS (HCV)
6HCV units40
9HUD FMR FY2027 at average bedrooms (Year-1 basis)1,082
11HCV contract rent, Year 1 (capped at market)1,077
12Annual renewal probability, base90%
15PROJECT-BASED SECTION 8 (PBS8)
16PBS8 units24
19PBS8 contract rent, Year 11,082
21HAP contract expiry (year of hold)5
22Renewal probability at expiry, base85%
25SUBSIDY BY YEAR, BASE CASE
26YearYear 1Year 2Year 3Year 4Year 5Year 10
27Expected HCV units36.032.429.226.223.613.9
28Expected PBS8 units24.024.024.024.020.420.4
29HCV voucher income465,471429,397396,118365,419337,099225,211
30PBS8 HAP income311,472320,816330,441340,354297,980345,440
31Total subsidy income ($)776,943750,213726,559705,773635,079570,651
32Subsidy share of gross revenue23.5%22.1%20.9%19.8%17.6%13.9%
34Subsidy share, stress case21.2%18.6%16.6%15.1%4.0%1.0%
SummaryAssumptionsRent RollSubsidyPro FormaStressDebtSale & ReturnsSources
The Subsidy sheet, as Compound wrote it. We chose the rows and show Years 1 to 5 and Year 10 of the ten year columns. Every unit count, probability and expiry year on this sheet is an analyst assumption.

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

Base case, Year 123.5%Base case, Year 517.6%Base case, Year 1013.9%Stress case, Year 121.2%Stress case, Year 54.0%Stress case, Year 101.0%

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.

Edgewood_Affordable_Acquisition_Model.xlsx
D13fx=MIN(D9,D$8)
BCDEFGHMN
4YearT12 actualYear 1Year 2Year 3Year 4Year 5Year 10Year 11
8Market rent1,0771,1041,1321,1601,1891,3461,379
11Rent ceiling, 60% AMI1,0501,0791,1091,1401,1721,3451,383
15Rent charged, 60% AMI1,0501,0791,1091,1401,1721,3451,379
19Ceiling binds, 60% AMI (1 = yes)1111110
40Gross potential rent3,249,5163,327,8433,409,2923,493,8683,551,4184,032,8404,134,239
41Vacancy and credit loss(162,476)(166,392)(170,465)(174,693)(177,571)(201,642)(206,712)
42Other income59,40060,88562,40763,96765,56674,18276,037
43Effective gross income ($)3,153,9153,146,4403,222,3363,301,2353,383,1423,439,4143,905,3804,003,564
46Operating 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)
51Total compliance cost(99,000)(102,960)(107,078)(111,362)(115,816)(140,908)(146,544)
52Total 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)
53Net operating income ($)1,429,0351,270,8141,289,4511,309,3331,330,4131,323,9891,446,3771,469,383
55Expense ratio incl. compliance54.7%59.6%60.0%60.3%60.7%61.5%63.0%63.3%
56Expense ratio excl. compliance56.5%56.8%57.1%57.4%58.1%59.4%59.6%
57Compliance cost per unit ($)375390406422439534555
62Agency loan debt service(945,825)(945,825)(945,825)(945,825)(945,825)(945,825)0
63Cash flow after debt service ($)245,789262,050279,485298,044289,024397,2151,362,944
64DSCR (NOI / debt service)1.34x1.36x1.38x1.41x1.40x1.53xn/a
SummaryAssumptionsRent RollSubsidyPro FormaStressDebtSale & ReturnsSources
Rows of the Pro Forma sheet, as Compound wrote them. We chose the rows and show the reported twelve months, Years 1 to 5, Year 10 and Year 11. The formula shown is the rent test for the 30% tier, and the "Rent charged, 60% AMI" row applies the same test.

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

Reported, 12 months to June 2026$1.43MBase case, Year 1$1.27MBase case, Year 5$1.32MBase case, Year 10$1.45MStress case, Year 1$1.18MStress case, Year 5$0.79MStress case, Year 10$0.50M

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.

Edgewood_Affordable_Acquisition_Model.xlsx
C14fx=C12/C13/C11
BCD
5NEW AGENCY LOAN
610-year Treasury par yield5.15%
7Agency spread1.65%
8Interest rate6.80%
11Annual debt constant7.82%
12Stress-case Year-1 NOI1,182,281
13Sizing DSCR1.25x
14Loan supported by stress NOI12,090,151
17Loan allowed by LTV20,560,000
18Agency loan amount ($)12,090,151
19Binding constraintDSCR on stress NOI
22LTV at close47.0%
40EXISTING LOAN VS NEW AGENCY LOAN
41Existing CMBS loanNew agency loan
42Loan amount (existing: current balance)16,082,01012,090,151
43Interest rate4.09%6.80%
44Amortization (months)360360
45Maturity date10/06/202912/01/2036
46Annual debt service1,074,309945,825
47DSCR on T12 NOI1.33x1.51x
48DSCR on Year-1 base NOI1.18x1.34x
49DSCR on Year-1 stress NOI1.10x1.25x
50Loan to purchase price62.6%47.0%
51Debt yield on Year-1 base NOI7.9%10.5%
52Months to maturity at closing34120
SummaryAssumptionsRent RollSubsidyPro FormaStressDebtSale & ReturnsSources
The loan sizing block and the loan comparison on the Debt sheet, as Compound wrote them. We chose the rows. Existing loan terms are from the trust's Form ABS-EE. The agency spread, sizing DSCR and maximum LTV are analyst assumptions.

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.

Edgewood_Affordable_Acquisition_Model.xlsx
C24fx=C23-C20
BC
5SOURCES AND USES
6Purchase price (latest appraised value)25,700,000
7Closing costs385,500
8Financing costs120,902
9Total uses ($)26,206,402
10Agency loan12,090,151
11Equity14,116,251
14Going-in cap rate (Year-1 NOI / price)4.94%
17SALE AT END OF HOLD
18Forward-year restricted NOI1,469,383
19Exit cap rate, restricted6.25%
20Restricted value ($)23,510,121
21Forward-year unrestricted NOI1,839,431
22Exit cap rate, unrestricted6.25%
23Unrestricted value ($)29,430,899
24Value gap: unrestricted less restricted ($)5,920,777
25Gap, % of unrestricted value20.1%
27Sale costs(470,202)
28Loan payoff(10,325,515)
29Net sale proceeds to equity ($)12,714,404
40RETURNS
41Levered IRR1.27%
42Equity multiple1.12x
43Year-1 cash-on-cash1.7%
44Average cash-on-cash, Years 1-102.2%
45Levered profit ($)1,733,040
46Unlevered IRR3.83%
SummaryAssumptionsRent RollSubsidyPro FormaStressDebtSale & ReturnsSources
Sources and uses, the sale and the returns on the Sale & Returns sheet, as Compound wrote them. We chose the rows. The purchase price is the 2019 appraised value, as the request set it.

Purchase price against value at the end of Year 10

Both values use Year-11 NOI at a 6.25% exit cap rate

Purchase price (2019 appraised value)$25.7MRestricted value, end of Year 10$23.5MUnrestricted value, end of Year 10$29.4M

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.

Edgewood_Affordable_Acquisition_Model.xlsx
C148fx='Sale & Returns'!$C$41
BCDEFGH
147Table 1: ceiling growth (rows) vs HCV annual renewal probability (columns)
148Ceiling growth (ACS + shift) | shift1.27%50%70%80%90%100%
1490.80%-2.0%(10.2%)(9.9%)(9.3%)(7.6%)(4.0%)
1501.80%-1.0%(3.8%)(3.7%)(3.3%)(2.5%)(0.5%)
1512.80%0.0%0.5%0.6%0.8%1.3%2.4%
1523.80%+1.0%2.0%2.1%2.2%2.5%3.2%
1534.80%+2.0%3.4%3.5%3.6%3.7%3.9%
156Table 2: ceiling growth (rows) vs compliance cost growth (columns)
157Ceiling growth (ACS + shift) | shift1.27%2.0%3.0%4.0%6.0%8.0%
1580.80%-2.0%(6.9%)(7.2%)(7.6%)(8.6%)(9.8%)
1591.80%-1.0%(2.0%)(2.2%)(2.5%)(3.1%)(3.8%)
1602.80%0.0%1.6%1.5%1.3%0.8%0.3%
1613.80%+1.0%2.8%2.7%2.5%2.1%1.7%
1624.80%+2.0%4.0%3.8%3.7%3.3%2.9%
165Table 3: HCV annual renewal probability (rows) vs compliance cost growth (columns)
166HCV renewal probability1.27%2.0%3.0%4.0%6.0%8.0%
16750%0.8%0.7%0.5%0.0%(0.5%)
16870%1.0%0.8%0.6%0.2%(0.4%)
16980%1.2%1.0%0.8%0.4%(0.2%)
17090%1.6%1.5%1.3%0.8%0.3%
171100%2.7%2.6%2.4%2.0%1.6%
SummaryAssumptionsRent RollSubsidyPro FormaStressDebtSale & ReturnsSources
The three sensitivity tables on the Assumptions sheet, as Compound wrote them. The corner cell of each table links to the levered IRR on the Sale & Returns sheet. Parentheses are negative returns.

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 sheetValueStatus
Rent ceiling binds from Year 1: 30% AMI465FLAG: ceiling below market rent
Rent ceiling binds from Year 1: 50% AMI855FLAG: ceiling below market rent
Rent ceiling binds from Year 1: 60% AMI1,050FLAG: ceiling below market rent
Rent ceiling binds from Year 1: 80% AMI1,440OK
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 123.5%OK
Voucher and Section 8 income share, highest year of hold23.5%OK
Stress DSCR, Year 1 (agency loan)1.25xOK
Stress DSCR, lowest year of hold (agency loan)0.53xFLAG: 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 · Free

FAQ

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.

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