The basic break-even formula in Excel is fixed costs divided by the contribution margin per unit: =B2/(B3-B4). If B2 contains fixed costs, B3 the selling price per unit, and B4 the variable cost per unit, the result is the number of units that must be sold for revenue to equal total costs and profit to be zero.
What break-even means
A break-even point is the sales level at which a business has covered its costs but has not yet made a profit or a loss. Below that point, revenue is less than total costs; above it, revenue exceeds total costs, assuming the same prices and cost structure continue to apply.
The formula uses contribution margin: the amount from each sale left after paying that unit’s variable costs. That remaining amount contributes toward fixed costs. Once total contribution has covered fixed costs, additional contribution increases profit. For example, if a product sells for $25 and costs $10 in variable expenses to make and sell, each unit contributes $15 toward fixed costs.
Set up the Excel inputs
In Excel for Microsoft 365 on Windows, enter labels in column A and values in column B. Keep the amounts in the same currency and use per-unit figures for both price and variable cost.
- A2: Fixed costs; B2: 30000
- A3: Selling price per unit; B3: 25
- A4: Variable cost per unit; B4: 10
In this example, fixed costs are $30,000, the selling price is $25 per unit, and variable cost is $10 per unit. Fixed costs might include rent, insurance, or salaried expenses that do not change directly with the number of units sold during the period. Variable costs could include materials, packaging, or a per-unit sales fee. The categories depend on the business and the period being analyzed.
Calculate break-even units
With fixed costs in B2, selling price per unit in B3, and variable cost per unit in B4, enter this formula in another cell to calculate the break-even quantity: =B2/(B3-B4). The result is 2,000 units because $30,000 divided by ($25 minus $10) equals 2,000.
You can also calculate the contribution margin per unit in its own cell. With the selling price in B3 and variable cost per unit in B4, enter =B3-B4 in B5. It returns $15. Then, with fixed costs in B2 and contribution margin per unit in B5, enter =B2/B5 in B6. That also returns 2,000 units. Keeping the margin visible can make the model easier to check and update.
Format the input and result cells as currency or numbers as appropriate. The break-even quantity is a number of units, not a dollar amount. If Excel displays a decimal, it means the mathematical threshold falls between whole units; the practical sales target may need to be rounded up.
Find break-even sales revenue
Some questions ask for the dollar sales needed to break even rather than the number of units. With fixed costs in B2, selling price per unit in B3, and variable cost per unit in B4, use =B2/(1-B4/B3). This calculates the break-even revenue using the contribution margin ratio. In the example, the result is $50,000: 2,000 units at $25 each.
Another way to get the same result is to multiply the break-even units by the selling price. With break-even units in B6 and selling price per unit in B3, enter =B6*B3. The result is $50,000. This second approach is straightforward when the unit calculation is already in the worksheet. The ratio-based version is useful when the desired output is revenue and the selling price and variable cost are entered as unit amounts.
Round up to a sellable quantity
A business generally cannot sell a fraction of a physical unit, so a decimal break-even result needs careful interpretation. With fixed costs in B2, selling price per unit in B3, and variable cost per unit in B4, use =ROUNDUP(B2/(B3-B4),0) to return the next whole unit when the calculation is fractional. For the example, it returns 2,000 because the unrounded result is already a whole number.
Rounding up is different from ordinary rounding: it always moves a positive fractional result to the next integer. That makes it appropriate for a minimum whole-unit target, but it does not change the underlying economics. If the unrounded result is 2,000.2 units, selling 2,001 units reaches or passes the calculated threshold under the model’s assumptions. Consider displaying both the exact result and the rounded target if precision matters.
Calculate profit or a target profit
Break-even is the point where profit equals zero, but the same inputs can estimate profit at a chosen quantity. With units sold in A2, fixed costs in B2, selling price per unit in B3, and variable cost per unit in B4, enter =A2*(B3-B4)-B2. If A2 is 1,999, the result is -$15; at 2,000 units it is $0; at 2,001 units it is $15. The checks follow from each unit contributing $15.
You can also solve for the units required to reach a profit target. With fixed costs in B2, target profit in B5, selling price per unit in B3, and variable cost per unit in B4, use =(B2+B5)/(B3-B4). If B5 is $6,000, this example requires 2,400 units: ($30,000 + $6,000) divided by $15. Round the result up if only whole units can be sold.
Check assumptions and cost categories
A break-even result is only as useful as the inputs and assumptions behind it. The simple formula assumes a single product, a stable selling price, a stable variable cost per unit, and a fixed-cost total that applies to the period being modeled. It also assumes the units counted are sold during that period. It does not automatically account for taxes, financing, inventory timing, or changes in costs and prices.
- Match the period: compare monthly fixed costs with monthly sales, or annual fixed costs with annual sales.
- Use variable costs per unit, not total variable expenses for the whole business. If you have total variable expenses, calculate a reliable per-unit amount for the same period and volume.
- Include relevant costs consistently. A cost treated as variable should not also be included in fixed costs.
- For a service, define the unit clearly, such as an hour, appointment, or project, and use a price and variable cost for that same unit.
If the selling price equals variable cost, the contribution margin is zero and fixed costs cannot be covered by selling more units under these assumptions. If variable cost is higher than the selling price, each additional sale increases the loss. In either case, the basic formula gives a zero or negative denominator, so there is no meaningful positive break-even quantity. Check the inputs rather than treating that result as a sales target.
Multiple products and changing costs
For several products, there is not necessarily one useful break-even unit count because products can have different prices and contribution margins. A combined estimate depends on the expected sales mix: the proportion of total sales represented by each product. If customers buy a different mix than expected, the weighted average contribution margin and the break-even point can change. A single-product formula should not be applied to total units across different products unless those units have comparable economics.
Costs that change in steps also need judgment. For example, selling more may require another shift, vehicle, or storage space, creating a new fixed-cost level. A single fixed-cost input cannot represent every volume range if costs jump at a threshold. In that situation, model the relevant ranges separately or build a scenario table, and make clear which cost assumptions apply at each sales level.
Use Goal Seek or a chart to explore the result
If you already have a profit formula and want Excel to find the quantity that makes profit zero, use Data > What-If Analysis > Goal Seek. Set the cell containing the profit formula to 0 by changing the cell containing units sold. Goal Seek adjusts one input to reach a target; it does not replace checking whether the result is realistic or whether whole units are required. For this simple model, direct division is usually easier to audit.
A chart can help show why break-even occurs. Create a quantity column and calculate revenue and total cost for each quantity. With quantity in A2, selling price per unit in B3, fixed costs in B2, and variable cost per unit in B4, revenue is =A2*B3 and total cost is =$B$2+A2*$B$4. Fill the formulas down for several quantities, then select the quantity and result columns and use Insert > Insert Line or Area Chart to make a line chart. The lines meet at the break-even quantity in this example. Use absolute references for the fixed inputs so they do not shift when you copy formulas down.
Common formula mistakes
A frequent error is dividing fixed costs by the selling price alone. That ignores variable costs and makes the estimated break-even point too low. The denominator must be contribution per unit: selling price minus variable cost. Another mistake is entering a total variable-cost amount where the formula expects a per-unit figure; the resulting margin will not represent what each sale contributes.
Also check that all monetary amounts use the same currency and that the chosen costs cover the same time period. A formula can calculate correctly while answering the wrong business question because an input is missing or classified incorrectly. For an illustrative planning model, the arithmetic is useful, but actual cost definitions and commercial terms should be checked with the relevant records or a qualified professional. This is general spreadsheet information, not financial, tax, investment, or legal advice.
Frequently Asked Questions
What is the simplest break-even formula in Excel?
Divide fixed costs by selling price per unit minus variable cost per unit. With fixed costs in B2, price in B3, and variable cost in B4, use =B2/(B3-B4). The result is units, not revenue.
How do I calculate break-even sales in dollars?
Divide fixed costs by the contribution margin ratio. With fixed costs in B2, price per unit in B3, and variable cost per unit in B4, use =B2/(1-B4/B3). In the worked example, break-even revenue is $50,000.
Should I round the break-even result up?
If you need a whole-unit sales target and the result has a fraction, round up to the next unit. Use ROUNDUP with zero decimal places. Keep in mind that the rounded target is a practical quantity, while the unrounded result is the mathematical threshold.
Why does my break-even formula return an error?
Check for a blank or text input, and check whether selling price is equal to or below variable cost. When the contribution margin is zero or negative, the formula cannot produce a meaningful positive break-even quantity. Confirm that the cells contain numeric values and the correct per-unit amounts.
Can I use the same formula for several products?
Not reliably by simply adding all units together when products have different prices or margins. A combined estimate needs an assumed sales mix and an appropriate weighted contribution margin. If the mix changes, the combined break-even point can change too.
Related
- Excel Data Validation for Dates: Rules and Examples
- Data Validation Checks in Excel: Rules and Examples
- Data Validation Checklist for Excel: What to Check
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- What-If Analysis in Excel: A Step-by-Step GuideTo add what-if analysis in Excel, open the Data tab and choose Forecast > What-If Analysis, then select…
- VLOOKUP and HLOOKUP in Excel: Formulas and UsesIn Excel, VLOOKUP searches down the first column of a table and returns a value from a column…
- Excel Time Card Calculator: Formulas, Setup, and TipsYes. In Excel for Microsoft 365 on Windows, a basic time card calculator can subtract clock-in from clock-out,…
- Excel Reverse HLOOKUP: Formulas and Better AlternativesExcel has no separate “reverse HLOOKUP” function. If you mean looking up a value in one row and…
- Concatenate Excel Formula With Comma: ExamplesTo concatenate two cells with a comma and a space between them, use =A2&", "&B2. For example, if…
- Excel Extract Text Between Quotes with FormulasIf the text is in A2, use =IFERROR(TEXTBEFORE(TEXTAFTER(A2,""""),""""),"") in Microsoft 365 or Excel 2024 to return the text…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.