formualizer

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

See the latest catalog edition: 2027

Try it

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

AV CalculatorA1:G46

Saved workbook valuesInputOutput

RowABCDEFG
1User Inputs for Plan Parameters
2Use Integrated Medical and Drug Deductible?HSA/HRA OptionsTiered Network Option
4Apply Skilled Nursing Facility Copay per Day?Annual Contribution Amount:1st Tier Utilization:
10Deductible ($)
11Coinsurance (%, Insurer's Cost Share)
12MOOP ($)
20Primary Care Visit to Treat an Injury or Illness (exc. Preventive, and X-rays)
21Specialist 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 / descriptionType / constraints / default
desired_metal_tier

Adapter 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

deductible

USD per year.

Type: number

Constraints: min: 0; max: 2000000; nullable: true

Default: null

coinsurance

Insurer share, fraction 0–1; 0.8 means 80% plan-paid.

Type: number

Constraints: min: 0; max: 1; nullable: true

Default: null

moop

USD per year.

Type: number

Constraints: min: 0; max: 9200; nullable: true

Default: null

hsa_contribution

USD per year.

Type: number

Constraints: min: 0; max: 2000000; nullable: true

Default: null

er_copay

USD per service; cost sharing flags are independent.

Type: number

Constraints: min: 0; nullable: true

Default: null

inpatient_copay

USD per service; cost sharing flags are independent.

Type: number

Constraints: min: 0; nullable: true

Default: null

pcp_copay

USD per service; cost sharing flags are independent.

Type: number

Constraints: min: 0; nullable: true

Default: null

specialist_copay

USD per service; cost sharing flags are independent.

Type: number

Constraints: min: 0; nullable: true

Default: null

generic_rx_copay

USD per service; cost sharing flags are independent.

Type: number

Constraints: min: 0; nullable: true

Default: null

preferred_brand_rx_copay

USD per service; cost sharing flags are independent.

Type: number

Constraints: min: 0; nullable: true

Default: null

Outputs (3)
Port / descriptionType / constraints / default
av_percent

Percentage points, 0–100, four-decimal AV ratio rounded then multiplied by 100.

Type: number

metal_tier

Metal label; Out of Range if outside selected standard.

Type: string

status_notes

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

Slug: 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.