Quick answer
The Break-Even formula calculates the number of units needed to cover fixed and variable costs.
=B2/(B3-B4)Syntax
Use this syntax as the pattern, then replace the sample arguments with your own cells, ranges, and criteria.
=Fixed Costs / (Price per Unit - Variable Cost per Unit)When to use it
Financial formulas turn business and investment assumptions into numbers you can compare. They are useful for loans, budgets, pricing, margin analysis, investment decisions, and business cases.
- Calculate monthly loan payments
- Estimate return on investment
- Calculate gross margin and markup
- Evaluate investment cash flows
Example worksheet layout
Test the formula in a small worksheet first. This makes it easier to confirm the result before copying the formula down hundreds of rows.
| Column or range | What it represents |
|---|---|
| A | Assumption label |
| B | Input value such as rate, periods, or amount |
| C | Financial result |
Copy-paste examples
These examples move from simple to more practical versions. Start with the beginner examples, confirm the result, then adapt the intermediate and advanced versions.
Basic Break-Even example
=B2/(B3-B4)Use the simple version to check the business logic first.
Use locked assumption cells
=$B$2/($B$3-$B$4)Lock assumptions when every row should use the same rate, cost, target, or starting value.
Avoid missing-input errors
=IFERROR(B2/(B3-B4),0)Useful when some rows are incomplete or when a denominator can be zero.
Create a display label
=IFERROR("Result: "&TEXT(B2/(B3-B4),"0.00"),"Check inputs")Use TEXT only for a display cell; keep the raw numeric result for calculations.
Use LET for auditability
=LET(result,B2/(B3-B4),result)LET makes financial model formulas easier to review.
Dashboard status label
=IFERROR(IF(B2/(B3-B4)>=0,"Positive","Negative"),"Check inputs")Use a label when the calculation feeds a dashboard status or exception report.
How to use it step by step
- Put your raw data in clear columns with headers such as Amount, Date, Status, Region, or Customer.
- Paste the starting formula into the first result cell, usually row 2.
- Replace sample references such as A2, B2, or $F$2:$F$100 with the cells in your own worksheet.
- Check the result on a few rows where you already know the expected answer.
- Copy the formula down only after the first row returns the right result.
Common mistakes and fixes
- Percentages are entered as whole numbers instead of percentages, such as 12 instead of 12%.
- Cash inflows and outflows use inconsistent signs.
- The result cell is not formatted as currency, number, or percentage as appropriate.
- Test the formula on a few known rows before using it across a full report.
Beginner tips
- Use a small sample first so you can see exactly how the Break-Even formula behaves.
- Convert your source range into an Excel Table when the data will grow over time.
- Add a short note near important formulas so future users know what each input represents.