01 / 10Two Projects Passed a Rule They Failed

Financial FunctionsCOUNTIFData Quality

Calculate Payback Period in Excel With Uneven Cash Flows

The Board Approved £390,000 of Energy Projects on a Three-Year Payback Rule and Both Took Four Years or More, Because the Sheet Divided the Cost by the Average Annual Saving

8 Nov 202610 min read

UsesCOUNTIFINDEX MATCHPVAVERAGE

Brindlecombe Dairy approves any energy project that pays for itself within three years. In March the board approved a heat pump and a solar array on that rule. Both took four years or more, and a compressor the sheet rejected paid back in two.

ProjectCostSheet saidActually took
Heat pump£150,0002.81 years4.13 years
Solar array£240,0003.00 years4.00 years
Compressor£45,0003.38 years2.00 years

Scope. COUNTIF, INDEX, AVERAGE, PV, IF, SUM and LOOKUP work the same in every version of Excel since 2007, on Windows, Mac and the web. The XLOOKUP in section 6 needs Excel 2021 or Microsoft 365, and there is a LOOKUP version beside it.

The project list is in A1:K6: cost in B, savings for years 1 to 6 in C to H. Sections 3 to 7 lay one project's cash flows down a column on a second sheet.

01Two Projects Passed a Rule They Failed

Brindlecombe's capital sheet lists each project's cost and the saving the supplier quoted for each of six years. The payback column was one formula, filled down:

=B2/AVERAGE(C2:H2)

Five Energy Projects, Two Ways to Measure Payback

Brindlecombe Dairy's five energy projects in A1:K6. Column B is the cost and C to H the quoted saving for each of six years. Column I is the payback the sheet reported, cost divided by the average saving. Column J is the payback from the cumulative cash flow, and K tests it against the three-year rule.

ABCDEFGHIJK
1
Project
Cost
Year 1
Year 2
Year 3
Year 4
Year 5
Year 6
Cost ÷ average
Payback (years)
Within 3 years?
2
LED lighting
60000
24000
24000
24000
24000
24000
24000
2.5
2.5
TRUE
3
Heat pump
150000
20000
30000
40000
50000
80000
100000
2.81
4.13
FALSE
4
Solar array
240000
30000
50000
70000
90000
110000
130000
3
4
FALSE
5
Compressor
45000
25000
20000
15000
10000
5000
5000
3.38
2
TRUE
6
Insulation
36000
15000
15000
15000
15000
15000
15000
2.4
2.4
TRUE

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

For the LED lighting and the insulation it gives the right answer: 2.50 and 2.40 years. For the heat pump it says 2.81 years, and for the solar array exactly 3.00. Both cleared the three-year bar, and £390,000 was approved.

Column J follows the cash year by year. The heat pump was still £10,000 short at the end of year 4, and the solar array only broke even on the last day of year 4. Neither was close to three years.

Scenario: Enter the five projects in A1:H6. Enter =B2/AVERAGE(C2:H2) in I2, fill it to I6, and check that I3 reads 2.81. Then enter =SUM(C3:E3) and check that the heat pump's first three years return only £90,000.

Try it in the grid

02Cost ÷ Average Assumes Every Year Is Equal

Dividing the cost by the average saving gives payback only when every year saves the same amount. The LED lighting saves £24,000 every year, so the average is each year's figure and 60,000 ÷ 24,000 is the truth.

The heat pump's savings ramp up as the old boilers are retired: £20,000 in year 1, £100,000 in year 6. The average of £53,333 is pulled up by years 5 and 6, then spread back over years 1 to 3 as if that money had arrived early.

YearHeat pump savingAverage used
120,00053,333
230,00053,333
340,00053,333
450,00053,333
580,00053,333
6100,00053,333

The same shortcut fails the other way for the compressor. Its savings fall once the worst air leaks are fixed, from £25,000 to £5,000. The average of £13,333 hides two early years that repay the full £45,000, so a two-year payback was reported as 3.38 and rejected.

The average also depends on how many years you list. Add a seventh year of £100,000 to the heat pump and the shortcut drops to 2.50 years, though nothing in the first four years has changed.

Scenario: Enter =B5/AVERAGE(C5:H5) for the compressor and check that it reads 3.38. Then enter =SUM(C5:D5) and check that its first two years already return £45,000, the full cost.

Try it in the grid

03Build a Cumulative Cash Flow Column

Payback is the moment the running total of cash stops being negative. So build that running total first, one row per year. On a second sheet, lay the heat pump down the page:

Year (A)Cash flow (B)Cumulative (C)
0-150,000-150,000
120,000-130,000
230,000-100,000
340,000-60,000
450,000-10,000
580,00070,000
6100,000170,000

The cost goes in as a negative in year 0. The cumulative starts with =B2 in C2, and every row below adds its own year to the total above:

=C2+B3

Fill it to C8. The sign changes between year 4 and year 5, so the heat pump pays back some way into year 5, nowhere near year 3.

Scenario: On a new sheet, enter years 0 to 6 in A2:A8 and the heat pump's cash flows in B2:B8, with -150000 in B2. Enter =B2 in C2 and =C2+B3 in C3, fill to C8, and check that C6 reads -10,000.

Try it in the grid

04Count the Full Years With COUNTIF

Every year that ends with the cumulative still negative is a full year before payback. COUNTIF counts them in one cell:

=COUNTIF(C3:C8,"<0")

Start the range at C3, year 1, so the year 0 outlay is not counted. For the heat pump it returns 4, because years 1 to 4 all end in the red. Put it in H2.

Zero is not negative. The solar array's cumulative is exactly 0 at the end of year 4, so COUNTIF returns 3 and the next step adds the whole of year 4. That is the right answer: the money is back on the last day of year 4.

Scenario: Enter =COUNTIF(C3:C8,"<0") in H2 and check that it reads 4. Then change B6 to 60000 and watch it drop to 3, because year 4 now ends at exactly 0.

Try it in the grid

05Add the Part-Year With INDEX

The last piece is how far into the next year the cash catches up. Divide what was still owed by that year's cash flow:

=H2-INDEX(C2:C8,H2+1)/INDEX(B2:B8,H2+2)

INDEX(C2:C8,H2+1) is the last negative cumulative, -10,000 at the end of year 4, because C2 is year 0. INDEX(B2:B8,H2+2) is year 5's cash, 80,000. Subtracting a negative adds it: 4 + 10,000 ÷ 80,000 is 4.125.

ProjectFull yearsStill owedNext yearPayback
LED lighting212,00024,0002.50
Heat pump410,00080,0004.13
Solar array390,00090,0004.00
Compressor120,00020,0002.00
Insulation26,00015,0002.40

The fraction assumes the cash arrives evenly through the year. That is the usual convention, and it is close enough for a three-year rule. Say so in the board paper, so nobody reads 4.13 as a date.

If a project never pays back within the years listed, COUNTIF returns 6 and INDEX reaches past B8 with #REF!. Catch it with IF:

=IF(H2=6,"Over 6 years",H2-INDEX(C2:C8,H2+1)/INDEX(B2:B8,H2+2))

Scenario: Enter =H2-INDEX(C2:C8,H2+1)/INDEX(B2:B8,H2+2) in H3 and check that it reads 4.125. Format it to two decimals and compare it with the 2.81 the board was shown.

Try it in the grid

06When the Cumulative Dips Below Zero Again

COUNTIF assumes that once the cumulative turns positive, it stays there. A large cost later in the project breaks that. Brindlecombe's battery store costs £100,000, saves £40,000 a year, and needs £100,000 of new cells in year 4: a net -60,000 that year.

YearCash flowCumulative
0-100,000-100,000
140,000-60,000
240,000-20,000
340,00020,000
4-60,000-40,000
540,0000
640,00040,000

COUNTIF finds three negative years, 1, 2 and 4, and the formula from section 5 returns 3.33. That figure means nothing: the money first came back at 2.50 years and was recovered for good at 5.00.

Use the last negative year instead of the count. On Microsoft 365, XLOOKUP searches from the bottom up with a search mode of -1:

=XLOOKUP(TRUE,C3:C8<0,A3:A8,0,0,-1)

It returns 4. Put that in H2 in place of the count and the section 5 formula returns 5.00. In older versions, =LOOKUP(2,1/(C3:C8<0),A3:A8) returns the same last year.

The two numbers make a check. If COUNTIF and the last negative year disagree, the cumulative went back below zero, and the board paper should give both crossings.

Scenario: Enter the battery store's cash flows in B2:B8 and keep the cumulative in C2:C8. Check that =COUNTIF(C3:C8,"<0") returns 3 and =XLOOKUP(TRUE,C3:C8<0,A3:A8,0,0,-1) returns 4.

Try it in the grid

07Discounted Payback With PV

Plain payback treats £80,000 in year 5 as worth the same as £80,000 today. Discounted payback shrinks each year's cash first, at the company's cost of capital, and then runs the same steps.

Put the rate in H1, 8% at Brindlecombe, and discount each year in column D:

=PV($H$1,A2,0,-B2)

PV returns what a single sum in year A2 is worth today. Year 0 stays at -150,000, and year 5's 80,000 becomes 54,446.66. Build the cumulative in column E exactly as in section 3.

YearDiscountedCumulative
0-150,000.00-150,000.00
118,518.52-131,481.48
225,720.16-105,761.32
331,753.29-74,008.03
436,751.49-37,256.53
554,446.6617,190.12

Run the section 5 formula on columns D and E, with the count of negative discounted years in H4:

=H4-INDEX(E2:E8,H4+1)/INDEX(D2:D8,H4+2)

The heat pump pays back in 4.68 discounted years, against the 2.81 on the sheet the board saw. While the savings are positive, discounting only pushes payback later, so a project that fails plain payback fails this too.

Scenario: Enter 8% in H1 and =PV($H$1,A2,0,-B2) in D2, filled to D8. Build the cumulative in E2:E8, enter =COUNTIF(E3:E8,"<0") in H4, and check that the formula above reads 4.68.

Try it in the grid

08Five Traps

  1. Cost divided by average saving. It is right only when every year is the same. Ramping savings look faster than they are, and falling savings look slower.
  2. Counting year 0. =COUNTIF(C2:C8,"<0") includes the outlay and adds a year to every payback. Start the range at year 1.
  3. Rounding up to whole years. Calling the heat pump "year 5" and the LED lighting "year 3" puts a 2.50 under the same label as a 2.99.
  4. A cumulative that dips back below zero. COUNTIF mixes early and late negative years. Use the last negative year, and report both crossings.
  5. Payback is not profit. The rule ignores everything after the crossing. The compressor pays back fastest and saves least after year 2, so rank projects on NPV.

09Four Exercises

  1. Reproduce it. Enter the five projects and confirm that cost ÷ average puts the heat pump under three years.
  2. Fix it. Build the heat pump's cumulative column and confirm that COUNTIF and INDEX return 4.13.
  3. Break it. Enter the battery store and confirm that COUNTIF gives 3.33 while the last negative year gives 5.00.
  4. Discount it. Add PV at 8% and confirm that the heat pump's discounted payback is 4.68 years.

What to Take Away

Every saving on Brindlecombe's sheet was the supplier's own figure. The error was the average, which moved late savings into the early years, exactly where a three-year rule looks.

Payback is a question about timing, so answer it with a running total. Count the negative years, add the part-year, check that the cumulative never dips back, and discount it before anyone signs.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge