Calculating tiered payout with cumulative numbers

[kshankar88]

Hello,
I took a look at the tiered payout problems posted but I could not apply same solution as my problems differs as below:

Annual New client (no: of clients)	Payout in $ per client
0-40	50
41-60	100
61-90	200
91+	250

<tbody>
</tbody>

In my problem, I have something called marginal pay in cumulative fashion, like how incentive/bounty gets paid. For e.g., I have the first 40 clients paid $50 each, the next 20 paid $100 each and the next 30 are paid $200 each, etc. so, when I have 90 new clients, the payout is $10,000 (NOT $18,000).


I tried to calculate the payout at each tier and different payout, but did not go right with sumproduct logic. Could you pl help what could be solution suggested?

Thanks,
Shankar

 

[kshankar88]

So when I have a table as below, I need to calculate the monthly payout to the client based on the table in previous post (using single formula in each cell marked with '?')

Name	Jan	Feb	Mar	Apr
Client1	24	35	14	29
Monthly Payout	?	?	?	?

<colgroup><col style="width:48pt" span="5" width="64"> </colgroup><tbody>
</tbody>

If I calculate manually, I obtain payout values for each month in order as 1200, 2700, 2700, 6400 totalling to 13000$.

 

[Peter_SSs]

If I calculate manually, I obtain payout values for each month in order as 1200, 2700, 2700, 6400 totalling to 13000$.

Welcome to the MrExcel board!

Can you explain how you calculated each of those payout values manually?

 

[hwong2004]

Assuming B2 to B5 are number of clients
Then,
C2=MIN(40,SUM($B$2:B2))*50+MAX(0,MIN(20,SUM($B$2:B2)-40))*100+MAX(0,MIN(30,SUM($B$2:B2)-60))*200+MAX(SUM($B$2:B2)-90,0)*250
C3=MIN(40,SUM($B$2:C2))*50+MAX(0,MIN(20,SUM($B$2:C2)-40))*100+MAX(0,MIN(30,SUM($B$2:C2)-60))*200+MAX(SUM($B$2:C2)-90,0)*250-SUM($B$3:B3)
D3=MIN(40,SUM($B$2:D2))*50+MAX(0,MIN(20,SUM($B$2:D2)-40))*100+MAX(0,MIN(30,SUM($B$2:D2)-60))*200+MAX(SUM($B$2:D2)-90,0)*250-SUM($B$3:C3)
E3=MIN(40,SUM($B$2:E2))*50+MAX(0,MIN(20,SUM($B$2:E2)-40))*100+MAX(0,MIN(30,SUM($B$2:E2)-60))*200+MAX(SUM($B$2:E2)-90,0)*250-SUM($B$3:D3)

For D3 and E3, you can just pull (or copy paste) the formula from C3

Hope this is what you are looking for

 

[Dave Patton]

in B3 =SUMPRODUCT(--(SUM($A$2:B2)>r_Tiers),SUM($A$2:B2)-r_Tiers,r_Delta)-SUM($A$3:A3)

Please note
r_Tiers is named range of the Tiers that you mention.
r_Delta is the Delta of the rates (difference from previous)

If you just need the total


=SUMPRODUCT(--(SUM($A$2:B2)>r_Tiers),SUM($A$2:B2)-r_Tiers,r_Delta)


There are many examples with similar formulas and alternatives on this forum.

One example is

http://www.mrexcel.com/forum/excel-questions/812111-formula-tiered-pricing.html

 

[kshankar88]

Hi hwong2004,
I am confused on the column names you mentioned above
Lets say my first table's column names are A1-A5, B1-B5

Lets say, my second table starts somewhere later as
A51-A53, B51-53, C51-53, D51-53, E51-53 such that '?' marked cells are B53, C53, D53 and E53. Could you pls help?

 

[kshankar88]

Hi Dave Patton,
Can you pl let know what are A, B columns here?
From my understanding of your answer, if B3 is first cell (from left), marked with '?', how can i find sum(A2:B2) because in A columns I have strings?
Also, when i move to Feb month, I have to keep in mind that I have already paid out first 24 clients from first range. So, in Feb, 35 has to be split as (30-24) for first tier and (35-(30-24)) for second tier....

Can you pl let know?

 

[kshankar88]

Welcome to the MrExcel board!

Can you explain how you calculated each of those payout values manually?

Hello Peter,
I calculated as follows:
for Jan, as 24<40 (40 is range of first tier), I calculated 24*50 = 1200. There are 16 more to fill tier1.
for Feb, I have 35 which is split as (40-24) and (35-(40-24)), hence it's 16*50 + 19*100 = 2700. I have 1 more to fill tier 2 (20 is range of tier2)
for Mar, I have 14 which is split as (20-19) and (14-(20-19)), hence it's 1*100 + 13*200 = 2700. I have 17 more to fill tier3 (30 is range of tier3)
for Apr, I have 29 which is split as (30-13) and (29-(30-13)), hence it's 17*200 + 12*150 = 6400. Now, for subsequent months, clients will go to tier 4, as I have paid out from tiers 1, 2, and 3.

Hope this clarified.

 

[shg]

Here's an example of what Dave describes:

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Clients​	Payout​	Delta​	​	​	​	​
2​	0​	50​	$ 50.00 ​			C2: =B2-N(B1)
3​	40​	100​	$ 50.00 ​
4​	60​	200​	$ 100.00 ​
5​	90​	250​	$ 50.00 ​
6​
7​
8​	Month​	Clients​	Cumu​	Payout​	Amt Per​
9​	Jan	7​	7​	$ 350.00 ​	$ 50.00 ​	D9: =SUMPRODUCT((C9 > $A$2:$A$5) * (C9 - $A$2:$A$5) * $C$2:$C$5) - SUM(D$8:D8)
10​	Feb	5​	12​	$ 250.00 ​	$ 50.00 ​
11​	Mar	3​	15​	$ 150.00 ​	$ 50.00 ​
12​	Apr	13​	28​	$ 650.00 ​	$ 50.00 ​
13​	May	6​	34​	$ 300.00 ​	$ 50.00 ​
14​	Jun	7​	41​	$ 400.00 ​	$ 57.14 ​
15​	Jul	11​	52​	$ 1,100.00 ​	$ 100.00 ​
16​	Aug	14​	66​	$ 2,000.00 ​	$ 142.86 ​
17​	Sep	4​	70​	$ 800.00 ​	$ 200.00 ​
18​	Oct	15​	85​	$ 3,000.00 ​	$ 200.00 ​
19​	Nov	7​	92​	$ 1,500.00 ​	$ 214.29 ​
20​	Dec	5​	97​	$ 1,250.00 ​	$ 250.00 ​

 

[Dave Patton]

SUM($A$2:B2)

Will sum the text in A2 and B2 which will evaluate as 0 + value in B2

SUM($A$3:A3) at the end of the formula sums the cells to the left.
The cell A3 has text;consequently, this part of the formula correctly subtracts 0 when the formula is in B3.

This structure lets one copy the same formula across to calculate the result for each column.