Payback Period formula: interpolated fractional years to break even on CapEx

Interpolate the crossover year from cumulative cash flows

For a -$100,000 equipment outlay followed by positive annual inflows (+$30k, +$45k, +$50k, +$40k), cumulative cash flow moves from -$100k (Yr 0) -> -$70k (Yr 1) -> -$25k (Yr 2) -> +$25k (Yr 3).

Counting negative cumulative rows AFTER Year 0 gives full unrecovered years k = COUNTIF(CumYr1:CumYrN, "<0") = 2. Dividing the remaining unrecovered balance at the end of Year k by Year k+1's cash flow ($25,000 / $50,000 = 0.50) yields an interpolated Payback Period of 2.50 years!

Interpolated payback within the forecast horizon

Test C6 < 0 first so projects whose final cumulative cash flow is still negative return "Not recovered in forecast" cleanly.

=IF(C6<0, "Not recovered in forecast", LET(k, COUNTIF(C3:C6, "<0"), ROUND(k + ABS(INDEX(C2:C6, k + 1)) / INDEX(B2:B6, k + 2), 2)))

Pair Payback with Net Present Value (NPV + Year 0)

Always check NPV alongside Payback because Payback ignores time value of money and post-payback cash flows.

=ROUND(B2 + NPV(0.1, B3:B6), 2)

Inputs and interpretation

This interpolation assumes a negative initial outlay followed by strictly positive annual inflows, with cumulative sums correctly calculated. It assumes cash arrives uniformly during the recovery year; end-of-year receipts would give a different recovery time. Nonmonotonic cash flows or zero-flow plateaus need a first-crossing method. The NPV companion instead uses end-of-year cash-flow timing at an illustrative 10% rate.

Further reading

• Microsoft Support — NPV function