Find missing trail commission — how to detect silently dropped trail payments

Trail commission rarely disappears with a bang. A client refinances away, a fixed-rate honeymoon kicks in, a lender's pay run breaks, or a loan gets misallocated to another broker — and the cash flow drops by a few hundred dollars a month until you notice. Most brokers don't notice. The cost compounds.

This piece is the reconciliation method I'd give a junior broker who has just been handed a 200-loan book. It works whether you're on AFG, LMG, Connective, or Finsure, because the logic is aggregator-agnostic.

What "missing trail" actually means

There are three things that look the same on a bank statement but mean very different things:

• Lost client — the loan was discharged or refinanced away. The loan_id was on last month's statement and is gone this month. This is a real, expected event.
• Nilled trail — the loan is still on your book, but the lender paid zero this month for a stated reason: fixed-rate period, arrears, or post-settlement honeymoon. The aggregator typically populates a nil_trail_reason.
• Missing trail — the loan paid trail last month, and this month it's either absent from the file or sitting at zero with no reason given. This is the category worth chasing. It's where money goes.

The first two are accounting; the third is broken pipes.

The reconciliation step most brokers skip

Open this month's aggregator export. Open last month's. Now ask: for every loan that paid trail last month, did it pay trail this month?

In practice that's:

1. Pull the loan_id (or account number — whatever your aggregator uses as a stable key) and the trail amount from last month's file.
2. Pull the same columns from this month's file.
3. Left-join last month to this month on loan_id.
4. Flag any row where the previous month's trail_commission was greater than zero and the current month's row is either missing entirely or has trail_commission = 0 with no reason set.

That's it. The hard part isn't the join — it's doing it every single month, on time, on the right columns, without losing the comparison when a lender changes their account-number format mid-cycle.

Why the spreadsheet approach breaks

Brokers who do this in Excel typically hit one of three failure modes:

• They keep only the latest file and lose the prior month's snapshot. No previous-month state, no comparison.
• They compare totals instead of loans. A $400 drop in trail income could be one large loan or twelve small ones — the totals don't tell you which client to call.
• They include loans the aggregator legitimately nilled — false positives every month — and stop trusting the report.

The trick is to diff at the loan level, snapshot every month, and exclude rows where the aggregator gave a nil reason. Anything else is noise.

Common causes of a real miss

When you find a missing-trail loan, the cause is almost always one of these:

• The loan changed account numbers. A lender restructure or a product switch issues a new account number; the aggregator can't reconcile it to your existing record and stops paying. Fixable with one phone call.
• The loan got reassigned. Another broker in the same brokerage gets credited, or the lender flags it as house-direct. Common around staff changes.
• A pay-run break at the lender. Rare but real. CBA and Westpac both have form here — usually corrected the following month with a back-pay.
• The aggregator misclassified the loan. Especially common with Connective's multi-sheet exports, where a loan slips into the wrong tab and silently stops contributing.
• A genuine discharge that the aggregator didn't mark. The status field still says active, but the loan settled out weeks ago.

For each of these, the action is different: ring the lender, ring the aggregator, ring the client, or update the record. But all of them start the same way — you have to know the loan dropped.

How much money is at stake

On a typical 200-loan AU residential book, you can reasonably expect 1–3 silently-dropped loans per month. At an average trail rate of ~15bp/year on a $500k balance, each missed loan is roughly $62/mo, or ~$750/year per loan. Catch two of them and that's $1,500 a year you'd otherwise have written off as run-off.

Over a five-year hold, missed trail compounds into real money — and because brokers tend to assume "the lender paid what they paid", it's almost never recovered retrospectively.

What good detection looks like

Whether you build the diff yourself or use a platform that does it for you, the bar is:

• Loan-level comparison, not totals
• Excludes legitimately nilled trail (don't chase what isn't broken)
• Surfaces the named client, not a row count
• Runs the day the new file lands, not at quarter-end
• Keeps a history of every missed-and-resolved loan, so you can spot lenders with chronic pay-run issues

Trail AI's missing-trail signal is built exactly this way — see the comparison with TrackMyTrail for how the named-client breakdown differs from a generic dashboard counter. The mechanics aren't proprietary; what matters is that you do it every month.

Key takeaways

• Missing trail is loan-level, not total-level. Compare the prior month's paying loans to this month's, named client by named client.
• Exclude legitimately nilled trail — fixed-rate honeymoons and arrears flags aren't broken pipes.
• Causes are usually mundane: account-number changes, reassignments, aggregator misclassification. All fixable.
• On a 200-loan book, you should expect 1–3 silent drops per month. Catching them is worth four figures a year, easily.
• Run the reconciliation the day your file lands. Quarter-end is too late to recover what's stale.