The profit margin formula in Excel is =(B2-C2)/B2: the price without VAT in B2, the cost in C2, the cell formatted as a percentage. A German Excel writes that one the same way, and writes =WENNFEHLER((B2-C2)/B2;0) where English Excel writes =IFERROR((B2-C2)/B2,0). Markup divides by the cost instead: =(B2-C2)/C2.
The profit margin formula in Excel is one cell: =(B2-C2)/B2, the price minus the cost, divided by the price, with the cell formatted as a percentage. Put the price without VAT in B2 and the cost of one unit in C2. For an online store three more formulas decide whether that margin is real: markup, which divides the same profit by the cost; the price that reaches a target margin; and the contribution per order, which also takes off the parcel and the payment fee. Below, each one written for an English Excel, a German Excel and Google Sheets, worked on one product, with a free sheet that has every row filled in.
In short
- Gross margin is =(B2-C2)/B2 and markup is =(B2-C2)/C2: the same euros over a different base.
- Take VAT out by dividing by 1 plus the rate before anything else, or every margin comes out several points too high.
- A German Excel names its functions in German and puts a semicolon between their arguments, because its decimal mark is the comma.
- Gross margin stops at the goods. The contribution per order also pays for the parcel and the payment fee, and it is the margin an order really leaves.
The formulas, in three spellings
The table uses the cells of the downloadable sheet below, one product down column B: the price with VAT in B2, the VAT rate in B3, the unit cost in B4, the parcel in B9, the payment provider's rate and fixed fee in B10 and B11, and a target margin in B15. Rows that use only signs read the same in every spreadsheet; the rows with a function are where the spellings part.
| Row | English Excel and Google Sheets | German Excel | Google Sheets, German locale |
|---|---|---|---|
| Net price (B5) | =B2/(1+B3) | =B2/(1+B3) | =B2/(1+B3) |
| Gross profit (B6) | =B5-B4 | =B5-B4 | =B5-B4 |
| Gross margin (B7) | =IFERROR(B6/B5,0) | =WENNFEHLER(B6/B5;0) | =IFERROR(B6/B5;0) |
| Markup (B8) | =IFERROR(B6/B4,0) | =WENNFEHLER(B6/B4;0) | =IFERROR(B6/B4;0) |
| Payment fee (B12) | =ROUND(B2*B10+B11,2) | =RUNDEN(B2*B10+B11;2) | =ROUND(B2*B10+B11;2) |
| Contribution per order (B13) | =B5-B4-B9-B12 | =B5-B4-B9-B12 | =B5-B4-B9-B12 |
| Contribution margin (B14) | =IFERROR(B13/B5,0) | =WENNFEHLER(B13/B5;0) | =IFERROR(B13/B5;0) |
| Price for that margin, incl. VAT (B16) | =ROUND(B4/(1-B15)*(1+B3),2) | =RUNDEN(B4/(1-B15)*(1+B3);2) | =ROUND(B4/(1-B15)*(1+B3);2) |
Worked example: one €64,90 product
A product sells for €64,90 with 19% VAT and costs €22,00 a unit. Its net price is €54,54, so the gross profit is €32,54: a gross margin of 59,7% and a markup of 147,9%. Shipped in a €6,40 parcel and paid with a fee of 1,5% plus €0,25, which comes to €1,22 on the price with VAT, one order of it contributes €24,92, a contribution margin of 45,7%. To reach a 65% gross margin on the same cost, the price with VAT would have to be €74,80.
| Row | Cell | Value |
|---|---|---|
| Price incl. VAT | B2 | €64,90 |
| VAT rate | B3 | 19% |
| Unit cost | B4 | €22,00 |
| Net price | B5 | €54,54 |
| Gross profit | B6 | €32,54 |
| Gross margin | B7 | 59,7% |
| Markup | B8 | 147,9% |
| Shipping and packing | B9 | €6,40 |
| Payment fee rate | B10 | 1,5% |
| Payment fee per payment | B11 | €0,25 |
| Payment fee | B12 | €1,22 |
| Contribution per order | B13 | €24,92 |
| Contribution margin | B14 | 45,7% |
| Target gross margin | B15 | 65% |
| Price for that margin, incl. VAT | B16 | €74,80 |
Why a German Excel writes them differently
A German Excel uses the comma as its decimal mark, so it cannot also use the comma to separate the arguments of a function. It uses a semicolon, and it names the functions in German: ROUND is RUNDEN and IFERROR is WENNFEHLER. Microsoft's ROUND page gives =ROUND(2.15, 1) as its example, and its German twin gives the same example as =RUNDEN(2,15;1) (both checked 15 Nov 2026). A decimal typed into a formula changes too: =B2/1.19 in English is =B2/1,19 in German. A formula pasted from an English page into a German Excel therefore fails at its first comma, which is the error most people meet first.
Google Sheets follows the spreadsheet's locale, set under File, Settings. Set to Germany, it reads the decimal comma and wants the semicolon between arguments, the way Google's German help for ROUND writes RUNDEN(99,44;1). Its function names stay English while Always use English function names is ticked in the same settings (checked 15 Nov 2026), so the third column keeps ROUND and IFERROR with semicolons.
Margin or markup: the mix-up that sets the wrong price
Margin divides the profit by the price, markup divides it by the cost, and a supplier quoting one while a price list uses the other is how a store loses points without noticing. A 30% markup on a €22,00 unit cost is a net price of €28,60, and its margin is only 23,1%. A 30% margin on the same cost needs a net price of €31,43. To convert in a cell: margin is markup divided by one plus the markup, =B8/(1+B8), and markup is margin divided by one minus the margin, =B7/(1-B7). The markup calculator does both directions without a spreadsheet.
Take VAT out by dividing
A price with 19% VAT holds the VAT on top of the net price, so the net price is the price divided by 1,19, not the price minus 19%. On the example, €64,90 divided that way is €54,54; taking 19% off gives €52,57, €1,97 too little. Leaving the VAT in is worse in the other direction: the margin on the price with VAT would read 66,1% instead of 59,7%, and a store comparing that against a supplier's net figures is comparing two different things.
Shopify's help page on cost per item gives the margin it shows on a product as the price minus the cost, divided by the price, times 100, from the cost you enter in the product's Price section (checked 15 Nov 2026). If your prices include VAT, take the VAT out first before you compare that figure with anything else.
From one product to the order, and to the month
Gross margin is the right first number and the wrong last one. It stops after the goods, so the example product shows 59,7%, while the order keeps 45,7% once the parcel and the payment fee are paid. That gap is gross margin vs contribution margin, and in a store that ships every order it is the gap that decides whether growth makes money. The contribution margin formula is the same subtraction carried on through every cost the order caused, ads included.
For a whole catalogue, one product per row, the SKU margin sheet sorts products by what they keep rather than by what they sell. For a whole month, the monthly P&L template takes the same arithmetic down all 34 lines, and the unit cost in B4 becomes the cost of goods sold: in its example month €22.880,00, 38,1% of net revenue. Without a spreadsheet at all, the profit margin calculator walks one product from its shelf price to its contribution.
The sheet
The margin formula sheet has every row of this post filled in for the €64,90 product: a column of values, then the formula of each worked-out row three times, for an English Excel, a German Excel and Google Sheets set to Germany. The formulas are written without their equals sign, so the file opens without running anything; type = in front of the one for your spreadsheet and the row is live. When one product becomes a catalogue and a catalogue becomes a month of orders, nouz works out the margin of every product, every order and every day from your own orders and costs, every night.