Break-Even Calculator (Excel and Google Sheets)

A break-even calculator takes your price, variable cost and fixed costs and returns the sales volume at which you stop losing money. This page shows the layout of a simple calculator that you can rebuild in Excel or Google Sheets in about twenty minutes: the cells, the formulas, a sensitivity grid and the data for a chart, with a worked example so you can check your own version. We do not offer a downloadable file here; if you would like a working version tailored to your business, you can contact us. The calculator supports break-even analysis and the break-even point formula, and is part of unit economics.

Sheet 1: Inputs

Create a sheet called Inputs. Put labels in column A and values in column B. Use a blue font for cells the user can change, and keep everything else black.

Add a note beside each input saying where the number comes from, for example "from price list, average after discounts" or "from last year's accounts, rent plus salaries plus insurance". Notes make the sheet auditable and help the next person to update it.

Sheet 2: Calc

Create a sheet called Calc, with labels in column A and formulas in column B.

Format B4 and B9 as percentages, B6 and B10 as currency, and the rest as numbers. If the contribution per unit is zero or negative, the break-even formulas will return an error or a negative number. Wrap them with a check such as =IF(B3<=0,"No break-even: contribution is not positive",ROUNDUP(Inputs!B5/B3,0)) to give a clear message.

The example numbers follow the workshop in the break-even analysis guide: a price of $400, a variable cost of $240 and fixed costs of $48,000. Check your sheet against them: 400 - 240 = 160; 48,000 / 160 = 300; 120,000 of revenue; (48,000 + 16,000) / 160 = 400; (48,000 - 6,000) / 160 = 262.5, rounded up to 263; (380 - 300) / 380 = 21.1 percent; 380 × 160 - 48,000 = 12,800; 380 × 160 / 12,800 = 4.75.

Sheet 3: Sensitivity grid

Add a grid that shows break-even units for different prices and variable costs. Put price options down column A (rows 4 to 8) and variable cost options across row 3 (columns B to D). In each cell, use a formula that refers to the row and column headers and to the fixed costs on the Inputs sheet, for example:

=ROUNDUP(Inputs!$B$5/($A4-B$3),0)

With prices of 360, 380, 400, 420 and 440 and variable costs of 216, 240 and 264, the example grid is:

The grid shows at a glance how much volume the business needs under different assumptions. The center cell should match the base case in the Calc sheet. Use conditional formatting to shade cells above your expected volume in red, to show where the business would lose money.

Sheet 4: Chart data

To draw the classic break-even chart, create a table with units from 0 to 600 in steps of 100, and compute revenue and total cost for each.

Select the three columns and insert a line chart with units on the horizontal axis. The two lines cross at the break-even point, at 300 units. Add a vertical marker at the expected volume if you wish.

Checks to build in

• Profit at break-even is zero. Add a check cell =B5*B3-Inputs!B5 and expect a small positive number due to rounding up.
• Revenue at break-even equals fixed costs divided by the contribution ratio, within rounding.
• Inputs are positive and price is above variable cost.
• Version and date are shown on the sheet, with a change log.
• Protect the formulas and leave the input cells unlocked.

Customizing the calculator

Want a working version?

This page shows the structure only. If you would like a working version tailored to your products, cost structure and reporting needs, contact us. For companies that sell products, linking the calculator to the demand plan keeps the volume assumption honest. See cost-volume-profit analysis for the model behind it, and budgeting and forecasting for how break-even targets feed the annual budget.