How to Do Break-Even Analysis in Excel: Formula, Table and Chart

shape
shape
shape
shape
shape
shape
shape
shape
How to Do Break-Even Analysis in Excel: Formula, Table and Chart - featured image

The break-even point is the level of sales at which your revenue exactly covers your costs, so you make neither a profit nor a loss. Every sale beyond that point adds to your profit. Knowing it helps you set prices, decide whether a product is worth launching, set a sales target and see how safe your business is if sales fall. Excel is a good tool for break-even analysis, because a few formulas give the answer, and a table and a chart show how profit changes with the number of units sold. This guide explains the terms, shows the formula, builds a worked example that you can verify by hand, adds a target profit and a margin of safety, and shows how to chart the result.

Updated November 2026

In this guide

  1. The terms you need
  2. The break-even formula
  3. Example data
  4. Step 1: set up the inputs
  5. Step 2: contribution margin and break-even
  6. Step 3: build a profit table
  7. Step 4: chart the break-even point
  8. Add a target profit
  9. Margin of safety
  10. What if the price or cost changes?
  11. Use a data table for scenarios
  12. Break-even with several products
  13. Limits of the model
  14. Common mistakes
  15. Practice problems
  16. Quick recap

The terms you need

Fixed costs stay the same whatever you sell, such as rent, salaries and insurance. Variable costs change with the number of units, such as materials, packaging and delivery. The selling price is what a customer pays for one unit. The contribution margin is the selling price minus the variable cost per unit, and it is the amount each unit contributes toward the fixed costs and the profit. Break-even happens when the total contribution equals the fixed costs.

The break-even formula

Break-even in units equals fixed costs divided by the contribution margin per unit. In symbols, BE units = Fixed costs / (Price – Variable cost). Break-even in revenue equals the break-even units times the price, or fixed costs divided by the contribution margin ratio, which is the contribution margin divided by the price. If the answer is not a whole number, round up, because you cannot sell part of a unit and you need to reach at least the break-even level.

Example data

A business sells a product for 25 per unit. The variable cost per unit is 10, and the fixed costs are 6,000 per month. The contribution margin per unit is 25 minus 10, which is 15. The contribution margin ratio is 15 divided by 25, which is 60 percent. The break-even units are 6,000 divided by 15, which is 400 units. The break-even revenue is 400 times 25, which is 10,000. We will build this in Excel and check each number.

Step 1: set up the inputs

In column A type the labels and in column B the values. A1 is Selling price with 25 in B1. A2 is Variable cost per unit with 10 in B2. A3 is Fixed costs with 6000 in B3. Color these three cells as inputs. Keep all assumptions in cells of their own, so that you can change them and see the effect. Do not type the numbers inside the formulas, because it makes the model hard to check and to update.

Step 2: contribution margin and break-even

In B4 type =B1-B2 for the contribution margin, which gives 15. In B5 type =B3/B4 for the break-even units, which gives 400. To round up in case of a fraction, use =ROUNDUP(B3/B4,0). In B6 type =B5*B1 for the break-even revenue, which gives 10,000. In B7 type =B4/B1 for the contribution margin ratio, which gives 0.6, or 60%. You can check the revenue with =B3/B7, which is 6,000 divided by 0.6, or 10,000 again.

Step 3: build a profit table

A table shows the profit at different sales levels. In A10 type Units, in B10 Revenue, in C10 Total cost and in D10 Profit. In A11 to A15 type 0, 200, 400, 600 and 800. Revenue is =A11*$B$1. Total cost is =$B$3+A11*$B$2. Profit is =B11-C11.

UnitsRevenueTotal costProfit
006,000-6,000
2005,0008,000-3,000
40010,00010,0000
60015,00012,0003,000
80020,00014,0006,000

The profit is zero at 400 units. Each extra unit adds 15 to the profit.

Step 4: chart the break-even point

Select the Units, Revenue and Total cost columns and insert a scatter chart with straight lines, or a line chart. The revenue line starts at zero and rises steeply. The total cost line starts at 6,000, which is the fixed cost, and rises more slowly. The lines cross at 400 units, which is the break-even point. Below it, costs are above revenue, which is a loss. Above it, revenue is above costs, which is a profit. Add a title and axis titles, as in our line graph guide.

Add a target profit

Often the goal is a profit, not only break-even. The units needed equal the fixed costs plus the target profit, divided by the contribution margin. For a target of 3,000, the units are 6,000 plus 3,000, which is 9,000, divided by 15, which gives 600 units. In Excel use =(B3+B8)/B4, with the target profit in B8. The revenue needed is 600 times 25, which is 15,000, and it matches the table above. A target like this turns the analysis into a sales goal that a team can understand.

Margin of safety

The margin of safety shows how far sales can fall before you start to lose money. If you expect to sell 550 units, the margin of safety in units is 550 minus 400, which is 150. As a percentage of the expected sales, it is 150 divided by 550, which is 27.3 percent. In Excel, use =(B9-B5)/B9 with the expected units in B9. A high margin of safety means the business is more secure. A low one means that a small fall in sales could cause a loss, so you may need to cut costs or raise prices.

What if the price or cost changes?

The model lets you test changes. If you raise the price to 30, the contribution margin becomes 20, and the break-even falls to 6,000 divided by 20, which is 300 units. If the variable cost rises to 12 with the price at 25, the contribution margin falls to 13, and the break-even rises to 6,000 divided by 13, which is 461.5, so 462 units. If the fixed costs fall to 4,500, the break-even is 4,500 divided by 15, which is 300 units. Try several scenarios and compare them in a small table.

Use a data table for scenarios

Excel can recalculate the break-even for many prices at once. Put a list of prices, such as 20, 25, 30 and 35, in a column, and the formula for the break-even in the cell above the next column. Select the range and go to Data, What-If Analysis, Data Table. Enter the price input cell as the column input cell. Excel fills in the break-even units for each price. At 20 the break-even is 600, at 25 it is 400, at 30 it is 300 and at 35 it is 240. The table makes the effect of pricing easy to see.

Break-even with several products

When a business sells more than one product, you need a mix. Calculate the weighted average contribution margin per unit, based on the share of each product in the sales. If product A has a margin of 15 and makes up 60 percent of sales, and product B has a margin of 30 and makes up 40 percent, the average margin is 15 times 0.6 plus 30 times 0.4, which is 21. The break-even units for fixed costs of 6,000 are 6,000 divided by 21, which is about 286 units in total. Use SUMPRODUCT, as in our SUMPRODUCT guide, for the weighted margin.

Limits of the model

Break-even analysis makes simple assumptions. It assumes that the price and the variable cost per unit stay constant, that fixed costs stay fixed at every volume and that everything you make is sold. In real life, discounts, volume prices and new staff change these numbers. Use the model as a guide, not as a prediction, and update it often. For the profit side of the picture, see our profit margin guide. Always check the inputs with real data from your accounts.

Common mistakes

The most common mistake is to treat all costs as fixed or all as variable. Another is to forget a cost, such as delivery or card fees. A third is to use revenue per unit that includes tax. A fourth is to forget to round up the units. A fifth is to divide by the price instead of the contribution margin. A sixth is to ignore time, since fixed costs are per month or per year, and the break-even must match the same period. Label the period in the sheet.

Practice problems

Try these. Problem 1: price 40, variable cost 15 and fixed costs 5,000 give a contribution margin of 25 and a break-even of 200 units. Problem 2: the break-even revenue is 200 times 40, which is 8,000. Problem 3: for a target profit of 2,500, the units are 7,500 divided by 25, which is 300. Problem 4: with expected sales of 250 units, the margin of safety is 50 units, or 20 percent. Problem 5: build the model in Excel and chart it.

Quick recap

Break-even units equal fixed costs divided by the contribution margin per unit, which is the price minus the variable cost. Keep the inputs in separate cells, round the units up, calculate the revenue and the ratio, and build a table and a chart to show the profit at different volumes. Add a target profit, the margin of safety and a few scenarios for price and cost changes. Use the results to set sales targets, but remember the assumptions behind the model and keep it up to date.

Frequently asked questions

What is the break-even formula?

Fixed costs divided by the contribution margin per unit.

What is the contribution margin?

The selling price minus the variable cost per unit.

How do I calculate break-even revenue?

Multiply the break-even units by the price, or divide fixed costs by the contribution margin ratio.

What is the margin of safety?

The amount by which sales can fall before the business makes a loss.

How do I add a target profit?

Add it to the fixed costs before dividing by the contribution margin.

Related guides and free tools

Leave a Reply

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