Conceptual architectural render generated using ChatGPT DALL-E. Not an actual design proposal.
A full 4% Low-Income Housing Tax Credit development pro forma for a proposed 51-unit mixed-income affordable housing project on the Blue Hill Avenue corridor in Mattapan, Boston. Built entirely from scratch as an independent work sample.
Conceptual architectural render generated using ChatGPT DALL-E. Not an actual design proposal.
This model was built entirely from scratch in Excel across eight interconnected worksheets. It covers the full LIHTC development finance workflow: credit pricing, tax-exempt bond sizing, multi-source capital stacking, construction cash flows, and a 23-year long-term operating projection.
The project targets low-income households who earn too much to qualify for deeply subsidized housing but too little to afford rising market rents. It is structured to meet the financing, regulatory, and income-mixing requirements of the Massachusetts Qualified Allocation Plan and the HUD 221(d)(4) mortgage program.
The full working model is embedded below. Use the sheet tabs at the bottom to browse all eight worksheets, or download the Excel file to inspect formulas and assumptions directly.
| Source | Amount | % of Total | Notes |
|---|---|---|---|
| Federal LIHTC Equity | $11,205,262 | 43.4% | 4% credits at $0.90/credit |
| State LIHTC Equity | $2,179,019 | 8.4% | State credits at $0.70/credit |
| HUD 1st Mortgage | $7,190,000 | 27.8% | 221(d)(4), 6.75%, 40-yr amort |
| Soft Debt (Local/State) | $2,992,976 | 11.6% | HSF, CBH, HOME, CPA, CEDAC |
| FHLB AHP + MassHousing | $1,141,043 | 4.4% | AHP grant + AHTF |
| Deferred Developer Fee | $1,121,752 | 4.3% | From total $2.53M fee |
| Total | $25,830,052 | 100% |
| Year | Revenue | NOI | DSCR | Cash Flow |
|---|---|---|---|---|
| Year 1 | $1,175,131 | $601,499 | 1.148x | $77,769 |
| Year 5 | $1,272,000 | $626,373 | 1.196x | $102,643 |
| Year 10 | $1,376,853 | $650,193 | 1.241x | $126,463 |
| Year 15 | $1,550,560 | $682,891 | 1.304x | $159,161 |
| Year 23 | $1,816,728 | $717,591 | 1.370x | $193,861 |
Revenue grows at 2% annually. Expenses are trended by category. Debt service is fixed for the 40-year HUD term, resulting in steadily improving debt service coverage over the hold period.
The eight worksheets span the complete LIHTC development finance workflow: program design with AMI-band unit mix allocations, a Year 1 operating budget with income averaging compliance, a full permanent and construction sources and uses, LIHTC eligible and qualified basis calculations with DDA boost, a 28-month construction draw schedule, and the 23-year long-term operating projection shown above.
The model reflects a realistic Boston-specific soft debt stack drawing from six public programs: the Housing Stabilization Fund, Community Based Housing program, Boston HOME, Community Preservation Act, Massachusetts HOME, and CEDAC. This multi-layered capital structure is standard for Boston affordable housing and reflects working knowledge of the city and state subsidy landscape.
The project uses a 4% federal LIHTC structure paired with a HUD-insured tax-exempt permanent mortgage under Section 221(d)(4). The first mortgage serves as the qualifying tax-exempt bond financing, meeting the requirement that at least 50% of eligible basis be financed with tax-exempt bonds. Rather than using a single AMI threshold, the project elects income averaging, allowing a mix of AMI levels (30%–80%) as long as the weighted average does not exceed 60%. The model calculates a weighted average of 49.4%.
The first mortgage is sized based on a 1.15x DSCR constraint against Year 1 NOI using the mortgage constant derived from a 6.75% interest rate and 40-year amortization. HUD MIP is properly accounted for in both the operating budget and the development cost budget. The project qualifies for a 1.30x Difficult Development Area basis boost, increasing the qualified basis from $23.9M to $31.1M — a critical tool for making the economics work in high-cost Boston.