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.