SUMPRODUCT formula & chart for blended tier pricing

[tammyanthos]

Blended tier pricing

I need to create a chart and formula that calculates what the price = based on the $ amount of the account

e.g. accounts up to $100k = .50, $100k-$250k = .60, etc. so an account of $175k would be.50 for the first $100k and .60 for the other $75k ?

 

[tammyanthos]

Thank you!
I tried the new formula for $1,124,900 and the answer should be $2894 and .257% (I did it the math the long way in bold to the right) but for some reason it's giving $2024.82 and .18%

 

[COwen]

Yep...Forgot that line...

 

[Dave Patton]

I show the Sumproduct formula; one uses the Rate table and the other includes the table information within the formula.

T202004a.xlsm
	A	B	C	D	E
1	Tiers	Bps	%	260,000.00	955.00
2
3	0	40	0.4000%
4	100,000	35	0.3500%
5	250,000	30	0.3000%
6	500,000	20	0.2000%
7	1,000,000	17.5	0.1750%
8
9				1,124,900.00	2,893.58
10
2b

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT(--(D1>A3:A7),D1-A3:A7,C3:C7-C2:C6)
E9	E9	=SUMPRODUCT(--(D9>{0;100000;250000;500000;1000000}),D9-{0;100000;250000;500000;1000000},{0.004;-0.0005;-0.0005;-0.001;-0.00025})

 

[COwen]

Does this work?

=IF(D1>100000,IF(D1<=250000,100000*0.4%+(((D1-150000))*0.35%),IF(D1<=500000,(((100000*0.4%)+((150000)*0.35%)+((D1-(250000))*0.3%))),IF(D1<1000000,(100000*0.4%)+(150000*0.35%)+(250000*0.3%)+((D1-500000)*0.2%),(100000*0.4%)+(150000*0.35%)+(250000*0.3%)+(500000*0.2%)+((D1-1000000)*0.175%)))),(D1*0.4%))

 

[Dave Patton]

T202004a.xlsm
	A	B	C	D	E	F	G
1	Tiers	Bps	%	260,000.00	955.00
2
3	0	40	0.4000%
4	100,000	35	0.3500%			400.00	400.00
5	250,000	30	0.3000%			525.00	525.00
6	500,000	20	0.2000%			30.00	750.00
7	1,000,000	17.5	0.1750%				1,000.00
8							218.58
9				1,124,900.00	2,893.58	955.00	2,893.58
10					2,893.58
11
2b

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT(--(D1>A3:A7),D1-A3:A7,C3:C7-C2:C6)
F4:F5	F4	=(A4-A3)*C3
F6	F6	=(D1-A5)*C5
G4:G7	G4	=(A4-A3)*C3
G8	G8	=(D9-A7)*C7
F9:G9	F9	=SUM(F4:F8)
E9	E9	=SUMPRODUCT(--(D9>{0;100000;250000;500000;1000000}),D9-{0;100000;250000;500000;1000000},{0.004;-0.0005;-0.0005;-0.001;-0.00025})
E10	E10	=SUMPRODUCT(--(D9>A3:A7),D9-A3:A7,C3:C7-C2:C6)

 

[tammyanthos]

I show the Sumproduct formula; one uses the Rate table and the other includes the table information within the formula.

T202004a.xlsm
	A	B	C	D	E
1	Tiers	Bps	%	260,000.00	955.00
2
3	0	40	0.4000%
4	100,000	35	0.3500%
5	250,000	30	0.3000%
6	500,000	20	0.2000%
7	1,000,000	17.5	0.1750%
8
9				1,124,900.00	2,893.58
10
2b

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT(--(D1>A3:A7),D1-A3:A7,C3:C7-C2:C6)
E9	E9	=SUMPRODUCT(--(D9>{0;100000;250000;500000;1000000}),D9-{0;100000;250000;500000;1000000},{0.004;-0.0005;-0.0005;-0.001;-0.00025})

I show the Sumproduct formula; one uses the Rate table and the other includes the table information within the formula.

T202004a.xlsm
	A	B	C	D	E
1	Tiers	Bps	%	260,000.00	955.00
2
3	0	40	0.4000%
4	100,000	35	0.3500%
5	250,000	30	0.3000%
6	500,000	20	0.2000%
7	1,000,000	17.5	0.1750%
8
9				1,124,900.00	2,893.58
10
2b

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT(--(D1>A3:A7),D1-A3:A7,C3:C7-C2:C6)
E9	E9	=SUMPRODUCT(--(D9>{0;100000;250000;500000;1000000}),D9-{0;100000;250000;500000;1000000},{0.004;-0.0005;-0.0005;-0.001;-0.00025})

 

[COwen]

To correct a small typo:

=IF(D1>100000,IF(D1<=250000,100000*0.4%+((D1-100000)*0.35%),IF(D1<=500000,(((100000*0.4%)+((150000)*0.35%)+((D1-(250000))*0.3%))),IF(D1<1000000,(100000*0.4%)+(150000*0.35%)+(250000*0.3%)+((D1-500000)*0.2%),(100000*0.4%)+(150000*0.35%)+(250000*0.3%)+(500000*0.2%)+((D1-1000000)*0.175%)))),(D1*0.4%))

 

[tammyanthos]

Does this work?

=IF(D1>100000,IF(D1<=250000,100000*0.4%+(((D1-150000))*0.35%),IF(D1<=500000,(((100000*0.4%)+((150000)*0.35%)+((D1-(250000))*0.3%))),IF(D1<1000000,(100000*0.4%)+(150000*0.35%)+(250000*0.3%)+((D1-500000)*0.2%),(100000*0.4%)+(150000*0.35%)+(250000*0.3%)+(500000*0.2%)+((D1-1000000)*0.175%)))),(D1*0.4%))

 

[tammyanthos]

To correct a small typo:

=IF(D1>100000,IF(D1<=250000,100000*0.4%+((D1-100000)*0.35%),IF(D1<=500000,(((100000*0.4%)+((150000)*0.35%)+((D1-(250000))*0.3%))),IF(D1<1000000,(100000*0.4%)+(150000*0.35%)+(250000*0.3%)+((D1-500000)*0.2%),(100000*0.4%)+(150000*0.35%)+(250000*0.3%)+(500000*0.2%)+((D1-1000000)*0.175%)))),(D1*0.4%))

YES!!! THANK YOU SO MUCH FOR ALL YOUR HARD WORK!!!

 

[COwen]

You are very welcome!