Excel workbook · Catalog edition 2025
CMS Actuarial Value (AV) Calculator 2025
Centers for Medicare & Medicaid Services (CMS / CCIIO)
Explore CMS 2025 actuarial value, metal tiers and Silver CSR scenarios with reproducible inputs.
- Inputs
- 217
- Outputs
- 6
- File size
- 2103 KB
- Verification
- Engine regression check
Try it
Try it loads a copy of this file from formualizer.com.
Saved workbook valuesInputOutput
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | User Inputs for Plan Parameters | ||||||
| 2 | Use Integrated Medical and Drug Deductible? | HSA/HRA Options | Tiered Network Option | ||||
| 4 | Apply Skilled Nursing Facility Copay per Day? | Annual Contribution Amount: | 1st Tier Utilization: | ||||
| 10 | Deductible ($) | ||||||
| 11 | Coinsurance (%, Insurer's Cost Share) | ||||||
| 12 | MOOP ($) | ||||||
| 20 | Primary Care Visit to Treat an Injury or Illness (exc. Preventive, and X-rays) | ||||||
| 21 | Specialist Visit |
What was checked
Scenarios match the engine’s own earlier results, not an independent check.
Scope and limitations
Catalog calculations, not certification.
Try it uses Formualizer’s JavaScript port of the workbook’s VBA macros to calculate actuarial value.
Estimates the share of essential health benefit costs paid by a health plan using the CMS 2025 continuance distributions. Experimental integrated medical/drug plan port with metal-tier and CSR scenario workflows.
The catalog marks this implementation as experimental. Bundle release status: candidate-blocked.
- The 2025 continuance tables use 2021 EDGE claims projected to 2025.
- Native Excel/VBA scenario parity remains unchecked.
- Separate deductible/MOOP, tiered networks, per-diem and optional benefit-limit branches are mapped but rejected.
- Requested B4:B7 outputs are adapter aliases; original CMS outputs are B48:B50.
- JavaScript port of the publisher's VBA macros; the publisher's VBA macro does not run. Independent Excel/VBA scenario parity remains unchecked.
- Release blocker (remaining work): Complete separate-deductible/MOOP, tiered-network and optional-benefit VBA branches.
- Release blocker (remaining work): Complete service cost-sharing override precedence and compare against native Excel/VBA oracle.
What it computes
Ports for the compact flow: quick_metal_check. The full port contract includes all 223 ports.
Inputs (11)
| Port / description | Type / constraints / default |
|---|---|
desired_metal_tierAdapter mirror of native HelperTab!D2: 1 Platinum, 2 Gold, 3 Silver, 4 Bronze. Source dropdown baseline is 3. | Type: integer Constraints: one of: 1, 2, 3, 4 Default: 3 |
deductibleUSD per year. | Type: number Constraints: min: 0; max: 2000000; nullable: true Default: null |
coinsuranceInsurer share, fraction 0–1; 0.8 means 80% plan-paid. | Type: number Constraints: min: 0; max: 1; nullable: true Default: null |
moopUSD per year. | Type: number Constraints: min: 0; max: 9200; nullable: true Default: null |
hsa_contributionUSD per year. | Type: number Constraints: min: 0; max: 2000000; nullable: true Default: null |
er_copayUSD per service; cost sharing flags are independent. | Type: number Constraints: min: 0; nullable: true Default: null |
inpatient_copayUSD per service; cost sharing flags are independent. | Type: number Constraints: min: 0; nullable: true Default: null |
pcp_copayUSD per service; cost sharing flags are independent. | Type: number Constraints: min: 0; nullable: true Default: null |
specialist_copayUSD per service; cost sharing flags are independent. | Type: number Constraints: min: 0; nullable: true Default: null |
generic_rx_copayUSD per service; cost sharing flags are independent. | Type: number Constraints: min: 0; nullable: true Default: null |
preferred_brand_rx_copayUSD per service; cost sharing flags are independent. | Type: number Constraints: min: 0; nullable: true Default: null |
Outputs (3)
| Port / description | Type / constraints / default |
|---|---|
av_percentPercentage points, 0–100, four-decimal AV ratio rounded then multiplied by 100. | Type: number |
metal_tierMetal label; Out of Range if outside selected standard. | Type: string |
status_notesCalculation status and experimental implementation notice. | Type: string |
Questions this workbook answers
- Calculate 2025 ACA actuarial value and metal tier
- CMS 2025 AV calculator API
- Compare Silver CSR 73 87 and 94 plan variants
- Evaluate deductible coinsurance and out-of-pocket plan design
Questions and answers
Is this the CMS-issued application?
The source workbook is published by CMS. This experimental Formualizer package ports a subset of its VBA and is not CMS certification.
Can I use the same plan design for 2025 and 2027?
You can compare inputs across editions, but the claims tables and validation differ, so actuarial values may change.
Does the CSR matrix convert a plan automatically?
It evaluates four explicit sample designs against Standard Silver and CSR 73%, 87% and 94% targets. It does not infer compliant benefit designs.
For agents
Connect an MCP client to the catalog endpoint:
https://catalog.formualizer.com/v1/mcpSlug: cms-av-calculator · Edition: 2025
- 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": "cms-av-calculator",
"edition": "2025",
"flow": "full_plan_design",
"inputs": {
"desired_metal_tier": 4,
"deductible": 7500,
"coinsurance": 0.6,
"moop": 9200,
"hsa_contribution": 0,
"pcp_copay": 0,
"specialist_copay": 0,
"generic_rx_copay": 0
}
}
}cellOverrides sets individual cells. selectCells reads at most 256 cells per request. trace requests formula lineage.
Flows
quick_metal_check
Quick metal check
Evaluate an integrated plan. Copay flags retain source checked defaults unless explicitly overridden (see presets).
11 input ports · 3 output ports. Full membership in the port contract.
full_plan_design
Full plan design
Full mapped input contract including service flags and reserved separate-Rx/tiered/limit ports. This edition rejects unported branches instead of ignoring inputs.
217 input ports · 6 output ports. Full membership in the port contract.
csr_variant_matrix
Silver CSR variant matrix
One call evaluates four independently reset scenario presets. Each variant overrides its benefit parameters and uses Silver/Gold/Platinum continuance distributions for 73/87/94. These are sample plan designs, not automatic CSR benefit derivation.
11 input ports · 6 output ports. Full membership in the port contract.
REST example
Bundle scenario: bronze · Flow: full_plan_design
curl 'https://catalog.formualizer.com/v1/models/cms-av-calculator/2025/evaluate?flow=full_plan_design' \
-H 'Content-Type: application/json' \
--data '{"inputs":{"desired_metal_tier":4,"deductible":7500,"coinsurance":0.6,"moop":9200,"hsa_contribution":0,"pcp_copay":0,"specialist_copay":0,"generic_rx_copay":0}}'Expected response
Expected values from the bundle, subject to the check level above.
{
"outputs": {
"av_percent": 60.12,
"metal_tier": "Bronze",
"status_notes": "Calculation Successful. Experimental integrated-plan JS port; Excel/VBA oracle parity unverified.",
"effective_coin_num": 3017.53944854406,
"adj_deductible": 8822.62923517528,
"adj_moop": 9200
}
}Looking for another spreadsheet? Request a workbook.