Determining Maximum Withdrawal

[walkman99]

Hello all,

I am a novice/intermediate Excel user with Office365. In real life, I'm modeling IRA withdrawals and I'm running into a problem that can be distilled into the mini-sheet included here.

I add or subtract annual interest from the balance using FV and I make a withdrawal based on the amount available. When the interest rate is negative, I am cannot figure out how to determine the maximum withdrawal. If I withdraw the start of year balance, the result is a negative number (which is not OK for the rest of the calculations). If I use the end of year balance (or start of next year), I run into a circular reference.

I feel like I may be making a fundamental mistake, hoping for some direction.

TY

mint_20242.xlsm
	AB	AC	AD
49	Returns
50	-1.0%
51		Investment	withdrawl
52	SoY 1	$100	$100
53	SoY 2	($1)
Spending

Cell Formulas
Range	Formula
AD52	AD52	=AC52
AC53	AC53	=FV($J$35,1,AD52,-AC52)

 

[walkman99]

<solved>
I don't think I worded the question clearly enough to get a good answer, but to close the loop:
Adding the optional type field to the FV function allows me to withdraw the end of year balance rather than the default SoY. This results in a zero balance for the next year (solving the problem).

Thanks for having a look.