Tanjim Reza
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:
And, if you take time into the consideration, you have to calculate the Annualized Return on Investment. In this case, the formula would be:
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.
📌 Steps:
- First and foremost, click on cell C7.
- Following, insert the formula below.
=(C6-C5)/C5- Subsequently, press the Enter key.
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.
📌 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.
- Afterward, click on cell C12 and insert the formula below to calculate the total sold value.
=C9*C10- Following, hit the Enter key.
- 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.
As a result, you will get the percentage Return on Investment. And, the final outcome would look like this.
Read More: Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)
Similar Readings
- How to Calculate Percentage of Percentage in Excel (2 Examples)
- Calculate Annual Growth Rate in Excel (3 Methods)
- How to Calculate Percentage of Completion in Excel (3 Methods)
- Calculate Gross Profit Margin Percentage with Formula in Excel
- How to Calculate Savings Percentage in Excel (3 Easy Ways)
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.
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.
📌 Steps:
- First, select cells C7:E7 >> go to the Insert tab >> click on Insert Column or Bar Chart tool >> select the Clustered Column option.
- 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.
- Consequently, the Select Data Source window will appear.
- Following, click on the Edit button on the Horizontal (Category) Axis Labels pane.
- 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.
- As a result, the Select Data Source window will appear again with proper horizontal labels.
- Subsequently, click on the OK button.
- As a result, your chart is ready.
- Finally, reshape your chart as your need and change the Chart Title to ROI Percentage.
Consequently, you will get your required ROI percentage chart with data. And, the final outcome should look like this.
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
- How to Calculate Percentage in Excel Based on Cell Color (4 Methods)
- Calculate Percentage of a Number in Excel (5 Easy Ways)
- How to Calculate Total Percentage in Excel (5 Ways)
- Calculate Average Percentage in Excel (3 Easy Methods)
- Percentage Difference Between Two Percentages in Excel
- Use Excel Formula to Calculate Percentage of Grand Total
- How to Calculate Contribution Percentage with Formula in Excel
