Case Study Spreadsheet updates

[superman9379]

Hopefully the IRS releases/finalizes 2024 numbers soon!

[MDM]

Version 22.14 (2022/2023/2024)

• Out with 2021 and in with 2024 tax year calculations!
• Provided separate entries for each spouse's pension and IRA/401k/403b/etc. withdrawals
• Small documentation changes on the 'Instructions' tab
• Increased default medical expense range on the 'HDHP Analysis' tab

Although I haven't seen a few 2024 numbers, either from an IRS source or the same numbers from multiple non-IRS sources (e.g., QBI thresholds and Pub. 15-T's withholding), the vast majority are available, so no time like the present to roll this out.  Most are from https://www.irs.gov/pub/irs-drop/n-23-75.pdf, with others from various probably-good-but-not-official-IRS sources.  As these become known, yellow cell colors on the 'Tax Code' tab will change to brown in future versions.

The default remains tax year 2023, but for early planners changing cell Calculations!R2 to 2024 will use those values. 

The sharp-eyed might notice that 2026 is also an option, but while those might get you in the ballpark, it would likely be an obstructed view in the upper bleachers.  In other words, don't rely on those at all for now.

Another set of numbers that won't be available any time soon are the IRMAA tiers that will apply in 2025 and 2026 but be based on 2023 and 2024 income.  Harry Sit's https://thefinancebuff.com/medicare-irmaa-income-brackets.html is a good place to get educated guesses about those as time goes by.  Current values in the CSS are "not unreasonable".

Some states exempt retirement income from pensions, IRAs, etc., from tax.  In some of those states, the exemption may be limited for the total MFJ income, but in other states limits are imposed per person.  Allowing per person inputs allows the per person exemption limits to be used correctly where applicable.

The per person inputs for IRA withdrawals means the default for the marginal rate chart x-axis changed from D31 (total amount) to B31 (one person's amount).  If you aren't dealing with per person state tax exemptions, whether the amount comes from one person or two doesn't matter.

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.

[MDM]

Thanks for your hard work MDM!

I've been a bit confused for a while about how to enter HSA contributions--specifically, how to differentiate my pretax contributions out of my salary from my employer contributions that are separate from my salary. I've been adding the employer contributions to my gross income and putting the combined HSA contribution into B7. Is there a better way to do this?

You're welcome!

There may be a better way, but that way works and is how I'd do it: you get the correct FICA and W-2 box 1 results, and those are what matter most.

[MDM]

Version 22.15 (2022/2023/2024)

• Implement https://www.irs.gov/pub/irs-drop/rp-23-34.pdf
• Fix typos

The slightly more comprehensive IRS update now available made some $50-$100 differences vs. what some non-IRS sources had estimated.  The remaining unknowns for 2024 are for withholding (Pub. 15-T is still for 2023 in Draft Tax Forms | Internal Revenue Service), and the IRMAA tiers covered in the previous update.

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.

[gbronc]

Thanks for getting this out so quickly with the new brackets.

Getting this:
"Enter filing status in cell G2" when setting R2 to 2026. All filing statuses in G2 fail with this error.

This worked fine on other releases and works fine with any other year set.

[MDM]

Thanks for getting this out so quickly with the new brackets.

Getting this:
"Enter filing status in cell G2" when setting R2 to 2026. All filing statuses in G2 fail with this error.

This worked fine on other releases and works fine with any other year set.

That message (in cell C63 - correct?) is a bit of a catch-all.  One thing that can cause it is using the space bar to delete a cell entry somewhere, instead of the Delete key.  Any chance that is the cause here?

[gbronc]

That message (in cell C63 - correct?) is a bit of a catch-all.  One thing that can cause it is using the space bar to delete a cell entry somewhere, instead of the Delete key.  Any chance that is the cause here?

OK, good to know. Re-downloaded and re-entered data and no longer seeing the error, so I'm guessing there was a space somewhere.

Thanks for the quick response.

[gbronc]

That message (in cell C63 - correct?) is a bit of a catch-all.  One thing that can cause it is using the space bar to delete a cell entry somewhere, instead of the Delete key.  Any chance that is the cause here?

I spoke too soon, it looks like the error happens when:
R2 = 2026
G9 is a number greater than 64

Doing this in XLSM or XLS file.

[MDM]

That message (in cell C63 - correct?) is a bit of a catch-all.  One thing that can cause it is using the space bar to delete a cell entry somewhere, instead of the Delete key.  Any chance that is the cause here?

I spoke too soon, it looks like the error happens when:
R2 = 2026
G9 is a number greater than 64

Doing this in XLSM or XLS file.

Ahh, must have been a Freudian slip to prevent at least some people from trying to use 2026 numbers. ;)

As stated in the 7-Nov update, "The sharp-eyed might notice that 2026 is also an option, but while those might get you in the ballpark, it would likely be an obstructed view in the upper bleachers.  In other words, don't rely on those at all for now."

But if you insist, :)
Put 2026 into cell 'Tax Code'!E241, and whatever number you choose into cell 'Tax Code'!E242.  I might use the formula =D242+(D242-C242) or something like that when a new release comes out.

Kidding aside, thanks for note!

[secondcor521]

@MDM, any suggestions for how to figure out the rationale for the tax rate graph around Calculations!F83?

I've put in all my numbers already.  I'm trying to dial in my Roth conversion amount for 2023.  I'm sure they are correct, but there are some blips in the graph I'd like to understand.

The best I've come up with so far is fiddling with the Roth conversion amount in Calculations!B31, then scrolling over to look at the tax results and see which numbers change, and then dive down from there.  That's probably the way you expect people to do it, but I'm asking in case there's something I'm missing.

As a side note, Idaho only applies the 5.8% flat tax above $2500 (single) and $5,000 (MFJ).  The $2,500/$5,000 acts like an additional Idaho-only standard deduction.  You probably only have a handful of Idaho filers, but I think it's an easy fix.  Maybe in State Tax!H34 and State Tax!I34?

Thanks for the awesome tool!

[gbronc]

Ahh, must have been a Freudian slip to prevent at least some people from trying to use 2026 numbers. ;)

As stated in the 7-Nov update, "The sharp-eyed might notice that 2026 is also an option, but while those might get you in the ballpark, it would likely be an obstructed view in the upper bleachers.  In other words, don't rely on those at all for now."

But if you insist, :)
Put 2026 into cell 'Tax Code'!E241, and whatever number you choose into cell 'Tax Code'!E242.  I might use the formula =D242+(D242-C242) or something like that when a new release comes out.

Kidding aside, thanks for note!

OK, missed that comment from you. I'm using this for Roth Conversion comparison modeling, so I really need a 2042 year, not 2026. :)
So, I am definitely just looking for a very high level view and figure that 2026 rates and results are probably closer than current 2024, but who knows.

Your workaround worked for me, but I'll take the results with a grain of salt.

[MDM]

I'm using this for Roth Conversion comparison modeling, so I really need a 2042 year, not 2026. :)
So, I am definitely just looking for a very high level view and figure that 2026 rates and results are probably closer than current 2024, but who knows.

Projections ~20 years out are really anyone's guess, but regardless of the 2024 election results the betting odds might favor "no change for those earning <$400K/yr" in the next few years because that's a more or less common theme between both Democrats and Republicans.

If that holds true, the method for back-of-the-envelope calculations described in that post is probably your best bet.  Good luck!

[MDM]

@MDM, any suggestions for how to figure out the rationale for the tax rate graph around Calculations!F83?

I've put in all my numbers already.  I'm trying to dial in my Roth conversion amount for 2023.  I'm sure they are correct, but there are some blips in the graph I'd like to understand.

The best I've come up with so far is fiddling with the Roth conversion amount in Calculations!B31, then scrolling over to look at the tax results and see which numbers change, and then dive down from there.  That's probably the way you expect people to do it, but I'm asking in case there's something I'm missing.

I wish there were some AI tool that would do this (and generate a really nice annotated chart like someone at Bogleheads did ), because I often have the same problem ;) and yours is the same approach I use. :)

As a side note, Idaho only applies the 5.8% flat tax above $2500 (single) and $5,000 (MFJ).  The $2,500/$5,000 acts like an additional Idaho-only standard deduction.  You probably only have a handful of Idaho filers, but I think it's an easy fix.  Maybe in State Tax!H34 and State Tax!I34?

Thanks for the feedback and I'll take a look!

[secondcor521]

@MDM, any suggestions for how to figure out the rationale for the tax rate graph around Calculations!F83?

I've put in all my numbers already.  I'm trying to dial in my Roth conversion amount for 2023.  I'm sure they are correct, but there are some blips in the graph I'd like to understand.

The best I've come up with so far is fiddling with the Roth conversion amount in Calculations!B31, then scrolling over to look at the tax results and see which numbers change, and then dive down from there.  That's probably the way you expect people to do it, but I'm asking in case there's something I'm missing.

I wish there were some AI tool that would do this (and generate a really nice annotated chart like someone at Bogleheads did ), because I often have the same problem ;) and yours is the same approach I use. :)

As a side note, Idaho only applies the 5.8% flat tax above $2500 (single) and $5,000 (MFJ).  The $2,500/$5,000 acts like an additional Idaho-only standard deduction.  You probably only have a handful of Idaho filers, but I think it's an easy fix.  Maybe in State Tax!H34 and State Tax!I34?

Thanks for the feedback and I'll take a look!

Thanks x 2!

[mmm999]

Roth Conversion and  ACA Premium Tax Credit hack/modification
Sorry if this topic has been discussed before, if so please point me to a link.  Thank you. 

Goal: I would like to see the marginal "tax" effect of the ACA premium phase out due to Roth conversion using the CSS.  There was a discussion on the effect here https://forum.mrmoneymustache.com/post-fire/aca-killing-traditional-to-roth-conversion-ladder/msg2466197/#msg2466197 by @seattlecyclone and in his/her blog here https://seattlecyclone.com/aca-premium-tax-credits-2021-edition/.

CSS Hack/Modification Steps:

• Determine the Second lowest cost Silver plan (SLCSP) in your area.
• In cell B114, enter the monthly enrollment premium.
• In cell B115, enter the negative number of cell B114.  For example, if B114 = $1500 then B115 = -$1500.
• In cell AD107, change MAX(0,MIN(AD105,AD106)) to MAX(0,MAX(AD105,AD106))
• In cell AE100, enter value shown in AD100.  For example, if AD100 = $18000 then AE100 = $18000.

These modifications seem to yield the marginal "tax" effect caused by ACA PTC Phase out as shown in figures of the above links.  I only test this hack/modification on a clean Cashflow-2021-2022-2023.xls. 

[MDM]

Roth Conversion and  ACA Premium Tax Credit hack/modification
Sorry if this topic has been discussed before, if so please point me to a link.  Thank you. 

Goal: I would like to see the marginal "tax" effect of the ACA premium phase out due to Roth conversion using the CSS.  There was a discussion on the effect here https://forum.mrmoneymustache.com/post-fire/aca-killing-traditional-to-roth-conversion-ladder/msg2466197/#msg2466197 by @seattlecyclone and in his/her blog here https://seattlecyclone.com/aca-premium-tax-credits-2021-edition/.

mmm999, thanks for asking.  Perhaps the best write-up on this is Roth Conversion and Capital Gains On ACA Health Insurance.

CSS Hack/Modification Steps:

• Determine the Second lowest cost Silver plan (SLCSP) in your area.
• In cell B114, enter the monthly enrollment premium.
• In cell B115, enter the negative number of cell B114.  For example, if B114 = $1500 then B115 = -$1500.
• In cell AD107, change MAX(0,MIN(AD105,AD106)) to MAX(0,MAX(AD105,AD106))
• In cell AE100, enter value shown in AD100.  For example, if AD100 = $18000 then AE100 = $18000.

Yes for B114.
B115 should be the negative of the Advance Premium Tax Credit (APTC).  The APTC may or may not be the same as the enrollment premium.
AD107 looks correct as is: if you owe more than the limit, you are liable only for the limit.
AE100 should be whatever your SLCSP is.  That may or may not be the same as the enrollment premium.

Does following Harry Sit's "how to" (the write-up linked above) work for you?

[secondcor521]

Roth Conversion and  ACA Premium Tax Credit hack/modification
Sorry if this topic has been discussed before, if so please point me to a link.  Thank you. 

Goal: I would like to see the marginal "tax" effect of the ACA premium phase out due to Roth conversion using the CSS.  There was a discussion on the effect here https://forum.mrmoneymustache.com/post-fire/aca-killing-traditional-to-roth-conversion-ladder/msg2466197/#msg2466197 by @seattlecyclone and in his/her blog here https://seattlecyclone.com/aca-premium-tax-credits-2021-edition/.

CSS Hack/Modification Steps:

• Determine the Second lowest cost Silver plan (SLCSP) in your area.
• In cell B114, enter the monthly enrollment premium.
• In cell B115, enter the negative number of cell B114.  For example, if B114 = $1500 then B115 = -$1500.
• In cell AD107, change MAX(0,MIN(AD105,AD106)) to MAX(0,MAX(AD105,AD106))
• In cell AE100, enter value shown in AD100.  For example, if AD100 = $18000 then AE100 = $18000.

These modifications seem to yield the marginal "tax" effect caused by ACA PTC Phase out as shown in figures of the above links.  I only test this hack/modification on a clean Cashflow-2021-2022-2023.xls.

I'm not sure why you feel this hack is necessary.  (I also don't follow your hack, but @MDM probably will.)

The post you linked back to talks about Roth conversions and the ACA subsidies and the SEHI deduction, all of which can be modeled in the CSS already by using it in the expected / normal fashion.

The CSS includes the effect of the ACA subsidies and SEHI deduction layered on top of any other tax effects that you have going on.  What I (and I would think most people) are interested in is the overall net effect of a Roth conversion with the entire tax picture entered into the CSS.  Said differently, I'm mostly interested in the fact that a $1000 additional Roth conversion increases my federal taxes by, say, 20.17%.  Whether that 20.17% is comprised of ACA subsidy loss, additional ordinary income tax, additional capital gains tax, more SS being taxed, or whatever is of secondary concern.

I am interested in the shape of my graph in the CSS mostly to understand why the various breakpoints (amounts of a Roth conversion which seem optimal) are where they are, that I've entered the data correctly, and that the spreadsheet is working properly.  I don't see a need to recreate the graph in the post you quoted in order to just show the ACA subsidy loss effect by itself (partly because I've already worked through that math and believe and understand it, I guess).

[mmm999]

First, thanks for your comments @MDM and @secondcor521.   I think I need to explain a bit more about my reason for modifying the CSS.  I would like to include ACA premium (that I need to pay) as a tax item so that I could see its effect on the marginal tax plot and allow me to identify optimal Roth conversion amounts (e.g. low marginal tax plateaus).  This is similar seeing the IRMAA spikes when decide on what amount to convert for people with Medicare.  Please correct me but I don't think the current spreadsheet has that feature for people with ACA insurance.

My modification is an attempt to force CSS to report my ACA premium (Enrollment premium - APTC) as an "Excess APTC tax" in cell G19.  This should increase my Net tax (G35) and most importantly the values in cells Q85:Q584 by the correct amounts based on conversion amounts in cells O85:O584.  The marginal tax vs tIRA plot should now include the effect of my ACA premium which btw add roughly 6-18% additional marginal tax according to the links in my original posts.  It is the plot which shows the additional  tax I'm interested in.  Hope I make it clearer on what I would like to accomplish.

@MDM, yes I know about Harry Sit's "how to" and have used it to estimate my taxes.  BTW, Federal Poverty Level table (AC110:AF119) in your latest CSS needs 2023 numbers.  I think it currently has 2022 numbers.  Thank you.

Update:  I would like to generate a plot like this https://www.bogleheads.org/forum/viewtopic.php?p=6960716#p6960716.  However, I don't think adding 8.5% to the plot like what was done here is correct.  The additional tax should vary from 6% to 18% depending on the conversion amounts.

[MDM]

First, thanks for your comments @MDM and @secondcor521.   I think I need to explain a bit more about my reason for modifying the CSS.  I would like to include ACA premium (that I need to pay) as a tax item so that I could see its effect on the marginal tax plot and allow me to identify optimal Roth conversion amounts (e.g. low marginal tax plateaus).  This is similar seeing the IRMAA spikes when decide on what amount to convert for people with Medicare.  Please correct me but I don't think the current spreadsheet has that feature for people with ACA insurance.

The developer of any software is often blind to mistakes that others see easily, so that could be the case here, but I think the current spreadsheet already does exactly what you want.  If you have a set of inputs that shows otherwise I'd be happy to consider that case.  Please let me know the inputs and desired vs. observed output you see.

My modification is an attempt to force CSS to report my ACA premium (Enrollment premium - APTC) as an "Excess APTC tax" in cell G19.  This should increase my Net tax (G35) and most importantly the values in cells Q85:Q584 by the correct amounts based on conversion amounts in cells O85:O584.  The marginal tax vs tIRA plot should now include the effect of my ACA premium which btw add roughly 6-18% additional marginal tax according to the links in my original posts.  It is the plot which shows the additional  tax I'm interested in.  Hope I make it clearer on what I would like to accomplish.

If you want to treat the difference between Gold, Silver, and Bronze plan premiums as a tax, I guess you can do that.  The intent of the CSS is this area is to reproduce Form 8962 calculations so it provides accurate numbers for Line 2 of https://www.irs.gov/pub/irs-pdf/f1040s2.pdf or Line 9 of https://www.irs.gov/pub/irs-pdf/f1040s3.pdf.

yes I know about Harry Sit's "how to" and have used it to estimate my taxes.  BTW, Federal Poverty Level table (AC110:AF119) in your latest CSS needs 2023 numbers.  I think it currently has 2022 numbers.  Thank you.

Harry's article discusses exactly how ACA effects get included in the tax calculation, so I'm not following....

The ACA uses the previous year's poverty level numbers, so using 2022 poverty levels when doing 2023 taxes is correct.  E.g., see For 2022, the 2021 federal poverty lines are used for this purpose and are shown below.

[secondcor521]

Please correct me but I don't think the current spreadsheet has that feature for people with ACA insurance.

I believe you're wrong (ETA:  MDM seems to agree with me that you're wrong - see above - but he is saying it less bluntly) and it does have that feature and therefore your modifications are not required.

What you may be missing - conceptually, at least - is that the ACA subsidy loss effect is happening at the same time that the ordinary tax brackets are happening, and as the capital gains tax brackets are happening, and any tax credits are happening.  So the graph that you see in the CSS is combining all of those effects at once.  You won't get a graph like in seattlecyclone's blog because that separates out the ACA subsidy loss from all of the other effects.

If you put in your tax items and use the spreadsheet as designed, the ACA subsidy loss effect will be embedded in the graph that you see.  You may just not be able to see it if your other tax items overwhelm or obscure it.

But that should be what you want.  You shouldn't make a Roth conversion target *just* based on the ACA subsidy loss alone; if you do you might be ignoring some other factor that makes a larger or smaller Roth conversion more optimal.  You should make it based on the totality of your income tax picture, which is what the CSS and the graph do for you.

Also, whatever graph you get, it probably won't look like others you see.  The "+8.5%" for that BH graph may have been appropriate for that user's tax situation - I didn't really check it that closely - but the shape of your graph will be different because your tax situation is likely different.

HTH.

[MDM]

Update:  I would like to generate a plot like this https://www.bogleheads.org/forum/viewtopic.php?p=6960716#p6960716.  However, I don't think adding 8.5% to the plot like what was done here is correct.  The additional tax should vary from 6% to 18% depending on the conversion amounts.

It appears that plot was generated using the CSS (Bogleheads call it Personal finance toolbox) as is, except for the annotations in red, green, and purple.

[mmm999]

Thanks again for your comments.  Since you are the CSS experts (I'm not) and both of you are telling me that I'm wrong then I need to go back and study the spreadsheet more closely.  However, I truly appreciate your time trying to explain things to me.

The ACA uses the previous year's poverty level numbers, so using 2022 poverty levels when doing 2023 taxes is correct.  E.g., see For 2022, the 2021 federal poverty lines are used for this purpose and are shown below.

Oh, I did not know that, thanks.  I clicked the link https://aspe.hhs.gov/poverty-guidelines provided in the spreadsheet, saw the 2023 numbers, and thought they should be used for 2023 tax.

[MDM]

The ACA uses the previous year's poverty level numbers, so using 2022 poverty levels when doing 2023 taxes is correct.  E.g., see For 2022, the 2021 federal poverty lines are used for this purpose and are shown below.

Oh, I did not know that, thanks.  I clicked the link https://aspe.hhs.gov/poverty-guidelines provided in the spreadsheet, saw the 2023 numbers, and thought they should be used for 2023 tax.

Good point.  Now that inflation-affected tax code numbers are updated on the 'Tax Code' tab, removing at least some of those links from the 'Calculations' tab is probably a good idea.  E.g., see 2023 2024 Federal Poverty Levels (FPL) for ACA Health Insurance that is linked from cell 'Tax Code'!D200.

[secondcor521]

Thanks again for your comments.  Since you are the CSS experts (I'm not) and both of you are telling me that I'm wrong then I need to go back and study the spreadsheet more closely.  However, I truly appreciate your time trying to explain things to me.

I'm not a CSS expert.  @MDM authored it so definitely an expert there.

Maybe the simplest and most direct way to articulate it to you is below.  I don't have Excel on my PC so I'm basing the following on a Google sheets view of the CSS, but I think it's right (@MDM can confirm or correct):

The Y axis on the graph is, by default, D67.
Cell D67 is total tax, which includes D63.
D63 is federal tax, which is the same as G34.

...

And now we take a diversion, because I more carefully read what you wrote earlier:

My modification is an attempt to force CSS to report my ACA premium (Enrollment premium - APTC) as an "Excess APTC tax" in cell G19.  This should increase my Net tax (G35) and most importantly the values in cells Q85:Q584 by the correct amounts based on conversion amounts in cells O85:O584.  The marginal tax vs tIRA plot should now include the effect of my ACA premium which btw add roughly 6-18% additional marginal tax according to the links in my original posts.  It is the plot which shows the additional  tax I'm interested in.  Hope I make it clearer on what I would like to accomplish.

The above is conceptually inaccurate.  From a taxation point of view, what varies by the 6-18% that you allude to is not your subsidized premium (Health premium minus PTC), but the subsidy itself (PTC).  Your subsidized premium (Health premium minus PTC) is not a tax.  It's what you're paying for your health insurance.

Note I wrote "PTC" in the previous sentences, not APTC.  Because your income estimate is used to calculate APTC and your actual income is used to calculate PTC, when they differ, the adjustment is either an additional amount you owe or an additional refund to you.  The additional amount you owe is listed in the spreadsheet as "Excess APTC tax" in F19:G19.  The additional refund to you is listed in the spreadsheet as "Net PTC" in F33:G33.

...

Now back to the original train of thought.

Both G19 and G33 should be incorporated into G34.

So the adjustment to get from APTC to PTC in G19/G33 goes to G34, which goes to D63, which goes to D67, which goes to the graph.

[mmm999]

@MDM and @secondcor521
I might understand the reason why I didn't see the marginal "tax" profile shown in https://seattlecyclone.com/aca-premium-tax-credits-2021-edition/, the red line in the 2nd plot ACA PTC Phase Out.  It was because I put in a value for the APTC cell B115.  Doing so will mask out the profile because the excess APTC tax, G19, is capped by values in cells AH92:AH94.

In other words, to see it, I must leave the APTC cell, B115, empty and only fill in values the enrollment premium (B114) and the SLCSP (AE100).  I was able to see the profile I need without the hack in my original post.

Thank you for your comments.  They made me examine the CSS more closely.

[mmm999]

This is what I get with APTC left empty.  Red arrow indicates the profile I have looked for

[MDM]

This is what I get with APTC left empty.  Red arrow indicates the profile I have looked for
...

The large negative marginal rate spike (increased income leading to lower tax) is due to crossing the 100% FPL amount and having zero APTC.  See Household income below 100% of the federal poverty line: if you have APTC then you can get PTC even if MAGI<FPL (sorry for the acronym salad...) but without APTC then you get no PTC if MAGI<FPL.

Probably best to enter your real situation if you want to use the results for real purposes. :)

[secondcor521]

I was able to see the profile I need without the hack in my original post.

After all this back and forth, can you explain why you see the need for this?

As I have been trying to say, it's pretty much useless for any Roth conversion (or any tax planning) decisions.  (By "it", I'm referring to "the profile [you] need", not the CSS.)

[mmm999]

After all this back and forth, can you explain why you see the need for this?

As I have been trying to say, it's pretty much useless for any Roth conversion (or any tax planning) decisions.  (By "it", I'm referring to "the profile [you] need", not the CSS.)

You and @MDM are absolutely right about the usefulness of the profile in real life.  It was just for me to learn more about the CSS capabilities.   After reading the blog in my original post, I tried to create the profile in the CSS.  It should be a simple exercise (right?) but I failed to see it.  I was surprised and began to trace cell by cell trying to understand the "problem".  I found that if I removed the caps on excess APTC tax I could see the profile (step 4 in my hack).  Thinking that I "discovered something new" I posted my modifications here and you saw the rest. 

When I learn something new, I like to start with something simple first and slowly add in more complexity.  In this case of course, it was Roth conversion vs ACA Premium.  CSS examples I could find online had too many things together (pension, capital gain, dividends etc..) which effectively compressed the profile to the point that it could not be easily seen.  So my simple unrealistic case had nothing but roth conversion and still I fumbled.  Thanks to your comments I finally realized what was going on.  In any case, I did learn something new with this "useless" profile and have even more appreciation of  the tool and its creator.

[mdearing1684]

I am new to your Case Study Spreadsheet and am having trouble "unlockingit" for data enty. I keep getting this message when I try to enter data "The cell or cells you're trying to change is in a protected spreadsheet.  To make achane, unprotect the sheet.  You might be requested for a password."
How do I work with this?

[MDM]

I am new to your Case Study Spreadsheet and am having trouble "unlockingit" for data enty. I keep getting this message when I try to enter data "The cell or cells you're trying to change is in a protected spreadsheet.  To make achane, unprotect the sheet.  You might be requested for a password."
How do I work with this?

See rows 1-36 on the 'Instructions' tab.  Does that help?

What cell are you trying to change (and why? if it might not be obvious)?

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

[jbfishing]

Hello,
I appreciate all the work maintaining and updating this spreadsheet over the years. It has been a great help to me. I have 2 questions:

1. For child tax credits, if I choose 1 qualifying child the non-refundable and refundable amounts add to $2500. If I choose 2 children they add to $4000. Is this right? If yes, what's going on here that 1 child is $2500 and 2 are $2000 per child?

2. Is there anywhere to account for the Energy Efficient Home Improvement Credit?

Thank you,
Jeff

[MDM]

Hello,
I appreciate all the work maintaining and updating this spreadsheet over the years. It has been a great help to me. I have 2 questions:

1. For child tax credits, if I choose 1 qualifying child the non-refundable and refundable amounts add to $2500. If I choose 2 children they add to $4000. Is this right? If yes, what's going on here that 1 child is $2500 and 2 are $2000 per child?

A qualifying child gets $2000 (with a $1600 refundable limit), an "other dependent" gets $500 non-refundable.  If you have two dependents, but only one under age 17, that's the $2500.  Two dependents both under age 17 gets $4000 (but limited to $3200 refundable).

2. Is there anywhere to account for the Energy Efficient Home Improvement Credit?

Cell Calculations!G25.  You'll have to do the "multiply by 30%", etc., calculations shown on https://www.irs.gov/pub/irs-dft/f5695--dft.pdf and enter the bottom line.

[jbfishing]

Thanks MDM for the quick response and explanation. I appreciate it.

[RWTL]

MDM thanks again for keeping this updated.  I've pulled the new version for the last three years and use it monthly to help with my taxes.  I appreciate your contribution to the forum by keeping this active!

[alcon835]

I agree with all the above, I only use this once or twice a year to track my tax situation, but it is insanely helpful and right on the nose every time. Thank you so much for this!

[EngineerYogi]

I used the sheet back in 2018, I'm looking to do an updated one. I downloaded the latest version and don't see where I put the deduction from pay for life insurance?

Also, how do I account for non-taxable income? My husband is military and a large chunk of his pay is not taxable.

[MDM]

I used the sheet back in 2018, I'm looking to do an updated one. I downloaded the latest version and don't see where I put the deduction from pay for life insurance?

Also, how do I account for non-taxable income? My husband is military and a large chunk of his pay is not taxable.

If the life insurance is pre-FICA, you could use "Pretax Commuter costs".  If it is post-FICA but pre-W2, "Pre-tax pension contribution" could work.

B72 is for "Other Untaxed Income" - note that it takes a monthly amount.

Hope it works for you - feel free to PM or post back with questions.

[EngineerYogi]

I used the sheet back in 2018, I'm looking to do an updated one. I downloaded the latest version and don't see where I put the deduction from pay for life insurance?

Also, how do I account for non-taxable income? My husband is military and a large chunk of his pay is not taxable.

If the life insurance is pre-FICA, you could use "Pretax Commuter costs".  If it is post-FICA but pre-W2, "Pre-tax pension contribution" could work.

B72 is for "Other Untaxed Income" - note that it takes a monthly amount.

Hope it works for you - feel free to PM or post back with questions.

Thanks! I was able to manipulate it a bit for the untaxed income, I found his federal taxable income and put that in the gross box, then took the non-taxable amount and divided it by 12 for the untaxed income box. I had to make a tweak in the state tax sheet since none of his income is taxed by state I just edited the AGI box to include only the value from my income. And I have both types of life insurance (company pays some that I have to pay taxes on, plus I purchase additional) so I put them in the respective boxes you recommended.

[murrays007]

First of all, thanks very much for a wonderful tool!  I’ve only begun to scratch the surface on everything this provides!

I’ve uncovered a slight error on the Personal Finance Toolbox under the “Misc. calcs” tab, cell B165, Marginal tax rate of conversion when funds from a taxable account are used to pay for taxes due to conversion.

I posted on the Bogleheads forum regarding this topic: https://www.bogleheads.org/forum/viewtopic.php?p=7664389#p7664389

I’ll provide a short description:

To calculate the marginal tax rate, you divide the (change in tax) by (change in income).  The formula in B165 correctly calculates the change in tax when selling funds in a taxable account to pay for the taxes resulting from the tIRA transfer, but it does not account for the additional income resulting from the sale of taxable funds. 

I’ll provide a quick example:

In my case, I pay any expenses not covered with interest and dividends by selling funds in my taxable account. Here's my assumptions:
- 24% federal only tax bracket
- NIIT of 3.8% and 50% cost basis/current value in the taxable account so the marginal tax becomes (15% + 3.8%)*50% = 9.4% on any additional fund sales,
- No other income, no SS, no pension, etc.
- $10k tIRA to Roth conversion

If I pay for the conversion with simple withholding, the marginal rate is a simple 24% or 2400/10000.

If I choose to pay the taxes by liquidating a taxable asset with 50% cost basis, I need to sell $2649 of the asset resulting in $2649 additional income and $249 in taxes.

Cell B165 currently calculates the marginal rate at 26.49% using the numbers above when the actual marginal rate should be (2400+249)/(10000+2649) = 20.94%

A formula that yields the correct marginal rate calculation is:
=B162/(1-B163*(1-B164))/(1+B162/(1-B163*(1-B164)))
(note, I didn’t take time to algebraically simplify the formula)

Thanks again for the spreadsheet and attention to this error.

[MDM]

Cell B165 currently calculates the marginal rate at 26.49% using the numbers above....

Welcome to the forum and thank you for your interest!

That calculation, however, is correct.  If you are paying extra tax to pay the conversion tax, the tax rate on your conversion must be higher than if you aren't paying extra tax. 

You can't make the conversion better by paying extra tax.  That would be a neat trick if you could, but you can't.

[murrays007]

Cell B165 currently calculates the marginal rate at 26.49% using the numbers above....

Welcome to the forum and thank you for your interest!

That calculation, however, is correct.  If you are paying extra tax to pay the conversion tax, the tax rate on your conversion must be higher than if you aren't paying extra tax. 

You can't make the conversion better by paying extra tax.  That would be a neat trick if you could, but you can't.

No disrespect, but I have to disagree.  You're paying additional tax, but you're also incurring additional income...at least the IRS tells me I am!  And you're making the conversion better by depositing an extra $2400 into the Roth at a relatively small additional cost of $249.  $10k in the Roth is clearly better than $7600.

Consider two independent events:
1) A simple tIRA to Roth conversion per my example above.  $10k converted, $2400 withheld for taxes, $7600 goes into the Roth.  24% marginal tax rate.

2) Let's assume that a new tax law allows retirees to deposit up to $2400 of unearned income into a Roth.  I liquidate $2649 of a 50% cost basis investment, pay $249 in taxes and $2400 goes into the Roth.  Marginal tax rate = 249/2649 = 9.4%.

Please explain to me how combining these two events nets a marginal tax rate of 26.49%?

[MDM]

Cell B165 currently calculates the marginal rate at 26.49% using the numbers above....

Welcome to the forum and thank you for your interest!

That calculation, however, is correct.  If you are paying extra tax to pay the conversion tax, the tax rate on your conversion must be higher than if you aren't paying extra tax. 

You can't make the conversion better by paying extra tax.  That would be a neat trick if you could, but you can't.

No disrespect, but I have to disagree.  You're paying additional tax, but you're also incurring additional income...at least the IRS tells me I am!  And you're making the conversion better by depositing an extra $2400 into the Roth at a relatively small additional cost of $249.  $10k in the Roth is clearly better than $7600.

Sure, but you can put $10K into the Roth by not withholding anything.  You can then pay $2400 tax or, in your example, $2649 tax.  Paying $2400 tax is better.

2) Let's assume that a new tax law allows retirees to deposit up to $2400 of unearned income into a Roth.  I liquidate $2649 of a 50% cost basis investment, pay $249 in taxes and $2400 goes into the Roth.  Marginal tax rate = 249/2649 = 9.4%.

I think I get what you are trying to do, but see above.  Paying $2400 is better than paying $2649, so it makes sense that the marginal rate comparison, 24% vs. 26.49%, is consistent with that.

[murrays007]

Sure, but you can put $10K into the Roth by not withholding anything.  You can then pay $2400 tax or, in your example, $2649 tax.  Paying $2400 tax is better.

Of course, $2400 is better than $2649, but now you've added another $2400 to the denominator of the marginal tax calculation.  In that case, the marginal tax rate should be 2400/(10000+2400) = 19.35%  19.35% is indeed better than 20.94%

2) Let's assume that a new tax law allows retirees to deposit up to $2400 of unearned income into a Roth.  I liquidate $2649 of a 50% cost basis investment, pay $249 in taxes and $2400 goes into the Roth.  Marginal tax rate = 249/2649 = 9.4%.

I think I get what you are trying to do, but see above.  Paying $2400 is better than paying $2649, so it makes sense that the marginal rate comparison, 24% vs. 26.49%, is consistent with that.

See above. What is not consistent is paying the $2400 tax bill from another tax free source doesn't appear any better than withholding the tax from the conversion amount.

Consider this article from Vanguard regarding Breakeven Tax Rate:

How to lower the BETR

Even better: The BETR is not a static number. There are investor choices that can lower the rate, and this, in effect, expands the pool of those who could ultimately benefit from the long-term tax advantages of a Roth conversion. Investors can:

Pay conversion taxes from a taxable account. Tapping into a separate cache of cash allows the full value of the IRA to move to a tax-advantaged account. The more tax-inefficient the account used to pay the conversion tax, the lower the BETR (and the greater the benefit of converting).

If I enter a 0% cost basis, the BETR (cell B166) goes above 24% 20 years later even with a "tax drag" from taxes on dividends.

Could you explain this?  I don't see how a higher future withdrawl tax rate could make a Roth conversion a bad idea when paying taxes from a taxable source which has the same tax rates in the future, or am I misunderstanding the BETR terminology?

[MDM]

Of course, $2400 is better than $2649, but now you've added another $2400 to the denominator of the marginal tax calculation.

I think you are getting hung up on the definition of the denominator in the marginal tax rate calculation.  Yes, the IRS will count the sale of appreciated securities as income.  No, in this case you shouldn't add that to the denominator.  To evaluate the marginal tax rate of the conversion, you want the tax divided by the amount of the conversion.  Period.

Whether you pay the $2400 from the conversion amount or from another tax-free source, the marginal tax rate is the same: 24%.  Nothing inconsistent about that.

Selling a 0% cost basis investment is a pretty terrible way to pay the conversion tax.  In the example you show, it makes the marginal tax rate of the conversion 29.56%, much worse than 24%.  Given 20 years and the conditions you chose, the BETR does drop from 29.56% to 24.77%.

[murrays007]

I want to thank you again for addressing this topic!  This is very important to me since we are retired early and have no income sources to pay conversion taxes other than liquidating taxable assets.

Of course, $2400 is better than $2649, but now you've added another $2400 to the denominator of the marginal tax calculation.

I think you are getting hung up on the definition of the denominator in the marginal tax rate calculation.  Yes, the IRS will count the sale of appreciated securities as income.  No, in this case you shouldn't add that to the denominator.  To evaluate the marginal tax rate of the conversion, you want the tax divided by the amount of the conversion.  Period.

I agree, all taxes generated from the conversion event need to be considered.

Whether you pay the $2400 from the conversion amount or from another tax-free source, the marginal tax rate is the same: 24%.  Nothing inconsistent about that.

The inconsistency I see is that your marginal tax calculation tells me that a $7600 Roth balance from a $2400 tax payment is equivalent to a $10k Roth balance from the same $2400 tax payment and somehow better than a $10k Roth balance from a $2649 combined tax payment.

Selling a 0% cost basis investment is a pretty terrible way to pay the conversion tax.  In the example you show, it makes the marginal tax rate of the conversion 29.56%, much worse than 24%.  Given 20 years and the conditions you chose, the BETR does drop from 29.56% to 24.77%.

I don't disagree, selling a 0% cost basis wouldn't be my preferred option, but it is still preferable to paying the taxes using tIRA withholding assuming current/future tax rates are the same and there is a dividend tax owed at the end of every year.  Allow me to demonstrate:

Using the conditions in the screen shot above, consider 3 options for a $10k tIRA to Roth conversion:
a) Pay the tax using a 0% basis investment ($2,956 total change in taxes) resulting in $10k in the Roth, nothing in the tIRA and nothing in the taxable account.
b) Don't do the conversion, keep $2,956 (0% cost basis) in the taxable account leaving a Roth balance of $0.
c) Pay the conversion tax using tIRA withholding resulting in $7600 in the Roth, $2,956 in the taxable account and nothing in the tIRA.

Assuming 18.8% tax is paid on the 2% dividends every year, here are the results I came up with:

Source: Sheet 4 on https://www.dropbox.com/scl/fi/ioeu1rw0jcm8zmbt0froy/Roth-Conversion-Example.xlsx?rlkey=2wjid1lwc0pki4y70c1hp0bpz&dl=0

This shows me that paying the conversion tax from the 0% basis investment is better than no conversion and using tIRA withholding due to the tax on dividends not being reinvested which is contrary to your marginal rate/BETR calculations.

I believe this goes back to my earlier comment on looking at the tax payment as a separate event from the conversion:
- Given the same present/future ordinary tax rates, there is no accounting advantage to converting a $10k tIRA to a $7600 Roth, though there are other benefits.
- The question becomes, is there a benefit from using the opportunity to move taxable funds into the Roth even when it generates additional taxes?  Unlike the tIRA/Roth benefits, moving the taxable funds into a Roth benefits from the tax drag on dividend payments plus any existing cost basis providing tax free growth even when the present/future tax rates remain the same.

Thanks again for your attention to this.  I look forward to your response!

[MDM]

Whether you pay the $2400 from the conversion amount or from another tax-free source, the marginal tax rate is the same: 24%.  Nothing inconsistent about that.

The inconsistency I see is that your marginal tax calculation tells me that a $7600 Roth balance from a $2400 tax payment is equivalent to a $10k Roth balance from the same $2400 tax payment and somehow better than a $10k Roth balance from a $2649 combined tax payment.

There is quantity and there is quality.  For quantity, no argument that making a larger conversion at the same favorable marginal rate is better than making a smaller conversion at that rate.  For quality, converting an amount for a lower tax cost is better than converting the same amount for a higher tax cost.

When you mix quantity and quality then it's not immediately clear and some calculations are needed.  For example, for people in the 0% federal bracket for qualified dividends and long term capital gains, there may be no tax drag at all in their taxable accounts.  In that case, taxable and Roth holdings may (or may not) be of equal value.  If there is a tax drag, then moving money from taxable to Roth can overcome the extra initial cost, as your spreadsheet shows.

On a Roth conversion, you may have tax withheld and then send a "rollover contribution" of that amount to get the same amount into the Roth as was distributed from the traditional.  If you want the tax withheld to equal your total tax cost for the conversion itself plus raising the funds to pay that tax, the withholding (i.e., the marginal tax rate on the distribution) will differ depending on how you raise those funds.

- Given the same present/future ordinary tax rates, there is no accounting advantage to converting a $10k tIRA to a $7600 Roth, though there are other benefits.
- The question becomes, is there a benefit from using the opportunity to move taxable funds into the Roth even when it generates additional taxes?  Unlike the tIRA/Roth benefits, moving the taxable funds into a Roth benefits from the tax drag on dividend payments plus any existing cost basis providing tax free growth even when the present/future tax rates remain the same.

Yes to all that, if a tax drag exists.  You mentioned Bogleheads: the Bogleheads Traditional versus Roth wiki article discusses those two points in its Calculations section.

[murrays007]

There is quantity and there is quality.  For quantity, no argument that making a larger conversion at the same favorable marginal rate is better than making a smaller conversion at that rate.  For quality, converting an amount for a lower tax cost is better than converting the same amount for a higher tax cost.

When you mix quantity and quality then it's not immediately clear and some calculations are needed.  For example, for people in the 0% federal bracket for qualified dividends and long term capital gains, there may be no tax drag at all in their taxable accounts.  In that case, taxable and Roth holdings may (or may not) be of equal value.  If there is a tax drag, then moving money from taxable to Roth can overcome the extra initial cost, as your spreadsheet shows.

We are (almost) in complete agreement, but I'm still "hung up" on your BETR calculation because it's telling me that using taxable funds with some capital gains to pay the conversion tax is less beneficial than withholding without sufficient time.  What exactly should I be comparing BETR to?  From the Vanguard site on this, "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."  http://https://investor.vanguard.com/investor-resources-education/news/a-betr-calculation-for-the-traditional-to-roth-ira-conversion-equation

Per your BETR calculation, it takes 25 years to "overcome the extra initial cost" (BETR reaches 24%) of using a 0% basis investment to pay the taxes, yet my spreadsheet shows an after tax benefit to converting from year 1 due to tax drag.  Based on this, shouldn't the BETR start at 24% and drop slightly each year so that the after tax value of the tIRA & taxable account equals the converted Roth value?  That's what Vanguard is telling me.

See the new columns on the right for my estimate of BETR based on the Vanguard definition:

Source: Sheet 5 https://www.dropbox.com/scl/fi/ioeu1rw0jcm8zmbt0froy/Roth-Conversion-Example.xlsx?rlkey=2wjid1lwc0pki4y70c1hp0bpz&dl=0

Edited to quote actual Vanguard information

[MDM]

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