How to calculate ROI in Excel

The basic ROI formula

The sheet lists six things a small manufacturer spent money on, what each one cost, and the total money it brought back. Subtract the cost from the return to get the gain, then divide by the cost. The brackets matter: =C2-B2/B2 divides first and gives a nonsense number.

The result is a decimal, 2.79. Select the cell and press Ctrl+Shift+% (Home > Number > Percent Style) to show it as a percentage. An ROI of 279% means every dollar spent came back as $3.79: the dollar itself plus $2.79 of gain. To fill the whole column, enter the formula in E2 and double-click the fill handle.

What counts as "return": the gotcha that inflates ROI

ROI only means something if the return is money you actually gained, after the costs of earning it. For a marketing campaign, the $9,100 here is the extra gross profit from the sales it brought in, not the sales revenue. If you put revenue in column C, a campaign that sold $9,100 of product that cost you $5,000 to make looks like 279% when the real ROI is far lower.

Revenue divided by ad spend has its own name, ROAS (return on ad spend), and many ad dashboards report it. It is useful, but it is not ROI. Decide what goes into cost the same way: include the designer's fee and the software, not just the ad budget, and use the same rules for every row so the comparison is fair.

If your return column already holds the gain (profit after costs), do not subtract the cost again. Then ROI is simply =C2/B2.

Negative ROI and comparing projects

The trade show cost $12,750 and brought back $10,900, so the gain is negative and the ROI is about -14.5%. Excel needs nothing special for this; the same formula works. To make losses stand out, use a number format that puts negatives in red, such as 0.0%;[Red]-0.0%.

To rank all six projects in one step in Excel 365 or 2021, SORTBY sorts the names by ROI from highest to lowest. HSTACK puts the ROI next to each name so you can see the numbers too. The ROI is calculated on the fly, so no helper column is needed.

Annualized ROI: fair comparison over different time spans

Plain ROI ignores time. The CNC machine's 67% took four years; the paid search's 69% took one. Annualized ROI asks what steady yearly rate would turn the cost into the return over that many years: (return/cost)^(1/years)-1. It is the same calculation as CAGR.

For the CNC machine that is about 13.7% a year, against 68.6% for paid search and 7.6% for the solar panels over six years. The ranking changes completely once time is included.

Annualizing assumes all the return arrives at the end. If an investment pays back a bit every year, like solar savings, this understates it; use IRR on the yearly cash flows instead.

Do not annualize short campaigns

The email campaign ran for three months, so years is 0.25. Annualizing raises 3.79 to the power of 4, which claims the campaign returns about 20,569% a year. That assumes you could reinvest the gain every quarter at the same rate, which no email list will let you do. Annualized ROI is for comparing investments of a year or more; for short campaigns, report the plain ROI and the period, for example "279% over 3 months".

Total ROI for a group, and which method to pick

For the ROI of everything together, add up the gains and the costs first, then divide. Averaging the six ROI percentages gives about 86%, because the tiny $2,400 campaign counts as much as the $85,000 machine. The correct overall figure is about 61%.

Which to use: plain ROI to compare things over the same period, such as this quarter's campaigns. Annualized ROI to compare investments of a year or more held for different lengths of time. IRR when money comes in or goes out at several points along the way. Margin, which divides the gain by revenue instead of cost, answers a different question: how much of each sale you keep.