Formula to Count Years Above Zero

Please attach a sample file using non-confidential (but realistic) data to illustrate your problem.

It would help if you include some examples where your formula does, and where it doesn't work, with the expected result entered manually for the latter examples.

Great idea!

I've attached a sample file that shows my problem.

The variables are all on the left, and a yearly schedule is on the right. The important thing is the values in the yellow box. This equation works perfectly as long as the annuity stays positive. The equation for Annuity 1 matches the last value in the yearly schedule. But once Annuity 2 goes negative, then the math has to change. The final amount in the yearly schedule is correct, the value in the yellow box is not.

I have far too many of these to maintain yearly schedules for each account and have them update automatically as each year goes by. So that's why I need to somehow calculate the expected value until it reaches zero and then switch to a new formula.

Please let me know if that doesn't make sense.

Finance formulas are not something that I understand well, so it is possible that there could be a simpler way, but this does work.

Array formula...

PHP Code: 

=FV($D$6,MIN(MATCH(0,FV($D$6,ROW(INDIRECT("1:"&$D$4)),D13,-D9),-1)+1,$D$4),D13,-D9)-(($D$4-MIN(MATCH(0,FV($D$6,ROW(INDIRECT("1:"&$D$4)),D13,-D9),-1)+1,$D$4))*D13) 


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Jason, I'm in awe! That formula is a work of art. Thanks so much!!

The formula works perfectly in my example workbook, but as soon as I add it to my actual workbook I get a #REF! error. Yes, I pushed Shift, Control, Enter.

I had excel evaluate the formula, and it specifically doesn't like the INDIRECT("1:"&$C$4)).

I've copied the formula directly into the workbook. I've tried replacing each of the values. I moved things around. I just can't get it to work in this one workbook.

Any ideas?

What is in C4?

That part of the formula generates an array to evaluate the expected value for each period in the life of the annuity, so the content of C4 must be valid for that purpose.

It can't cope with partial periods thought, the number in C4 must be a whole number.

If you need partial periods, then we will need to re-evaluate the process.

Originally Posted by oeldere

Just a guess:

So now we have to guess the point you are trying to make?

An observation is useless without information.

Originally Posted by oeldere

My formula will do that (and for Annuity 2 that is 8 years).

And it is actually 9 years, the 8th year value is still positive.

Jason, that's exactly what it was. Once I rounded the life expectancies, everything works just fine. Thanks!!


Oeldere, I see that you're calculating the point of zero. My issue is that I needed a self contained formula for a larger table, since I won't be able to maintain yearly information as I did in the example workbook.