Health Insurance Regulation & Actuarial Filing
ACA Medical Loss Ratio (MLR) & Rebate Formula
Calculate Statutory Health Insurer Adjusted Premium Denominator, HCQI-Adjusted Medical Loss Ratio (Claims + Quality_Improvement) / (Premiums - Regulatory_Assessments), and Mandatory Policyholder Rebate.
Include certified Quality Improvement in the numerator and deduct regulatory fees from premiums!
Unlike simple GAAP loss ratios (Claims / Gross_Premiums), the statutory Affordable Care Act Medical Loss Ratio (45 CFR Part 158) adjusts BOTH sides of the fraction before testing the 80.0% Individual/Small-Group or 85.0% Large-Group minimum floor: (1) the Numerator adds certified Health Care Quality Improvement (HCQI) expenses—such as care-coordination nurses and readmission prevention—onto Incurred Clinical Claims; and (2) the Denominator subtracts federal/state licensing and regulatory assessments from Earned Premiums: MLR_ACA = (Incurred_Claims + HCQI_Spend) / (Earned_Premiums - Regulatory_Assessments)! Whenever MLR_ACA falls below Statutory_Target, the insurer owes policyholders a cash/premium-credit Rebate = MAX(0, (Statutory_Target - MLR_ACA) * Adjusted_Denominator)!
Verify on Earned_Premiums = $105,000,000 (B2), Deductible_Regulatory_Assessments = $5,000,000 (B3 -> Adjusted Denominator = $100,000,000 in E2), Incurred_Clinical_Claims = $74,000,000 (B4), Certified_HCQI_Spend = $3,000,000 (B5 -> Numerator = $77,000,000), and Small_Group_Target_MLR = 0.80 (80.0%, B6): Attained ACA MLR equals 77M / 100M = 0.7700 (77.00% in E3); Mandatory Member Rebate Pool equals (0.80 - 0.77) * $100,000,000 = $3,000,000.00 (E4); and Filing Status flags "Rebate Owed (MLR Below Statutory Floor)" (E5)!
| 45 CFR Part 158 MLR Metric ($105M Prem, $5M Reg Fees, $74M Claims, $3M HCQI, 80% Min) | Formula | Verified Output |
|---|---|---|
| Adjusted Premium Denominator ($) | Earned_Premiums - Regulatory_Assessments | $100,000,000.00 |
| Attained Statutory MLR | (Claims + HCQI) / Adjusted_Denominator | 0.7700 (77.00% vs 80.0% Min) |
| Mandatory Member Rebate ($) | MAX(0, (Target_MLR - MLR) * Adj_Denom) | $3,000,000.00 ($0 @ $78M Claims) |
| Regulatory Filing Status | Attained_MLR >= Statutory_Target_MLR | Rebate Owed (MLR Below Statutory Floor) |
Statutory MLR Mandatory Policyholder Rebate Formula
Validate Earned_Premiums > Regulatory_Assessments >= 0, non-negative Claims and HCQI, and Target_MLR in (0.50, 1.00)!
=LET(pr_e, B2, rg_f, B3, cl_i, B4, hc_q, B5, tg_m, B6, IF(OR(pr_e <= 0, rg_f < 0, rg_f >= pr_e, cl_i < 0, hc_q < 0, tg_m <= 0.5, tg_m >= 1), "Invalid MLR Filing", LET(dn_a, pr_e - rg_f, ml_r, (cl_i + hc_q) / dn_a, ROUND(MAX(0, (tg_m - ml_r) * dn_a), 2))))Individual/Small-Group (80%) vs Large-Group (85%) Segment Test
Raise Incurred_Claims to $78,000,000 (MLR = 81.00%): Small-Group (80% target) clears with $0.00 rebate, whereas Large-Group (85% target in cell B6) triggers a $4,000,000.00 rebate!
=IF(ISTEXT(E4), E4, IF(E4 > 0, "Rebate Owed (MLR Below Statutory Floor)", "Compliant (Zero MLR Rebate)"))Try the example
Use the original values or change them to test your understanding.