Spreadsheet Practice

How to calculate the payback period in Excel

Excel formula

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

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)).

Result on the example
3.31 in E2
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Different formula, see below

See it work

The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.

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.

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

Result3.3125

  1. n4

    A name defined in LET, or a named range.

  2. C2:C76 rows × 1 col: -$50,000, -$38,000, -$23,000, -$5,000, …

    A range: 6 cells in one column.

  3. COUNTIF(C2:C7,"<0")4

    COUNTIF: Counts cells that meet one condition.

    • rangeC2:C76 rows × 1 col: -$50,000, -$38,000, -$23,000, -$5,000, …
    • criteria"<0"
  4. n-13

    The - operator subtracts.

  5. INDEX(C2:C7,n)-5000

    INDEX: Value at a given row and column of a range.

    • arrayC2:C76 rows × 1 col: -$50,000, -$38,000, -$23,000, -$5,000, …
    • row_numn4
  6. B2:B76 rows × 1 col: -$50,000, $12,000, $15,000, $18,000, …

    A range: 6 cells in one column.

  7. n+15

    The + operator adds.

  8. INDEX(B2:B7,n+1)16000

    INDEX: Value at a given row and column of a range.

    • arrayB2:B76 rows × 1 col: -$50,000, $12,000, $15,000, $18,000, …
    • row_numn+15
  9. INDEX(C2:C7,n)/INDEX(B2:B7,n+1)-0.3125

    The / operator divides.

  10. n-1-INDEX(C2:C7,n)/INDEX(B2:B7,n+1)3.3125

    The - operator subtracts.

  11. LET(n,COUNTIF(C2:C7,"<0"),n-1-INDEX(C2:C7,n)/INDEX(B2:B7,n+1))3.3125

    LET: Names values inside a formula.

    • name1n4
    • value1COUNTIF(C2:C7,"<0")4
    • name2, value2n-1-INDEX(C2:C7,n)/INDEX(B2:B7,n+1)3.3125

In Google Sheets

Google Sheets needs a different formula: =COUNTIF(C2:C7,"<0")-1-INDEX(C2:C7,COUNTIF(C2:C7,"<0"))/INDEX(B2:B7,COUNTIF(C2:C7,"<0")+1). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=COUNTIF(C2:C7,"<0")-1-INDEX(C2:C7,COUNTIF(C2:C7,"<0"))/INDEX(B2:B7,COUNTIF(C2:C7,"<0")+1)3.31Without LET, for Excel 2019 and earlier.
=COUNTIF(C2:C7,"<0")4.00Different resultThe 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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate the payback period

In E2, calculate how many years it takes for the cumulative cash flow in column C to reach zero, including the fraction of the final year.

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.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.