How to Calculate ROI Percentage in Excel (6 Easy Ways)

Sometimes you may need to calculate the Return on Investment (ROI) percentage in Excel. In this article, I will explain how to calculate ROI percentage in Excel using Microsoft 365 version.

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

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. Using Generic Formula to Calculate ROI Percentage

You can employ the generic formula to calculate the 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.

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

2. Calculating ROI Percentage for Capital Gain in Excel

In this section, I will calculate the ROI percentage using Capital Gain. Basically, capital gain is considered as an economic term that 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.

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

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

3. Calculating 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.

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

Here, 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.

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

Where the C6 cell contains the Date of Selling Share and the C4 cell contains the Date of Buying Share. 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.

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

5. Using 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.

Here C6 cell contains the Date of Selling Share and the C4 cell contains the Date of Buying Share. 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 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.

• Subsequently, press ENTER.

Lastly, you will get the annual ROI percentage.

6. Estimating Each Year’s ROI Percentage in Excel

You can employ the generic formula to calculate the 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.

Where 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 Calculate Expected Return in Excel

✅ 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, that 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.

Practice Section

Now, you can practice the explained method by yourself.

Download Practice Workbook

You can download the practice workbook from here:

Conclusion

I hope you found this article helpful. Here, I have explained 6 suitable methods for how to calculate ROI Percentage in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to ROI Calculation in Excel | Excel for Finance | Learn Excel