Case Study Spreadsheet updates

[murrays007]

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

[MDM]

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]

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?

[MDM]

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

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]

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?

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.

[MDM]

Not enough to analyze in depth now but you may be on to something.

[murrays007]

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]

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]

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]

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]

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]

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]

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]

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]

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?

[fyre4ce]

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]

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

[fyre4ce]

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.

[murrays007]

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.

[dandarc]

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:

• Rather than "Request Edit Access" to the file on Google Drive - which, if granted, would allow one to edit the same file that everyone else sees, so it will not be granted ;)
  - After opening the file on Google Drive (link above), click File>Download>Microsoft Excel (xls)
  Then, either open in Excel immediately, edit, and later save from Excel to your computer, or save immediately to your computer and later open and edit that file with Excel.
  
• See
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg2138684/#msg2138684 and
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1879496/#msg1879496
  regarding warnings one might see, and why those are spurious warnings for these files.

• 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]

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]

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:

• Rather than "Request Edit Access" to the file on Google Drive - which, if granted, would allow one to edit the same file that everyone else sees, so it will not be granted ;)
  - After opening the file on Google Drive (link above), click File>Download>Microsoft Excel (xls)
  Then, either open in Excel immediately, edit, and later save from Excel to your computer, or save immediately to your computer and later open and edit that file with Excel.
  
• See
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg2138684/#msg2138684 and
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1879496/#msg1879496
  regarding warnings one might see, and why those are spurious warnings for these files.

• 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]

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

[MDM]

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

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

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]

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]

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]

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]

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:

• Rather than "Request Edit Access" to the file on Google Drive - which, if granted, would allow one to edit the same file that everyone else sees, so it will not be granted ;)
  - After opening the file on Google Drive (link above), click File>Download>Microsoft Excel (xls)
  Then, either open in Excel immediately, edit, and later save from Excel to your computer, or save immediately to your computer and later open and edit that file with Excel.
  
• See
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg2138684/#msg2138684 and
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1879496/#msg1879496
  regarding warnings one might see, and why those are spurious warnings for these files.

• 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]

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]

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]

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]

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]

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]

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.

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.

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.

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. ;)

[dandarc]

Posting a bit late but thank you again for the best tax estimator out there - once again matched the commercial software exactly for 2023 (even with my mods for my specific self-employed situation). And already know the range is likely $7500 to $16000 for 2024 for federal income tax.

[gworona]

Suggestions:
1. put a link to this thread at the top of the Instructions tab.  I spent a some time trying to figure out where the heck I got this wonderful tool and where to get the most recent version.
2. make a version history tab.  Digging through the forum thread to see what changed in the most recent versions is a pain.

Otherwise, and besides my quibbling, this is a fantastic tool.  Thank you very much.

[MDM]

Suggestions:
1. put a link to this thread at the top of the Instructions tab.  I spent a some time trying to figure out where the heck I got this wonderful tool and where to get the most recent version.

Done! - in the version that will be released next, whenever that happens.

2. make a version history tab.  Digging through the forum thread to see what changed in the most recent versions is a pain.

Good idea, again starting with the next release.

Otherwise, and besides my quibbling, this is a fantastic tool.  Thank you very much.

You are welcome.  And thanks for the perspectives above - always good to hear what fresh eyes see.

[arkcom]

The caps on APTC repayment make for a really perverse tax rate graph.  It seems like the "optimal" tax strategy would be to estimate 133% FPL to maximize the credit and then in December tax gain harvest or convert ira up to 399% to take advantage of the repayment cap.  I don't know how many years the IRS would allow it, if they cared at all.

[gworona]

That is exactly what I plan to do.  Except to only go up to 397%.  You don't want to go over.  It's quite a cliff.

[ktsten]

First time poster (hopefully I'm posting in the correct place).  Discovered the CashFlow .xls today & I'm THRILLED.  Looking to RE 1/1/2025 & this is just the tool we needed to support our planning.

I am confused with inputs for B24 vs B25.  B24 prompts 'tax-exempt interest/dividends' while B25 prompts 'qualified dividends'.  What kinds of 'dividends' should be input in B24?  I'm assuming these should NOT be 1099-Div box 1b (that goes in B25). 

I appreciate this may be a basic question - the tool is so robust I want to be sure I'm leveraging it's full power!

[MDM]

ktsten, welcome to the forum.

First time poster (hopefully I'm posting in the correct place).  Discovered the CashFlow .xls today & I'm THRILLED.  Looking to RE 1/1/2025 & this is just the tool we needed to support our planning.

I am confused with inputs for B24 vs B25.  B24 prompts 'tax-exempt interest/dividends' while B25 prompts 'qualified dividends'.  What kinds of 'dividends' should be input in B24?  I'm assuming these should NOT be 1099-Div box 1b (that goes in B25). 

I appreciate this may be a basic question - the tool is so robust I want to be sure I'm leveraging it's full power!

Maybe just a typo, but you should be making the entries in D24 and D25: the cells with a green background.  Overwriting formulas with numbers may give incorrect results. ;)

The non-qualified dividend amount is the difference (if any) between boxes 1a and 1b on a 1099-DIV.

So far so good?

[ktsten]

ktsten, welcome to the forum.

First time poster (hopefully I'm posting in the correct place).  Discovered the CashFlow .xls today & I'm THRILLED.  Looking to RE 1/1/2025 & this is just the tool we needed to support our planning.

I am confused with inputs for B24 vs B25.  B24 prompts 'tax-exempt interest/dividends' while B25 prompts 'qualified dividends'.  What kinds of 'dividends' should be input in B24?  I'm assuming these should NOT be 1099-Div box 1b (that goes in B25). 

I appreciate this may be a basic question - the tool is so robust I want to be sure I'm leveraging it's full power!

Maybe just a typo, but you should be making the entries in D24 and D25: the cells with a green background.  Overwriting formulas with numbers may give incorrect results. ;)
The non-qualified dividend amount is the difference (if any) between boxes 1a and 1b on a 1099-DIV.

So far so good?

Yes typo (my bad) - I meant D24 & D25 above

I'm tracking on non-qualified dividend and have that entered in D23 (1099-DIV difference between 1a and 1b).  Then D25 is qualified dividend (1b from 1099-DIV).  I'm stumped on what the heck goes in D24?

[MDM]

I'm tracking on non-qualified dividend and have that entered in D23 (1099-DIV difference between 1a and 1b).

To which you add any interest (checking, savings, bonds, etc.). 

Then D25 is qualified dividend (1b from 1099-DIV).  I'm stumped on what the heck goes in D24?

Tax-exempt interest/dividends.  You may not have any.  Think Twice Before Buying Tax-Free Municipal Bonds has some information.

[Not Sure]

MDM, I just wanted to say thanks for sharing this spreadsheet!

I built my own tax calculator in a custom spreadsheet, which largely does other things, and find it very helpful to check my results against your much more complete model!

Cheers!

[gworona]

I believe that the Marginal /Cumulative Tax Rate chart overstates the cumulative rate.  The cumulative total tax rate shown in E67 is not the same as the cumulative rate calculated in T84 and below which is then charted as the blue line.  Shouldn't the denominator of T84 be $D$52+O84 (AGI plus the additional tIRA withdrawal or whatever you are charting for)?

[MDM]

I believe that the Marginal /Cumulative Tax Rate chart overstates the cumulative rate.  The cumulative total tax rate shown in E67 is not the same as the cumulative rate calculated in T84 and below which is then charted as the blue line.  Shouldn't the denominator of T84 be $D$52+O84 (AGI plus the additional tIRA withdrawal or whatever you are charting for)?

Good question!

E67 is one version of an "effective" tax rate, dividing all taxes (federal and state income, SS and Medicare) by AGI.  It's a calculable number, but not often useful. 

Marginal tax rates are the useful ones when making choices, and those rates should have only the amount of the choice in the denominator.  One should re-evaluate whenever the marginal rate gets "worse" for the choice in question.  E.g., when it increases due to Roth conversions, or decreases due to traditional contributions. 

The default "cumulative" curve can be somewhat misleading in that case (because it "hides" the cost of the worse rates by averaging them in with the "better" rates), unless you reset that calculation by putting the amount "up to the point where it gets worse" in P84. 

The cumulative curve is useful when "at first it gets worse but then it gets better," such as saving a low marginal rate to reach the earned income or saver's credit higher rate on traditional contributions, or going through the 27% marginal tax rate on conversions when qualified dividends are also present.  Worth pushing through the Social Security hump and/or IRMAA cliffs? discusses another good example of this.

Marginal Vs Effective Tax Rates And When To Use Each is also a good reference. 

[gworona]

Ah.  So E67 is the effective rate, the gray line is the marginal rate of an additional dollar at that choice amount, and the blue line is the marginal rate of the entire choice amount at that point (sort of, Cumulative Marginal).

[MDM]

Version 22.18 (2022/2023/2024)

• Instructions
  Added link to https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/
• Calculations
  Added annual tIRA conversion/withdrawal to the future income section.
  Default tax year (cell Calculation!R2) now 2024
  Improved part-year ACA calculations
• State Tax
  Updates to CA, CT, IA, MO, NJ, and NY state tax calculations.
• SocialSecurity
  Added Windfall Elimination Provision (WEP) calculations.
• Tax Code
  Updated IRMAA tier estimates for 2025 (based on TY2023) and 2026 (based on TY2024)
• Versions
  Added this tab.

Nothing major in this update, but a bunch of minor items that could be significant to anyone in that situation, e.g., Windfall Elimination Provision for SS, or various state tax items, etc. 

The starting point for state tax numbers is 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.  Thanks again to those who have made such suggestions - and I'm assuming the Iowa change mentioned in that thread is correct (enough).

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:

• Rather than "Request Edit Access" to the file on Google Drive - which, if granted, would allow one to edit the same file that everyone else sees, so it will not be granted ;)
  - After opening the file on Google Drive (link above), click File>Download>Microsoft Excel (xls)
  Then, either open in Excel immediately, edit, and later save from Excel to your computer, or save immediately to your computer and later open and edit that file with Excel.
  
• See
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg2138684/#msg2138684 and
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1879496/#msg1879496
  regarding warnings one might see, and why those are spurious warnings for these files.

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