Set optimal pricing based on costs, margins, and market factors
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
Most pricing errors come from marking up cost instead of setting margin. This calculator makes the difference explicit and shows what a discount actually costs in volume terms.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Product | Cost | Price | Margin % | Markup % | BE at −10% |
| 2 | Standard | 61.05 | 129.00 | 52.7% | 111.3% | +23.4% |
| 3 | Premium | 133.60 | 289.00 | 53.8% | 116.3% | +22.8% |
| 4 | Starter | 38.00 | 69.00 | 44.9% | 81.6% | +28.6% |
| 5 | Bundle | 232.65 | 449.00 | 48.2% | 93.0% | +26.2% |
Why each one is written the way it is
=([@Price]-[@Cost])/[@Price]
Margin: profit as a share of price. This is the number that matters, and it is not the one most people quote.
=([@Price]-[@Cost])/[@Cost]
Markup: profit as a share of cost. A 50% markup is a 33% margin — confusing the two is the most expensive arithmetic error in small business pricing.
=[@[Margin %]]/([@[Margin %]]-DiscountPct)-1
Break-even volume increase for a discount. At a 53% margin, a 10% discount needs 23% more units just to stand still.
=[@Cost]/(1-TargetMargin)
Price from a target margin, which is how prices should be set. Marking up cost lets your supplier choose your margin.
A worked example with real numbers
A distributor offered 15% off to win a volume order, reasoning that a 53% margin left plenty of room. The break-even calculation showed the order needed to be 39% larger than the customer's normal volume to leave them no worse off; it was 20% larger. The deal was taken anyway as a strategic account, but with the real cost — about €9,400 of margin — known in advance rather than discovered at year end.
Tab by tab
| Tab | Contents |
|---|---|
| Pricing | Cost, price, margin, markup and discount break-even. |
| Costs | Loaded cost per product, linked to the costing sheet. |
| Scenarios | Price and volume combinations side by side. |
| Discounts | Approved discount bands and their margin impact. |
| README | Margin versus markup, with the conversion table. |
What is included, and what to look at next
Specific to this workbook, not generic
Margin is profit over price; markup is profit over cost. A 50% markup gives a 33% margin. Setting prices by markup means your margin moves whenever your costs do, which is backwards.
From what your operating costs require, not from a benchmark. Work out the gross margin needed to cover fixed costs at realistic volume, then add the profit you want.
Yes, for something in return — volume commitment, longer term, prepayment, a reference. An unconditional discount is a price cut that you will be asked to repeat.
"This calculator has helped us optimize our pricing strategy across our entire product line. We've seen significant improvement in our margins while staying competitive."