To calculate return on investment (ROI) in Excel, subtract the investment cost from the value it generated, then divide by the cost. For example, if B2 contains the cost and B3 contains the total value returned, the layout is: B2 = 5,000 and B3 = 6,800; enter =(B3-B2)/B2 in another cell and format the result as a percentage. The result is 36%.
What ROI measures—and what it leaves out
ROI is a ratio that compares an investment’s gain or loss with what it cost. It is often used to evaluate a project, campaign, purchase, or other use of resources. A positive result means the measured value exceeded the stated cost; a negative result means it fell short. A zero result means the measured value equaled the cost.
The calculation is simple, but the inputs need clear definitions. In the opening example, 6,800 is the total value returned, including recovery of the original 5,000 cost. The gain is therefore 1,800, and 1,800 divided by 5,000 is 0.36, or 36%. If 6,800 instead means profit after the original cost, the same formula would give a different and misleading answer. Decide whether your return figure is total proceeds or net gain before entering it.
ROI also does not automatically account for how long an investment took, uncertainty, inflation, or the timing of individual payments. Two projects can have the same ROI but very different durations and cash-flow patterns. Treat a result as a measure of the inputs you included, not as a complete measure of an investment’s quality.
Set up the worksheet clearly
Make the cost and return easy to identify, and record what each amount includes. For a basic calculation in Microsoft Excel for Microsoft 365 on Windows, a small table can use these labels and values:
- A2: Investment cost; B2: 5,000
- A3: Total value returned; B3: 6,800
- A4: ROI; enter the formula in B4
Use a consistent unit and period: do not mix monthly costs with annual returns unless you are deliberately comparing them over a defined period. If the project has several expenses, include each relevant one rather than using only the most visible charge. Likewise, define whether “value returned” means revenue, cash received, savings, or another benefit.
For example, revenue is not necessarily the same as profit. If a campaign produces sales, those sales may involve product, delivery, or service costs beyond the campaign spend. Counting all sales as a return while omitting related costs can overstate ROI. A useful worksheet makes those assumptions visible instead of hiding them in a single number.
Calculate ROI when there are multiple costs
Put each project cost in its own cell, and keep the total value generated separate. Assume the costs are in B2:B4—advertising, setup, and other project expenses—and total value returned is in B5. Enter this formula in B6:
=(B5-SUM(B2:B4))/SUM(B2:B4)
With costs of 3,000, 1,200, and 800, the total cost is 5,000. If B5 is 6,800, the result is 36%. This approach makes the cost base auditable: you can see which expenses are included and update one item without rebuilding the calculation.
To avoid a divide-by-zero error when the cost cell is empty or zero, use a guarded formula. Assume B2 contains total cost and B3 contains total value returned; enter this in B4:
=IFERROR((B3-B2)/B2,"")
The formula displays a blank if the calculation produces an error. That can be convenient in a report, but it can also conceal missing data. For a working model, consider showing a note such as “Enter a nonzero cost” in a separate status cell so an empty result is not mistaken for a completed calculation.
Format the result as a percentage
Excel stores percentages as decimal values: a result of 0.36 represents 36%. Select the ROI cell, then choose Home > Number group > Percent Style. Increase or decrease the displayed decimal places with the decimal-place buttons in the Number group. The underlying value does not change when you alter the display format.
Do not multiply the formula by 100 and then apply percentage formatting. For example, a formula that returns 36 and is formatted as a percentage displays 3,600%. The opening formula already returns the decimal fraction Excel expects for percentage formatting. If you need a plain numeric figure instead, leave the cell in a number format and label the unit clearly.
For a report, choose a level of precision that suits the decision. Whole percentages are easy to scan, while one or two decimal places can help distinguish results that are close. More decimal places do not make uncertain estimates more precise; they only show more digits from the calculation.
Worked example: a project with additional expenses
Suppose a business evaluates a project with a 5,000 initial payment, 600 in later operating expenses, and 7,500 in total value returned. The relevant cost is 5,600, not just the initial payment. Assume B2 contains 5,000, B3 contains 600, and B4 contains 7,500. Enter this formula in B5:
=(B4-SUM(B2:B3))/SUM(B2:B3)
The cost total is 5,600; the gain after those costs is 1,900. Dividing 1,900 by 5,600 gives about 0.3393, so the ROI is approximately 33.93%. This example assumes the 7,500 figure is the total value before subtracting the listed costs and that no other relevant costs or benefits are omitted. If the return figure is already net of some expenses, make sure those expenses are not subtracted twice.
For a marketing example, the same discipline matters. If a campaign costs 2,000 and is associated with 5,000 in sales, a basic calculation using sales as the returned value gives 150%. But that is a sales-based calculation, not necessarily a profit-based ROI. If fulfilling those sales costs 2,000, the net gain after both campaign and fulfillment costs is only 1,000 against 4,000 total costs, or 25%. State which version you report so people do not compare unlike measures.
Compare investments over different time periods
Simple ROI does not express an annual rate. A 36% total ROI earned over one year is not directly comparable with the same 36% earned over five years. When an investment has a single starting amount and a single ending value, an annualized compound rate can provide a time-adjusted comparison. It assumes growth compounds evenly; it does not describe uneven payments.
Assume B2 contains the starting investment of 5,000, B3 contains the ending value of 6,800, and B4 contains the duration of 2 years. Enter this formula in B5 and format it as a percentage:
=(B3/B2)^(1/B4)-1
The result is about 16.62% per year, compounded, while the simple total ROI over the full two years remains 36%. The annualized result is not 36% divided by two: compounding changes the relationship. This calculation is only suitable when the start value, ending value, and duration make sense for the investment being compared.
Where cash flows occur on different dates, a single beginning and ending value may not tell the full story. Excel’s XIRR function can estimate an annualized internal rate of return from dated cash flows, provided the cash-flow range includes at least one negative and one positive value. That is a different measure from simple ROI, and its result depends on the dates and cash-flow amounts entered. Use it when timing is central to the comparison, and label it accurately rather than calling it simple ROI.
Common mistakes and interpretation limits
- Leaving out costs: Include relevant setup, operating, and other expenses consistently. A result is only as complete as its cost list.
- Confusing revenue with gain: Sales or proceeds may not equal profit. State whether your return input is gross revenue, net benefit, cash received, or another measure.
- Mixing periods: Align the dates covered by costs and benefits. If they differ, explain the period or use a measure that reflects timing.
- Comparing unlike projects: A result based on estimated savings is not automatically comparable with one based on cash income, even when both are called ROI.
- Reading a percentage as a dollar amount: ROI is a ratio. A 30% result does not tell you whether the gain was 30 dollars or 30,000 dollars without the underlying cost.
ROI can help summarize a decision, but it does not show the scale of the investment, the risk of achieving the projected return, or nonfinancial effects. For a major decision, preserve the assumptions and source figures alongside the percentage. If the amounts relate to a real loan, investment, tax matter, or other financial decision, check the actual terms with the lender or a qualified professional; this calculation is an illustration, not financial, tax, or legal advice.
Frequently Asked Questions
What is the basic ROI formula in Excel?
Subtract the investment cost from the total value returned, then divide by the investment cost. If B2 is cost and B3 is total value returned, use =(B3-B2)/B2 and format the result as a percentage.
How do I calculate a negative ROI in Excel?
Use the same formula. If the value returned is less than the cost, the result is negative. For example, a cost of 1,000 and a total return of 800 produce a 20% loss, displayed as -20% when the cell is formatted as a percentage.
Should ROI be entered as a percentage or decimal?
Excel calculates the ratio as a decimal, such as 0.36, and percentage formatting displays it as 36%. Do not multiply by 100 if you are also applying percentage format.
Can ROI be higher than 100%?
Yes. If the gain is greater than the original cost, the ROI exceeds 100%. For example, a 1,000 cost that generates 2,500 in total value has a 150% ROI: the 1,500 gain is one and a half times the cost.
Is ROI the same as profit margin?
No. ROI compares a gain with the investment cost. Profit margin generally compares profit with revenue. They use different denominators, so the percentages answer different questions and should not be treated as interchangeable.
Related
- Bluebeam Export to Excel: Steps, Fields, and Fixes
- Blank Checklist Template Excel: Create One That Works
- Bloomberg Excel Add-In Repair and Troubleshooting
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Date Comparison: Formulas, Rules, and FixesWith dates in A2 and B2, compare them using =A2<B2: Excel returns TRUE if A2 is earlier than…
- How to Fill A to Z in Excel With Formulas or AutofillTo fill A to Z in Excel, enter =CHAR(ROWS($A$1:A1)+64) in the first cell, then fill it down 26…
- Excel Sum Shortcut: Insert a SUM Formula FastIn Excel for Microsoft 365 on Windows, press Alt+= (Alt plus the equals key) to insert an AutoSum…
- Excel Formula to Add Dates in a Weekly Sequence AutomaticallyTo add dates in weekly sequence in Excel, enter a starting date in A2, then put =A2+7 in…
- Excel INDEX MATCH Tutorial: Exact and Flexible LookupsTo look up a value with INDEX MATCH in Excel, use MATCH to find the position of a…
- Excel Timesheet Formula Minus Lunch: Simple ExamplesTo subtract an unpaid lunch entered as minutes, put the shift start in B2, the end in C2,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.