Before you launch a product, sign a lease or hire, it helps to know how much you must sell just to cover costs. This guide shows how to calculate break-even point in Excel, then go one step further to find the sales needed for a profit target.
1. Separate fixed and variable costs
Fixed costs stay the same whatever you sell in a month: rent, salaries, software. Variable costs rise with each unit: materials, packaging, delivery, payment fees. List both clearly.
2. Work out contribution per unit
Contribution per unit is selling price minus variable cost per unit. It is the amount each sale contributes towards fixed costs and then profit.
Example
| Input | Value |
|---|---|
| Fixed costs per month | ₹60,000 |
| Price per unit | ₹500 |
| Variable cost per unit | ₹300 |
| Contribution per unit | ₹200 |
| Break-even | 300 units, ₹1,50,000 revenue |
| For ₹40,000 profit | 500 units |
Selling 400 units a month would give a margin of safety of 25%.
3. Calculate break-even units and revenue
Break-even units = fixed costs ÷ contribution per unit. Break-even revenue = break-even units × price. In Excel, wrap the division in IFERROR in case contribution is zero.
4. Add a profit target and margin of safety
Units for a target profit = (fixed costs + target profit) ÷ contribution per unit. Margin of safety = (expected sales − break-even sales) ÷ expected sales. It shows how far sales can fall before you make a loss.
Common mistakes
Treating all costs as fixed
Payment fees and shipping usually scale with sales. Classifying them correctly changes the answer.
Using one price for a mixed range
If you sell several products, use a weighted average price and variable cost based on your expected sales mix.
FAQs
What does break-even mean?
It is the level of sales where total revenue equals total costs, so profit is zero.
Can a service business use break-even?
Yes. Use billable hours or projects as the unit, with the price and variable cost per hour or project.
Why is my break-even so high?
Either fixed costs are high or contribution per unit is low. Raising price, cutting variable cost or reducing overheads all lower it.
Get the template
The ReadySheets Break-even Calculator works out break-even units and revenue, sales for your target profit and margin of safety, with a break-even chart. Pair it with the Pricing Calculator.
Related guides: How to Price a Product for Profit (with an Excel Pricing Calculator) · How to Make a Profit and Loss Statement in Excel