In this article, we’ll learn how to calculate the ROI in Excel. ROI or Return On Investment, is a financial ratio used to determine the benefit of an investment. In simple terms, it is used to find an investor’s profit or loss compared to the investment made.
The higher the ROI, the more profitable the investment. The Return On Investment(ROI) can also be negative demonstrating a loss, resulting in a bad investment.
Note: Mathematically ratio cannot be negative, but it’s a way of expression of a loss.
ROI is a very commonly used term. For example, ROI of a business from the investor’s point of view. ROI of any sort of buy or sell in daily life, and even ROI of college studies (fees paid to what you have gained or earned).
ROI is a ratio, but its percentage (%) expression is also widely used, as it’s easy to interpret. Expressed as ROI%.
Note: ROI is independent of time. It does not take the investment time or duration into consideration while calculating.
Calculate ROI in Excel – Mathematical Formula
Return On Investments (ROI) is very easy to calculate, and has a very simple formula:
Example:
Jack invested $10,000 in bitcoin and after 1 year sells it for $50,000. What would the ROI of his investment be?
Solution: Substituting in values in the above formula we get:
Net Profit/Loss (here Profit) from the investment: ($50,000) Total Return – ($10,000) Invested = $40,000
ROI = (Net Profit or Loss) / Total Invested = $40,000/$10,000 = 4
Now, as already mentioned above, that for ease of understanding, ROI is commonly expressed as a percentage:
ROI% = ROI (ratio) x 100%
ROI or ROI% = 400%
Jack made a profit of 400% on his investment or he quadrupled (4x) his money by his investment in bitcoin.
Steps to Calculate ROI in Excel
- Input the relevant data for which you want to calculate Return On Investment(ROI), and label them accordingly for example:
Use “Investment Amount” for the invested value and “Total Return” for the amount gained or lost on the investment.
- Now, calculate Net Profit/Loss, using the formula:
=Total Return – Investment Amount
here, for the 3rd row of our data, B3-A3, and apply to all the cells of the data.
Note: If you will manually enter the currency symbol, it won’t work in the calculation. Use Currency Formatting to format using Excel standards.
- To calculate ROI, perform a simple division function using the formula:
=Net Profit or Loss / Investment Amount
here, for the 3rd row of our data, C3/A3, and apply to all the cells of the data.
- To calculate ROI%, perform ROI multiply by 100.
here, for the 3rd row of our data, D3*100 and apply to all the cells of the data.
Note: You can directly calculate ROI% using just two cell columns – Investment Amount and Total Return, using the formula –
=(Total Return – Investment Amount)/Investment Amount * 100
Related Posts
How to pull Stock Market Data in Excel
How to Calculate CAGR in Excel
Conclusion
Hope you have learned well what is Return On Investment(ROI) and how to calculate ROI in Excel. Stay tuned for more Excel tutorials to QuickExcel!