This guide will explain how to use Excel formulas to calculate your return on investment or ROI.
Return on investment, or ROI, is a financial metric that evaluates the profitability of a certain investment. It measures the percentage gained or lost in relation to the amount invested.
This metric is useful when comparing various investments or assessing how well your investments are performing.
We can calculate ROI by dividing the net profit or loss of the investment by the investment’s initial cost. In this guide, we will provide a step-by-step tutorial on how to calculate ROI using Excel formulas.
A Real Example of Calculating ROI Using Excel
Let’s explore a simple example where we must calculate an investment’s ROI.
In the table above, we want to compare the performance of three different stocks that we’ve invested in. For each of these stocks, we already know the initial investment amount or the dollar amount the investor spent to purchase the investment. We also know the current value of the investment or how much the investment is worth today if sold.
To find the return on investment of Stock A, we can use the following formula:
=(B3-B2)/B2
This formula first finds the net profit or gain by subtracting the current value of the investment (B3) from the initial investment amount (B2). The formula then divides the net difference by the initial investment.
We can copy the formula to find the ROI of Stock B and Stock C. From our example, we can see that only Stock A has a negative ROI (-7%), while Stock B gave us the highest return on our investment (50%)
Click on the link below to create your own copy of our examples.
Head to the next section to read our step-by-step tutorial on how to calculate ROI using Excel formulas.
How To Calculate ROI Using Excel
- Identify the initial cost of your investment.
In our current example, we’ve invested $10,000 in a certain mutual fund. - Next, identify the current value of the investment. This is typically how much the investment is worth if sold today.
For this example, suppose our initial investment is now worth $11,500. - We’ll use the previous two values to calculate the net gain (or loss) of our investment. We can find this value by subtracting the current value of our investment from the initial investment.
If the result is negative, your investment has generated a net loss, while a positive result means we’ve generated a net profit.
We’ll use the formula B2-B1 to find the net gain from our investment. - We can now calculate the ROI by dividing the net profit by the initial investment.
We can apply number formatting to display the results as a percentage.
Using our ROI formula, we now know that our investment has an ROI of 15%.
These are all the steps you need to know to start calculating ROI in Microsoft Excel.
FAQs
- What if my investment results in a loss? How will ROI be affected?
If the investment results in a loss, the ROI will be negative, signifying a financial loss. - Can Excel automatically update ROI calculations if the investment value changes?
If you set up your Excel sheet with formulas linked to cells where investment values are updated, the ROI calculation will automatically update whenever these values change. This is particularly useful for investments with frequently changing values, like stocks.
To learn more about using Excel with financial data, you can read our post on how to forecast cash flow in Excel.
That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!
