Musiha Mahfuza Mukta
Sometimes you may need to calculate Return on Investment (ROI) percentage in Excel. In this article, I will explain how to calculate ROI percentage in Excel using Microsoft 365 version.
Download Practice Workbook
You can download the practice workbook from here:
What Is ROI Percentage?
The Return on Investment (ROI) percentage is basically a ratio of how much you have earned the profit for investment. The basic theory of ROI is the ratio of net profit and cost of investment.
For example, a 35% ROI percentage means, if you invest 50,000$ on any share, after a certain period of time you will sell that share for a cost of 65,000$. Which means you made a profit of 15,000$.
6 Methods to Calculate ROI Percentage in Excel
Here, I will describe the 6 suitable methods on how to calculate ROI percentage in Excel. Also, for your better understanding, I’m going to use the following dataset. Which contains two columns with Investment Details.
Now, I will find out the ROI percentage for the following dataset.
1. Use of Generic Formula to Calculate ROI Percentage
You can employ the generic formula to calculate Return on Investment (ROI) percentage in Excel.  Additionally, I’m going to use the following formula.
Now, let’s talk about the steps.
📌 Steps:
- Firstly, select the C9 cell where you want to keep the calculated ROI percentage.
- Secondly, from the Home tab >> go to the Number option.
- Thirdly, choose Percentage (%).
So, whatever value you insert in the C9 cell, Excel will return that in Percentage form.
- Now, select cell C9 where you want to calculate the ROI percentage.
- Then, use the corresponding formula in the C9 cell.
=(C7-C5)/C5Where the C7 cell contains the Sales value and the C5 cell contains the Investment value.
- At this time, press ENTER to get the result.
Lastly, you will see the ROI percentage.
Read More: How to Fill Formula Down to Specific Row in Excel (7 Easy Methods)
2. Calculating ROI Percentage for Capital Gain in Excel
In this section, I will calculate the ROI percentage using Capital gain. Basically, the capital gain is considered as an economic term which denotes the profit earned by any asset that holds an increased sales value over a period of time. Now, let’s talk about the steps.
📌 Steps:
- Firstly, select a different cell C6 where you want to find the capital gain.
- Secondly, use the corresponding formula in the C6 cell.
=C5-C4Where the C5 cell contains the Sales value and the C4 cell contains the Investment value.
- Thirdly, press ENTER.
- Now, select the C8 cell where you want to keep the calculated ROI percentage.
- Then, from the Home tab >> go to the Number option.
- Lastly, choose Percentage (%).
As a result, whatever you insert in the C8 cell, Excel will return that in Percentage form.
At this time, I will calculate the ROI percentage. For that, I will use the following formula.
- So, select cell C8 where you want to calculate the ROI percentage.
- Then, use the corresponding formula in the C8 cell.
=C6/C4Where the C6 cell contains the Capital gain and the C4 cell contains the Investment value.
- Subsequently, press ENTER.
Finally, you will see the ROI percentage.
Read More: How to Drag Formula in Excel with Keyboard (7 Easy Methods)
Similar Readings
- How to Drag Formula and Ignore Hidden Cells in Excel (2 Examples)
- Fill Down to Next Value in Excel (5 Easy Methods)
- [Fixed!] Excel VLOOKUP Drag Down Not Working (11 Possible Solutions)
- How to Enable Drag Formula in Excel (With Quick Steps)
3. Calculation of ROI Percentage in Excel for Net Income
On the other hand, you can calculate the ROI percentage using Net Income. Now, let’s talk about the steps. Here, you have to find out the Capital gain and then include the other earnings.
📌 Steps:
- Firstly, select a different cell C6 where you want to find the capital gain.
- Secondly, use the corresponding formula in the C6 cell.
=C5-C4Where C5 cell contains the Sales value and C4 cell contains the Investment value.
- Thirdly, press ENTER.
As a result, you will get the value of capital gain.
- Now, select another cell C8 where you want to keep the Net Income.
- Then, use the following formula in the C8 cell.
=C6+C7Here, the C6 cell contains the Capital gain value and the C7 cell contains the Dividends value.
- After that, press ENTER.
So, you will get the value of net income.
Now, I will calculate the ROI percentage. For that, I’m going to use the following formula.
- So, select cell C10 where you want to calculate the ROI percentage.
- Then, use the corresponding formula in the C10 cell.
=C8/C4Where C8 cell contains the net income and C4 cell contains the Investment value.
- Subsequently, press ENTER and convert it into percentage form using the Number format.
Finally, you will see the ROI percentage.
4. Calculating ROI Percentage for Annualized Return in Excel
Here, I will calculate the annual ROI percentage in Excel. To do so, firstly, I have to find out the number of years in between the investment time and selling time.
📌 Steps:
- Firstly, select a blank cell C9 where you want to keep the number of years.
- Secondly, use the corresponding formula in the C9 cell.
=(C6-C4)/365Where the C6 cell contains the Share selling date and the C4 cell contains the Share investing date. By subtracting two dates you will get the total days in between them. So, you must divide by 365 to find the number of years.
- Thirdly, press ENTER.
As a result, you will get the total number of years of holding the share.
Now, I will calculate the annual ROI percentage. For that, I’m going to use the following formula.
- So, select cell C10 where you want to calculate the ROI percentage.
- Then, use the corresponding formula in the C10 cell.
=(C7/C5)^(1/C8)-1Where C8 cell contains the number of years and the C7, and C4 cells contain the Sales value and Investment value respectively.
- After that, press ENTER and convert it into percentage form using the Number format under the Home tab.
Finally, you will see the ROI percentage.
Read More: How to Add or Subtract Based on Cell Value in Excel (3 Ways)
5. Use of RATE Function to Calculate Annual ROI Percentage
You can use the RATE function to calculate the annual ROI percentage in Excel. To do so, firstly, you have to find out the number of years between the investment time and selling time. The steps are given below.
📌 Steps:
- Firstly, select a blank cell C8 where you want to keep the number of years.
- Secondly, use the corresponding formula in the C8 cell.
=(C6-C4)/365Here C6 cell contains the Share selling date and the C4 cell contains the Share investing date. By subtracting two dates you will get the total days in between them. So, you must divide by 365 to find the number of years.
- Thirdly, press ENTER.
As a result, you will get the total number of years of holding the share.
- Now, select another cell C10 where you want to keep the ROI percentage.
- Then, use the corresponding formula in the C10 cell.
=RATE(C8,0,-C5,C7)- Subsequently, press ENTER.
Lastly, you will get the annual ROI percentage.
Formula Breakdown
Here, the RATE function will return the annual rate in percentage of investment.
- Now, C8 denotes the NPER as the share-holding period.
- Then, 0 denotes that the annual payment is unknown.
- Then, C5 denotes the Investment, and the Negative sign denotes that you have made the payment.
- After that, C7 denotes the Sales value.
Similar Readings
- [Fixed!] Excel Drag to Fill Not Working (8 Possible Solutions)
- How to Drag Formula Horizontally with Vertical Reference in Excel
- Fill Across Worksheets in Excel (3 Quick Ways)
- How to Fill Down to Last Row with Data in Excel (3 Quick Methods)
6. Each Year ROI Percentage Calculation in Excel
You can employ the generic formula to calculate Return on Investment (ROI) percentage in Excel.  Additionally, I’m going to use the following formula.
Now, let’s talk about the steps.
📌 Steps:
- Firstly, select the D5 to D7 cells where you want to keep the calculated ROI percentages.
- Secondly, from the Home tab >> go to the Number option.
- Thirdly, choose Percentage (%).
So, whatever you insert in the D5 to D7 cells, Excel will return that in Percentage form.
- Now, select cell D5 where you want to calculate the ROI percentage.
- Then, use the corresponding formula in the D5 cell.
=(C5-$C$10)/$C$10Where C5 cell contains the Ending value and the $C$10 cell contains the Beginning value. Here, the Dollar sign ($) has fixed up the position of the C10 cell.
- After that, press ENTER.
- Now, to find out all the ROI percentages, drag the Fill Handle icon (Small Plus (+) Sign on the bottom right corner).
As a result, you will see all the ROI percentages after every year.
Read More: How to Use Fill Handle in Excel (4 Quick Tricks)
✅ Tips for Calculation of ROI Percentage in Excel
Here, you may need to increase or decrease the decimal places. You can do it in a simple way. Explore the Number section of the Home tab, you will find the Increase Decimal and Decrease Decimal options. So, you can choose what you prefer to use.
As an example, you can see, I use the Increase Decimal option. As a result, you can see by increasing the decimal places the value has been updated. Here, Excel will do this Rand calculation by itself. According to your need, you can use Increase Decimal or Decrease Decimal.
Read More: Drag Number Increase Not Working in Excel (A Solution with Easy Steps)
Practice Section
Now, you can practice the explained method by yourself.
Conclusion
I hope you found this article helpful. Here, I have explained 6 suitable methods about how to calculate ROI Percentage in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
