Need help in creating a sliding scale commission worksheet

Thanks in advance for any help you can provide. I am trying to figure out how to best create an Excel worksheet that calculates real estate commission splits as they are received over the course of a calendar year. Like many real estate offices, agents have thresholds which reward them with better splits as their production continues to grow. The worksheet would catalogue commissions as they are received and calculate the splits as they pass each threshold.

From $0.00 - $75,000 70% of gross commissions rec'd
from $75,000 - $150,000 75% of gross commissions rec'd
$150,000 + 80% of gross commissions rec'd

I have tried a number of nested if functions, but one commission could can take you from say $70,000 to $155,000 which creates too many scenarios to test for. I've uploaded a stripped down worksheet which hopefully shows you what I'm talking about.

I appreciate your help!

I created a custom function I think it might do the trick. You can insert the new function hold ALT + F11 to open the visual basic editor window, on the menu click insert and module. Then just copy paste the code below onto the screen.

Please Login or Register  to view this content.

@JieJenn, it is a tiered calculation.

You can certainly use an embedded IF, however, personally for tiered calcs I like flexibility of SUMPRODUCT

First I would list the marginal % of each tier, e.g:

Please Login or Register  to view this content.

Then, Cumulative Commission

Please Login or Register  to view this content.

For per sale commission (Col L in your sample)

Please Login or Register  to view this content.

Advantage of above over embedded IF is flexibility - i.e you can easily add / remove tiers by simply adjusting precedent ranges to include / exclude additional tiers.

Oh sorry you're right. I wasn't paying attention....

@Estimated Profit,

should have added that the results of the above would tie out to your values with exception of row 18 for which I estimate a total of 58,553.24 [73618.08 * 75% + 4174.60 * 80%] as opposed to your value of 62234.14

Calculate Sliding Scale Tax/Commission