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]

Though this will work
=INDEX(D35:J35,MATCH(TRUE,$D$41:$J$41<0,0)-1)

So
=IF(SUM(D41:J41)<0,INDEX(D35:J35,MATCH(TRUE,$D$41:$J$41<0,0)-1),"No Payback")

 

[sgibbs183]

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

That won't work as in a project we could have total costs up to year 6 so we need to identify in which year the project cummulative cash flows reaches 0

 

[nemmi69]

what about my last formula? Its based on your original.

 

[sgibbs183]

This works! Thank you

 

[nemmi69]

Glad to help