Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Please

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
User avatar
swimirvine
Posts: 368
Joined: Tue May 28, 2013 7:51 am

Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Please

Post by swimirvine » Mon Nov 10, 2014 9:29 am

I've read several posts on this site by people asking whether they should contribute to a Roth 401(k) or a 401(k).

Most recently this one: http://www.bogleheads.org/forum/viewtop ... st=2252902

which had several great responses and links to articles as well as a spreadsheet.

This is my first attempt at a spreadsheet that will tell you if you should contribute to a Roth 401(k) vs. 401(k)

It assumes you are maxing out your tax-deferred space and that additional money is going into a taxable brokerage account. If you were to choose Roth 401(k), this would simply lower the amount you are contributing to your taxable brokerage account each year. This is my first attempt at a spreadsheet like this. I have previously done spreadsheets that will balance your portfolio across multiple accounts but these are very simple.
This one requires knowledge of tax laws. My knowledge in this arena is severely limited.

Please download and critique this spreadsheet and make suggestions to make it more accurate. I'm hoping it reflects the value of doing Roth contributions when you are far away from retirement based on the expected Tax-drag in a taxable brokerage account and how this tax-drag becomes more significant over longer periods of time.

Fill in column L. The rest in calculated for you.

ZoHo Docs Version
https://docs.zoho.com/sheet/published.d ... f76f7aabfd
Google Docs Version (with the ability to download and save)
https://docs.google.com/spreadsheets/d/ ... sp=sharing


Thanks!
Last edited by swimirvine on Tue Nov 11, 2014 9:06 am, edited 4 times in total.
The way I invest my money is not the right way to invest, it's the right way for ME to invest.

User avatar
swimirvine
Posts: 368
Joined: Tue May 28, 2013 7:51 am

Re: Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Pl

Post by swimirvine » Mon Nov 10, 2014 10:29 am

I had to change the Google sheet to make it view only. Someone was editing the Columns other than M. Please download this Google sheet to explore it further and make suggestions/edits.

Thanks!
The way I invest my money is not the right way to invest, it's the right way for ME to invest.

User avatar
serbeer
Posts: 1165
Joined: Fri Dec 28, 2007 2:09 pm

Re: Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Pl

Post by serbeer » Mon Nov 10, 2014 2:59 pm

Cannot validate the spreadsheet, but another spreadsheet like this was put together by Harry of TFB blog and can be found at the end of this post:
http://thefinancebuff.com/roth-401k-for ... e-max.html

Feel free to compare it to your and to learn from this comparison.

User avatar
swimirvine
Posts: 368
Joined: Tue May 28, 2013 7:51 am

Re: Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Pl

Post by swimirvine » Mon Nov 10, 2014 3:05 pm

serbeer wrote:Cannot validate the spreadsheet, but another spreadsheet like this was put together by Harry of TFB blog and can be found at the end of this post:
http://thefinancebuff.com/roth-401k-for ... e-max.html

Feel free to compare it to your and to learn from this comparison.
I saw that post and the calculations are very helpful. I tried to break it down year by year so people would be able to figure out if they should contribute to a Roth and how many years before retirement they should consider stopping Roth contributions.
The way I invest my money is not the right way to invest, it's the right way for ME to invest.

User avatar
swimirvine
Posts: 368
Joined: Tue May 28, 2013 7:51 am

Re: Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Pl

Post by swimirvine » Tue Nov 11, 2014 7:53 am

Got some feed back from The Finance Buff and made some changes to the spreadsheet.

Here's the link again to the spreadsheet on ZoHo docs:
https://docs.zoho.com/sheet/published.d ... f76f7aabfd

Here's the link again to the spreadsheet on google docs:
https://docs.google.com/spreadsheets/d/ ... sp=sharing
The way I invest my money is not the right way to invest, it's the right way for ME to invest.

subham
Posts: 168
Joined: Wed Oct 01, 2014 1:01 pm

Re: Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Pl

Post by subham » Tue Nov 11, 2014 12:50 pm

Thanks Swimirvine. Will review and get back.

subham
Posts: 168
Joined: Wed Oct 01, 2014 1:01 pm

Re: Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Pl

Post by subham » Tue Nov 11, 2014 3:22 pm

OP: is it possible to figure out how to split between Roth vs Trad each year as opposed to making it a binary decision of contributing full to Roth or Trad?

User avatar
swimirvine
Posts: 368
Joined: Tue May 28, 2013 7:51 am

Re: Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Pl

Post by swimirvine » Tue Nov 11, 2014 3:47 pm

subham wrote:OP: is it possible to figure out how to split between Roth vs Trad each year as opposed to making it a binary decision of contributing full to Roth or Trad?
The money to be invested in Roth vs. Traditional would be equal in retirement assuming equal tax rates (traditional wins for lower retirement tax rates) (Roth wins for higher retirement tax rates)

the major question is at what difference between current vs retirement marginal tax rates would contributing to a Roth (any amount of money) leave you with more after tax dollars in retirement from your 401(k) and taxable account (given a certain tax drag of taxable investing and a certain long term capital gains tax rate). The effect of the tax-drag on a taxable brokerage account compounds over time. So Roths are more valuable when you are further away from retirement.

You question points out an obvious superfluous data point in my spread sheet. Whether you enter $1 or $1,000,000 the numbers in the final column remain the same.
Last edited by swimirvine on Thu Feb 04, 2016 8:29 am, edited 1 time in total.
The way I invest my money is not the right way to invest, it's the right way for ME to invest.

bsteiner
Posts: 3581
Joined: Sat Oct 20, 2012 9:39 pm
Location: NYC/NJ/FL

Re: Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Pl

Post by bsteiner » Tue Nov 11, 2014 9:49 pm

There are some additional benefits to the Roth:

1. All of the money in the plan or IRA doesn't have to come out all at once at retirement. Distributions can be stretched over the lifetimes of the IRA owner and spouse, and thereafter over the beneficiary's life expectancy.

2. There are no required distributions from a Roth IRA during lifetime after age 70 1/2. This is a substantial benefit.

3. There is an income tax deduction for the Federal, but not the state, estate tax. In a state with a state estate tax, the Roth gets the income tax out of the estate, so as to avoid this problem.

4. Our clients generally provide for their children in trust rather than outright, to keep the children's inheritances out of their estates for estate tax purposes, and to protect against creditors and spouses. However, trusts reach the top income tax bracket at $12,150. With a traditional IRA, if the beneficiaries are in lower income tax brackets, the trustees have to choose between distributing the amounts received from the IRA so that they'll be taxable to the beneficiaries, giving up the protection of the trust, or accumulating these amounts in the trust and paying income tax at a higher rate. The Roth avoids this tradeoff.

5. To the extent retirement plans and IRAs are protected from creditors, the Roth protects more value from creditors.

6. The Roth is a more valuable asset to allocate GST exemption to.

subham
Posts: 168
Joined: Wed Oct 01, 2014 1:01 pm

Re: Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Pl

Post by subham » Wed Nov 12, 2014 3:33 pm

bsteiner wrote:There are some additional benefits to the Roth:

1. All of the money in the plan or IRA doesn't have to come out all at once at retirement. Distributions can be stretched over the lifetimes of the IRA owner and spouse, and thereafter over the beneficiary's life expectancy.

2. There are no required distributions from a Roth IRA during lifetime after age 70 1/2. This is a substantial benefit.

3. There is an income tax deduction for the Federal, but not the state, estate tax. In a state with a state estate tax, the Roth gets the income tax out of the estate, so as to avoid this problem.

4. Our clients generally provide for their children in trust rather than outright, to keep the children's inheritances out of their estates for estate tax purposes, and to protect against creditors and spouses. However, trusts reach the top income tax bracket at $12,150. With a traditional IRA, if the beneficiaries are in lower income tax brackets, the trustees have to choose between distributing the amounts received from the IRA so that they'll be taxable to the beneficiaries, giving up the protection of the trust, or accumulating these amounts in the trust and paying income tax at a higher rate. The Roth avoids this tradeoff.

5. To the extent retirement plans and IRAs are protected from creditors, the Roth protects more value from creditors.

6. The Roth is a more valuable asset to allocate GST exemption to.
Not sure i understand #4, but these are excellent points in favor of Roth.

Financebuff would do a great favor by adding these when he is making a case for Roth for people who are maximizing the contributions and are on their way to building enough wealth that could last generations.

User avatar
tfb
Posts: 7977
Joined: Mon Feb 19, 2007 5:46 pm
Contact:

Re: Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Pl

Post by tfb » Wed Nov 12, 2014 6:30 pm

subham wrote:Financebuff would do a great favor by adding these when he is making a case for Roth for people who are maximizing the contributions and are on their way to building enough wealth that could last generations.
To be clear, I'm not against *having* or *holding* Roth accounts. I'm against *contributing to* Roth accounts in lieu of taking a deduction in peak working years. Backdoor Roth, rolling over after-tax 401k to Roth, converting to Roth in [relatively] low income years are all good ways to get money into Roth accounts. The goal is to have Roth accounts toward the end so that you can benefit from all those things above.
Harry Sit, taking a break from the forums.

User avatar
FiveK
Posts: 5826
Joined: Sun Mar 16, 2014 2:43 pm

Re: Suggestions for the Wiki

Post by FiveK » Sun Jan 24, 2016 10:27 pm

[Posts moved into here from: Suggestions for the Wiki, see below. --admin LadyGeek]

The spreadsheet referenced on the wiki page (https://www.bogleheads.org/wiki/Traditi ... an_max_out) is
A) https://docs.zoho.com/sheet/published.d ... f76f7aabfd

In the note attached to that spreadsheet link on the wiki page, there is reference to this thread (viewtopic.php?f=10&t=150516), in which one finds a link to a different spreadsheet,
B) https://docs.zoho.com/sheet/published.d ... 37d6fddfc7

Spreadsheets A and B appear to give different answers to the question “At what tax rate on withdrawals from the traditional account are contributions to Roth and traditional equal in after-tax results?” The answer is trivial (i.e., when withdrawal rate = contribution rate) for small contributions, but is more complex when contributions are large enough to require taxable investment.

E.g., with rates for
- annual return = 7% (5% from growth and 2% from dividends)
- tax on ordinary income = 30%
- tax on dividends and long term capital gains = 20%
and 30 years between contribution and withdrawal,
...Spreadsheet A suggests 22.02%, while spreadsheet B (by adjusting cell B3 to drive B24 to 0) gets 23.35%.

As a tiebreaker, let’s look at some algebra:
d = return from dividends
e = annual taxable return, including tax on dividends; = i – d*T1
f = effective capital gains tax rate, including added basis from dividends; = T2 * g / e
g = return from growth
i = total return; = g + d
n = number of years invested
P = Total pre-tax amount available to invest
R = Marginal tax rate at withdrawal from the traditional account
T = Current marginal tax rate
T1 = tax rate on dividends
T2 = tax rate on capital gains

Split the total “P” going both to Roth and traditional into a = (P – P*T) and b = P*T.
Roth_a: (P – P*T) * (1 + i)^n
Roth_b: P *T * 0 (all goes to the IRS, nevermore to be seen)
Tradtional_a: (P – P*T) * (1 + i)^n * (1 - R)
Traditional_b: P*T * (1 - T) * ((1 + e)^n * (1 - f) + f)

Traditional_b is “taxable” if P is high enough. See
viewtopic.php?f=10&t=154006&p=2314766#p2310272 for the derivation of that formula.

Setting Roth_a = Traditional_a + Traditional_b we get
P*(1-T) * (1 + i)^n = P*(1-T) * (1 + i)^n * (1 - R) + P*T*(1-T) * ((1 + e)^n * (1 - f) + f)

Solving for R/T:
R/T = ((1 + e)^n * (1 - f) + f) / (1 + i)^n

d = .02, g = .05, i = .07
T1 = 0.2, e = 0.066
T2 = 0.2, f = 0.1515
n = 30

R/T = (1.066^30 * .8485 + .1515) / 1.07^30 = .7782

With T = 30%, R = 23.35%.

Seems spreadsheet B (not the one linked directly from the wiki page) is correct. Or is there something else?

User avatar
FiveK
Posts: 5826
Joined: Sun Mar 16, 2014 2:43 pm

Re: Suggestions for the Wiki

Post by FiveK » Fri Jan 29, 2016 4:34 pm

Following up on the previous post: For IRAs, it is reasonable to assume equal expense ratios in taxable and tax-advantaged funds. For 401ks, 403bs, etc. this is less likely to be true.

Might be possible to combine the "invest in a 401k if the expense ratio is high?" calculation with its two time periods (before and after rollover of the 401k to an IRA) with this "traditional+taxable vs. Roth" equation and have one Grand Unifying Theory.... Until then, spreadsheet B does allow entry of an expense ratio difference.

1) Anyone have the Grand Unifying Theory equation handy?
2) Should the wiki link directly to spreadsheet B instead of spreadsheet A?

User avatar
LadyGeek
Site Admin
Posts: 49146
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Please

Post by LadyGeek » Sun Jan 31, 2016 7:30 pm

I moved FiveK's posts into here, as they pertain to a discrepancy between the spreadsheet referenced in the Notes section of Traditional versus Roth and the spreadsheet in the first post of this 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.

RmeHyd8tion11
Posts: 23
Joined: Thu Oct 16, 2014 8:49 pm

Re: Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Pl

Post by RmeHyd8tion11 » Sun Jan 31, 2016 11:03 pm

swimirvine wrote:
subham wrote:OP: is it possible to figure out how to split between Roth vs Trad each year as opposed to making it a binary decision of contributing full to Roth or Trad?
The money to be invested in Roth vs. Traditional would be equal in retirement assuming equal tax rates (traditional wins for lower retirement tax rates) (Roth wins for higher retirement tax rates)
I believe that it is important to account for real growth as well. Half of a personal pan pizza (6") contains less calories than a quarter of an extra large pizza (16") even if they are made with the same ingredients and baked under the same conditions i.e. if the tax brackets and deductions were the same.

User avatar
FiveK
Posts: 5826
Joined: Sun Mar 16, 2014 2:43 pm

Re: Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Pl

Post by FiveK » Sun Jan 31, 2016 11:54 pm

RmeHyd8tion11 wrote:I believe that it is important to account for real growth as well. Half of a personal pan pizza (6") contains less calories than a quarter of an extra large pizza (16") even if they are made with the same ingredients and baked under the same conditions i.e. if the tax brackets and deductions were the same.
Accounting for things is often good. Not sure how the sausage, peppers, and mushrooms come into the evaluation of the two spreadsheets though. What do you think about which - if either - is correct?

Post Reply