ROI in Excel: The Essential Excel Formula for Return on Investment
Learn an exact excel formula for return on investment (ROI) in Excel. This guide covers fundamentals, multiple-project ROI, error handling, and practical, step-by-step examples with ready-to-use formulas and tips for accurate portfolio analysis.
To calculate ROI in Excel, use the standard formula: ROI = (NetProfit / Cost) * 100, where NetProfit = Revenue - Cost. In practice, place Revenue in B2 and Cost in C2 and enter =(B2-C2)/C2, formatted as a percentage. According to XLS Library, this straightforward approach works for single projects and scales when you aggregate across portfolios.
ROI basics and common definitions
ROI, or return on investment, measures the efficiency of an investment by comparing net gains to the cost of the investment. In Excel, you typically define NetProfit as Revenue minus Cost and then compute ROI as NetProfit divided by Cost, often expressed as a percentage. Different organizations express ROI slightly differently, so it helps to agree on a single convention before building dashboards. The simplest approach uses a single-period view, which is easy to audit and explain to stakeholders. As you scale to portfolios, you’ll aggregate revenues and costs across projects and then compute ROI on the totals. This ensures comparability and prevents misinterpretation caused by per-project variations.
=B2-C2 # NetProfit: Revenue minus Cost=(B2-C2)/C2 # ROI as decimal fraction (format as % to show percent)=((B2-C2)/C2)*100 # ROI as percentage without relying on cell formattingBasic Excel ROI formula templates
ROI is most commonly expressed as a percentage. The basic template for a single project is simple: ROI = (Revenue - Cost) / Cost. If you want a decimal value, format the result as a percentage or multiply by 100. For multiple projects, you can aggregate first and then compute ROI:
=(SUM(B2:B10) - SUM(C2:C10)) / SUM(C2:C10)=IFERROR((SUM(B2:B10) - SUM(C2:C10)) / SUM(C2:C10), 0)If Cost could be zero, guard against division by zero to avoid errors:
=IF(C2=0, 0, (B2-C2)/C2)Steps
Estimated time: 45-60 minutes
- 1
Set up data structure
Organize your data with Revenue in one column and Cost in the adjacent column. Ensure column headers clearly label Revenue, Cost, NetProfit, and ROI where you’ll display results.
Tip: Use a table for automatic range expansion as you add more projects. - 2
Compute NetProfit
In a NetProfit column, calculate Revenue minus Cost for each row using =B2-C2 and fill down.
Tip: NetProfit is the foundation for accurate ROI calculations. - 3
Calculate ROI per row
In an ROI column, compute =IF(C2=0,0,(B2-C2)/C2) to get a per-project ROI, formatted as a percentage.
Tip: Guard against division by zero to avoid errors. - 4
Aggregate ROI for a portfolio
If you want overall ROI, sum revenues and costs then divide: =(SUM(B2:B10)-SUM(C2:C10))/SUM(C2:C10).
Tip: Use SUM for a clean portfolio metric. - 5
Handle errors and zero-costs
Wrap your formulas with IFERROR to return safe values when inputs are missing or non-numeric: =IFERROR((B2-C2)/C2, 0)
Tip: Data validation reduces broken dashboards. - 6
Format and present
Format ROI cells as Percentage and add a brief interpretation note for stakeholders.
Tip: Clear labeling improves trust in the numbers.
Prerequisites
Required
- Required
- Basic knowledge of formulas and cell referencesRequired
- Data organized with Revenue and Cost columns (e.g., A:B or A:C data ranges)Required
Optional
- Ability to format cells as PercentageOptional
- Optional: named ranges for readability (e.g., RevenueRange, CostRange)Optional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| Copy ROI resultCopy ROI cell to clipboard | Ctrl+C |
| Paste ROI resultPaste ROI into another cell or document | Ctrl+V |
| Format ROI as percentageApply percentage formatting to ROI value | Ctrl+⇧+% |
People Also Ask
What is the standard ROI formula in Excel?
The standard ROI formula is (Revenue - Cost) / Cost, expressed as a percentage. This can be calculated per row or on aggregated data for a portfolio. Using IFERROR helps handle missing or zero-cost inputs.
ROI is calculated as revenue minus cost, divided by cost, shown as a percentage. If a cost is zero, you should handle it to avoid errors.
How do I compute ROI for multiple projects in Excel?
To compute ROI across multiple projects, sum all revenues and all costs, then apply (SUM(Revenue) - SUM(Cost)) / SUM(Cost). This provides the portfolio ROI and avoids misinterpretation from per-project averages.
For many projects, add up revenue and cost first, then divide to get the overall ROI.
Why can ROI be negative, and what does it mean?
A negative ROI occurs when costs exceed revenues. It indicates a loss on the investment. Negative ROI should trigger a review of pricing, costs, or the investment strategy.
If ROI is negative, it means the project lost money relative to the cost.
How can I format ROI as a percentage in Excel?
Select the ROI cells and apply Percentage formatting (Ctrl+Shift+%). This converts decimal ROI values into percent form for easier interpretation.
Format ROI as a percent so it’s easier to read, like 12% instead of 0.12.
What about ROI when taxes or discounts apply?
If taxes or discounts affect Revenue, adjust Revenue before applying the ROI formula, e.g., ROI = (RevenueAfterTax - Cost) / Cost. Clear data definitions make this reliable.
If taxes or discounts matter, use the post-tax revenue in the ROI calculation.
Are there alternatives to ROI I should consider?
Yes. Depending on your model, consider ROAS (return on advertising spend), NPV (net present value), or IRR (internal rate of return) for longer-term or cash-flow-based analyses.
ROI isn’t the only metric; you may want ROAS, NPV, or IRR for deeper financial insights.
The Essentials
- Calculate ROI as (Revenue - Cost) / Cost
- Aggregate data for portfolio ROI before comparison
- Guard against zero-cost inputs with IFERROR
- Format ROI as percentage for clear interpretation