Taxes were somewhat of a grey area for me up until recently. My spreadsheet contains cells for $ withdrawal (based upon a withdrawal % chosen from a drop-down), $ total income (to account for pension and social security (if chosen to include those items, again from a drop-down) and federal taxes (state/local taxes are my next project). I created an Excel VBA function to calculate federal taxes based on the withdrawal (from investments) and regular income (i.e., pension/ss). [Edit begin] I then put the calculated tax in the main spreadsheet, calculate fixed expenses based on this tax amount (i.e., fixed expenses known beforehand + taxes), and any discretionary money left over (total income - fixed expenses).[Edit end] If anyone finds it helpful, I include the code below. It's not perfect, as it assumes all of the investment withdrawal are either qualified dividends and/or long-term capital gains from taxable accounts. But it brings me close. I'm sure it can be easily modified to calculate taxes on withdrawals from tax sheltered accounts. I just figured, I can probably draw from taxable accounts until RMDs, at which time DW and I will be collecting social security. The function also assumes you're filing married filing jointly, but again, that's just a matter of changing the values in the tax reference sheet. Finally, the function assumes you're taking the standard deduction as a married couple filing jointly.
The calculation of tax uses another sheet that I use as reference with tax brackets. Basically:
Income Floor (In my spreadsheet, column E)
Income Ceiling (In my spreadsheet, column F)
Bracket Amt (ceiling - floor) (In my spreadsheet, column G)
Regular income tax rate (In my spreadsheet, column H)
Short-term capital gains rate (In my spreadsheet, column I)
Long-term capital gains rate (In my spreadsheet, column J)
Here is the VBA function with comments. I hope someone finds it useful. If you find problems with it, I'd appreciate it if you let me/us know.
Code:
' Takes withdrawal amount and total income amount as params
Private Function CalcTaxes(cWithdrawal As Currency, cTotalIncome As Currency) As Currency
Dim cRegularIncome As Currency
Dim cInvestmentIncome As Currency
Dim taxBracketCell As Range
Dim cBracketAmtTot As Currency
Dim cTaxAmt As Currency
Dim nTaxBracketRow As Integer
Dim cStandardDeduction As Currency
' Add fixed property tax, which is contained in a reference sheet
cTaxAmt = Sheet7.Range("PROPERTY_TAX").Value
' Get regular income and apply standard deduction contained in a reference sheet
cRegularIncome = cTotalIncome - cWithdrawal
cStandardDeduction = MaxVal(Sheet7.Range("STANDARD_DEDUCTION") - cRegularIncome, 0)
cRegularIncome = MaxVal(cRegularIncome - Sheet7.Range("STANDARD_DEDUCTION"), 0)
' Calculate tax on regular income
cBracketAmtTot = 0
nTaxBracketRow = 2
' Iterate through the tax bracket sheet. Column G corresponds to Bracket Amt
For Each taxBracketCell In Sheet6.Range("G3:G11")
If cRegularIncome < cBracketAmtTot Then Exit For
nTaxBracketRow = taxBracketCell.Cells.Row
cTaxAmt = cTaxAmt + (MinVal(cRegularIncome - cBracketAmtTot, taxBracketCell.Value) * Sheet6.Range("H" & nTaxBracketRow))
cBracketAmtTot = cBracketAmtTot + taxBracketCell.Value
Next taxBracketCell
' Apply any remaining standard deduction to investment income
cInvestmentIncome = cWithdrawal - cStandardDeduction
' Calculate tax on investment income
If cRegularIncome < cBracketAmtTot Then
cTaxAmt = cTaxAmt + (MinVal(cBracketAmtTot - cRegularIncome, cInvestmentIncome) * Sheet6.Range("J" & nTaxBracketRow))
End If
nTaxBracketRow = nTaxBracketRow + 1
' Iterate through the tax bracket sheet. Column G corresponds to Bracket Amt
For Each taxBracketCell In Sheet6.Range("G" & nTaxBracketRow & ":G11")
If cRegularIncome + cInvestmentIncome < cBracketAmtTot Then Exit For
cTaxAmt = cTaxAmt + (MinVal(cRegularIncome + cInvestmentIncome - cBracketAmtTot, taxBracketCell.Value) * Sheet6.Range("J" & taxBracketCell.Cells.Row))
cBracketAmtTot = cBracketAmtTot + taxBracketCell.Value
Next taxBracketCell
' Return calculated tax
CalcTaxes = cTaxAmt
End Function