SOLVED

Finding crossing point for positive and negative values

Copper Contributor

I'm trying to figure out a formula that can determine when a retirement account goes negative (i.e. what age I run out of money) based on a column of monthly values.

 

I have a sheet that shows a schedule of retirement account values, with a column for date (monthly), a column for contribution amount (which increases every 12 months), and various scenario columns to show performance with variables for "retirement salary" and "assumed Rate of Return". Eventually, a bunch of the columns cross from positive to negative (run out of money). I'm looking for a formula to put at the top of each column that tells me what month the column changed from positive to negative. I could sort this out with a 'sister' column to each scenario to check for this transition. I'm just looking for something more elegant. Security on my work computer is such that VBA isn't an acceptable solution. Thanks!

3 Replies
best response confirmed by xPR1MUSx (Copper Contributor)
Solution

@xPR1MUSx 

=INDEX($A$2:$A$13,SMALL(IF(B2:B13<=0,ROW($A$1:$A$12)),1))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

find crossing point.png

 

That is perfect! I've used Index, but not that clever. SMALL is new to me. Thanks so much!

@xPR1MUSx 

I suspect the month you want returned depends on whether the 'account' is a month opening or month closing amount.  Anyway, a single schedule would require

= XLOOKUP(0, account, month, "OK", -1)

or, using a Lambda function,

= FinalMonthλ(account)

where FinalMonthλ
= LAMBDA(account, 
    XLOOKUP(0, account, month, "OK", -1)
  )

 

If you have multiple scenarios to analyse simultaneously this would become

Worksheet formula
= BYCOL(table, FinalMonthλ)

 

1 best response

Accepted Solutions
best response confirmed by xPR1MUSx (Copper Contributor)
Solution

@xPR1MUSx 

=INDEX($A$2:$A$13,SMALL(IF(B2:B13<=0,ROW($A$1:$A$12)),1))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

find crossing point.png

 

View solution in original post