Finding Unit Cost from a Tiered Rebate

[zzer0]

Hello,

I am looking to find the final unit cost for a product given a volume rebate.

In the below rebate structure, base unit cost is discounted based on volume, in which there is a 27.5% discount for sales up to 99, a 32.6% discount on 100-249, 37.5% on 250-499 etc etc.

I am looking for a formula to calculate the cumulative rebate for x units sold that I can then extrapolate to the final unit cost.

Thanks in advance.

 

[kevin9999]

This might do until a more elegant solution comes along...

Book1
	A	B	C	D	E	F
1	Units sold	248
2	Base units cost	$5.97
3	Total cost (w/o rebate)	$1,480.56
4	Cumulative rebate	$454.47
5	Total cost (w/ rebate)	$1,026.09
6	Final unit cost	$4.14
7
8			Start	End	Discount	Rebate
9			1	99	27.5%	$ 162.53
10			100	249	32.6%	$ 291.93
11			250	499	37.5%	$ -
12			500	999	42.5%	$ -
13			1000	1499	48.1%	$ -
14			1500	10000	51.1%	$ -
Sheet1

Cell Formulas
Range	Formula
B3	B3	=IFERROR(B1*B2,0)
B4	B4	=SUM(F9:F14)
B5	B5	=IFERROR(B3-B4,0)
B6	B6	=IFERROR(B5/B1,0)
F9	F9	=IF($B$1>=D9,B2*D9*E9,B2*E9*B1)
F10:F14	F10	=IF($B$1>=D9,(D10-D9)*$B$2*E10,0)