Calculating Return on Investment (ROI) in Excel

What Is Return on Investment (ROI)?

Return on investment (ROI) is a calculation that shows how an investment or asset has performed over a certain period. It expresses gain or loss in percentage terms. 

The formula for calculating ROI is simple:

(Current Value - Beginning Value) / Beginning Value = ROI

The current value can be one of two things: whatever amount the investment was sold for (its realized value) or whatever the investment is worth at the present time (like the market price of a stock). The beginning value is a historical figure: the price originally paid for the investment or the cost price

Key Takeaways

  • Return on investment (ROI) shows how much of a return an asset or investment has provided over a specific time period, expressed in percentage terms.
  • ROI is calculated by subtracting the beginning value from the current value and then dividing the number by the beginning value. It can be calculated by hand or via excel.
  • ROI is a performance measure that makes for easy comparisons of the total return of various investments, but on the downside, it only measures from a random endpoint and doesn't account for the time value of money.

Calculating ROI in Excel

Financial modeling best practices require calculations to be transparent and easily auditable. Unfortunately, when you pile all of the calculations into a formula, you can't easily see what numbers go where, or what numbers are user inputs or hardcoded.

The way to set this up in Excel is to have all the data in one table, then break out the calculations line by line.

You want to create space for your starting and ending values, and then use cell references to determine the 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 ROI only measures 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.