How to do a break-even analysis in Excel

Published Monday 11 May 2026

Table of contents

• What is break-even analysis?
• The break-even analysis formula
• How to calculate break-even in Excel using a formula
• How to calculate break-even in Excel using Goal Seek
• How to create a break-even chart in Excel
• Break-even analysis example
• How to use break-even analysis for your business
• Common break-even analysis mistakes to avoid
• Track your business finances with Xero
• FAQs on break-even analysis in Excel

Key takeaways

• A break-even analysis tells you exactly how many units you need to sell, or how much revenue you need to earn, before your business starts turning a profit. It's a straightforward calculation you can set up in any Excel spreadsheet.
• The core formula is: break-even point (units) = fixed costs / (selling price per unit - variable cost per unit). You can also calculate break-even in revenue terms by dividing fixed costs by your contribution margin ratio.
• Excel's Goal Seek feature and charting tools make it simple to run what-if scenarios and visualise your break-even point, so you can adjust pricing or costs and instantly see the impact.
• Regularly updating your break-even analysis helps you make confident decisions about pricing, new product launches, and managing costs as your business grows.

What is break-even analysis?

A break-even analysis is a financial calculation that shows you the point where your total revenue equals your total costs. At this point, your business is neither making a profit nor a loss.

The break-even point (BEP) is the specific number of units you need to sell, or the amount of revenue you need to generate, to cover all your costs. Once you pass this point, every additional sale contributes directly to profit. Learn more about the advantages of calculating a break-even point.

To understand break-even analysis, you need to know 3 key terms:

• Fixed costs: expenses that stay the same regardless of how many units you sell, for example rent, insurance, and salaries
• Variable costs: expenses that change based on the number of units you produce or sell, for example raw materials, packaging, and shipping
• Contribution margin: the amount left over from each sale after covering variable costs, calculated as selling price per unit minus variable cost per unit

The break-even analysis formula

Once you know your fixed costs, variable costs, and selling price, you can calculate your break-even point using a simple formula.

The break-even formula in units is:

Break-even point (units) = Fixed costs / (Selling price per unit - Variable cost per unit)

The bottom part of this formula, selling price minus variable cost, is your contribution margin per unit. It represents how much each sale contributes towards covering your fixed costs.

You can also calculate break-even in revenue terms. This is useful when you sell products at different price points or want to know the total sales value you need to reach.

Break-even point (revenue) = Fixed costs / Contribution margin ratio

The contribution margin ratio is your contribution margin per unit divided by your selling price per unit. For example, if your selling price is £10 and your variable cost is £4, your contribution margin is £6. Your contribution margin ratio is £6 / £10 = 0.6, or 60%. This ratio is closely related to your gross profit margin.

How to calculate break-even in Excel using a formula

Setting up a break-even analysis in Excel takes just a few minutes. Follow these steps to build your own spreadsheet from scratch.

1. Set up your spreadsheet

Open a new Excel workbook and create 2 columns. In column A, add your labels. In column B, you'll enter the corresponding values. This keeps your data organised and easy to update later.

2. Enter your fixed costs

In cell A1, type "Fixed costs" and in cell B1, enter your total fixed costs as a single number. Add up all your regular expenses that don't change with sales volume, for example rent, insurance, loan repayments, and staff salaries.

3. Enter your variable cost per unit

In cell A2, type "Variable cost per unit" and in cell B2, enter the cost to produce or deliver 1 unit. Include materials, packaging, direct labour, and any other costs that increase with each unit sold.

4. Enter your selling price per unit

In cell A3, type "Selling price per unit" and in cell B3, enter the price you charge for 1 unit. If your prices vary, use an average selling price across your product range.

5. Calculate the contribution margin

In cell A4, type "Contribution margin" and in cell B4, enter the formula =B3-B2. This gives you the amount each unit sale contributes towards covering your fixed costs.

6. Calculate the break-even point

In cell A5, type "Break-even point (units)" and in cell B5, enter the formula =B1/B4. This divides your total fixed costs by the contribution margin per unit. The result is the number of units you need to sell to break even. You can round this up using =ROUNDUP(B1/B4,0) since you can't sell a fraction of a unit.

How to calculate break-even in Excel using Goal Seek

Excel's Goal Seek feature is a powerful tool for break-even analysis. It lets you work backwards from a target profit of £0 to find the exact number of units you need to sell.

1. Set up a profit formula

Using the spreadsheet from the previous section, add a row for "Units sold" in cell A6 with a starting value (for example, 100) in B6. Then in cell A7, type "Profit" and in B7, enter the formula =(B3-B2)*B6-B1. This calculates your total profit based on the number of units sold.

2. Open Goal Seek

Click on the cell containing your profit formula (B7). Then go to the Data tab, click "What-If Analysis," and select "Goal Seek" from the dropdown menu.

3. Configure Goal Seek

In the Goal Seek dialogue box, set "Set cell" to B7 (your profit cell), set "To value" to 0 (because break-even means zero profit), and set "By changing cell" to B6 (your units sold cell). Click OK.

4. Review the result

Excel will automatically calculate the number of units needed to reach £0 profit. The value in B6 updates to show your break-even quantity. This approach is especially useful when you want to test different pricing or cost scenarios quickly.

How to create a break-even chart in Excel

A break-even chart gives you a visual picture of where your costs and revenue intersect. It's a useful way to present your analysis to business partners or investors.

1. Create a data range

Set up a new area in your spreadsheet with columns for units sold, total fixed costs, total costs, and total revenue. In the units sold column, list values from 0 up to a number well above your expected break-even point, for example in increments of 50 or 100.

2. Add your formulas

For each row, calculate total fixed costs (this stays the same for every row), total costs (fixed costs + variable cost per unit x units), and total revenue (selling price per unit x units). Copy these formulas down for each row in your data range.

3. Insert a chart

Select your full data range, including headers. Go to the Insert tab and choose a line chart. Excel will plot your total costs and total revenue as 2 lines. The point where the lines cross is your break-even point.

4. Format the chart

Add a chart title such as "Break-even analysis." Label the horizontal axis as "Units sold" and the vertical axis as "Amount (£)." Use different colours for the total cost and total revenue lines so they're easy to tell apart. You can also add a data label at the intersection point to highlight the exact break-even quantity.

Break-even analysis example

Here's a worked example using realistic numbers for a small coffee shop in the UK.

Imagine you're opening a coffee shop and your main product is a flat white priced at £3.50 per cup. Your monthly figures look like this:

• Fixed costs: £4,000 per month (rent £1,800, staff wages £1,500, insurance £200, utilities £300, and other overheads £200)
• Variable cost per cup: £1.10 (coffee beans, milk, cup, lid, and napkin)
• Selling price per cup: £3.50

First, calculate the contribution margin per cup:

£3.50 - £1.10 = £2.40

Next, apply the break-even formula:

Break-even point = £4,000 / £2.40 = 1,667 cups per month

That works out to roughly 56 cups per day (based on a 30-day month). If your coffee shop serves more than 56 flat whites a day, you're making a profit. Fewer than that, and you're operating at a loss.

In revenue terms, your break-even point is:

1,667 cups x £3.50 = £5,834.50 per month

You can plug these numbers into Excel using the steps above to test different scenarios. For example, what happens if you raise the price to £3.80? Your new contribution margin would be £2.70, and your break-even point drops to 1,482 cups per month, or about 50 per day.

How to use break-even analysis for your business

Knowing your break-even point gives you a solid foundation for making business decisions with confidence. Here are some practical ways to use it.

• Setting prices: your break-even analysis shows the minimum price you can charge and still cover costs. Use it to test different price points and see how each one affects your profitability.
• Managing costs: by separating fixed and variable costs, you can identify where to cut spending. Reducing either type of cost lowers your break-even point and helps you reach profitability sooner. Explore strategies to reduce your break-even point.
• Launching new products: before investing in a new product line, run a break-even analysis to estimate how many units you'd need to sell. This helps you decide whether the opportunity is worth pursuing.
• Pitching to investors: a clear break-even analysis shows potential investors or lenders that you understand your numbers. It demonstrates when your business is expected to become profitable. Back it up with a solid financial statement.
• Planning for growth: as your business scales, your cost structure changes. Updating your break-even analysis regularly helps you anticipate how growth affects your profitability.

Common break-even analysis mistakes to avoid

A break-even analysis is only as good as the data you put into it. Understanding the limitations of a break-even analysis helps you use it effectively. Watch out for these common errors that can throw off your results.

1. Forgetting to include all fixed costs: it's easy to overlook expenses like software subscriptions, loan interest, or professional fees. Make a comprehensive list of every fixed cost before running your calculation.
2. Underestimating variable costs: include all costs that change with production volume, not just the obvious ones. Delivery charges, transaction fees, and packaging all count as variable costs.
3. Assuming costs stay constant: both fixed and variable costs can change over time. Rent increases, supplier price changes, and seasonal fluctuations all affect your break-even point.
4. Using outdated numbers: your break-even analysis should reflect current costs and prices. Review and update it at least quarterly, or whenever your costs or pricing change significantly.
5. Ignoring multiple products: if you sell more than 1 product, a single break-even calculation won't give you the full picture. Calculate break-even for each product line or use a weighted average approach.
6. Confusing break-even with profitability targets: breaking even means zero profit. Your actual sales target should be higher than your break-even point to generate the profit margin your business needs.

Track your business finances with Xero

Break-even analysis is just 1 part of understanding your business finances. Keeping accurate records of your income, expenses, and costs makes it much easier to run these calculations and make informed decisions.

Xero's cloud accounting software helps you track your business finances in real time. You can monitor cash flow, categorise expenses, and pull reports that give you the numbers you need for break-even analysis and beyond. Get one month free.

FAQs on break-even analysis in Excel

Here are some frequently asked questions about break-even analysis in Excel.

What is a good break-even point for a small business?

There's no single "good" break-even point because it depends on your industry, cost structure, and business model. Generally, a lower break-even point is better because it means you need fewer sales to cover your costs and start earning a profit.

Can you do a break-even analysis in Excel without a template?

Yes. You only need 3 numbers to get started: your total fixed costs, variable cost per unit, and selling price per unit. Using the steps outlined above, you can build a break-even spreadsheet from scratch in a few minutes.

How often should you recalculate your break-even point?

Recalculate your break-even point whenever your costs or prices change. At a minimum, review it quarterly. If your business experiences seasonal fluctuations or frequent cost changes, monthly updates give you a more accurate picture.

How do you calculate break-even with multiple products?

The simplest approach is to calculate a weighted average contribution margin across all your products based on their sales mix. Divide your total fixed costs by this weighted average to find your overall break-even point in units. You can also run separate break-even analyses for each product line.

What is the difference between break-even in units and break-even in revenue?

Break-even in units tells you how many items you need to sell to cover your costs. Break-even in revenue tells you the total sales value you need to reach. Both use the same underlying data, but revenue-based break-even is more useful when you sell products at varying price points.