Broker Commission Reconciliation Worksheet
Reconcile broker commissions against lender statements, aggregator policy, and payment batches in one worksheet. Use it to spot upfront and trail variances, document exceptions, and keep an audit trail.
Trusted by frontline teams 15 years of frontline software AI customization in seconds
Built for: Mortgage Broking · Financial Services · Broker Aggregators · Lending Operations
Overview
This Broker Commission Reconciliation Worksheet is built to compare what a broker should have been paid with what was actually paid for a defined reconciliation period. It captures the lender name, aggregator name, policy reference, broker identifiers, payment batch reference, expected and paid upfront and trail commission amounts, and the reviewer’s conclusion. The form also includes variance fields, a reason code, an explanation field, and corrective action tracking so exceptions do not get lost in email.
Use this template when you need a repeatable record for monthly or ad hoc commission checks, payment disputes, policy audits, or lender statement reviews. It is especially useful when commissions are paid across multiple sources and someone needs to confirm the numbers before approval. The supporting documents and attestation sections help create an audit trail that shows what was reviewed, when it was reviewed, and who signed off.
Do not use this worksheet as a substitute for the underlying commission policy or lender agreement. If the calculation rules are still changing, first finalize the source policy and then reconcile against it. It is also not the right form for collecting broad broker profile data or unrelated performance metrics. Keep the scope narrow: one broker, one payment batch or period, one reconciliation outcome, and only the documents needed to verify the result.
Standards & compliance context
- This worksheet supports audit trail expectations by recording the reviewer, source documents, reconciliation outcome, and approval status in one place.
- The form should collect only the broker and payment details needed to verify commission accuracy, following data minimization principles.
- If broker identifiers or other PII are included, the form should disclose why the data is collected and limit access to authorized reviewers.
- Keep the policy reference and lender agreement evidence attached so the reconciliation can be traced back to the governing rule set.
General regulatory context for orientation only — verify current requirements with counsel or the relevant agency before relying on this template for compliance.
What's inside this template
Submission Notice and Reconciliation Scope
This section defines the exact period, lender, aggregator, and policy being checked so the reconciliation does not drift beyond its source records.
-
Reconciliation Period Start
Start date for the commission period being reconciled.
-
Reconciliation Period End
End date for the commission period being reconciled.
-
Reconciliation Type
Select the reconciliation workflow that applies.
-
Lender Name
Name of the lender included in this reconciliation.
-
Aggregator Name
Aggregator or licensee whose commission policy applies.
-
Aggregator Commission Policy / Agreement Reference
Policy, agreement, or document reference used for the reconciliation.
Reviewer and Broker Payment Details
This section ties the review to a specific broker, payment currency, and batch reference so the result can be traced back to the payment record.
-
Reviewer Name
Name of the person completing the reconciliation.
- Reviewer Role
-
Broker Identifier
Internal broker code, employee ID, or approved business identifier.
-
Broker Name
Optional broker name if needed for internal matching.
- Payment Currency
-
Payment Batch Reference
Optional payment run or batch reference for traceability.
Commission Reconciliation Summary
This section captures the expected versus paid amounts and the overall status so the reviewer can see the result at a glance.
-
Expected Upfront Commission
Expected upfront commission amount under the lender agreement and policy.
-
Paid Upfront Commission
Actual upfront commission amount paid to the broker.
-
Expected Trail Commission
Expected trail commission amount under the lender agreement and policy.
-
Paid Trail Commission
Actual trail commission amount paid to the broker.
-
Number of Loans Reviewed
Count of loans included in this reconciliation.
- Reconciliation Status
Variance Review and Exception Handling
This section explains any mismatch, records the reason, and shows whether corrective action is required before the file can be closed.
- Is there any variance?
-
Upfront Commission Variance
Difference between expected and paid upfront commission. Enter a positive or negative amount if applicable.
-
Trail Commission Variance
Difference between expected and paid trail commission. Enter a positive or negative amount if applicable.
- Variance Reason
-
Variance Explanation
Explain the cause of the variance and how it was validated.
- Corrective Action Required?
Supporting Documents and Audit Trail
This section preserves the evidence used in the review so the reconciliation can be verified later without searching across systems.
-
Lender Statement or Commission Report
Upload the lender statement, commission report, or equivalent supporting file.
-
Policy or Agreement Document
Upload the relevant aggregator policy or lender agreement excerpt.
-
Supporting Notes
Add any additional context needed for the audit trail.
-
Review Completed Date
Date the reconciliation review was completed.
Attestation and Approval
This section records the reviewer’s sign-off and the approver’s decision so the worksheet functions as a controlled record, not just a calculation sheet.
- Attestation
- Approval Status
-
Approver Comments
Add approval notes, correction requests, or escalation details.
How to use this template
- Enter the reconciliation period, reconciliation type, lender name, aggregator name, and policy reference so the review is tied to one defined commission rule set.
- Identify the reviewer, broker, payment currency, broker identifier, and payment batch reference so the worksheet can be matched to the correct payment record.
- Record the expected and paid upfront and trail commission amounts, then compare them against the lender statement and payment batch totals.
- Mark whether a variance is present, document the variance amount and reason, and explain any manual adjustment or exception in the supporting notes.
- Attach the lender statement and policy document, complete the review date, and submit the attestation for approval or follow-up action.
Best practices
- Use the same source date for the lender statement, policy reference, and payment batch so the reconciliation compares like with like.
- Mark required fields clearly and keep optional notes separate so the reviewer can complete the form without unnecessary data entry.
- Use numeric fields for commission amounts and counts, and avoid free-text entry for values that need to be totaled or audited.
- Document the variance reason with a specific policy or statement reference instead of a generic note like 'difference found'.
- Keep the reconciliation scope narrow to one broker and one payment batch whenever possible so exceptions are easier to trace.
- Attach the lender statement at the time of review, not after approval, so the audit trail reflects the actual evidence used.
- Route any unresolved variance to corrective action before closing the worksheet so the exception does not disappear in a later cycle.
What this template typically catches
Issues teams running this template most often surface in practice:
Common use cases
Frequently asked questions
What does this worksheet reconcile?
This worksheet compares expected and paid upfront and trail commissions for a broker against the lender statement, aggregator commission policy, and the payment batch reference. It is designed to confirm whether the broker was paid correctly for the reconciliation period. It also captures variance reasons and corrective actions when amounts do not match.
When should I use a broker commission reconciliation worksheet?
Use it at the end of a commission cycle, after lender statements are available and before broker payments are finalized or closed out. It is also useful when investigating a disputed payment, a policy change, or a lender agreement update. If you reconcile monthly, the worksheet can become the standard review record for each period.
Who should complete and approve this form?
A commission analyst, broker operations reviewer, or finance team member usually completes the reconciliation. A supervisor, finance manager, or compliance approver can then review the attestation and approval status. The reviewer should be the person who can compare source documents and explain any variance.
What documents should be attached?
Attach the lender statement and the policy document that governs the commission calculation for the period. Supporting notes should explain any adjustments, exclusions, or manual corrections. Keeping these items together creates a clear audit trail if the payment is questioned later.
How does this help with compliance and audit readiness?
The worksheet records the reconciliation period, policy reference, reviewer details, variance explanation, and approval outcome in one place. That structure supports audit trail expectations and makes it easier to show how a payment decision was reached. It also reduces the risk of undocumented manual overrides.
What are the most common mistakes when using this template?
Common issues include using the wrong reconciliation period, entering expected and paid amounts from different source dates, and leaving the variance reason too vague. Another frequent mistake is skipping the policy reference or failing to attach the lender statement. Those gaps make it hard to verify the result later.
Can this worksheet be customized for different lenders or aggregator policies?
Yes. You can add lender-specific fields, commission tiers, clawback notes, or approval routing if your process requires them. Keep the core fields intact so every reconciliation still captures the same minimum data set for comparison and review.
How should this connect to other systems?
It can be linked to commission payment records, document storage for lender statements, and approval workflows for finance or compliance review. If your stack supports integrations, use the payment batch reference and broker identifier as matching keys. That makes it easier to trace a reconciliation back to the source transaction.
Related templates
Go deeper on the topic
-
Lockout/tagout (LOTO) is the procedure for controlling hazardous energy — electrical, hydraulic, pneumatic, mechanical, thermal, chemical — before...
-
Job hazard analysis (JHA) — also called job safety analysis (JSA) — is the structured exercise of breaking a work task into sequential steps, identifying the...
-
A near-miss is an event that could have caused injury or damage but didn't — a slip that didn't fall, a load that shifted but didn't drop, a machine that...
-
AI governance is the framework a company uses to decide what AI tools are allowed to do, who's accountable for their outputs, what data they're allowed to...
-
When scheduling tools lack leave and budget data, costly errors follow. See how integrated workforce management closes the context gap.
-
Integrated digital workplace task management tips to keep work moving, reduce stalls, and turn conversations into accountable action.
-
MangoApps is named a Leader in the IDC MarketScape for Content Management in Authenticated Digital Workspaces. See what sets our intranet platform apart.
-
Compare the top internal communications platforms of 2026—MangoApps, Staffbase, Simpplr, and more—by workforce fit, features, and frontline reach.
Ready to use this template?
Get started with MangoApps and use Broker Commission Reconciliation Worksheet with your team — pricing built for small business.
