formualizer

Excel workbook · Catalog edition v30.3

HUD CNA Reserve for Replacement Financial Factors

U.S. Department of Housing and Urban Development (HUD — Multifamily CNA e-Tool)

Test reserve deposits and mortgage debt service against the pinned HUD CNA example, with annual reserve balances and minimum-balance diagnostics.

Inputs
32
Outputs
12
File size
728 KB
Verification
Independent scenario check

Try it

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

Trial Deposits AnalysisB3:G19

Saved workbook valuesInputOutput

RowBCDEFG
3Annual Deposit Rate of Change
4Initial Rate of Change:Relative Year of Change:New Rate of Change:
5eTool Data (Existing):0.00%12.00%
6User Trial Input:0.00%12.00%
7
8Deposit Amounts
11User Trial Input:$466,925$556.00
17PASSFAILPASS

What was checked

Changed-input scenarios match an independent calculation.

Scope and limitations

Catalog calculations, not certification.

Try it uses Formualizer’s exact-match correction for three risk-analysis formulas.

  • Pinned historic v30.3 example; latest publisher edition not checked.
  • 456 units are derived from the original financial schedule; no unit-count input is exposed.
  • This flow varies trial deposits and the fixed 20-year inflated-needs schedule, not a new CNA component inventory.
  • Publisher trial amortization diagnostic C65:C74 references original eTool balances; preserve and interpret with care.
  • Risk and DSC adjustment cells remain zero as instructed by the workbook. DSC output equals the assumed coverage without an NOI adjustment.
  • Source diagnostic I65:J65 divides by zero for years with no needs; these error cells are excluded from outputs.
  • MATCH shim supports exact matches only; all calls in the pinned source use 0 or FALSE.
  • Runtime MATCH with FALSE returns #N/A for three risk-analysis formulas. The runtime adapter implements exact-match semantics for all MATCH uses in this pinned workbook; no source formula edits.

What it computes

Ports for the compact flow: quick_reserve_analysis. The full port contract includes all 44 ports.

Inputs (10)
Port / descriptionType / constraints / default
initial_reserve

USD, nonnegative initial reserve deposit. Default 466925. Replaces source formula with trial value. Source default: 466925.

Type: number

Constraints: min: 0

Default: 466925

annual_deposit_per_unit

USD/unit/year, nonnegative first-year deposit. Default 556; source example has 456 units. Source default: 556.

Type: number

Constraints: min: 0

Default: 556

initial_deposit_escalation

Decimal fraction per year (0.02 = 2%). Default 0; first year uses this rate. Source default: 0.

Type: number

Constraints: min: 0; max: 1

Default: 0

deposit_change_year

Relative year, integer 1..20. Default 1; from year 2, new rate applies when year >= this value. Source default: 1.

Type: number

Constraints: min: 1; max: 20

Default: 1

new_deposit_escalation

Decimal fraction per year. Default 0.02; applies from change year, except year 1 uses initial rate. Source default: 0.02.

Type: number

Constraints: min: 0; max: 1

Default: 0.02

mortgage_amount

USD principal, positive. Default 20000000. Affects amortization mitigation and debt service. Source default: 20000000.

Type: number

Constraints: min: 0.000001

Default: 20000000

mortgage_coupon

Decimal annual coupon, 0.025 = 2.5%. Default 0.025. Source default: 0.025.

Type: number

Constraints: min: 0

Default: 0.025

mortgage_years

Years, positive amortizing mortgage term. Default 35. Source default: 35.

Type: number

Constraints: min: 0.000001

Default: 35

annual_mip

Decimal annual mortgage insurance premium. Default 0.0065. Source default: 0.0065.

Type: number

Constraints: min: 0

Default: 0.0065

assumed_dscr

Dimensionless assumed debt service coverage ratio. Default 1.2; this is an assumed NOI case, not an underwritten actual NOI. Source default: 1.2.

Type: number

Default: 1.2

Outputs (6)
Port / descriptionType / constraints / default
year1_reserve

USD end-year-1 reserve; signed, negative means deficit.

Type: number

year10_reserve

USD end-year-10 reserve; signed.

Type: number

year20_reserve

USD end-year-20 reserve; signed.

Type: number

annual_debt_service

USD/year principal and interest plus annual MIP.

Type: number

assumed_case_dscr

Dimensionless resulting coverage at assumed_dscr; DSC adjustment remains source zero.

Type: number

first10_result

Enum PASS or FAIL for first-ten-year required-minimum test.

Type: string

Questions this workbook answers

  • How do higher annual reserve deposits change HUD CNA balances?
  • Test multifamily reserve deposits and mortgage debt service
  • Calculate HUD reserve minimum balance shortfalls

Questions and answers

Is this a publisher-operated service?

No. This independent executable mapping cites and preserves the pinned publisher workbook. It is not publisher endorsed.

What are the scope and units?

USD unless labeled USD/unit/year; percentage inputs use decimal fractions. Fixed 456-unit, 20-year source example. Full flow adds annual inflated needs, minimum reserve and amortization mitigation.

For agents

Connect an MCP client to the catalog endpoint:

https://catalog.formualizer.com/v1/mcp

Slug: hud-cna-reserve-factors · Edition: v30.3

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": "hud-cna-reserve-factors",
    "edition": "v30.3",
    "flow": "quick_reserve_analysis",
    "inputs": {
      "initial_reserve": 1000000,
      "annual_deposit_per_unit": 900
    }
  }
}

cellOverrides sets individual cells. selectCells reads at most 256 cells per request. trace requests formula lineage.

Flows

quick_reserve_analysis

Quick reserve analysis

Main decision inputs; omitted inputs retain the pinned source example defaults.

10 input ports · 6 output ports. Full membership in the port contract.

full_reserve_analysis

Full reserve analysis

32 input ports · 12 output ports. Full membership in the port contract.

REST example

Bundle scenario: funded_reserve · Flow: quick_reserve_analysis

curl 'https://catalog.formualizer.com/v1/models/hud-cna-reserve-factors/v30.3/evaluate?flow=quick_reserve_analysis' \
  -H 'Content-Type: application/json' \
  --data '{"inputs":{"initial_reserve":1000000,"annual_deposit_per_unit":900}}'

Expected response

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

{
  "outputs": {
    "year1_reserve": 1420400,
    "year10_reserve": 2879318.215989068,
    "year20_reserve": 3394110.179226563,
    "annual_debt_service": 987988.5169231384,
    "assumed_case_dscr": 1.2,
    "first10_result": "PASS"
  }
}

Looking for another spreadsheet? Request a workbook.