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.
| Project | Cost | Sheet said | Actually took |
|---|---|---|---|
| Heat pump | £150,000 | 2.81 years | 4.13 years |
| Solar array | £240,000 | 3.00 years | 4.00 years |
| Compressor | £45,000 | 3.38 years | 2.00 years |
Scope.
COUNTIF,INDEX,AVERAGE,PV,IF,SUMandLOOKUPwork the same in every version of Excel since 2007, on Windows, Mac and the web. TheXLOOKUPin section 6 needs Excel 2021 or Microsoft 365, and there is aLOOKUPversion 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.
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.
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.
| Year | Heat pump saving | Average used |
|---|---|---|
| 1 | 20,000 | 53,333 |
| 2 | 30,000 | 53,333 |
| 3 | 40,000 | 53,333 |
| 4 | 50,000 | 53,333 |
| 5 | 80,000 | 53,333 |
| 6 | 100,000 | 53,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.
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 |
| 1 | 20,000 | -130,000 |
| 2 | 30,000 | -100,000 |
| 3 | 40,000 | -60,000 |
| 4 | 50,000 | -10,000 |
| 5 | 80,000 | 70,000 |
| 6 | 100,000 | 170,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.
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.
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.
| Project | Full years | Still owed | Next year | Payback |
|---|---|---|---|---|
| LED lighting | 2 | 12,000 | 24,000 | 2.50 |
| Heat pump | 4 | 10,000 | 80,000 | 4.13 |
| Solar array | 3 | 90,000 | 90,000 | 4.00 |
| Compressor | 1 | 20,000 | 20,000 | 2.00 |
| Insulation | 2 | 6,000 | 15,000 | 2.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.
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.
| Year | Cash flow | Cumulative |
|---|---|---|
| 0 | -100,000 | -100,000 |
| 1 | 40,000 | -60,000 |
| 2 | 40,000 | -20,000 |
| 3 | 40,000 | 20,000 |
| 4 | -60,000 | -40,000 |
| 5 | 40,000 | 0 |
| 6 | 40,000 | 40,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.
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.
| Year | Discounted | Cumulative |
|---|---|---|
| 0 | -150,000.00 | -150,000.00 |
| 1 | 18,518.52 | -131,481.48 |
| 2 | 25,720.16 | -105,761.32 |
| 3 | 31,753.29 | -74,008.03 |
| 4 | 36,751.49 | -37,256.53 |
| 5 | 54,446.66 | 17,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.
08Five Traps
- 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.
- Counting year 0.
=COUNTIF(C2:C8,"<0")includes the outlay and adds a year to every payback. Start the range at year 1. - 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.
- A cumulative that dips back below zero.
COUNTIFmixes early and late negative years. Use the last negative year, and report both crossings. - 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
- Reproduce it. Enter the five projects and confirm that cost ÷ average puts the heat pump under three years.
- Fix it. Build the heat pump's cumulative column and confirm that
COUNTIFandINDEXreturn 4.13. - Break it. Enter the battery store and confirm that
COUNTIFgives 3.33 while the last negative year gives 5.00. - Discount it. Add
PVat 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.