What Is Return on Investment (ROI)?
Return on investment (ROI)measures the profit generated from an investment. It seeks to identify how much money an investment made relative to its cost.
The formula for calculating this popular profitability metric is simple. You divide the investment's net income by its original cost and then multiply that figure by 100 to arrive at a percentage. The higher the percentage, the more profitable the investment.
Key Takeaways
- Return on investment (ROI) is a popular profitability metric used to evaluate how well an investment has performed.
- It’s calculated by dividing how much the investment made or lost by its cost and then multiplying that figure by 100 to arrive at a percentage.
- The higher the percentage, the more profitable the investment.
- ROI can be calculated easily in Excel. You just input the data, add the formula, and the software works it out.
- ROI is great for making comparisons but doesn’t consider the time value of money.
How to Calculate Return on Investment (ROI)
The formula for calculating ROI is as follows:
(Current Value - Beginning Value) / Beginning Value = ROI
The current value can be one of two things: whatever amount the investment was sold for (itsrealized value) or whatever the investment is worth at the present time (like themarket priceof astock). The beginning value is a historical figure: the price originally paid for the investment or thecost price.
How to Calculate ROI in Excel
We make investments to make money, so it's natural for an investor to wonder whether their investment paid off and by how much. That could be a person valuing how much a stock investment generated or a company analyzing the profitability of an acquisition or purchase of new equipment.
ROI can be calculated either by hand, calculator, or using software such as Microsoft Excel. Excel is generally a great program for doing calculations and keeping track of financial data.
You can calculate ROI in Excel using the following steps:
Open Excel and create a new spreadsheet
The first thing you need to do is switch on whatever device you plan to use and click on the green and white Excel icon. When the program opens, select create a new spreadsheet.
Label the cells
Before entering the data into the various cells in the spreadsheet, you’ll want to give these cells a name. For example, in cell A1 you could write “amount invested,” in cell B1 “amount gained from investment,” and in cell C1 "ROI." Entering these descriptions is good for record-keeping and will help to avoid confusion later.
To avoid potentially losing your work, make sure you save your Excel document regularly.
Add data
Now you can start inputting the various data required to make the ROI calculation. Using the above example, type the amount invested in cell A2 and the amount made from the investment in cell B2. These two figures are needed to compute the profit generated on the investment.
Input the formula
It’s now time to make the calculation. In the cell where you want the ROI to appear, type without any spaces = followed by the name of the cell where you put the amount invested, the forward slash sign (/), and the name of the cell where the amount made from the investment appears. Using the example above, you would type “=A2/B2” into cell C2.
Get the percentage
All that’s now missing is the percentage. To get the final result, click the % icon while highlighting the cell where the ROI appears. In our example, that would be cell C2.
:max_bytes(150000):strip_icc()/roi_example-5bfd8728c9e77c0058b5ffc2 "Calculating Return on Investment (ROI) in Excel (1)")
Calculating ROI in Excel Example
Let’s imagine we recently sold an antique on eBay and wanted to figure out the ROI in Excel.
First, we need to label our cells. Let’s type “initial cost” in cell A1, “financial gain” in cell B1, and “ROI" in cell C1. Next, it’s time to input the data. In A2 we type 15, which was how much the antique initially cost when it was bought at a flea market 15 years ago, and in B2 we type 218, which was how much the antique was sold for, less the initial $15 invested and $8 billed postage cost.
All that’s left now is to calculate the ROI. In cell C2, type “=A2/B2,” press enter, and then click on the % icon. If everything went correctly, C2 should now read 7%. This is our ROI.
ROI Pros and Cons
A positive aspect of ROI as a performance measure is that you can easily compare the total return of different investments.
However, there are a few considerations to keep in mind. Sometimes in the basic ROI formula the "current value" is expressed as a "gain on investment."This isn't completely accurate. If you started with $100, and ended with $140, your gain on the investment is $40. But the current value is the entire $140.
The other big one is that ROIonlymeasures from an arbitrary endpoint. It does not consider the time value of money, which is a critical element of return. This is especially clear if you look at the 2020 ROI of -18% in the table above. That is not a yearly change from the prior value of 2019. Rather, it's the total change measured from the start, in 2017. While it accurately reflects total return over the period, it doesn't show the annual return or the compounded rate of change.
How Do You Calculate ROI on an Investment?
ROI is calculated by dividing the financial gain of the investment by its initial cost. You then multiply that figure by 100 to arrive at a percentage.
What Is the Difference Between Irr and ROI?
Both return on investment (ROI) and internal rate of return (IRR) measure the performance of investments or projects.ROI tells you the total rate of return for an investment from the beginning to the end, or the present moment, whereas IRR reveals the annual rate of growth that an investment is expected to generate.
What Is a Good ROI Ratio?
That depends on a number of factors, including the type of asset and the length of time it was held. Some things are expected to grow in value, whereas other things generally deteriorate in value. In the case of stocks, a good benchmark to use would be an index like the S&P 500. A decent ROI would be anything above the return generated by the index. Time is also important as you need to account for inflation. $100 five years ago isn’t the same as $100 today.
The Bottom Line
Return on investment (ROI) is one of the most popular profitability metrics out there. It’s used by companies, big and small, as well as individuals to calculate the money they made off an investment.
Calculating ROI is simple, both on paper and in Excel. In Excel, you enter how much the investment made or lost and its initial cost in separate cells, then, in another cell, ask Excel to divide the two figures (=cellname/cellname) and give you a percentage.
