AFG trail report — what's inside and how to use it

Where to find it in AFG

The trail report lives inside AFG Connect under Reports → Commissions. Most brokers download a CSV or XLSX, run it for the most recent calendar month, and review monthly. AFG also publishes a YTD report — useful for tax preparation but unwieldy for active book management.

Permissions matter: junior writers usually see only their own loans, while a principal sees the brokerage-wide view. If your numbers don't match what your aggregator BDM is quoting, check the permission scope first.

What's in the file

An AFG trail export has one row per commission event. Important columns:

• LOAN_ID / TRANSACTION_ID — the loan identifier and the specific commission event (one loan generates many transactions over time).
• CLIENT_NAME, CONTACT — the borrowing client. For trail tracking these mostly serve as readable labels; the LOAN_ID is the actual key.
• LENDER, PRODUCT — which bank / non-bank holds the loan, and the specific product code. Critical for lender-concentration analysis.
• COMMISSION_TYPE — Upfront, Trail, Adjustment, Reversal or Clawback. This is the discriminator that says what kind of event the row represents.
• COMMISSION — the dollar value of the event. Negative on adjustments / reversals / clawbacks.
• LOAN_AMOUNT — the loan balance the commission was calculated against. AFG fills this with principal at settlement on upfront rows and period-end outstanding balance on trail rows. (See the gotcha section below.)
• SETTLEMENT_DATE, TRANSACTION_DATE — when the loan settled and when this specific commission event was paid.
• LOAN_WRITER, RATE — the writing broker and the broker's commission split (not the interest rate on the loan — easy to misread).

The row-typed quirk

Most aggregator exports are column-typed — separate columns for upfront, trail, clawback. AFG is row-typed: every commission event sits in the same COMMISSION column, discriminated by COMMISSION_TYPE. This is more flexible but it has a sharp edge: the LOAN_AMOUNT column means different things on different rows. On Upfront rows, it's the principal at settlement (a fixed historical number). On Trail rows, it's the period-end balance (a dropping number as the loan amortises).

If you import naively, the same loan looks like it changes settlement balance with every monthly trail payment. The fix is to treat the upfront row as the source of truth for principal at settlement — TrailScope does this automatically, but a hand-built spreadsheet usually doesn't.

Reading it for portfolio decisions

Three views the AFG report uniquely supports:

1. Lender concentration — group by LENDER, sum trail-type rows over the trailing 12 months. Anything above 30–40% with one lender is a concentration flag.
2. Loan-writer performance — group by LOAN_WRITER, separate upfront from trail to see writing strength versus retained book. Multi-writer brokerages live or die on this view.
3. Clawback rate — count Adjustment / Reversal / Clawback rows over a rolling 12 months. Anything above 3–5% of upfronts is unusually high and worth investigating.

Common gotchas

• NULL principal on trail-only loans — if you joined AFG via an existing book transfer, some loans may never have an upfront row in your export. Trail-only loans will show null principal at settlement until you obtain the original upfront figure.
• DST date drift — many spreadsheet tools parse AFG dates assuming UTC, which can shift a date by one day east of Australia. Always parse with named-month or serial-number forms, not naive ISO strings.
• Rate vs interest rate — the RATE column is the broker's commission split (e.g. 0.55%), not the loan's interest rate. Don't accidentally write it back into your CRM as the loan rate.

Working with this in TrailScope

TrailScope ingests AFG exports directly: drop the CSV or XLSX into the Upload page and the importer detects the row-typed shape automatically. The clawback schedule (56 lenders × 8 three-month bands) is built in, so you can see exactly which loans are inside which clawback window. See all features →