Net Income Percentage in Excel

What is Net Income Percentage?

Net income percentage, also known as net profit margin, is a financial ratio that measures how much of the total revenue is left as profit after deducting all the expenses, taxes, and interest. It shows how efficiently a company can generate profit from its sales.

The formula for net income percentage is:

Net Income Percentage=Total RevenueNet Income​×100%

Where:

• Net income is the difference between total revenue and total expenses, including cost of goods sold, operating expenses, interest, taxes, and other income or losses.
• Total revenue is the amount of money earned from selling goods or services.

How to Calculate Net Income Percentage in Excel?

To calculate net income percentage in Excel, you need to have the data for net income and total revenue in separate cells. Then, you can use the following steps:

1. Select an empty cell where you want to display the net income percentage.
2. Enter the formula =net_income/total_revenue, where net_income and total_revenue are the cell references for the net income and total revenue values, respectively. For example, if net income is in cell B2 and total revenue is in cell B3, enter =B2/B3.
3. Press Enter to complete the formula.
4. On the Home tab, in the Number group, click the percentage symbol (%) to apply a percentage format to the cell. Alternatively, you can press Ctrl+Shift+5.

Example of Net Income Percentage in Excel

Let’s say you want to calculate the net income percentage for a company that has the following data for the year 2023:

To find the net income percentage, you need to first calculate the net income by subtracting the total expenses from the total revenue, and adding the other income and subtracting the other expenses. The formula for net income is:

$$\text{Net Income}=\text{Total Revenue}-\text{Total Expenses}+\text{Other Income}-\text{Other Expenses}$$

In Excel, you can enter the following formula in cell B9 to calculate the net income:

=B2-(B3+B4+B5+B6)-(B8-B7)

The result is $155,000.

Then, you can enter the following formula in cell B10 to calculate the net income percentage:

=B9/B2

The result is 0.31.

Finally, you can format the cell B10 as a percentage by clicking the percentage symbol (%) on the Home tab or pressing Ctrl+Shift+5. The result is 31%.

The net income percentage of 31% means that for every dollar of revenue, the company earns 31 cents of profit.

Other Approaches to Calculate Net Income Percentage in Excel

There are other ways to calculate net income percentage in Excel, such as using the SUM function, the SUMPRODUCT function, or a pivot table. Here are some examples of how to use these approaches:

• Using the SUM function: You can use the SUM function to add or subtract multiple values in one formula. For example, you can enter the following formula in cell B9 to calculate the net income:

=SUM(B2,-B3,-B4,-B5,-B6,B7,-B8)

The result is the same as before, $155,000.

• Using the SUMPRODUCT function: You can use the SUMPRODUCT function to multiply and add arrays of values in one formula. For example, you can enter the following formula in cell B9 to calculate the net income:

=SUMPRODUCT(B2:B8,{1,-1,-1,-1,-1,1,-1})

The result is the same as before, $155,000.

• Using a pivot table: You can use a pivot table to summarize and analyze the data in a table. For example, you can create a pivot table from the data in cells A1:B8 by following these steps:
  1. Select the data range A1:B8.
  2. On the Insert tab, in the Tables group, click PivotTable.
  3. In the Create PivotTable dialog box, choose where you want to place the pivot table, such as a new worksheet or an existing worksheet. Click OK.
  4. In the PivotTable Fields pane, drag the Item field to the Rows area, and the Amount field to the Values area.
  5. Right-click any cell in the Values area, and click Summarize Values By > Sum.
  6. Right-click any cell in the Values area, and click Show Values As > % of Grand Total.

The result is a pivot table that shows the net income percentage for each item, as well as the total net income percentage. The total net income percentage is the same as before, 31%.