Calculate ROI in Excel: Formula, Examples, and Tips

Formulas & functions · Updated November 21, 2026

To calculate ROI in Excel, subtract the investment cost from the value it generated, then divide the result by the cost: =(B2-A2)/A2. Put the cost in A2 and the total value returned in B2, then format the formula cell as a percentage; for example, a $1,200 cost and $1,560 return produce an ROI of 30%.

What ROI means

ROI stands for return on investment. It expresses a gain or loss relative to the amount invested, which makes it easier to describe performance in percentage terms. A positive result means the value generated exceeded the cost; zero means they were equal; and a negative result means the investment did not recover its full cost.

The basic calculation is net gain divided by investment cost. In a simple project, “value returned” might mean sales revenue, savings, or the ending value of an asset. The exact meaning depends on what you are measuring. Be consistent: if you include only some costs or benefits, the resulting ROI describes only that partial picture.

ROI is a ratio, not a dollar amount. A 30% ROI does not mean a $30 gain; it means the gain equals 30% of the cost. It also does not, by itself, show how long it took to earn the return, how uncertain the result was, or whether the cash was available at different times.

Set up the worksheet

A small, clearly labeled table makes the calculation easier to review and update. In a new worksheet, use headings such as these:

  • A1: Investment cost
  • B1: Total value returned
  • C1: Net gain
  • D1: ROI

Enter the cost and return in row 2, and use the remaining columns for calculated results. Use the same currency and time period for both input amounts. For example, do not compare a cost in dollars with a return in thousands of dollars unless you convert one of them first.

For a single investment, a layout with the cost in A2 and total value returned in B2 is enough. The return should represent the amount received or value created before subtracting the original investment, because the formula subtracts the cost to find the gain. If your input is already net profit after costs, do not subtract the investment cost a second time.

Enter the basic ROI formula

With the investment cost in A2 and the total value returned in B2, enter this formula in D2:

=(B2-A2)/A2

Excel calculates the difference between the return and cost, then divides that difference by the cost. If A2 contains 1200 and B2 contains 1560, the calculation is (1560 − 1200) ÷ 1200, or 0.3. Excel stores that result as a decimal; displayed as a percentage, it is 30%.

You can also calculate net gain separately. With the investment cost in A2 and the total value returned in B2, enter this formula in C2:

=B2-A2

In the example, C2 returns 360. Keeping the gain and ROI in separate columns can help readers see both the dollar outcome and the proportional return. It is not necessary to create the extra column if you only need the percentage.

Work through a realistic example

Suppose a business spends $2,400 on a campaign and attributes $3,150 in sales value to it. If the $3,150 is the total value returned before subtracting campaign cost, the net gain is $750. With the campaign cost in A2 and value returned in B2, enter this formula in D2:

=(B2-A2)/A2

The calculation is ($3,150 − $2,400) ÷ $2,400 = 0.3125, so the ROI is 31.25%. This figure says the measured gain was 31.25% of the campaign cost. It does not prove the campaign caused every sale; that depends on how the sales were attributed and what other factors influenced them.

If the investment instead generates only $2,100 in value, the same formula gives ($2,100 − $2,400) ÷ $2,400 = −0.125, or −12.5%. A negative ROI is a meaningful result, not an Excel error: it indicates a loss equal to 12.5% of the original cost under the inputs you provided.

Calculate ROI across several entries

When costs and benefits are recorded on multiple rows, calculate ROI from the totals rather than averaging the individual ROI percentages. For example, suppose costs are in A2:A4 and the corresponding total value returned is in B2:B4. Enter this formula in C2:

=(SUM(B2:B4)-SUM(A2:A4))/SUM(A2:A4)

SUM adds the values in each range, and the formula calculates the overall gain as a share of total cost. Imagine costs of $100 and $900 with respective returns of $150 and $990. The individual returns are 50% and 10%, but the combined result is ($1,140 − $1,000) ÷ $1,000 = 14%. A simple average of 50% and 10% would be 30%, which misrepresents the overall dollars because the two investments were different sizes.

This total-based method works when the entries use a consistent definition of cost and return and belong to a comparable measurement. If rows represent unrelated projects, calculate each project separately and label the results rather than combining them into a single figure without explanation.

Format and present the result

After entering the formula, select its cell and choose Home > Number > Percentage. In Excel for Microsoft 365 on Windows, the Percentage Style button is also available in the Number group on the Home tab. Increase or decrease the displayed decimal places if you need more or less precision. Formatting changes how the result appears, not the underlying calculation.

A result of 0.3 displayed in General format may look like a decimal, while Percentage format displays it as 30%. Do not multiply the formula by 100 and then apply percentage formatting: doing both would display 3,000% for a 30% return. If you need a dollar gain, format the gain cell as currency separately; ROI itself should usually remain a percentage.

Add clear labels and, where helpful, note the measurement period and included costs. For example, “Campaign ROI, first 90 days” is more informative than “ROI” alone. A percentage without a stated scope can be misunderstood, especially when readers compare figures calculated with different assumptions.

Compare projects fairly

ROI can help compare opportunities, but only when the figures are calculated on a comparable basis. Before ranking projects, check that each estimate uses the same time horizon, currency, cost categories, and definition of value returned. If one project includes labor and another counts only its purchase price, their percentages do not offer a like-for-like comparison.

Also consider the size of the investment. A small project can have a high percentage return but generate fewer dollars than a larger project with a lower percentage. For example, a 50% ROI on $100 means a $50 gain, while a 10% ROI on $10,000 means a $1,000 gain. Which matters more depends on the decision and any limits on available funds, time, or capacity.

For planning, separate estimates from realized results. Label assumptions, such as projected revenue or expected savings, so readers do not mistake a forecast for a measured outcome. ROI summarizes the relationship between cost and gain; it does not account for risk, operational constraints, or the value of other alternatives on its own.

Account for time and cash-flow timing

A basic ROI percentage does not indicate how quickly a return occurred. A 20% return over one year and a 20% return over five years have the same simple ROI, even though the time commitment differs. When duration matters, state the period beside the figure or calculate an annualized rate with an appropriate method.

For a single initial investment, an ending value, and a duration measured in years, an annualized compound rate can be estimated with this formula. If the starting cost is in A2, the ending value is in B2, and the number of years is in C2, enter it in D2:

=(B2/A2)^(1/C2)-1

For a $1,000 starting investment that grows to $1,331 over three years, the formula returns 0.1, or 10% per year. This calculation assumes one starting amount and one ending value, with growth compounded over the stated period; it does not account for deposits or withdrawals partway through. If cash flows happen at different dates, a basic ROI or this annualized calculation may not represent the timing accurately.

Common errors and how to handle them

  • Dividing by the wrong amount: ROI uses the investment cost as the denominator, not the total value returned. Check that the cost cell is the bottom part of the calculation.
  • Counting costs twice: If the return input is already net profit after subtracting the investment, do not subtract the original cost again. Use a clearly defined gross value returned in the basic formula.
  • Mixing percentages and decimals: Enter monetary amounts as numbers such as 1200, not as a percentage. Use Percentage formatting on the formula result.
  • Including an empty or zero cost: Dividing by zero returns Excel’s #DIV/0! error. A zero-cost case does not have a conventional ROI percentage because the denominator is zero; report the benefit or use a clearly explained alternative measure instead of treating it as an ordinary percentage.
  • Using inconsistent inputs: Check that the cost and return cover the same project, currency, and period, and that relevant costs have not been left out.
  • Overinterpreting the figure: ROI is not a guarantee of future performance and does not show uncertainty or explain why an outcome occurred. Treat projections as estimates and identify their assumptions.

Frequently Asked Questions

What is the Excel formula for ROI?
With investment cost in A2 and total value returned in B2, use =(B2-A2)/A2. Format the formula cell as a percentage to display the result in familiar ROI terms.

How do I calculate ROI as a percentage in Excel?
Enter the ROI formula, select its result cell, and choose Home > Number > Percentage. A decimal result of 0.25 then displays as 25%; percentage formatting does not change the underlying value.

Can ROI be negative?
Yes. If the value returned is less than the investment cost, the formula produces a negative result. For example, a $900 cost and $810 return produce a $90 loss, or a −10% ROI.

What if the investment cost is zero?
The basic formula divides by the cost, so a zero cost produces a #DIV/0! error. Since a percentage return relative to zero has no defined denominator, show the benefit separately or explain another measure rather than assigning a standard ROI.

Should I average multiple ROI percentages?
Usually not when the investments have different sizes. Add the costs and returns, then calculate ROI from those totals to reflect the overall dollar-weighted result. Compare individual percentages when you want to assess separate projects, and keep their dollar gains visible too.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.