Tiered commissions on cumulative sales

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!

Thank SHG. It works perfectly.

Originally Posted by shg

See attached.

Hey guys, I don't seem to have permission to download this attachment? Is it still present on the forum? Any help would be appreciated.

I'm trying to do the same exact thing but perhaps I do not understand the logic that was presented in the spread sheet. Can it be quickly explained? Thanks.