Proposed Traditional vs. Roth automated tool (in Excel/VB) [updated for 2024]

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Proposed Traditional vs. Roth automated tool (in Excel/VB) [updated for 2024]

Post by fyre4ce »

"Traditional or Roth" questions are among the most common here on BH. To my knowledge, we have have the most comprehensive resource on the internet in our wiki for trying to answer this question - if anyone knows of a better one, please share! But despite this, questions and confusion are still common. To my mind, there are two reasons why. The first is that any quantitative analysis requires prediction of future tax rates - it depends on future tax laws, future pre-tax contributions, and investment growth, all of which are challenging to predict. There are also many "special cases" that complicate the analysis and give wiki readers lots of material to wade through, most of which won't be applicable to their particular situation. So, it's understandable that the problem can seem overwhelming.

As I hinted in the latest wiki update, my idea for a while has been to create a dedicated software tool that will automate this analysis, and can give recommendations based on a modest set of user inputs. I finally had time to complete a draft version of this tool, which is linked below. I'd rather not give much explanation on how to use it here, because I included a User Guide within the tool that should stand on its own. The "Straddling Brackets" example from the wiki page is pre-loaded.

FutureValue v0.96: https://drive.google.com/file/d/1qROMPj ... sp=sharing
Edit 2/8/2023: added an updated version

Anyone willing to take a look is welcome to provide feature requests, bug reports, etc. Any feedback would be appreciated! I ran every case I could think of through it for testing, including all the examples within the wiki page, and the results seemed to make sense, but there could be cases I haven't found that it doesn't handle well. Also, I'm not a programmer/software engineer by training, so any critiques/suggested improvements for my code would also be welcome. Thanks in advance!
Last edited by fyre4ce on Thu Feb 08, 2024 10:27 pm, edited 14 times in total.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by LadyGeek »

fyre4ce - You posted a duplicate thread in the personal investments forum, which I've removed. There were no replies. Let's keep this to a single thread.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

*bump*
User avatar
Lee_WSP
Posts: 10346
Joined: Fri Apr 19, 2019 5:15 pm
Location: Arizona

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by Lee_WSP »

I haven’t had a chance to really mess around with your spreadsheet yet, but re-reading the example; it can be extremely shortened and simplified.

For people straddling brackets, the rule is simple: contribute to trad up to (or down to) the lower bracket and Roth the rest or continue with trad, it won't make a huge difference.
Last edited by Lee_WSP on Fri Jul 09, 2021 3:33 pm, edited 1 time in total.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

Lee_WSP wrote: Fri Jul 09, 2021 12:34 pm I haven’t had a chance to really mess around with your spreadsheet yet, but re-reading the example; it can be extremely shortened and simplified.

For people straddling brackets, the rule is simple: contribute to trad up to (or down to) the lower bracket and Roth the rest.
That's usually the case, yes, but without knowing what the marginal rate at withdrawal looks like, you won't know whether to switch to Roth at the boundary, or continue contributing traditional (eg. going from 24% to 22% when you expect 12% withdrawals). This tool automates that calculation and shows you what the optimal split is.
User avatar
Lee_WSP
Posts: 10346
Joined: Fri Apr 19, 2019 5:15 pm
Location: Arizona

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by Lee_WSP »

fyre4ce wrote: Fri Jul 09, 2021 3:31 pm
Lee_WSP wrote: Fri Jul 09, 2021 12:34 pm I haven’t had a chance to really mess around with your spreadsheet yet, but re-reading the example; it can be extremely shortened and simplified.

For people straddling brackets, the rule is simple: contribute to trad up to (or down to) the lower bracket and Roth the rest.
That's usually the case, yes, but without knowing what the marginal rate at withdrawal looks like, you won't know whether to switch to Roth at the boundary, or continue contributing traditional (eg. going from 24% to 22% when you expect 12% withdrawals). This tool automates that calculation and shows you what the optimal split is.
IIRC,
The test case in the wiki says their withdrawal rate was 24%, but if you don't know or won't estimate, then it doesn't really matter whether you go Roth or not; you obviously don't care enough to make an estimate in the first place.

I'll see about looking at your tool for a few minutes.
User avatar
Lee_WSP
Posts: 10346
Joined: Fri Apr 19, 2019 5:15 pm
Location: Arizona

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by Lee_WSP »

Comments

Your user guide tab does not line up correctly in my 2007 version of excel. A few of the bottom cells did not merge.

I'd like to see more information on what exactly the future value tab is. It sounds like all it does is calculate the future value of a set of contributions? I suppose if so, it is self explanatory, however, what is the purpose of the tab in that case? To estimate RMD's?

And I just can't make heads or tails of your graph in the Trad v Roth tab.

This is my second look at your tool.

Simply put, I find it rather complicated and the outputs are not intuitive.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

Lee_WSP wrote: Fri Jul 09, 2021 3:41 pm Your user guide tab does not line up correctly in my 2007 version of excel. A few of the bottom cells did not merge.
Other users have reported formatting problems with other versions of Excel too. It looks fine on both my machines (both Excel 2019). I'll look into it more; maybe switching to a typewriter font or something would help.
Lee_WSP wrote: Fri Jul 09, 2021 3:41 pm I'd like to see more information on what exactly the future value tab is. It sounds like all it does is calculate the future value of a set of contributions? I suppose if so, it is self explanatory, however, what is the purpose of the tab in that case? To estimate RMD's?
You're close to correct here. It just reports future values for five contribution types, along with the two special cases below (which are a composite of the five basic types). But the "meat" of this tab is the estimation of future tax rates, which is a necessary piece of the overall analysis. A simplistic T vs R comparison can be done by looking at the pre-tax and Roth values (bigger = better), but a more detailed analysis that looks at all possible splits is on the Trad vs. Roth tab.
Lee_WSP wrote: Fri Jul 09, 2021 3:41 pm And I just can't make heads or tails of your graph in the Trad v Roth tab.
The black line is future value, so the best combination of traditional and Roth is the highest point on this curve. The red and blue lines are marginal rates now and in the future, so you can see how they vary over your possible contribution range.

Any suggestions for making it more useful?
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by LadyGeek »

fyre4ce wrote: Fri Jul 09, 2021 4:10 pm Other users have reported formatting problems with other versions of Excel too. It looks fine on both my machines (both Excel 2019). I'll look into it more; maybe switching to a typewriter font or something would help.
Arial should be the appropriate font. "Typewriter" fonts, e.g. Courier, are not proportionally spaced and look much worse.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

LadyGeek wrote: Fri Jul 09, 2021 4:17 pm
fyre4ce wrote: Fri Jul 09, 2021 4:10 pm Other users have reported formatting problems with other versions of Excel too. It looks fine on both my machines (both Excel 2019). I'll look into it more; maybe switching to a typewriter font or something would help.
Arial should be the appropriate font. "Typewriter" fonts, e.g. Courier, are not proportionally spaced and look much worse.
They definitely look worse, but my thought was that it might be more "deterministic" between platforms. The font is Calibri now; I'll switch to Arial in the next revision and see if it helps.
User avatar
Lee_WSP
Posts: 10346
Joined: Fri Apr 19, 2019 5:15 pm
Location: Arizona

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by Lee_WSP »

fyre4ce wrote: Fri Jul 09, 2021 4:10 pm The black line is future value, so the best combination of traditional and Roth is the highest point on this curve. The red and blue lines are marginal rates now and in the future, so you can see how they vary over your possible contribution range.

Any suggestions for making it more useful?
Make it more intuitive. I think your target audience are those who don't know how to or don't want to put the effort into figuring out their future RMD's and tax rates. As such, I think making it so that a ten year old can understand and use the tool would be helpful.

I'll take a further look at the graph itself and see if there's any simple changes to make it more intuitive.

edit:
It doesn't look like it works well in google sheets.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

Bumping this up for more comments.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

Bump again. Hoping more of the “usual suspects” on retirement account questions might weigh in too! :happy
McQ
Posts: 1414
Joined: Fri Jun 18, 2021 12:21 am
Location: California

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by McQ »

Here is a possibility for the tool (apologies if already mentioned in the other wiki thread, which i did not quite complete, except I got far enough to decide that you have the patience of Job ...)

Every Roth converter wants to know what their future tax rate will be, so that they can decide how much / how far up in the bracket structure to convert today. But nobody other than a tax geek close to retirement has any confidence in their projections, nor even any idea how to mock up the problem.

Your tool can help. Future tax brackets are easily predictable: you simply estimate inflation and increment the bracket boundary. How do you estimate future inflation? You take the trailing inflation rate over the past n decades from the BLS site (2.5% over the last 30 years, BTW). Or, you allow the user to enter a value (go ahead, let them enter 9% compounded).

The page can be titled something like, What will it take to be in X tax bracket in Y years? First branch is "current structure" second is "reversion in 2026 to tax structure before the tax cuts enacted in 2017" (if you want) User chooses a future year, either accepts historical inflation or enters their own rate from a dropdown.

Tool returns tax bracket floors projected for the date. These should be AGI floors: about $200,000 for the post-65 to get into the 24% bracket here in 2021, not $172,000 and change. (My examples here are all MFJ over 65).

Why I think this would be worthwhile: if a user enters a date 30 years in the future, and accepts historical inflation, they will learn that the floor of the 24% bracket will then be over $400,000. The floor of the 22% bracket will be about $240,000.

I submit that although perhaps 90% of ordinary people can't predict their future tax bracket when given a blank page, a large fraction of the BH population will be able to make an intuitive judgment, "am I going to have over $400,000 a year of income when I retire in 2051?" Which then answers the question about tax bracket.

The tool can be made even more useful if the user is given a followup option. "In which bracket will you fall?" Tool instructs user to enter social security payments (refer them to Quick Calculator at ssa.gov, be sure to click future dollars) and any pension expected. And next, to enter projected balance in any traditional retirement account (tool will divide by 27.3, the first year RMD).

Typical user will underestimate their social security estimates, and toss in a one million or two million dollar TDA. Tool will say, "with $80,000 SS, taxable at .85, and $2 million in your TDA, you will fall into the 12% bracket in 2051, as your AGI of ~$140,000 is well under the threshold of the projected 22% bracket."

This will cause consternation; learning may result.

I guarantee you, based on decades of reading financial journalism, nobody has a clue that the ceiling of the 12% bracket, in AGI terms, is going to be:
1. north of $135,000 in ten years;
2. north of $175,000 in twenty years;
3. north of $240,000 in thirty years.
... and don't get me started on what it will take to enter the 32%/33% bracket in thirty years, or how many millions will have to be in the TDA.

Best wishes.
You can take the academic out of the classroom by retirement, but you can't ever take the classroom out of his tone, style, and manner of approach.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

McQ wrote: Tue Aug 03, 2021 12:16 am Here is a possibility for the tool (apologies if already mentioned in the other wiki thread, which i did not quite complete, except I got far enough to decide that you have the patience of Job ...)

Every Roth converter wants to know what their future tax rate will be, so that they can decide how much / how far up in the bracket structure to convert today. But nobody other than a tax geek close to retirement has any confidence in their projections, nor even any idea how to mock up the problem.

Your tool can help. Future tax brackets are easily predictable: you simply estimate inflation and increment the bracket boundary. How do you estimate future inflation? You take the trailing inflation rate over the past n decades from the BLS site (2.5% over the last 30 years, BTW). Or, you allow the user to enter a value (go ahead, let them enter 9% compounded).

The page can be titled something like, What will it take to be in X tax bracket in Y years? First branch is "current structure" second is "reversion in 2026 to tax structure before the tax cuts enacted in 2017" (if you want) User chooses a future year, either accepts historical inflation or enters their own rate from a dropdown.

Tool returns tax bracket floors projected for the date. These should be AGI floors: about $200,000 for the post-65 to get into the 24% bracket here in 2021, not $172,000 and change. (My examples here are all MFJ over 65).

Why I think this would be worthwhile: if a user enters a date 30 years in the future, and accepts historical inflation, they will learn that the floor of the 24% bracket will then be over $400,000. The floor of the 22% bracket will be about $240,000.

I submit that although perhaps 90% of ordinary people can't predict their future tax bracket when given a blank page, a large fraction of the BH population will be able to make an intuitive judgment, "am I going to have over $400,000 a year of income when I retire in 2051?" Which then answers the question about tax bracket.

The tool can be made even more useful if the user is given a followup option. "In which bracket will you fall?" Tool instructs user to enter social security payments (refer them to Quick Calculator at ssa.gov, be sure to click future dollars) and any pension expected. And next, to enter projected balance in any traditional retirement account (tool will divide by 27.3, the first year RMD).

Typical user will underestimate their social security estimates, and toss in a one million or two million dollar TDA. Tool will say, "with $80,000 SS, taxable at .85, and $2 million in your TDA, you will fall into the 12% bracket in 2051, as your AGI of ~$140,000 is well under the threshold of the projected 22% bracket."

This will cause consternation; learning may result.

I guarantee you, based on decades of reading financial journalism, nobody has a clue that the ceiling of the 12% bracket, in AGI terms, is going to be:
1. north of $135,000 in ten years;
2. north of $175,000 in twenty years;
3. north of $240,000 in thirty years.
... and don't get me started on what it will take to enter the 32%/33% bracket in thirty years, or how many millions will have to be in the TDA.

Best wishes.
Thanks very much for the feedback, and the compliment on patience :-)

There's clearly a choice between performing the calculation in nominal or real dollars. I prefer real, for a couple reasons. First, it keeps the numbers in terms that users can more easily relate to, namely their current sense of purchasing power. I think most people would intuitively struggle to grasp what a $240,000/year income will actually buy in 30 years. Second, at least for stocks, I'd expect predictions of their future real return to be more accurate than their future nominal return. (Bonds have more complexity that I won't go into here.) Whether future stock returns at 8% with inflation of 3%, or 10% with inflation of 5%, or 6% with an inflation of 1% is harder to predict than saying real returns will be about 5%. I understand there's still a lot of uncertainty with that number, but it's certainly less than the uncertainty of that number plus uncertainty with respect to inflation.

The future values calculated on the Future Value sheet are nominal, not real, however.

I think the tool does provide future tax brackets, as you suggest. Cells D41 and D44 are the predicted future marginal tax rates for ordinary income and long-term capital gains/qualified dividends respectively. More detail is provided on the Trad vs. Roth sheet, where the blue line shows the predicted future marginal tax rate over the range of possible pre-tax contributions. The fact that one's choice of pre-tax or Roth contributions today can affect one's tax rate in the future is something many people miss.
User avatar
David Jay
Posts: 14569
Joined: Mon Mar 30, 2015 5:54 am
Location: Michigan

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by David Jay »

McQ wrote: Tue Aug 03, 2021 12:16 amI guarantee you, based on decades of reading financial journalism, nobody has a clue that the ceiling of the 12% bracket, in AGI terms, is going to be:
1. north of $135,000 in ten years;
2. north of $175,000 in twenty years;
3. north of $240,000 in thirty years.
Based on current law, the 12% bracket will not exist at all after 2026.
It's not an engineering problem - Hersh Shefrin | To get the "risk premium", you really do have to take the risk - nisiprius
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

David Jay wrote: Tue Aug 03, 2021 2:51 pm
McQ wrote: Tue Aug 03, 2021 12:16 amI guarantee you, based on decades of reading financial journalism, nobody has a clue that the ceiling of the 12% bracket, in AGI terms, is going to be:
1. north of $135,000 in ten years;
2. north of $175,000 in twenty years;
3. north of $240,000 in thirty years.
Based on current law, the 12% bracket will not exist at all after 2026.
Is this a feature request for an option to revert to pre-TCJA brackets for future withdrawals?
User avatar
David Jay
Posts: 14569
Joined: Mon Mar 30, 2015 5:54 am
Location: Michigan

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by David Jay »

fyre4ce wrote: Tue Aug 03, 2021 3:04 pm
David Jay wrote: Tue Aug 03, 2021 2:51 pm
McQ wrote: Tue Aug 03, 2021 12:16 amI guarantee you, based on decades of reading financial journalism, nobody has a clue that the ceiling of the 12% bracket, in AGI terms, is going to be:
1. north of $135,000 in ten years;
2. north of $175,000 in twenty years;
3. north of $240,000 in thirty years.
Based on current law, the 12% bracket will not exist at all after 2026.
Is this a feature request for an option to revert to pre-TCJA brackets for future withdrawals?
No, it was a pedantic response to someone predicting a tax bracket out 10-20-30 years when the bracket is scheduled to “go away” long before even 10 years.
It's not an engineering problem - Hersh Shefrin | To get the "risk premium", you really do have to take the risk - nisiprius
McQ
Posts: 1414
Joined: Fri Jun 18, 2021 12:21 am
Location: California

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by McQ »

David Jay wrote: Tue Aug 03, 2021 3:08 pm
fyre4ce wrote: Tue Aug 03, 2021 3:04 pm
David Jay wrote: Tue Aug 03, 2021 2:51 pm
McQ wrote: Tue Aug 03, 2021 12:16 amI guarantee you, based on decades of reading financial journalism, nobody has a clue that the ceiling of the 12% bracket, in AGI terms, is going to be:
1. north of $135,000 in ten years;
2. north of $175,000 in twenty years;
3. north of $240,000 in thirty years.
Based on current law, the 12% bracket will not exist at all after 2026.
Is this a feature request for an option to revert to pre-TCJA brackets for future withdrawals?
No, it was a pedantic response to someone predicting a tax bracket out 10-20-30 years when the bracket is scheduled to “go away” long before even 10 years.
Checking my understanding of TCJA here: of course the 12% *rate* goes away in 2026--but does the bracket itself also go away? If not, might it still be useful to project out its ceiling? That ceiling marks the jump from 12 to 22 (or 15 to 25), a sufficiently large gap to drive a fair amount of tax planning to avoid.
PS: acknowledgement, TCJA tinkered with the ceiling of the 12/15% bracket (but not its floor or its rough span)
You can take the academic out of the classroom by retirement, but you can't ever take the classroom out of his tone, style, and manner of approach.
User avatar
Lee_WSP
Posts: 10346
Joined: Fri Apr 19, 2019 5:15 pm
Location: Arizona

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by Lee_WSP »

Rates will revert back to 2017 rates adjusted for inflation.

As an example see below.
12% tax rate goes back up to 15%
22% tax rate goes back up to 25%
24% tax rate goes back up to 28%
https://fedtaxplanners.com/how-2026-sun ... -tax-cuts/
McQ
Posts: 1414
Joined: Fri Jun 18, 2021 12:21 am
Location: California

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by McQ »

fyre4ce wrote: Tue Aug 03, 2021 3:04 pm
David Jay wrote: Tue Aug 03, 2021 2:51 pm
McQ wrote: Tue Aug 03, 2021 12:16 amI guarantee you, based on decades of reading financial journalism, nobody has a clue that the ceiling of the 12% bracket, in AGI terms, is going to be:
1. north of $135,000 in ten years;
2. north of $175,000 in twenty years;
3. north of $240,000 in thirty years.
Based on current law, the 12% bracket will not exist at all after 2026.
Is this a feature request for an option to revert to pre-TCJA brackets for future withdrawals?
[fyre4ce, Following up also on your longer reply to me]:
I defer to your decision about what is already there / needs to be there in the tool. But I think you’ve identified the nub of the question: nominal dollars versus real dollars. It would be interesting to me to learn whether others side with you, hence this followup.

Although it is true that a user can better gauge spending power when real dollars are used, especially in the distant future, in fact, in my observation, no one other than an academic or a trained professional ever succeeds in consistently translating everything—living expenses, tax brackets, asset returns—into real terms.

Any life insurance illustration will use nominal dollars

All mortgage payments use nominal dollars

Most historical stock charts will use nominal dollars--certainly anything seen in the day to day financial press; likewise, the historical return on US stocks is far more likely to be stated as 10% rather than 7% (real).

And journalist accounts (on Roth conversions, for instance) are wont to project the same or higher tax brackets for a person in the future, without any clue as to what it will take to be in, say, the 32% bracket 12 years hence, or how many nominal dollars in a tax-deferred account will be required for an RMD rate near 4% to create enough taxable income to throw a retiree into that 32% bracket.

My two cents. But then, when I use Quick Calculator on the SSA.gov site, I always toggle to “future inflated dollars.” Probably too much complexity for your tool to add such a toggle, but worth a mention.

And if you can program that sort of toggle, then you could indeed add a toggle for "assume TCJA continues unchanged"/ "revert to the pre-TCJA rates and brackets." I somewhat disagree with David Jay; it is not pedantic to at least surface the suggestion of having a pre-TCJA rate & bracket calculator. Elsewhere on BH, particularly in Roth conversion threads, I do see people struggling with / debating over whether TCJA will indeed revert, which determine which rates the future projections have to use, per Lee_WSJ. The statute says it sunsets; thinking about what voters will tolerate argues most of it will survive.

Only five more years to learn which it will be :)
You can take the academic out of the classroom by retirement, but you can't ever take the classroom out of his tone, style, and manner of approach.
User avatar
Lee_WSP
Posts: 10346
Joined: Fri Apr 19, 2019 5:15 pm
Location: Arizona

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by Lee_WSP »

I'm a fan of simplicity. My model is just a RMD calculator with an interest rate and beginning amount input. I usually model it with a ROR of 2-3 to account for real growth and compare the RMD to current brackets. Extremely rudimentary, but it's about as precise as I think is reasonable to expect from prognostication.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

Update: I finally had some time to work on this again, adding some features and fixing some bugs. The most notable features are an option to switch on 2026+ sunset tax rates, and also toggle inflation adjustment on some tax numbers that are not currently inflation-adjusted (NIIT threshold, SS phase-in thresholds). The latest file is here:

https://drive.google.com/file/d/12y56S2 ... sp=sharing

I share the affinity for simplicity, and I think having a separate "advanced options" menu for power users, which more novice users can easily ignore, is the right way to balance simplicity and capability.

Still happy to accept feedback!
Last edited by fyre4ce on Wed Sep 22, 2021 2:19 am, edited 1 time in total.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

McQ wrote: Tue Aug 03, 2021 3:40 pm Checking my understanding of TCJA here: of course the 12% *rate* goes away in 2026--but does the bracket itself also go away? If not, might it still be useful to project out its ceiling? That ceiling marks the jump from 12 to 22 (or 15 to 25), a sufficiently large gap to drive a fair amount of tax planning to avoid.
PS: acknowledgement, TCJA tinkered with the ceiling of the 12/15% bracket (but not its floor or its rough span)
I looked at this in detail, and it looks to me like the thresholds for the top of the 10% bracket and the 12/15% bracket remained unchanged, except for inflation adjustment, with the TCJA. The TCJA just reduced the rate on the former 15% bracket down to 12%. But the higher brackets all got reconfigured. That's why the bracket thresholds in 2018 for 10% and 12% are not even multiples of $1,000, but the thresholds for higher brackets are. So for the post-sunset rates, I assumed the 12% reverts to 15% but the thresholds for 10% and 15% remain the same (except for a small inflation bump), but I had to do some reverse engineering to figure out what higher brackets, by going back to the 2017 brackets and adding estimated inflation bumps for all the years in between.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

Bumping this back up for more comments.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by LadyGeek »

The User Guide is a bit difficult to understand.

You need to say in a friendly way what, exactly, this spreadsheet is for. Why do I want to calculate the future value of these accounts? You have the answer at the end A2. It should be at the top.

A5 - The first word "future" should be capitalized.

Walk the user through an end-to-end example. (Insert after A6.) Start by entering your data here... click this... look at that... make changes here... see how it affects your investment decision.

Then, it becomes clear what you use the other sheets for.

Revision History - Version 0.8 does not have a date.

(To be clear, I'm retired and won't be using the tool. However, these are my suggestions to make it easier to use and understand.)
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

LadyGeek wrote: Thu Sep 23, 2021 6:51 pm The User Guide is a bit difficult to understand.

You need to say in a friendly way what, exactly, this spreadsheet is for. Why do I want to calculate the future value of these accounts? You have the answer at the end A2. It should be at the top.

A5 - The first word "future" should be capitalized.

Walk the user through an end-to-end example. (Insert after A6.) Start by entering your data here... click this... look at that... make changes here... see how it affects your investment decision.

Then, it becomes clear what you use the other sheets for.

Revision History - Version 0.8 does not have a date.

(To be clear, I'm retired and won't be using the tool. However, these are my suggestions to make it easier to use and understand.)
Thanks, all great suggestions. I'll fold them into the next revision.

Any feedback on the use of the tool itself? I'm interested to know whether the layout is intuitive for users, or not so much. I understand you're retired and this is mostly for users in the accumulation phase, but you could still use fictitious numbers.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

LadyGeek wrote: Thu Sep 23, 2021 6:51 pm The User Guide is a bit difficult to understand.

You need to say in a friendly way what, exactly, this spreadsheet is for. Why do I want to calculate the future value of these accounts? You have the answer at the end A2. It should be at the top.

A5 - The first word "future" should be capitalized.

Walk the user through an end-to-end example. (Insert after A6.) Start by entering your data here... click this... look at that... make changes here... see how it affects your investment decision.

Then, it becomes clear what you use the other sheets for.

Revision History - Version 0.8 does not have a date.

(To be clear, I'm retired and won't be using the tool. However, these are my suggestions to make it easier to use and understand.)
I uploaded a new version with two worked examples, a simple and a more complex, both taken from the BH wiki page. I made the other changes you suggested, except for the capitalization of "future" - I couldn't find the word you were talking about. Happy to take more feedback from anyone.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

Bumping this up for more inputs.
retire2022
Posts: 3285
Joined: Tue Oct 02, 2018 6:10 pm
Location: NYC

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by retire2022 »

fyre

In the future tab, there isn't an entry input for Roth contributions

All I see is pretax, but no space for Roth contributions
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

retire2022 wrote: Sun Jan 09, 2022 2:55 pm fyre

In the future tab, there isn't an entry input for Roth contributions

All I see is pretax, but no space for Roth contributions
That was intentional. The reason is that Roth contributions are forever tax-free so have no impact on one's tax situation beyond the current tax year. The future Roth balance is not needed to estimate future tax rates.

In an earlier version I included a Future Value calculation for Roth too, with the goal of estimating total future retirement income. The problem is that there are other factors that come into play, like taxable investments, and the need to calculate a total future tax bill rather than just a marginal rate, that made the tool even more complicated. I decided to revert back to just a simple future marginal rate estimator.
User avatar
Eagle33
Posts: 2383
Joined: Wed Aug 30, 2017 3:20 pm

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by Eagle33 »

fyre4ce wrote: Sun Jan 09, 2022 4:42 pm
retire2022 wrote: Sun Jan 09, 2022 2:55 pm fyre

In the future tab, there isn't an entry input for Roth contributions

All I see is pretax, but no space for Roth contributions
That was intentional. The reason is that Roth contributions are forever tax-free so have no impact on one's tax situation beyond the current tax year. The future Roth balance is not needed to estimate future tax rates.

In an earlier version I included a Future Value calculation for Roth too, with the goal of estimating total future retirement income. The problem is that there are other factors that come into play, like taxable investments, and the need to calculate a total future tax bill rather than just a marginal rate, that made the tool even more complicated. I decided to revert back to just a simple future marginal rate estimator.
You may want to modify the Purpose in the Use Guide if Roth calculation is no longer included.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

Eagle33 wrote: Mon Jan 10, 2022 1:40 pm
fyre4ce wrote: Sun Jan 09, 2022 4:42 pm
retire2022 wrote: Sun Jan 09, 2022 2:55 pm fyre

In the future tab, there isn't an entry input for Roth contributions

All I see is pretax, but no space for Roth contributions
That was intentional. The reason is that Roth contributions are forever tax-free so have no impact on one's tax situation beyond the current tax year. The future Roth balance is not needed to estimate future tax rates.

In an earlier version I included a Future Value calculation for Roth too, with the goal of estimating total future retirement income. The problem is that there are other factors that come into play, like taxable investments, and the need to calculate a total future tax bill rather than just a marginal rate, that made the tool even more complicated. I decided to revert back to just a simple future marginal rate estimator.
You may want to modify the Purpose in the Use Guide if Roth calculation is no longer included.
There's a distinction to be made here. The tool calculates the future value of a single Roth contribution made during the current tax year. This can be compared against the equivalent single pre-tax contribution value (after taxes) to decide which one is better. That's the question most folks are interested in answering and why I created the tool.

The hardest part of that calculation is estimating the future marginal tax rate, which is necessary to calculate the future value of a pre-tax contribution. But in order to know that tax rate, the tool has to know the entire plan for future pre-tax contributions between now and withdrawal, so a total future pre-tax balance can be calculated. From this, a withdrawal rate (eg. 4%) can be assumed, a total taxable income can be calculated, and that value can be looked up in a tax rate table. That's why the tool asks for the current pre-tax balance and planned future contributions.

There is no equivalent need to calculate a total future Roth balance. Whether someone in starting today with a $20M Roth balance, or a $0 Roth balance, has no impact on their future tax rates or whether pre-tax or Roth contributions are better going forward. But if someone has a $20M pre-tax balance, that has a huge impact because withdrawals/RMDs will lock them into a high bracket later on - that will shift the balance for contributions today in favor of Roth.

That said, if users want to know what their expected future Roth balance will be, for other reasons (eg. estimating future net worth or total spendable income) I can add in a couple lines to generate a similar "future Roth balance" line. But it wouldn’t be used in any downstream calculations the way the tool is set up now. Hope that all made sense and let me know what you think.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

Bumping this up. I finally had some time to update this tool for 2022 tax law. I also added an IRMAA feature to the tool, which I think is an often-overlooked future tax cost. I still think this could be a very valuable tool for making T vs. R decisions, and would love some more feedback, both on the technical capability (features/bugs) and overall usability and utility.

Link to latest file: https://drive.google.com/file/d/1jQPu6J ... sp=sharing
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

Bumping up for more opinions.
User avatar
Eagle33
Posts: 2383
Joined: Wed Aug 30, 2017 3:20 pm

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by Eagle33 »

Why is the file preventing removal of personal information?
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

Eagle33 wrote: Thu Aug 04, 2022 3:40 pm Why is the file preventing removal of personal information?
I don’t think it’s preventing the removal of personal information. Excel has an option where it strips out meta-data that can contain personal information (user’s login name, file path on local drive, etc) and I believe you’re getting an automatic warning to not type personal information into the cells.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

Eagle33 wrote: Thu Aug 04, 2022 3:40 pm Why is the file preventing removal of personal information?
I just posted an updated version with the warning removed.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

I just posted an updated version of the tool, with multi-year capability added per request, and some other improvements. Still looking for feedback!
sc9182
Posts: 2165
Joined: Wed Aug 17, 2016 7:43 pm

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by sc9182 »

OP - thanks for update ; have briefly looked at the took late spring. will take another look soon.

Tool appears good - but we ourselves may not be prime candidates as we are ways away from retiring/Roth-conversions ; But, we could run a few hypothetical scenarios- would do - let you know ..

Again- thanks for taking time to continually improvise and enhance this program/tool - and offer us to try/use it free !
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

sc9182 wrote: Thu Sep 22, 2022 7:32 pm OP - thanks for update ; have briefly looked at the took late spring. will take another look soon.

Tool appears good - but we ourselves may not be prime candidates as we are ways away from retiring/Roth-conversions ; But, we could run a few hypothetical scenarios- would do - let you know ..

Again- thanks for taking time to continually improvise and enhance this program/tool - and offer us to try/use it free !
Actually, I'd say the tool is more targeted for early and mid career investors trying to predict their future tax situation. This tool doesn't explicitly look at Roth conversions, and there are more accurate tools available for looking at conversions for soon-to-be retirees. I'm in my 30's and decided to create it mostly to analyze my own tax situation. Then I thought it it could be useful to others so I polished it up and added some features that didn't apply to me. Thanks very much for reviewing and I'm interested to get some feedback!
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

fyre4ce wrote: Thu Sep 22, 2022 8:13 pm
sc9182 wrote: Thu Sep 22, 2022 7:32 pm OP - thanks for update ; have briefly looked at the took late spring. will take another look soon.

Tool appears good - but we ourselves may not be prime candidates as we are ways away from retiring/Roth-conversions ; But, we could run a few hypothetical scenarios- would do - let you know ..

Again- thanks for taking time to continually improvise and enhance this program/tool - and offer us to try/use it free !
Actually, I'd say the tool is more targeted for early and mid career investors trying to predict their future tax situation. This tool doesn't explicitly look at Roth conversions, and there are more accurate tools available for looking at conversions for soon-to-be retirees. I'm in my 30's and decided to create it mostly to analyze my own tax situation. Then I thought it it could be useful to others so I polished it up and added some features that didn't apply to me. Thanks very much for reviewing and I'm interested to get some feedback!
Bumping this up for more feedback, from sc9182 and others.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

Bumping this up again for feedback. When I get around to updating the T vs. R page for 2023 I plan to add a link to the tool, as I believe it will help some readers.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB)

Post by fyre4ce »

Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB) [updated for 2023]

Post by fyre4ce »

Minor updates based on some experience from a forum member: https://drive.google.com/file/d/18TGOq1 ... sp=sharing
User avatar
FiveK
Posts: 15691
Joined: Sun Mar 16, 2014 2:43 pm

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB) [updated for 2023]

Post by FiveK »

fyre4ce wrote: Sun May 14, 2023 4:59 pm
FiveK wrote: Sat May 13, 2023 8:49 pm Seems overly complex in the sense of striving for precision where accuracy is unlikely.
At risk of side-tracking this discussion, I'll ask - what complexity and precision would you like to see removed?
Replying in this thread to avoid side-tracking the other.

First, having me call a spreadsheet overly complex when I've posted things such as this spreadsheet-generated chart could fit under pots vs. kettles. ;) Don't take this as denigrating the quality of your work because that's not at all the intent.

It is, however, precisely the feedback I got from posts such as that one that informs my comments here:
  1. Some people are just spreadsheet-averse. E.g., I think one can make a good case that for most people either of the spreadsheets covered in the Tax estimation tools are better than the web-based ones, but many people just prefer the Q&A-type interface that web tools provide. For those people, the amount of data entry required here would likely be prohibitive.
  2. At the other end of the spectrum, see Optimized Roth Conversion Model Update - Bogleheads.org. One could have the same question about how accurate the results from that will be, given all the needed assumptions. Have you compared the results of the tool covered in this thread with those from the one in that thread?
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB) [updated for 2023]

Post by fyre4ce »

Added a minor revision that includes the option for the sale of appreciated investments for the "contributing the maximum" case.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB) [updated for 2023]

Post by fyre4ce »

FiveK wrote: Mon May 15, 2023 5:11 pm
fyre4ce wrote: Sun May 14, 2023 4:59 pm
FiveK wrote: Sat May 13, 2023 8:49 pm Seems overly complex in the sense of striving for precision where accuracy is unlikely.
At risk of side-tracking this discussion, I'll ask - what complexity and precision would you like to see removed?
Replying in this thread to avoid side-tracking the other.

First, having me call a spreadsheet overly complex when I've posted things such as this spreadsheet-generated chart could fit under pots vs. kettles. ;) Don't take this as denigrating the quality of your work because that's not at all the intent.

It is, however, precisely the feedback I got from posts such as that one that informs my comments here:
  1. Some people are just spreadsheet-averse. E.g., I think one can make a good case that for most people either of the spreadsheets covered in the Tax estimation tools are better than the web-based ones, but many people just prefer the Q&A-type interface that web tools provide. For those people, the amount of data entry required here would likely be prohibitive.
  2. At the other end of the spectrum, see Optimized Roth Conversion Model Update - Bogleheads.org. One could have the same question about how accurate the results from that will be, given all the needed assumptions. Have you compared the results of the tool covered in this thread with those from the one in that thread?
Thanks very much for the review. I promise, I won't be offended no matter what you say; this is a spare-time project. I wrote this tool to try to help people, and any useful feedback is welcome.

About your old post that you linked, my biggest issue with it is that the output (+/- number of years) doesn't seem intuitive to me. When I wrote my tool, I chose the inputs (traditional dollars contributed today) and outputs (total future value of investment) to be intuitive to the way I think about the problem, and how others may too.

I appreciate the feedback you got that an interview-style web tool would be best for some users, but I simply lack the capability to create one on my own. If you know someone with those skills, I'm happy to contribute the math side of the project, but until that happens, users are stuck with a spreadsheet unfortunately.

You had two basic points of feedback. The first is that the tool is too complex. A big challenge when creating the tool is knowing your potential audience and trying to help as many of them as possible. Some want a simple tool, while other have unusual situations that require more complexity. Of course, everyone wants something that's one-click simple to use, gives accurate results, and covers a wide range of cases, but that's not possible. For this reason (and, frankly, because that's how the development over time played out) is that the tool can be used in several ways, ranging from simple to complex. Simplest is to enter the green inputs and have it do a "once-thru" calculation for an assumed plan. It will report whether traditional or Roth is better. Next step up is the "Trad vs. Roth" macro, that calculates it for a range of possible contributions today; this can catch things like breakpoints in current or future rates. Most complex is the "Multi-Year" capability (if memory serves, you suggested this feature be added) can handle any combination of future contributions, withdrawals, conversions, and rates of return. Users can choose the best option for them.

Most of the "complexity" I think you're referring to is internal to the tool, not necessarily complexity that makes it hard to use; the macro is one-click. There are a few inputs that are borderline in value; future tax-free income is probably the least valuable, but users can leave it blank. The rest of the inputs - investment performance, tax rates, pre-tax balances, other future income - are necessary and I'd expect any tool to ask for them. That said, if you have any suggestions for inputs to change, formatting, reorganization, I'm happy to give them a try.

Your second point is that it's too precise given the low accuracy of the problem. This may just be a difference in philosophy. My approach is to get the math right, and then have confidence in the outputs that's proportional to confidence in the inputs. There's nothing wrong with using the correct equations inside the tool; in fact, I can't imagine doing anything else. Users just need to know that if the tool predicts a close call, there's a good chance it's wrong, and the truly correct answer can only be known for sure in hindsight. It's not a weakness of the tool, but it could be a weakness of how someone uses it.

Besides, not every situation will have high uncertainty. I used it in the forums recently on someone who was about 10 years from retirement and had mostly a bond portfolio. Those two facts alone shrunk the cone of uncertainty a lot. About the only high-uncertainty input is tax rates, and I ran the tool twice, once with current rates and once with TCJA-sunset rates (both are hard-coded and can be switched with a click). IIRC, Roth had a small advantage with one set of rates and a significant advantage with the other, so I recommended Roth. It would not have been possible to arrive at that result with a tool with much less precision. Will Roth be the right choice in hindsight? Probably but no guarantee. It's a bit like blackjack: if the math says hitting has an EV of 0.52 and standing 0.49, you should hit. Yes, you might still lose the hand, but it's still the percentage play.

I looked at the tool you mentioned, and I think it's too different to compare directly. Ironically, it's much more complex than my tool, looking at each year of retirement, RMDs, carrying balances forward, etc. whereas my tool only looks at a single year of withdrawal. My tool is more geared toward the accumulation phase; it doesn't consider conversions, just contributions. Know of anything else that is a closer match?
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB) [updated for 2023]

Post by fyre4ce »

Just posted a minor update; biggest change is adding a two-year inflation adjustment for IRMAA tier thresholds.
Topic Author
fyre4ce
Posts: 2538
Joined: Sun Aug 06, 2017 11:29 am

Re: Proposed Traditional vs. Roth automated tool (in Excel/VB) [updated for 2024]

Post by fyre4ce »

Updated for 2024.
Post Reply