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

## How to Calculate Net Profit Margin Percentage in Excel: Easy Steps

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.

## Step 1: Calculating Gross Profit in Excel

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

## Step 2: Calculating 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**.

## Step 3: Calculating 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**.

## Step 4: Calculating Net Profit in Excel

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

## Step 5: Calculating Net Profit Margin Percentage in Excel

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 Margin Percentage in Excel

**Download Practice Workbook**

## 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.