Break-Even Analysis in Excel: Formula, Template & Lender-Ready Model

By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated September 29, 2026

Break-even analysis tells you the sales level where total revenue equals total costs. In Excel, the core calculation is simple: divide fixed costs by contribution margin per unit for break-even units, or divide fixed costs by the contribution margin ratio for break-even sales dollars. The real value comes from building the model so price, costs, scenarios, and charts update automatically.

TL;DR:

• Break-even units = fixed costs ÷ contribution margin per unit.
• Break-even sales dollars = fixed costs ÷ contribution margin ratio.
• Changes in price, variable costs, or fixed costs move the break-even point. Expected sales volume affects forecast profit and margin of safety.
• Goal Seek can solve for one changing input at a time. Solver is better when several variables or constraints must move together.
• A lender-ready model should show assumptions, downside cases, margin of safety, and the difference between accounting break-even and cash requirements.

Table of Contents

• Core break-even formulas and contribution margin
• Worked break-even example
• What the Excel template includes and how to use it
• Step-by-step Excel build and formulas
• Target profit and margin of safety
• Multi-product break-even analysis
• Use Goal Seek to find your break-even price or volume
• Build a revenue vs total cost chart
• Key assumptions and frequent errors
• Sensitivity analysis
• Break-even across different business models
• Using Excel Data Tables
• What makes a break-even model lender-ready?
• When break-even is the right tool, and when to upgrade
• Get your break-even model validated by Frac CFO
• Sources
• FAQ

Core break-even formulas and contribution margin

Break-even is the point where total revenue equals total costs. For a single-product model, the essential formulas are:

• Contribution margin per unit = Price per unit − Variable cost per unit.
• Contribution margin ratio = Contribution margin per unit ÷ Price per unit.
• Break-even units = Fixed costs ÷ Contribution margin per unit.
• Break-even sales dollars = Fixed costs ÷ Contribution margin ratio.

These formulas are standard cost-volume-profit relationships. The important modeling work is making sure each cost is classified correctly and the assumptions match the period being analyzed.

Worked break-even example

Assume a retail business has $8,000 of monthly fixed costs, sells its product for $40 per unit, and incurs $22 of variable cost per unit.

• Contribution margin per unit = $40 − $22 = $18.
• Contribution margin ratio = $18 ÷ $40 = 45%.
• Break-even units = $8,000 ÷ $18 = 444.44 units, so the business must sell at least 445 whole units.
• Break-even sales = $8,000 ÷ 45% = approximately $17,778.

This example also shows why discrete units should normally be rounded up. Selling 444 units would still leave the business slightly below break-even.

What the Excel template includes and how to use it

A well-built workbook should let you change one assumption and have every calculation and chart update automatically. A practical structure is:

• Inputs sheet: fixed costs, price per unit, variable cost per unit, expected units sold, and target profit.
• Calculation sheet: contribution margin, contribution margin ratio, break-even units, break-even sales, target-profit units, projected profit, and margin of safety.
• Scenario or sensitivity sheet: alternative prices, costs, and fixed-cost assumptions.
• Chart sheet or dashboard: revenue and total-cost lines with the break-even point clearly labeled.

You can browse ready-made options in the Excel templates collection, or build your own using the layout below.

Step-by-step build: worksheet layout and exact formulas to paste

Use column A for labels and column B for values or formulas. That keeps the model readable and avoids mixing labels with inputs.

1. A2: Fixed Costs. Enter the monthly amount in B2.
2. A3: Price per Unit. Enter the selling price in B3.
3. A4: Variable Cost per Unit. Enter the variable cost in B4.
4. A5: Contribution Margin per Unit. In B5 enter =B3-B4.
5. A6: Contribution Margin Ratio. In B6 enter =IFERROR(B5/B3,0).
6. A7: Break-Even Units. In B7 enter =IF(B3<=B4,"No break-even",ROUNDUP(B2/B5,0)).
7. A8: Break-Even Sales. In B8 enter =IF(B6<=0,"No break-even",B2/B6).
8. A9: Expected Units Sold. Enter your forecast volume in B9.
9. A10: Projected Profit. In B10 enter =(B9*B3)-(B9*B4)-B2.

The error checks matter. If price is less than or equal to variable cost, selling more units does not create a conventional break-even point because each additional sale contributes zero or a negative amount toward fixed costs.

For a service business without a clear physical unit, work in sales dollars using total revenue and total variable costs to calculate the contribution margin ratio.

Pro Tip: Name your input cells using Excel’s Name Box. A formula such as =FixedCost/(PricePerUnit-VarCostPerUnit) is easier to audit than a long chain of anonymous cell references.

Target profit and margin of safety

Break-even answers “how much must we sell to avoid a loss?” Management usually needs two more questions answered: “how much must we sell to hit our profit target?” and “how far above break-even is our forecast?”

• Target-profit units = (Fixed Costs + Target Profit) ÷ Contribution Margin per Unit.
• Margin of safety in sales dollars = Expected Sales − Break-Even Sales.
• Margin of safety % = (Expected Sales − Break-Even Sales) ÷ Expected Sales.

Using the worked example above, if the business wants a $4,000 monthly operating profit, target units are ($8,000 + $4,000) ÷ $18 = 666.67, so it needs at least 667 units.

Multi-product break-even analysis

A single-product break-even formula is not enough when the business sells several products with different margins. In that case, use a weighted-average contribution margin based on the expected sales mix.

Example: Product A contributes $20 per unit and represents 60% of expected unit sales. Product B contributes $10 per unit and represents 40%.

Weighted-average contribution margin = ($20 × 60%) + ($10 × 40%) = $16 per composite unit.

If fixed costs are $16,000, break-even is $16,000 ÷ $16 = 1,000 composite units. At the assumed mix, that means about 600 units of Product A and 400 units of Product B.

The warning is important: if the real sales mix changes, the break-even point changes too. A multi-product model should therefore make sales-mix assumptions visible rather than burying them inside formulas.

Use Goal Seek to find your break-even price or volume

Goal Seek is useful when you know the result you want and need Excel to solve for one input.

1. Build a profit formula cell, such as =(UnitsSold*PricePerUnit)-(UnitsSold*VarCostPerUnit)-FixedCost.
2. Open Data → What-If Analysis → Goal Seek.
3. Set “Set cell” to the profit formula.
4. Set “To value” to 0 for break-even, or enter a target profit such as 5000.
5. Set “By changing cell” to the one input Excel should solve, such as units sold or price per unit.

Goal Seek changes one input cell at a time. If you need several variables to move together, especially with constraints such as minimum margin, capacity, staffing, or price limits, Excel Solver is the better tool.

Build a revenue vs total cost chart and label the intersection

A chart makes the model easier for a lender, investor, or non-finance manager to read. Build three columns:

• Units sold: from zero to comfortably above break-even.
• Total revenue: units × price per unit.
• Total cost: fixed costs + (units × variable cost per unit).

Insert a line chart. The intersection of total revenue and total cost is the break-even point. Label the exact units and sales value rather than asking the reader to estimate the crossing visually.

Key assumptions and frequent errors to check

Simple break-even models assume price and cost behavior remain reasonably stable over the relevant range. Three common errors can make the result look safer than it really is:

• Semi-variable costs: costs with both fixed and usage-based components get forced into the wrong bucket. Split the fixed and variable elements where practical.
• Owner compensation: excluding a realistic owner salary can understate the fixed-cost base.
• Transaction and fulfillment costs: payment processing, commissions, shipping subsidies, marketplace fees, and similar costs often belong in the variable-cost assumption.

Pro Tip: Run downside cases rather than relying on one base forecast. For example, test a 10% increase in variable costs, a price reduction, and a fixed-cost increase separately and together.

Sensitivity analysis: what happens when price, cost, or volume moves

A single break-even number tells you where the model balances under one set of assumptions. Sensitivity analysis shows how fragile that answer is.

Changes in price, variable cost, or fixed cost move the break-even point directly. Expected sales volume does not change the break-even formula itself; instead, it changes projected profit and the margin of safety above or below break-even.

Price sensitivity often matters most for thin-margin businesses because even a small discount reduces contribution margin. Fixed-cost sensitivity matters for businesses with large leases, salaried teams, or other committed overhead. Variable-cost sensitivity matters when materials, freight, commissions, or platform fees can move quickly.

Run at least a base case and downside case before using the result to support a lease, hiring decision, financing request, or major pricing change.

Break-even analysis across different business models

The same underlying logic applies across physical products, subscriptions, and services, but the inputs look different.

Retail: A shop with $8,000 in monthly fixed costs, a $40 average selling price, and $22 in variable cost has an $18 contribution margin and needs 445 whole units to break even.

Subscription/SaaS: Suppose a software business has $10,000 in monthly fixed costs, charges $50 per subscriber, and incurs $10 of variable servicing cost per subscriber. Contribution margin is $40, or 80% of revenue. Break-even is 250 subscribers, equivalent to $12,500 of monthly recurring revenue at that price and cost structure.

Services: A consulting business with no useful physical “unit” can work directly in sales dollars: fixed costs divided by the contribution margin ratio calculated from total revenue and variable delivery costs such as subcontractors or pass-through software.

E-commerce: Payment processing fees, marketplace fees, pick-and-pack charges, and shipping subsidies should be considered in the variable-cost structure when they rise with orders.

Using Excel Data Tables for scenario analysis

A two-variable Excel Data Table can show dozens of price-and-cost combinations without manually rebuilding each scenario.

Put price per unit across the top row and variable cost per unit down the left column. Put a reference to the break-even-units formula in the top-left corner of the scenario grid. Select the full table, open Data → What-If Analysis → Data Table, set the row input cell to the price input, and set the column input cell to the variable-cost input.

This turns the model from a single answer into a decision tool. You can immediately see which combinations keep the required sales volume realistic and which ones push it beyond capacity or demand.

What makes a break-even model lender-ready?

A lender-ready break-even model needs more than a correct formula. It should make the assumptions easy to trace and show whether the business still works when conditions are less favorable than expected.

• State the time period: monthly and annual assumptions should not be mixed.
• Document the source of key inputs: lease terms, payroll, supplier quotes, historical gross margin, payment fees, or other support for major assumptions.
• Show base and downside cases: lenders care about what happens if sales are weaker or costs are higher than planned.
• Include margin of safety: show how far expected sales sit above break-even, not only the break-even number itself.
• Separate accounting break-even from cash needs: loan principal repayments are cash outflows but not operating expenses. A business can show accounting profit and still face cash-flow pressure.
• Respect capacity constraints: if the model requires 5,000 units to break even but the operation can produce only 3,000, the mathematical answer is not commercially achievable.
• Keep inputs visible: avoid hard-coded assumptions hidden inside formulas.

When break-even is the right tool, and when to upgrade

Break-even analysis is best used as a short-to-medium-term decision tool: testing a price, assessing a launch, evaluating a lease, or checking whether a cost structure is viable. Standard cost-volume-profit models become less reliable when costs, prices, mix, or capacity change materially with scale.

Once the decision involves several moving levers at once—such as pricing, hiring, product mix, capacity, financing, and minimum profitability—move to scenario forecasting or an optimization model using Excel Solver.

Get your break-even model validated by Frac CFO

A break-even calculation is only as reliable as the assumptions feeding it. Frac CFO can review the model directly in your existing Excel or Google Sheets file, check the cost classifications and formulas, and build clearer base and downside scenarios.

• Profitability and Pricing Analysis — review pricing, contribution margin, cost assumptions, and break-even logic.
• Bookkeeping Excel Template — organize cost data before it feeds the model.
• Shopify Profit and Loss Tracker Excel — organize e-commerce revenue and variable-cost inputs.

Browse One-Time Projects for financial modeling, pricing, break-even, and optimization work.

Sources

• OpenStax — Calculate a Break-Even Point in Units and Dollars
• Microsoft Support — Use Goal Seek to find the result you want
• Yale School of Management — A Primer on Breakeven Analysis

FAQ

How do you make a break-even analysis in Excel?

Enter fixed costs, price per unit, and variable cost per unit in separate input cells. Calculate contribution margin as price minus variable cost, then divide fixed costs by contribution margin to get break-even units. Round up when units must be whole numbers.

How do I create a break-even analysis table in Excel?

List a range of sales volumes, calculate total revenue and total cost for each volume, and identify the point where revenue meets or exceeds total cost. For scenario analysis, use a two-variable Data Table to test combinations of price and variable cost.

How do I calculate break-even analysis?

Break-even units equal fixed costs divided by contribution margin per unit. Break-even sales dollars equal fixed costs divided by the contribution margin ratio.

What is the basic BEP formula?

The basic single-product formula is Fixed Costs ÷ (Price per unit − Variable cost per unit). If the result is fractional and products are sold only in whole units, round up.

What is margin of safety in break-even analysis?

Margin of safety measures how far expected sales are above break-even sales. In percentage terms, it is (Expected Sales − Break-Even Sales) ÷ Expected Sales.

About the author

Jonas Tyrone Lobaton is the founder of Frac CFO. He holds a Master’s degree in Quantitative Finance and the FMVA® certification, with work spanning financial modeling, forecasting, optimization, dashboards, business finance, and spreadsheet automation.

Recommended

• Investor Pitch Model Excel Template
• Three Statement Financial Model Excel Template
• Cash & Financing Optimizer
• Inventory & Budget Tracker Excel Template