Forum Discussion

xPR1MUSx's avatar
xPR1MUSx
Copper Contributor
Oct 17, 2023
Solved

Finding crossing point for positive and negative values

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 s...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Oct 17, 2023

    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.

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 17, 2023

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λ)

 

Resources