Tiered Pricing & Volume Discount Optimizer

Purpose and model boundary

This calculator prices volume tiers. Up to five products are each sold in up to five tiers, where each tier starts at a whole-unit order size and runs to the next tier's start. A product uses all-units pricing, where every unit of the order is charged the price of the tier the order size falls in, or incremental pricing, where each unit is charged the price of the tier that unit number falls in. Each order can also carry a charge to the customer and a cost to you. Your unit cost can fall at its own cost breaks. An order table says how many orders of each size you expect.

One Calculate fills every result section of that one model: all your orders, the dead zones where a bigger all-units order pays less, one order checked in detail, and the best tier prices for one chosen product within the price range you set. An 11 by 11 what-if table of one order's contribution against order size and unit cost is calculated with them.

Demand is fixed: the model does not invent extra volume from a discount. Every order you enter stays the same at every price tried. So with no other rule, a higher price always earns more, and the best list is simply the highest one allowed. That is why a highest average price and a minimum margin are offered, and why a warning says so when the top of the range decides the answer. Every figure on this page is computed by the spreadsheet; the page adds nothing to it. The calculator does not say whether a customer will accept a price.

Inputs and units

• Products, up to 5 rows, typed, filled from the top without a blank row between used rows:
  • a product ID: a capital letter, then up to 15 capital letters or digits, each ID used once;
  • the pricing method: All-units or Incremental;
  • the unit cost, from 0 to 1,000,000, which applies below the first cost break;
  • the charge per order to the customer and the cost per order to you, each from 0 to 1,000,000: setup, handling, shipping, picking, packing or delivery that happens once per order;
  • an optional minimum order, from 1 to 10,000,000 (blank means 1), and an optional maximum order, not below the minimum (blank means no maximum, so the last tier is open).
• Volume tiers, up to 25 rows, typed or pasted from a spreadsheet: the product, the order size where the tier starts, from 1 to 10,000,000, and the price per unit, from 0 to 1,000,000, with up to 4 decimals. Every product needs a tier starting at 1 and may have at most 5. Rows of one product need not be together or in order.
• Unit cost breaks, optional, up to 25 rows, typed or pasted: the product, the order size the cost applies from, from 2 to 10,000,000, and the unit cost from that size, from 0 to 1,000,000. At most 5 per product.
• Orders, up to 20 rows, typed or pasted: the product, the order size, from 1 to 10,000,000 and within that product's minimum and maximum, and the number of such orders, from 0 to 1,000,000. Decimals are allowed: this is an expected count over the period, not a probability. Rows with the same product and size simply add.
• Price search: the product to optimize, chosen from the product IDs you entered, and the lowest and highest price change to try, each from -90% to +200%, the lowest not above the highest.
• What-if table: the order size to check, from 0 to 10,000,000 (0 means no order); a unit cost change for all products, from -90% to +200% (0 unless you change it), applied to every unit cost and cost break before anything is calculated, while costs per order do not change; and the spacing of the table's rows, from 1 to 1,000,000 units, and columns, from 1% to 20%.
• More options, closed until you open it:
  • the prices to try per tier, from 2 to 5 (5 unless you change it);
  • a minimum unit margin, from 0% to 95% (blank means no minimum);
  • a highest average price per unit, from 0 to 1,000,000 (blank means no cap).

Money is in dollars. Order sizes are whole units.

Governing relationships

Tiers. A tier runs from its start to one unit below the next tier's start, so tiers never overlap and leave no gap. An order exactly at a tier's start is in that tier. The last tier ends at the product's maximum order, or stays open.

Item revenue of one order. All-units: the order size times the price of the tier the size falls in. Incremental: for each tier, the units of the order inside that tier times that tier's price, added up. With tiers from 1 at 10.00 and from 101 at 8.00, 150 units earn 1,200.00 all-units (150 × 8.00) and 1,400.00 incremental (100 × 10.00 + 50 × 8.00).

Unit cost. The unit cost of an order is the cost of the product's largest cost break at or below the order size, or the product's unit cost when there is none. It applies to every unit of the order, and it is independent of the selling tiers. The unit cost change multiplies it.

One order. Revenue is item revenue plus the charge per order. Cost is the order size times the unit cost, plus the cost per order. Contribution is revenue minus cost, and margin is contribution divided by revenue, not shown when revenue is 0. An order of size 0 has no charge and no cost. The price per unit shown is item revenue divided by units, with charges left out.

All orders. Each order row's item revenue, charges, item cost and order costs are that amount times the number of orders, posted to the cent, rounding half away from zero. Every product total and grand total is a sum of posted lines.

Dead zones. A dead zone is a range of order sizes where a bigger order pays less item revenue than a smaller order the customer could place. It can only happen with all-units pricing, because incremental revenue never falls as the order grows. For each tier after the first, the model takes the most any smaller orderable order pays. If an order at the tier's start pays less than that, the zone runs from the tier's start to the last size that still pays less: 100 × 10.00 = 1,000 and 1,000 / 8.00 = 125, so at 8.00 from 101 the zone is 101 to 124. A zero-price tier is dead to its end. Minimum orders are respected: a size nobody can order does not count. Zones are measured on item revenue; charges per order are the same on both sides, so revenue and contribution fall by the same amount at a price break.

Best tier prices. For the one product to optimize, each tier price is tried at evenly spaced points from (1 + lowest change) to (1 + highest change) times its current price, rounded to 4 decimals, and every combination is calculated, plus today's list. With 5 prices and 5 tiers that is 3,126 lists. A list is allowed when:

• prices never rise from one tier to the next;
• when a minimum margin is entered, every tier's price keeps (price − unit cost) / price at or above it, using the highest unit cost a unit in that tier can carry, so a cost break later in the tier does not hide a loss before it;
• when a highest average price is entered, the product's item revenue divided by its units stays at or below it. Charges per order are not in this average. A list exactly at the cap passes at any size.

The best list is the allowed one with the highest contribution to the cent. On a tie it is the one closest to today's prices, then the first in order. This is the best of the lists tried: a better list may exist between them. It is never a claim about customer behaviour.

What-if table. Rows are 11 order sizes centred on the order size to check, one row step apart; columns are 11 unit cost changes centred on yours, one column step apart. Each cell is the contribution of one order of the product to optimize at today's prices, recalculated through the spreadsheet's own two-variable data table. The middle cell is the order checked. When a step would take the lowest row below 0 units, or the lowest column below -100%, the table is withheld with that reason and every other result still stands.

Calculation sequence

The spreadsheet first checks every product, tier, cost break and order row and every setting, and stops with NOT VALID at the first problem it finds, with every figure at 0. It then ranks each product's tiers and cost breaks, prices every order row and posts it to the cent, and adds the totals. It measures the dead zones at current prices, then prices the order to check. For the product to optimize it builds the candidate prices, evaluates every list on the unrounded contribution, marks which lists keep each rule, and picks the best. It posts today's list and the best list over that product's orders to give the comparison. The what-if table recalculates the order checked at its 121 combinations. A block of consistency checks, including that posted revenue less posted cost equals posted contribution, that no allowed list beats the one chosen and that the table's middle cell equals the order checked, runs before the status is returned.

Outputs and interpretation

The headline gives the contribution of all orders, the change in contribution at the best prices, and the contribution of the order checked. The result sections follow.

• All orders: the number of orders and units, item revenue, order charges, revenue, item cost, order costs, cost, margin, the number of dead zones and the orders inside them; with the results by product, by order row (including whether the row sits in a dead zone) and by tier (with each tier's change at the break, its dead zone, its highest unit cost and whether its price is below that cost).
• Best tier prices: the contribution and average price today and at the best prices, how many lists were tried, how many are allowed and how many pass each rule, the lowest average price tried, whether today's prices meet the rules, and whether a best price is the highest one tried; with a table by tier (today's price, the lowest and highest price tried, the best price, its change and the floor price) and a comparison of today against best. Under INFEASIBLE the best prices are blank and the counts show which rule stopped them.
• Order checked: its tier, price per unit, item revenue, charge, revenue, unit cost, item cost, cost per order, total cost, margin, and whether it sits in a dead zone.

A negative change at the best prices is not a mistake: when today's prices break a cap or a margin rule, it is what meeting that rule costs. Prices and average prices show up to 4 decimals, as the workbook formats them, so 0.2868 reads 0.2868 and an average of 3.641667 reads 3.6417; the API and MCP return them unrounded.

For nearby questions, the Price Volume Profit Scenario Simulator projects profit when demand responds to price, the Quote Pricing & Deal Margin Optimizer prices one quote with its discounts, labor and commission, and the EOQ Calculator looks at order size from the buyer's side.

Validation and status logic

The workbook returns one status, chosen in this order: NOT VALID, then INFEASIBLE, then CHECK, then OK.

• NOT VALID names the first problem: a product row (a blank row above a used row, a missing or malformed ID, a repeated ID, an unknown method, a value outside its limits, or a maximum order below the minimum); no product; a tier row (a gap, a missing value, a product not in the table, a start outside its limits or above the maximum order, a price outside its limits, or two tiers of one product at the same start); a product with no tier starting at 1 or with more than 5 tiers; a cost break row (a gap, a missing value, a product not in the table, a start below 2, a cost outside its limits, or a repeated start) or more than 5 breaks for a product; an order row (a gap, a missing value, a product not in the table, a size outside its limits or outside the product's minimum and maximum, or a number of orders outside its limits); no order row; and then a setting outside its limits, including a product to optimize that is not in the table and a lowest price change above the highest; and last a plan above the size limit: "Calculation exceeds the supported numerical range; reduce the prices, order sizes or number of orders." NOT VALID is a refusal, not a numeric answer: every figure is withheld.
• INFEASIBLE means no list tried keeps every rule. All orders, the dead zones, the order checked, the what-if table and today's figures stay valid and shown; the best prices, the best contribution, the change and the best series on the charts are blank, and the counts say how many lists keep each rule.
• CHECK accompanies a valid result that needs a look because a workbook consistency check failed.
• OK otherwise.

Warnings say when there are all-units dead zones and how many orders fall inside them; when a tier is priced below the highest unit cost it can carry; when a best price is the highest one tried, because with fixed demand a higher price earns more unless a rule stops it; when today's prices of the product to optimize break a rule, so the change is measured from prices that are not allowed; when a product's tier prices rise with order size; when the product to optimize has no units ordered, so today's prices are kept; when the order size to check is outside the product's minimum or maximum order; when a cost break starts above the product's maximum order and never applies; and when the what-if table is withheld. A warning never changes the status.

Assumptions and limitations

• Capacities are 5 products, 5 tiers and 5 cost breaks per product, 25 tier rows, 25 cost break rows and 20 order rows.
• Demand is fixed. Nothing here estimates how many orders a price wins or loses, or whether customers would move their order size away from a dead zone.
• One product is optimized at a time; other products are unchanged. Every tier uses the same range, relative to its own current price, and the search tries only the declared points of that range.
• One charge and one cost per order cover whatever is billed or paid once per order.
• Quantities are whole units; the number of orders may be a decimal.
• A cost break applies to every unit of an order of that size. Costs per order do not change with the unit cost change. There is no tax, currency conversion, capacity limit or timing.
• Very large plans are refused, not estimated. Above about $10 trillion of revenue plus cost, where whole cents stop being exact, the calculator refuses. It tests that on every path that reports money: each order row at today's prices, the highest price list that meets the rules, the order checked, one order of any order row and any size on the charts. Below that limit one cent of posted contribution decides the best price list, apart from small rounding in the underlying sum.
• Amounts within two cents below the $10 trillion limit may show as $10 trillion.
• Very large orders landing exactly on the end of a dead zone may show as inside it.
• At a dead-zone boundary, rounding to 15 significant digits can make the zone end one order early when two very large orders differ by less than that precision, such as $0.0001 at about $2 trillion.
• Results exactly on, or within rounding noise of, a half-cent edge may differ by one cent, including a tie between price lists, so the best price list named can change.
• The highest average price has a size allowance, because the same money summed two ways differs by rounding at this size. A list can pass above the cap by at most 0.0001 cents of revenue up to about $500 million, 0.2 cents at $1 trillion and 2 cents at the $10 trillion limit.

Restrictions and non-computing states

The calculator enforces its numeric and choice limits before it accepts a result. A blank row between used rows, a repeated product ID, a product with no tier at 1, and an order outside its product's minimum and maximum are refused by the workbook. When a step would take the what-if table's lowest row below 0 units or its lowest column below -100%, only the table is withheld, with that reason. The same holds when only some what-if scenarios are above the size limit: "Some what-if scenarios exceed the supported numerical range."

Errors and warnings

A request rejected by the page's input limits is not a computed result. A transport or service failure, a timeout or a cancelled run cannot establish a fresh workbook answer, and the last result stays on the page marked as out of date. Review the workbook's References and Disclaimer, and check a result against your own costs and terms, before using it to set real prices.

Sources and further reading

Your prices, tiers, costs, charges and order counts come from the inputs; the calculator holds no market, industry or customer data, and every value is your own assumption. The sources below explain the ideas the model uses. They do not endorse this calculator.

• Wikipedia, Economic order quantity, section "Quantity discounts" describes the two main types of quantity discount, all-units and incremental: one price for the whole order set by its size, against a separate price for each successive block of units. The calculator uses the same two methods; it does not use that article's order-quantity formula (retrieved 2026-11-03).
• OpenStax, Principles of Accounting, Volume 2: Managerial Accounting, 3.1 Explain Contribution Margin defines contribution margin as the amount by which a selling price exceeds variable cost, the amount left to cover fixed costs and profit; the calculator's contribution is revenue less the item cost and the cost per order (retrieved 2026-11-03).
• Microsoft Support, Calculate multiple results by using a data table describes the two-variable data table the spreadsheet uses for the 11 by 11 what-if table (retrieved 2026-11-02).

This is an educational model of the prices and orders you enter, not financial, accounting, pricing or legal advice. It does not estimate how customers would respond to a price, it is not certified financial reporting, and it does not guarantee a profit; check a result against your own costs and terms before using it.