#### MDM

• Senior Mustachian
• Posts: 10913
« Reply #300 on: January 22, 2022, 06:34:45 PM »
Hey MDM, I love this spreadsheet. One discrepancy that I'm seeing in the calculation for schedule 8812. The Line 5 calculation, according to the IRS website (https://www.irs.gov/instructions/i1040s8#en_US_2021_publink100077560) is giving me a \$29 dollar difference from your spreadsheet. It's not a huge difference but I'm just trying to understand why. Unfortunately I can't really follow the cell calculation well enough to figure out the difference.
If you could PM me (or post here) your inputs, I'd be happy to take a look.

#### john6221

• Stubble
• Posts: 134
« Reply #301 on: January 22, 2022, 07:57:40 PM »
Hey MDM, I love this spreadsheet. One discrepancy that I'm seeing in the calculation for schedule 8812. The Line 5 calculation, according to the IRS website (https://www.irs.gov/instructions/i1040s8#en_US_2021_publink100077560) is giving me a \$29 dollar difference from your spreadsheet. It's not a huge difference but I'm just trying to understand why. Unfortunately I can't really follow the cell calculation well enough to figure out the difference.
If you could PM me (or post here) your inputs, I'd be happy to take a look.

Thanks, I appreciate you taking a look. Here is the info for inputs:

Schedule 8812, Part 1-A
Line 1: 189430
Line 4a: 2
Line 4b: 2
Line 13: A checked, B unchecked

Schedule 8812, Part 1-B
Line f: 3600

Please let me know if that's all of the info that you need.

According to my calculations, for Part 1-A, Line 5, I get a result of \$5200, while the Case Study spreadsheet gets \$5229.

Thanks!

#### secondcor521

• Magnum Stache
• Posts: 4493
• Age: 52
• Location: Boise, Idaho
• Big cattle, no hat.
« Reply #302 on: January 22, 2022, 08:16:42 PM »
Hey MDM, I love this spreadsheet. One discrepancy that I'm seeing in the calculation for schedule 8812. The Line 5 calculation, according to the IRS website (https://www.irs.gov/instructions/i1040s8#en_US_2021_publink100077560) is giving me a \$29 dollar difference from your spreadsheet. It's not a huge difference but I'm just trying to understand why. Unfortunately I can't really follow the cell calculation well enough to figure out the difference.
If you could PM me (or post here) your inputs, I'd be happy to take a look.

Thanks, I appreciate you taking a look. Here is the info for inputs:

Schedule 8812, Part 1-A
Line 1: 189430
Line 4a: 2
Line 4b: 2
Line 13: A checked, B unchecked

Schedule 8812, Part 1-B
Line f: 3600

Please let me know if that's all of the info that you need.

According to my calculations, for Part 1-A, Line 5, I get a result of \$5200, while the Case Study spreadsheet gets \$5229.

Thanks!

Assuming you're MFJ and Schedule 8812 line 3 is the same as line 1 (which are both probable), then my results basically match the CSS (depending on rounding).  My Line 5 worksheet results are:

1   7200
2   0
3   7200
4   4000
5   3200
6   12500
7   3200
8   150000
9   39430
10   1971.5
11   1971.5
12   5228.5

#### MDM

• Senior Mustachian
• Posts: 10913
« Reply #303 on: January 22, 2022, 08:24:16 PM »
Thanks for the question.  It is a great example of "it depends what you want to see," and cell Calculations!R80 controls that.

In case anyone else wants to look, putting 189430 in cell B3, and 2 in cells G2 through G7 will reproduce john6221's question.

If you change cell R80 from "Y" to "N", you should see the correct result of \$5200.  But then look at the marginal rate chart and all those spikes for the first ~\$24K of tIRA withdrawals/conversions.

The note for cell R80 is "Use Y for a "clean" graph (at left) and continuous derivatives.  Anything else (e.g., N) will use actual tax calculations that can have discontinuous derivatives."

The default is "Y" because the clean marginal rate chart seems, in general, the more useful output.  But if one wants to see exactly what the tax code does, "N" will do that.

#### john6221

• Stubble
• Posts: 134
« Reply #304 on: January 22, 2022, 09:11:29 PM »
All makes sense to me now. Thank you for the clarification!

Sent from my iPhone using Tapatalk

#### MDM

• Senior Mustachian
• Posts: 10913
« Reply #305 on: January 30, 2022, 10:36:53 PM »
Version 21.10 (2021)
Version 22.00 (2022)
• Minor cleanup in the 2021 version
• Introduction of the 2022 version
The 2021 version appears to match all the TaxSlayer (several of the Tax-Aide Workbook exercises) and TurboTax (our own return) comparisons I've done, so that's good.

The 2022 version, as previous posts have mentioned, has to be considered "subject to change as Congress does whatever Congress will do" this year.  That's always true, but perhaps more so this year than most.  We'll see....

To prepare for changes, all of the inflation-indexed numbers (and some of the tax law affected ones) are gathered in the 'Tax Code' sheet.  The corresponding HLOOKUPs needed to bring those to the 'Calculations' sheet may be slowing calculations a bit so this strategy may or may not persist.

If it is working as designed, setting cell Calculation!R2 to 2021 should provide the same results in the 2022 version for 2021 taxes as the 2021 version calculates.  Of course, setting that cell to 2022 would be the usual mode for the 2022 version.

Still haven't seen a 2021 Pub. 974, so I'm not 100% sure the CSS can be used to get an accurate-to-the-dollar answer to the iterative calculations needed when one has both self-employed health insurance (SEHI) and premium tax credits (PTC) with an Affordable Care Act policy - at least if one's tax situation is covered by the CSS - but it is looking good.

Another task it may be able to do - although it will take a little jury-rigging - is determine the amount of unrestricted scholarship money that should be declared as income in order to increase the American Opportunity Tax Credit and have a net tax saving.

More on both the SEHI/PTC iteration and the education credit hacking in future versions.

As always (and particularly after major changes such the 2022 version), comments/suggestions/bug identifications/etc. are appreciated.  Either PM me or drop a note here.

2021 taxes version: 2021 Case Study Spreadsheet
2022 taxes version: 2022 Case Study Spreadsheet

Note:

#### dandarc

• Magnum Stache
• Posts: 4854
• Age: 39
« Reply #306 on: January 31, 2022, 09:31:56 AM »
Hurray for 2022!

Is there a way built-in to enter the energy-efficiency credit? I'm expecting to probably owe negative income tax to the extent we get a PTC for 2022, in large part due to the tax credit on our new solar panels they turned on earlier this month.

Off the bat, I think I could just write over the foreign tax credit in G21?

#### MDM

• Senior Mustachian
• Posts: 10913
« Reply #307 on: January 31, 2022, 12:15:23 PM »
Off the bat, I think I could just write over the foreign tax credit in G21?
That would work perfectly if you have no other non-refundable credits (see https://www.irs.gov/pub/irs-pdf/f1040s3.pdf).  There is a hierarchy to those credits in that the ones further down the list on Schedule 3 become worthless if the ones higher on the list have taken tax liability to zero.

Coding a full Schedule 3 with all the hierarchical limits is a bit more than I've contemplated, but maybe a catch-all "line 5 and below" item would suffice....

#### dandarc

• Magnum Stache
• Posts: 4854
• Age: 39
« Reply #308 on: January 31, 2022, 01:52:36 PM »
Off the bat, I think I could just write over the foreign tax credit in G21?
That would work perfectly if you have no other non-refundable credits (see https://www.irs.gov/pub/irs-pdf/f1040s3.pdf).  There is a hierarchy to those credits in that the ones further down the list on Schedule 3 become worthless if the ones higher on the list have taken tax liability to zero.

Coding a full Schedule 3 with all the hierarchical limits is a bit more than I've contemplated, but maybe a catch-all "line 5 and below" item would suffice....
Knocking on all the wood - I think 2021 was the last year to feature the Lifetime Learning Credit for a good while. Wife should graduate this at the end of April, which we paid for before year end (knocking on wood again).

Reading through all that and looking at the most relevant form to me - I don't think I'd attempt to get this into the spreadsheet either. One of the reasons I pulled the trigger on the panels finally is this tax credit is going to not only offset our entire tax bill this year, the carryforward is likely to offset a good chunk or all of next year too. Even more so if they somehow get something through congress that bumps 26% to 30%.

#### Mrs Brightside

• Posts: 87
• Location: a Cinnabon in Omaha
• What one fool can do, another can.
« Reply #309 on: February 16, 2022, 12:37:35 PM »
This is probably a tax ignorance problem rather than a spreadsheet problem, but I was surprised that my backdoor Roth contribution increased the taxes due. For "tIRA distribution (converted to Roth or not)" in Calculations cell D31, I put 12000 total Annual. I also filled in the sheet for form 8606, not sure whether that feeds into the Calculations tab or is totally separate. For 8606, I have this: (we did the contribution and the conversion in 2021)

Year:   2021
31-Dec-2020 tIRA basis (after-tax amount in all tIRAs) 0
Contributions made for this tax year in this calendar year
Pre-tax   0
Post-tax   12000

Distributions made in this calendar year
Not converted to Roth   0
Converted to Roth   12000
31-Dec tIRA balance   0

End result was to increase my Net Tax by \$4440. Fair warning, I am using the spreadsheet in Google sheets so I understand macros might be broken e.g. the total tax rate chart update button.

#### MDM

• Senior Mustachian
• Posts: 10913
« Reply #310 on: February 16, 2022, 12:49:20 PM »
This is probably a tax ignorance problem rather than a spreadsheet problem, but I was surprised that my backdoor Roth contribution increased the taxes due. For "tIRA distribution (converted to Roth or not)" in Calculations cell D31, I put 12000 total Annual. I also filled in the sheet for form 8606, not sure whether that feeds into the Calculations tab or is totally separate. For 8606, I have this: (we did the contribution and the conversion in 2021)

Year:   2021
31-Dec-2020 tIRA basis (after-tax amount in all tIRAs) 0
Contributions made for this tax year in this calendar year
Pre-tax   0
Post-tax   12000

Distributions made in this calendar year
Not converted to Roth   0
Converted to Roth   12000
31-Dec tIRA balance   0

End result was to increase my Net Tax by \$4440. Fair warning, I am using the spreadsheet in Google sheets so I understand macros might be broken e.g. the total tax rate chart update button.
Thanks, good feedback for how one could interpret that line.

Based on that, the note in cell A31 will read "Use this for any taxable traditional withdrawal from an IRA, 401k, etc., whether converted to Roth or not.  This does not include the non-taxable amounts from a backdoor Roth process.  Use line 55, Roth IRA, for those amounts" when a new version is released.

The Form8606 sheet is completely separate from the Calculations sheet.

#### MDM

• Senior Mustachian
• Posts: 10913
« Reply #311 on: February 22, 2022, 02:27:17 AM »
Version 22.01 (2022)
• Updated the Social Security benefit examples to use the latest ones from the SSA.  No change in the calculations, which do match the examples exactly.
• Added an input cell for the non-refundable credits that appears on Schedule 3 lines 5 and 6.
• Added the ability to find the exact maximum Self-Employed Health Insurance amount when the Affordable Care Act Premium Tax Credit is also present.
• Added Long Term Care (LTC) insurance amounts to itemized medical deductions.  Haven't added the age-dependent test on the maximum LTC premiums that may be used - probably won't bother unless/until someone has a particular need for this.
• Added the ability to determine the optimum amount of unrestricted college scholarship funds to include as income.
• Updated state tax brackets and rates - thanks once again to the Tax Foundation who publishes this summary.
• Updated comments in some cells to give better directions.
Thanks to dandarc and Mrs Brightside for their suggestions that have been incorporated in this version.  So far no errors have surfaced, so this release is all new functionality and documentation.

As a reminder, setting cell Calculation!R2 to 2021 should provide the same results in the 2022 version for 2021 taxes as the 2021 version calculates.  One could do that to check whether this tool gives a "close enough" 2021 calculation when compared with your commercial tax software of choice.  If so, there is a good chance it will provide (to the extent income is predicted correctly) a good estimate of 2022 tax liability when R2 = 2022.

Here's the "More on both the SEHI/PTC iteration and the education credit hacking in future versions" mentioned in the last update.
------------------------------
I'll use Harry Sit's 2021 Self-Employed ACA Health Insurance Deduction In TurboTax article for an SEHI/PTC example.
• Set cell B30 = 45000, B114 = 500, B115 = -150, AE100 = 7200, G2 =1, G9 = 35, R2 = 2021, and R80 = N.  Those should match the income and ACA values.
• To have the CSS do the iterative SEHI/PTC calculations, in Excel go to "File > Options > Formulas" and use the settings shown below.  If iterations will converge, they usually converge quickly.  These settings seem to give accurate-enough results in a short-enough time.

• Put the formula =AD159 into cell B48
That should give the result of \$1,388 for the SEHI deduction, and a total PTC (cell AD102) of \$4,612, same as in the linked example.  Deleting the formula in B48 and entering 1388 as a value shouldn't change the PTC, and thus confirm the calculations.

Things get trickier if the selected premium is \$498/mo instead of \$500/mo.  Change B114 to 498, put =AD159 into B48, and the SEHI result is \$1,377 with a total PTC of \$4,612.  This is not a legal result because \$1377+\$4612=\$5989 and that is more than \$498*12=\$5976.  At least it's closer than what TurboTax calculates (see comment #3 in the linked article).  But there is a way to get the exact best legal answer:
• An SEHI guess of \$1377 gives a result only \$13 too high, so the correct number is probably close to that.
• Put "B48" (w/o quotes) into cell G107.  Click the "Update chart" button near L114.
• Ensure =AH96 is in cell R83
• Set P84 = 1200 and P83 = 1.  This will check every whole dollar value from \$1200 to \$1700.
• See the result of \$1,371 in cell R78.
• Confirm that by putting 1371 into B48.  The PTC is \$4,599 for a total of \$5,970.
• Putting 1372 into B48, the PTC is \$4,611 for a total of \$5,983.  \$1371 provides a legal result, but \$1372 is too high.
------------------------------

See p. 16-17 of Pub. 970, and the Education Benefits Calculator tool page, for background on choosing to make some types of college scholarship money taxable in order to receive more in education credits than the additional tax on that amount.

Picking up where the SEHI/PTC example left off (i.e., \$498/mo premium), assume the filer has \$6000 in qualified education expenses and a \$4000 unrestricted (e.g., it can be used for room & board in addition to tuition) scholarship.  This would be simpler without the SEHI/PTC complication, but let's do both at once:
• Set cell B48 back to the =AD159 formula.
• In cell B92, put the formula =MAX(0,(AOTC_Expense - (Unrestricted_Scholarship-D40))/12).  Use 6000 for AOTC_Expense and 4000 for Unrestricted_Scholarship, so the actual formula will be =MAX(0,(6000-(4000-D40))/12)
• Put "D40" (w/o quotes) into cell G107.  Click the "Update chart" button near L114.
• If needed, put "D67" (w/o quotes) into cell L107.  Click the "Update chart" button near L114.
• Set P84 = 0 and P83 = 8.  See the result of \$1,896 in cell Q78.
In words, what the above accomplished was to check every \$8 increment of taxable scholarship money from \$0 to \$4000 (there are ~500 points in the chart) for the lowest overall tax bill, including all credits.  Assuming \$1,896 is close to "the middle of the ballpark", now try P84 = 1650 and P83 = 1.  That refines the answer to \$1,899.

The iterated SEHI amount comes to \$1618, but that is not a converged solution, as one can verify using the SEHI/PTC procedure above.  The dollar-by-dollar test gives \$1612 for the SEHI amount.

Cells P76:R78, along with this write-up, can help as a guide for what needs doing on both the SEHI/PTC and education credit issues.
------------------------------

Don't know if anyone will use either of these (let along both at the same time), but maybe....

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

2021 taxes version: 2021 Case Study Spreadsheet
2022 taxes version: 2022 Case Study Spreadsheet

Note:

#### noppenbd

• Posts: 1
« Reply #312 on: February 22, 2022, 06:56:37 AM »
Love the case study spreadsheet.  Is it possible to add C47 (spouse SE,SEP) to the X-axis options for the tax rate graph?  Thanks!

#### MDM

• Senior Mustachian
• Posts: 10913
« Reply #313 on: February 22, 2022, 01:06:01 PM »
Love the case study spreadsheet.  Is it possible to add C47 (spouse SE,SEP) to the X-axis options for the tax rate graph?  Thanks!
noppenbd, welcome to (at least your first post on) the forum.

There are two ways to do this:
1. Swap your definition of "Earner #1" and "Earner #2" and then use B47 for the X-axis. :)
2. Excel will allow you to bypass the data validation if, instead of trying to type C47 into G107, you enter C47 into another cell and then copy that into G107.  E.g., type C47 into cell B107 (yes, that makes no sense by itself, but wait...) then copy B107 into G107, click the "Update chart" button, and go back and delete the B107 entry.  The chart title will now say "... vs. Something" but you'll know what that Something is and the chart numbers/curves will be correct.

#### mmm999

• Posts: 3
« Reply #314 on: February 28, 2022, 02:54:53 PM »
Hi MDM,
This is my first post.  I have question about Line 10 Form 8812 (cell Y190 in the 2021 spreadsheet).  The IRS instructions for line 10 is

"Subtract line 9 from line 3.
• If zero or less, enter -0-.
• If more than zero and not a multiple of \$1,000, enter the next multiple of \$1,000. For
example, if the result is \$425, enter \$1,000; if the result is \$1,025, enter \$2,000, etc.
"

Currently, cell Y190 has
=MAX(0,Y181-Y189)

should it be something like
=MAX(0,IF(Y181>Y189,CEILING(Y181-Y189,1000)))
to match with the instructions?
Thank you.

#### MDM

• Senior Mustachian
• Posts: 10913
« Reply #315 on: February 28, 2022, 04:38:04 PM »
Currently, cell Y190 has
=MAX(0,Y181-Y189)

should it be something like
=MAX(0,IF(Y181>Y189,CEILING(Y181-Y189,1000)))
to match with the instructions?
mmm999, welcome to the forum and thanks for the observation.

Yes, for those of you filing MFJ with AGI>\$400K or single/HOH with AGI>\$200K and wanting an exact tax calculation, something like that is needed.  E.g., see cell Y185.

See replies #299-#304 in this thread for some discussion about exact tax calculations for specific inputs vs. smooth marginal rate charts for a range of values.

Shouldn't be hard to add similar "If smooth curves desired, don't use the CEILING function, otherwise do" logic for the next release.

#### mmm999

• Posts: 3
« Reply #316 on: March 03, 2022, 07:04:22 PM »
Hi MDM,
Thanks for your answer.  I looked at replies #299-#304 and realized that I also had discrepancy on line 5 Form 8812 (cell Y185) too.  For my case, however, changing smoothing value did not help.

H&R Block: \$2000
CSS No smoothing:  \$1950
CSS Yes smoothing:  \$1993

Here are my values for Form 8812
Line 1: 400144
Line 3: 400144
Line 4a: 1
Line 4b: 0

Thanks.

#### MDM

• Senior Mustachian
• Posts: 10913
« Reply #317 on: March 03, 2022, 11:10:25 PM »
LOL!  mmm999, you may be the only person with AGI > \$400K and Child Tax Credits looking at this calculation (that may disappear after 2021), but I sincerely appreciate the feedback and will investigate.  Should be a quick fix once I get time but that may not be until the weekend or early next week.

#### mmm999

• Posts: 3
« Reply #318 on: March 04, 2022, 08:54:00 AM »
LOL!  mmm999, you may be the only person with AGI > \$400K and Child Tax Credits looking at this calculation (that may disappear after 2021), but I sincerely appreciate the feedback and will investigate.  Should be a quick fix once I get time but that may not be until the weekend or early next week.

First attempt doing Roth conversion to top 24% + LTCG which pushed my AGI > \$400K.  I was aiming for <= \$400K to maximize the CTC but forgot to add foreign tax \$ to my income estimate.  This resulted in a loss of \$50 tax credit or \$50/\$144= 44% tax rate on my last \$114 (\$400,144 - \$400,000) conversion 8-(.  Lesson learned!

#### MDM

• Senior Mustachian
• Posts: 10913
« Reply #319 on: March 08, 2022, 05:48:19 PM »
Version 22.02 (2022)
Version 21.12 (2021)
• Changes to the 2021 version of Form 8812.
• Minor tweaks to the SEHI/ACA convergence calculations.
Thanks to mmm999 for noticing the 8812 issue.

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

2021 taxes version: 2021 Case Study Spreadsheet
2022 taxes version: 2022 Case Study Spreadsheet

Note:

#### Paul der Krake

• Walrus Stache
• Posts: 5719
• Age: 14
• Location: UTC-10:00
« Reply #320 on: April 03, 2022, 07:01:01 PM »
Feature request!

In California there is a 1.1% disability tax on wage income, up to the usual social security cap of \$145k and some change. Washington also has 0.58% flat tax on wage income, but this one has no cap. I realize it'd be impractical to model every single county and city taxes out there, but perhaps focusing on the statewide ones is doable?

#### MDM

• Senior Mustachian
• Posts: 10913
« Reply #321 on: April 03, 2022, 07:30:58 PM »
Feature request!

In California there is a 1.1% disability tax on wage income, up to the usual social security cap of \$145k and some change. Washington also has 0.58% flat tax on wage income, but this one has no cap. I realize it'd be impractical to model every single county and city taxes out there, but perhaps focusing on the statewide ones is doable?
Those might be doable, especially if you could provide/confirm some specific Excel cell formulas.  E.g., I got a private request for a (simplified) MI property tax credit calculation, including cell formulas and comments, so that will probably make it into the next version.

On the 'State Tax' tab would those CA and WA taxes be =0.011*MIN(E8,some_cap) and =0.0058*E8 respectively?

#### Paul der Krake

• Walrus Stache
• Posts: 5719
• Age: 14
• Location: UTC-10:00
« Reply #322 on: April 04, 2022, 08:11:06 AM »
Feature request!

In California there is a 1.1% disability tax on wage income, up to the usual social security cap of \$145k and some change. Washington also has 0.58% flat tax on wage income, but this one has no cap. I realize it'd be impractical to model every single county and city taxes out there, but perhaps focusing on the statewide ones is doable?
Those might be doable, especially if you could provide/confirm some specific Excel cell formulas.  E.g., I got a private request for a (simplified) MI property tax credit calculation, including cell formulas and comments, so that will probably make it into the next version.

On the 'State Tax' tab would those CA and WA taxes be =0.011*MIN(E8,some_cap) and =0.0058*E8 respectively?
As far as I understand it, both of these taxes are assessed on gross wages, just like FICA, whereas E8 links to W-2 Box 1.

#### MDM

• Senior Mustachian
• Posts: 10913
« Reply #323 on: April 04, 2022, 01:25:54 PM »
Feature request!

In California there is a 1.1% disability tax on wage income, up to the usual social security cap of \$145k and some change. Washington also has 0.58% flat tax on wage income, but this one has no cap. I realize it'd be impractical to model every single county and city taxes out there, but perhaps focusing on the statewide ones is doable?
Those might be doable, especially if you could provide/confirm some specific Excel cell formulas.  E.g., I got a private request for a (simplified) MI property tax credit calculation, including cell formulas and comments, so that will probably make it into the next version.

On the 'State Tax' tab would those CA and WA taxes be =0.011*MIN(E8,some_cap) and =0.0058*E8 respectively?
As far as I understand it, both of these taxes are assessed on gross wages, just like FICA, whereas E8 links to W-2 Box 1.
Looking at DB101 California - California State Disability Insurance (SDI): Eligibility and Application it says "Your paycheck is \$1,000 before taxes. Every time you get a paycheck, 1.1% of that goes automatically to SDI."  Usually the "paycheck" amount goes to W-2 Box 1, because employer-based HSA, medical insurance, etc., have already been subtracted.

New State Employee Payroll Tax Law for Long-Term Care Benefits - Washington State Hospital Association refers to "wages reported on a Form W-2" which also implies W-2 box 1.

Those articles also note many reasons why a W-2 wage earner would not be subject to the taxes, so if they get added to the CSS it will be (same as I'll do for the MI credit) with a "*0" at the end so users will have to take action beyond entering the state ID to included the calculation.

All I did, however, was quickly skim those two linked articles.  Does a more detailed study lead to a different conclusion?

#### Paul der Krake

• Walrus Stache
• Posts: 5719
• Age: 14
• Location: UTC-10:00
« Reply #324 on: April 04, 2022, 07:29:59 PM »
Regarding California: it's on gross wages, not after deductions. I have confirmed this empirically by looking at a recent CA paycheck with a sizeable 401(k) contribution where the amount withheld is exactly 1.1% of the gross wages.

Regarding WA: oh boy it's complicated. I'm pretty sure it's also on gross wages BUT it turns out there's not one but two separate taxes in different life stages, with different rules.

All this to say: perhaps instead of opening this can of worms, the best way forward is to provide a free-form row (around F37?) where users can enter their own formula.

#### MDM

• Senior Mustachian
• Posts: 10913
« Reply #325 on: April 04, 2022, 07:56:44 PM »
Regarding California: it's on gross wages, not after deductions. I have confirmed this empirically by looking at a recent CA paycheck with a sizeable 401(k) contribution where the amount withheld is exactly 1.1% of the gross wages.

Regarding WA: oh boy it's complicated. I'm pretty sure it's also on gross wages BUT it turns out there's not one but two separate taxes in different life stages, with different rules.

All this to say: perhaps instead of opening this can of worms, the best way forward is to provide a free-form row (around F37?) where users can enter their own formula.
Can you tell if in CA it is wages before any deductions (including medical insurance, etc.)?

In any case, providing free form calculation space seems a good idea.  E.g., it would be easy enough to make cells X23:AG73 on the 'State Tax' tab editable, with column AB taken as a refundable credit and column AG as an additional tax.  The specific state result would go via cells H17 and I17 to B19 and then to Calculations!G37.  Seem reasonable?

Of course, one can always remove sheet protection and edit anything....

#### Paul der Krake

• Walrus Stache
• Posts: 5719
• Age: 14
• Location: UTC-10:00
« Reply #326 on: April 04, 2022, 08:45:38 PM »
Can you tell if in CA it is wages before any deductions (including medical insurance, etc.)?
Yep, it's unambiguously before medical/dental/FSA/401(k) deductions. This is from a leading payroll provider so I'd bet good money that they have this right. I think it's safe to assume any other type of deduction would be ignored as well.

In any case, providing free form calculation space seems a good idea.  E.g., it would be easy enough to make cells X23:AG73 on the 'State Tax' tab editable, with column AB taken as a refundable credit and column AG as an additional tax.  The specific state result would go via cells H17 and I17 to B19 and then to Calculations!G37.  Seem reasonable?
Yes!

#### Telecaster

• Magnum Stache
• Posts: 2888
• Location: Seattle, WA
« Reply #327 on: April 04, 2022, 08:57:01 PM »
Washington also has 0.58% flat tax on wage income, but this one has no cap. I realize it'd be impractical to model every single county and city taxes out there, but perhaps focusing on the statewide ones is doable?

FWIW, that tax has been delayed until July 2023 (and I'm guessing might die before then) and there are ways to opt out as well.

#### Jason95357

• Posts: 2
« Reply #328 on: April 12, 2022, 08:55:45 PM »
Regarding California: it's on gross wages, not after deductions. I have confirmed this empirically by looking at a recent CA paycheck with a sizeable 401(k) contribution where the amount withheld is exactly 1.1% of the gross wages.

Regarding WA: oh boy it's complicated. I'm pretty sure it's also on gross wages BUT it turns out there's not one but two separate taxes in different life stages, with different rules.

All this to say: perhaps instead of opening this can of worms, the best way forward is to provide a free-form row (around F37?) where users can enter their own formula.
Can you tell if in CA it is wages before any deductions (including medical insurance, etc.)?

In any case, providing free form calculation space seems a good idea.  E.g., it would be easy enough to make cells X23:AG73 on the 'State Tax' tab editable, with column AB taken as a refundable credit and column AG as an additional tax.  The specific state result would go via cells H17 and I17 to B19 and then to Calculations!G37.  Seem reasonable?

Of course, one can always remove sheet protection and edit anything....

Looking at my returns, California's "Schedule 540 (CA)" starts with the Federal income and itemized deductions, and has a columns for subtractions and additions.  Without adding customization for each state, the simplest thing would be to provide a free form that allowed amounts to be added/subtracted from the Federal income and itemized deductions.  Personally, I'd then customize my own document and make those free form field to calculate the exact income additions/subtractions and deductions additions/subtractions.

The CA tax form is here:
https://www.ftb.ca.gov/forms/2021/2021-540-ca.pdf
« Last Edit: April 12, 2022, 09:06:40 PM by Jason95357 »

#### mangorunner

• Posts: 8
« Reply #329 on: April 27, 2022, 10:12:49 PM »
Thanks for this thing of BEAUTY!

Using Version V22.02 dated 03/08/2022.  One minor thing I noticed:
Cells AD98 and AE98 are locked (even though green).  I do realize they can be unlocked manually.

Thanks again for the tremendous effort you have put into this extremely helpful planning tool!

#### mangorunner

• Posts: 8
« Reply #330 on: April 27, 2022, 10:34:49 PM »
Another item:

Cell AD100 is using a full-year amount (12 months) even if "No" is entered in cell AD98 and number of months in cell AE98.  Of course, it's easy enough to game the system (spreadsheet) to get around that.

Same with cell AI100.  It's also using a full-year amount.

If we only have 11 months of coverage (registered in January 2022; coverage started on Feb 1), should we be taking the total of 11 months and dividing that total by 12 to enter a monthly amount in Column B (cells B114 and B115)?  That's how I'm gaming the system to get it to work right now.  Thanks in advance for your advice.
« Last Edit: April 27, 2022, 10:42:45 PM by mangorunner »

#### MDM

• Senior Mustachian
• Posts: 10913
« Reply #331 on: April 27, 2022, 11:27:45 PM »
Thanks for this thing of BEAUTY!

Using Version V22.02 dated 03/08/2022.  One minor thing I noticed:
Cells AD98 and AE98 are locked (even though green).  I do realize they can be unlocked manually.

Thanks again for the tremendous effort you have put into this extremely helpful planning tool!
Thanks - easy fix (already done) for the next release.

#### MDM

• Senior Mustachian
• Posts: 10913
« Reply #332 on: April 27, 2022, 11:58:05 PM »
Another item:

Cell AD100 is using a full-year amount (12 months) even if "No" is entered in cell AD98 and number of months in cell AE98.  Of course, it's easy enough to game the system (spreadsheet) to get around that.

Same with cell AI100.  It's also using a full-year amount.

If we only have 11 months of coverage (registered in January 2022; coverage started on Feb 1), should we be taking the total of 11 months and dividing that total by 12 to enter a monthly amount in Column B (cells B114 and B115)?  That's how I'm gaming the system to get it to work right now.  Thanks in advance for your advice.
This one is less clear, because there can be a conflict between using the CSS for cash flow analysis vs. tax estimation.  Yes, it would be nice if those goals always coincide :) and see below for how we might do this.

One way the CSS is tested is by comparing its results to the annual Tax-Aide Workbook problem answers.  Don't recall offhand what I did to get a "less than 12 month" 1095-A situation to match, but it may have been exactly as you are "gaming it."  Or maybe it didn't matter because...?

Anyway, at a quick glance the following changes might allow you to enter your actual monthly cash flow numbers in B114 and B115 while also matching what will be on your 2022 1095-A.  Could you test and let me know?

You would also have to enter 11/12 of the annual SLCSP in AE100 to match what the 1095-A will show, because there is no formula in that cell - it's a pure input.

That could fit your situation of expecting ACA premiums to continue.  For someone expecting ACA coverage to stop later in the year (e.g., anticipating a shift to employer-provided coverage), however, that may not be appropriate.  Any thoughts on that?

• Posts: 8