Break-Even Analysis in Excel — Free Template + Formula Guide (2026)

“How many do we need to sell before we stop losing money?” is the most important question in any business plan, pricing decision, or product launch — and Excel answers it with one formula. In 40 minutes you’ll build a complete break-even model: fixed versus variable costs, the contribution margin, break-even units and revenue, a units-to-profit data table, the classic two-line break-even chart, the margin of safety, and reverse what-ifs with Goal Seek. Works in Excel 2010 through Microsoft 365.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is Break-Even Analysis?

Break-even analysis finds the exact sales volume at which a business, product, or project stops losing money: the point where total revenue equals total costs and profit is precisely zero. Below that volume every month ends in loss; above it, every additional sale is profit. It is the oldest tool in management accounting — formally, cost-volume-profit (CVP) analysis — and it remains the first model any investor, lender, or sensible founder asks for, because it converts a fuzzy hope (“we’ll sell loads”) into a concrete, testable threshold (“we need 667 units a month”).

The model rests on one structural insight: costs come in two species. Fixed costs — rent, salaries, insurance, software — must be paid whether you sell ten thousand units or none. Variable costs — materials, shipping, transaction fees — are incurred once per unit sold. Because fixed costs are a lump and variable costs are a slope, total cost is a straight line that starts above zero; revenue is a straight line that starts at zero but climbs faster (assuming you price above variable cost). Two straight lines with different slopes must cross exactly once — and that crossing is the break-even point. The entire analysis is the geometry of those two lines.

The hero of the calculation is the contribution margin: price minus variable cost per unit. Each sale “contributes” that amount — first toward paying down the fixed-cost lump, then, once the lump is covered, straight into profit. Divide fixed costs by the contribution per unit and you get the number of contributions needed: break-even units. Everything else in CVP — break-even revenue, target-profit volumes, margin of safety, operating leverage — is a rearrangement of that one division.

What makes break-even analysis so practically useful is not the single number but the sensitivity around it. The model has only three inputs — fixed costs, price, variable cost — so you can instantly see what each lever does: a 10% price rise typically cuts the break-even point far more than a 10% cost cut, a fact that surprises almost everyone the first time they see it. Excel is the perfect environment for exactly this kind of play, and the interactive calculator further down this page lets you feel the lever effects before you build the model yourself. Its limits are worth knowing too: it assumes linear costs and a constant price, a single product (or fixed mix), and that everything produced is sold — fine for decision-grade estimates, not for precision accounting.

Before You Start

You need three numbers, and the quality of the analysis depends entirely on how honestly you assemble them.

Total fixed costs for the period

Pick a period — monthly is the most actionable — and total every cost you would still pay at zero sales: rent, salaries (including your own!), insurance, software, accounting, loan interest. Annual items get divided by 12. Founders systematically understate this number by omitting their own pay; resist.

Selling price per unit

The realised price, not the list price: net of average discounts, marketplace commissions you never see, and refunds. If you sell at £60 list but average 5% discounts and 2% refunds, your realised price is nearer £55.80.

Variable cost per unit

Every cost incurred because one more unit sells: materials, packaging, shipping, payment processing (a percentage of price!), per-unit labour, sales commission. The classification test for anything ambiguous: if sales were zero next month, would you still pay it? Yes → fixed. No → variable. Semi-variable costs (a rep on salary plus commission) get split into both buckets.

1.Step 1 — Separate Fixed and Variable Costs

Open a sheet and build a cost classifier: every cost line in column A, its monthly amount in column B, and its type — Fixed or Variable — in column C (add Data Validation with a Fixed/Variable list to keep it clean). Work straight down your bank statement or P&L so nothing escapes. Then total each bucket:

For variable costs, the classifier should hold the per-unit amounts (cost of one unit’s materials, one shipment, one payment fee), totalled the same way with "Variable". Watch the percentage-based items: payment processing at 2.9% of a £60 price is £1.74 per unit — convert percentages to per-unit money so everything adds.

Three classification traps to handle deliberately. Semi-variable costs: a salesperson on £2,000 salary plus £5/unit commission contributes £2,000 to fixed and £5 to variable — split them, never average them. Step costs: rent is fixed until you outgrow the warehouse, then it jumps — treat it as fixed within your “relevant range” of volumes and re-run the model beyond it. Your own time: unpaid founder hours are a real cost the model should see; give them at least a token salary in fixed costs or the break-even point will flatter you.

Finish by promoting the three key numbers to a clean input block on the model sheet: B1 = total fixed costs (say £24,000/month), B2 = price per unit (£60), B3 = variable cost per unit (£24). Every formula that follows reads from exactly these three cells, which is what makes the model instantly re-runnable.

2.Step 2 — The Contribution Margin Formula

The contribution margin per unit is the engine of everything that follows. In B4:

With a £60 price and £24 variable cost, each unit contributes £36. Read that the right way: it is not profit. The first several hundred £36 contributions go entirely toward the £24,000 fixed-cost mountain; only after the mountain is paid does £36 per unit become profit. This reframing — sales as contributions to a fixed lump — is the mental model that makes the rest of CVP obvious.

Also compute the contribution margin ratio in B5 — the contribution per pound of revenue rather than per unit:

Here that is 36/60 = 60%: every £1 of sales contributes 60p toward fixed costs and profit. The ratio is the version you need when units are awkward (mixed products, services billed in odd lumps) and it drives the break-even revenue formula in Step 4. As a benchmark, software and services often run 70–90% ratios, manufacturing 30–50%, and retail/distribution 20–35% — which is precisely why a retailer needs so much more volume to cover the same overheads.

3.Step 3 — Break-Even Units

Now the headline formula. Break-even units = fixed costs ÷ contribution margin per unit. In B6:

With our numbers: 24,000 ÷ 36 = 666.7 units. The logic is plain division: you need £24,000 of contributions, each unit contributes £36, so you need 666.7 of them. Since you cannot sell a fraction of a unit, round up — =ROUNDUP(B1/(B2-B3),0) — to 667 units; at 666 you are still £24 short.

Sense-check the number against reality immediately: 667 units a month is roughly 31 per working day. Is that plausible for your channel, your traffic, your sales team? This is the moment break-even analysis earns its keep — if the answer is “obviously not”, you have just learned, for the cost of one formula, that the plan needs different economics (see the FAQ on exactly which levers to pull).

The same division answers target-profit questions: to find the volume that delivers a desired profit, add the target to the fixed costs — the profit is just more lump to cover. Units for £10,000 monthly profit:

…= 944.4, so 945 units. Notice the gap: zero profit at 667 units, £10,000 profit at 945. Beyond break-even, profit accumulates at the full £36 per unit — the fixed costs are already paid — which is why businesses feel so dramatically different just past their break-even point than just before it.

4.Step 4 — Break-Even Revenue

Stakeholders often think in money, not units. Break-even revenue is the turnover at which you break even, and there are two equivalent routes. The direct one — break-even units times price — in B7:

666.7 × £60 = £40,000 of monthly sales to break even. The second route divides fixed costs by the contribution margin ratio from Step 2:

24,000 ÷ 0.60 = the same £40,000 — and this version is the more useful of the two in practice, because it needs no unit definition at all. A consultancy with mixed projects, an agency with varied retainers, a shop with thousands of SKUs: as long as the blended contribution margin ratio is reasonably stable, “fixed costs divided by the ratio” gives a defensible break-even turnover without modelling a single unit.

It also produces the most quotable sentence in the whole analysis. At a 60% ratio, “we keep 60p of every sales pound; we need £24,000 kept; so we need £40,000 of sales” — an explanation a non-financial audience follows in one hearing. If your monthly P&L (see our profit & loss guide) shows revenue hovering near £40,000, you now know precisely why profit keeps flickering around zero.

5.Step 5 — Build the Units→Profit Data Table

One number tells you where break-even is; a table shows the whole profit landscape around it — and feeds the chart in Step 6. Starting at A10, build four columns: Units, Revenue, Total cost, Profit. In the Units column, list volumes from 0 up to about twice break-even in round steps — 0, 100, 200 … 1,400 (type 0 and 100, select both, drag the fill handle). Then one row of formulas in row 11, with everything anchored to the input block:

…for Revenue in B11; Total cost in C11 is the fixed lump plus the variable slope:

…and Profit in D11 is simply =B11-C11. Select B11:D11 and fill down. Scan the Profit column: it starts at −£24,000 (zero sales = the full fixed-cost loss), climbs by £3,600 per 100-unit step (100 × the £36 contribution), passes through zero between 600 and 700 units — bracketing the 667 from Step 3 — and reaches +£26,400 at 1,400 units.

The table makes the model’s deep structure visible: profit is a straight line in volume, with slope equal to the contribution margin and intercept equal to minus the fixed costs. It also gives you conversational answers the single formula cannot — “at 500 units we lose £6,000 a month; at 900 we make £8,400” — which is the language planning meetings are conducted in. Add conditional formatting (red fill for negative profit, green for positive) and the break-even point announces itself as the colour change.

6.Step 6 — Draw the Classic Break-Even Chart

The break-even chart — two straight lines crossing — is one of the most recognisable diagrams in business, and your Step 5 table builds it in four clicks. Select the Units, Revenue, and Total cost columns (A10:C24; hold Ctrl to skip Profit), then Insert → Charts → Scatter → Scatter with Straight Lines.

Use a scatter chart, not a line chart — this matters. A scatter chart treats Units as a true numeric x-axis, so the geometry is faithful; a line chart treats your unit values as evenly spaced category labels, which distorts slopes and puts the crossing in the wrong place the moment your steps are uneven.

Read the picture: the revenue line rises from the origin at £60 per unit; the total-cost line starts at £24,000 (the fixed lump on the y-axis — your loss at zero sales) and rises at the shallower £24 per unit. Left of the crossing, cost is on top: the vertical gap is your loss at that volume. Right of it, revenue is on top: the gap is profit, widening forever. The crossing itself sits at exactly 667 units / £40,000 — your Steps 3 and 4 answers, now visible as geometry.

To mark the break-even point explicitly, add a one-point series: right-click → Select Data → Add, with X = =$B$6 (break-even units) and Y = =$B$7 (break-even revenue), then format that lone marker large and bright with a data label. Because the marker reads the input cells, it slides along the chart automatically whenever an assumption changes — a small touch that makes the model feel alive in a meeting. Title the chart with the verdict, not a description: “Break-even: 667 units (£40k) / month”.

7.Step 7 — Margin of Safety

Break-even tells you where the cliff edge is; the margin of safety tells you how far from the edge you are standing. It is the percentage by which expected sales exceed break-even sales — equivalently, how far demand can fall before you tip into loss. Put your expected monthly volume in B8 (say 900 units), then in B9:

Format as a percentage: (900 − 667) / 900 = 25.9%. Sales could fall by a quarter before the business starts losing money. As rough working bands: above 35–40% is comfortable; 20–35% is normal but worth watching; below 15–20% is fragile — an ordinary seasonal dip or one lost customer pushes you under water, and you should be actively working a lever (price, costs, volume) rather than merely monitoring.

The concept pairs naturally with operating leverage — how fixed-heavy your cost base is. A high-fixed-cost business (software, gyms, airlines) has a steep profit line: it breaks even late, but profit explodes past the threshold; the same structure makes downturns brutal, which is why such businesses need bigger safety margins. A variable-heavy business (drop-shipping, marketplaces) breaks even early but earns thinly per unit. Your Step 5 table shows your own leverage directly — the slope of the profit column is it — and the margin of safety tells you whether that leverage is currently working for you or waiting to hurt you.

Track the margin of safety monthly alongside actual sales — it is a far better early-warning KPI than profit itself, because it deteriorates before profit goes negative. It belongs on the same dashboard as your financial KPIs.

8.Step 8 — What-If Analysis with Goal Seek

Every formula so far runs forwards: inputs in, break-even out. Real planning questions run backwards: “we can realistically sell 400 units — what price breaks even?”, “at £60, what’s the most we can spend on fixed costs?”, “what variable cost must we negotiate to break even at 500?”. Excel’s Goal Seek solves these without any algebra.

First give it a profit cell to aim at. In B10, profit at expected volume:

Now: Data → What-If Analysis → Goal Seek. Three boxes: Set cell = B10 (the profit formula), To value = 0 (break-even — or any target profit), By changing cell = the lever you want solved. To find the break-even price at 400 units: set B8 to 400 first, then Set B10, To 0, By changing B2. Excel iterates and lands on £84 — the price at which 400 units exactly covers everything. One run per question: change “By changing cell” to B1 to solve maximum fixed costs, to B3 to solve the required variable cost, or set “To value” to 10000 to solve for a £10k-profit price.

Two practical cautions. Goal Seek overwrites the changing cell with its answer — note the result, then Undo (Ctrl+Z) or work on a copy of the input block, or your base case silently mutates. And it changes exactly one variable at a time; for a grid of answers across two levers simultaneously (price × volume, say), use Data → What-If Analysis → Data Table, which tabulates the profit formula over both dimensions at once — the natural next step once Goal Seek has answered the single-lever questions.

Try It: Break-Even Calculator

The whole model, live. Enter fixed costs, price, and variable cost per unit — exactly the three input cells from Step 1 — and the calculator returns break-even units, break-even revenue, and the contribution margin, while the chart redraws the revenue and total-cost lines with the break-even point marked at their crossing. Try the levers: raise the price by 10% and watch how far the BEP marker slides left; then cut fixed costs 10% and compare. Feeling which lever moves the point furthest is the fastest pricing lesson in business.

Advanced Break-Even Tips

Multi-product break-even with a weighted margin

With several products, compute each product’s contribution margin and weight by sales mix: =SUMPRODUCT(margins,mixShares) gives the weighted CM per “average unit”, and fixed costs divided by it gives total break-even units, split back into products by the mix. The caveat is the mix assumption itself — if buyers drift toward the low-margin product, the true break-even rises, so always re-run with a pessimistic mix.

Two-variable sensitivity with Data Tables

Lay prices across a row, volumes down a column, point the corner cell at your profit formula, and Data → What-If Analysis → Data Table fills the grid with profit at every combination. Heat-map it (3-colour scale, midpoint 0) and the zero contour — the break-even frontier across both levers — appears as the colour boundary.

Break-even on cash, not accounting profit

The classic model uses accounting costs, but a startup usually cares about cash break-even: strip non-cash items (depreciation) from fixed costs and add cash items the P&L hides (loan principal repayments). The volume where monthly cash burn stops is often materially different from accounting break-even — and it is the number that determines runway. Pair this analysis with a proper cash flow forecast.

From one-off analysis to a live model

A break-even model earns most when the whole team can poke at it. Upload your workbook to DataHub Pro and the inputs become sliders, the chart stays live, and the break-even point updates for anyone with the link — pricing meetings stop being screenshot archaeology.

Worked Example: Pricing a Coffee Subscription Box

A founder launches a monthly speciality-coffee box. Fixed costs: £3,000/month studio and storage, £4,500 part-time staff, £1,200 software and marketing tools, £800 insurance and accounting, plus a deliberately modest £2,500 founder salary — £12,000 fixed. Per box: £7.10 coffee, £1.40 packaging, £3.20 postage, £0.90 payment fees and £1.40 returns/breakage allowance — £14.00 variable. Planned price: £26 per box.

The model in three formulas. Contribution margin: 26 − 14 = £12 per box (a 46% ratio — typical for physical-product subscriptions). Break-even units: 12,000 ÷ 12 = 1,000 boxes a month. Break-even revenue: 1,000 × 26 = £26,000. The founder’s realistic year-one ceiling, based on audience size and comparable launches, is 800 subscribers — below break-even. At 800 boxes the data table reads: revenue £20,800, total cost £23,200, monthly loss £2,400. The plan, as priced, does not work — better to learn that in Excel than in month nine.

Now the levers, via Goal Seek. Price: Set profit to 0 by changing price at 800 units → £29. A £3 rise (11.5%) closes the entire gap, because all £3 lands in the contribution margin — break-even drops from 1,000 to 800 boxes. Variable cost: by changing unit cost at £26 → £11.00, a £3 (21%) cut — achievable only partly, perhaps £1 via a postage renegotiation. Fixed costs: by changing fixed at 800 units → £9,600, a 20% cut that mostly means the founder unpaying themselves — the worst lever. The arithmetic says what it usually says: price is the strongest lever, an 11.5% price change outworking a 20–21% change in either cost line.

The founder ships at £28.50 with the cheaper postage (£13 variable): contribution £15.50, break-even 12,000 ÷ 15.5 = 775 boxes — just under the 800 forecast, for a slim 3% margin of safety in month one, growing as subscribers compound. Punch these numbers into the calculator above and watch the BEP marker make the same journey. That is break-even analysis doing its job: not predicting the future, but ruling out a plan that could never work and quantifying exactly what had to change.

Common Errors & Fixes

#DIV/0! in the break-even formula

Price equals variable cost, so the contribution margin — the denominator — is zero. If it is a typo, fix the inputs; if it is real, the business cannot break even at any volume and the unit economics must change. Guard the cell with =IF(B2<=B3,"No BEP — CM ≤ 0",B1/(B2-B3)).

The break-even point looks suspiciously low

Costs have leaked out of the model: founder pay omitted from fixed costs, or percentage costs (payment fees, marketplace commission, refunds) missing from the variable side. Re-walk a real bank statement against the classifier — flattering break-even numbers are nearly always missing-cost errors, not good news.

The chart’s lines cross in the wrong place

You used a Line chart, which spaces your unit values as evenly-spaced category labels instead of true x-values. Recreate it as Scatter with Straight Lines and the geometry — and the crossing — becomes faithful.

Goal Seek says it can’t find a solution

Usually the target is unreachable from the chosen lever (no positive price reaches £50k profit at 10 units), or the Set cell isn’t a formula, or the changing cell doesn’t feed it. Confirm the dependency chain, pick a reachable target, and remember Goal Seek changes one cell only.

Reality doesn’t match the model

Remember the model’s assumptions: linear costs, one price, constant mix, in a limited volume range. Bulk discounts bend the cost line; promotional pricing bends revenue; step costs (a second machine, a bigger unit) jump the fixed line. Within the relevant range the straight-line model is excellent — re-fit it when you move ranges rather than forcing one model to span them.