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.
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+D7Here, 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+D9Here 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.2The 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.30The 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+D13Here, 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 RevenuesWhen 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/D6It 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.
