How to Calculate Profit Margin in Excel: Gross, Net and Markup Formulas

shape
shape
shape
shape
shape
shape
shape
shape
How to Calculate Profit Margin in Excel: Gross, Net and Markup Formulas - featured image

Profit margin tells you how much of each sale you keep as profit. It is one of the most important numbers in any business, because a high sales figure means little if the costs eat up most of it. In Excel, you can calculate the gross profit margin, the net profit margin and the markup with simple formulas, and you can work backward from a target margin to a selling price. This guide explains the definitions, shows formulas with a worked example that you can check by hand, explains the difference between margin and markup, shows how to build a product table, and lists the common mistakes. The examples use made-up numbers, and this is general information, not financial advice.

Updated November 2026

In this guide

  1. The key definitions
  2. Example data
  3. Step 1: set up the sheet
  4. Step 2: gross profit and gross margin
  5. Step 3: net profit and net margin
  6. Step 4: calculate markup
  7. Margin versus markup table
  8. Find the selling price from a target margin
  9. Find the selling price from a markup
  10. Build a product table
  11. Margin with discounts and taxes
  12. Break-even sales
  13. Common mistakes
  14. Practice problems
  15. Quick recap

The key definitions

Revenue is the money you receive from sales. Cost of goods sold, or COGS, is the direct cost of making or buying what you sold. Gross profit is revenue minus COGS. Gross margin is gross profit divided by revenue, shown as a percentage. Net profit is what remains after all expenses, such as rent, wages, marketing and taxes. Net margin is net profit divided by revenue. Markup is the profit divided by the cost, not by the revenue. Margin and markup are often mixed up, but they give different percentages for the same sale.

Example data

A shop had revenue of 5,000 last month. The cost of goods sold was 3,000. Other expenses, such as rent and wages, were 1,200. By hand, the gross profit is 5,000 minus 3,000, which is 2,000. The gross margin is 2,000 divided by 5,000, which is 40 percent. The net profit is 2,000 minus 1,200, which is 800, and the net margin is 800 divided by 5,000, which is 16 percent. We will build these numbers in Excel.

Step 1: set up the sheet

Put the labels in column A and the numbers in column B. In A1 type Revenue, in A2 COGS, in A3 Gross profit, in A4 Gross margin, in A5 Expenses, in A6 Net profit and in A7 Net margin. Type 5000 in B1, 3000 in B2 and 1200 in B5. Keeping the inputs in separate cells lets you change them and see the results update at once. Use a clear format for money and percent, and color the input cells so that others know which cells to change.

Step 2: gross profit and gross margin

In B3 type =B1-B2, which gives 2,000. In B4 type =B3/B1, which gives 0.4. Format B4 as a percentage to show 40%. A gross margin of 40 percent means that for every 100 of sales, you keep 40 after paying for the goods. You can also write the margin in one formula, =(B1-B2)/B1. Use whichever version is easier to read for the people who will check your work.

Step 3: net profit and net margin

In B6 type =B3-B5, which gives 800. In B7 type =B6/B1, which gives 0.16, or 16%. The net margin is lower than the gross margin, because the other expenses are taken off. If the net profit is negative, the business made a loss, and the margin is negative too. Track both margins over time. A falling gross margin points to rising costs or discounts, and a falling net margin with a steady gross margin points to rising expenses.

Step 4: calculate markup

Markup is profit divided by cost. In our example, the markup is 2,000 divided by 3,000, which is 0.6667, or 66.67 percent. In Excel, use =B3/B2. The markup is higher than the margin of 40 percent, because it is divided by the smaller number, which is the cost. A markup of 66.67 percent means that the selling price is 1.6667 times the cost, since 3,000 times 1.6667 is 5,000. Retailers often set prices with a markup, while financial reports use the margin.

Margin versus markup table

The same profit gives different percentages, so convert carefully.

MarkupMarginCost 100 sells at
25%20.0%125
50%33.3%150
66.67%40.0%166.67
100%50.0%200

The conversion formulas are margin equals markup divided by one plus markup, and markup equals margin divided by one minus margin. For example, a markup of 50 percent gives a margin of 0.5 divided by 1.5, which is 33.3 percent.

Find the selling price from a target margin

If you know the cost and the margin you want, the price is the cost divided by one minus the margin. For a cost of 60 and a target margin of 40 percent, the price is 60 divided by 0.6, which is 100. In Excel, with the cost in B1 and the margin in B2, use =B1/(1-B2). Check: the profit is 100 minus 60, which is 40, and 40 divided by 100 is 40 percent. A common error is to multiply the cost by 1.4, which gives a price of 84 and a margin of only 28.6 percent.

Find the selling price from a markup

If you use a markup, the price is the cost times one plus the markup. For a cost of 60 and a markup of 50 percent, the price is 60 times 1.5, which is 90. In Excel, use =B1*(1+B3), with the markup in B3. The margin of this price is 30 divided by 90, which is 33.3 percent. Decide whether your business thinks in margins or markups, and use one method consistently in the price list, so the numbers are not confused.

Build a product table

Make a table with the columns Product, Cost, Price, Profit, Margin and Markup. In the Profit column use =C2-B2, in Margin =D2/C2 and in Markup =D2/B2. For a product with a cost of 60 and a price of 100, the profit is 40, the margin is 40 percent and the markup is 66.67 percent. For a product with a cost of 20 and a price of 30, the profit is 10, the margin is 33.3 percent and the markup is 50 percent. Sort by margin to find the best and the weakest products. See our table guide for building the table.

Margin with discounts and taxes

A discount reduces the price, and therefore the margin, more than people expect. If a product costs 60 and sells for 100, a 10 percent discount gives a price of 90 and a profit of 30. The margin falls from 40 percent to 33.3 percent. Sales tax is not part of your revenue, because you collect it for the tax authority, so calculate the margin on the price before tax. Our pro forma invoice guide shows how to separate the tax from the price in a document.

Break-even sales

The break-even point is the sales level where profit is zero. If your fixed expenses are 1,200 a month and your gross margin is 40 percent, the break-even revenue is 1,200 divided by 0.4, which is 3,000. Below that level, you lose money, and above it you make a profit. In Excel use =B5/B4, with the expenses in B5 and the gross margin in B4. At the revenue of 5,000 in our example, the profit of 800 is 5,000 minus 3,000 of break-even, times 0.4. Use the break-even to set a monthly sales target.

Common mistakes

The most common mistake is to confuse margin and markup, which leads to prices that are too low. Another is to divide the profit by the cost when you want the margin. A third is to forget that discounts cut the margin. A fourth is to include sales tax in the revenue. A fifth is to ignore expenses, and look only at the gross margin. A sixth is to mix up the units, such as percent and decimal. Always label the percent cells, and check one product by hand.

Practice problems

Try these. Problem 1: revenue of 800 and COGS of 500 give a gross profit of 300 and a margin of 37.5 percent. Problem 2: the markup on the same sale is 300 divided by 500, which is 60 percent. Problem 3: the price for a cost of 45 and a 25 percent margin is 60. Problem 4: the price for a cost of 45 and a 25 percent markup is 56.25. Problem 5: the break-even revenue for expenses of 900 and a margin of 30 percent is 3,000.

Quick recap

Gross profit is revenue minus cost, and gross margin is gross profit divided by revenue. Net margin uses the profit after all expenses. Markup divides the profit by the cost, so it is always higher than the margin. To find a price from a margin, divide the cost by one minus the margin, and from a markup, multiply the cost by one plus the markup. Build a product table, watch the effect of discounts and use the break-even to set targets. Check one example by hand to be sure.

Frequently asked questions

What is the formula for profit margin?

Profit divided by revenue, shown as a percentage.

What is the difference between margin and markup?

Margin divides profit by revenue, and markup divides profit by cost.

How do I find the price for a target margin?

Divide the cost by one minus the margin.

What is a good profit margin?

It depends on the industry, so compare with similar businesses.

How do I calculate gross margin in Excel?

=(Revenue-COGS)/Revenue formatted as a percentage.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *