After more Googling and experimenting then I care to discuss, I have run into a brick wall attempting to create a formula that can help me through my objective. I found a few threads with similar questions, but no answers, so am hopeful someone can provide direction. I am comparing several commission structures, one of which is outlined below:
<tbody>
</tbody>
This is a graduated, or step up fee structure. For example, if the gross commission is $40,000, the net would be $4,000. Pretty simple. But if the fee is $400,000, then the first $50,000 earns only 10%, the next $40,000 earns 20%, the next $200,000 earns 30%, and the last $100,000 would earn 40%, which totals $5,000+$8,000+$60,000+$40,000=$113,000.
I'd like to enter the gross commission into a cell, and have the net commission calculated. We will be evaluating several structures with similar design, so theoretically I want to have probably 10 different net commission scenarios computed by entering the gross commission. If someone can provide direction on the above example, I am sure I can figure out the rest.
Thank you in advance.
Gross Commission ($) | Net Commission (%) |
0-50,000 | 10% |
50,001 - 100,000 | 20% |
100,001 - 300,000 | 30% |
300,001+ | 40% |
<tbody>
</tbody>
This is a graduated, or step up fee structure. For example, if the gross commission is $40,000, the net would be $4,000. Pretty simple. But if the fee is $400,000, then the first $50,000 earns only 10%, the next $40,000 earns 20%, the next $200,000 earns 30%, and the last $100,000 would earn 40%, which totals $5,000+$8,000+$60,000+$40,000=$113,000.
I'd like to enter the gross commission into a cell, and have the net commission calculated. We will be evaluating several structures with similar design, so theoretically I want to have probably 10 different net commission scenarios computed by entering the gross commission. If someone can provide direction on the above example, I am sure I can figure out the rest.
Thank you in advance.