**Net Profit Margin **is a financial and accounting term that expresses the ratio between net profit and total sales or revenue. It is calculated from **the income statement** of any company. In this article, I’ll show you how you can calculate **Net Profit Margin Percentage **in Excel.

Suppose, you know the sales and expenses of a particular year of your company. Now, I will show you how you can calculate different values of the income statement and finally get the **Net Profit Margin Percentage**.

**Table of Contents**hide

## Download Practice Workbook

## Accounting Format for Calculating Net Profit Margin Percentage

Before calculating, you have to make sure that your dataset is in accounting format. The expressions in the accounting format are slightly different from the general format. The accounting format will allow you to make different account calculations in a proper notion.

If your dataset is imported or previously used, the accounting format can already be set. Otherwise, you have to set the format accounting.

➤ Go to the **Home **menu and check if** Accounting** is selected in the **Number **ribbon. If not selected, then select **Accounting**.

In **Accounting**, the negative numbers are expressed with a first bracket. If you select any number with the first bracket you will see a negative number in the formula bar. In an income statement, negative numbers indicate outflow when the positive numbers indicate inflow.

## Steps to Calculate Net Profit Margin Percentage in Excel

### 1. Calculate Gross Profit

To calculate the **Net Profit Margin **first you have to calculate the gross profit. Gross profit is the difference between total revenue or sales and the cost of goods sold(COGS) of your company. The cost of goods sold is the cost that is directly associated with the production of goods. To calculate the gross profit in the income statement,

➤ Type the following **formula** in cell **D8**,

`= D6+D7`

Here, the formula will add the numbers of cells **D6 **and **D7 **and give the value in cell **D8**.

Remember that, here you are adding the number because you have already inserted the COGS as a negative number. If you have inserted the value as a positive number, you must have subtracted the number from the sales to get the gross profit.

➤ Press **ENTER**.

You will get the gross profit in cell **D8**.

**Read More:** **How to Calculate Gross Profit Margin Percentage with Formula in Excel**

### 2. Calculate Operating Profit

When you subtract the operating expenses from the gross profit, you get the **Operating Profit**. Operating expenses are those expenses of your company that are not directly related to the production of goods but facilitate the production and sales. Administrative cost, Marketing cost, Distribution cost, etc. are included in the operating expenses. Operating Profit is also known as earnings before interests and taxes (EBIT) because all the costs except interests and taxes are included in this cost.

To calculate operating profit,

➤ Type the following formula in cell **D10**,

`= D8+D9`

Here the formula will add the numbers of cells **D8 **and **D9 **and give the value in cell **D10**. The number in **D13 **is a negative number that’s why you need to add these numbers.. If the number is a positive number then you have to subtract the number.

➤ Press **ENTER**.

You will get the operating profit in cell **D10**.

**Related Content:** **How to Use Profit and Loss Percentage Formula in Excel (4 Ways)**

### 3. Calculate Interest and Tax Expenses

You may have incurred interest expenses depending on how you have financed the production of your company. If you get the capital for the production by taking loans from banks then you have to pay interest for those loans. How much interest you have incurred depends on the value you have taken as loans. You may repay the full interest or a part of the interest costs in an accounting period based on the financing policy of your company.

Let’s say, in this period, you will pay 20% of your operating profit to pay the interest expenses. To get the value of the Interest expenses,

➤ Type the following formula in cell **C11**,

`= D10*0.2`

The formula will give the value of the interest expenses in this period you are willing to pay.

➤ Press **ENTER**.

You will get the interest expenses in cell **C11**.

You have to pay taxes on the income of your company. These taxes are not implied on the interests which you pay for debt financing. You can add all the costs associated with tax under the tax expenses in the income statement

Most often the ax rate is higher than the interest rate. This is one of the reasons why big companies finance the production cost by taking a loan. To find out the tax expenses of your company for a period you must exclude the interest expenses from your operating profit and multiply the resulting value with the tax rate. Let’s say the tax rate is 30%.

➤ Type the following formula in cell **C12**,

`= (D10-C11)*0.30`

The formula will give the value of the tax expenses in this period you have to pay.

➤ Press **ENTER**.

You will get the tax expenses in cell **C12**.

Now,

➤ Type the following formula in cell **D13**,

`= -(C11+C12)`

The formula will give the total interest and tax expenses. Here, you have to add a minus sign before the summation as it indicates an outflow.

➤ Press **ENTER**.

You will get the total interest and tax expenses in cell **D13**.

**Related Content:** **How to Calculate Percentage Based on Conditional Formatting (6 Ways)**

**Similar Readings:**

**How to Calculate Variance Percentage in Excel (3 Easy Methods)****Excel Formula to Add Percentage Markup [with Calculator]****How do you Calculate Percentage Increase or Decrease in Excel****Excel formula to calculate percentage of grand total (4 Easy Ways)****Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)**

### 4. Calculate Net Profit

After subtracting all the expenses from total sales, you will get the net profit. So, when you will subtract the total interest and tax expenses from the operating profit you will get the net profit.

To find out the net profit,

➤ Type the following formula in cell **D14**,

`= D10+D13`

Here, the formula will add the numbers of cells **D10 **and **D13 **and give the value in cell **D14**. The number in **D13 **is a negative number that’s why you need to add these numbers. If the value is a positive number then you have to subtract the number.

➤ Press **ENTER**.

You will get the total interest and tax expenses in cell **D13**.

**Read More:** **How to Use Profit and Loss Percentage Formula in Excel (4 Ways)**

### 5. Calculate Net Profit Margin Percentage

The ratio of the net profit and total sales or revenues is known as the **Net Profit Margin**. It gives you the value of the profit achieved from each dollar sale. The formula of the net profit

`Net Profit Margin = Net Profit/Total Sales or Revenues`

When you convert the net profit margin in the percentage you get the net profit margin percentage. It gives the profit achieved from a sale of 100 dollars.

➤ Type the following formula in cell **D15**,

`= D14/D6`

It will give the **Net Profit Margin**.

➤ Press **ENTER**.

You will get the **Net Profit Margin** in cell **D13**.

Now, to convert this number in the percentage,

➤ Click on the **Percentage **icon in the **Number **ribbon of the **Home **tab.

As a result, you will get the **Net Profit Margin Percentage **in cell **C15**.

**Read More:** **How to Calculate Total Percentage in Excel (5 Ways)**

## Conclusion

In this article, I’ve shown you step to step guidelines for calculating the **Net Profit Margin**. I hope now you know how to calculate the **Net Profit Margin Percentage** in Excel. By following this article you’ll be also able to prepare the Income statement of your company. If you have any confusion please feel free to leave a comment.