# Formula to calculate payback periods

#### ClaireCat

##### New Member
Hi all,

I am trying to enter a formula to calculate how many periods it will take to payback. The formula I have works well unless someone enters additional capex at a later date - then it all goes to pot.

The original calculation is on sheet 2 - but I'm playing around with sheet 1 to try and get a formula that works.

If anyone could help, or has any suggestions, they would be very much appreciated.

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
so, are you expecting the Payback period on Sheet 1 to be Period 7?

so, are you expecting the Payback period on Sheet 1 to be Period 7?

Yes. my old formula (sheet 2) works fine, but if I put -50 in capex in period 7, I want a formula that will manage to cope with that and still work it out.

ok, try this

Book1
EFGHIJKLMN
7Period 1Period 2Period 3Period 4Period 5Period 6Period 7Period 8
8
9P&L50100100100100
10
11Capex-100-100-100-50
12
13
14Cashflow-100-100-1005010010050100
15Cum-100-200-300-250-150-500100
16Negative cum?
17
18Fraction------1.00.0
19
20
21Build period4periods
22Payback periodPeriod 7
Sheet1
Cell Formulas
RangeFormula
G14=SUM(G9:G11)
G15=SUM(\$G9:G11)
G22{=INDEX(G7:N7,MATCH(TRUE,G15:N15>=0,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Excellent, that worked a treat.

Is is possible to get it, instead of telling me which period it becomes positive, for it to tell me how many periods it took without capex in it to become positive?

So, in this case, it would say 4?

Excellent, that worked a treat.

Is is possible to get it, instead of telling me which period it becomes positive, for it to tell me how many periods it took without capex in it to become positive?

So, in this case, it would say 4?

not quite sure I understand the RED marked above.
could you elaborate a bit more?

not quite sure I understand the RED marked above.
could you elaborate a bit more?

Each period that has a capex entry is a period in which we are building kit and not carrying out actual work at a client. I need a count of how many months it takes when we are not building to make our money back.

So in the above example, there are 4 months with capex in it, so I don't want to count them.
Without capex, it is period 8 in which we are not building and making money - which is 4 periods of non-build.

It's the 4 periods of non-build that I'm interested in.

Each period that has a capex entry is a period in which we are building kit and not carrying out actual work at a client. I need a count of how many months it takes when we are not building to make our money back.

So in the above example, there are 4 months with capex in it, so I don't want to count them.
Without capex, it is period 8 in which we are not building and making money - which is 4 periods of non-build.

It's the 4 periods of non-build that I'm interested in.

sorry, it's really out of my depth re P&L, capex etc.
will this work?

Book1
EFGHIJKLMN
7Period 1Period 2Period 3Period 4Period 5Period 6Period 7Period 8
8
9P&L50100100100100
10
11Capex-100-100-100-50
12
13
14Cashflow-100-100-1005010010050100
15Cum-100-200-300-250-150-500100
16Negative cum?
17
18Fraction------1.00.0
19
20Payback period4
Sheet1
Cell Formulas
RangeFormula
G20{=MATCH(TRUE,G15:N15>0,0)-COUNTIF(G11:N11,"<"&0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Last edited:

Replies
6
Views
203
Replies
7
Views
140
Replies
2
Views
67
Replies
1
Views
181
Replies
7
Views
302

1,203,462
Messages
6,055,562
Members
444,799
Latest member
CraigCrowhurst

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back