Break-Even Analysis in Excel: FP&A Formulas & Charts (2026)

July 9, 2026 · VeloraAI Team
Financial Modeling Data Analysis Excel

Roughly 60% of new product launches miss their first-year profit target — and in most cases, no one on the deal team ever bothered to compute a proper break-even point. A break-even analysis in Excel is the fastest way to translate a pricing deck into a hard number: how many units, how much revenue, and how many months before the P&L flips from red to black. This guide walks through the exact formulas, a working single-product model, a weighted-average multi-product model, a break-even chart, and the sensitivity checks FP&A teams run before green-lighting a launch.

What Is Break-Even Analysis and Why Does It Matter?

Break-even analysis calculates the sales volume — in units or dollars — at which total revenue equals total costs, producing zero profit. Below break-even the product loses money; above it, every incremental unit contributes to profit. For FP&A, corporate development, and pricing teams, it is the single most defensible input into a go/no-go decision.

The three questions break-even answers

  1. How many units must we sell to cover our fixed costs?
  2. At what revenue level does the product stop losing money?
  3. How sensitive is that answer to price, variable cost, or fixed cost changes?

💡 Pro Tip: Break-even is not the same as payback. Break-even asks "when do we stop bleeding on a monthly basis?" Payback asks "when do we recover the upfront cash outlay?" Confusing the two is the fastest way to get a launch memo rejected.

The Core Break-Even Formulas

Every break-even calculation reduces to two equations. Memorize these and you can rebuild any model from scratch on a whiteboard.

Break-even in units

Break-Even Units = Fixed Costs / (Price per Unit − Variable Cost per Unit)

The denominator is the contribution margin per unit — the dollars each sale contributes toward covering fixed costs after paying its own variable cost.

Break-even in revenue

Break-Even Revenue = Fixed Costs / Contribution Margin Ratio

Where:

Contribution Margin Ratio = (Price − Variable Cost) / Price

The revenue version is what you use when you sell a bundle of SKUs at different prices and cannot express volume in a single unit.

ℹ️ Note: Fixed costs are costs that do not scale with volume in the relevant range — rent, salaried headcount, software licenses. Variable costs scale directly — raw materials, freight, credit card fees, hourly labor. Semi-variable costs (utilities, sales commissions with a base) should be decomposed into their fixed and variable components before you plug them into these formulas.

How Do You Build a Break-Even Model in Excel? (Single Product)

To build a single-product break-even model in Excel, list your fixed costs, price per unit, and variable cost per unit as inputs; compute contribution margin per unit and contribution margin ratio; then divide fixed costs by each to get break-even units and break-even revenue. Add a data table for monthly sales to see the P&L flip.

Step 1 — Lay out the inputs

Put every driver in a labeled cell in a blue-font "Inputs" block so downstream formulas are auditable.

Cell Label Example Value
B3 Selling price per unit $75
B4 Variable cost per unit $32
B5 Monthly fixed costs $180,000
B6 Target monthly profit $60,000
B7 Corporate tax rate 25%

Step 2 — Compute contribution margin

=B3-B4                     Contribution margin per unit
=(B3-B4)/B3                Contribution margin ratio

With the numbers above, the contribution margin per unit is $43 and the ratio is 57.3%. That ratio is a headline metric — anything below 30% in a physical-goods business is a red flag.

Step 3 — Compute break-even

=B5/(B3-B4)                Break-even units per month
=B5/((B3-B4)/B3)           Break-even revenue per month

At $180,000 of monthly fixed cost, the company must sell 4,187 units or $314,024 of revenue each month just to cover costs.

Step 4 — Add target-profit break-even

To hit a specific profit target (pre-tax), add the target to the numerator:

=(B5+B6)/(B3-B4)           Units to hit target profit

If the target profit is after-tax, gross it up first:

=(B5 + B6/(1-B7))/(B3-B4)  Units for after-tax target

Example: If you need $60,000 of after-tax profit at a 25% tax rate, you must earn $80,000 pre-tax. Total contribution needed = $180,000 + $80,000 = $260,000. At $43 per unit, that is 6,047 units per month.

Step 5 — Compute the margin of safety

The margin of safety is how far current or forecast sales sit above break-even — the buffer before you post a loss.

=(Forecast_Units - Break_Even_Units) / Forecast_Units

A margin of safety below 20% means the plan is fragile. Under 10%, the FP&A committee will send it back.

How Do You Do Break-Even Analysis for Multiple Products?

For a multi-product break-even in Excel, calculate the weighted-average contribution margin ratio using each product's revenue mix, then divide total fixed costs by that weighted CMR to get blended break-even revenue. Allocate the result across products using the same mix percentages to get each SKU's individual break-even.

Single-product formulas break when a business sells three SKUs at three price points with three cost structures. Almost every real FP&A situation is multi-product.

The weighted contribution margin approach

Assume a SaaS company sells Basic, Pro, and Enterprise plans:

Product Price Variable Cost CMR Revenue Mix
Basic $29 $8 72.4% 50%
Pro $99 $22 77.8% 35%
Enterprise $499 $95 81.0% 15%

The weighted CMR is:

=SUMPRODUCT(CMR_Range, Mix_Range)
= 0.724*0.50 + 0.778*0.35 + 0.810*0.15
= 0.7566   (75.66%)

If total monthly fixed costs are $450,000:

=450000/0.7566   Blended break-even revenue = $594,764

Allocated back to each product line at the mix percentages:

  • Basic: $297,382 → 10,254 subscriptions
  • Pro: $208,167 → 2,103 subscriptions
  • Enterprise: $89,215 → 179 subscriptions

⚠️ Warning: Weighted break-even is only valid if the sales mix stays constant. If Enterprise deals close 20% instead of 15%, the whole answer changes. Run a data table across mix scenarios or the number is worse than useless — it is falsely precise.

Break-Even Analysis Flow: How the Model Connects

graph TD
    A[Selling Price per Unit] --> C[Contribution Margin]
    B[Variable Cost per Unit] --> C
    C --> D[Contribution Margin Ratio]
    E[Fixed Costs] --> F[Break-Even Units]
    C --> F
    E --> G[Break-Even Revenue]
    D --> G
    F --> H[Target Profit Units]
    I[Target Profit] --> H
    F --> J[Margin of Safety]
    K[Forecast Volume] --> J
    G --> L[Break-Even Chart]
    F --> L

Every input flows into contribution margin; every output flows out of it. When you audit a break-even model, walk this graph — if any arrow is missing a formula link, the model is broken.

How Do You Build a Break-Even Chart in Excel?

A break-even chart plots total revenue and total costs against sales volume; the intersection is the break-even point. To build one, generate a sales volume series from zero to twice break-even, calculate revenue and total cost at each level, then insert a Line or Scatter chart with both series and mark the crossover point.

Step 1 — Build the data table

In column A, create a volume series from 0 to 8,000 units in 500-unit increments. In columns B, C, and D:

B: =A2*Price               Total Revenue
C: =Fixed_Cost             Fixed Costs (constant)
D: =A2*Variable_Cost+C2    Total Costs

Step 2 — Insert the chart

Select columns A through D. Insert → Scatter with Smooth Lines. Format:

  • Total Revenue line: solid green
  • Total Costs line: solid red
  • Fixed Costs line: dashed gray

Step 3 — Mark break-even

Add a fifth column with the break-even point value repeated on every row, plot it as a vertical dotted line, and label it with a text box: "Break-even: 4,187 units / $314K."

💡 Pro Tip: Use a dynamic named range (=OFFSET(...)) or an Excel Table (Ctrl+T) so the chart automatically extends when you add rows. Static ranges are the number-one reason break-even charts drift out of sync with the model.

Sensitivity Analysis: Which Assumption Matters Most?

Break-even is a point estimate, and point estimates are dangerous. Every launch review should include a two-variable data table stress-testing break-even against price and variable cost.

Building the data table

  1. In cell F3, put the break-even formula: =Fixed_Cost/(Price-Variable_Cost)
  2. In row 4 across F–P, list price scenarios: $60, $65, $70, $75, $80, $85, $90.
  3. In column E from row 5 down, list variable cost scenarios: $25, $28, $31, $34, $37, $40.
  4. Select the block E3:P10. Data → What-If Analysis → Data Table.
  5. Row input cell = Price cell. Column input cell = Variable Cost cell.

Excel returns a matrix of break-even units for every combination. The pattern you are looking for: does break-even blow up disproportionately when price drops by 10%? If yes, the launch is price-fragile and needs a hedged pricing plan.

Elasticity check with a one-way table

For a single-variable stress test, use a one-way table or a simple sensitivity row:

=Fixed_Cost/((Price*(1+Price_Change))-Variable_Cost)

Drag across a range of Price_Change values from −20% to +20% in 5% steps.

Common Break-Even Mistakes That Wreck the Analysis

The math is not hard — the classification of costs is. Below are the four errors we see most often in launch memos.

1. Treating step-fixed costs as fully variable

A warehouse lease is fixed until you exceed capacity — then you sign a second lease and fixed cost jumps. Model step-fixed costs with a stepped IF chain or a LOOKUP against a capacity band table, not a linear formula.

2. Ignoring the tax shield on interest

If the company is levered, interest expense is a fixed cost, but it produces a tax shield. Post-tax break-even should use:

=(Fixed_Cost - Interest*Tax_Rate)/(Price - Variable_Cost)

3. Confusing accounting break-even with cash break-even

Depreciation is a fixed cost on the P&L but not a cash outflow. Cash break-even subtracts non-cash items:

=(Fixed_Cost - Depreciation - Amortization)/(Price - Variable_Cost)

Cash break-even is always lower than accounting break-even. Both matter — they answer different questions.

4. Assuming linear variable cost

Volume discounts, learning-curve effects, and freight tiering all make variable cost non-linear. Model variable cost as a function of volume using a SUMPRODUCT against tiered rates rather than a single value.

Break-Even vs Payback vs NPV: When to Use Which

Break-even is one tool in a decision toolkit. Use the right one for the question you are answering.

Metric Question It Answers Best Use Case Weakness
Break-Even Units How many units to zero P&L? Product launch, pricing Ignores time value
Break-Even Revenue What sales level covers cost? Multi-SKU, service business Requires stable mix
Payback Period When do we recover cash? Capex, working capital deploy Ignores post-payback cash
NPV Is the project value-additive? Any multi-year decision Requires WACC estimate
IRR What return does the project earn? Comparing projects Fails on non-standard cash flows

Break-even sits at the front of the decision funnel — if you cannot break even, the NPV question is moot.

Automating Break-Even with AI Tools

Once the model exists, the tedious part is refreshing it whenever pricing, mix, or cost assumptions change. AI-native Excel add-ins like VeloraAI let analysts describe a break-even scenario in plain English — "recompute break-even if the Pro tier drops 15% and Enterprise mix rises to 25%" — and get the recalculated model, updated chart, and a written summary of what moved. The math has not changed since Peter Drucker; the workflow around it finally has.

Frequently Asked Questions

What is the break-even formula in Excel?

The core break-even formula in Excel is =Fixed_Costs/(Price-Variable_Cost) for units and =Fixed_Costs/((Price-Variable_Cost)/Price) for revenue. Wrap them in cell references rather than hard-coded values so a single input change flows through the entire model. Use named ranges (Fixed_Cost, Price, Variable_Cost) to make the formulas self-documenting.

How do you calculate break-even point for multiple products?

Compute each product's contribution margin ratio ((Price - Variable Cost) / Price), multiply by its revenue mix percentage, and sum with SUMPRODUCT to get a weighted CMR. Divide total fixed costs by the weighted CMR to get blended break-even revenue, then allocate back to each SKU by the mix percentages to find product-level break-even.

What is a good margin of safety percentage?

Most FP&A teams consider a margin of safety above 30% comfortable, 20-30% adequate, and below 20% risky. For volatile industries — commodities, early-stage SaaS, hardware launches — analysts often demand 40%+. The right threshold depends on volume forecast accuracy: a business that consistently hits within 5% of plan can operate on a much thinner buffer than a startup with ±40% forecast error.

What is the difference between contribution margin and gross margin?

Contribution margin equals revenue minus all variable costs (production plus variable selling and shipping). Gross margin equals revenue minus cost of goods sold (COGS), which usually includes some fixed manufacturing overhead. Contribution margin is the right metric for break-even because it isolates the incremental economics of one more unit sold; gross margin blends fixed and variable costs together.

Can break-even analysis be used for services?

Yes — replace "units" with billable hours, seats, or engagements, and "variable cost" with the marginal cost of delivering one more (consultant hourly rate, cloud compute per user, hosting per tenant). Services businesses often find their contribution margin ratio is above 70%, meaning break-even is dominated by fixed overhead rather than delivery cost. The model shape is identical; only the units change.

Next Steps

Build the single-product model first, then extend to weighted multi-product, then layer sensitivity. A robust break-even model takes a competent analyst about two hours to build and pays for itself the first time a CEO asks "what if we cut price by 10%?" and gets an answer in seconds instead of overnight. If you want the same answer, the same afternoon, without rebuilding the model for every scenario, plug an AI copilot into the workbook and let the math keep up with the questions.