cannot resolve circular reference Cell AP35

[hmr2662]

I found the circular reference! Once I mapped out the cell formulas showing cell number and "formulas derived from dependencies" with those cells, I was able to determine that formulas calculating cell L and Z were the culprit. Upon further investigation, I had the formula in cell L for IRA End Balance pulling the IRA withdrawal from CURRENT YEAR instead of prior year. Once I compared 401K end balance and roth ira end balance formulas, I was able to see the error. Praise the Lord! Even with a complicated set of formulas as this workbook is, it all comes down to Core Logic. Your logic must make sense! Thanks for your help.
HR
*******************
This post is in reference to one called "need retirement withdrawal posted on 4/11." I resolved that one as I reworked some formulas and have most of the logic down. Basically, I have most all of the retirement calculator logic worked out so it does the following:
In years where Annual Income<Retirement Goal a retirement Withdrawal is required. That retirement withdrawal will pull 4% from 401K, IRA and Roth IRA end balances respectively. Any additional withdrawals needed to make up the shortfall will be pulled from the investment account with the largest balance. I have added columns with formulas so it is easier to see the logic in cells AN-AW. I have adjusted the formulas so if there are no withdrawals (in years where annual income> retirement goal), those monies are not displayed. The problem is with cell AP35 for computing max IRA withdrawal. I am set with computing max 401K and max Roth IRA Withdrawal. But this is giving me a circular reference error as the max IRA withdrawal is calculated as (.04* ira end bal) and I have the end balance adding back in any withdrawals. I found that I had to do that in order to "establish" the beginning balance for the next year. It should equal end balance from prior year minus any withdrawals to give you a beginning balance for the next year.

I know I am in the home stretch with these formulas and would appreciate any help to finish it up.

Max IRA Withdrawal is AP35=IF(AB35<0,L35*0.04,0)*(-1)
End IRA Bal is L35=IF(B35>Retire_Age+Yrs_InRetire,"",SUM(L34,J35,K35,Z35))
IRA Withdrawal is Z35=IF(B35>Retire_Age+Yrs_InRetire,0,MAX(IF(ISNUMBER(AP35+AU35),AP35+AU35)))
AU35=Additional IRA Withdrawal

I simply do not know how to construct the logic for these formulas any other way.

Thanks
HR

[ben_hensel]

File => Options => Formulas: Calculation Options => Enable Iterative Calcuation = "Yes"

That won't fix the logic if that's what your problem is, but it will allow circular calcuation. Well, it's on you to make sure it's not just wildly oscillating around, without converging, until it hits the iteration ceiling though.

Checking that allowd rows 35 on to do work. Rows 22 - 24 were importing a #VALUE! error from the empty text strings in T, U, V, and X columns.

[hmr2662]

Ben,

Thanks for the information. I was really hoping for some insight as to how to solve the circular reference as I do not want folks to click on this worksheet and get this error. I am just uncertain as to how to structure the formulas for the necessary logic. I thought about nested IF's for the max withdrawals for each investment but I do not think that will do it, as it requires additional logic to determine which account to pull the additional funds from.

Any other thoughts?

HR

[hmr2662]

I thought I would provide the core formulas here in my own reply post to hopefully assist in resolving the circular reference found in AP35 (1st year of retirement) where formulas to calculate max ira withdrawal and beginning ira balance and ira withdrawal are interconnected.

AP35= MAX IRA WITHDRAWAL = IF(AN35<0,L35*0.04,0)*(-1) circular reference
I23 = BEG IRA BALANCE = IF(AND(ISNUMBER(Z22),Z22<0),L22+Z22,L22)
Z = IRA WITHDRAWAL = IF(B35>Retire_Age+Yrs_InRetire,"",MAX(IF(ISNUMBER(AP35+AU35),AP35+AU35)))
L23 = END IRA BALANCE = IF(B23>Retire_Age+Yrs_InRetire,"",SUM(L22,J23,K23,Z23))

B = AGE
J= IRA INTEREST
K = IRA INVESTMENT
V = ANNUAL INCOME (401K INTEREST + IRA INTEREST + ROTH IRA INTEREST + OTHER RETIREMENT + SOCIAL SECURITY)
X = RETIREMENT INCOME GOAL


AB = TOTAL RETIRE WITHDRAW = IF(B22>Retire_Age+Yrs_InRetire,0,SUM((V22-X22),0))
AN = TOTAL WITHDRAWAL NEEDED = F(AND(N(V23),(V23-X23)<0),V23-X23,0)
AO = MAX 401K W/D = IF(AN23<0,G23*0.04,0)*(-1)
AU = ADD'L IRA WQITHDRAWAL NEEDED = IF(AP22<>AW22,0,AS22)
AW = MAXIMUM OF 401K, IRA AND ROTH IRA WITHDRAWALS (WHICH ARE NEGATIVE NUMBERS) = MIN(AO22,AP22,AQ22)
AT = ADDL 401K WITHDRAWAL NEEDED =IF(AO22<>AW22,0,AS22)

401K W/D=IF(B23>Retire_Age+Yrs_InRetire,"",MAX(IF(ISNUMBER(AO23+AT23),AO23+AT23)))
END 401K BAL=IF(B23>Retire_Age+Yrs_InRetire,"",MAX(G22,0)+MAX(E23,0)+MAX(F23,0)-MIN(Y22,0))

Thanks,
HR