Calculate the Payback Period in Excel

The payback period is the time an investment takes to earn back what it cost. Excel has no payback period function, so you build it in three steps: list the cash flows, add a cumulative total with SUM, and find the year the total turns positive with IF. This guide is for anyone comparing projects, equipment purchases or business ideas, and for candidates who meet payback questions in a finance Excel test.

What is the payback period?#

The payback period is the number of years until the money coming in equals the money you spent. A project that costs 10,000 and returns 2,500 a year pays back in 4 years.

When every year's cash flow is the same, the formula is a simple division:

=10000/2500

That returns 4. Real projects rarely earn the same amount each year, though. When the cash flows differ, you need a running total to see when the investment is covered. That is what the rest of this guide builds.

A shorter payback means your money is at risk for less time. That makes it a popular first check, next to the NPV function, which tells you whether a project is worth doing at all.

Set up the cash flows#

Put one year per row, with the investment as a negative number in year 0. Money out is negative, money in is positive.

Year 0 is "today", when you pay for the investment. Years 1 to 5 are the cash the project brings in. If your forecast comes from monthly figures, the cash flow forecast guide shows how to build those first.

Build a cumulative cash flow column with SUM#

The cumulative column shows how much is still unrecovered at the end of each year. In C2, type:

=SUM($B$2:B2)

Then fill it down to C7 (Windows: select C2:C7 and press Ctrl+D; Mac: ⌘D). The first cell, $B$2, is locked with dollar signs, so the range grows as you fill: C3 becomes =SUM($B$2:B3), C4 becomes =SUM($B$2:B4), and so on. This is a running total, and the article on how to build a running total explains it in more detail.

To add the dollar signs quickly, click inside the reference and press F4 on Windows or ⌘T on a Mac. The absolute references lesson lets you practise exactly this.

The cumulative total is negative in years 0, 1 and 2, then turns positive in year 3. So the investment pays back during year 3.

Find the payback year with IF#

Payback happens in the row where the previous total is negative and the current total is not. An IF formula can check that and calculate the exact point. In D3, type:

=IF(AND(C2<0,C3>=0),A2-C2/B3,"")

Fill it down to D7. Only one row passes the test. In D5 the formula becomes:

=IF(AND(C4<0,C5>=0),A4-C4/B5,"")

It returns 2.875. Here is what each part does:

• AND(C4<0,C5>=0) checks that the total was still negative last year and is not negative this year.
• A4 is the last full year before payback: 2.
• -C4/B5 is the fraction of year 3 needed: 3,500 still unrecovered ÷ 4,000 earned that year = 0.875. The minus sign turns the negative 3,500 into a positive number.
• "" leaves every other row blank.

So the payback period is 2.875 years, or about 2 years and 10.5 months (0.875 × 12 = 10.5). The IF function guide covers IF and AND in more detail.

Calculate the payback period in one formula#

If you want the answer in a single cell, count the negative years and pick the right values with INDEX:

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

This also returns 2.875. COUNTIF(C3:C7,"<0") counts the full years after year 0 that are still negative: 2. The first INDEX picks the last negative total (-3,500), and the second picks the next year's cash flow (4,000).

This version assumes the total crosses zero only once, which is true for a normal project: one big cost, then income. If the project never pays back, the second INDEX points past the range and returns #REF!. Wrap it in a check such as =IF(C7<0,"Not paid back", ...) to show a clear message instead.

Discounted payback period#

The discounted payback period uses cash flows that are adjusted for the time value of money: 1,000 next year is worth less than 1,000 today. Divide each cash flow by (1 + rate) to the power of its year, then repeat the same steps.

With a discount rate of 8% in F1, put this in a new column and fill it down:

=B2/(1+$F$1)^A2

The cumulative total turns positive in year 4, so the discounted payback is 3 + 1,046.21 ÷ 2,940.12 = about 3.36 years. It is always longer than the simple payback, because later money counts for less.

Common payback period mistakes#

Most wrong answers come from the setup, not the formula. Check these:

1. The investment is positive. If year 0 is 10,000 instead of -10,000, the cumulative total is never negative and nothing is found.
2. No dollar signs in the SUM. =SUM(B2:B2) filled down only adds one cell per row. Lock the start: $B$2.
3. Using the wrong year. Add the fraction to the last negative year (2), not the payback year (3).
4. Dividing by the cumulative total. Divide the unrecovered amount by that year's cash flow (B5), not by the cumulative figure (C5).
5. Reading payback as profit. Payback ignores everything after the break-even year. A project with a short payback can still earn less overall. Compare it with NPV, and see the break-even analysis guide for the unit-based version of the same idea.

Practise it#

The payback period is just SUM, IF and a locked reference working together. Try the exercise on this page, then practise SUM in a live spreadsheet and build conditions in the IF function lesson: you type each formula and it is checked instantly. The SUM function guide and IF function guide are quick references for the syntax. For finance-style questions under time pressure, try the finance Excel mock test.