How to Calculate ROI Percentage in Excel With a Formula

Formulas & functions · Updated November 18, 2026

To calculate return on investment (ROI) in Excel, subtract the investment cost from its final value, divide the result by the cost, and format the answer as a percentage. For example, if you invested $1,000 and the investment is now worth $1,200, enter =(C2-B2)/B2 in a result cell, where B2 contains 1000 and C2 contains 1200. The result is 20% after percentage formatting. Before you start, decide which costs and returns belong in your calculation; consistent inputs matter as much as the formula.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows, or an equivalent Excel version.
  • The original investment cost and the final value or proceeds you want to compare with it.
  • Any additional costs or income that should be included, recorded consistently with your chosen method.
  • A blank worksheet or a copy of your existing workbook for the calculation.

ROI is a ratio: it describes the gain or loss relative to the amount invested. A positive percentage indicates a gain, zero indicates that the value equals the cost, and a negative percentage indicates a loss. ROI does not, by itself, show how long it took to earn that return or how risky the investment was.

Step-by-Step

  1. Choose what the calculation covers. Define the investment and the period you are measuring. For a simple asset purchase, you might compare its purchase cost with its current value or sale proceeds. For a project, you might compare the project’s total cost with the financial benefit it generated. Do not mix an asset’s current value with a return measured over a different period unless that is what you intend. Write down the start and end dates or period in the sheet so the result has context.
  2. Set up clear column headings. In a new worksheet, enter Investment Cost in A1, Final Value in B1, and ROI in C1. Put the cost in A2 and the final value in B2. For example, enter 1000 in A2 and 1200 in B2. Use numeric values rather than typing a currency symbol into the number; you can apply a currency format later. Headings make it easier to check which value is the denominator and reduce the chance of swapping the inputs.
  3. Calculate the ROI ratio. With the investment cost in A2 and the final value in B2, enter this formula in C2: =(B2-A2)/A2. Excel subtracts the original cost from the final value to find the gain, then divides that gain by the original cost. With A2 equal to 1000 and B2 equal to 1200, the calculation is (1200 − 1000) ÷ 1000, or 0.2. The underlying result is a decimal ratio, not yet a displayed percentage.
  4. Format the result as a percentage. Select C2, then choose Home > Number > Percent Style. Excel displays 0.2 as 20%. Increase or reduce the displayed decimal places with the decimal-place buttons in the Number group if you need more detail. For this example, one decimal place would show 20.0%, while zero decimal places shows 20%. Formatting changes how the value appears; it does not change the calculation.
  5. Check the result against the inputs. Confirm that the final value is greater than the cost for a positive ROI, equal to the cost for 0%, or less than the cost for a negative ROI. In the example, the gain is $200 on a $1,000 investment, so 20% is the expected result. If Excel displays 2000%, check that you did not multiply the formula by 100 and also apply Percentage format. If it displays 0.2%, check that you did not enter 0.2 when you meant 20% as a manually supplied rate.
  6. Include additional costs when appropriate. If you paid fees or other costs beyond the original purchase, include them in the total investment rather than comparing the final value only with the purchase price. For a layout with purchase cost in A2, additional costs in B2, and final value in C2, enter this in D2: =(C2-A2-B2)/(A2+B2). If the purchase cost is $1,000, additional costs are $100, and final value is $1,320, total investment is $1,100 and gain is $220, so the ROI is 20%. This version treats both cost entries as part of the investment.
  7. Include returns in a consistent way. If the investment produced income, decide whether that income is part of the return you want to measure. For example, if you want total ROI, add income received to the final value before subtracting total cost. With total cost in A2, final asset value in B2, and income received in C2, enter this in D2: =(B2+C2-A2)/A2. If cost is $1,000, final value is $1,150, and income is $50, the gain is $200 and ROI is 20%. Do not add income twice if your reported final value already includes it.
  8. Apply the same calculation to more rows. To compare several investments, place each investment on a separate row with costs in column A and final values in column B. Enter the ROI formula in C2 using the first row’s values, then select C2 and drag the small fill handle at its bottom-right corner down the column. Excel adjusts the row references for each line. Check at least one copied result manually, especially if some rows use different cost or income assumptions.
  9. Label the result and save the workbook. Use a heading such as ROI (%) and, if useful, add columns for the measurement period and notes about included costs. Save the file with a clear name and keep the underlying cost and value data. A percentage without its inputs, dates, and assumptions can be difficult to interpret later.

The standard formula can also be written as (gain ÷ investment cost) × 100 when reporting a number directly. In Excel, the preferred approach is usually to calculate the ratio and apply Percentage format. Do one or the other, not both: a formula that multiplies by 100 and a cell formatted as a percentage will display a number one hundred times too large.

Common Mistakes

  • Using the wrong denominator. ROI divides by the investment cost, not the final value. Reversing the subtraction or dividing by the final value calculates something different. Keep the cost as the denominator for the standard ROI calculation.
  • Leaving out relevant costs. Fees, setup costs, or other expenses can make the reported return look higher than it is if they are excluded. Decide which costs belong in the calculation and apply the same rule to every comparison.
  • Double-counting income or costs. A final value may already include distributions, rebates, or other returns. Check what each input represents before adding income or subtracting costs separately.
  • Multiplying by 100 twice. Percentage formatting already converts a ratio such as 0.2 to a displayed 20%. Multiplying by 100 in the formula as well produces an incorrect display when percentage formatting is also applied.
  • Comparing different time periods. A 20% ROI earned over one month is not directly equivalent to 20% earned over five years. The basic formula does not account for duration, timing of cash flows, inflation, or risk.
  • Treating a percentage as a dollar amount. ROI shows a rate relative to cost, not the size of the profit. A 20% return on $1,000 is $200, while 20% on $100,000 is $20,000. Consider showing both ROI and the gain when comparing projects.

Tips

  • Use a separate column for every input: initial cost, additional costs, income, and ending value. This makes the calculation auditable and easier to update.
  • Format money inputs as currency using Home > Number, and format the ROI result as a percentage. Keep actual numbers in cells rather than combining numbers and labels in one cell.
  • Use a consistent definition of ROI when comparing alternatives. If one result includes fees and another does not, the percentages are not a fair comparison.
  • Keep full precision in the stored values and use cell formatting to control the number of displayed decimals. Rounding each input before calculating can change the result.
  • Add dates or a period label beside each result. If you need to compare returns over different time spans, research an appropriate annualized-return method rather than treating the basic ROI percentage as annual.
  • For a presentation, show the dollar gain alongside the percentage and briefly state what costs and returns were included. This helps readers understand both the scale and the assumptions behind the result.

Troubleshooting

Excel shows a decimal instead of a percentage: The formula may be correct and the cell may simply be in General format. Select the result and use Home > Number > Percent Style. A stored value of 0.2 should display as 20%.

The result is 100 times too large: Look at the formula bar. If the formula multiplies by 100, remove that multiplication when the cell is also formatted as a percentage. Alternatively, keep the multiplied result in a General-format cell, but do not combine the two approaches.

The formula returns #DIV/0!: The investment-cost cell is blank or contains zero. ROI is undefined when the amount invested is zero, so enter the correct cost or leave the result blank until you have one. Do not replace a missing cost with an arbitrary number just to suppress the error.

The result is negative or seems unexpectedly low: Check that the final value is in the correct cell and that all costs use the same units and currency. A negative result can be correct when final value is below total cost. Also check for costs entered as positive numbers in a formula that expects them to be subtracted, or income that has been omitted or counted twice.

Excel reports a formula error or treats the formula as text: Confirm that the entry begins with an equals sign and that cell references match your layout. If the cell displays the formula characters instead of a result, change its format from Text to General, then re-enter the formula. If your regional Excel settings use a different list separator, Excel may require semicolons in functions with arguments; the simple formulas shown here do not use function arguments.

Frequently Asked Questions

What is the basic ROI formula in Excel?
Subtract the original investment cost from the final value, then divide by the original cost. Format the resulting ratio as a percentage. For instance, a 0.2 result displays as 20%.

Should I multiply the ROI formula by 100?
Usually not. Apply Percentage format to the ratio instead. Multiplying by 100 is only appropriate when you want the formula itself to return a number such as 20 and will not also format that number as a percentage.

Can ROI be negative?
Yes. If the final value is below the total investment cost, the formula returns a negative percentage. A 20% loss, for example, is displayed as -20% when the final value is 80% of the cost.

How do I calculate ROI when there are extra costs?
Add eligible extra costs to the original cost to form the total investment. Calculate the gain by subtracting that total from the final value, then divide the gain by the total investment. Keep the same cost definition across the comparisons you are making.

Does ROI show how long it took to earn the return?
No. The basic ROI percentage does not account for time. Note the period beside the result, and use a suitable annualized measure if you need to compare investments held for different lengths of time.

Is ROI enough to compare two investments?
Not always. ROI does not show the dollar scale, timing of returns, or risk. Compare the percentage with the underlying costs, gains, dates, and assumptions before drawing conclusions. For decisions involving real investments, check the relevant terms with a qualified professional; this spreadsheet example is not financial, tax, or investment advice.

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.