Payback Period from Cash Flows

All versionsRunning total

When does an investment pay for itself? Track the cumulative cash flow and find the first period it turns positive — the payback period, read straight off a running total.

Quick formula: with cash flows in B (period 0 negative), cumulative in C:

=C1 + B2

Each row adds this period’s cash to the running total. The period where cumulative first reaches ≥ 0 is the payback period.

Functions used (tap for the full reference guide):

SUM MATCH

The example

$1,000 outlay returning $400/yr.

	A	B	C
1	Year	Cash flow	Cumulative
2	0	−$1,000	−$1,000
3	1	$400	−$600
4	2	$400	−$200
5	3	$400	$200

The formula

Build the cumulative column, then read the payback:

=C2 + B3 // cumulative =MATCH(TRUE, C2:C9>=0, 0) - 1 // payback period // turns positive in year 3

How it works

Payback is where the running total crosses zero:

Start the cumulative with the initial outlay (a negative number) in period 0.
Each later row: =previousCumulative + thisCashFlow.
The payback period is the first period where the cumulative is ≥ 0.
For a fractional answer, interpolate within the crossing year: full years + unrecovered / cashFlowThatYear.

Discounted payback uses present-valued cash flows instead of raw ones — build the cumulative on cashFlow/(1+rate)^period to account for the time value of money. Plain payback ignores it.

Try it: interactive demo

Live demo

Cash flows (period 0 first, outlay negative).

Payback:

Variations

Fractional payback

Interpolate the crossing year:

=fullYears + unrecovered / yearCashFlow

Period it turns positive

With MATCH:

=MATCH(TRUE, cum>=0, 0) - 1

Discounted payback

PV the flows first:

=cf / (1+rate)^period

Pitfalls & errors

Ignores time value. Plain payback treats year-5 dollars like today’s. Use discounted payback or NPV for investment decisions.

Ignores cash after payback. A fast payback can still be a worse investment than a slower one with bigger later returns — check NPV/IRR too.

Period 0 sign. The initial outlay must be negative, or the cumulative never starts below zero.

Practice workbook

📊

Download the free Payback Period from Cash Flows practice workbook

A payback sheet with cumulative cash flow, fractional, MATCH, and discounted variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate payback period in Excel?

Build a cumulative cash-flow column (=previous + thisFlow, starting with the negative outlay) and find the first period it reaches zero or above.

How do I get a fractional payback period?

Interpolate within the crossing year: fullYears + unrecoveredAmount / thatYear'sCashFlow.

What is discounted payback?

It builds the cumulative on present-valued cash flows (cashFlow/(1+rate)^period), accounting for the time value of money that plain payback ignores.

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

Function references: SUM · MATCH