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.
Saved workbook valuesInputOutput
| Row | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 3 | Annual Deposit Rate of Change | |||||
| 4 | Initial Rate of Change: | Relative Year of Change: | New Rate of Change: | |||
| 5 | eTool Data (Existing): | 0.00% | 1 | 2.00% | ||
| 6 | User Trial Input: | 0.00% | 1 | 2.00% | ||
| 7 | ||||||
| 8 | Deposit Amounts | |||||
| 11 | User Trial Input: | $466,925 | $556.00 | |||
| 17 | PASS | FAIL | PASS |
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 / description | Type / constraints / default |
|---|---|
initial_reserveUSD, 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_unitUSD/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_escalationDecimal 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_yearRelative 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_escalationDecimal 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_amountUSD principal, positive. Default 20000000. Affects amortization mitigation and debt service. Source default: 20000000. | Type: number Constraints: min: 0.000001 Default: 20000000 |
mortgage_couponDecimal annual coupon, 0.025 = 2.5%. Default 0.025. Source default: 0.025. | Type: number Constraints: min: 0 Default: 0.025 |
mortgage_yearsYears, positive amortizing mortgage term. Default 35. Source default: 35. | Type: number Constraints: min: 0.000001 Default: 35 |
annual_mipDecimal annual mortgage insurance premium. Default 0.0065. Source default: 0.0065. | Type: number Constraints: min: 0 Default: 0.0065 |
assumed_dscrDimensionless 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 / description | Type / constraints / default |
|---|---|
year1_reserveUSD end-year-1 reserve; signed, negative means deficit. | Type: number |
year10_reserveUSD end-year-10 reserve; signed. | Type: number |
year20_reserveUSD end-year-20 reserve; signed. | Type: number |
annual_debt_serviceUSD/year principal and interest plus annual MIP. | Type: number |
assumed_case_dscrDimensionless resulting coverage at assumed_dscr; DSC adjustment remains source zero. | Type: number |
first10_resultEnum 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/mcpSlug: 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.