Sales template
Discount & Margin Calculator
What a discount does to your margin, and how much extra volume you would have to sell to stand still.
- Live formulas
- 70
- Columns
- 10
- File
- 9 KB
$5
Checkout opening soonOr get it with all 66 paid templates in the complete pack — $45.
Works in Excel 2013 and later, Microsoft 365, Google Sheets and LibreOffice Calc. Set up to print landscape on one page wide.
About this Discount & Margin Calculator template
A 10% discount sounds small until you see what it does to profit. This Excel discount and margin calculator takes each product's cost, list price and discount, and shows the sale price, profit per unit, new margin and margin points lost.
The key column is the extra volume you would need to sell just to earn the same profit after the discount — usually far more than people expect.
Good for
- →Deciding whether a promotion is worth running
- →Setting limits on how much sales staff can discount
- →Seeing which products can take a discount and which cannot
What it works out for you
What discounting costs you
- →Average list margin
- →Average margin after discount
- →Margin points given away
- →Profit per unit at list
- →Profit per unit after discount
- →Worst extra volume needed
- →Average extra volume needed
- →Lines discounted 15% or more
- →Lines now below 40% margin
- →Lines selling at a loss
What you fill in
The table has 10 columns. Calculated columns fill themselves in; the rest are yours to type over.
- →Product
- →Unit cost
- →List price
- →List margin
- →Discount
- →Sale price
- →Profit per unit
- →New margin
- →Margin lost
- →Extra volume needed
How to use it
The last column is the one that matters: the extra volume you need just to make the same profit after the discount. It is usually far more than people expect.
The file opens with sample rows so you can see every formula working before you change anything. Type your own figures over them. When you need more rows, insert them inside the existing block rather than underneath it, so the totals keep covering every row.
Functions it uses
Every one of these exists in Excel, Google Sheets and LibreOffice, so the file behaves the same wherever you open it.
AVERAGECOUNTIFIFERRORMAXQuestions about the Discount & Margin Calculator
How is 'extra volume needed' calculated?
Profit per unit at list price divided by profit per unit after the discount, minus one. It is how many more units, as a percentage, you must sell to earn the same total profit.
Why does a small discount need so much extra volume?
The discount comes straight off profit, not off the whole price. On a thin margin, a small price cut removes a large share of the profit on every sale.
What if a discount pushes a product below cost?
Profit per unit goes negative and the summary counts it under lines selling at a loss.
