How to Calculate Percentage Return on Investment in Excel

Excel is extremely useful for data analysis, data interpretation, and business analytics. The Percentage Return on Investment is one of the most important key features of business analytics. In this article, I will show you 2 practical examples to calculate the percentage return on investment in Excel.


Download Practice Workbook

You can download our practice workbook from here for free!


What Is Percentage Return on Investment?

The percentage Return on Investment is mainly the measurement of the profitability of an investment. It is measured by dividing the net return of an investment and the cost of the investment.

So, the formula for calculating the percentage rate of investment would be:

Percentage Return on Investment = (Net Return / Cost of Investment)

And, if you take time into the consideration, you have to calculate the Annualized Return on Investment. In this case, the formula would be:

Percentage Annualized Return on Investment = [(Total Sold Value / Total Investment Value) (1/Number of Years) – 1

2 Practical Examples to Calculate Percentage Return on Investment in Excel

Example 1: When Total Investment and Sell Values Are Given

Say, you are given the total investment and total sold value. Now, you need to calculate the percentage of Return on Investment. Follow the steps below to do this.

Sample Dataset to Calculate Percentage Return on Investment in Excel

πŸ“Œ Steps:

  • First and foremost, click on cell C7.
  • Following, insert the formula below.
=(C6-C5)/C5
  • Subsequently, press the Enter key.

Formula to Calculate Percentage Return on Investment in Excel

Thus, you will get your desired result as the percentage ROI of this case. Besides, you can use this outcome as a Percentage Return on Investment calculator.

Read More: How to Apply Percentage Formula for Multiple Cells in Excel (5 Methods)


Example 2: When Unit Investment and Sell Values Are Given

Now, it might happen that, you are not given the total investment and sold value. Rather, you are given a scenario of an investment with the unit cost and selling price of shares. In this case, go through the steps below to calculate the percentage of Return on Investment in Excel.

Sample Dataset with Unit Investment and Selling Price

πŸ“Œ Steps:

  • At the very beginning, you need to calculate the total investment. To do this, click on cell C11 and insert the following formula.
=C6*C7
  • Subsequently, press the Enter key.

Formula to Calculate Total Investment

  • Afterward, click on cell C12 and insert the formula below to calculate the total sold value.
=C9*C10
  • Following, hit the Enter key.

Formula to Calculate Total Sold Value

  • Finally, click on cell C13 and insert the formula below to calculate the percentage Return on Investment in Excel.
=(C12-C11)/C11
  • Following, hit the Enter key.

Formula to Calculate Percentage Return on Investment in Excel

As a result, you will get the percentage Return on Investment. And, the final outcome would look like this.

Calculated Percentage Return on Investment in Excel

Read More: Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)


Similar Readings


How to Calculate the Annualized Percentage Return on Investment in Excel for Multiple Years

Now, there is a limitation of ROI that, it does not take time into consideration. Now, if you want to calculate ROI for multiple years and compare them correctly, you will need to calculate the Annualized Percentage Return on Investment. Follow the steps below to calculate this.

πŸ“Œ Steps:

  • Similar to Example 2, calculate the total investment and total sold value first.
  • Following,Β  click on cell C13 and insert the following formula.

=((C12/C11)^(1/(C8-C5)*365))-1

  • Subsequently, hit the Enter key.

Formula to Calculate Annualized Percentage Return on Investment in Excel

Thus, you will get your annualized percentage ROI which varies with time. So, you can get multiple results for multiple years.

Read More: How to Use IF and Percentage Formula in Excel (2 Easy Ways)


How to Make an ROI Percentage Chart in Excel

Furthermore, sometimes you may need to make an ROI percentage chart in Excel for better visualization and interpretation. Say, you have ROI percentages for 4 consecutive months of a year. Now, you want to create a chart from these. Follow the steps below to accomplish this.

Sample Dataset Containing 4 ROI

πŸ“Œ Steps:

  • First, select cells C7:E7 >> go to the Insert tab >> click on Insert Column or Bar Chart tool >> select the Clustered Column option.

Insert a Chart for ROI Values

  • As a result, a chart containing the ROI percentages will appear.
  • Now, right-click on the chart area and choose the Select Data… option from the context menu.

Choose the Select Data Option

  • Consequently, the Select Data Source window will appear.
  • Following, click on the Edit button on the Horizontal (Category) Axis Labels pane.

Select Data Source Window

  • As a result, the Axis Labels window will appear.
  • Subsequently, at the Axis label range: option, refer to cells C4:F4.
  • Following, click on the OK button.

Axis Labels Window

  • As a result, the Select Data Source window will appear again with proper horizontal labels.
  • Subsequently, click on the OK button.

Finalize the Select Data Options

  • As a result, your chart is ready.
  • Finally, reshape your chart as your need and change the Chart Title to ROI Percentage.

Change the Chart Title

Consequently, you will get your required ROI percentage chart with data. And, the final outcome should look like this.

ROI Percentage Chart

Read More: Calculate Percentage Using Absolute Cell Reference in Excel (4 Methods)


πŸ’¬ Things to Remember

  • You must keep in mind that if you take time into consideration, you have to calculate the Annualized Percentage Return on Investment.
  • The Percentage ROI is calculated in different ways in statistics.

Conclusion

So, in this article, I have shown you 2 practical examples to calculate the percentage Return on Investment in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. And, you are very welcome to comment here if you have any further questions or recommendations regarding this article.

Visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo