ROI & Payback Period

Excel Formulas › Financial

All versionsRatios

Two quick investment yardsticks: ROI (return on investment) is the percentage gain relative to cost, and the payback period is how long until the investment pays for itself. Both are simple ratios that need no special functions.

Quick formula: for a gain in B1 and a cost in B2:
=(B1 - B2) / B2
ROI = net gain ÷ cost, formatted as a percentage. Payback = cost ÷ annual cash flow.

Functions used (tap for the full reference guide):

IFERROR

The example

Invest $5,000, earn $7,000 back (and $2,000/yr).

AB
1MetricValue
2Cost$5,000
3Total return$7,000
4ROI40%
5Payback (at $2k/yr)2.5 yrs

The formula

ROI and payback period:

=(7000 - 5000) / 5000 → 40% ROI =5000 / 2000 → 2.5-year payback

How it works

Two complementary measures:

  1. ROI = (gain − cost) / cost. A $2,000 net gain on a $5,000 cost is 40%. Format the cell as a percentage.
  2. Payback period = cost / annual cash flow. At $2,000/year, a $5,000 investment pays back in 2.5 years.
  3. ROI measures how much you earn; payback measures how fast you recover the outlay — use both.
  4. Neither accounts for the time value of money; for that, pair with NPV or IRR.

Annualize ROI for fair comparison: a 40% return over 3 years isn’t the same as 40% in one. Convert to an annual rate with CAGR — =(end/start)^(1/years)-1 — before comparing investments of different lengths.

Try it: interactive demo

Live demo

Set cost, return, and annual cash flow.

ROI:   Payback:

Variations

Annualized ROI (CAGR)

Fair across different horizons:

=(end/start)^(1/years) - 1

Guard divide-by-zero

If cost could be 0:

=IFERROR((B1-B2)/B2, "n/a")

Payback with uneven cash flow

Use a running total of cash flows and find when it turns positive.

Pitfalls & errors

ROI ignores time. 40% over 5 years is far worse than 40% in 1. Annualize (CAGR) before comparing investments of different durations.

Define “return” consistently. Is it total proceeds or net gain? (gain−cost)/cost vs gain/cost differ by 100% — pick one convention.

Payback ignores what happens after. A fast payback can still be a worse investment overall; check total ROI or NPV too.

Practice workbook

📊
Download the free ROI & Payback Period practice workbook
An ROI and payback calculator with live formulas, the annualized-ROI and divide-by-zero-guard variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate ROI in Excel?
Use =(gain - cost) / cost and format as a percentage, e.g. a $2,000 gain on a $5,000 cost is 40%.
How do I calculate payback period?
Divide the cost by the annual cash flow: =cost / annual_cash_flow. A $5,000 cost returning $2,000/year pays back in 2.5 years.
Should I annualize ROI?
Yes, when comparing investments of different lengths. Convert to an annual rate with CAGR, =(end/start)^(1/years)-1, so the comparison is fair.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Break-even point · CAGR · Net present value (NPV)

Function references: IFERROR