Author Topic: Case Study Spreadsheet updates  (Read 299911 times)

murrays007

  • 5 O'Clock Shadow
  • *
  • Posts: 17
Re: Case Study Spreadsheet updates
« Reply #500 on: January 24, 2024, 08:32:53 AM »
...using taxable funds with some capital gains to pay the conversion tax is less beneficial than withholding without sufficient time.
Assuming a 24% ordinary tax rate and an 18.8% QD and LTCG rate, start with $13157.89 in traditional and $3889.03 in taxable.

What do you get for Roth and taxable balances after paying the conversion tax
- from the conversion amount?
- by selling a 0% basis stock?
- by selling a 40% basis stock?
- by selling a 100% basis stock? (this one is the same as using available cash)

What do those results tell you?
Here you go, see attached file.  Note, the amount in the taxable account is the total amount liquidated necessary to cover all taxes including CG so this changes with the basis.  If you want to keep the taxable account balance the same, I'll need to add more columns for "Taxable balance left over after conversion".

This tells me that 100% basis is the best option followed in order by 40%, 0% and withholding, pretty much what we've been saying all along.
« Last Edit: January 24, 2024, 08:44:57 AM by murrays007 »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #501 on: January 24, 2024, 09:29:01 AM »
Note, the amount in the taxable account is the total amount liquidated necessary to cover all taxes including CG so this changes with the basis.  If you want to keep the taxable account balance the same, I'll need to add more columns for "Taxable balance left over after conversion".
You have to use the same starting point if you are comparing different options.

murrays007

  • 5 O'Clock Shadow
  • *
  • Posts: 17
Re: Case Study Spreadsheet updates
« Reply #502 on: January 24, 2024, 09:57:02 AM »
Note, the amount in the taxable account is the total amount liquidated necessary to cover all taxes including CG so this changes with the basis.  If you want to keep the taxable account balance the same, I'll need to add more columns for "Taxable balance left over after conversion".
You have to use the same starting point if you are comparing different options.
Of course, but the question is "how much do you have to invest if the conversion event doesn't happen".  Regardless, here's the spreadsheet, same results.  Note, you could start with $10k or $1M in the taxable account, the conversion advantage and BETR columns remain the same.  You can check my math in the spreadsheet if you'd like, Sheet 4: https://www.dropbox.com/scl/fi/ioeu1rw0jcm8zmbt0froy/Roth-Conversion-Example.xlsx?rlkey=2wjid1lwc0pki4y70c1hp0bpz&dl=0

I have a question, given "BETR is that future tax rate at which the after-tax withdrawal value for an investor would be the same in both the no-conversion and conversion scenarios", why would BETR at year 0 be higher than 24% if all the same conditions apply as your calculation indicates when the basis is less than 100%?  Shouldn't the straight withdrawal form the tIRA and taxable account yield the exact same after tax value as the Roth per the definition?
« Last Edit: January 24, 2024, 10:00:56 AM by murrays007 »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #503 on: January 24, 2024, 10:42:50 AM »
You can check my math in the spreadsheet if you'd like, Sheet 4: https://www.dropbox.com/scl/fi/ioeu1rw0jcm8zmbt0froy/Roth-Conversion-Example.xlsx?rlkey=2wjid1lwc0pki4y70c1hp0bpz&dl=0
I've tried a few times without success to access that.  In any case, it can be difficult to check someone else's spreadsheet quickly....

Quote
I have a question, given "BETR is that future tax rate at which the after-tax withdrawal value for an investor would be the same in both the no-conversion and conversion scenarios", why would BETR at year 0 be higher than 24% if all the same conditions apply as your calculation indicates when the basis is less than 100%?  Shouldn't the straight withdrawal form the tIRA and taxable account yield the exact same after tax value as the Roth per the definition?
If you pay only the marginal rate (24% here) on the conversion amount, then if the tax has been paid from the conversion amount it's just the commutative property of multiplication and the BETR remains 24%.

If you pay the tax from a 100% basis taxable investment, then yes, at the time of conversion the BETR is the same 24% and then decreases with time if there would be tax drag in the taxable account.

If you pay the tax from a <100% basis taxable investment, you have incurred extra tax.  Therefore the BETR is immediately higher than 24% because the tax rate on a traditional withdrawal alone would have to be higher to match the extra tax.

murrays007

  • 5 O'Clock Shadow
  • *
  • Posts: 17
Re: Case Study Spreadsheet updates
« Reply #504 on: January 24, 2024, 11:25:00 AM »
I've tried a few times without success to access that.  In any case, it can be difficult to check someone else's spreadsheet quickly....
Interesting, I just accessed the link from a different browser without a dropbox account and it allowed me to download it without issue.  I've attached the spreadsheet below.

Regardless, my screenshots continue to show that converting has a benefit over withholding when doing a Roth conversion regardless of the basis.  What could I be doing wrong?

Quote
I have a question, given "BETR is that future tax rate at which the after-tax withdrawal value for an investor would be the same in both the no-conversion and conversion scenarios", why would BETR at year 0 be higher than 24% if all the same conditions apply as your calculation indicates when the basis is less than 100%?  Shouldn't the straight withdrawal form the tIRA and taxable account yield the exact same after tax value as the Roth per the definition?
If you pay only the marginal rate (24% here) on the conversion amount, then if the tax has been paid from the conversion amount it's just the commutative property of multiplication and the BETR remains 24%.

If you pay the tax from a 100% basis taxable investment, then yes, at the time of conversion the BETR is the same 24% and then decreases with time if there would be tax drag in the taxable account.

If you pay the tax from a <100% basis taxable investment, you have incurred extra tax.  Therefore the BETR is immediately higher than 24% because the tax rate on a traditional withdrawal alone would have to be higher to match the extra tax.
I disagree, you're ignoring the value and basis of the taxable account if you withdraw from the tIRA.  Why would you have to pay the CG tax from the proceeds of a straight withdrawal when you still have the money in a taxable account which you can choose to sell or not? 

Per my original example, I have $10k in tIRA.  If I convert it and sell a 0% basis taxable fund to pay the $2400, ($2956 total sale), I have $10k in the Roth.

If I withdraw $10k from the tIRA and pay the taxes, I have $7600 & $2956 (more) in my taxable account.  If I sell $2956, pay the $556 CG tax, I'm left with the exact same $10k in cash.  My screenshots and spreadsheet show this very clearly.

Put another way, withdrawing the tIRA doesn't reduce the tax liability of the funds you planned to liquidate when converting.
« Last Edit: January 24, 2024, 11:45:59 AM by murrays007 »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #505 on: January 24, 2024, 07:47:22 PM »
Not enough to analyze in depth now but you may be on to something.

murrays007

  • 5 O'Clock Shadow
  • *
  • Posts: 17
Re: Case Study Spreadsheet updates
« Reply #506 on: January 24, 2024, 10:08:17 PM »
Not enough to analyze in depth now but you may be on to something.
I'll admit, I've been opening responses for a couple days hoping an acknowledgment like this.

Thanks again for your attention and let me know if you have any additional questions or any more that I can help.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #507 on: January 27, 2024, 01:57:01 PM »
Not enough to analyze in depth now but you may be on to something.
I'll admit, I've been opening responses for a couple days hoping an acknowledgment like this.

Thanks again for your attention and let me know if you have any additional questions or any more that I can help.
Turns out my lack of time will likely extend longer than I thought.  Seems you have the bit between your teeth so have at it!

As noted, the formula used when paying the conversion tax by selling investments came from the Bogleheads topic Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Please - Bogleheads.org.  It is (I think) also included in Retirement plan analysis (math) - Bogleheads.  If you can reach consensus with those folks that would be great.  When I looked at the formula, it seemed directionally correct, correct at 0% tax or 100% basis, etc., but if appearances are deceiving and something better is available, great! 

I suppose you'd have to consider tax payment methods both "now" and "in the future" to ensure "amount available after all taxes" is accurately used as the deciding metric.

The CSS is a crowd-sourced tool so I'm happy to include/revise whatever is needed.  For the most part, folks who participate in forums like this, Bogleheads, Early-Retirement.org, etc., are also interested only in what is correct, not personal glory or proving someone else wrong.  I read the Boglehead thread you mentioned early on, and hope you do take to heart that it shouldn't be a personal thing, and avoid snarky comments.  One of the first Roth vs Traditional threads at Bogleheads.org didn't set a very good example....

murrays007

  • 5 O'Clock Shadow
  • *
  • Posts: 17
Re: Case Study Spreadsheet updates
« Reply #508 on: January 27, 2024, 08:02:57 PM »
Turns out my lack of time will likely extend longer than I thought.  Seems you have the bit between your teeth so have at it!

As noted, the formula used when paying the conversion tax by selling investments came from the Bogleheads topic Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Please - Bogleheads.org.  It is (I think) also included in Retirement plan analysis (math) - Bogleheads.  If you can reach consensus with those folks that would be great.  When I looked at the formula, it seemed directionally correct, correct at 0% tax or 100% basis, etc., but if appearances are deceiving and something better is available, great! 

I suppose you'd have to consider tax payment methods both "now" and "in the future" to ensure "amount available after all taxes" is accurately used as the deciding metric.

The CSS is a crowd-sourced tool so I'm happy to include/revise whatever is needed.  For the most part, folks who participate in forums like this, Bogleheads, Early-Retirement.org, etc., are also interested only in what is correct, not personal glory or proving someone else wrong.  I read the Boglehead thread you mentioned early on, and hope you do take to heart that it shouldn't be a personal thing, and avoid snarky comments.  One of the first Roth vs Traditional threads at Bogleheads.org didn't set a very good example....
I hope you’re at least doing something enjoyable!  Thanks for the vote of confidence and the links. I’m about 80% closer to a useable formula, but the change to basis from dividends compounding can’t be solved by a simple formula, but we should be able to get it close enough to not affect the BETR by more than a few 10ths of a percent. Ironically, the marginal rate calculation that set me on this path doesn’t appear to be relevant to the BETR value!

I think I’ll start a new thread rather than reply to the one you linked since it appears to be 10 years old.

I’ll admit, there is some satisfaction in “winning” a debate, I’m more interested in correcting what I believe to be an error in a widely used and respected spreadsheet. I’ve certainly learned a great deal while framing my discussion with you and on Bogleheads.

murrays007

  • 5 O'Clock Shadow
  • *
  • Posts: 17
Re: Case Study Spreadsheet updates
« Reply #509 on: February 08, 2024, 12:27:54 PM »
Turns out my lack of time will likely extend longer than I thought.  Seems you have the bit between your teeth so have at it!

As noted, the formula used when paying the conversion tax by selling investments came from the Bogleheads topic Roth 401(k) vs. 401(k) Spreadsheet Attempt - Critique Please - Bogleheads.org.  It is (I think) also included in Retirement plan analysis (math) - Bogleheads.  If you can reach consensus with those folks that would be great.  When I looked at the formula, it seemed directionally correct, correct at 0% tax or 100% basis, etc., but if appearances are deceiving and something better is available, great! 
FYI, I started this thread which has a detailed summary of my logic: https://www.bogleheads.org/forum/viewtopic.php?t=422766  I got some input as you can see to help make some improvements.

Here's a link to the Excel sheet via dropbox: https://www.dropbox.com/scl/fi/ioeu1rw0jcm8zmbt0froy/Roth-Conversion-Example.xlsx?rlkey=2wjid1lwc0pki4y70c1hp0bpz&dl=0
And a Googlesheets version that should work, though I haven't fully analyzed it: https://docs.google.com/spreadsheets/d/1eId77CKaBKAknLBsNt16UH4P7OEuGl7OKHHvt_fVdaI/edit?usp=sharing

Have a look when you get a chance and we can discuss how to incorporate it into the current CSS.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #510 on: February 08, 2024, 09:32:08 PM »
Based on that thread, it seems "...the assumption that cash flow will be available "in the future" and the math above is why the BETR would be higher than the current conversion tax rate" is the main point. 

In other words, in that case we should change the wording "Current capital gain tax rate" to "Current tax rate on the funds used to pay for the conversion" (or something like that) and note that in the case of paying the tax from the conversion itself, "Basis fraction of funds used to pay tax" should be set to 0%.

As also noted there, "different assumptions can lead to different conclusions."  If someone would never have cash flow to pay the tax, and would always pay the tax out of the conversion, then we're back to the "commutative property" analysis.

Having cash flow available (e.g., SS, RMDs, etc.) seems the more plausible assumption.  What do you think?

murrays007

  • 5 O'Clock Shadow
  • *
  • Posts: 17
Re: Case Study Spreadsheet updates
« Reply #511 on: February 08, 2024, 10:57:10 PM »
Based on that thread, it seems "...the assumption that cash flow will be available "in the future" and the math above is why the BETR would be higher than the current conversion tax rate" is the main point. 

In other words, in that case we should change the wording "Current capital gain tax rate" to "Current tax rate on the funds used to pay for the conversion" (or something like that) and note that in the case of paying the tax from the conversion itself, "Basis fraction of funds used to pay tax" should be set to 0%.

As also noted there, "different assumptions can lead to different conclusions."  If someone would never have cash flow to pay the tax, and would always pay the tax out of the conversion, then we're back to the "commutative property" analysis.

Having cash flow available (e.g., SS, RMDs, etc.) seems the more plausible assumption.  What do you think?

I don't think that assumption is valid based on this part of the Vanguard definition of BETR: "...after-tax withdrawal value for an investor would be the same in both the no-conversion and conversion scenarios." 

With any assumption of growth, the taxable account funds in the no-conversion scenario will have value and likely some level of tax due regardless of other funds being available.  In other words, it doesn't matter if the taxable account funds are used to pay for future tIRA withdrawals or not, the question is what is the after tax value of those funds after X number of years with the given assumptions.   

It is possible, probably likely in my personal case, those funds would be held until after death and the basis is stepped up, but that's an option the user can choose.  And yes, that does increase the time 0 BETR to match the current CSS calculation.

And yes, using simple withholding reverts back to the commutative property as you noted and is summarized in columns S-Y, though I'm not sure that needs to be included in the final product.

murrays007

  • 5 O'Clock Shadow
  • *
  • Posts: 17
Re: Case Study Spreadsheet updates
« Reply #512 on: February 09, 2024, 10:50:14 AM »
FYI, I put together a synopsis of different takes on calculating marginal tax rates under the various scenarios used for paying the taxes.  Have a look if you're interested: https://www.bogleheads.org/forum/viewtopic.php?p=7702290#p7702290

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #513 on: February 10, 2024, 11:12:01 AM »
FYI, I put together a synopsis of different takes on calculating marginal tax rates under the various scenarios used for paying the taxes.  Have a look if you're interested: https://www.bogleheads.org/forum/viewtopic.php?p=7702290#p7702290
Appears the discussion there is ongoing with no consensus. ;)

At this point, some new wording and/or cell notes as previously proposed is likely all that will change in the "Roth vs. Traditional when contributing the maximum allowed" section of the 'Misc. calcs' tab when the next version is released.  That will most likely happen when the Tax Foundation releases its 2024 state tax post.

murrays007

  • 5 O'Clock Shadow
  • *
  • Posts: 17
Re: Case Study Spreadsheet updates
« Reply #514 on: February 10, 2024, 02:50:11 PM »
Appears the discussion there is ongoing with no consensus. ;)
Indeed!  It's a bit like the Monty Hall question :)

At this point, some new wording and/or cell notes as previously proposed is likely all that will change in the "Roth vs. Traditional when contributing the maximum allowed" section of the 'Misc. calcs' tab when the next version is released.  That will most likely happen when the Tax Foundation releases its 2024 state tax post.
One more point about the assumption of funds being available to cover tIRA withdrawal taxes in the future, why does that assumption seem to only apply to LTCG tax liability present at the time of conversion?

For example, there's a "Tax rate on capital gains at withdrawal" input on the current spreadsheet and shouldn't that rate apply to the tax liability of funds in the taxable account at the time of conversion for cases where basis < 100%?  Put another way, shouldn't the future funds available assumption make that tax rate equal to 0 for all future taxable account LTCG tax liability or, more appropriately, the "Tax rate on capital gains at withdrawal" apply to all untaxed LTCGs, not just the gains after the time of conversion?

« Last Edit: February 10, 2024, 10:19:28 PM by murrays007 »

fyre4ce

  • 5 O'Clock Shadow
  • *
  • Posts: 12
Re: Case Study Spreadsheet updates
« Reply #515 on: February 12, 2024, 01:02:45 AM »
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 derived the formula on Bogleheads and mine was a little more complicated, calculating balance growth and basis growth separately. It may be possible to simplify that down a bit, but it looks like a change of some kind with the CSS calculation is probably needed.

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.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #516 on: February 12, 2024, 01:58:31 AM »
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.

Quote
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. :)

fyre4ce

  • 5 O'Clock Shadow
  • *
  • Posts: 12
Re: Case Study Spreadsheet updates
« Reply #517 on: February 12, 2024, 10:55:21 AM »
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.

Quote
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.

murrays007

  • 5 O'Clock Shadow
  • *
  • Posts: 17
Re: Case Study Spreadsheet updates
« Reply #518 on: February 12, 2024, 01:04:38 PM »
I derived the formula on Bogleheads and mine was a little more complicated, calculating balance growth and basis growth separately. It may be possible to simplify that down a bit, but it looks like a change of some kind with the CSS calculation is probably needed.

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.

Thanks again for your input @fyre4ce.

FYI, I've reduced the BETR formula down to a few cells and could make it one cell if desired.  It's in the "Calculated Totals" row here: https://docs.google.com/spreadsheets/d/1eId77CKaBKAknLBsNt16UH4P7OEuGl7OKHHvt_fVdaI/edit?usp=sharing

The table above that is for more easily visualizing the values and confirming the more complicated formulas.

Per this post, https://www.bogleheads.org/forum/viewtopic.php?p=7707499#p7707499, @fyre4ce and I came up with the same BETR values to .01% out to 40 years so we are definitely on the same page.
« Last Edit: February 12, 2024, 02:07:16 PM by murrays007 »

dandarc

  • Walrus Stache
  • *******
  • Posts: 5457
  • Age: 41
  • Pronouns: he/him/his
Re: Case Study Spreadsheet updates
« Reply #519 on: February 12, 2024, 01:19:13 PM »
Version 22.16 (2022/2023/2024)
  • 2024 Federal Withholding Tables
  • Format tax section to match IRS forms better
Implemented the "Standard Withholding Rate Schedules" from the spreadsheet attached to https://www.irs.gov/pub/irs-dft/p15t--dft.pdf.  That's the last of the 2024 IRS updates that apply to this spreadsheet.

Cosmetic labeling and reordering of the tax numbers, mostly in Calculations!E2:I43.  Makes it easier to compare with actual Form 1040 and Schedules 2&3 numbers.

As always, comments/suggestions/bug identifications/etc. are appreciated.  Either PM me or drop a note here.

2022/2023/2024 taxes version: Case Study Spreadsheet

Notes:
  • In early 2023, Microsoft added a protection/roadblock to using downloaded Excel files containing macros.  Details can be found at A potentially dangerous macro has been blocked - Microsoft Support.

    Most of the CSS can be used just fine without having macros enabled.  If you want to change the x-axis or y-axis variable for the marginal rate chart, however, you either need to know how Excel data tables work and edit the formulas directly, or enable macros and use the Calculations!G107 or L107 cells along with clicking the "Update chart" button near cell L114.

    It appears the simplest way to get around Microsoft's "helpful" macro block is described in the "Details can be found at..." link above (see that for a screen shot of step #3 below):
      1. Open Windows File Explorer and go to the folder where you saved the file.
      2. Right-click the file and choose Properties from the context menu.
      3. At the bottom of the General tab, select the Unblock checkbox and select OK.
Just double checking - is this the most recent version? Not usually so far upthread, so I thought I'd ask.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #520 on: February 12, 2024, 03:14:46 PM »
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.
For example, should a+a*b+b be written as a*(1+b)+b or a+b*(1+a)?  But back to the problem at hand.

Looking at the formulas shown in Maxing out your retirement accounts, they appear algebraically identical to what is in the CSS (see "Algebraic comparison" below).

If G_Tx in your post is given by (using the nomenclature in that BH wiki):
  v - (v - 1) * MTRcg*
then for basis fraction = 1 we have
BETR / [current ordinary income marginal tax rate] = G_Tx / (1 + [total return in tax advantaged])^n
and that is also what is in the CSS's cell B161.

For a basis fraction < 1, dividing by (1 - [current capital gains tax rate] * (1 - [basis fraction])) is also what the CSS does.

So it seems we are agreeing on all counts (and, at least for basis fraction = 1, also agreeing with Harry Sit's results) - except are you saying the year-by-year calculation gives something different?  Or did I miss something in the algebraic comparison?

Algebraic comparison
v = (1+r-y*MTRdiv)^t
r = B154+B153
y = B153
MTRdiv = B156
t = B160
v  = (1+B154+B153-B153*B156)^B160
   = (1+B154+B153*(1-B156))^B160
   = (1+B157)^B160
so r-y*MTRdiv = B157

MTRcg* = MTRcg * (r-y)/(r-y*MTRdiv)
MTRcg = B158
r-y = B154
MTRcg* = B158*B154/B157 = B159

MTRw/MTRn = (v-(v-1)*MTRcg*)/(1+r)^t
  = (v*(1-MTRcg*)+MTRcg*)/(1+r)^t
  = ((1+B157)^B160 * (1-B159) + B159)/(1+B155)^B160
  = B161


MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #521 on: February 12, 2024, 03:18:05 PM »
Version 22.16 (2022/2023/2024)
  • 2024 Federal Withholding Tables
  • Format tax section to match IRS forms better
Implemented the "Standard Withholding Rate Schedules" from the spreadsheet attached to https://www.irs.gov/pub/irs-dft/p15t--dft.pdf.  That's the last of the 2024 IRS updates that apply to this spreadsheet.

Cosmetic labeling and reordering of the tax numbers, mostly in Calculations!E2:I43.  Makes it easier to compare with actual Form 1040 and Schedules 2&3 numbers.

As always, comments/suggestions/bug identifications/etc. are appreciated.  Either PM me or drop a note here.

2022/2023/2024 taxes version: Case Study Spreadsheet

Notes:
  • In early 2023, Microsoft added a protection/roadblock to using downloaded Excel files containing macros.  Details can be found at A potentially dangerous macro has been blocked - Microsoft Support.

    Most of the CSS can be used just fine without having macros enabled.  If you want to change the x-axis or y-axis variable for the marginal rate chart, however, you either need to know how Excel data tables work and edit the formulas directly, or enable macros and use the Calculations!G107 or L107 cells along with clicking the "Update chart" button near cell L114.

    It appears the simplest way to get around Microsoft's "helpful" macro block is described in the "Details can be found at..." link above (see that for a screen shot of step #3 below):
      1. Open Windows File Explorer and go to the folder where you saved the file.
      2. Right-click the file and choose Properties from the context menu.
      3. At the bottom of the General tab, select the Unblock checkbox and select OK.
Just double checking - is this the most recent version? Not usually so far upthread, so I thought I'd ask.
Yes.  I suppose it would have been better to have this lengthy side discussion about a couple of cells on the 'Misc. calcs' tab in a separate thread....

The next planned release will be after the Tax Foundation releases its compilation of 2024 state tax rates.

murrays007

  • 5 O'Clock Shadow
  • *
  • Posts: 17
Re: Case Study Spreadsheet updates
« Reply #522 on: February 12, 2024, 04:13:51 PM »
The challenge I had was calculating the cost basis since the after tax dividends add to the basis in dollars each year.  This is easy in a table, but took some time to resolve into a single cell.

I don't have the basis expressed as an algebraic formula, though I'm sure I could come up with it if needed, but I have it calculated in a single cell in H29 here: https://docs.google.com/spreadsheets/d/1eId77CKaBKAknLBsNt16UH4P7OEuGl7OKHHvt_fVdaI/edit?usp=sharing

ETA: My formulas are far more complex than needed, @Fivek came up with a simple change to one formula in the CSS: https://www.bogleheads.org/forum/viewtopic.php?p=7710544#p7710544

Here's the formula for cell B161 in the CSS as I understand the post which comes up with the same BETR in a variety of cases I tried:
Code: [Select]
=(((1 + B157)^B160 * (1 - B159) + B159)-(1-B164)*B158) / (1 + B155)^B160
« Last Edit: February 12, 2024, 05:05:28 PM by murrays007 »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #523 on: February 12, 2024, 06:29:37 PM »
...a simple change to one formula in the CSS: https://www.bogleheads.org/forum/viewtopic.php?p=7710544#p7710544

Here's the formula for cell B161 in the CSS as I understand the post which comes up with the same BETR in a variety of cases I tried:
Code: [Select]
=(((1 + B157)^B160 * (1 - B159) + B159)-(1-B164)*B158) / (1 + B155)^B160
Looks great!

That kind of update is, shall we say, very doable!  Thanks!

murrays007

  • 5 O'Clock Shadow
  • *
  • Posts: 17
Re: Case Study Spreadsheet updates
« Reply #524 on: February 12, 2024, 06:43:26 PM »
...a simple change to one formula in the CSS: https://www.bogleheads.org/forum/viewtopic.php?p=7710544#p7710544

Here's the formula for cell B161 in the CSS as I understand the post which comes up with the same BETR in a variety of cases I tried:
Code: [Select]
=(((1 + B157)^B160 * (1 - B159) + B159)-(1-B164)*B158) / (1 + B155)^B160
Looks great!

That kind of update is, shall we say, very doable!  Thanks!

When I read the post, I doubted it could be that simple since I did more of a brute force formula without refining the algebra.

Thank you for listening and remaining patient. I’ve definitely learned more about BETR than anyone ever needs to know :D

fyre4ce

  • 5 O'Clock Shadow
  • *
  • Posts: 12
Re: Case Study Spreadsheet updates
« Reply #525 on: February 12, 2024, 11:41:02 PM »
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.
For example, should a+a*b+b be written as a*(1+b)+b or a+b*(1+a)?  But back to the problem at hand.

Looking at the formulas shown in Maxing out your retirement accounts, they appear algebraically identical to what is in the CSS (see "Algebraic comparison" below).

If G_Tx in your post is given by (using the nomenclature in that BH wiki):
  v - (v - 1) * MTRcg*

then for basis fraction = 1 we have
BETR / [current ordinary income marginal tax rate] = G_Tx / (1 + [total return in tax advantaged])^n
and that is also what is in the CSS's cell B161.

For a basis fraction < 1, dividing by (1 - [current capital gains tax rate] * (1 - [basis fraction])) is also what the CSS does.

So it seems we are agreeing on all counts (and, at least for basis fraction = 1, also agreeing with Harry Sit's results) - except are you saying the year-by-year calculation gives something different?  Or did I miss something in the algebraic comparison?

Algebraic comparison
v = (1+r-y*MTRdiv)^t
r = B154+B153
y = B153
MTRdiv = B156
t = B160
v  = (1+B154+B153-B153*B156)^B160
   = (1+B154+B153*(1-B156))^B160
   = (1+B157)^B160
so r-y*MTRdiv = B157

MTRcg* = MTRcg * (r-y)/(r-y*MTRdiv)
MTRcg = B158
r-y = B154
MTRcg* = B158*B154/B157 = B159

MTRw/MTRn = (v-(v-1)*MTRcg*)/(1+r)^t
  = (v*(1-MTRcg*)+MTRcg*)/(1+r)^t
  = ((1+B157)^B160 * (1-B159) + B159)/(1+B155)^B160
  = B161

I think I see the problem, highlighted in red above. The formula V_at = V - (V - 1) x MTR_cg* is a simplification of the more general formula, which is:

V_at = V - (V - B) x MTR_cg

The derivation of the general formula, and the simplification with the MTR_cg* calculation for when the initial value equals the initial basis, are here:

https://www.bogleheads.org/wiki/Tax_analysis_(math)#Taxable_account_performance

When the basis fraction is <1, you have to adjust both the nominal dollars to track in the taxable side account (which goes up), and also the growth rate on the taxable (which goes down, because the basis is lower). It looks like your formula only did the first one but not the second.

The challenge I had was calculating the cost basis since the after tax dividends add to the basis in dollars each year.  This is easy in a table, but took some time to resolve into a single cell.

I don't have the basis expressed as an algebraic formula, though I'm sure I could come up with it if needed, but I have it calculated in a single cell in H29 here: https://docs.google.com/spreadsheets/d/1eId77CKaBKAknLBsNt16UH4P7OEuGl7OKHHvt_fVdaI/edit?usp=sharing

ETA: My formulas are far more complex than needed, @Fivek came up with a simple change to one formula in the CSS: https://www.bogleheads.org/forum/viewtopic.php?p=7710544#p7710544

Here's the formula for cell B161 in the CSS as I understand the post which comes up with the same BETR in a variety of cases I tried:
Code: [Select]
=(((1 + B157)^B160 * (1 - B159) + B159)-(1-B164)*B158) / (1 + B155)^B160

I tried your formula in my spreadsheet and it gives the same results for all cases I checked. It's less intuitive to me than the way I derived it, but if it works, then go for it!

passionfruit17

  • 5 O'Clock Shadow
  • *
  • Posts: 19
Re: Case Study Spreadsheet updates
« Reply #526 on: February 15, 2024, 09:33:23 PM »
Where do you enter dependents? I get a $500 credit for my 21 year old student, not sure where to account for that.
I ended over-withdrawing this year, so going through an effort to do better next year. I don;t want to give Uncle Sam a a loan, but I also don't want to undershoot and run afoul of Safe Harbor.

secondcor521

  • Walrus Stache
  • *******
  • Posts: 5503
  • Age: 54
  • Location: Boise, Idaho
  • Big cattle, no hat.
    • Age of Eon - Overwatch player videos
Re: Case Study Spreadsheet updates
« Reply #527 on: February 15, 2024, 09:49:24 PM »
Where do you enter dependents? I get a $500 credit for my 21 year old student, not sure where to account for that.
I ended over-withdrawing this year, so going through an effort to do better next year. I don;t want to give Uncle Sam a a loan, but I also don't want to undershoot and run afoul of Safe Harbor.

I'm not sure if the version I have is the latest, but it should be around Calculations!G3 or so.

passionfruit17

  • 5 O'Clock Shadow
  • *
  • Posts: 19
Re: Case Study Spreadsheet updates
« Reply #528 on: February 16, 2024, 09:51:47 AM »
Where do you enter dependents? I get a $500 credit for my 21 year old student, not sure where to account for that.
I ended over-withdrawing this year, so going through an effort to do better next year. I don;t want to give Uncle Sam a a loan, but I also don't want to undershoot and run afoul of Safe Harbor.

I'm not sure if the version I have is the latest, but it should be around Calculations!G3 or so.

Doh! CTC line 30 is autogenerated, but Line G2 is where you enter dependents. You can also enter full-time student status in G10
I can't believe I missed it! Thanks!

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #529 on: February 24, 2024, 03:15:27 PM »
Version 22.17 (2022/2023/2024)
  • 2024 State tax updates
Thanks once again to the Tax Foundation folks who compile 2024 State Income Tax Rates and Brackets, the values in that publication are now included.

The state tax numbers are taken on faith from the Tax Foundation spreadsheet.  I leave it to you residents of the various states to check for typos or describe simple calculations that would make the results more accurate.  State Income Tax calculations - Crowdsourcing request is the preferred place for those responses.

For those who live in states with tax codes rivaling the federal system (e.g., California et al.), well, what's in here should at least be approximately correct....

The tweak to the ratio calculation for Roth conversion analysis on the Misc. calcs tab is also included.


As always, comments/suggestions/bug identifications/etc. are appreciated.  Either PM me or drop a note here.

2022/2023/2024 taxes version: Case Study Spreadsheet

Notes:
  • In early 2023, Microsoft added a protection/roadblock to using downloaded Excel files containing macros.  Details can be found at A potentially dangerous macro has been blocked - Microsoft Support.

    Most of the CSS can be used just fine without having macros enabled.  If you want to change the x-axis or y-axis variable for the marginal rate chart, however, you either need to know how Excel data tables work and edit the formulas directly, or enable macros and use the Calculations!G107 or L107 cells along with clicking the "Update chart" button near cell L114.

    It appears the simplest way to get around Microsoft's "helpful" macro block is described in the "Details can be found at..." link above (see that for a screen shot of step #3 below):
      1. Open Windows File Explorer and go to the folder where you saved the file.
      2. Right-click the file and choose Properties from the context menu.
      3. At the bottom of the General tab, select the Unblock checkbox and select OK.

Reynold

  • Bristles
  • ***
  • Posts: 353
Re: Case Study Spreadsheet updates
« Reply #530 on: March 27, 2024, 08:17:09 AM »
I'm trying to use the spreadsheet to work out tax savings for doing Roth conversions early in retirement, and have not found a way to see long term cumulative tax expenses.  Am I missing something?

Also, plugging different values into cell B31, tIRA distribution (converted to Roth or not), does not seem to change the graph in cell F82, am I missing something there? 

secondcor521

  • Walrus Stache
  • *******
  • Posts: 5503
  • Age: 54
  • Location: Boise, Idaho
  • Big cattle, no hat.
    • Age of Eon - Overwatch player videos
Re: Case Study Spreadsheet updates
« Reply #531 on: March 27, 2024, 09:58:11 AM »
I'm trying to use the spreadsheet to work out tax savings for doing Roth conversions early in retirement, and have not found a way to see long term cumulative tax expenses.  Am I missing something?

Also, plugging different values into cell B31, tIRA distribution (converted to Roth or not), does not seem to change the graph in cell F82, am I missing something there?

The CSS appears to be intended to be used to calculate the current year federal (and to some extent state) income tax situation.  Roth conversions usually save taxes over a multiple year time frame, and are thus beyond what the CSS tool does.  There are other tools that help with the Roth conversion question:  Pralana Gold ($), RPM over at Bogleheads, and (when it is working and up to date) i-orp.com.

In order for the graph to work properly, you need to be using genuine Excel (not any of the Excel knockoffs like Google sheets or Libre Office).

Also, you may be misunderstanding the graph.  The X-axis represents different possible values of tIRA distributions from $0 up to about $120,000, and the Y-axis represents the resulting tax liability.  So changing the value in B31 just changes the current tax situation to a different point on the graph; the graph represents a wide range of options.

Said another way, you're probably changing B31 to different values to see the impact on your taxes.  Instead, you can just look at the graph and see the impact on your taxes for all possible (well, a wide range of) values of tIRA distribution.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #532 on: March 27, 2024, 10:39:58 AM »
I'm trying to use the spreadsheet to work out tax savings for doing Roth conversions early in retirement, and have not found a way to see long term cumulative tax expenses.  Am I missing something?

Also, plugging different values into cell B31, tIRA distribution (converted to Roth or not), does not seem to change the graph in cell F82, am I missing something there?

The CSS appears to be intended to be used to calculate the current year federal (and to some extent state) income tax situation.  Roth conversions usually save taxes over a multiple year time frame, and are thus beyond what the CSS tool does.  There are other tools that help with the Roth conversion question:  Pralana Gold ($), RPM over at Bogleheads, and (when it is working and up to date) i-orp.com.

In order for the graph to work properly, you need to be using genuine Excel (not any of the Excel knockoffs like Google sheets or Libre Office).

Also, you may be misunderstanding the graph.  The X-axis represents different possible values of tIRA distributions from $0 up to about $120,000, and the Y-axis represents the resulting tax liability.  So changing the value in B31 just changes the current tax situation to a different point on the graph; the graph represents a wide range of options.

Said another way, you're probably changing B31 to different values to see the impact on your taxes.  Instead, you can just look at the graph and see the impact on your taxes for all possible (well, a wide range of) values of tIRA distribution.
Thanks, that was essentially what I had started typing, then noticed you had already covered it! :)

The "how much of a Roth conversion should I do this year?" question comes down to evaluating your marginal tax rate for the conversion now, vs. the marginal tax rate you predict to incur for withdrawing/converting in the future.

As has been said, It’s Difficult to Make Predictions, Especially About the Future.  For example, if one is concerned about the higher tax rates a survivor will face when going from MFJ to single filing, that leads to larger conversion amounts now.  But if one is concerned about large nursing home expenses in the future, the itemized medical deduction for those expenses will lead to lower tax rates and thus smaller conversion amounts now.

There is also the difference between charitable heirs who will pay 0% on inheritance, vs. heirs (your children, perhaps) who might still be working and subject to high marginal rates when the forced distributions are added to their W-2 income.

See this post for how the CSS can give an estimate for future marginal rates.  Given all the uncertainty in market returns, personal situations, tax law, etc., it's not clear how much a multi-year model provides in terms of actionable information for "what to do this year?" over a "what do I expect for a marginal rate after SS and RMDs are present?"

A multi-year model could be more useful if the amount of Roth conversions from year to year will significantly affect future RMDs and thus future marginal tax rates.  But the spread in results the even more Simple method (compared with the post linked above) can provide lends support to the suggestion "take a reasonable guess with minimal effort at your future situation and decide what to do this year; repeat the process next year with updated guesses."

Reynold

  • Bristles
  • ***
  • Posts: 353
Re: Case Study Spreadsheet updates
« Reply #533 on: March 27, 2024, 12:52:37 PM »
I'm trying to use the spreadsheet to work out tax savings for doing Roth conversions early in retirement, and have not found a way to see long term cumulative tax expenses.  Am I missing something?

Also, plugging different values into cell B31, tIRA distribution (converted to Roth or not), does not seem to change the graph in cell F82, am I missing something there?

The CSS appears to be intended to be used to calculate the current year federal (and to some extent state) income tax situation.  Roth conversions usually save taxes over a multiple year time frame, and are thus beyond what the CSS tool does.  There are other tools that help with the Roth conversion question:  Pralana Gold ($), RPM over at Bogleheads, and (when it is working and up to date) i-orp.com.

Also, you may be misunderstanding the graph.  The X-axis represents different possible values of tIRA distributions from $0 up to about $120,000, and the Y-axis represents the resulting tax liability.  So changing the value in B31 just changes the current tax situation to a different point on the graph; the graph represents a wide range of options.

Said another way, you're probably changing B31 to different values to see the impact on your taxes.  Instead, you can just look at the graph and see the impact on your taxes for all possible (well, a wide range of) values of tIRA distribution.

Thank you for the explanation, you are correct, I misunderstood what the graph was showing.  I wanted to look over an even wider range of tIRA distributions, and I was hoping for something like I had seen from a couple of financial advisors that allowed (obviously estimated) tax savings over a projected lifetime versus tax hit now for converting some amount of a tIRA to a Roth.  Most of those tools didn't seem to account at all for ACA subsidy loss (which we will be using shortly when COBRA runs out) and this spreadsheet did, so I was hoping it would do future projections as well and solve ALL my financial questions for all time. :)

I'll look for the tools you and MDM mention for that purpose, then.  Thanks also MDM for replying, I realize that predicting future tax law whims of Congress is difficult, but I see a much higher likelihood of higher tax rates in my future than lower ones.  If I'm wrong and rates go down, I'll be pretty happy actually. :)

The other thing I noticed that puzzled me a bit is that regardless of the amount that I put in cell B31, the federal tax changes but the NJ state tax does not.  State tax just seems to be based on my other income, not the tIRA distribution.  NJ, at least, does tax these distributions like ordinary income.  I'm not sure where to change this though in the spreadsheet? 

secondcor521

  • Walrus Stache
  • *******
  • Posts: 5503
  • Age: 54
  • Location: Boise, Idaho
  • Big cattle, no hat.
    • Age of Eon - Overwatch player videos
Re: Case Study Spreadsheet updates
« Reply #534 on: March 27, 2024, 01:01:40 PM »
The other thing I noticed that puzzled me a bit is that regardless of the amount that I put in cell B31, the federal tax changes but the NJ state tax does not.  State tax just seems to be based on my other income, not the tIRA distribution.  NJ, at least, does tax these distributions like ordinary income.  I'm not sure where to change this though in the spreadsheet?

Take a look at the cell B19 in the State Tax worksheet.

The CSS is primarily focused on federal taxes and it does a very good job at that.  It does somewhat work for state income taxes as well, but the fidelity there is less because there are 50 different state income tax rulesets and maintaining those for free is more than one should expect.

If you make improvements to the NJ state tax calculations, you can share them here or with @MDM and they might be included in the next revision.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #535 on: March 27, 2024, 01:25:50 PM »
I wanted to look over an even wider range of tIRA distributions
Just change cell P83 to a larger number, either directly or by editing the formula there and changing "110000" to whatever upper limit you want.  If you want the graph to start with an amount >0, make cell P84 that amount.

Quote
and I was hoping for something like I had seen from a couple of financial advisors that allowed (obviously estimated) tax savings over a projected lifetime versus tax hit now for converting some amount of a tIRA to a Roth.  Most of those tools didn't seem to account at all for ACA subsidy loss (which we will be using shortly when COBRA runs out) and this spreadsheet did, so I was hoping it would do future projections as well and solve ALL my financial questions for all time. :)
Careful not to use "minimum tax paid" as your goal.  It should be "maximum amount left after tax".  Those will be the same for a single year, but not necessarily when adding multiple years.

Quote
I'll look for the tools you and MDM mention for that purpose, then.  Thanks also MDM for replying, I realize that predicting future tax law whims of Congress is difficult, but I see a much higher likelihood of higher tax rates in my future than lower ones.  If I'm wrong and rates go down, I'll be pretty happy actually. :)
If you have an idea of the marginal rate you'll be paying in the future, then converting through that rate now is likely to be as good a strategy as any tool will provide.

Quote
The other thing I noticed that puzzled me a bit is that regardless of the amount that I put in cell B31, the federal tax changes but the NJ state tax does not.  State tax just seems to be based on my other income, not the tIRA distribution.  NJ, at least, does tax these distributions like ordinary income.  I'm not sure where to change this though in the spreadsheet?
Based on NJ Income Tax – IRA Distributions it appears NJ taxes a variable portion of an IRA distribution.  One could change the formula in cell 'State Tax"!O52 to multiply B11 by the non-taxable fraction....  I'm open to other suggestions, preferably made in the State Income Tax calculations - Crowdsourcing request thread, but I won't ignore them if made here. ;)