The margin formula: how to calculate margin, with the Excel formula
Margin = (price − cost) ÷ price. Multiply by 100 for a percentage. A product that sells for $50.00 and cost you $30.00 has a margin of ($50.00 − $30.00) ÷ $50.00 = 40%. In Excel or Google Sheets, with cost in A2 and price in B2, type =(B2-A2)/B2 and format the cell as a percentage. To find the price for a target margin, use =A2/(1-0.6) for 60%.
The margin formula
Gross profit = price − cost
Margin = (price − cost) ÷ price
Margin % = (price − cost) ÷ price × 100Shopify uses the same formula on the product page: “([price - cost] / price) × 100”, with the example of a $50 price and a $30 cost giving a 40% margin. Margin is always measured on the selling price, so it can never reach 100% while the product costs anything.
The Excel and Google Sheets formulas
Put the cost in column A and the price in column B, starting in row 2. Each formula below was checked in a spreadsheet; they work the same in Excel, Google Sheets and LibreOffice.
| What you want | Formula | Cost $30.00, price $50.00 |
|---|---|---|
| Gross profit | =B2-A2 | $20.00 |
| Margin | =(B2-A2)/B2 | 40.00% |
| Markup | =(B2-A2)/A2 | 66.67% |
| Price for a 60% margin | =A2/(1-0.6) | $75.00 |
| Margin from a markup in H2 | =H2/(1+H2) | 50% markup → 33.33% |
| Markup from a margin in J2 | =J2/(1-J2) | 40% margin → 66.67% |
| Cost | Price | Margin | Markup | Price for 60% |
|---|---|---|---|---|
| $16.00 | $40.00 | 60.00% | 150.00% | $40.00 |
| $10.80 | $25.00 | 56.80% | 131.48% | $27.00 |
| $12.50 | $20.00 | 37.50% | 60.00% | $31.25 |
Copy the formula down the column to fill every row. To avoid a #DIV/0! error on empty rows, use =IF(B2=0,"",(B2-A2)/B2).
Margin vs markup
Both describe the same profit; they divide it by a different number. Margin divides by the price, markup by the cost. A $20.00 profit on a $40.00 sale that cost $20.00 is a 50% margin and a 100% markup.
| Margin | Markup |
|---|---|
| 20% | 25% |
| 25% | 33.33% |
| 40% | 66.67% |
| 50% | 100% |
| 60% | 150% |
The price for a target margin
Price = cost ÷ (1 − target margin)
$30.00 ÷ (1 − 0.40) = $50.00 $10.80 ÷ (1 − 0.60) = $27.00A common mistake is to add the margin to the cost: $30.00 + 40% = $42.00 gives a margin of only 28.57%, because 40% of the cost is a markup, not a margin.
Gross margin vs net margin
- Gross margin stops at the cost of the goods: (net sales − cost of goods sold) ÷ net sales. Shopify's profit reports calculate gross margin from net sales, so discounts and refunds lower it.
- Net margin takes every expense off: payment fees, shipping you pay, ads, apps, rent and wages, then divides what is left by net sales.
- A healthy gross margin can still leave a thin net margin. To see what is left after payment fees on one product, use the Shopify profit margin calculator.
The cost that goes into the formula
The formula is only as right as the cost you put in it. For a store, that is what the unit really cost you: the supplier price plus its share of inbound freight and duty, averaged over your deliveries. A cost that is one delivery old, or leaves freight out, makes every margin look higher than it is. See Shopify's Cost per item, explained. True COGS keeps Shopify's Cost per item at the weighted average (or FIFO) cost of what you paid, so the margins Shopify shows use your real cost.
Related guides
Questions merchants ask
What is the formula for margin?
Margin = (price − cost) ÷ price. Multiply by 100 for a percentage. A $50.00 price and a $30.00 cost give a 40% margin.
What is the margin formula in Excel?
With cost in A2 and price in B2, type =(B2-A2)/B2 and format the cell as a percentage. The same formula works in Google Sheets.
How do I calculate the price for a target margin?
Divide the cost by one minus the margin: price = cost ÷ (1 − margin). In Excel, =A2/(1-0.6) gives the price for a 60% margin.
What is the difference between margin and markup?
Margin divides the profit by the price; markup divides it by the cost. A 50% margin is a 100% markup.
How do I convert markup to margin?
Margin = markup ÷ (1 + markup). A 50% markup is a 33.33% margin. The other way, markup = margin ÷ (1 − margin).
What is the difference between gross margin and net margin?
Gross margin takes only the cost of goods sold off sales; net margin takes off every expense, such as fees, shipping, ads and wages.
Why is my margin in Shopify higher than expected?
Usually because Cost per item is older than your last delivery or leaves out freight and duty. Shopify's margin is only as accurate as the cost recorded on each order.
Try True COGS free. True COGS is an inventory costing app for Shopify by Nexus Logic Systems. It records the price you paid on every delivery (a received purchase order or a transfer), with its share of freight and duty on paid plans, keeps every price with its date, and writes your moving weighted average cost, or FIFO cost on a paid plan, to Shopify's Cost per item. It is free for up to 25 variants with a cost on record; paid plans start at $29 a month or $290 a year.
Install from the App Store