Writing formula for calculation

[Exceldummy2018]

I need to compute a table, but I am not sure on how to write the formula.

For example,
Sales price: 10Mil

For first 1mil the tiered rate is 1%
The next 1 mil the tiered rate is 0.8%
The next 3 mil the tiered rate is 0.7%
The next 3 mil the tiered rate is 0.6%
Thereafter the tiered rate is 0.5%.

By input 10Mil into the cell for sales price, it will automatically compute the tiered price for each tier category and its respective payable amount. Need assistance here.

 

[Special-K99]

Am not an expert in tiered pricing but try this
With A1 as the sales price

=SUMPRODUCT((A1>{0,1000000,2000000,5000000,8000000})*(A1-{0,1000000,2000000,5000000,8000000})*{.01,.08,.07,.06,.05})

 

[Dave Patton]

Excel 2010
	B	C	D	E	F	G
1	10,000,000.00	67,000.00		Bracket	Rate	Rate_Diff
2		67,000.00		0	1.00%	1.00%
3		67,000.00		1,000,000	0.80%	-0.20%
4				2,000,000	0.70%	-0.10%
5				5,000,000	0.60%	-0.10%
6				8,000,000	0.50%	-0.10%
7a

Cell Formulas
Range	Formula
G2	=F2-N(F1)
C1	= SUMPRODUCT(--(B1>$E$2:$E$6),B1-$E$2:$E$6,G2:G6)
C2	= SUMPRODUCT(--(B1>{0;1000000;2000000;5000000;8000000}),B1-{0;1000000;2000000;5000000;8000000},{0.01;-0.002;-0.001;-0.001;-0.001})
C3	= SUMPRODUCT(--(B1>$E$2:$E$6),B1-$E$2:$E$6,{0.01;0.008;0.007;0.006;0.005}-{0;0.01;0.008;0.007;0.006})