. Hopefully this works for people in any tax bracket now (although I haven't tested exhaustively). However this will only work for 2021 (as I didn't add/update the tax brackets for prior years).
While not perfect, I also attempted to hack in a quick adjustment for tax-deferred savings. In particular, there is a new cell B27 which holds the "max tax-deferred savings" amount, the default calculation is $19,500 * 2 if "J"oint or 1 (single). Feel free to adjust if you have more/less tax-deferred space.
I then adjust the "taxable income" by the lesser of this amount or 15% of salary (to
to guestimate the right tax-brackets after adjusting for savings). As noted - this isn't perfect... But should be
than the original (which was more likely to show incorrect taxes - as it assumed
were tax-deferred and thus reduced taxes (which may not be true for everyone).
Offered as a revision (again copy/paste [special as text]) into cell A1 in a blank sheet and format as #Cruncher noted above, that should be it:
Code: Select all
Tax lookup key S-2017 S-2018 S-2029 S-2020 S-2020 S-2021 J-2017 J-2018 J-2019 J-2020 J-2021
Bracket 1 rate 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% 10%
Bracket 2 rate 15% 12% 12% 12% 12% 12% 15% 12% 12% 12% 12%
Bracket 3 rate 25% 22% 22% 22% 22% 22% 25% 22% 22% 22% 22%
Bracket 4 rate 24% 24%
Bracket 5 rate 32% 32%
Bracket 6 rate 35% 35%
Bracket 7 rate 37% 37%
Standard deduction base 10,400 $12,000 $12,200 $12,400 $12,400 $12,550 $20,800 $24,000 $24,400 $24,800 $25,100
Extra deduction age 65+ $1,550 $1,600 $1,650 $1,650 $1,650 $1,650 $2,500 $2,600 $2,600 $2,600 $2,600
Bracket 2 floor $9,325 $9,525 $9,700 $9,875 $9,875 $9,950 $18,650 $19,050 $19,400 $19,750 $19,900
Bracket 3 floor $37,950 $38,700 $39,475 $40,125 $40,125 $40,525 $75,900 $77,400 $78,950 $80,250 $81,050
Bracket 4 floor $91,900 $82,500 $84,200 $85,525 $85,525 $86,375 $153,100 $165,000 $168,400 $171,050 $172,750
Bracket 5 floor $164,925 $329,850
Bracket 6 floor $209,425 $418,850
Bracket 7 floor $523,600 $628,300
"Tax lookup key (e.g., S-2017, J-2020)" J-2021
SS taxable (enter 85%) 85%
Consider extra age 65+ deduction TRUE
Tax lookup key column number =MATCH(B17,1:1,0)
Years 30
Withdrawal rate 4.0%
Salary $80,000
Social Security $20,000
Adjust working spending $ for retirement $0
Retirement % of adjusted working spending 100%
Max tax-deferred savings =19500*IF(LEFT(B17,1)="J",2,1)*150%
Base tax =IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(11:11,1,B20),INDEX(2:2,1,B20)*INDEX(11:11,1,B20),0)+IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(12:12,1,B20),INDEX(3:3,1,B20)*(INDEX(12:12,1,B20)-INDEX(11:11,1,B20)),0)+IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(13:13,B20),INDEX(4:4,1,B20)*(INDEX(13:13,1,B20)-INDEX(12:12,1,B20)),0)+IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(14:14,B20),INDEX(5:5,1,B20)*(INDEX(14:14,1,B20)-INDEX(13:13,1,B20)),0)+IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(15:15,B20),INDEX(6:6,1,B20)*(INDEX(15:15,1,B20)-INDEX(14:14,1,B20)),0)+IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(16:16,B20),INDEX(7:7,1,B20)*(INDEX(16:16,1,B20)-INDEX(15:15,1,B20)),0)
Plus marginal tax rate of =IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(16:16,1,B20),INDEX(8:8,1,B20),IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(15:15,1,B20),INDEX(7:7,1,B20),IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(14:14,1,B20),INDEX(6:6,1,B20),IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(13:13,1,B20),INDEX(5:5,1,B20),IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(12:12,1,B20),INDEX(4:4,1,B20),IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(11:11,1,B20),INDEX(3:3,1,B20),INDEX(2:2,1,B20)))))))
On excess over (while working) =IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(16:16,1,B20),INDEX(16:16,1,B20),IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(15:15,1,B20),INDEX(15:15,1,B20),IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(14:14,1,B20),INDEX(14:14,1,B20),IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(13:13,1,B20),INDEX(13:13,1,B20),IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(12:12,1,B20),INDEX(12:12,1,B20),IF(B23-MIN(B23*15%,B27)-INDEX(9:9,1,B20)>INDEX(11:11,1,B20),INDEX(11:11,1,B20),0))))))+INDEX(9:9,1,B20)
On excess over (while retired 65+) =B30+IF(B19,INDEX(10:10,1,B20),0)
Growth rate 6.9% 5.5%
$1 grows to =FV(B32,$B21,-1,0,0) =FV(C32,$B21,-1,0,0)
Withdrawal @ 4% =$B22*B33 =$B22*C33
Needed savings per year =($B26*($B23+$B25-$B28-$B29*($B23-$B30))-$B24+$B28+$B29*($B18*$B24-$B31))/(B34-$B29*B34+$B26*(1-$B29)) =($B26*($B23+$B25-$B28-$B29*($B23-$B30))-$B24+$B28+$B29*($B18*$B24-$B31))/(C34-$B29*C34+$B26*(1-$B29))
Salary less savings =$B23-B35 =$B23-C35
Tax =$B28+$B29*(B36+MAX(0,B35-B27)-$B30) =$B28+$B29*(C36+MAX(0,C35-B27)-$B30)
Salary less savings less tax =B36-B37 =C36-C37
=CONCAT("After ",B21," years savings grow to") =FV(B32,$B21,-B35,0,0) =FV(C32,$B21,-C35,0,0)
=CONCAT("Withdrawal @ ",TEXT(B22,"0.0%")) =$B22*B39 =$B22*C39
Plus Social Security =$B24+B40 =$B24+C40
Tax =$B28+$B29*(B40+$B18*$B24-$B31) =$B28+$B29*(C40+$B18*$B24-$B31)
Withdrawal plus Social Security less tax =B41-B42 =C41-C42
Retirement $ versus working =B43-B38 =C43-C38
Retirement percent of working =B43/B38 =C43/C38
Taxable income OK (while working) =IF(B36<$B30,"TOO LOW",IF(B36>$B30+INDEX(13:13,1,$B20)-INDEX(12:12,1,$B20),"TOO HIGH","OK")) =IF(C36<$B30,"TOO LOW",IF(C36>$B30+INDEX(13:13,1,$B20)-INDEX(12:12,1,$B20),"TOO HIGH","OK"))
Taxable income OK (while retired) =IF(B40+$B18*$B24<$B31,"TOO LOW",IF(B40+$B18*$B24>$B31+INDEX(13:13,1,$B20)-INDEX(12:12,1,$B20),"TOO HIGH","OK")) =IF(C40+$B18*$B24<$B31,"TOO LOW",IF(C40+$B18*$B24>$B31+INDEX(13:13,1,$B20)-INDEX(12:12,1,$B20),"TOO HIGH","OK"))