# How to calculate the payback period in Excel

Count the years still negative, then add the fraction of the next year needed: `=LET(n,COUNTIF(C2:C7,"<0"),n-1-INDEX(C2:C7,n)/INDEX(B2:B7,n+1))`.

**Excel:** `=LET(n,COUNTIF(C2:C7,"<0"),n-1-INDEX(C2:C7,n)/INDEX(B2:B7,n+1))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=COUNTIF(C2:C7,"<0")-1-INDEX(C2:C7,COUNTIF(C2:C7,"<0"))/INDEX(B2:B7,COUNTIF(C2:C7,"<0")+1)`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Year | Cash flow | Cumulative |  | Payback (years) |
| 0 | -50000 | =B2 |  |  |
| 1 | 12000 | =C2+B3 |  |  |
| 2 | 15000 | =C3+B4 |  |  |
| 3 | 18000 | =C4+B5 |  |  |
| 4 | 16000 | =C5+B6 |  |  |
| 5 | 14000 | =C6+B7 |  |  |

Formula in E2: `=LET(n,COUNTIF(C2:C7,"<0"),n-1-INDEX(C2:C7,n)/INDEX(B2:B7,n+1))`

Result: 3.31

## How it works

The cumulative column is negative for years 0 to 3, so `n` is 4 and the investment is recovered during year 4. At the end of year 3, $5,000 is still unrecovered (`INDEX(C2:C7,n)`), and year 4 brings in $16,000 (`INDEX(B2:B7,n+1)`), so it takes 5,000/16,000 of that year: 3 + 0.3125 = 3.31 years.

The Google Sheets version spells out the count three times instead of naming it with LET.

## Other ways

- `=COUNTIF(C2:C7,"<0")-1-INDEX(C2:C7,COUNTIF(C2:C7,"<0"))/INDEX(B2:B7,COUNTIF(C2:C7,"<0")+1)`: Without LET, for Excel 2019 and earlier.
- `=COUNTIF(C2:C7,"<0")`: The whole number of years until the cumulative cash flow turns positive. (4: payback happens during year 4.)

## Common mistakes

- The formula assumes the cumulative total stays positive once it turns. If a later year is negative again, check by eye.
- Payback ignores the time value of money and everything after payback. Use NPV or IRR to judge the project.
- With even cash flows you don't need any of this: divide the investment by the yearly cash flow.

## Questions

**How do I calculate discounted payback?**

Build the cumulative column from discounted flows, `=C2+B3/(1+$F$1)^A3`, and use the same formula on it.

**How do I build the cumulative column?**

Start with `=B2` in C2, then `=C2+B3` in C3 filled down.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/calculate-the-payback-period