Excel workbook · Catalog edition 2023
OHFA 2023 9% LIHTC Underwriting Model
Ohio Housing Finance Agency (OHFA)
Ohio Housing Finance Agency 2023 9% LIHTC eligible credit sizing, operating budget and cashflow sensitivity from the AHFA source application.
- Inputs
- 41
- Outputs
- 14
- File size
- 735 KB
- Verification
- Independent scenario check
Try it
Try it loads a copy of this file from formualizer.com.
Saved workbook valuesInputOutput
| Row | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| 6 | Total Acquisition Basis | Total Rehabilitation Basis | Total New Construction Basis | ||||
| 7 | Total Eligible Basis | $- | $- | $- | |||
| 8 | Deducted Federal Financing | $- | $- | $- | |||
| 13 | Percentage of QCT/DDA Sites | 0% | 0% | ||||
| 27 | Applicable Fraction | 0.00% | |||||
| 31 | Applicable Tax Credit Percentage | 4.00% | 9.00% | 9.00% | |||
| 39 | Total Housing Credits Eligible | $- | $- | $- | |||
| 41 | Total Housing Credit Eligible | $- | Pool |
What was checked
Changed-input scenarios match an independent calculation.
Scope and limitations
Catalog calculations, not certification.
Try it uses direct references supplied by Formualizer to carry amortization values into the cash flow schedule.
- Scalarize Cashflow legacy TRANSPOSE CSE formula across C:S to direct references to MLP Amortization U2:U18. No source file edits or table hydration. Source #REF diagnostics remain outside mapped outputs.
What it computes
Ports for the compact flow: quick_lihtc_credit_sizing. The full port contract includes all 55 ports.
Inputs (15)
| Port / description | Type / constraints / default |
|---|---|
unitsResidential units, count; first rent-roll cohort; nonnegative unless stated. Default 0 (blank numeric source cells act as zero). Source location Budget & Costs!C9; effective default 0. | Type: number Constraints: min: 0 Default: 0 |
monthly_tenant_rentUSD per unit per month, positive income; nonnegative unless stated. Default 0 (blank numeric source cells act as zero). Source location Budget & Costs!I9; effective default 0. | Type: number Constraints: min: 0 Default: 0 |
low_income_floor_areaSquare feet, low-income rental area; paired with market floor area; nonnegative unless stated. Default 0 (blank numeric source cells act as zero). Source location Project Details!C244; effective default 0.0. | Type: number Constraints: min: 0 Default: 0 |
market_floor_areaSquare feet, market rental area; applicable fraction is lesser of unit and floor fractions; nonnegative unless stated. Default 0 (blank numeric source cells act as zero). Source location Project Details!C243; effective default 0.0. | Type: number Constraints: min: 0 Default: 0 |
acquisition_basisUSD, building acquisition eligible basis allocation; nonnegative unless stated. Default 0 (blank numeric source cells act as zero). Source location Budget & Costs!F256; effective default 0. | Type: number Constraints: min: 0 Default: 0 |
rehabilitation_basisUSD, residential rehabilitation eligible basis allocation; nonnegative unless stated. Default 0 (blank numeric source cells act as zero). Source location Budget & Costs!G282; effective default 0. | Type: number Constraints: min: 0 Default: 0 |
new_construction_basisUSD, residential new construction eligible basis allocation; nonnegative unless stated. Default 0 (blank numeric source cells act as zero). Source location Budget & Costs!H281; effective default 0. | Type: number Constraints: min: 0 Default: 0 |
rehab_qct_fractionDecimal fraction 0..1 of rehabilitation qualifying for 30% QCT/DDA boost; nonnegative unless stated. Default 0 (blank numeric source cells act as zero). Source location Housing Credits!D13; effective default 0.0. | Type: number Constraints: min: 0; max: 1 Default: 0 |
new_qct_fractionDecimal fraction 0..1 of new construction qualifying for 30% QCT/DDA boost; nonnegative unless stated. Default 0 (blank numeric source cells act as zero). Source location Housing Credits!E13; effective default 0.0. | Type: number Constraints: min: 0; max: 1 Default: 0 |
rehab_credit_rateAnnual decimal credit rate 0..1; nonnegative unless stated. Default 0.09 (blank numeric source cells act as zero). Source location Housing Credits!D31; effective default 0.09. | Type: number Constraints: min: 0; max: 1 Default: 0.09 |
new_credit_rateAnnual decimal credit rate 0..1; nonnegative unless stated. Default 0.09 (blank numeric source cells act as zero). Source location Housing Credits!E31; effective default 0.09. | Type: number Constraints: min: 0; max: 1 Default: 0.09 |
programText program switch: 4% LIHTC or 9% Competitive; activates competitive basis boost tests. Source default blank. Source location Project Details!C20; effective default . | Type: string Constraints: one of: , 4% LIHTC, 9% Competitive Default: blank string |
rent_limit_ami_fractionDecimal fraction 0..1, rent affordability AMI; does not override rent-limit compliance; nonnegative unless stated. Default 0 (blank numeric source cells act as zero). Source location Budget & Costs!G9; effective default 0. | Type: number Constraints: min: 0; max: 1 Default: 0 |
vacancyDecimal fraction 0..1, annual operating budget vacancy; nonnegative unless stated. Default 0.07 (blank numeric source cells act as zero). Source location Budget & Costs!E82; effective default 0.07. | Type: number Constraints: min: 0; max: 1 Default: 0.07 |
mbe_wbe_developerText enum Yes / No; Yes activates a 15% rehabilitation/new-construction basis boost when program is 9% Competitive. Default Yes. Source location Development Team!D70; effective default Yes. | Type: string Constraints: one of: Yes, No Default: "Yes" |
Outputs (5)
| Port / description | Type / constraints / default |
|---|---|
annual_credit_eligibleUSD per year, eligible ceiling before a separate requested amount; calculated from publisher's workbook formulas. | Type: number |
acquisition_creditUSD per year; calculated from publisher's workbook formulas. | Type: number |
rehabilitation_creditUSD per year; calculated from publisher's workbook formulas. | Type: number |
new_construction_creditUSD per year; calculated from publisher's workbook formulas. | Type: number |
applicable_fractionDecimal fraction 0..1, rounded to four decimal places; calculated from publisher's workbook formulas. | Type: number |
Questions this workbook answers
- Calculate Ohio LIHTC annual credits
- Size OHFA eligible basis with QCT boost
- Stress Ohio affordable housing operating cashflow
Questions and answers
Does this replace OHFA underwriting approval?
No. This executes selected formulas in the historical 2023 publisher application; current program rules and the complete application must be reviewed separately.
What does the quick flow return?
Annual credit ceiling, acquisition/rehabilitation/new-construction credit components, and the applicable low-income fraction.
For agents
Connect an MCP client to the catalog endpoint:
https://catalog.formualizer.com/v1/mcpSlug: ohfa-lihtc-9pct · Edition: 2023
- search_workbooks
- Optional arguments: query (string), theme (string), tag (string).
- inspect_workbook
- Required: slug (string). Optional: edition (string), flow (string).
- evaluate_workbook
- Required: slug (string), inputs (object). Optional: edition (string), flow (string), cellOverrides (object), selectCells (array of strings), trace (boolean).
{
"name": "evaluate_workbook",
"arguments": {
"slug": "ohfa-lihtc-9pct",
"edition": "2023",
"flow": "full_underwriting_budget_cashflow",
"inputs": {
"units": 60,
"monthly_tenant_rent": 950,
"low_income_floor_area": 54000,
"acquisition_basis": 2000000,
"rehabilitation_basis": 6000000,
"program": "9% Competitive",
"rent_limit_ami_fraction": 0.6,
"first_year_vacancy": 0.07,
"vacancy": 0.07,
"annual_office_expense": 100000,
"annual_electricity": 90000,
"annual_maintenance_supplies": 60000,
"annual_insurance": 40000,
"annual_replacement_reserve": 18000,
"senior_loan_amount": 3000000,
"senior_loan_rate": 0.06,
"senior_loan_amortization": 30
}
}
}cellOverrides sets individual cells. selectCells reads at most 256 cells per request. trace requests formula lineage.
Flows
quick_lihtc_credit_sizing
Quick LIHTC credit sizing
Eligible basis, applicable fractions, credit rates and boost activation; single low-income rent cohort, acquisition/rehab/new construction allocations. Annual credit ceiling is distinct from requested credits and equity. Other competitive pool boosts use full cellOverrides.
15 input ports · 5 output ports. Full membership in the port contract.
full_underwriting_budget_cashflow
Full underwriting budget and cashflow
Eligible basis, applicable fractions, credit rates and boost activation; all mapped rent, operating expense, senior debt and annual cashflow assumptions. Additional rent cohorts, detailed cost rows and competitive pool switches use cellOverrides. Not an award or compliance determination.
41 input ports · 14 output ports. Full membership in the port contract.
REST example
Bundle scenario: rehabilitation_full · Flow: full_underwriting_budget_cashflow
curl 'https://catalog.formualizer.com/v1/models/ohfa-lihtc-9pct/2023/evaluate?flow=full_underwriting_budget_cashflow' \
-H 'Content-Type: application/json' \
--data '{"inputs":{"units":60,"monthly_tenant_rent":950,"low_income_floor_area":54000,"acquisition_basis":2000000,"rehabilitation_basis":6000000,"program":"9% Competitive","rent_limit_ami_fraction":0.6,"first_year_vacancy":0.07,"vacancy":0.07,"annual_office_expense":100000,"annual_electricity":90000,"annual_maintenance_supplies":60000,"annual_insurance":40000,"annual_replacement_reserve":18000,"senior_loan_amount":3000000,"senior_loan_rate":0.06,"senior_loan_amortization":30}}'Expected response
Expected values from the bundle, subject to the check level above.
{
"outputs": {
"annual_credit_eligible": 700999.9999999999,
"acquisition_credit": 80000,
"rehabilitation_credit": 620999.9999999999,
"new_construction_credit": 0,
"applicable_fraction": 1,
"effective_gross_income": 636120,
"operating_expenses": 308000,
"budget_noi": 328120,
"year_one_noi": 328120,
"year_one_cashflow": 112281.81094500751,
"year_two_cashflow": 115764.21094500742,
"senior_debt_service": 215838.1890549925,
"year_one_dscr": 1.520212903178129,
"net_equity": 0
}
}Looking for another spreadsheet? Request a workbook.