Commission Tracking: Spreadsheets vs Software for Mortgage Brokers

A spreadsheet can record what you were paid; it cannot easily tell you what you were owed. That single gap is the difference between the two approaches. For a small, new loan book a well-built spreadsheet is genuinely enough — but as the book grows, the manual work of reconciling every upfront and trail payment against expectation, catching payments that simply stop, and provisioning for clawback becomes the thing that quietly costs brokers money. Commission tracking software exists to automate that reconciliation. The honest answer to “spreadsheet or software?” is: it depends on the size of your book and how much of your income you’re willing to leave unchecked.

This guide compares the two fairly — including when a spreadsheet is the right call — so you can decide where your book actually sits.

Can you track mortgage commission in a spreadsheet?

Yes — and plenty of brokers do, successfully, especially early on. A spreadsheet is flexible, free, familiar, and completely under your control. If you’re writing a modest number of loans a year and you personally see every commission line as it lands, a well-structured spreadsheet can do the job: a row per loan, columns for upfront and trail, a running total, and a tab for expected versus received.

The point of this guide is not that spreadsheets are bad. It’s that a spreadsheet does one thing well — recording — and the harder, money-saving job is reconciling, which a spreadsheet only does as well as you manually maintain it. As the book grows, that maintenance is where things quietly break.

Where does a spreadsheet start to break down?

Not through one dramatic failure — through the accumulation of small, unnoticed ones. The specific pressure points for broker commission are:

1. Reconciling paid against owed. Recording that a lender paid you $4,200 is easy. Knowing whether $4,200 was correct means modelling the drawn amount net of offset, the aggregator split, and GST treatment for that specific loan — and doing it for every loan, every month. A spreadsheet can hold that logic, but you have to build it, maintain it, and trust that no formula silently broke.

2. Trail that simply stops. A wrong payment is visible — a number looks off. A payment that stops is invisible: there’s just no row this month. When a loan discharges or is refinanced and its trail ceases, nothing in a spreadsheet raises its hand. Across a large book, a handful of silently-stopped trails a year is a real leak that never appears as a single alarming figure — see the mechanics in Trail Commission Explained.

3. Net-of-offset modelling. Upfront is generally calculated on the drawn balance less offset funds, not the facility limit, and the measurement window differs by lender. Reproducing that per-loan in a spreadsheet is possible but fiddly, and it’s exactly the kind of nuance that gets approximated away. If you don’t model it, you can’t tell an underpayment from a correct payment. The full explanation is in How Mortgage Broker Commissions Work.

4. Stepped-trail anniversaries. Some lenders increase trail the longer a loan is held, changing your entitlement on an anniversary date. That’s a scheduled change nobody diaries — and a spreadsheet won’t remind you.

5. Clawback exposure. Every settled loan carries a contingent liability for roughly two years. Treating upfront as clean profit without provisioning for clawback overstates your income in every growth year. A spreadsheet can hold a provisioning column, but it won’t track the exposure across the book or age it for you. Our companion guide covers this: Mortgage Commission Clawbacks in Australia.

6. Volume and fragility. A broker with a few hundred active loans receives thousands of individual commission line items a year. Checking them by hand isn’t realistic, so most go unchecked. And spreadsheets themselves are error-prone at scale: decades of academic audits of real operational spreadsheets — most associated with Ray Panko of the University of Hawaii and catalogued by the European Spreadsheet Risks Interest Group — have consistently found errors in the large majority of the spreadsheets examined. That research is general, not commission-specific, but the lesson transfers: a single broken formula can propagate silently through a whole book of numbers.

7. No audit trail. A spreadsheet doesn’t record who changed what, when. For a regulated business that can matter — and it certainly matters when you’re trying to work out why last quarter’s totals don’t reconcile.

What does commission tracking software actually do?

The category earns its place by automating the reconciliation a spreadsheet makes you do by hand. Concretely, dedicated commission tracking software generally:

• Reconciles what you were paid against what you were owed, loan by loan, rather than just recording receipts.
• Flags variances and missing payments automatically — including the invisible case of a trail that stopped — so you’re told, not left to notice.
• Tracks clawback exposure as loans age through the clawback window, so upfront isn’t mistaken for settled profit.
• Projects trail over the life of the book, turning “what’s this worth?” into a number.
• Keeps an audit trail and lets you export your data.

That’s the category. CommissionsIQ does exactly this: you import your commission statements, it reconciles received against expected, and it flags the variances, underpayments and missing payments for you to investigate — with clawback risk monitoring across the book and AI-generated trail projections. One honest caveat worth stating plainly, because not every product is the same: CommissionsIQ works from the commission statements you import (structured file upload, with lender/aggregator formats mapped), and is built to connect via API where a broker, aggregator or provider supplies access — it does not claim live, out-of-the-box feeds from every aggregator. When you’re comparing tools, check which model each one actually uses.

Spreadsheets vs software: side by side

When is a spreadsheet still the right tool?

Genuinely, in a few situations:

• You’re new or low-volume. A handful of settlements a month you can see individually don’t need software. Start with a spreadsheet; move when the manual check stops being realistic.
• You want to learn the mechanics first. Building your own reconciliation once is a good way to understand net-of-offset, splits and GST before you let a tool do it. This guide’s companions are written to help with exactly that.
• Budget is genuinely tight and the book is small. If the leak a tool would catch is smaller than its cost, the tool isn’t justified yet. Be honest in both directions — that calculation flips as the book grows.

The tipping point isn’t a magic loan count; it’s the moment you can no longer confidently say you’ve checked every line. When “I think it’s about right” replaces “I’ve reconciled it,” you’ve outgrown the spreadsheet.

What should you look for in commission tracking software?

If you decide you’re past the spreadsheet, judge tools on substance rather than screenshots. The questions that matter for an Australian broker:

1. Does it reconcile, or just record? Plenty of tools are prettier spreadsheets — they store your payments but never tell you if one was wrong. Reconciliation against expected commission is the whole point.
2. Does it catch missing trail, not just wrong amounts? The stopped payment is the hardest leak to spot manually and the most valuable thing to automate.
3. Does it model net-of-offset, splits and GST? These are what make AU broker commission genuinely hard. A tool that ignores them will “reconcile” to the wrong number.
4. Does it track clawback exposure across the roughly two-year window, so you can provision properly?
5. Where is your data hosted, and can you export it? For a regulated business, Australian hosting and clean data portability matter. Owning your data — being able to export it — is non-negotiable.
6. Does the pricing fit your book? Tiered plans should map to your loan volume, not force you into an enterprise plan for a mid-size book.
7. Are its integration claims honest? “API-ready” and “live integration with X” are very different. Ask which aggregators and CRMs it genuinely connects to today versus what requires you to supply access or upload files.

That last point is worth dwelling on: in a regulated niche, a vendor’s honesty about its own limits is a reasonable proxy for how much to trust the rest of its claims.

What does it cost — software, or the leak it prevents?

The instinct is to compare a subscription against zero, because the spreadsheet feels free. It isn’t: it costs your time to maintain and carries the error risk above. The fairer comparison is the subscription against the money a tool recovers or protects — underpayments surfaced, trail that would otherwise have stopped unnoticed, clawback provisioned instead of clawed back from a month that didn’t earn it.

You don’t have to guess at the scale. The commission calculator lets you model upfront, trail over a loan’s life, and what a clawback would cost you on a given deal — which is a concrete way to see whether a few per cent of unchecked variance across your book is larger than a subscription. For most growing books, it is.

Frequently asked questions

The bottom line

Spreadsheets and commission tracking software aren’t really competing to do the same job well. A spreadsheet is an excellent recorder and a poor reconciler; software exists to reconcile. For a small book where you see every line, recording may be all you need. For a growing book where thousands of line items a year go unchecked, the manual reconciliation a spreadsheet demands is precisely the work that doesn’t get done — and unchecked commission is where brokers quietly lose money.

Three things to take away regardless of which you choose:

1. Reconcile, don’t just record — the money is in comparing paid against owed, not in a tidy list of receipts.
2. Watch for the payment that stops, not only the one that’s wrong — it’s the harder leak to see and the easier one to automate.
3. Compare cost honestly — a subscription against the leak it prevents, not against an imaginary free spreadsheet.

Sources and further reading

• MFAA — industry guidance on broker remuneration and commission structures
• ASIC — Report 516: Review of mortgage broker remuneration (net-of-offset background)
• Combined Industry Forum — reform package response to ASIC Report 516
• FBAA / CoreData — broker clawback survey (impact and prevalence)
• R. Panko, Spreadsheet Errors: What We Know and related field-audit research, University of Hawaii
• European Spreadsheet Risks Interest Group (EuSpRIG) — spreadsheet error research and case catalogue
• Companion guides: How Mortgage Broker Commissions Work · Mortgage Commission Clawbacks · Trail Commission Explained

Always verify against your own aggregator’s current commission schedules. Rates, calculation methods and clawback terms vary by lender, product and settlement date. This guide compares approaches to tracking commission; it is general information, not financial, tax or legal advice.

This is general information, not financial, tax or legal advice. Always verify commission entitlements against your aggregator's current schedules.