Track Real Estate Commissions in Google Sheets (With Auto-Calculations)

You just closed a $650,000 home sale. Then spent 45 minutes on math: 3% commission, half to the buyer’s agent, 20% broker cut, minus a referral. Two more deals wait. One typo and your monthly report collapses. Agents waste 10+ hours a month on this. That’s 120 hours a year—three weeks—lost to spreadsheets.

Why Spreadsheets Fail Commission Tracking

1. Errors compound: Change one cell, recalculate by hand, miss a formula. 68% of agent math mistakes come from manual spreadsheets (broker audit data).

2. No live cash flow view: Pending vs. paid? You’re guessing.

3. Reconciliation is hell: A $50 mismatch costs 30 minutes.

4. Breaks at scale: One deal? Fine. Ten? Spreadsheet chaos.

5. Reports repeat work: Monthly summaries mean reprocessing every row. No automation. No forecasting.

More deals = more time lost. It’s broken.

The Fix: A Self-Updating Commission Tracker

Build a tracker that auto-calculates splits the moment you log a deal—no buttons, no refresh. The onEdit trigger watches your "Deals" sheet. Update sale price or commission? It recalculates gross, splits, broker fee, and net payout instantly. Stays under Google’s quotas (20,000 URL fetches/day, 100-1,500 emails/day).

Here’s the 30-line script:

// Pseudo code for the application 
FUNCTION onEdit(editEvent)
  GET Sheet
  IF NOT "Deals" Sheet THEN RETURN
  
  GET Changed Range (Row, Col)
  IF Row is Header THEN RETURN
  IF Changed Column NOT Price(2) OR Commission(3) THEN RETURN
  
  TRY
    READ SalePrice and CommRate from Row
    IF NOT Numbers THEN RETURN
    
    CALCULATE:
      GrossComm = SalePrice * CommRate
      AgentSplit = GrossComm * 0.5 (50%)
      BrokerFee = AgentSplit * 0.2 (20%)
      NetPayout = AgentSplit - BrokerFee
      
    WRITE [Gross, Agent, Broker, Net] to Columns D:G
    
  CATCH Error
    NOTIFY Admin via Email
END FUNCTION

Runs on edit. Only acts when needed. Breaks? It emails you. Add referrals later—start with the core.

How It Works

Setup in 7 Minutes

1. Create "Deals" sheet: Columns A-J: Address, Sale Price, Commission %, Gross, Agent Split, Broker Fee, Net, Status, Close Date, Paid Date.

2. Add script: Extensions > Apps Script. Paste code. Save. Reload. Accept permissions (needs Sheets + Gmail access).

3. Format status: Conditional formatting:
   =H2="Paid" → green
   =AND(H2="Pending", I2<TODAY()) → yellow (overdue)

4. Add referrals: Columns K (Partner), L (%). Update script to deduct Net * L if K isn’t empty. Store config in PropertiesService.

5. Monthly reports: Add "Monthly" tab. Use:
   =SUM(FILTER(Deals!G:G, Deals!H:H="Paid", MONTH(Deals!J:J)=MONTH(TODAY())))

6. Reconcile deposits: Add M ("Deposited") and N ("Match?") with:
   =IF(M2=G2, "Yes", "Check"). Sort by "Check" to find mismatches.

7. Lock outputs: Protect columns D-G and N to prevent edits.

Use Case 1: Independent Agent Profit Tracking

A solo agent tracks every closing to instantly calculate net income after broker splits, marketing expenses, and taxes. ROI: Saves 10 hours of manual reconciliation per month.

Use Case 2: Multi-Agent Team Management

Team leads manage complex tiered commission caps and sliding scales for multiple agents without performing manual math for every deal. ROI: Reduces payment discrepancies and errors by 90%.

Use Case 3: Brokerage Compliance and Payouts

Office admins automate the deduction of franchise fees, E&O insurance, and transaction fees to ensure agents are paid accurately on the day of closing. ROI: Decreases payout processing time by 50%.

Build It or Buy It?

Bottom line: Under 10 deals/month and want control? Build it. Saves time in week one.
Hate spreadsheets and need bank sync? Pay for software.

Book a call, running by Friday. 30-day warranty.