Declining Commission Rate

I have the need to calculate a declining commission rate (or is it called regressive?). There is a base commission rate which is calculated on each sales dollar up to a target. Once the sales value exceeds the target, the commission rate begins declining. For each dollar over the target, there is a different rate applied. The total commission then is a cumulative sum of each of the commission values which have been calculated by the regressive rate.

I have attached a workbook, which will better explain the process. In the workbook you will see a green colored data range, which is for design purposes only. I hope to use a formula or function which will calculate what I need, without having this range. The 'over target' column will not be static, as the 'over target' calculation will change with each record. The chart is not required, but is part of the draft workbook to verify that this is a non-linear result. The example section (yellow) is more of what I will need, with the ability to reference the base commission rate and the ability to change the sale and target values.

Thank you for your reply. Unfortunately, it does not satisfy my needs, due to the following requirements:

1. The 'Over Target' list may grow upwards of target + 50, instead of the example of 10. It will not be a fixed number.
2. The provided formula does not give me the cumulative total, which I hope to able to accomplish.

By a cumulative total, and using over target + 5, the result should be the $100(target) + 0.09 + 0.08 + 0.07 + 0.06 + 0.05 = $100.35. I am hoping to have this in one cell, without the need of having an extensive table consisting of 50 +/- rows. Maybe there is an Excel statistical function that I do not know about.

I have re-posted the Excel workbook, with 3 example tables. Again, I'm hoping these are examples, and that there is a function / formula that will calculate them. But for example purposes, here are the tables.

Some of the examples are a little confusing; on Sheet 1 examples the commission is target*base% + incremental whereas on Table Examples you're adding the Target (100) + cumulative commission.

Anyway, given potential no. of tiers, per sample tables, you would want to go down a SUMPRODUCT route I suspect as offers a lot of flexibility.

Using scenario of 119 sale {Sheet1!A21}, 100 Target {Sheet1!B21}, and using marginal rate Table per G:K on Table Examples

Formula:

Please Login or Register  to view this content.

the above would return 10.95 rather than 100.95 as it's adding the incremental (0.95) to the Target @ Base value as opposed to just Target

You are right - that was more confusing than it could have been. Sorry about interchanging my 100's and 10's. You were correct in your assumption - the Table Examples should have been Target (100 * 10%) + cumulative commission.
I am grateful for your assistance, and risk sounding ungrateful as I push this a little further. I guess I was hoping that there might be an Excel function that would eliminate the need for a marginal rate table. The tables were shown as examples. As you can see, in one instance the rate regresses to zero at target +10, in another instance it is target + 20, and in another it is target + 30. I was hoping I was clear that those were given as examples, as the zero rate can be set to occur at any desired cap. Even if I acquiesce and use the marginal rate tables, it still seems that I am limited to using a set table length (in your example: target + 30).

So, if I've understood, the below would work but is over complicated, unfortunately I can't look at this currently to simplify but others will.

Using same values as before only now the "cap" is entered into G21, so with 119 you'd get different results if you used 20, or 30... i.e .10.95 (20) or 11.27 (30)

Formula:

Please Login or Register  to view this content.