Free Excel templates

A free ACA employer mandate penalty calculator in Excel

Section 4980H month by month: the 95 percent offer test, the (a) payment, the (b) payment and its cap, the amounts for 2025 to 2027, and the three affordability safe harbor ceilings.

The example is a single applicable large employer with 120 full-time employees in every month of 2026, so 6 of them may go without an offer before the test fails. Offering coverage to 110, it fails, and with at least one full-time employee holding a premium tax credit the (a) exposure for the year is $300,600 at $3,340. Offering to 116, it passes, and 6 employees with a credit all year give a (b) exposure of $30,060 at $5,010. Replace the blue cells with your own figures.

Get the calculator

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other free models.

ACA_Employer_Mandate_Penalty_Calculator.xlsx (14 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this calculator

The calculator repeats the monthly logic of The ACA Employer Mandate Handbook. The book follows one employer group through 2025: a parent that measures hours with the look-back method and a subsidiary that does not, an (a) exposure of $10,633.33 and a (b) exposure of $10,150.00, the Forms 1095-C that reported them and a Letter 226-J.

DRM-free PDF, $19.99 → · Paperback, $32.99, shipped worldwide

What is in the file

Five tabs. Blue cells are inputs; every result is a formula, and the last tab compares the results with the example.

Scenario 1 and Scenario 2
One row per month: full-time employees, those offered coverage, those with a premium tax credit and the share of 30; then the employees not offered, the number allowed (5 percent or five), whether the offer test is met, the (a) payment, the (b) payment before and after the cap, and the total for the year. The year of the amounts is chosen at the top.
Amounts
The indexed (a) and (b) amounts for 2025 ($2,900 and $4,350), 2026 ($3,340 and $5,010) and 2027 ($3,780 and $5,670), and the required contribution percentages for 2025 and 2026.
Safe harbors
The ceilings of the rate of pay, Form W-2 and federal poverty line safe harbors for the contribution, hourly rate, wages and poverty guideline you enter. In the example, a contribution of $150 against ceilings of $194.22, $232.40 and $129.89 meets the rate of pay and Form W-2 safe harbors and fails the federal poverty line safe harbor.
Checks
Each result against the example value printed in the book.

Three things to try

Offer coverage to one more person. In Scenario 1, raise the number offered until the employees not offered fall to 6: the test is met, the (a) exposure disappears for that month, and only employees with a credit can still cost money, under (b).

Enter a group member's share. For a member of a controlled group, replace 30 with the member's allocable share for each month. The (a) payment and the cap on (b) both rise as the share falls.

Test a contribution. On the Safe harbors tab, lower the contribution until all three safe harbors are met. The federal poverty line ceiling does not depend on any employee's rate or wages, which is why it can be checked before the plan year begins.

Questions

Does the calculator tell me what I owe?

No. It computes exposures to discuss with a CPA. The IRS computes any payment from its own records, member by member and month by month, and proposes it in Letter 226-J.

Does it decide whether a company is an applicable large employer?

No. ALE status comes from the prior year's full-time employees and FTEs of the whole controlled group; the book works that count in chapters 2 and 3.

Does it check minimum value?

No. Whether a plan provides minimum value is a question for the plan's insurer or ERISA counsel; the calculator takes the offer and the credits as given.

Related reading