Formula to calculate payback periods

[ClaireCat]

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.

https://drive.google.com/file/d/1lambPuZxLW_WneONt3EO4yPgz38p-Sfz/view?usp=sharing

 

[AlanY]

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

 

[ClaireCat]

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.

 

[AlanY]

ok, try this

Book1
	E	F	G	H	I	J	K	L	M	N
7			Period 1	Period 2	Period 3	Period 4	Period 5	Period 6	Period 7	Period 8
8
9	P&L					50	100	100	100	100
10
11	Capex		-100	-100	-100				-50
12
13
14	Cashflow		-100	-100	-100	50	100	100	50	100
15	Cum		-100	-200	-300	-250	-150	-50	0	100
16	Negative cum?
17
18	Fraction		-	-	-	-	-	-	1.0	0.0
19
20
21	Build period		4	periods
22	Payback period		Period 7
Sheet1

Cell Formulas
Range	Formula
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.

 

[ClaireCat]

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?

 

[AlanY]

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?

 

[ClaireCat]

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.

 

[AlanY]

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
	E	F	G	H	I	J	K	L	M	N
7			Period 1	Period 2	Period 3	Period 4	Period 5	Period 6	Period 7	Period 8
8
9	P&L					50	100	100	100	100
10
11	Capex		-100	-100	-100				-50
12
13
14	Cashflow		-100	-100	-100	50	100	100	50	100
15	Cum		-100	-200	-300	-250	-150	-50	0	100
16	Negative cum?
17
18	Fraction		-	-	-	-	-	-	1.0	0.0
19
20	Payback period		4
Sheet1

Cell Formulas
Range	Formula
G20	{=MATCH(TRUE,G15:N15>0,0)-COUNTIF(G11:N11,"<"&0)}
Press CTRL+SHIFT+ENTER to enter array formulas.