Spreadsheet Practice

How to calculate the break-even point in Excel

Formula (Excel and Google Sheets)

=ROUNDUP(B2/(B3-B4),0)

Divide fixed costs by the margin on each unit and round up: =ROUNDUP(B2/(B3-B4),0) is the number of units you must sell.

Result on the example
1028 in B5
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B5. Change any input and the result updates, or edit the formula to experiment.

How it works

Each unit sold contributes its price minus its variable cost, $18, toward the fixed costs. Dividing $18,500 by $18 gives 1,027.8 units, and since you can't sell part of a unit, ROUNDUP makes it 1,028. Beyond that, each unit adds $18 of profit.

=ROUNDUP(B2/(B3-B4),0)

Result1028

  1. B2$18,500

    The value in B2.

  2. B3$45.00

    The value in B3.

  3. B4$27.00

    The value in B4.

  4. B3-B418

    The - operator subtracts.

  5. B2/(B3-B41027.777778

    The / operator divides.

  6. ROUNDUP(B2/(B3-B4),0)1028

    ROUNDUP: Rounds away from zero.

    • numberB2/(B3-B41027.777778
    • num_digits0

In Google Sheets

The same formula works in Google Sheets: =ROUNDUP(B2/(B3-B4),0). We ran it in Google Sheets mode on the same data and it returns 1028.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=B2/(B3-B4)1027.777778Different resultThe exact figure, for use in further calculations. 1,027.8 units, unrounded.
=B2/(1-B4/B3)46250Different resultBreak-even in sales dollars: fixed costs divided by the contribution margin ratio. In dollars: $46,250.

Common mistakes

  • If the price is no higher than the variable cost, every sale loses money and the formula returns a negative number or #DIV/0!.
  • Rounding down would leave you just short of break-even. Use ROUNDUP.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate the break-even point

In B5, calculate how many units must be sold each month to cover the fixed costs in B2, given the price in B3 and the variable cost per unit in B4.

Questions

How many units for a target profit?

Add the target to the fixed costs: =ROUNDUP((B2+B6)/(B3-B4),0).

How do I show it on a chart?

Chart total revenue and total cost against units; break-even is where the two lines cross.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.