Break-Even Analysis Calculator Excel Template

How-to basics · Updated November 21, 2026

A break-even analysis calculator in Excel shows how many units you must sell—or how much sales revenue you need—to cover costs without making a profit or loss. Build the template with fixed costs, selling price, and variable cost per unit; the core calculation is fixed costs divided by contribution per unit (selling price minus variable cost).

What a break-even analysis measures

Break-even is the point at which total revenue equals total costs. Below that point, the activity has an operating loss under the assumptions in the model; above it, it generates a profit. The calculation is useful when pricing a product, assessing a sales target, comparing a new service with an existing one, or estimating the volume needed to cover a planned expense.

A basic analysis distinguishes two kinds of costs:

  • Fixed costs: Costs that do not change with the number of units sold within the period and operating range being analyzed, such as a monthly lease or a fixed software subscription.
  • Variable costs: Costs that rise or fall with each unit, such as materials, packaging, or a per-unit production charge.

These labels depend on the situation and time frame. A cost that is fixed for one month may change over a year, and some costs are mixed: they include a fixed base plus a variable component. A useful calculator makes its period and cost assumptions visible instead of treating every expense as permanently fixed or variable.

Excel template layout

In a blank Microsoft Excel for Microsoft 365 workbook on Windows, enter these labels in column A and values or formulas in column B. Use one consistent period—for example, one month—for fixed costs and the sales plan.

  • A2: Fixed costs; B2: enter the total fixed costs.
  • A3: Selling price per unit; B3: enter the price.
  • A4: Variable cost per unit; B4: enter the cost for one unit.
  • A5: Contribution per unit; B5: calculate this value.
  • A6: Break-even units; B6: calculate the whole-unit target.
  • A7: Sales at whole-unit break-even; B7: calculate the revenue at that target.
  • A8: Planned units; B8: enter a sales estimate.
  • A9: Estimated profit; B9: calculate the result at the planned volume.
  • A10: Exact break-even sales; B10: calculate the revenue threshold before rounding units.

Format the input and result cells as currency where appropriate. On Windows, select the relevant cells and use Home > Number group > Currency; you can also use the Number Format dropdown in that group. Keep unit counts as numbers rather than currency. Add a note beside the inputs to state whether costs and prices are monthly, annual, or for another period.

Formulas to calculate the break-even point

Contribution per unit is the amount each sale leaves to pay fixed costs after its variable cost is covered. With fixed costs in B2, selling price per unit in B3, and variable cost per unit in B4, put this formula in B5: =B3-B4. If the price is $50 and variable cost is $30, contribution is $20 per unit.

With fixed costs in B2 and contribution per unit in B5, put this formula in B6 to round the required volume up to a whole unit: =IF(B5<=0,"Not achievable",ROUNDUP(B2/B5,0)). ROUNDUP is appropriate when units cannot be fractional: a requirement of 600.1 units means at least 601 whole units. The IF check prevents a misleading result when contribution is zero or negative. For a viable example with $12,000 fixed costs and $20 contribution per unit, the formula returns 600 units.

With the whole-unit break-even quantity in B6 and selling price per unit in B3, put this formula in B7: =B6*B3. In the example, 600 units at $50 each produce $30,000 in sales. This is the revenue at the rounded unit target.

With fixed costs in B2, selling price in B3, and variable cost in B4, put this formula in B10 for the exact revenue threshold: =IF(OR(B3<=0,B4>=B3),"Not achievable",B2/(1-B4/B3)). At the example values, it returns $30,000. In other cases, B10 may differ from B7 because B6 rounds units up; the exact revenue threshold can correspond to a fraction of a unit, which cannot be sold as a whole item.

With planned units entered in B8, contribution per unit in B5, and fixed costs in B2, put this formula in B9: =B8*B5-B2. At 750 planned units, $20 contribution per unit, and $12,000 fixed costs, estimated profit is $3,000. This is a simplified operating estimate, not necessarily accounting net income or cash flow.

Worked example and how to read it

Suppose a small business sells a product for $50 per unit. Materials and other variable costs total $30 per unit, and fixed costs for the period are $12,000. Contribution is $20. The business must sell 600 units to break even because 600 multiplied by $20 contributes exactly $12,000 toward fixed costs.

At 600 units, revenue is $30,000 and variable costs are $18,000. Adding fixed costs of $12,000 gives total costs of $30,000, matching revenue. At 750 units, revenue is $37,500, variable costs are $22,500, and total costs are $34,500, leaving $3,000 before any costs excluded from the model.

The contribution calculation explains why revenue alone is not enough to assess the target. A business with a $50 price does not keep the full $50 to cover fixed costs: it keeps $20 after the assumed variable cost. If variable cost rises to $35 while price and fixed costs stay the same, contribution falls to $15, and break-even volume becomes 800 units ($12,000 divided by $15). Update the inputs rather than adjusting the final answer manually.

Build and check the workbook

  1. Enter labels and input values in the cells listed above. Use nonnegative fixed costs and a positive selling price, and make sure all figures use the same currency and period.
  2. Enter the contribution formula in B5, then the break-even, sales, and profit formulas in their designated cells. Excel recalculates the dependent results when an input changes.
  3. Distinguish inputs from formulas with a simple fill color or border, and label the units clearly. Avoid typing over a formula when changing a scenario.
  4. Test the model with easy values. For example, if fixed costs are $1,000 and contribution is $10 per unit, break-even should be 100 units. Confirm that revenue less variable and fixed costs equals zero at that quantity.
  5. Try a second scenario by changing price, variable cost, or fixed costs. Check that a higher fixed cost raises the target and a higher contribution per unit lowers it, all else equal.

Excel for Mac has the same basic cell formulas and function names; the ribbon layout can differ slightly by release. Excel for the web also supports these formulas, though its menus and editing experience may look different. No add-in, macro, or special template file is required for this calculator.

Assumptions and limitations

The standard formula assumes a constant selling price and variable cost per unit, and that fixed costs remain fixed over the volume analyzed. It also assumes the units counted are comparable. If a company sells several products with different margins, one single-unit break-even calculation is not enough unless the model uses a justified sales mix. A shift toward lower-margin products can increase the overall break-even target even if total unit sales stay the same.

Capacity can also limit the usefulness of the answer. A calculation might say 4,000 units are needed, while available production capacity is only 3,000. That result is a warning to examine pricing, costs, capacity, or the plan—not a guarantee that the target can be reached. Discounts, returns, commissions, shipping, spoilage, taxes, and step costs may also change the actual result if they are omitted or modeled incorrectly.

Break-even is not a forecast. It does not tell you how likely customers are to buy, when payments arrive, whether the business has enough cash to pay bills, or whether the chosen period is representative. Treat it as a scenario tool and revisit inputs when supplier prices, staffing, pricing, or expected sales mix changes. For a consequential decision, validate the assumptions with appropriate accounting or business expertise.

Common mistakes to avoid

  • Putting total variable costs in the per-unit input: B4 must be variable cost for one unit. If $30 is the cost per unit, do not enter $30,000 of total variable costs there.
  • Mixing time periods: Monthly fixed costs divided by contribution for a whole year produces an inconsistent target. Align the period for costs and sales.
  • Ignoring nonpositive contribution: If variable cost equals or exceeds price, each additional sale does not contribute toward fixed costs. The model cannot produce a normal break-even sales volume in that case.
  • Rounding too early: Keep the underlying price and cost inputs at useful precision. Round the unit target up for a practical whole-unit goal, but understand that a rounded result can show a small positive profit rather than exactly zero.
  • Calling the result “profit” without qualification: The example’s profit formula includes only the modeled contribution and fixed costs. It excludes expenses not entered in the template, so label the result estimated operating profit under stated assumptions.

When to use a chart or another approach

A small input-and-output table is often the easiest calculator to audit. For a visual explanation, create a separate table of unit volumes, revenue, and total costs, then chart revenue and total costs against units. Their intersection represents break-even under the model. In Excel, select the table and use Insert > Recommended Charts to choose a suitable chart; inspect the axis labels so that units and currency are not confused.

For multiple products, a weighted-average contribution approach can be used only when the expected sales mix is reasonably supported and stated. For uncertain prices or costs, make several clearly labeled scenarios—such as low, expected, and high variable cost—rather than presenting one result as certain. A full budget or cash-flow forecast may be more appropriate when timing, inventory purchases, credit terms, or other cash movements matter. The simple template is valuable precisely because it is transparent, but it is not a replacement for those wider models.

Frequently Asked Questions

What is the break-even formula in Excel?
For whole units, divide fixed costs by selling price minus variable cost per unit, then round up. If fixed costs are in B2, price in B3, and variable cost in B4, the guarded formula is =IF(B3<=B4,"Not achievable",ROUNDUP(B2/(B3-B4),0)). It returns a unit count when price exceeds variable cost.

How do I calculate break-even sales in dollars?
Divide fixed costs by the contribution margin ratio: 1 minus variable cost per unit divided by selling price per unit. The B10 formula above applies this relationship and checks for a nonpositive contribution margin. Its answer is an exact revenue threshold under the model’s assumptions, not necessarily revenue at a whole number of units.

Can Excel calculate break-even for several products?
Yes, but you need more than one price and cost pair. Estimate contribution by product and use a defensible expected sales mix to calculate a weighted-average contribution per unit, or model the products and mix explicitly. If the mix changes, the break-even estimate changes too.

Why does my break-even formula return an error or an unusual number?
Check that the price is greater than variable cost, that the cells contain numbers rather than text, and that costs and sales refer to the same period. A zero contribution creates division by zero; a negative contribution can produce an economically meaningless result. The IF checks in the template help flag those conditions.

Is break-even the same as making a profit?
No. At break-even, the modeled revenue equals modeled costs and profit is zero. Selling above the break-even quantity can produce a positive result under the same assumptions, but omitted costs, changes in price or cost, and demand limits can alter the real outcome.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.