Profit & margins
Discount formulas in Excel
To calculate a discounted price in Excel, multiply the original price by one minus the discount: with the price in B2 and the discount in C2, use =B2*(1-C2). For an online store, the formulas that matter just as much are the margin after the discount and the extra sales you'd need to break even.
The four discount formulas in Excel
Set up a row with the original price in B2, the discount percentage in C2 (formatted as %), and the product cost in E2.
| What you want | Formula | Example result |
|---|---|---|
| Discounted price (D2) | =B2*(1-C2) | $40.00 |
| Amount off | =B2-D2 | $10.00 |
| % discount between two prices | =(B2-D2)/B2 | 20.0% |
| Margin after discount | =(D2-E2)/D2 | 50.0% |
Example inputs: price $50, discount 20%, cost $20.
Discounted price
=B2*(1-C2)
- B2
- original price
- C2
- discount as a percentage, e.g. 20%
Percentage discount between two prices
If you know the sale price and want the discount percentage, use =(B2-D2)/B2 and format the cell as a percentage.
Margin after discount
=(D2-E2)/D2
- D2
- discounted price
- E2
- cost per unit
On the example product, margin drops from ($50 − $20) ÷ $50 = 60% at full price to ($40 − $20) ÷ $40 = 50% with the discount.
The break-even sales lift formula
This is the formula most stores never run. A discount cuts profit per unit, so you need to sell more units just to earn the same total profit as before.
Break-even increase = Discount % ÷ (Gross margin % − Discount %) In Excel: =C2/(F2-C2)
- C2
- discount %
- F2
- gross margin at full price, =(B2-E2)/B2
Worked example: 20% off a product with a 60% marginExample numbers
- Profit per unit at full price = $50 − $20 = $30
- Profit per unit at 20% off = $40 − $20 = $20
- Break-even increase = 20% ÷ (60% − 20%) = 50%
You need to sell 50% more units, 15 for every 10, just to match full-price profit. Check: 15 × $20 = $300 = 10 × $30.
The required lift climbs fast as the discount gets closer to your margin:
| Discount | Lift needed at 60% margin | Lift needed at 40% margin |
|---|---|---|
| 10% | 20% | 33% |
| 20% | 50% | 100% |
| 30% | 100% | 300% |
| 40% | 200% | Never breaks even |
When the discount equals or exceeds your margin, every sale makes a loss, and no volume can make it back.
Discount Code Profit Impact CalculatorYour real margin after a discount code, and the volume needed to break even. Want this tracked automatically, every day?See which Google Ads campaigns actually make money, measured on real profit (POAS), not just revenue.Try Selvra OSStacked discounts
When two discounts apply one after the other, such as 20% off sitewide plus an extra 10% with a code, they don't add up to 30%. The second discount applies to the already-reduced price.
Final price: =B2*(1-C2)*(1-D2) Total discount: =1-(1-C2)*(1-D2)
- C2
- first discount, e.g. 20%
- D2
- second discount, e.g. 10%
On a $50 product: $50 × 0.8 × 0.9 = $36, a total discount of 28%, not 30%. That's slightly better for you than adding the percentages, but check your store settings: some platforms let codes combine in ways that cost more than you expect.
Percentage off vs dollar off
| Percentage off | Dollar off | |
|---|---|---|
| Example | 20% off | $10 off |
| Cost to you | Grows with order size | Fixed per order |
| Best for | Low-price items, where 20% sounds bigger than $4 | High-price items, or "$10 off orders over $75" |
To compare them in Excel, convert a dollar discount to a percentage of price: with the dollar amount in C2 and the price in B2, use =C2/B2. A $10 discount on a $50 product is 20%; on a $100 order, it's only 10%. That's why minimum-spend dollar discounts often protect margin better than a flat percentage.
Using the formulas to plan a promotion
- List the products in the promotion with price, cost and the planned discount.
- Add the break-even lift column and sort by it.
- Compare the lift with what's realistic. If past promotions lifted sales by 30%, a product that needs 100% will lose money.
- Adjust: lower the discount on low-margin products, or leave them out.
Want a ready-made sheet? The profit margin spreadsheet has margin, markup and price-from-margin formulas set up; add the discount columns above to it.
Alternatives to straight discounts
- Bundles move more units with a smaller discount. See the bundle discount calculator.
- Free shipping thresholds reward bigger baskets instead of cutting prices.
- Gift with purchase costs you the item's cost, not its retail price.
- Targeted codes for new or lapsed customers avoid giving money off to people who'd have paid full price.
FAQ
What's the Excel formula for a discount?
With the original price in B2 and the discount percentage in C2, the discounted price is =B2*(1-C2). A $50 price with 20% off gives $40.
How do I calculate the percentage discount between two prices?
With the original price in B2 and the sale price in D2, use =(B2-D2)/B2 and format it as a percentage. $50 down to $40 is a 20% discount.
How much does a discount cost me?
In profit, the full discount amount on every unit sold: a 20% discount on a $50 product costs $10 of profit per unit, whatever the product cost. That's why the sales lift needed to break even is so much bigger than the discount percentage.
Why use a decimal or percentage for the discount cell?
Excel stores 20% as 0.2. If you type 20 instead of 20%, the formula =B2*(1-C2) gives a negative price. Format the cell as a percentage before typing, or enter 0.2.