Real Estate Commission Calculator (Tiered)

[duteberta]

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:

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)​

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).
​

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.

 

[etaf]

have you got a new spreadsheet at all ?

 

[duteberta]

Here is the spreadsheet as it was last seen- without the correction.

https://www.dropbox.com/s/1rvuwl1g423ydao/COMMISSIONtRACKER.xls?dl=0

have you got a new spreadsheet at all ?

 

[Tim Rogge]

I am a new team lead and this spreadsheet is awesome for tracking however not so much for business planning. I'm killing myself trying to work out a code that works and am stuck on how to put this in terms of number of transactions and average sales price. Anyone who can help with this? I have the base of the sheet built but get stuck on when I hit the broker and franchise caps and try to roll them into the tiered structure. Please help! ... Tim