Quick answer
The ROI formula calculates return on investment as gain divided by cost.
=(B3-B2)/B2Syntax
Use this syntax as the pattern, then replace the sample arguments with your own cells, ranges, and criteria.
=(Gain - Cost) / CostWhen to use it
Financial formulas turn business and investment assumptions into numbers you can compare. They are useful for loans, budgets, pricing, margin analysis, investment decisions, and business cases.
- Calculate monthly loan payments
- Estimate return on investment
- Calculate gross margin and markup
- Evaluate investment cash flows
Example worksheet layout
Test the formula in a small worksheet first. This makes it easier to confirm the result before copying the formula down hundreds of rows.
| Column or range | What it represents |
|---|---|
| A | Assumption label |
| B | Input value such as rate, periods, or amount |
| C | Financial result |
Copy-paste examples
These examples move from simple to more practical versions. Start with the beginner examples, confirm the result, then adapt the intermediate and advanced versions.
Basic ROI example
=(B3-B2)/B2Use the simple version to check the business logic first.
Use locked assumption cells
=($B$3-$B$2)/$B$2Lock assumptions when every row should use the same rate, cost, target, or starting value.
Avoid missing-input errors
=IFERROR((B3-B2)/B2,0)Useful when some rows are incomplete or when a denominator can be zero.
Create a display label
=IFERROR("Rate/result: "&TEXT((B3-B2)/B2,"0.00%"),"Check inputs")Use TEXT only for a display cell; keep the raw numeric result for calculations.
Use LET for auditability
=LET(result,(B3-B2)/B2,result)LET makes financial model formulas easier to review.
Dashboard status label
=IFERROR(IF((B3-B2)/B2>=0,"Positive","Negative"),"Check inputs")Use a label when the calculation feeds a dashboard status or exception report.
How to use it step by step
- Put your raw data in clear columns with headers such as Amount, Date, Status, Region, or Customer.
- Paste the starting formula into the first result cell, usually row 2.
- Replace sample references such as A2, B2, or $F$2:$F$100 with the cells in your own worksheet.
- Check the result on a few rows where you already know the expected answer.
- Copy the formula down only after the first row returns the right result.
Common mistakes and fixes
- Percentages are entered as whole numbers instead of percentages, such as 12 instead of 12%.
- Cash inflows and outflows use inconsistent signs.
- The result cell is not formatted as currency, number, or percentage as appropriate.
- Test the formula on a few known rows before using it across a full report.
Beginner tips
- Use a small sample first so you can see exactly how the ROI formula behaves.
- Convert your source range into an Excel Table when the data will grow over time.
- Add a short note near important formulas so future users know what each input represents.