Free Excel template

Free Excel price list template with volume discounts

Q: Does this work with XLOOKUP?

Yes. In Excel 2021 or Microsoft 365, =XLOOKUP(E6,$A$29:$A$33,$B$29:$B$33,0,-1) finds the next smaller tier and does not depend on the column position.

For wholesalers, distributors and small B2B sellers who give bigger discounts on bigger orders. Keep your products with unit cost and list price, set the quantity tiers once, and type the quantities a customer wants; the template finds the right discount, calculates net prices, line totals and margin, and warns you when a discount pushes a line below your minimum margin.

cellwise-volume-discount-price-list-template.xlsxExcel workbook · 5.9 KB · works in Excel, Google Sheets, Numbers and LibreOffice · no signup

Download free template

Preview and try it

This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.

	A	B	C	D	E	F	G	H	I
1	Price list with volume discounts
2	Customer	Pinecrest Property Management			Quote valid (days)			30
3	Quote date	Nov 2, 2026			Valid until			46358
4	Minimum margin	0.25
5	SKU	Product	Unit cost	List price	Quote qty	Discount	Net price	Line total	Margin
6	PP-5000	Copy paper 8.5 x 11, case of 10 reams	31.2	49.99	40	0.05	47.49	1899.6	0.34301958307
7	TN-26X	Toner cartridge, high yield	88	139	6	0	139	834	0.36690647482
8	PN-BLK	Gel pens, black, box of 12	4.1	9.49	30	0.05	9.02	270.6	0.54545454545
9	NB-LG	Legal pads, pack of 12	7.25	14.99	25	0.05	14.24	356	0.49087078652
10	FL-LTR	File folders, letter, box of 100	9.8	18.5	10	0	18.5	185	0.47027027027
11	ST-STD	Standard staples, box of 5000	1.35	3.99		0	3.99	0	0.66165413534
12	BN-1	Binders, 1 in, white	2.15	4.79	120	0.1	4.31	517.2	0.50116009281
13	LB-30	Address labels, 100 sheets	12.4	24.99		0	24.99	0	0.50380152061
14	EN-10	#10 envelopes, box of 500	14.6	27.49	8	0	27.49	219.92	0.46889778101
15	CF-DK	Coffee, dark roast, 2 lb bag	13.9	21.99	24	0	21.99	527.76	0.3678944975
16	CP-12	Paper cups 12 oz, sleeve of 50	3.4	6.49	300	0.15	5.52	1656	0.38405797101
17	CL-WP	Disinfecting wipes, 3-pack	6.9	11.99	60	0.05	11.39	683.4	0.39420544337
18	TB-13	Trash bags 13 gal, box of 200	15.75	22.99	520	0.2	18.39	9562.8	0.14355628059
19	HS-1G	Hand soap refill, 1 gal	8.4	15.99		0	15.99	0	0.47467166979
20	Quote totals				1143			16712.28	0.25140974182

How the formulas work

Find the discount tier with approximate VLOOKUP

F6=IF(E6<1,0,VLOOKUP(E6,$A$29:$B$33,2,TRUE))

With TRUE as the last argument, VLOOKUP finds the largest minimum quantity that is not above the order quantity: 40 reams lands on the 25+ tier and gets 5%. The tier table must be sorted from smallest to largest minimum quantity. The IF gives 0% to products not on this quote.

Net price and line total

G6=ROUND(D6*(1-F6),2)

List price minus the discount, rounded to cents so the printed quote adds up. The line total in H6 is net price times quantity; a product without a quantity shows $0.00 but still shows its net price, so the sheet doubles as a price list.

Margin at the discounted price

I6=IF(G6>0,(G6-C6)/G6,0)

Margin is profit as a share of the selling price, (price - cost) / price. Trash bags at the 500+ tier sell for $18.39 against a $15.75 cost, a 14% margin, below the 25% minimum in B4. D25 counts quoted lines like that.

Count lines below the minimum margin

D25=COUNTIFS(I6:I19,"<"&B4,E6:E19,">0")

Two conditions: margin below the minimum and a quantity on the quote. Joining "<" with B4 builds the criterion from the cell, so changing the minimum to 20% re-counts immediately.

Price breaks for one product

C29=IFERROR(ROUND(INDEX($D$6:$D$19,MATCH($E$27,$A$6:$A$19,0))*(1-B29),2),0)

Type a SKU in E27 and the tier table shows that product's net price and margin at every tier, which is what a customer asks for when deciding how much to order. INDEX and MATCH look up the list price; IFERROR shows 0 for a SKU that is not on the list.

Make it yours

Discounts here apply per line (per product). If you discount on the total order quantity instead, point the VLOOKUP at the total in E20 rather than at each line's quantity.
Highlight thin margins: select I6:I19, Home > Conditional Formatting > New Rule > Use a formula, enter =AND(E6>0,I6<$B$4) and choose a red fill.
Margin and markup are different. A 25% margin is a 33% markup on cost. If you think in markup, add a column with =(G6-C6)/C6.
Keep one master price list and save each customer quote as a copy (File > Save As) or as PDF, so old quotes do not change when you update prices.

FAQ

How do I calculate tiered pricing in Excel?

Put the tier minimum quantities and discounts in a two-column table sorted from smallest to largest, then use =VLOOKUP(quantity,table,2,TRUE) to fetch the discount and multiply the list price by (1 - discount).

Why does VLOOKUP return the wrong tier?

Usually the tier table is not sorted by minimum quantity, or the last argument is FALSE (exact match), which only finds exact quantities. Sort ascending and use TRUE.

Does this work with XLOOKUP?

Yes. In Excel 2021 or Microsoft 365, =XLOOKUP(E6,$A$29:$A$33,$B$29:$B$33,0,-1) finds the next smaller tier and does not depend on the column position.

Functions used

Related guides

More free templates

Updated November 2, 2026