Payback Period using Lookup is not working

[sgibbs183]

Hello,

I cannot get my head around the payback period not working in excel using a lookup.
Cell D50 shows formula =IF(SUM(D41:J41)<0,LOOKUP(0,D41:J41,D35:J35)+1,"No Payback") which looks up 0 in the cumulative cash flows to return the year+1 which should be year 4, but it is returning year 7. Please help

 

[nemmi69]

D35:J35 is why it returns 7

 

[nemmi69]

Sorry, meant to say. Its the way the functions work. Seems it finds the last matching occurrence. Give me a bit may have a way to do it.

 

[nemmi69]

try
=INDEX(D35:J35,MATCH("-",D39:J39,0))

 

[sgibbs183]

That doesn't work, it is returning year 4...

 

[nemmi69]

That doesn't work, it is returning year 4...

That's what you asked for

 

[sgibbs183]

Sorry that was a typo - it should return year 2

 

[nemmi69]

So why would it be 2?

 

[sgibbs183]

Because the costs on here are showing as positive, and teh beenfits as negative, so when the cumulative cash flows reaches NIL is when we start seeing a payback for the project which is in year 2 - hence why I was looking up 0 in the lookup formula to start with

 

[nemmi69]

Could you reference the total costs? If so =INDEX(D35:J35,MATCH("-",D37:J37,0)-1)