If you know how to track the performance of your business finances, you’ll have a better idea of what you should do next. For anyone running their own business, Microsoft Excel may be a highly useful bookkeeping tool. In this article, I illustrated several simple and effective methods for profit and loss percentage formula in Excel. For your better understanding, we will get help from a sample dataset. The data set contains Product, Cost Price, and Selling Price. We will calculate, Profit/Loss, Amount, and Percentage.
How to Calculate Profit and Loss Percentage Formula in Excel: 4 Ways
We can use the following general mathematics formula to determine percent profit or loss.
= (Gain or loss/previous value*100)
Method 1: Profit and Loss Percentage Formula from Cost price and Sell Price
In this method, we will use the mathematical formula subtraction to simply get the result of profit or loss and then use percentage formatting from the Number Format ribbon.
First, click on cell E5 and type the following formula.
=D5-C5
Now, press ENTER key.
Now, we will use AutoFill to fill the rest of the series. So, right-click the mouse button and drag it down.
As a result, we got our profit or loss amount. Where, positive value indicates Profit and negative value indicates Loss.
Now, click on cell F5 and type the following formula.
=E5/C5
Now, press the ENTER key.
Finally, right-click the mouse button and drag it down to fill the rest of the series.
Here, we are calculating the amount of profit or loss by subtracting the Cost Price from the Selling Price. Then, we are dividing the Profit or Loss amount by the Cost Price. Now, we can multiply it by a hundred to get the percentage. But, Excel has an in-built feature to do it automatically.
Now, select the range from F5 to F9. After that, go to the Home tab and click the percentage sign (%) as the following image shows.
Finally, our result is ready and it looks like the following image.
Related Content: How to Calculate Gross Profit Margin Percentage with Formula in Excel
Method 2: Profit and Loss Percentage Formula in Excel
We will calculate Profit and Loss according to the mathematical formula.
Now, click on cell F5 and type the following formula.
=(D5-C5)/C5
Here, firstly we subtracted Cost Price from Selling Price then divided it with the Cost Price to calculate the percentage of profit or loss.
Now, press ENTER key.
After that, drag down to AutoFill.
Now, go to the home tab and select the percentage as shown in the image.
Done. Our result is as follows.
Here, positive value indicates Profit and negative value indicates Loss.
Related Content: How to Calculate Net Profit Margin Percentage in Excel
Method 3:Profit and Loss Percentage Formula with Conditional Formatting
We can use Conditional Formatting also to calculate Profit and Loss Percentage in Excel. Let me show you, how it works.
First of all, we will follow all the procedures shown in method 1. After completing, our result will look like this.
Now, we will select the range E5 to E9.
Now, go to the Home tab, then Conditional Formatting and select New Rule. As, the following image shows.
After that, a new dialogue box will pop up like this.
From the dialogue box, we will select Format only cells that contain then in Edit the Rule Description we will select less than.
Now, we will put value 0 as we want negative values to be formatted which indicates loss.
After that, click on the Format option and another dialogue box will pop up. From that box, we will select Fill in the header tab and select the color we prefer then click OK then again click OK in the New Formatting Rule dialogue box.
Finally, our result will look like the following image.
Here, Excel has formatted cells containing negative values. Which indicates a loss.
Simple. Isn’t it.
Method 4:Â Profit and loss percentage Formula along with IF Function
In this method, we will see, how we can use the IF function along with profit and loss percentage formula.
First, click on cell E5 and type the following formula.
=IF(D5=C5,"No profit,No loss",IF(D5<C5,"Loss",IF(D5>C5,"Profit")))
Here, we are telling Excel with the IFÂ function to yield result No profit No Loss when Cost Price equals to Selling price, Profit when Cost price is less than Selling Price, and Loss when Cost price is greater than Selling Price.
Now, press ENTER key.
After that, simply drag it down using right click button in the mouse to AutoFill rest of the series.
After that, we will simply follow method 1.As a result, our data set at the end will look like the following image.
That’s all.
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, I’ve attached a practice workbook where you may practice these methods.
Download Practice Workbook
Conclusion
There are four different ways to calculate accrued interest on a loan in Excel. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback. You may also browse this site’s other Excel-related topics.
