Health Actuarial & Value-Based Care Finance
Managed Care PMPM Capitation & IBNR Pool Formula
Calculate Risk-Adjusted PMPM Capitation Pool Revenue, Actuarial Completion-Factor Ultimate Incurred Claims (Paid / Completion_Factor), Medical Cost Ratio (MCR), and Net Risk Margin PMPM.
Divide paid claims by the actuarial completion factor—never multiply by (1 + lag)!
Risk-bearing medical groups and Accountable Care Organizations (ACOs) accepting full-risk Per-Member-Per-Month (PMPM) capitation earn Monthly_Cap_Pool = Attributed_Lives * RAF_Score * Base_PMPM (where RAF_Score is the CMS Hierarchical Condition Category risk-adjustment factor). Because hospital and specialist claims arrive 30–90 days after date of service, actuaries estimate Ultimate Incurred Claims from Paid-to-Date Claims using Completion Factor CF_lag = Paid_To_Date / Ultimate_Incurred: Ultimate_Claims = Paid_Claims / CF_lag (so IBNR Reserve = Paid_Claims * (1 - CF_lag) / CF_lag)! Multiplying Paid_Claims * (2 - CF_lag) is a classic finance-team error: at CF_lag = 0.875, $3.50M / 0.875 equals $4.00M ($500k IBNR), whereas $3.50M * 1.125 understates liability by $62,500!
Verify on Attributed_Lives = 10,000 members (B2), Cohort_RAF_Score = 1.15 (B3), Base_Capitation_PMPM = $400.00 (B4 -> Risk-Adjusted Pool = 10,000 * 1.15 * $400 = $4,600,000.00 in E2), Paid_Claims_To_Date = $3,500,000 (B5), and Lag_Completion_Factor = 0.875 (87.5%, B6): Ultimate Incurred Claims equals 3,500,000 / 0.875 = $4,000,000.00 (E3); Medical Cost Ratio MCR is 4.00M / 4.60M = 0.8696 (86.96% in E4); and Net Capitation Risk Margin is +$600,000 / 10,000 = +$60.00 PMPM (E5)!
| Full-Risk Capitation Metric (10k Lives, RAF=1.15, $400 PMPM, $3.5M Paid, CF=0.875) | Formula | Verified Output |
|---|---|---|
| Risk-Adjusted Capitation Pool ($) | Lives * RAF_Score * Base_PMPM | $4,600,000.00 ($460.00 PMPM) |
| Ultimate Incurred Claims ($) | Paid_Claims / Completion_Factor | $4,000,000.00 ($500k IBNR) |
| Medical Cost Ratio (MCR) | Ultimate_Incurred / Capitation_Pool | 0.8696 (86.96%) |
| Net Capitation Margin PMPM ($) | (Cap_Pool - Ultimate_Claims) / Lives | +$60.00 PMPM (-$20 @ $4.2M Paid) |
Completion-Adjusted Capitation PMPM Margin Formula
Validate Lives > 0, RAF_Score > 0, Base_PMPM > 0, Paid_Claims >= 0, and 0 < Completion_Factor <= 1!
=LET(lv_n, B2, rf_s, B3, pm_b, B4, pd_c, B5, cf_l, B6, IF(OR(lv_n <= 0, rf_s <= 0, pm_b <= 0, pd_c < 0, cf_l <= 0, cf_l > 1), "Invalid Capitation Pool", LET(cp_p, lv_n * rf_s * pm_b, ul_c, pd_c / cf_l, ROUND((cp_p - ul_c) / lv_n, 2))))Winter Utilization Surge & Run-Out Check
Test Paid_Claims = $4,200,000 in cell B5: Ultimate Incurred hits $4,800,000 and Net Margin flips to -$20.00 PMPM! Test fully run-out claims (CF = 1.00): IBNR vanishes and Ultimate Incurred equals Paid_Claims!
=IF(ISTEXT(E2), E2, ROUND((B5 / B6) / E2, 4))Try the example
Use the original values or change them to test your understanding.