How to calculate the payback period in Excel

Step 1: a cumulative cash flow column

List the years in A, starting at 0 for the upfront cost, and each year's net cash flow in B: the investment as a negative, then the yearly savings or extra profit as positives. The example is an 85,000 packaging machine expected to bring in 22,000, 26,000, 28,000, 24,000, 20,000 and 18,000 over six years.

In C2 enter =B2, then in C3 =C2+B3 and fill down. The cumulative total starts at -85,000 and climbs: -63,000, -37,000, -9,000, then 15,000 in year 4. Payback happens somewhere in the year where it turns from negative to positive.

Step 2: the fractional year with one formula (Excel 365)

XMATCH finds the first row where the cumulative total reaches zero or more; one row before that is the last negative year. LET names that position n, then the formula takes that year and adds the unrecovered amount divided by the next year's cash flow.

The fraction assumes cash comes in evenly through the year. That is the usual convention for payback; if your savings are seasonal, work in months or quarters instead of years.

In Excel 2019 or earlier there is no LET or XMATCH. Use the helper column in the next step, or MATCH(TRUE,C2:C8>=0,0), which must be entered with Ctrl+Shift+Enter in those versions.

Step 3: a helper column you can see (any version)

If you want the logic on the sheet, add a Crossing column. In F2 enter =IF(AND(C2<0,C3>=0),A2-C2/B3,"") and fill down to the row before the last year. It stays blank except in the year where the cumulative total crosses zero, where it shows the payback.

Then =MAX(F2:F7) picks out the number. A visible column is easier to audit than one long formula, and it works in every version of Excel.

When cash flow dips negative later

Real projects are not always a clean climb. Columns G and H show the same machine with a 45,000 overhaul in year 5, so that year's net flow is -25,000. The cumulative total goes positive in year 4 (15,000), back to -10,000 in year 5, and positive again in year 6 (8,000).

The first crossing still says 3.375 years, which is optimistic: the money is not truly recovered until year 6. To find the last crossing, search from the bottom with XMATCH's search mode -1, or use MAX on the helper column in I, which has two crossings (3.375 and 5.56) and returns the later one.

A popular shortcut, COUNTIF(H3:H8,"<0") to count the negative years, breaks here too: it counts four negative years and gives 4.6, a payback that matches neither crossing.

Discounted payback

Plain payback treats 24,000 in year 4 as worth the same as 24,000 today. Discounted payback converts each year's cash flow to today's money first, using a discount rate in an input cell. K1 holds 8% as an example; use the rate your business uses for investment decisions.

In D2, =B2/(1+$K$1)^A2 discounts each year's flow (year 0 is divided by 1, so it stays -85,000). Build the cumulative column E exactly as before, then run the same payback formula on D and E. After year 4 the discounted total is still -2,471, and year 5's 20,000 is worth 13,612 today.

Discounted payback is always longer than plain payback when the rate is above zero. It still ignores everything after the payback year, so pair it with NPV if the later years matter.

Which method to pick

Equal cash flows every year: cost divided by the yearly amount is enough. Uneven flows: the cumulative column with the fractional year. Any year after payback can go negative (an overhaul, a replacement, a lost contract): find the last crossing, not the first. Comparing projects or long horizons: add discounted payback, and look at NPV and IRR as well, because payback says nothing about the cash after the payback date.

Payback answers "how long is our money at risk?". A project with a 2-year payback and nothing after it can be worse than a 4-year payback that keeps paying for a decade.