So, I took a swing at adding a new worksheet to the CSS tool....
Nice work! That must have taken some effort!
If you had to pick the most important one or two or three concepts you are proposing, what would they be?
Here are what I think are the most important concepts, in decreasing order of importance:
1) Powerful and usable tool to estimate future marginal tax rates. This is an input to many types of investment problems, and in my experience most people just guess. This was the motivator behind making this proposed update in the first place. It synergizes well with the tax model in the CSS, because it can do a direct lookup of the current marginal rate for a given contribution size. But, I wrote it so that the user can manually override most of the numbers at various points through the calculation. The core of the math is using a Future Value function to estimate future taxable income:
[estimated future taxable withdrawals] = FV([expected real return],[expected years to withdrawal],-[expected future annual contributions],-[current pre-tax account balance]) * [expected future withdrawal rate]A similar calculation is repeated for taxable investments. Then, add SS income and other income, subtract the age 65+ standard deduction, and do a lookup in the tax rate table to get an estimated marginal future tax rate.
2) Once you have current and future marginal rates, I like having future values for the different tax structures all in one place. Users can compare pre-tax to Roth, pre-tax and Roth to taxable, non-deductible to taxable, etc. Having them all in one spot makes the spreadsheet more compact and takes the place of separate calculators (like "Growth in a taxable account"). Also, I've noticed not all taxable calculators get the basis correct. I believe I have the most accurate formula (derived
here). I coded it to do either annual or continuous compounding, but I'd be OK removing that feature and only offering annual, if it were an issue.
3) A traditional versus Roth calculator should definitely have a "contributing the maximum" feature, which is very easy to add once the other pieces are there. This is partly why it makes a lot of sense to include the taxable formula, because this is necessary for this math. Formulas used are
here.
4) I've never seen a formula for deciding between traditional and Roth when you're not able to get the full employer match, so I derived one
here. I think it's a nice feature that could help some users, and it makes sense to have it along with the other features of that sheet. But, it probably affects only a small number of users, so that's why I list it further down in my priority list.
5) The "Social Security Spike Checker" is actually the most complicated sub-tool on the page, and the easiest to separate if necessary. But, it could be useful to a lot of users so I included it. It runs the same calculation for an alternate scenario of trying to go underneath the tallest spike of SS taxation in retirement. The algorithm goes like this:
a) Future out the amount of other (non-SS) income in retirement necessary to get the user just barely under the 22.2% or 40.7% spike in SS taxation. Formulas for these values are derived
here.
b) Given a withdrawal rate, figure out the future pre-tax balance that will generate this amount of income.
c) Figure out a reduced pre-tax contribution that will get the user to this balance, and contribute the rest to Roth. If this change leaves extra room in the Roth account to switch some taxable investments to Roth, do it, but taxable contributions can't go below 0.
d) Using the new pre-tax, Roth, and possibly taxable contributions, and keeping the other variables the same, calculate the total tax due for this alternate scenario.
e) Calculate the total income available after taxes for this alternate scenario, and compare it to the baseline. The lower-performing scenario is indicated with a "-X.XX%" to show the relative size of the difference.
Hope that helps!