How to Calculate Break-Even in Excel

By Dustin Thornton

A break-even point is a financial tool that allows a company to forecast the amount of product it must sell to break even or reach zero dollars of income. A break-even analysis can show how many units of product it must sell or how many dollars of revenue it must earn to break even. Calculating a break-even point in Microsoft Excel requires inputting the necessary information to calculate the break-even point and then inputting a simple formula.

Step 1

Open Microsoft Excel on your computer.

Step 2

Type in "Fixed Costs" into the A1 cell in Excel.

Step 3

Type in "Sale Price" into the A2 cell.

Step 4

Enter the "Variable Cost Per Unit" into the A3 cell.

Step 5

Type in "Break-Even Units" into the A4 cell.

Step 6

Enter "Break-Even Sales" into the A5 cell.

Step 7

Enter the fixed cost per month of the business into the B1 cell.

Step 8

Type the selling price per unit into the B2 cell.

Step 9

Enter the cost of each unit into the B3 cell.

Step 10

Type in the following formula into the B4 cell:=B1/(B2-B3)Press "Enter." Excel will calculate how many units you must sell per month to break-even.

Step 11

Type in the following formula into the B5 cell:=B4*B2Press "Enter." Excel will calculate the revenue per month required to break even.

Tips & Warnings

  • Type in the fixed cost per year of your business into the B1 cell to get the annual break-even point.