Payback period in Excel: simple and discounted payback

The payback period formula

For a project with the same cash flow every year the formula is a single division. A $60,000 investment that saves $15,000 a year pays back in 60,000 ÷ 15,000 = 4 years.

Most real projects have uneven cash flows: a slow first year, a few strong years, then a decline. The method is then to add the cash flows up year by year, the cumulative cash flow, and find the point where it crosses zero:

Payback = A + B ÷ C, where A is the last year with a negative cumulative cash flow, B is the absolute value of the cumulative cash flow at the end of that year, and C is the cash flow of the following year.

The fraction assumes the cash arrives evenly during that year. If it does not, for example because a large payment comes at year end, the true payback is later. Monthly cash flows give a more precise answer.

Step by step with a cumulative cash flow column

1. List the years in column A, starting with year 0 for the investment, and the cash flows in column B. The investment is negative.
2. In C2 enter =B2, in C3 =C2+B3, and fill down. This is the running total.
3. Find the last negative value. In the example the cumulative cash flow is −$20,000 at the end of year 3 and +$6,000 at the end of year 4, so the payback falls in year 4.
4. Add the fraction: 3 + 20,000 ÷ 26,000 = 3.77 years. Multiply by 12 for months: 45.2 months, or 3 years and a bit over 9 months.

Notice that the divisor is the year-4 cash flow ($26,000), not the cumulative figure. Dividing by the cumulative $6,000 is a common slip that gives a nonsensical result.

One formula instead of reading the table

Reading the crossing point by eye is fine once, but a formula updates when the inputs change. With the cumulative cash flow in C2:C8 and the cash flows in B2:B8 (year 0 in row 2):

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

n counts the negative cumulative values, which is the number of years before the crossing including year 0. Because year 0 sits in row 2, the last negative year is n − 1, its cumulative value is the n-th entry of the range, and the next year's cash flow is entry n + 1. The minus sign turns the negative unrecovered amount into a positive fraction. For the example, n = 4, so the formula computes 3 − (−20,000) ÷ 26,000 = 3.77.

An alternative without LET is a helper column with =IF(AND(C2<0,C3>=0),A2-C2/B3,"") filled down, which shows the payback only in the row where the crossing happens. Both versions assume the cumulative cash flow turns positive once and stays positive. If a later year is negative again, check the table by eye.

Worked example: simple and discounted payback of an $85,000 machine

Cash flows, the discount rate and the target are inputs (yellow). The cumulative columns are filled down, and one formula each finds the payback.

Worked example: simple and discounted payback of an $85,000 machine

	A	B	C	D	E	F
1	Year	Cash flow ($)	Cumulative ($)	Discount factor	Discounted cash flow ($)	Cumulative discounted ($)
2	0	-85,000	-85,000	1.0000	-85,000	-85,000
3	1	18,000	-67,000	0.9259	16,667	-68,333
4	2	22,000	-45,000	0.8573	18,861	-49,472
5	3	25,000	-20,000	0.7938	19,846	-29,626
6	4	26,000	6,000	0.7350	19,111	-10,515
7	5	24,000	30,000	0.6806	16,334	5,819
8	6	20,000	50,000	0.6302	12,603	18,422
9
10	Measure					Result
11	Discount rate					8.0%
12	Target payback (years)					4.0
13	Simple payback (years)					3.77
14	Simple payback (months)					45.2
15	Discounted payback (years)					4.64
16	NPV over the six years					18,422
17	Simple payback within target?					Yes
18	Discounted payback within target?					No

Fictional project with illustrative cash flows, not investment advice. Cash flows within a year are assumed to arrive evenly.

Discounted payback period

The simple payback treats $1 in year 4 like $1 today. The discounted payback first converts every cash flow into its present value and then runs the same method on the cumulative discounted cash flow.

The discount factor for year t is =1/(1+rate)^t, so at 8% the year-4 factor is 0.7350 and the $26,000 is worth $19,111 today. The cumulative discounted cash flow is still −$10,515 after year 4 and turns positive in year 5, which contributes $16,334 in present-value terms. The discounted payback is 4 + 10,515 ÷ 16,334 = 4.64 years, almost a year longer than the simple payback. The same LET formula works on the discounted columns E and F.

With a positive discount rate and positive inflows, the discounted payback is always longer than the simple payback. If the cumulative discounted cash flow never turns positive within the project's life, the project's NPV at that rate is negative.

What payback does not tell you

Payback measures speed of recovery, not profitability. It ignores everything after the payback point: here years 5 and 6 bring another $44,000 that the payback period does not see. Two projects with the same payback can have very different values, and a project with a slightly longer payback can be worth much more.

That is why payback is usually used as a screen for liquidity and risk next to a value measure. The example's NPV at 8% over six years is $18,422 (the last cell of the cumulative discounted column). Many companies set a maximum payback, such as the 4 years used here: the simple payback meets it, the discounted payback does not. The target is a business choice, not a rule. For the value side, see the IRR guide and the present worth analysis example; for the volume needed to cover fixed costs, see break-even analysis.

Common mistakes

• Dividing by the cumulative cash flow. The fraction is the unrecovered amount divided by that year's cash flow, not by the cumulative total.
• Counting year 0 as a year. The investment happens at time 0. If the cumulative cash flow is still negative at the end of year 3, the answer is 3 point something, not 4 point something.
• Rounding up without saying so. 3.77 years is not 4 years. If you report whole years, say that you rounded up.
• Ignoring later negative cash flows. A major overhaul in a later year can make the cumulative cash flow negative again. Simple formulas find only the first crossing.
• Treating payback as a profitability measure. Payback ignores the cash after the payback point and, in its simple form, the time value of money. Look at NPV as well.
• Mixing months and years. With monthly cash flows the result is in months. Divide by 12 before comparing with a target in years.