duteberta
Board Regular
- Joined
- Jun 14, 2009
- Messages
- 92
- Office Version
- 365
- Platform
- MacOS
I am struggling to make an accurate calculator for real estate commissions. I wish to see a RUNNING TOTAL of YTD commissions as they occur. This would also serve to let me view sales activity.
Here is the commission policy:
In addition here are two caveats:
My struggle is where a sale or commission crosses the "criteria line" and part of the value should adhere to one criteria and the other part of the value should adhere to another criteria.
Here is a Dropbox link to my spreadsheet. I highlighted in RED where I'm struggling, because my formulas are not sufficient.
Here is the commission policy:
Annual Gross Commissionable Sales (GCS) $0 - $1,999,999
the agent/broker split is 60/40 (60% to agent and 40% to broker)
Annual Gross Commissionable Sales (GCS) $2,000,000 - $4,999,999
the agent/broker split is 70/30 (70% to agent and 30% to broker)
Annual Gross Commissionable Sales (GCS) $5,000,000 - $9,999,999
the agent/broker split is 80/20 (80% to agent and 20% to broker)
Annual Gross Commissionable Sales (GCS) $10,000,000 to infinity
the agent/broker split is 90/10 (90% to agent and 10% to broker)
the agent/broker split is 60/40 (60% to agent and 40% to broker)
Annual Gross Commissionable Sales (GCS) $2,000,000 - $4,999,999
the agent/broker split is 70/30 (70% to agent and 30% to broker)
Annual Gross Commissionable Sales (GCS) $5,000,000 - $9,999,999
the agent/broker split is 80/20 (80% to agent and 20% to broker)
Annual Gross Commissionable Sales (GCS) $10,000,000 to infinity
the agent/broker split is 90/10 (90% to agent and 10% to broker)
In addition here are two caveats:
They agent must pay 6% out of the agent's split until the annual $3,000 cap is reached, then the agent keeps 100% of their split for the rest of the year.
The brokers split has a cap of $22,000 per year. Once the brokers side reaches a total of $22,000, the agent then keeps 100% of commissions (the split ends).
The brokers split has a cap of $22,000 per year. Once the brokers side reaches a total of $22,000, the agent then keeps 100% of commissions (the split ends).
My struggle is where a sale or commission crosses the "criteria line" and part of the value should adhere to one criteria and the other part of the value should adhere to another criteria.
Here is a Dropbox link to my spreadsheet. I highlighted in RED where I'm struggling, because my formulas are not sufficient.