How to find missing and unpaid insurance commissions for Insurance Agencies

1. Step 1: Build Your Book-of-Business Baseline

   Before you can find what is missing, you need a single source of truth for what should be paying. Start by creating a master spreadsheet that lists every active policy in your book. Each row represents one member or policy. Required columns include: member last name, policy or member ID, carrier name, product type (Medicare Advantage, Med Supp, life, etc.), effective date, current status (active, pending, termed), and the commission rate or dollar amount you expect each month.

   This baseline does not need to be perfect on day one, but it must be comprehensive. Pull data from your agency management system if you use one (AMS360, Vertafore, EZLynx, or similar). If you do not have an AMS, reconstruct the list from historical commission statements, application tracking spreadsheets, and carrier enrollment confirmations. For Medicare lines, include the plan name and contract year, because commission rates reset each Annual Enrollment Period and carriers sometimes revert to old rates by mistake.

   Once you have the baseline, add a column for the last month you confirmed payment. This will become your tracking mechanism. When you receive a statement and confirm a member paid, update that column. Members whose last-confirmed date falls more than 45 days behind the current month are your first candidates for missing commissions. The goal is not to track every nuance of every policy — the goal is to know which members you expect to generate revenue, so you can notice when they do not.

   Many agencies skip this step and jump straight to comparing statements, but without a baseline you are only catching errors the carrier happens to reveal. You will miss members who disappear entirely from statements, which is the most common form of underpayment.

2. Step 2: Standardize Incoming Commission Statements

   Carriers send statements in wildly inconsistent formats. One carrier's CSV uses 'Member ID' while another uses 'Subscriber Number.' One lists commission per member per month; another aggregates by product line. Before you can compare anything, you need to normalize these files into a common structure. Create a second spreadsheet — call it your reconciliation workbook — with standardized column headers: Member Last Name, Member ID, Carrier, Product, Effective Date, Commission Amount, Statement Month, and Source File.

   For each carrier statement you download, copy the relevant data into this reconciliation workbook and map their columns to yours. If a carrier sends a PDF, you will need to extract the data first. Some PDFs allow you to copy and paste into Excel with formatting intact. Others require a PDF-to-Excel converter tool or manual transcription. Carriers that send image-based PDFs are the worst offenders — if you manage significant volume with one of these carriers, consider calling their producer services team and requesting machine-readable formats. Many carriers will accommodate the request if you explain it is for reconciliation purposes.

   Once all statements for a given month are in your reconciliation workbook, sort by member ID or last name. This lets you visually scan for duplicates, which sometimes indicate a carrier paid twice (rare but it happens, and you will owe it back during the next true-up). More often, sorting reveals gaps — members in your baseline who are absent from every statement that month.

   Standardization is tedious, but it is the only way to scale beyond a handful of carriers. Agencies that skip this step spend hours each month hunting through individual PDFs, re-learning each carrier's quirks, and missing patterns that span multiple carriers.

3. Step 3: Match Statements Against Your Baseline

   Now you compare what you expected to receive against what carriers actually paid. In your reconciliation workbook, add a column called 'Baseline Match.' Use a lookup formula to check whether each member in your baseline appears in that month's statements. If you are comfortable with Excel, a VLOOKUP or INDEX-MATCH formula will do this automatically. The formula searches your reconciliation workbook for each member ID from your baseline. If it finds a match, it returns the commission amount from the statement. If it does not find a match, it returns an error or blank cell.

   Members who appear in your baseline but not in any statement are your primary candidates for missing commissions. However, not every missing match is an error. A member might have termed the previous month and you have not updated your baseline yet. A new enrollment might still be pending at the carrier. A group might have moved to a different carrier mid-month. This is why you need the notes column from Step 1 — it helps you distinguish between legitimate gaps and true underpayments.

   For members who do appear in statements, compare the paid amount to your expected amount. Discrepancies fall into a few categories: commission rate changes (the carrier adjusted rates and did not notify you), partial-month payments (the member enrolled mid-month or termed mid-month), chargebacks from a prior period, or calculation errors. Flag any discrepancy larger than a few dollars. Small rounding differences are common and usually not worth pursuing, but a member who should pay fifty dollars and only shows twenty is worth investigating.

   Create a separate tab in your workbook for discrepancies. List the member, the expected amount, the actual amount, the difference, and your hypothesis for why it happened. This tab becomes your recovery list. Do not try to resolve everything immediately — finish the matching process for all carriers first, so you can see the full scope of what is missing.

4. Step 4: Investigate High-Probability Underpayments

   Not all missing commissions are equally likely to be recoverable. Focus first on the patterns that represent the largest dollar amounts and the highest probability of carrier error. Members who paid last month but are absent this month — especially if they are on a product with low voluntary disenrollment rates like Medicare Advantage — are high-probability underpayments. The carrier likely still has the member enrolled and collecting premiums, but dropped the commission record due to a system glitch or data feed error.

   Another high-probability pattern is members who show a commission amount of zero or a token amount like one cent. This usually means the carrier's system flagged the record for manual review and no one completed the review. Group policies that renewed but show a lower per-member commission than the prior month are also worth investigating — the carrier may have applied the wrong rate tier or failed to load the renewal rate schedule.

   For each high-probability case, log into the carrier portal and look up the member. Verify the member is still active and that your agency is still listed as the writing agent. If the member is active and you are the agent of record, take a screenshot showing the policy details, effective date, and your agency information. This screenshot is your evidence when you contact the carrier. If the portal shows the member termed or shows a different agent, update your baseline and move on — that is not a recoverable underpayment.

   Once you have verified a handful of high-probability cases, you will start to see patterns specific to each carrier. One carrier might consistently drop commissions for members who move between counties. Another might fail to pay on the first month after a plan change. Document these patterns in your notes. They let you predict where next month's errors will occur and catch them faster.

5. Step 5: Document and Submit Recovery Requests

   Carriers will not correct underpayments unless you provide documentation they can trace back to their own records. For each confirmed underpayment, prepare a recovery request that includes: your agency name and writing number, the member's name and policy or member ID as it appears in the carrier's system, the statement month in question, the amount you expected, the amount you received (or that the member was absent from the statement), and a brief explanation of why you believe the commission is owed.

   Attach screenshots from the carrier portal showing the member is active and your agency is the writing agent. If the carrier's own statement from a prior month shows the member paying, attach that too. Carriers are more likely to process your request quickly if you do the detective work for them and present the evidence in a format their commission team can verify without having to reconstruct the history themselves.

   Most carriers have a commission dispute or inquiry process. Some accept email to a dedicated address; others require you to submit a ticket through the producer portal; a few still require faxed requests on agency letterhead. Follow the carrier's process exactly. If you submit via the wrong channel, your request may sit unprocessed for months. If the carrier does not publish a dispute process, call the producer services number and ask how to submit a commission inquiry. Document the name of the person you spoke with and the date.

   Submit requests in batches organized by carrier and by statement month. A single email with twenty missing members from the same month is easier for the carrier to process than twenty separate emails. Track each request in a new tab of your workbook: submission date, carrier, number of members, total dollar amount, carrier reference number if provided, and resolution status. Follow up every two weeks if you do not receive a response. Carriers are slow, but they are slower if you do not follow up.

6. Step 6: Track Recoveries and Update Your Baseline

   When a carrier processes your recovery request, the payment usually appears on a future statement, often labeled as an adjustment or prior-period correction. It will not necessarily appear in the month you requested it. Some carriers batch all adjustments into a quarterly true-up. Others add corrections to the next regular monthly statement. A few send a separate check or direct deposit with a cryptic reference number.

   When you receive a recovery payment, match it back to your recovery request log. Update the resolution status and note the amount received. If the amount is less than you requested, investigate why. The carrier may have agreed with part of your claim but not all of it, or they may have applied a chargeback or clawback that offset the recovery. If the discrepancy is significant, follow up with the carrier for an explanation.

   Once you confirm a recovery is complete, update your baseline to reflect the correction. If the issue was a one-time glitch, no further action is needed. If the issue was a systemic problem — for example, the carrier's system does not handle mid-month effective dates correctly — add a note to your baseline for that carrier so you can watch for the same issue in future months.

   Tracking recoveries also gives you data on which carriers are reliable and which are not. If one carrier consistently underpays and takes months to process corrections, that information is valuable when you are deciding where to place new business. Some agencies build a scorecard that tracks average recovery time and error rate by carrier. This is not about shaming carriers — it is about making informed business decisions and knowing where to focus your reconciliation effort.

7. Step 7: Automate What You Can and Refine Your Process

   After you have run this reconciliation process manually for two or three months, you will notice repetitive tasks that consume time without adding insight. These are candidates for automation. If you are comfortable with spreadsheet formulas, build templates that auto-populate your reconciliation workbook when you paste in a new carrier statement. Use conditional formatting to highlight discrepancies above a certain threshold, so you do not have to scan every row manually.

   If you manage a large book or work with many carriers, consider whether purpose-built software would save more time than it costs. Tools designed for commission reconciliation can ingest statements automatically, normalize formats without manual mapping, and flag anomalies using rules you configure once and reuse every month. The time saved often pays for the software within the first quarter, especially during high-volume periods like Annual Enrollment.

   Regardless of whether you automate, refine your process each month. If you spent an hour tracking down a discrepancy that turned out to be a known carrier quirk, document that quirk so you do not waste the hour again next month. If a particular carrier's statement format changed, update your reconciliation template. If you discovered a new type of underpayment pattern, add a check for it to your matching process.

   The goal is not to eliminate all manual work — some judgment calls will always require a human. The goal is to eliminate redundant work and to catch errors earlier in the cycle, before they compound across multiple months. Agencies that treat reconciliation as a repeatable process rather than a monthly fire drill recover more revenue and spend less time doing it.