How to Use the ROI Formula in Excel (Step-by-Step)

If you have two investments in your portfolio, how do you compare and evaluate them both?

Out of many ways, one key investment evaluation metric is the Return on Investment (ROI) ratio 🗝

It expresses the net profit on an investment as a proportion of the cost of investment. It is a very useful and practical ratio that’s massively used around the world of finances by investment managers.

To learn the concept of ROI, its formula, and ways how you can calculate it in Microsoft Excel, read this tutorial till the end.

And before you scroll down, do not forget to grab your free sample practice workbook for this guide here 🚀

Table of Contents

What is ROI and its formula

ROI stands for Return on Investment.

It is a profitability ratio (denoted as a percentage) that expresses the return on an investment relative to the cost of the investment.

So, this ratio is all about the returns on an investment in proportion to its total cost ➗

Formula:

The formula clearly tells that we are taking the net return of investment (all that it has earned you to date) as a proportion of the cost of investment (all that you’ve ever invested into it).

As this ratio is seen in terms of a percentage, we multiply the results will 100.

ROI is used in practice by individual and institutional investors as well as corporations to see how much their investments are earning. Let me give you a quick example to explain how it works.

Example:

Say you invested $1000 in the stocks of ABC Company 2 years ago. Today, the same stocks are valued in the stock market at $1250. In these two years, the Company paid $50 in dividends.

So, what is the net return that you earned on your investment of $1000 in these 2 years?

• Capital Appreciation of $250 ($1250 – $1000)
• Dividends of $50
• Makes a total of $300

The ROI would be $300 / $1000 * 100 = 30%

Your investment has earned you a return of 30% as compared to its cost.

After another year, maybe these stocks further appreciate to $1400 and release dividends of another $30. So, by that time the ROI would rise from 30% to 48%.

Hence, the ROI is always calculated for the return of an investment on today’s value of the investment. With this, what is a sufficient ROI depends on the ideal target return for the investor and the holding period.

If as an investor, your target ROI is 45% or more, you also need to see the time (2 years, 3 years, 5 years, etc.) over which you expect your investment to yield the same.

In the above example, we see the investment yields a 45%+ ROI in 3 years. Until 2 years, the ROI stood at 30%. So, whether this investment is worth the cost for you or not depends on your target return % + holding period 💭

Sounds interesting? Let’s move to the next section to see how we can calculate it in Excel.

How to calculate ROI in Excel (ROI Examples)

This section discusses multiple examples of ROI calculation in Excel.

Basic ROI calculation

If I put the example we discussed above in numbers in Excel, calculating the ROI for it in Excel is literally a matter of seconds.

Check this out. Here are the investment details.

To calculate the ROI for this investment, we need to calculate the total net return earned on this investment to date.

Step 1) Begin by calculating the appreciation in share value over the years.

Step 2) Add the dividends collected to it.

Step 3) Divide the total return on investment by the cost of investment.

ROI in Excel is calculated to be 30%. That’s how easy it is to put together the formula for calculating ROI in Excel.

Hold on? Do your results look like a decimal number instead of a percentage? 😲

Step 4) Select the cell containing the results (the decimal number)

Step 5) Go to the Home tab > Number group > click the percentage icon.

Applying the percentage format to it will give you the ROI as a percentage.

Calculating ROI for multiple years

Apart from basic ROI calculation, what if you have to calculate the ROI for an investment for multiple years? Let’s see how to do it through the below example.

I purchased the shares of Company ABC some years ago for $3000. Here are some numbers from Company ABC’s performance during the years 📈

The data shows that the stocks have increased in value over the years and have also earned me dividends. Plus, I have paid a $200 commission to purchase these stocks (which is an additional cost to me).

I will show you how to calculate the ROI for these stocks for each of these 3 years in Excel. Come along with me.

We have the value of these stocks as of each year-end. However, we need to find the return for each year which will be the cumulative appreciation in value to date + dividends received to date.

Step 1) Calculate the appreciation in value for each year by deducting the cost of investment from the value of stocks at each year’s end.

I have used an absolute reference for the cell that contains the investment value so that it is not changed as I drag the formula toward the right.

Step 2) Calculate the total dividends received by each year’s end by adding up the dividend of each year with the dividends of previous years.

For the first year, it is simply equivalent to $200 (the dividend for the first year).

For the second year, it will be $200 + $100 (dividend for the first year plus the second year). And so on drag it towards the right.

Step 3) Add the value appreciation and cumulative dividends to calculate the total return on investment for each year.

Step 4) Also, don’t forget the commission paid initially. Since it is a cost, we will deduct it from the total return to reach the net return on investment.

Using an absolute reference for the commission paid so that I can carefreely drag the formula across all the years.

Pro Tip!

The commission is a direct and consequential cost of the investment. There are two ways to treat it.

• You can either deduct it from the total cumulative return; or
• You can add it to the initial cost of investment ($3000 + $200)

Step 5) Divide the total return of Year 1 by the cost of investment.

Step 6) Drag it across all years to have the ROI computed for each year.

The ROI continues to increase every year to reach 48.3% by Year 3. This tells that ROI is a time-specific concept that is different for each year.

For each year, the ROI of an investment is the total return earned on it by that year divided by the cost of the investment.

Annualized ROI

The ROIs computed above are the rates of return on investment for three successive years.

However, as told above, one big downside of ROI is that it ignores the effect of the holding period. For example, the increase in ROI from 6.7% to 48.3% is huge. But this doesn’t mean that the underlying investment has performed too extra-ordinarily 🤏

It’s just that three years have lapsed since the investment was made. The return on investment keeps adding up as each year passes (the numerator), whereas the cost of investment (the denominator) remains the same. Hence, the resulting percentage keeps getting bigger and better every year.

48.3% is the 3-year ROI for this investment.

To avoid misinterpretation of ROI, you can also calculate an [start-highlight]annualized ROI.[end-highlight] There are two ways how you can do this.

Way 1) Average out the ROI over the number of years.

We have divided the ROI for each year by the number of years elapsed. This gives an average annual ROI for each year.

Until Year 2, the annualized ROI was 13.8%. By Year 3, the annualized ROI for each year becomes 16.1%.

Way 2) Compute the annual ROI by using the following formula:

This is how we calculate it in Excel for a 3-year period.

It tells you the compound annual return rate for the investment over 3 years.

Interpreting ROI – Pros and Cons

ROI tells you what the return on your investment is relative to its cost. However, what is a good ROI is something that’s specific to each investor 📝

It depends on the risk tolerance level of the investors. Usually, risk-averse investors would want to hold investments for a shorter period so they might settle for lower ROIs. In contrast to them, investors who are risk-takers would want a higher ROI on the cost of long holding periods.

Here are some aspects to be kept in mind while you use ROI as an investment evaluation technique:

Pros

• The ROI calculation is straightforward. Takes minutes, if not seconds, to calculate in Excel.
• It is a universally and widely accepted valuation metric so you can use it across various investments and industries.
• Helps in comparing different investments.

Cons

• It does not account for the time value of money.
• ROI ignores the size of the investment options. A lower ROI on a large investment might be better than a lower ROI on a larger investment.
• The ROI metric fails to capture the holding period of an investment. This can make comparisons uneven.

Pro Tip!

The failure to incorporate the holding period of an investment is the biggest downside of using ROIs. For example, Investment A has an ROI of 30%, and Investment B has an ROI of 50%.

Looks like Investment B is better. Maybe it’s not 🤯

Investment B’s holding time period is 5 years (50%/5 years makes the annualized ROI 10%). Whereas Investment A’s holding period is only 2 years (30%/2 years makes the annualized ROI 15%).

Digging down beyond mere numbers reveals that Investment A offers a better ROI.

Making comparisons based on ROIs sometimes might be like comparing apples (a 5-year investment) to oranges (a 3-year investment). Unless you see them together with the holding time frame.

Conclusion

ROI is one of the top investment assessment metrics used by investors to evaluate the return on their investments and to compare two or more investments 💡

However, since it fails to account for the holding period, sometimes, it is not the most efficient method to go with. In that case, investors prefer using the NPV (Net present value) method which discounts cash flows of each investment on a given WACC (Weighted Average Cost of Capital), or the IRR (Internal Rate of Return).

All these methods have their own significance and learning them will take your finance game to another level.