Calculating Return on Investment (ROI) in Excel

By
Daniel Jassy
Full Bio
Daniel Jassy, CFA, is an Investopedia Academy instructor and the founder of SPYderCRusher Research. He contributes to Excel and Algorithmic Trading.
Learn about our editorial policies
Updated August 08, 2022
Reviewed by
JeFreda R. Brown
Dr. Brown
Reviewed by JeFreda R. Brown
Full Bio
Dr. JeFreda R. Brown is a financial consultant, Certified Financial Education Instructor, and researcher who has assisted thousands of clients over a more than two-decade career. She is the CEO of Xaris Financial Enterprises and a course facilitator for Cornell University.
Learn about our Financial Review Board
Fact checked by
Yarilet Perez
Yarilet Perez
Fact checked by Yarilet Perez
Full Bio

Yarilet Perez is an experienced multimedia journalist and fact-checker with a Master of Science in Journalism. She has worked in multiple cities covering breaking news, politics, education, and more. Her expertise is in personal finance and investing, and real estate.

Learn about our editorial policies

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.