This tutorial will demonstrate how to calculate** break-even** analysis using formula in Excel. **Break-even** is one of the most important parameters for running a business company. When you start a business, the first target becomes that you try to run your business without any loss. For that, you need to know the **break-even** **point in excel** for your business. This is the best parameter to determine whether the business is running profitably or with a loss. So, it is extremely essential to perform** a break-even** analysis Excel formula in **Excel**.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here.

## What Is Break Even Analysis?

While running a business, you have to measure whether your company is at a loss or not. To determine that, the most useful method is the **break-even** analysis. Break-even analysis means your earnings are equal to the money you have spent in the business. In simple words, if the profit is exactly zero then it is called the **break-even** point. If you have a profit higher than the **break-even** point, then you are running your business profitably but if you have a profit lower than the **break-even** point, then you are running your business at a loss. So, with the **break-even** analysis, you can easily understand the situation of your company and make decisions accordingly.

## 3 Effective Methods to Perform Break Even Analysis with Formula in Excel

Weâ€™ll use a sample dataset overview as an example in Excel to understand easily. If you follow the steps correctly, you should learn how to perform the **break-even** analysis Excel formula in Excel on your own. The methods are described thoroughly below.

### 1. Using Goal-Seek Feature to Perform Break-Even Analysis

In this case, our goal is to perform the **break-even analysis by using the Goal-Seek feature in Excel**. This feature is very useful in times when we want to find a specific input for the available output. We can learn this method by following the below steps.

**Steps:**

- First, we arranged a dataset like the below image. In this case, we have the
**Parameters**in**column B**and**Values**in**column C**. Moreover, in cells**B5**,**B6**,**B7**,**B8**,**B9**,**B10**, and**B11**we have sequentially**Unit Prices**,**Unit Sold**,**Revenue**,**Cost Per unit**,**Variable****Costs**,Â**Fixed Costs**, and**Profit**.

- Next, insert the following formula in the
**C7 cell**.

`=C5*C6`

- After that, you will get the result for this cell by using the multiplication of two cells.

- Then, insert the following formula in the
**C9 cell**.

`=C6*C8`

- Afterward, you will get the result for this cell with the help of the multiplication of two cells.

- Subsequently, insert the following formula in the
**C11 cell**.

`=C7-C9-C10`

- Furthermore, you will get the result for this cell by using substructions of
**C9**and**C10**cells from the**C7Â**cell.

- Moreover, go to
**Data**>>**What-if Analysis**>>**Goal Seek**options.

- Therefore, in the
**Goal Seek**dialog box, set the cell you want to change( in this case cell**C11**named**Profit**) in the**Set Cell**Moreover, in the**To value**option, select the data you want to achieve. In the**By changing cell**option, insert the cell number you want to use as a source of the change(in this case cell**C5**named**Unit Price**).

- Hence, if you press the
**Enter**button, in the**Goal Seek Status**dialog box, you will get the result and click on the**OK**option there.

- Finally, you will get the result for this cell with the help of the
**Goal Seek**feature in Excel. It shows us the minimum**Unit Price**and minimum**Revenue**at which the profit is zero. If we want to do a profit in our business, we have to mark the**Unit Price**and**Revenue**higher than the results.

Thus, we have performed the **break-even** analysis Excel by using **the Goal-Seek feature **in Excel.

**Read More: How to Do Multi Product Break Even Analysis in Excel**

### 2. Determining Break Even Analysis by Excel Formula

Now, we want to determine **break-even analysis** in Excel by using formulas. We can fulfill our goal by following the below steps.

**Steps:**

- At first, we arranged a dataset like the below image. In this case, we have the
**Parameters**in**column B**and**Values**in**column C**. Moreover, in cells**B5**,**B6**,**B7, B8, B9, B10,**and**B11**we have sequentially**Unit Prices**,**Unit Sold**,**Revenue**,**Cost Per unit**,**Variable****Costs**,Â**Fixed Costs**, and**Profit**.

- Then, insert the following formula in the
**C5 cell**.

`=C10/C6+C8`

- Now, you will get the result for this cell with the help of the division of cells.

- After that, insert the following formula in the
**C7 cell**.

`=C5*C6`

- Next, you will get the result for this cell with the help of the multiplication of two cells.

- Therefore, insert the following formula in the
**C9 cell**.

`=C6*C8`

- Last, you will get the result for this cell with the help of the multiplication of two cells. It shows us the minimum
**Unit Price**and minimum**Revenue**at which the profit is zero. With this, we have found out the minimum**Variable Costs**in the business. If we want to do a profit in our business, we have to mark the**Unit Price**,**Revenue,**and**Variable costs**higher than the results.

Therefore, we have determined the **break-even analysis** in Excel by using formulas.

**Read More: How to Do NPV Break Even Analysis in Excel (with Easy Steps)**

### 3. Break Even Analysis with Excel Chart

Next, we want to determine **break-even analysis in Excel by using charts**. We can fulfill our goal by following the below steps.

**Steps:**

- To begin with, we arranged a dataset like the below image. In this case, we have the
**Parameters**in**column B**and**Values**in**column C**. Moreover, in cells**B9**,**C9**,**D9,**and**E9**we have sequentially**Unit Prices**,**Revenue**,**Costs**, and**Profit**.

- In addition, insert the following formula in the
**C10 cell**.

`=B10*$C$6`

- Furthermore, you will get the result for this cell with the help of the multiplication of two cells and then use the
**Fill Handle**Â to apply the formula to all the cells.

- Subsequently, you will get the proper
**Revenue**for all cells of this column.

- Hence, insert the following formula in the
**D10 cell**.

`=$C$5*$C$6+$C$7`

- Now, you will get the result for this cell with the help of the addition of cells, and then use the
**Fill Handle**Â to apply the formula to all the cells.

- After that, you will get the proper
**Costs**for all cells of this column.

- Thereafter, insert the following formula in the
**E10 cell**.

`=C10-D10`

- As a result, you will get the result for this cell with the help of the subtraction of two cells and then use the
**Fill Handle**Â to apply the formula to all the cells.

- Therefore, you will get the proper
**Profit**for all cells of this column.

- Presently, select the
**data table**and thenÂ go to**Insert tab**.

- Consequently, select the
**Scatter chart**from the**Insert tab**.

- Now, you will get the chart accordingly.

- Additionally, select the intersection point in the chart. Then, right-click on the point and select
**Add Data Callout**from the**Add Data Label**option in Excel.

- Finally, you will get the insertion point marked here in the chart. The result
**875**represents the**break-even unit cost**and**12,070**represents the**break-even Revenue**cost in the chart. If we want to run the business profitably, then we have to keep the**Unit costs**and**Revenue**more than the results we have found.

As a result, we have determined the **break-even analysis** in Excel by using charts.

**Read More: Mortgage Break-Even Analysis in Excel (with Easy Steps)**

## How to Calculate Payback Period in Excel

At this point in the article, our goal is to calculate the payback period in Excel. We can do this by using different formulas in different sections. We want to use **COUNTIF**, **VLOOKUP**, and **ABS functions** in Excel. The **COUNTIF** function counts the number of cells under certain conditions in Excel. The** VLOOKUP** function is mainly used to find a certain value from a range of data. We will use the **ABS** function to get the absolute results. We will describe the whole process by following the below steps.

**Steps:**

- At first, we arranged a dataset similar to the below image. In this case, we have
**Year**,**Cash Inflows**,**Cash Outflows**, and**Net Cash Flow**sequentially in columns**B**,**C**,**D**, and**E**.

- Then, insert the following formula in the
**D12 cell**.

`=COUNTIF(E5:E10,"<0")`

- Moreover, you will get the result for this cell using the
**COUNTIF**function.

- Furthermore, insert the following formula in the
**D13 cell**.

`=VLOOKUP(D12, B5:E10, 4)`

- As a result, you will get the result for this cell using the
**VLOOKUPÂ**function.

- Therefore, insert the following formula in the
**D14 cell**.

`=VLOOKUP(D12+1, B5:E10, 2)`

- So, again, you will get the result for this cell using the
**VLOOKUP**function.

- Hence, insert the following formula in the
**D15 cell**.

`=ABS(D13/D14)`

- Next to that, you will get the result for this cell using the
**ABSÂ**function.

- Additionally, insert the following formula in the
**D16 cell**.

`=D12 + D15`

- Afterward, you will get the result for this cell using the addition of two cells.

- Therefore, insert the following formula in the
**D17 cell**.

`=D16*12`

- Last, you will get the result for this cell using the multiplication of cells.

Therefore, we have calculated the **payback period** in Excel by the use of **Â **the** COUNTIF**,** VLOOKUP**, and **ABS **functions in Excel.

## Conclusion

Henceforth, we have described all methods that will help you to show a **break-even analysis** Excel formula. We will be glad to know if you can execute the task in any other way. Follow the **ExcelDemy** website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.