formualizer

Excel workbook · Catalog edition 2023

OHFA 2023 4% LIHTC Underwriting Model

Ohio Housing Finance Agency (OHFA)

Ohio Housing Finance Agency 2023 4% LIHTC eligible credit sizing, operating budget and cashflow sensitivity from the AHFA source application.

Inputs
39
Outputs
14
File size
636 KB
Verification
Independent scenario check

Try it

Try it loads a copy of this file from formualizer.com.

LIHTCsB6:H45

Saved workbook valuesInputOutput

RowBCDEFGH
6Total Acquisition BasisTotal Rehabilitation BasisTotal New Construction Basis
7Total Eligible Basis$-$-$-
8Deducted Federal Financing$-$-$-
13Percentage of QCT/DDA Sites0%0%Input 2020 TractInput Zip Code
27Applicable Fraction0.00%
31Applicable Tax Credit Percentage4.00%4.00%4.00%
39Total LIHTCs Eligible$-$-$-
41Total LIHTCs Eligible $-

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 53 ports.

Inputs (15)
Port / descriptionType / constraints / default
units

Residential 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_rent

USD 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_area

Square 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!C230; effective default 0.

Type: number

Constraints: min: 0

Default: 0

market_floor_area

Square 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!C229; effective default 0.

Type: number

Constraints: min: 0

Default: 0

acquisition_basis

USD, building acquisition eligible basis allocation; nonnegative unless stated. Default 0 (blank numeric source cells act as zero). Source location Budget & Costs!F253; effective default 0.

Type: number

Constraints: min: 0

Default: 0

rehabilitation_basis

USD, residential rehabilitation eligible basis allocation; nonnegative unless stated. Default 0 (blank numeric source cells act as zero). Source location Budget & Costs!G279; effective default 0.

Type: number

Constraints: min: 0

Default: 0

new_construction_basis

USD, residential new construction eligible basis allocation; nonnegative unless stated. Default 0 (blank numeric source cells act as zero). Source location Budget & Costs!H278; effective default 0.

Type: number

Constraints: min: 0

Default: 0

rehab_qct_fraction

Decimal 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 LIHTCs!D13; effective default 0.0.

Type: number

Constraints: min: 0; max: 1

Default: 0

new_qct_fraction

Decimal 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 LIHTCs!E13; effective default 0.0.

Type: number

Constraints: min: 0; max: 1

Default: 0

rehab_credit_rate

Annual decimal credit rate 0..1; nonnegative unless stated. Default 0.04 (blank numeric source cells act as zero). Source location LIHTCs!D31; effective default 0.04.

Type: number

Constraints: min: 0; max: 1

Default: 0.04

new_credit_rate

Annual decimal credit rate 0..1; nonnegative unless stated. Default 0.04 (blank numeric source cells act as zero). Source location LIHTCs!E31; effective default 0.04.

Type: number

Constraints: min: 0; max: 1

Default: 0.04

program

Text program switch: 4% LIHTC or 9% Competitive; activates competitive basis boost tests. Source default 4% LIHTC. Source location Project Details!C20; effective default 4% LIHTC.

Type: string

Constraints: one of: , 4% LIHTC, 9% Competitive

Default: "4% LIHTC"

rent_limit_ami_fraction

Decimal 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

first_year_vacancy

Decimal fraction 0..1, first-year cashflow vacancy; nonnegative unless stated. Default 0.07 (blank numeric source cells act as zero). Source location Budget & Costs!E83; effective default 0.07.

Type: number

Constraints: min: 0; max: 1

Default: 0.07

vacancy

Decimal 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

Outputs (5)
Port / descriptionType / constraints / default
annual_credit_eligible

USD per year, eligible ceiling before a separate requested amount; calculated from publisher's workbook formulas.

Type: number

acquisition_credit

USD per year; calculated from publisher's workbook formulas.

Type: number

rehabilitation_credit

USD per year; calculated from publisher's workbook formulas.

Type: number

new_construction_credit

USD per year; calculated from publisher's workbook formulas.

Type: number

applicable_fraction

Decimal 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/mcp

Slug: ohfa-lihtc-4pct · 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-4pct",
    "edition": "2023",
    "flow": "quick_lihtc_credit_sizing",
    "inputs": {
      "units": 60,
      "monthly_tenant_rent": 950,
      "low_income_floor_area": 54000,
      "acquisition_basis": 2000000,
      "rehabilitation_basis": 6000000,
      "program": "4% LIHTC",
      "rent_limit_ami_fraction": 0.6,
      "first_year_vacancy": 0.07,
      "vacancy": 0.07
    }
  }
}

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.

39 input ports · 14 output ports. Full membership in the port contract.

REST example

Bundle scenario: rehabilitation_quick · Flow: quick_lihtc_credit_sizing

curl 'https://catalog.formualizer.com/v1/models/ohfa-lihtc-4pct/2023/evaluate?flow=quick_lihtc_credit_sizing' \
  -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":"4% LIHTC","rent_limit_ami_fraction":0.6,"first_year_vacancy":0.07,"vacancy":0.07}}'

Expected response

Expected values from the bundle, subject to the check level above.

{
  "outputs": {
    "annual_credit_eligible": 320000,
    "acquisition_credit": 80000,
    "rehabilitation_credit": 240000,
    "new_construction_credit": 0,
    "applicable_fraction": 1
  }
}

Looking for another spreadsheet? Request a workbook.