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.

**Table of Contents**Expand

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

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

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

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

`=C5-C4`

Where the** C5 **cell contains the** Sales** **Value** and the **C4 **cell contains the **Investment** **V****alue.**

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

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

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

`=C5-C4`

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 **C****apital 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 **N****et 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 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 **Date of Selling Share** and the **C4 **cell contains the **D****ate 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.

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

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

`=(C6-C4)/365`

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** **Y****ears**.

- Thirdly, press
**ENTER**.

As a result, you will get the **Nu****mber of** **Y****ears**. 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**.

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

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

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