Calculating Return on Investment in Excel

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.  

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 hard coded.

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.  

A positive aspect of ROI as a performance measure is that you can easily compare the total return of different investments.  

However, there 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 investment is $40. But the current value is the entire $140. 

The other big one is that ROI only measures from an arbitrary end point. 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.