What the file is
Every commission run, Loan Market Group (LMG) issues brokers under its brands, including Loan Market, Nectar Mortgages, PLAN, FAST and Choice, a recipient created tax invoice. It is the aggregator's statement of every commission line paid to you for the period, and because LMG creates it on your behalf it is also your tax invoice for that income. The portal exports it as a spreadsheet, and that .xlsx export is the file MyLoanBook reads.
The file has one row per commission line. A single client can produce several rows in a month: a trail line for each loan, an upfront line for a new settlement, a negative line for a clawback, a fee line. There are around two dozen columns. Most brokers only ever look at one or two of them, which is how trail leaks go unnoticed for years.
The columns that matter
Client and loan identifiers. The client name, loan account and lender columns are the keys. Match on the loan account rather than the client name; names change, accounts do not, and one client can hold several loans across lenders.
Lender Payment. What the lender paid LMG for this line, before the aggregator split. It is the gross figure and it is not your income. Reporting from it overstates your revenue by the size of your split.
Commission GST Inclusive. What you receive for the line, after the split, including GST. This is the column to use for income, for reconciliation and for valuation. The GST is collected on the tax office's behalf; the ex-GST figure is the business income.
Payment type. Tells you what the line is: TRAIL, UPFRONT, DISCHARGED, ARREARS or a fee. Filtering on this column is the fastest way to see what changed this month.
Settlement date. The date the loan settled with the lender and the date the clawback clock started. Every loan with a settlement date inside the last 24 months is still exposed.
Loan balance. The outstanding balance the lender used to calculate trail. Divide the trail line by the balance and multiply by twelve to recover the annual trail rate the lender is paying on that loan.
Payment types explained
TRAIL is the ongoing monthly commission on a loan that is current. UPFRONT is the one-off commission on a new settlement, usually appearing two to six weeks after the settlement date. DISCHARGED marks a loan that has closed this period; the trail line is often a partial month, and it will not appear next month. ARREARS marks a loan the lender has suspended trail on because the client has missed repayments; the amount is typically zero. Fee lines are deductions: software, aggregation fees, and clawbacks, which appear as negative UPFRONT lines a month or two after the discharge that caused them.
The pattern to watch is a DISCHARGED line this month followed by a negative UPFRONT line next month on the same account. That is a clawback, and if you did not see it coming the settlement date will tell you how much it cost.
Try it: Clawback calculator
Pick a clawback schedule, enter the loan and the months since settlement, and see what you would repay if the client left today.
A monthly reconciliation in four steps
Step one: totals. Sum Commission GST Inclusive for TRAIL lines this month and compare to last month. A fall of more than 2% to 3% that is not explained by a known discharge needs a look.
Step two: missing accounts. List every loan account that had a TRAIL line last month and none this month. Each one is either discharged, in arrears, or dropped by the lender in error. The first two will have their own payment-type lines; the third will not, and it is the one worth a call to LMG.
Step three: new accounts and upfronts. Every settlement you know of should produce an UPFRONT line within six weeks and a TRAIL line the month after. Missing upfronts are the most common thing brokers recover when they reconcile for the first time.
Step four: rates. For a sample of loans, trail divided by balance times twelve should match the lender's rate for that product. Rate changes, step-downs after a set number of years, and lender system migrations show up here first.
What a leak looks like
A leak is trail that has stopped without a discharge you knew about. On the file it appears as a loan account that was earning trail and now is not, with no DISCHARGED line, or as a loan whose balance suddenly dropped to a fraction of what it was, or as a lender whose trail rate on every loan changed in the same month.
Individually each one is small: $60 here, $90 there. Across a $48m book, LMG brokers who reconcile for the first time routinely find several hundred dollars a month of trail that had gone missing over a couple of years, and a handful of clients who left without anyone noticing. Reading the file once a month, with the same four steps, is the whole method. MyLoanBook automates the comparison, but the columns and the logic are exactly these.