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

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.

Dataset for How to Calculate ROI Percentage in Excel


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.

ROI = [(Sales value – Investment value) / Cost of investment]

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.

Use of Generic Formula to Calculate ROI Percentage in Excel

  • Now, select cell C9 where you want to calculate the ROI percentage.
  • Then, use the corresponding formula in the C9 cell.
=(C7-C5)/C5

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.

Result of using Generic formula for How to Calculate ROI Percentage in Excel

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-C4

Where the C5 cell contains the Sales value and the C4 cell contains the Investment value.

  • Thirdly, press ENTER.

Calculating ROI Percentage for Capital Gain in Excel

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

ROI = Capital gain / Cost of Investment
  • So, select cell C8 where you want to calculate the ROI percentage.
  • Then, use the corresponding formula in the C8 cell.
=C6/C4

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.

Read More: How to Drag Formula in Excel with Keyboard (7 Easy Methods)


Similar Readings


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-C4

Where C5 cell contains the Sales value and C4 cell contains the Investment value.

  • Thirdly, press ENTER.

Calculate the ROI Percentage in Excel for Net Income

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+C7

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.

ROI = Net income / Cost of investment 
  • So, select cell C10 where you want to calculate the ROI percentage.
  • Then, use the corresponding formula in the C10 cell.
=C8/C4

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 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)/365

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

How to Calculate ROI Percentage for Annualized Return in Excel

Now, I will calculate the annual ROI percentage. For that, I’m going to use the following formula.

ROI = [(Sales value / Investment value) ^ (1 / Number of years)] – 1
  • 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)-1

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.

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)/365

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

Use of RATE Function to Calculate Annual ROI Percentage in Excel

  • 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


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.

ROI = [(Ending value – Beginning value) / Cost of Investment]

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.

Each Year ROI Percentage Calculation in Excel

  • 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$10

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

Tips for Calculation of ROI Percentage in Excel

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.

Practice Section for How To Calculate ROI Percentage in Excel


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.


Related Articles

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo