Excel 2007
Windows 8.1
Hello everyone, and thank you in advance!!!
I am creating a commission worksheet for my real estate agents so they can easily track their earnings for every deal. I have essentially found the solution (thanks to the second-half of a YouTube post from MrExcel), but do not know how to implement it for my specific needs.
MrExcel's Learn Excel #757 - Sliding Commission
http://youtu.be/r1nqJR_UAFc
The problem:
Agents earn a gross commission per deal, generally between 2.5% and 3.0% of the total sale. For example, If an agent sells a home for $250K, they generate a gross commission of 3.0%, or $7,500. My business collects this check from the title company, deducts brokerage and admin fees, then pays the agent their net commission in three tiers based on ANNUAL sales volume:
>$0 but <$1,000,000 = 65% of 3%
>=$1,000,000 but <$2,000,000 = 75% of 3%
>=$2,000,000 = 90% of 3%
The two tricky items are as follows:
1) Agents' sales volumes accumulate over the course of the year (they don't reset until the next fiscal year) i.e. a rolling total.
2) The agents must completely meet each threshold before getting paid a higher percentage on the remainder. For example, if an agent's first few deals total $800,000, then they sell a home for $1.25mm, they will be paid on the deal as follows: 3.0% of $200K @ 65%, 3.0% of $1mm @ 75%, and 3.0% of $50K @ 90%.
Assuming each agent tracks their rolling annual volume total, and we know their gross commission, how can we find their net commission?
I've used nested IFs with AND... but I can't get a unified formula or process to handle all scenarios across all three tiers. I'm pretty sure nested VLOOKUPs will work if I create a commission split table on a separate sheet within the workbook. That said, my brain can only handle two moves ahead, not three. Any help you can give will be GREATLY appreciated.
Windows 8.1
Hello everyone, and thank you in advance!!!
I am creating a commission worksheet for my real estate agents so they can easily track their earnings for every deal. I have essentially found the solution (thanks to the second-half of a YouTube post from MrExcel), but do not know how to implement it for my specific needs.
MrExcel's Learn Excel #757 - Sliding Commission
http://youtu.be/r1nqJR_UAFc
The problem:
Agents earn a gross commission per deal, generally between 2.5% and 3.0% of the total sale. For example, If an agent sells a home for $250K, they generate a gross commission of 3.0%, or $7,500. My business collects this check from the title company, deducts brokerage and admin fees, then pays the agent their net commission in three tiers based on ANNUAL sales volume:
>$0 but <$1,000,000 = 65% of 3%
>=$1,000,000 but <$2,000,000 = 75% of 3%
>=$2,000,000 = 90% of 3%
The two tricky items are as follows:
1) Agents' sales volumes accumulate over the course of the year (they don't reset until the next fiscal year) i.e. a rolling total.
2) The agents must completely meet each threshold before getting paid a higher percentage on the remainder. For example, if an agent's first few deals total $800,000, then they sell a home for $1.25mm, they will be paid on the deal as follows: 3.0% of $200K @ 65%, 3.0% of $1mm @ 75%, and 3.0% of $50K @ 90%.
Assuming each agent tracks their rolling annual volume total, and we know their gross commission, how can we find their net commission?
I've used nested IFs with AND... but I can't get a unified formula or process to handle all scenarios across all three tiers. I'm pretty sure nested VLOOKUPs will work if I create a commission split table on a separate sheet within the workbook. That said, my brain can only handle two moves ahead, not three. Any help you can give will be GREATLY appreciated.
