Free Excel Templates

Experience Mod Calculator (Excel)

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.

Get the Excel calculator

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.

The handbook behind this calculator

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.

Paperback PDF edition, $19.99 Book page and full workbook

What is inside the workbook

Calculator (one sheet)
At the top, your rounded mod. Then up to ten classes with payroll for each experience year, ELR and D-ratio, and the expected losses and expected primary losses they produce; the rating values block (split point, state accident limitation, ERA factor, weight and ballast); the result block (E, Ep, Ee, Ap, Ae, numerator, denominator, raw and rounded mod); and up to 200 claim rows, each typed MO (medical only) or LT (lost time), reduced by the ERA, capped by the limitation, split into primary and excess, with the mod points the claim adds in the last column.

What the prefilled example returns

MeasureValue
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 mod1.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.

Questions practitioners ask

Which formula does the calculator use?

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.

Where do I find my own ELRs, D-ratios, weight and ballast?

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.

What does the ERA factor do?

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.

Can the calculator replace the bureau's worksheet?

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.