The formula
Break-even unitsExcelGoogle Sheets
=ROUNDUP(B1/(B2-B3),0)In D1 → 500
Break-even revenueExcelGoogle Sheets
=B1/(1-B3/B2)In D2 → $12,500.00
Contribution margin ratioExcelGoogle Sheets
=(B2-B3)/B2In D3 → 48%
✓ Tested. All 3 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fixed costs / month | 6000 | 500 | |
| 2 | Price per unit | 25 | $12,500.00 | |
| 3 | Variable cost per unit | 13 | 48% |
How it works
- B2-B3
- Contribution margin per unit.
- ROUNDUP(…,0)
- You can't sell a fraction of a unit.
- 1-B3/B2
- Contribution margin ratio; revenue = fixed costs ÷ ratio.
Common errors and fixes
Why does the formula return #DIV/0!?
Price equals variable cost: you never break even.
Negative units?
Variable cost is above price.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.