Which "IF" statements, Cheego? In what cells of my last spreadsheet? (I sent you a private message two days ago with this question.)

was very difficult for me. There may be an easier way, but I had to add about 35 rows that combine the federal and state tax brackets. I explain this later. But to get an answer for you, Cheego, I set the state standard deduction to $0 and the tax rate to 7.75% for every state bracket. Here is what the results look like with this change. As explained in my previous post, to have the "Years" calculated, I entered values in column C for three of the four possible items (Return, Starting Portfolio, and Ending Portfolio), and blanked the cell in column C for Years, the fourth item.

Here is what the results looked like when I set it up for California to test my approach. I used the standard deduction and tax brackets from the

often references.

To make this work, I first added rows 7 to 13 for the 2018 federal tax brackets and rows 14 to 23 for the California brackets. "Use" in Column D does two things. It selects either Single or Joint based on the assumption in cell B1, and also adjusts the brackets for "Other taxable income" and "Standard deduction" in cells B3:C4. This makes both the federal and state brackets correspond to the taxable IRA withdrawal and not to taxable income. Column A uses the Excel

to rank each of the federal and state tax brackets. E.g., the 22% federal bracket (# 5 $16,700) comes between the 2% and 3% CA brackets (#4 $12,479 and #6 $23,731).

Rows 25 to 41 order the federal and state brackets according to their rank previously determined. The "Comb" rate in column E combines the federal and state tax rates for each of the Combined Brackets. Formulas in cells B43 and B44 use this table to back into the amount of the pre tax IRA withdrawal necessary to produce the desired

For those wishing to use my new spreadsheet, first Select All, Copy, and Paste the following at cell A1 of a blank Excel sheet. (If you have problems pasting, refer to

Code: Select all

```
Single = 1 Joint = 2 1
Federal California
Other taxable income 34000 0
Standard deduction 12000 4236
Bracket
Rank Single Joint Use Rate
=RANK(D7,D$7:D$23,1) 0 0 =IF(B$1=1,B7,C7)-B$3+B$4 0.1
=RANK(D8,D$7:D$23,1) 9525 19050 =IF(B$1=1,B8,C8)-B$3+B$4 0.12
=RANK(D9,D$7:D$23,1) 38700 77400 =IF(B$1=1,B9,C9)-B$3+B$4 0.22
=RANK(D10,D$7:D$23,1) 82500 165000 =IF(B$1=1,B10,C10)-B$3+B$4 0.24
=RANK(D11,D$7:D$23,1) 157500 315000 =IF(B$1=1,B11,C11)-B$3+B$4 0.32
=RANK(D12,D$7:D$23,1) 200000 400000 =IF(B$1=1,B12,C12)-B$3+B$4 0.35
=RANK(D13,D$7:D$23,1) 500000 600000 =IF(B$1=1,B13,C13)-B$3+B$4 0.37
=RANK(D14,D$7:D$23,1) 0 0 =IF(B$1=1,B14,C14)-C$3+C$4 0.01
=RANK(D15,D$7:D$23,1) 8223 16446 =IF(B$1=1,B15,C15)-C$3+C$4 0.02
=RANK(D16,D$7:D$23,1) 19495 38990 =IF(B$1=1,B16,C16)-C$3+C$4 0.03
=RANK(D17,D$7:D$23,1) 30769 61538 =IF(B$1=1,B17,C17)-C$3+C$4 0.04
=RANK(D18,D$7:D$23,1) 42711 85422 =IF(B$1=1,B18,C18)-C$3+C$4 0.08
=RANK(D19,D$7:D$23,1) 53980 107960 =IF(B$1=1,B19,C19)-C$3+C$4 0.093
=RANK(D20,D$7:D$23,1) 275738 551476 =IF(B$1=1,B20,C20)-C$3+C$4 0.103
=RANK(D21,D$7:D$23,1) 330884 661768 =IF(B$1=1,B21,C21)-C$3+C$4 0.113
=RANK(D22,D$7:D$23,1) 551473 1000000 =IF(B$1=1,B22,C22)-C$3+C$4 0.123
=RANK(D23,D$7:D$23,1) 1000000 1074996 =IF(B$1=1,B23,C23)-C$3+C$4 0.133
Bracket Federal State Combined Tax After Tax
1 =VLOOKUP($A25,$A$7:$E$23,4,FALSE) =VLOOKUP($B25,$D$7:$E$13,2,TRUE) =VLOOKUP($B25,$D$14:$E$23,2,TRUE) =IF(ISERROR(C25),0,C25)+IF(ISERROR(D25),0,D25) 0 =B25-F25
=A25+1 =VLOOKUP($A26,$A$7:$E$23,4,FALSE) =VLOOKUP($B26,$D$7:$E$13,2,TRUE) =VLOOKUP($B26,$D$14:$E$23,2,TRUE) =IF(ISERROR(C26),0,C26)+IF(ISERROR(D26),0,D26) =F25+E25*(B26-B25) =B26-F26
=A26+1 =VLOOKUP($A27,$A$7:$E$23,4,FALSE) =VLOOKUP($B27,$D$7:$E$13,2,TRUE) =VLOOKUP($B27,$D$14:$E$23,2,TRUE) =IF(ISERROR(C27),0,C27)+IF(ISERROR(D27),0,D27) =F26+E26*(B27-B26) =B27-F27
=A27+1 =VLOOKUP($A28,$A$7:$E$23,4,FALSE) =VLOOKUP($B28,$D$7:$E$13,2,TRUE) =VLOOKUP($B28,$D$14:$E$23,2,TRUE) =IF(ISERROR(C28),0,C28)+IF(ISERROR(D28),0,D28) =F27+E27*(B28-B27) =B28-F28
=A28+1 =VLOOKUP($A29,$A$7:$E$23,4,FALSE) =VLOOKUP($B29,$D$7:$E$13,2,TRUE) =VLOOKUP($B29,$D$14:$E$23,2,TRUE) =IF(ISERROR(C29),0,C29)+IF(ISERROR(D29),0,D29) =F28+E28*(B29-B28) =B29-F29
=A29+1 =VLOOKUP($A30,$A$7:$E$23,4,FALSE) =VLOOKUP($B30,$D$7:$E$13,2,TRUE) =VLOOKUP($B30,$D$14:$E$23,2,TRUE) =IF(ISERROR(C30),0,C30)+IF(ISERROR(D30),0,D30) =F29+E29*(B30-B29) =B30-F30
=A30+1 =VLOOKUP($A31,$A$7:$E$23,4,FALSE) =VLOOKUP($B31,$D$7:$E$13,2,TRUE) =VLOOKUP($B31,$D$14:$E$23,2,TRUE) =IF(ISERROR(C31),0,C31)+IF(ISERROR(D31),0,D31) =F30+E30*(B31-B30) =B31-F31
=A31+1 =VLOOKUP($A32,$A$7:$E$23,4,FALSE) =VLOOKUP($B32,$D$7:$E$13,2,TRUE) =VLOOKUP($B32,$D$14:$E$23,2,TRUE) =IF(ISERROR(C32),0,C32)+IF(ISERROR(D32),0,D32) =F31+E31*(B32-B31) =B32-F32
=A32+1 =VLOOKUP($A33,$A$7:$E$23,4,FALSE) =VLOOKUP($B33,$D$7:$E$13,2,TRUE) =VLOOKUP($B33,$D$14:$E$23,2,TRUE) =IF(ISERROR(C33),0,C33)+IF(ISERROR(D33),0,D33) =F32+E32*(B33-B32) =B33-F33
=A33+1 =VLOOKUP($A34,$A$7:$E$23,4,FALSE) =VLOOKUP($B34,$D$7:$E$13,2,TRUE) =VLOOKUP($B34,$D$14:$E$23,2,TRUE) =IF(ISERROR(C34),0,C34)+IF(ISERROR(D34),0,D34) =F33+E33*(B34-B33) =B34-F34
=A34+1 =VLOOKUP($A35,$A$7:$E$23,4,FALSE) =VLOOKUP($B35,$D$7:$E$13,2,TRUE) =VLOOKUP($B35,$D$14:$E$23,2,TRUE) =IF(ISERROR(C35),0,C35)+IF(ISERROR(D35),0,D35) =F34+E34*(B35-B34) =B35-F35
=A35+1 =VLOOKUP($A36,$A$7:$E$23,4,FALSE) =VLOOKUP($B36,$D$7:$E$13,2,TRUE) =VLOOKUP($B36,$D$14:$E$23,2,TRUE) =IF(ISERROR(C36),0,C36)+IF(ISERROR(D36),0,D36) =F35+E35*(B36-B35) =B36-F36
=A36+1 =VLOOKUP($A37,$A$7:$E$23,4,FALSE) =VLOOKUP($B37,$D$7:$E$13,2,TRUE) =VLOOKUP($B37,$D$14:$E$23,2,TRUE) =IF(ISERROR(C37),0,C37)+IF(ISERROR(D37),0,D37) =F36+E36*(B37-B36) =B37-F37
=A37+1 =VLOOKUP($A38,$A$7:$E$23,4,FALSE) =VLOOKUP($B38,$D$7:$E$13,2,TRUE) =VLOOKUP($B38,$D$14:$E$23,2,TRUE) =IF(ISERROR(C38),0,C38)+IF(ISERROR(D38),0,D38) =F37+E37*(B38-B37) =B38-F38
=A38+1 =VLOOKUP($A39,$A$7:$E$23,4,FALSE) =VLOOKUP($B39,$D$7:$E$13,2,TRUE) =VLOOKUP($B39,$D$14:$E$23,2,TRUE) =IF(ISERROR(C39),0,C39)+IF(ISERROR(D39),0,D39) =F38+E38*(B39-B38) =B39-F39
=A39+1 =VLOOKUP($A40,$A$7:$E$23,4,FALSE) =VLOOKUP($B40,$D$7:$E$13,2,TRUE) =VLOOKUP($B40,$D$14:$E$23,2,TRUE) =IF(ISERROR(C40),0,C40)+IF(ISERROR(D40),0,D40) =F39+E39*(B40-B39) =B40-F40
=A40+1 =VLOOKUP($A41,$A$7:$E$23,4,FALSE) =VLOOKUP($B41,$D$7:$E$13,2,TRUE) =VLOOKUP($B41,$D$14:$E$23,2,TRUE) =IF(ISERROR(C41),0,C41)+IF(ISERROR(D41),0,D41) =F40+E40*(B41-B40) =B41-F41
Withdrawal less taxes 100000
Index in After Tax =MATCH(B42,G25:G41,1)
Withdrawal pre tax =INDEX(B25:B41,B43,1)+(B42-INDEX(G25:G41,B43,1))/(1-INDEX(E25:E41,B43,1))
Periods per year 12
Real return every year =IF(ISNUMBER(C46),C46,RATE(C47*B45,B44/B45,-C48,C49,0)*B45) 0.02
Years =IF(ISNUMBER(C47),C47,NPER(C46/B45,B44/B45,-C48,C49,0)/B45)
Starting portfolio value =IF(ISNUMBER(C48),C48,-PV(C46/B45,B47*B45,B44/B45,C49,0)) 2800000
Ending portfolio value =IF(ISNUMBER(C49),C49,FV(C46/B45,C47*B45,B44/B45,-C48,0)) 0
```

To modify for another state, replace the standard deduction in cell C4 and the tax brackets in cells B14:C23 and E14:E23. To use a single rate (as

does) there is no need to change the bracket amounts. It's only necessary to replace

rate in cells E14:E23 with the single rate.