Calculate tiered commisions on commulative monthly sales

[Legacy 109559]

Hi -
I have done a lot of reseach regarding tiered commision formulas in excel but none of them addresses how to calculate tiered commisions on cummulative monthly sales.

The annual quota is $1,200,000.

There are four commision tiers
0 - 80% of quota = 3% commision
80%- 100% of quota = 9% comission
100%-110% of quota = 5% commision
110%- > of quota = 8% commision

I have variable sales made in each of 12 months. I need to calculate the comission on cummulative sales basis each month.

For instance if in first month the whole $1,200,000 is achieved the comission will be as follow:
3% on 960000
9% on rest 240000

On other hand if the sales for first month were $20,000 and sales for seond month were $1,400,000

then the commision for first month would be 3% on $20,000
and
comission for second month would be as follow
3% on 940,000 (960000-20000) as commision is paid on annual cumulative sales.
9% on 260,000
5% on 12,000
8% on 80,000

Now this is my dilema as usually tiered comission are paid on monthly or quarterly basis and not on commulative annual sales. I can seem to get my head around this.

Any help would be appreciated.

Thanks

AL!

 

[Domenic]

If I'm not mistaken, I believe the calculation for your last example is not correct. If so, let's assume that B2 contains the sales for the first month, C2 contains the sales for the second month, and so on. Try...

B3, copied across:

=SUMPRODUCT(--(SUM($A$2:B2)>{0,960000,1200000,1320000}),B2-{0,960000,1200000,1320000},{0.03,0.06,-0.04,0.03})-SUM($A$4:A4)

Note that Column A is needed in the calculation. It can be either empty/blank or contain a text value, such as a row header.

Hope this helps!