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

Build a commission tracker that handles splits, caps, and team overrides automatically in Google Sheets. Stop using "napkin math" for your paycheck.

You just closed a $500k deal. 3% commission. Great. But wait-split with the broker is 70/30. There's a 6% franchise fee off the top. You hit your cap last month, so maybe it's 100%? And you owe your Transaction Coordinator $350.

Trying to calculate your "Net Agent Income" manually on every deal leads to errors. And errors mean you might be overpaying (or underpaid).

The Calculator

Build a spreadsheet that takes your Gross Commission Income and runs it through the logic: check your cap status, deduct franchise fees, apply your split, subtract TC fees. Out comes your actual net check.

The Code: Split Calculator

1. Setup

Sheet "Deals": Col A: Agent, B: GCI, C: Net Payout.
Sheet "Agents": Col A: Name, B: Split (e.g., 0.70), C: Cap (e.g., 15000), D: Paid YTD.

2. The Script

This script runs when you click a button "Calculate Commission".

function calculateCommission() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dealSheet = ss.getSheetByName('Deals');
  const agentSheet = ss.getSheetByName('Agents');
  
  const dealRow = dealSheet.getActiveCell().getRow();
  const agentName = dealSheet.getRange(dealRow, 1).getValue(); // Col A
  const gci = dealSheet.getRange(dealRow, 2).getValue();         // Col B
  
  if (!gci || !agentName) return;
 
  // 1. Fetch Agent Stats
  const agentData = getAgentData(agentSheet, agentName);
  
  if (!agentData) {
     SpreadsheetApp.getUi().alert("Agent not found!");
     return;
  }
  
  let split = agentData.split;
  let capRemaining = agentData.cap - agentData.paidYTD;
  
  // 2. Logic: Cap Enforcement
  // If they have met their cap, split is 100% (1.0)
  if (capRemaining <= 0) {
    split = 1.0;
  }
  
  // 3. Calculate Company Share
  let companyShare = gci * (1 - split);
  
  // Handle partial cap (if this deal pushes them over)
  if (capRemaining > 0 && companyShare > capRemaining) {
    companyShare = capRemaining;
  }
  
  // 4. Net Payout
  const netAgent = gci - companyShare;
  
  // 5. Write Result
  dealSheet.getRange(dealRow, 3).setValue(netAgent);
  dealSheet.getRange(dealRow, 4).setValue(companyShare); // Log company dollar
  
  // Optional: Update Agent's Paid YTD automatically here
}
 
function getAgentData(sheet, name) {
  const data = sheet.getDataRange().getValues();
  for (let i = 1; i < data.length; i++) {
    if (data[i][0] === name) {
      return {
        row: i + 1,
        split: data[i][1],
        cap: data[i][2],
        paidYTD: data[i][3]
      };
    }
  }
  return null;
}

Advanced: Team Overrides

Managing a team? Add a "Rainmaker" percent. Just deduct another line item: const teamLeadCut = gci * 0.10; before calculating the agent split.

Build vs. Buy

Brokerage software (DotLoop, SkySlope) handles compliance, but their commission modules are often $100/agent/month add-ons. Spreadsheets are free, transparent, and you can audit every penny.