# Calculate tiered commisions on commulative monthly sales

#### koshain

##### New Member
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

##### MrExcel MVP
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!

1,081,765
Messages
5,361,156
Members
400,615
Latest member
inzimam

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...