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

**Table of Contents**hide

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

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

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

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

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

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.

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

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