An unlocked Excel calculator that rebuilds a workers' compensation experience mod from payroll, expected loss rates, D-ratios and your claim list, and shows what each claim costs in mod points.
Enter payroll by class for the three policy years of the experience period, the expected loss rate and D-ratio of each class, your state's split point, accident limitation, ERA factor, weight and ballast, and up to 200 claims. In the prefilled case (Brannock Supply Co., a fictional distributor, 84 claims in 2023 to 2025), expected losses of $935,698 against actual losses of $1,108,526 after ERA and limitation give a mod of 1.1980, rounded 1.20. On Brannock's 2027 policy that mod costs $154,125 a year.
One unlocked Excel workbook, zero macros, zero circular references. Free: enter your email address and the download opens at once, along with the other free finance models.
Experience_Mod_Calculator.xlsx (20 KB)
You join the reader list: one email when a new free model or book comes out. Unsubscribe in one click. Already subscribed? Enter the same address. How your address is used.
This calculator is a standalone extract of the 12-tab workbook in The Workers' Compensation Handbook, which follows one employer from class codes and premium to the experience mod, the premium audit, claims and return to work, the loss pick and the choice between guaranteed cost, a retrospective plan and a large deductible.
| Measure | Value |
|---|---|
| Expected losses E | $935,698 |
| Expected primary Ep (E x D-ratio) | $301,923 |
| Expected excess Ee | $633,775 |
| Actual primary Ap (after ERA, split at $20,000) | $508,626 |
| Actual excess Ae (limited at $260,000) | $599,900 |
| Weight W and ballast B (row from $500,000) | 0.17 and $79,000 |
| Numerator | $1,215,642 |
| Denominator | $1,014,698 |
| Experience mod | 1.1980, rounded 1.20 |
Read the claim column before the total. The largest claim, a 2023 tractor-trailer rollover incurred at $312,000, is capped at $260,000 and splits into $20,000 primary and $240,000 excess: it adds only $60,800 to the numerator. Removing it entirely would bring the mod to 1.14. Actual primary losses are 1.68 times expected primary while actual excess is 0.95 times expected excess: the debit comes from the count of claims, not from the rollover. One more $60,000 lost-time claim would add $26,800 to the numerator and 0.0264 of mod, about $15,412 of annual premium.
For the method step by step, read how to calculate an experience mod; for how the mod flows into a loss-sensitive program, the workers' comp retrospective rating plan; for the payroll side of the premium, the workers' comp premium audit.
Formula as published by NCCI for its experience rating plan; state-specific split points and limitations since ratings effective November 1, 2023. Rules described in principle as read in November 2026; states differ. The company, its claims and the case state's values are fictional. Education only, not insurance advice.
The NCCI split rating formula: mod = (Ap + W x Ae + (1 - W) x Ee + B) / (E + B), rounded to two decimals. Expected losses are payroll / 100 x the expected loss rate of each class, and expected primary losses are E x the D-ratio. In the prefilled case the numerator is $1,215,642, the denominator $1,014,698 and the mod 1.1980, rounded 1.20.
On your rating bureau's experience rating worksheet and in your state's filed experience rating values. The prefilled values belong to a fictional case state and are teaching assumptions: replace every blue cell, including the split point and the state accident limitation, before reading your result. California and New York use their own rating plans.
Under the Experience Rating Adjustment, medical-only claims enter the rating at 30 percent of their value in the states that apply it. In the case, 60 medical-only claims of $176,420 enter at $52,926. Set the factor to 1.00 if your state does not apply the ERA: Brannock's mod would then be 1.32.
No. It reproduces the arithmetic so you can check, project and explain the mod. The bureau's published worksheet is the document of record, and its claim values, payroll and rating values govern.