Tiered Commission Rate Formula

[J Lee]

I have a spreadsheet that needs to calculate commission payouts. Commission rates are tiered - everything up through 50% of their goal earns 3%, 50-100% earns 4%, above 100% earns 5%.

My problem is that commissions are earned for an entire month, but payed out weekly. Can anyone help me with a formula for week 3's commission payout (column I)?

Thanks!

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Week 1	Week 2	Week 3	Week 4	Week 5	Month Total	Month Goal	Week 3 Commission Payout				% Achieved	Rate
2	John	10,000	20,000	20,000			65,000	80,000	700				Above 100%	5%
3	Laura	10,000	15,000	10,000			35,000	80,000	300				50.01% - 100%	4%
4	Allison	30,000	35,000	40,000			105,000	100,000	1,650				Up to 50%	3%
Sheet

 

[etaf]

how do you pay weekly on a monthly goal ?
do you have a 4 week month or are they calendar months

John for example - has sold 65K , but the months target is 80K - so would you Pay 4% of the 20K
then in Week 4 - if john sold 40K - thats over 100% so 5%
would you then recalculate the months figures
as
week 1 - you paid 3% of 10k
Week 2 - you paid 3% of 20k
week 3 - you paid 4% of 20k - so you also owe 1% for week 1 and 2
week 4 - assuming 40K - over 100% - then you owe 5% for week 4 , 1% for week 3 and another 1% for week 2 and 1

is that how it would work ?

Week 5 - is that part of the Month goal as well
how do you work that

 

[J Lee]

We would pay the commission for what they sold during that week.

For John, the payout would have been as follows
Week 1 - $300 - 3% of 10000
Week 2 - $600 - 3% of 20000
Week 3 - $700 - 3% of 10000 + 4% of 10000 (since he exceeded 50% of his goal during this time period)

For Allison, the payout would have been
Week 1 - $900 - 3% of 30000
Week 2 - $1200 - 3% of 20000 + 4% of 15000
Week 3 - $1650 - 4% of 35000 + 5% of 5000

In this example, weeks 4 and 5 haven't happened yet. But eventually I will want to translate the formula to those weeks as well.

 

[shg]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr][tr][td]

3​

[/td][td="bgcolor:#F3F3F3"]

% Ach​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td="bgcolor:#F3F3F3"]

Delta​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]

0%​

[/td][td]

3%​

[/td][td="bgcolor:#E5E5E5"]

3%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]

50%​

[/td][td]

4%​

[/td][td="bgcolor:#E5E5E5"]

1%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]

100%​

[/td][td]

5%​

[/td][td="bgcolor:#E5E5E5"]

1%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td="bgcolor:#F3F3F3"]

Sales​

[/td][td="bgcolor:#F3F3F3"]

Month Goal​

[/td][td="bgcolor:#F3F3F3"]

Week 1​

[/td][td="bgcolor:#F3F3F3"]

Week 2​

[/td][td="bgcolor:#F3F3F3"]

Week 3​

[/td][td="bgcolor:#F3F3F3"]

Week 4​

[/td][td="bgcolor:#F3F3F3"]

Week 5​

[/td][/tr]
[tr][td]

9​

[/td][td]John[/td][td]

80,000​

[/td][td]

10,000​

[/td][td]

20,000​

[/td][td]

20,000​

[/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]Laura[/td][td]

80,000​

[/td][td]

10,000​

[/td][td]

15,000​

[/td][td]

10,000​

[/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]Allison[/td][td]

100,000​

[/td][td]

30,000​

[/td][td]

35,000​

[/td][td]

40,000​

[/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td="bgcolor:#F3F3F3"]

Commissions​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td="bgcolor:#E5E5E5"]John[/td][td]DoNotUse[/td][td="bgcolor:#E5E5E5"]

300​

[/td][td="bgcolor:#E5E5E5"]

600​

[/td][td="bgcolor:#E5E5E5"]

700​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][/tr]
[tr][td]

15​

[/td][td="bgcolor:#E5E5E5"]Laura[/td][td]DoNotUse[/td][td="bgcolor:#E5E5E5"]

300​

[/td][td="bgcolor:#E5E5E5"]

450​

[/td][td="bgcolor:#E5E5E5"]

300​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][/tr]
[tr][td]

16​

[/td][td="bgcolor:#E5E5E5"]Allison[/td][td]DoNotUse[/td][td="bgcolor:#E5E5E5"]

900​

[/td][td="bgcolor:#E5E5E5"]

1,200​

[/td][td="bgcolor:#E5E5E5"]

1,650​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][/tr]
[/table]

The formula in E14 and copied right and down is

=SUMPRODUCT((SUM($E9:E9) > $C$4:$C$6 * $D9) * (SUM($E9:E9) - $C$4:$C$6 * $D9) * $E$4:$E$6) - SUM($D14:D14)

 

[J Lee]

Thanks shg
Worked like a charm!

 

[shg]

You're welcome.