Expert guide

Real Estate Commission Tracker in Excel

Splits, caps and fees calculated the same way on every deal

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: log each deal in an Excel Table with agent, close date, price, commission rate, side, status and any referral or team share. Keep each agent's plan (split, cap, fees, cap year start) in a plans table. Calculate gross commission, normal company dollar, the remaining cap from year-to-date company dollar on earlier closed deals, capped company dollar with MIN, and net to agent. Separate pending from closed. We build commission workbooks as custom Excel spreadsheets for brokerages and teams.

Commission disputes are almost always calculation disputes: a split applied after the cap should have kicked in, a transaction fee charged twice, a referral fee taken from the wrong side. They erode trust between agents and brokers, and they usually come from formulas typed into individual rows. A tracker with one set of rules, applied to every deal in order, removes most of them.

The Two Tables

TableColumns
tblPlansAgent, SplitToAgent (e.g. 70%), Cap (company dollar), CapYearStart (anniversary date), TransactionFee, PostCapFee
tblDealsDealID, Agent, Property, Side (Buyer/Seller/Both), Price, CommRate, SideShare, ReferralPct, TeamPct, Status (Pending/Closed/Fell through), CloseDate

The Formulas, in Order

Gross commission (agent's side):
=[@Price] * [@CommRate] * [@SideShare]

After referral fee paid out:
=[@Gross] * (1 - N([@ReferralPct]))

Agent's cap year start for this deal:
=LET(anniv, XLOOKUP([@Agent], tblPlans[Agent], tblPlans[CapYearStart]),
     y, YEAR([@CloseDate]),
     d, DATE(y, MONTH(anniv), DAY(anniv)),
     IF(d > [@CloseDate], DATE(y - 1, MONTH(anniv), DAY(anniv)), d))

Normal company dollar (before cap):
=[@[After referral]] * (1 - XLOOKUP([@Agent], tblPlans[Agent], tblPlans[SplitToAgent]))

Company dollar already paid this cap year (earlier closed deals):
=SUMIFS([Capped company dollar], [Agent], [@Agent], [Status], "Closed",
        [CloseDate], ">=" & [@[Cap year start]], [CloseDate], "<" & [@CloseDate])

Capped company dollar:
=IF([@Status] <> "Closed", [@[Normal company dollar]],
    MIN([@[Normal company dollar]],
        MAX(0, XLOOKUP([@Agent], tblPlans[Agent], tblPlans[Cap]) - [@[Company $ YTD]])))

Fee:
=IF([@[Company $ YTD]] >= XLOOKUP([@Agent], tblPlans[Agent], tblPlans[Cap]),
    XLOOKUP([@Agent], tblPlans[Agent], tblPlans[PostCapFee]),
    XLOOKUP([@Agent], tblPlans[Agent], tblPlans[TransactionFee]))

Net to agent:
=[@[After referral]] - [@[Capped company dollar]] - [@Fee]

Two details matter. The YTD sum only counts deals closed before this one, so the order of closings determines when the cap is reached. And two deals closing on the same day need a tie-breaker; add a time or sequence column and include it in the comparison if that happens in your office.

Worked Example

DealGross after referralCompany $ YTD beforeCapped company $Net to agent (before fee)
March$9,000$0$2,700$6,300
June$12,000$2,700$3,600$8,400
August$15,000$6,300$1,700$13,300
October$10,000$8,000$0$10,000

With a 70/30 split and an $8,000 cap, the August deal would normally pay $4,500 to the brokerage, but only $1,700 of cap remains. From October the agent keeps the full commission, minus the post-cap fee.

Teams and Referrals

  • Team splits usually apply after the brokerage split: team lead's share = net to agent × TeamPct.

  • Decide and document whether team members' deals count towards the lead's cap or their own.

  • Outgoing referral fees come off the top; incoming referral income may have its own split.

  • Keep each rule in the plans table, not in a deal row, so it is applied consistently.

Pipeline and Year-to-Date Views

Pending income this quarter (per agent):
=SUMIFS(tblDeals[Net to agent], tblDeals[Agent], A2, tblDeals[Status], "Pending",
        tblDeals[ExpectedClose], "<=" & EOMONTH(TODAY(), 2))

YTD net to agent:
=SUMIFS(tblDeals[Net to agent], tblDeals[Agent], A2, tblDeals[Status], "Closed",
        tblDeals[CloseDate], ">=" & DATE(YEAR(TODAY()),1,1))

Cap progress:
=SUMIFS(tblDeals[Capped company dollar], tblDeals[Agent], A2, tblDeals[Status], "Closed",
        tblDeals[CloseDate], ">=" & CurrentCapYearStart) / Cap

Controls a Broker Should Insist On

  • Lock formula columns; only deal inputs are editable.

  • Status changes to Closed only after funds are received.

  • A payout statement per deal generated from the row, not typed.

  • Monthly reconciliation of company dollar to the operating account.

  • A change log: who edited a closed deal and why.

Where AI Helps

AI can read closing statements and settlement documents to extract price, commission and referral details for checking against the log, and draft payout statements in plain language. The extraction pattern is in extracting PDF data with AI. Payouts themselves should come from the tested formulas, never from AI arithmetic.

Similar tiered and capped logic appears in sales commission plans; packaging it as a named function is shown in the Excel LAMBDA guide. For client and lead tracking alongside deals, Excel CRM Lite fits agents and small teams.

FAQ

Real estate commission tracker FAQ

Splits, caps, fees and teams

Gross commission = sale price × commission rate × the agent's side share. Agent's share = gross commission × split percentage, minus any per-transaction fees. With a cap, the split changes once the agent has paid the brokerage a set amount in the year, so the formula must check year-to-date company dollar before the deal.

A cap is the maximum company dollar an agent pays the brokerage in an anniversary or calendar year. After the agent reaches it, they typically keep 100 percent of commission minus transaction fees for the rest of that year. Caps make the split for each deal depend on earlier deals.

Calculate the company dollar the deal would normally produce, then limit it to the remaining cap: =MIN(NormalCompanyDollar, MAX(0, Cap − CompanyDollarYTD)). The agent receives the gross minus that capped company dollar and any fees.

Yes, as a separate status. Pending deals give agents and brokers a projected income view, but only closed and funded deals should count towards caps, payouts and year-to-date totals.

When there are many agents with different plans, teams with internal splits, referral fees, and payouts that must reconcile to the trust or operating account. A database or brokerage back-office system reduces errors at that scale; Excel works well for an individual agent or small team.