Variable rates applied to accumulated results

[leebauman]

Hello! In need of assistance to build functional tool to help sales agents track monthly commissions. The commission rate is applied to monthly sales and the rate increases as accumulated annual sales reach specified thresholds. I'm having trouble entering formulas in the "Monthly Incentive" column which generate accurate results. I appreciate any assistance!

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

Accumulated Annual Sales​

[/td][td]

$0 - $2.5M​

[/td][td]

$2.5M - $3.5M​

[/td][td]

$3.5M+​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

Incentive Rate​

[/td][td]

0.05%​

[/td][td]

0.63%​

[/td][td]

0.88%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

Month​

[/td][td]

Monthly Sales​

[/td][td]

Accumulated Annual Sales​

[/td][td]

Monthly Incentive​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]January[/td][td]

$250,000​

[/td][td]

$250,000​

[/td][td]

$0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]February[/td][td]

$250,000​

[/td][td]

$500,000​

[/td][td]

$0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]March[/td][td]

$250,000​

[/td][td]

$750,000​

[/td][td]

$0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]April[/td][td]

$250,000​

[/td][td]

$1,000,000​

[/td][td]

$0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]May[/td][td]

$250,000​

[/td][td]

$1,250,000​

[/td][td]

$0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]June[/td][td]

$250,000​

[/td][td]

$1,500,000​

[/td][td]

$0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]July[/td][td]

$250,000​

[/td][td]

$1,750,000​

[/td][td]

$0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]August[/td][td]

$250,000​

[/td][td]

$2,000,000​

[/td][td]

$0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]September[/td][td]

$250,000​

[/td][td]

$2,250,000​

[/td][td]

$0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]October[/td][td]

$400,000​

[/td][td]

$2,650,000​

[/td][td]

$0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]November[/td][td]

$250,000​

[/td][td]

$2,900,000​

[/td][td]

$0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]December[/td][td]

$750,000​

[/td][td]

$3,650,000​

[/td][td]

$0​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[jtakw]

Hi,

Can you clarify, for the month of October, where the cumulative is 2,650,000, is the incentive to be paid 250,000 at .05%, and 150,000 at .63%
Or, all 400,000 at .63%

 

[leebauman]

Hello, for October, the first $249,999 would be paid at the .05% rate (up to the $2.5M cumulative amount) and the remaining $150,001 would be paid at the .63% rate.

Same logic for December...first $599,999 paid at the .63% rate and remaining $150,001 paid at the .88% rate.

Thanks for the response!

 

[jtakw]

With some slight adjustment to your setup.

You can use E7 formula copied down, and ignore my Column F,
Or, setup F2:F4 as shown, and use F7 formula copied down.

Book1
	B	C	D	E	F
1		Above	To		Delta
2	Accumulated Annual Sales	0	2499999	0.05%	0.05%
3		2499999	3499999	0.63%	0.58%
4		3499999		0.88%	0.25%
5
6	Month	Monthly Sales	Accumulated Annual Sales	Monthly Incentive	Monthly Incentive
7	January	$250,000	$250,000	$125	$125
8	February	$250,000	$500,000	$125	$125
9	March	$250,000	$750,000	$125	$125
10	April	$250,000	$1,000,000	$125	$125
11	May	$250,000	$1,250,000	$125	$125
12	June	$250,000	$1,500,000	$125	$125
13	July	$250,000	$1,750,000	$125	$125
14	August	$250,000	$2,000,000	$125	$125
15	September	$250,000	$2,250,000	$125	$125
16	October	$400,000	$2,650,000	$1,070	$1,070
17	November	$250,000	$2,900,000	$1,575	$1,575
18	December	$750,000	$3,650,000	$5,100	$5,100
Sheet428

Cell Formulas
Range	Formula
F2	=E2-E1
F7	=SUMPRODUCT((D7>=C$2:C$4)*(D7-C$2:C$4)*F$2:F$4)-SUM(F$6:F6)
E7	=SUMPRODUCT((D7>=C$2:C$4)*(D7-C$2:C$4)*(E$2:E$4-E$1:E$3))-SUM(E$6:E6)

 

[leebauman]

That works perfectly. Thank you so much. Wishing you a fantastic 2019.

Many thanks.

 

[jtakw]

You're welcome, Happy New Year.