Economics - Payout Date Formula, Ending Balance goes to 0 Date Formula

[OilEconomist]

Thanks in advance for any suggestions. I will let you know if any proposed solutions work.


What excel formula (non-VBA) will calculate the:

(1) the first date where the cumulative cash flow goes from negative or zero... to positive (i.e. Undiscounted Payback date). There is the possibility where it could go negative again because of reinvestment. If this happens and there's a way to calculate all dates where this happens that would be great, if not, the first date is the most important.

(2) the first date where the Ending Balance will go to 0. This has to do with a loan where if the Ending Balance is 0, the loan has been paid. The Ending Balance will either be > 0, or 0 (i.e. never a negative number). There is a possibility that there could be a cash call where another loan is needed, so the number can positive again and go back to 0. If this happens and there's a way to calculate all dates where this happens that would be great, if not, the first date is the most important.
​

Currently, I do the following:

(1) To check where the cash flow goes positive to negative, I check the prior month and current month in a row beneath, where it's yes or no. I match where it says "yes" with the formula =IFERROR( INDEX( Date.Series, MATCH( "Yes", Yes_Hurdle.IRR.01, 0)), "N/A")
(2) I do similar for the ending balance (i.e. where it goes from positive to 0).
​

I would like to eliminate the extra step.