I got pulled into this discussion on Bogleheads, and I think there is a mistake in the way the CSS "Roth vs. Traditional when contributing the maximum allowed" on the Misc Calcs handles cases when the basis fraction is not 100%.

For d=2%, g=5%, T1=19.7%, T2=18.8%, n=40, T=24%, B163=18.8%, and B164=50% the tool is calculating a BETR of 19.855% and I am getting a value of 19.689%. I think it has to do with the way the "ratio of equal outcomes" (cell B161) gets calculated. When I built a quick spreadsheet I'm getting the same R/T value when basis = 100% bit different values and different BETRs when it's <100%; the R/T value seems to be where the two calculations diverge.

I think I copied the formula for the 100% basis fraction from this post because it gave the same result as Harry Sit's Traditional or Roth 401k - Zoho Sheet (don't know what formula Harry uses), relying on the odds of two separate(?) approaches that give the same erroneous answer being low. Of course, low is not the same as zero.

Both the CSS and Harry's tool give 17.989% for the 100% basis case. What do you get when all the above is the same, except basis fraction = 100%?

I think the formula in B165 for a <100% basis case also came from that same BH thread, although Harry's tool doesn't have that option. If you have to pay tax on the funds sold to pay the conversion tax, that's worse than not having to do so. Seems reasonable at first glance to lump the worseness into "marginal tax rate of conversion" while keeping the same "ratio for equal outcomes" but maybe not?

If the difference is <0.2% after 40 years, at the least what's in the CSS now is a decent approximation, and leaving that as a single cell formula may be preferable to a multi-row/multi-column spreadsheet section. But if there is a better single cell formula, that would be great.

On a related topic, a couple years ago I built a tool that combines a bunch of related functions: calculating the performance of different kinds of accounts (taxable, non-deductible, pre-tax, Roth, tax-free), estimating future tax rates, and calculating TvsR comparisons. Most of these are already done in the CSS, but not pulled together in a cohesive way. I suggested you consider merging it into the CSS and you declined. Since then I've developed and polished it quite a bit. It might be worth a second look.

Won't hurt to take a second look. :)

As a check, I ran out the taxable account values year-by-year. For the example I gave with basis fraction = 50% and a $10,000 contribution (or conversion), the taxable value that must be sold today is $2,649.01 (=$2,400/(1 - 18.8%*[1-50%])) with a basis of $1,324.50 (=$2,649.01 * 50%). As a check, $2,649.01 - ($2,649.01 - $1,324.50)*18.8% = $2,400.00.

The future pre-tax value of the taxable account will be $34,225.44 and the future basis will be $9,001.12 (by spreadsheet), giving a future after-tax value of $29,483.26 (=$34,225.44 - ($34,225.44 - $9,001.12)*18.8%). The future Roth value (and pre-tax value before taxes) will be $149,744.58 (=$10,000 * 1.07^40), so the BETR will be

**19.689%** (=$29,483.26 / $149,744.58).

Using closed-form formulas for the value and basis ratios gives the same answers:

v = V_Tx(40)/V_Tx(0) = (1 + 7% - [2%*19.7%])^40 = 12.920102

b = 0.5 + (2% * [1-19.7%])/(7% - [2%*19.7%])*(v-1) = 3.397924

V_Tx(40) = $2,649.01 * (v - (v-b)*18.8%) = $29,483.26

I think the issue is that using an "effective capital gains tax rate" doesn't account for the basis fraction being something other than 1 at the start.

Simplicity is a good goal, but you actually have the calculation done in four cells (B157, B159, B161, and B165). I think if you use those four cells to explicitly track the basis ratio, you'll get the right answer and it will be clear to the user. If it were me, I'd do it in these three steps: v (taxable balance ratio), b (taxable basis ratio), G_Tx (taxable after-tax growth ratio), and then the BETR is:

BETR = [current ordinary income marginal tax rate] / (1 - [current capital gains tax rate] * (1 - [basis fraction])) * G_Tx / (1 + [total return in tax advantaged])^n

I'm sure there's more than one way to do it, though.