In any business organization, large or small, you need to know some bookkeeping basics, e.g. how to calculate profit or loss. Profit is the difference between the selling price and the cost price of the product. Profit, divided by the cost price then multiplied by 100 gives the profit percentage. In this article, we will show 3 methods to calculate profit percentage in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Methods to Calculate Profit Percentage in Excel
We will show how to calculate 3 types of profit margin percentages with examples in this section.
Assume that we have the following dataset of a clothing store with their selling price and the cost.
1. Gross Profit Percentage Formula in Excel
Gross profit is the simplest form of profit. We just deduct the cost of the product from the total revenue, and we get this. We do not consider other costs of business in this profit margin. It is a preliminary profit idea.
Now we will show the calculation process.
- First, we’ve added two more columns to show profit and percentage.
- Now we will find out the profit using the Price and Cost. Go to Cell E4 & put the following formula.
=C4-D4
- Now, drag the Fill Handle icon.
Here, we get the profit by subtracting cost from revenue.
- Now, we will find out the percentage. Divide the profit by the price or revenue. Go to Cell F4 Then type the below formula.
=E4/C4
- Now, double click the Fill Handle icon.
- You can see that we’ve got the results in decimal form. Now, we will convert this value into percentage form. Select all the cells of the Percentage column.
- Then, choose the Percentage (%) format from the Number group.
Now, look at the result.
Finally, we get the Gross Profit Percentage.
Read More: How to Calculate Gross Profit Margin Percentage with Formula in Excel
2. Calculate Operating Profit Percentage in Excel
We will get the Operating profit by deducting the operating cost and the cost of the product from the revenue. The operating cost includes transportation, the salary of employees, rent, marketing costs, and maintenance costs. The total operating cost is also known as SG&A.
The formula for Operating Profit is as follows:
Now, we see the calculation process of the operating profit. In the below dataset, we have different operating costs from the product cost.
We will find out the operating profit by following the steps below.
Steps:
- First, we will find out the total operating cost using the SUM function. Go to Cell C11.
- Write down the following formula.
=SUM(C6:C9)
- Press Enter to get the result.
We get the total operating cost here.
- We will find out the operating profit by subtracting the cost of goods and operating costs from the revenue. Put the following formula on Cell C12.
=C4-C5-C11
- Again, hit the Enter button.
- Now, divide the profit by the revenue.
- Divide the profit by the revenue. Put the following formula on Cell C13.
=C12/C4
- Finally, press the Enter button.
Read More: How to Use Profit and Loss Percentage Formula in Excel (4 Ways)
Similar Readings
- How to Calculate Dividend Growth Rate in Excel (3 Methods)
- How to Calculate Total Percentage in Excel (5 Ways)
- Calculate Average Percentage in Excel [Free Template+Calculator]
- How to Calculate Grade Percentage in Excel (3 Easy Ways)
- How to Calculate Percentage of Sales in Excel (5 Suitable Methods)
3. Determine Net Profit Percentage
In this example, we will show how to determine the Net profit. We know that we need to pay a certain amount of tax to the authority. Also, the company has to pay interest on bank loans. This net profit is determined after deducting all the tax and interest along with the remaining costs.
Steps:
- We have tax and interest in our dataset. And the operating cost already calculated.
- We have tax and interest in our dataset. And the operating cost has already been calculated.
- Determine the profit by subtracting the tax, interest, and other costs. Put the formula on Cell C14.
=C4-C5-C13-C10-C11
- Press the Enter button to execute.
- Go to Cell C15.
- Put formula on that cell.
=C14/C4
- Hit the Enter button again. Convert the result into a percentage.
Read More: How to Calculate Net Profit Margin Percentage in Excel
Conclusion
In this article, we described how to calculate the profit percentage in Excel. We showed three different types of profit calculation here. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
Related Articles
- How to Use the Exponential Growth Formula in Excel (2 Methods)
- How to Calculate Percentage of a Number in Excel (5 Easy Ways)
- Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)
- How to Calculate Percentage Increase from Zero in Excel (4 Methods)
- How to Calculate Percentage Change with Negative Numbers in Excel
- Calculate Reverse Percentage in Excel (4 Easy Examples)