ROI Formula in Excel: Calculate Return on Investment
By ExcelReady TeamUpdated 7 min read
The short answer
ROI (return on investment) is the gain divided by the cost. In Excel, if the cost is in B2 and the total return is in C2, type =(C2-B2)/B2 and format the cell as a percentage. A cost of 2,000 that brings back 5,000 gives 150%. A negative result means you lost money on the investment.
Key takeaways
- ROI = (Return - Cost) / Cost. In Excel: =(C2-B2)/B2, formatted as a percentage.
- Always divide by the cost, not by the return. Dividing by revenue gives a margin, not ROI.
- Include every cost: setup and running costs both belong in the bottom of the fraction.
- Plain ROI ignores time. For a yearly figure, use =(C2/B2)^(1/years)-1.
- Wrap the formula in IFERROR or IF to avoid #DIV/0! when the cost is still blank.
On this page+
The ROI formula in Excel is =(Return-Cost)/Cost. ROI stands for return on investment: how much you gained for each unit of money you put in. Type the formula with cell references, format the result as a percentage, and you can compare campaigns, projects or purchases side by side. This guide is for anyone who has to answer "was it worth it?" in a spreadsheet, at work or in a job test.
What is ROI?#
ROI measures the profit from an investment as a share of its cost. The formula is:
ROI = (Return - Cost) / Cost- Return is everything the investment brought back: sales, savings or the final value.
- Cost is everything you spent to get it.
- Return - Cost is the gain (or loss).
If you spend 2,000 and get 5,000 back, the gain is 3,000 and the ROI is 3,000 / 2,000 = 1.5, which is 150%. An ROI of 0% means you broke even. A negative ROI means you lost money.
How to calculate ROI in Excel#
Put the cost and the return in their own columns, then divide the gain by the cost. Here is a simple marketing table:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Campaign | Cost | Return | ROI |
| 2 | 2,000 | 5,000 | ||
| 3 | Search ads | 8,000 | 10,000 | |
| 4 | Trade show | 12,000 | 9,000 | |
| 5 | Social | 3,000 | 4,200 |
Click D2 and type:
Excel formula=(C2-B2)/B2Press Enter. The result is 1.5.
Format it as a percentage: Home > %, or press Ctrl+Shift+% (Mac: Control+Shift+%). It now shows 150%.
Fill the formula down to D5 (Windows: Ctrl+D; Mac: ⌘D).
| Campaign | ROI |
|---|---|
| 150% | |
| Search ads | 25% |
| Trade show | -25% |
| Social | 40% |
The brackets matter. Excel divides before it subtracts, so =C2-B2/B2 would calculate 5,000 - 1 = 4,999. If you're new to building formulas like this, start with the lesson on your first formulas.
ROI is the same maths as percent change, with the cost as the "old" value and the return as the "new" one. The percent change formula guide explains why you always divide by the starting value.
How to include every cost#
ROI is only honest if the cost includes everything you spent. A project often has a one-off setup cost and a running cost. Add them before you divide:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Project | Setup cost | Running cost | Return | ROI |
| 2 | 2,000 | 500 | 5,000 | 100% | |
| 3 | Webinar | 4,000 | 1,000 | 6,500 | 30% |
| 4 | Trade show | 9,000 | 3,000 | 10,800 | -10% |
| 5 | Partner | 3,000 | 1,500 | 7,200 | 60% |
In E2:
=(D2-SUM(B2:C2))/SUM(B2:C2)For Email, the total cost is 2,500 and the gain is 2,500, so ROI is 100%. Counting only the setup cost would have shown 150% and made the project look better than it was.
For the ROI of all four projects together, add up first and divide once: =(SUM(D2:D5)-SUM(B2:C5))/SUM(B2:C5) gives about 22.9%. Don't average the four ROI percentages: that gives a small project the same weight as a big one.
How to calculate annualized ROI#
Plain ROI ignores time. A 40% return in one year is far better than 40% over three years. Annualized ROI turns a total return into a yearly rate:
=(End/Start)^(1/Years)-1If 10,000 grows to 14,000 in 3 years:
=(14000/10000)^(1/3)-1The result is about 11.87% a year, compared with 40% in total. This is the same calculation as the compound annual growth rate, explained in the CAGR formula guide. If you have exact start and end dates, YEARFRAC gives the years: =(C2/B2)^(1/YEARFRAC(D2,E2))-1.
ROI vs profit margin and other measures#
ROI divides the gain by the cost. Other measures divide by something else, so keep the labels clear:
| Measure | Formula | Answers |
|---|---|---|
| ROI | (Return - Cost) / Cost | How much did we gain per unit spent? |
| Profit margin | (Revenue - Cost) / Revenue | How much of each sale is profit? |
| Return on ad spend | Revenue / Ad spend | How much revenue per unit of ad spend? |
On the Email row, ROI is 150% but the margin is (5,000 - 2,000) / 5,000 = 60%. Both are right; they answer different questions. See the profit margin formula guide for margin and markup.
ROI also ignores when the money arrives. For long projects with cash coming in over several years, a discounted measure is fairer. The NPV function guide shows how to value future cash in today's money.
How to find the return you need for a target ROI#
Turn the formula around when you know the ROI you want and need the return. Rearranged, ROI = (Return - Cost) / Cost becomes:
Return needed = Cost * (1 + Target ROI)If a project costs 12,000 (in B2) and your manager wants at least 40% ROI (in F1), type:
=B2*(1+$F$1)The result is 16,800. Anything less than that misses the target. The $ signs keep F1 fixed when you fill the formula down a list of projects, so every row compares against the same target.
To flag each project, add a check next to the ROI column:
=IF(D2>=$F$1,"Meets target","Below target")With the ROI table above and a 40% target, Email (150%) and Social (40%) meet it, while Search ads (25%) and the Trade show (-25%) don't. For harder cases, such as a price that must deliver a target ROI, Goal Seek can work backwards to the input for you.
Common ROI mistakes in Excel#
Most wrong ROI figures come from one of these:
- Dividing by the return.
=(C2-B2)/C2is a margin, not ROI. - Missing brackets.
=C2-B2/B2subtracts 1 from the return. Put the gain in brackets. - Multiplying by 100 and formatting as %. That shows 15000% instead of 150%. Do one or the other, and see how percentage formatting works.
- #DIV/0! on empty rows. If the cost is blank, use
=IF(B2=0,"",(C2-B2)/B2)or=IFERROR((C2-B2)/B2,""). - Leaving out costs. Staff time, fees and running costs all lower the real ROI.
Practise it#
ROI is percent change with a business label. Practise percent change in a live spreadsheet: you type the formula, format it, and the sheet checks your answer at once. Then try the ROI exercise on this page, where the cost is split over two columns.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Each project has a setup cost and a running cost. Click E3 and calculate the ROI for the Webinar: return minus total cost, divided by total cost. Use SUM to add the two costs.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ROI | |||||
| 2 | ||||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Tip: Total cost is SUM(B3:C3). Take it away from D3, put that in brackets, then divide by SUM(B3:C3) again.
Practise this in a lesson
Frequently asked questions
What is the formula for ROI in Excel?
Type =(Return-Cost)/Cost with cell references, for example =(C2-B2)/B2, then format the cell as a percentage with Ctrl+Shift+% (Mac: Control+Shift+%).
How do I calculate ROI as a percentage in Excel?
The formula already gives a decimal such as 0.25. Format the cell as Percentage from the Home tab and it shows 25%. Don't multiply by 100 as well, or you'll see 2500%.
How do I calculate annualized ROI in Excel?
Use =(End/Start)^(1/Years)-1. For 10,000 that grows to 14,000 in 3 years, =(14000/10000)^(1/3)-1 gives about 11.87% a year, compared with 40% in total.
What is a good ROI?
It depends on the industry, the risk and how long the money is tied up. Compare ROI against your other options over the same period rather than against a single fixed number.
What is the difference between ROI and profit margin?
ROI divides profit by what you invested. Profit margin divides profit by revenue. The same deal can have a high ROI and a low margin, so label which one you report.