Case Study Spreadsheet updates

[MDM]

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]

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]

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]

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]

All makes sense to me now. Thank you for the clarification!


Sent from my iPhone using Tapatalk

[MDM]

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:

• 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 ;)
  - For the 2021 version, click the "Download" icon at the top right after the file opens.
  - For the 2022 version, 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.

[dandarc]

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]

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]

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]

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]

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]

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:

• 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 ;)
  - For the 2021 version, click the "Download" icon at the top right after the file opens.
  - For the 2022 version, 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.

[noppenbd]

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]

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]

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]

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]

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]

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]

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!

No rush.  I love your spreadsheet and greatly appreciate your time and effort.

[MDM]

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:

• 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 ;)
  - For the 2021 version, click the "Download" icon at the top right after the file opens.
  - For the 2022 version, 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.

[Paul der Krake]

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]

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]

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]

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]

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]

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]

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]

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]

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

[mangorunner]

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]

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.

[MDM]

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]

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?

AD100:  =IF(AD94="Yes","",IF(AD98="Yes",D114,AE98/12*D114))
AI100:   =IF(AD98="Yes",-D115,-AE98/12*D115)
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?

[mangorunner]

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?

AD100:  =IF(AD94="Yes","",IF(AD98="Yes",D114,AE98/12*D114))
AI100:   =IF(AD98="Yes",-D115,-AE98/12*D115)
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?

Thank you so much for your reply and input!  I let this get away from me but I will test and get back to you over the weekend.

[passionfruit17]

I'm just discovering this amazing tool, it's obviously complex and I'm running into issues that are likely my misunderstanding on how to use it, but I can't seem to figure out on my own.

My employer pays $1500 towards my HSA when I choose a HDHP and I pay the rest to max it out including the catchup ($5800): I think I need to put the $1500 (is that what "employee-sponsored means? I would have thought is simply meant offered by your employer and taken from your paycheck rather than paid for separately) and I put the $5800 in B45, Personal HSA? I supposed it doesn't really matter except to check that the tool estimated paycheck matches my actual check to show everything was accounted for.

I TLH'd this year and have about $50K in STCG (no carry-overs). when I enter -50,000 in cell D26, I properly see the 1040 income D42 go down by $3000 as expected, but the amount in B42 goes down by $50K, which I did not expect. As a result, the monthly budget calculations are thrown off (B73 and below) because hte tool thinks my income is %50K lower than it is. What am I doing wrong?

[MDM]

I'm just discovering this amazing tool, it's obviously complex and I'm running into issues that are likely my misunderstanding on how to use it, but I can't seem to figure out on my own.

Good that you found this thread - that's what it's here for!

My employer pays $1500 towards my HSA when I choose a HDHP and I pay the rest to max it out including the catchup ($5800): I think I need to put the $1500 (is that what "employee-sponsored means? I would have thought is simply meant offered by your employer and taken from your paycheck rather than paid for separately) and I put the $5800 in B45, Personal HSA? I supposed it doesn't really matter except to check that the tool estimated paycheck matches my actual check to show everything was accounted for.

"Employer-sponsored" means the HSA contribution occurs via payroll deduction.   Row 45 is for those who contribute to an HSA by sending a check to Fidelity (or whomever).

The HSA employer match can go in B19 "employer match" along with the 401k employer match, or the total (contributions + employer match) can go in "employer-sponsored HSA".   If the latter, you have to add the HSA match to "gross salary/wages" to keep everything consistent.

I TLH'd this year and have about $50K in STCG (no carry-overs). when I enter -50,000 in cell D26, I properly see the 1040 income D42 go down by $3000 as expected, but the amount in B42 goes down by $50K, which I did not expect. As a result, the monthly budget calculations are thrown off (B73 and below) because hte tool thinks my income is %50K lower than it is. What am I doing wrong?

Hah, good point!  Maybe I should just delete the calculations in B42 and C42, because those cells' results aren't used anywhere.  I'll think about that some more...might just have to included a note saying "one time events, especially those that occur within one's investment accounts, may incorrectly affect budget calculations" or something like that.

If the "-50,000 in cell D26" leads to a correct tax calculation for this year (I think it does - do you see anything to the contrary?) then that may be as good as we can hope for.

Presumably (and we hope) the $50K TLH is a one time event.  I.e., you don't plan on losing $50K in each subsequent year.  Thus that amount would not be a useful input to the "simplified evaluation of 'how long to Financial Independence?'" section in row 165 and below.

Feedback from first-time users such as yourself is great, because fresh eyes see things "more experience" eyes glaze over.  Any specific suggestions on documentation/feature additions that would be (or would have been) helpful?  Thanks!

[passionfruit17]

I'm mostly using this for looking at this year (taxes, will I exceed the MAGI for IRA if I switch to Roth 401(k) for example, etc...) so I'm fine informing the budgeting/projections. I know what I spend pretty well and how much is leftover to invest in taxable for example, so I was more exploring the sheet than trying to get budget right.

Thanks for the help, I wouldn't be surprised if I have more questions as I play with it more. I've barely glanced at the SD tab for example although I'm pretty sure I have another spreadsheet that does the same but it's always fun to compare different tools and understand how they differ.

EDIT: the tax calculations see reasonable, so I have no reason to suspect they are messed up, but not qualified to know if they're right when I add ST/LT losses in, that's the main reason I started looking at this spreadsheet :)

[passionfruit17]

It looks like B63:B64 show income taxes for both earners, I expected to see the tax share of earner 2 in C63:64 instead of instructions/info.
It makes sense though, for MFJ it's the total of both incomes that matter, each individual is not taxed separately, but at first it was confusing me.

[MDM]

EDIT: the tax calculations see reasonable, so I have no reason to suspect they are messed up, but not qualified to know if they're right when I add ST/LT losses in, that's the main reason I started looking at this spreadsheet :)

They should be fine for that scenario, because that's something that has been tested multiple times.  The rest of the calculations should also be fine, based on testing a variety of inputs against the results from a couple of commercial programs in previous years.

When the tax law itself changes, that opens more room for error, but when there aren't any law changes except for inflation adjustment of brackets, etc., it's highly likely that what worked in previous years continues to work for the current tax year.  Not an ironclad guarantee (pretty much any software ever written has a bug or two), but.... :)

[MDM]

It looks like B63:B64 show income taxes for both earners, I expected to see the tax share of earner 2 in C63:64 instead of instructions/info.
It makes sense though, for MFJ it's the total of both incomes that matter, each individual is not taxed separately, but at first it was confusing me.

Exactly.  There just aren't that many Married Filing Separately - and for that matter Qualifying Widow(er) - situations to add the complexity of forcing all income to be split between "Spouse A" and "Spouse B" so the total MFS tax could be compared with the MFJ tax.

[MDM]

I TLH'd this year and have about $50K in STCG (no carry-overs). when I enter -50,000 in cell D26, I properly see the 1040 income D42 go down by $3000 as expected, but the amount in B42 goes down by $50K, which I did not expect. As a result, the monthly budget calculations are thrown off (B73 and below) because hte tool thinks my income is %50K lower than it is. What am I doing wrong?

Hah, good point!  Maybe I should just delete the calculations in B42 and C42, because those cells' results aren't used anywhere.  I'll think about that some more...might just have to included a note saying "one time events, especially those that occur within one's investment accounts, may incorrectly affect budget calculations" or something like that.

If the "-50,000 in cell D26" leads to a correct tax calculation for this year (I think it does - do you see anything to the contrary?) then that may be as good as we can hope for.

Presumably (and we hope) the $50K TLH is a one time event.  I.e., you don't plan on losing $50K in each subsequent year.  Thus that amount would not be a useful input to the "simplified evaluation of 'how long to Financial Independence?'" section in row 165 and below.

Feedback from first-time users such as yourself is great, because fresh eyes see things "more experience" eyes glaze over.  Any specific suggestions on documentation/feature additions that would be (or would have been) helpful?  Thanks!

After thinking for a bit, deleting the calculations in B42 and C42 seems appropriate because they weren't adding any value and could cause distractions. 

It's also likely that any capital loss is a one-time thing (often for TLH), and plays no part in current or future cash flows.  Given that, using "the larger of zero or the entered value" for STCG and LTCG in D73 and D147 seems appropriate.  It's possible that some capital gains would also be one-time things, but not as likely as for capital losses.

At least, that's where a little thinking has led and those changes are in the test version for the next release....

[passionfruit17]

I just got a bonus and it pushed me over the MAGI to qualify for tIRA/Roth IRA ($204K to $214K this year). The only "trick" I think I can use was to contribute to my traditional 401(k) (my employer offers both), but it looks like I have crossed the line where I will qualify for IRA contributions for the foreseeable future unless I don't get a bonus next year. By the way, the "over IRA limit?" message in L42 was confusing me. I thought it meant I was contributing too much to the IRA ($7K since I'm over 50) when I think it was telling me I was over the MAGI limit to qualify for IRA. Maybe the message should say "over MAGI IRA limit" in one case and "over IRA contribution limit" in the other?

Since I was doing traditional (pre-tax) 401(k) to be able to contribute to IRAs, and that reason had gone away, I now need to decide if it's worth my time doing Roth 401(k). I think the tool for that is "Misc Calcs" row 150? I need to read through the Boglehead content and others to understand what numbers I need to plug in (I think B162 for me should be 15% not 20% for example but need to figure out what they mean). Today my income (with trad 401k) puts me at the bottom of the 24% bracket whereas I would be at the bottom of the 22% bracket in retirement (using today's numbers even though they are likely to change)

I also plan to retire at 62, so possibly 10 years before RMDs and want to understand if/how much I should do Roth conversions during that time. In today's number, my retirement needs ($100 post-tax as a round number) put me at the top of the 12% bracket or the bottom of 22% depending on where I draw the money from (how much in taxes to get to $100K post taxes). This means that Roth conversion would likely be in the 22% bracket and if worth doing at that rate could go all the way to the top of the bracket (about $90K in conversion every year?). Is there a tool to decide whether I should plan on converting to the top of 22% or even at 24%?

Also is there a tool to decide if it's worth doing a backdoor roth? I'd have to move a sizable rollover IRA ($800K+) into my 401(k) and my 401(k) as has a 0.04% administration fee on top of what a similar fund would cost in my Fidelity rollover. so it's costing me $3200 a year (compounded) to hold the money in the 401(k), so the advantage of a backdoor Roth needs to be at least that much to break even, so I think I shouldn;t backdoor.  My 401(k) plan won't support mega backdoor.

All rates are federal only, add 4.55% for state

I realize these are both questions about the tool and advice about my particular situation, so I need ot post somewhere else for non-generic tool questions (I'm new to this forum)

[MDM]

By the way, the "over IRA limit?" message in L42 was confusing me. I thought it meant I was contributing too much to the IRA ($7K since I'm over 50) when I think it was telling me I was over the MAGI limit to qualify for IRA. Maybe the message should say "over MAGI IRA limit" in one case and "over IRA contribution limit" in the other?

Thanks for that feedback.  I'll have to think about that, especially for the phase-out situations in which one could say "income too high" or "deduction (for tIRA) / contribution (for Roth) too high", depending on which way one looks at it.

Since I was doing traditional (pre-tax) 401(k) to be able to contribute to IRAs, and that reason had gone away, I now need to decide if it's worth my time doing Roth 401(k). I think the tool for that is "Misc Calcs" row 150? I need to read through the Boglehead content and others to understand what numbers I need to plug in (I think B162 for me should be 15% not 20% for example but need to figure out what they mean). Today my income (with trad 401k) puts me at the bottom of the 24% bracket whereas I would be at the bottom of the 22% bracket in retirement (using today's numbers even though they are likely to change)

Yes, if you can contribute the maximum to the Roth choice, the fair comparison to traditional includes the need for some taxable contribution to make the total pre-tax amount going to Roth equal the total pre-tax amount going to traditional+taxable.  When entering tax rates, include any state tax rates that apply.

I also plan to retire at 62, so possibly 10 years before RMDs and want to understand if/how much I should do Roth conversions during that time. In today's number, my retirement needs ($100 post-tax as a round number) put me at the top of the 12% bracket or the bottom of 22% depending on where I draw the money from (how much in taxes to get to $100K post taxes). This means that Roth conversion would likely be in the 22% bracket and if worth doing at that rate could go all the way to the top of the bracket (about $90K in conversion every year?). Is there a tool to decide whether I should plan on converting to the top of 22% or even at 24%?

The Roth conversion choice is similar to the traditional vs. Roth contribution choice: compare a known marginal tax rate now to an estimated tax rate later.  There is even a similarity to the "contribute the maximum" situation discussed above, if you pay the conversion tax out of taxable funds.  The Bogleheads wiki on Roth IRA conversion goes into more detail.

Also is there a tool to decide if it's worth doing a backdoor roth? I'd have to move a sizable rollover IRA ($800K+) into my 401(k) and my 401(k) as has a 0.04% administration fee on top of what a similar fund would cost in my Fidelity rollover. so it's costing me $3200 a year (compounded) to hold the money in the 401(k), so the advantage of a backdoor Roth needs to be at least that much to break even, so I think I shouldn;t backdoor.  My 401(k) plan won't support mega backdoor.

I think your analysis here is all the tool you need. :)

I realize these are both questions about the tool and advice about my particular situation, so I need ot post somewhere else for non-generic tool questions (I'm new to this forum)

Whether new or not, all good questions!

[dandarc]

Is that a 0.04% fee ($320 per year on an $800K balance) or 0.4% ($3,200)?

No idea where the line is for you on this, but if your 'additional fees to hold in 401K' is off by an order of magnitude, I could see that swaying the call for you regarding the backdoor Roth.

[passionfruit17]

sorry, 0.04% / $320 I messed up. Less clearcut now :)

[MDM]

sorry, 0.04% / $320 I messed up. Less clearcut now :)

You're probably in coin-flip territory in that case.

For a quick approximation, you could use the "Growth in a taxable account" section (Misc. calcs tab rows 129-148).  Changing C131 and C139 to zero makes "Case B" act like a Roth.  Changing C137 to =B137-320 approximates the cost of the 401k .04% fee.

Setting B136=0, B137=7000, and using the other numbers already entered for Case A (without any comment on the likelihood they will be correct going forward), facilitating the backdoor Roth loses for several years, then catches up between 7 and 8 years, and stays favorable after that.  But it's not a huge effect, so either way is defensible.

[passionfruit17]

Another question:

My wife started a business this year (in addition to a part-time W2 job) and according to her accountant we can deduct enough of her startup costs to get our MAGI back into IRA contribution territory, so I was trying to plug those numbers in the sheet.
I am covered by a 401(k) at work but my spouse is not, even though she has W2 income, my understanding is that it changes the tIRA contribution income limit from $109k to $204k (which happens to match the Roth limit). I got the non-covered spouse numbers from here: https://www.forbes.com/advisor/retirement/ira-contribution-limits
I think that means that G45 should be $204k, not $109K? How do I tell the spreadsheet my spouse is not covered by a plan at work? or am I confused?

My widf has enough W2 wages to fully fund a tIRA, although I think that even if she didn't we can complement under "spousal IRA".

[MDM]

I think that means that G45 should be $204k, not $109K? How do I tell the spreadsheet my spouse is not covered by a plan at work? or am I confused?

Enter what you know is correct.  As it says on row 40 of the Instructions, " - No check is made to enforce compliance with the various IRS rules on IRA, 401k, HSA, etc. contributions"

But if you want the - advisory only - comments in K41:M46 to be more accurate, use L39 and M39 to denote work plan coverage.

[passionfruit17]

Thanks for the help, I wish I *knew* that what I think is correct, so second-guessing of/when the magic spreadsheet doesn't say the same thing :)
I had missed the L39/M39 section, perfect, it was user error!

[MDM]

Version 22.03 (2022)
Mostly a bunch of niche issues, but always of interest to those in such a niche. ;)

• Added the ability to have a "free form input" (row 129) entry for the marginal rate chart
• Cleaned up some temporary calculations at the bottom of the Misc. calcs tab
• Added space for "free form calculations" on the State Tax tab
     - See recent discussion in this thread about CA and MI state taxes and the worked example for a MI credit on that row.
     - Thanks Paul der Krake, Telecaster, Jason95357, and an anonymity-requesting person from MI.
• Eliminated the separate "1040 Total Income" subtotals for earners #1 & #2: this tool isn't set up to do MFS taxes and those cells were confusing and not used anyway (thanks passionfruit17).
• Account for unused W-2 dependent care benefit
• Assume capital losses are one-time things (e.g., a paper loss for tax loss harvesting) and do not affect cash flow
• Non-itemized charitable deduction applicable only to 2021
• Use the Tax Code tab values for >65 std. deduct. values in the "traditional balance needed..." tables.
• Check if W-2 401k plus SE 401k contributions over the maximum
• Use of 133% of FPL for ACA calculations with unemployment applies only to 2021
• ACA changes for part-year situations per mangorunner's comments (see previous posts).

Use of the free form input for the marginal rate chart is limited only by one's imagination.  One example:
   - MFJ both under age 50, neither participating in a workplace retirement plan, and $50K W-2 income.
   - Use cell F129 as "Total IRA contribution" and enter that text in H129.
   - Enter F129 in cell G107 and click the "Update chart" button
   - in cell B50, enter =IF(F129<=2000,F129,IF(AND(F129>2000,F129<=4000),2000,IF(AND(F129>4000,F129<=8000),F129-2000,6000)))
   - in cell C50, enter =F129-B50
      - Those formulas give the maximum saver's credit for the couple as their combined tIRA contribution increases.
   - Change cell P83 to 24.
   - This gives the chart below:



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:

• 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 ;)
  - For the 2021 version, click the "Download" icon at the top right after the file opens.
  - For the 2022 version, 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.