Case Study Spreadsheet updates

[MDM]

Version 21.06 (2021)

2021

• Child and Dependent Care Credit "smoothing"

Child and dependent care credit
Implemented a continuous approximation to the actual law.  This allows the marginal rate chart for a family with
- Earner #2 wages = $25,100
- One dependent age 10, another age 5
- Dependent care cost = $16,000

to look like


instead of



Those who prefer to see the discontinuous marginal rates of the tax code (for this and all other included features) in all their glory may change cell R80 from Y to N.

2021 taxes version: 2021 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 ;) - click the "Download" icon. 
  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.

[MDM]

Version 21.07 (2021)

2021

• Improved the tax estimate in the "Time to FI" section
• Added EIP#3 default payment.  Only those who did not receive the full $1400/person will need to override.
• Updated childcare pre-tax limit to $10,500 in Form 2441
• Provided an SEHI+PTC iteration capability

Thanks to MountainLakeMaka and Harry Sit for insights and suggestions on these updates.

SEHI+PTC iteration capability
The spreadsheet is now attempting to handle the iterative calculations when both Premium Tax Credits (PTC) and Self-Employed Health Insurance (SEHI) premiums are involved, at least for one Schedule C and one SEHI premium.  For people with both self-employment income and Affordable Care Act coverage, determining the optimal amount of the SEHI deduction can be an iterative process.  See pp. 57-75 of https://www.irs.gov/pub/irs-pdf/p974.pdf.

These calculations have been tested and appear to converge reliably over a wide range of conditions. To enable this capability,
    - Enter the appropriate values in cells B114, B115, AD123 and AE100
    - Enter the formula =AD159 in cell B48.  This enables the iteration
    - Entering a specific value, e.g., 0 (zero) in cell B48, or deleting the cell contents, will eliminate the iteration.
    - Ensure the "Enable iterative calculation" box is checked in File > Options > Formulas if the iterations should be, but aren't, occurring.
   Iteration requires significant extra calculation and decreases spreadsheet responsiveness.  Useful if needed, but avoid it otherwise.
   If many #Value, #DIVbyZero, etc., errors propagate, put a specific value in cell B48, fix the root cause, and re-enable iteration.  If errors persist, reply or PM.

At least a couple of things are not clear:

• Whether the spreadsheet correctly implements the IRS iteration procedure.  AFAIK there are no 2021 examples for comparison yet.
• Whether the IRS procedure always provides the most beneficial result for the taxpayer.  I may start a separate thread for this specific issue, but meanwhile if anyone knows of 2021 examples for comparison, please reply or PM.

In general, suggestions/comments/corrections on the SEHI+PTC issue are welcome.  Somewhat of a niche issue, but mathematically interesting and financially significant when it occurs.

2021 taxes version: 2021 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 ;) - click the "Download" icon. 
  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.

[jeromedawg]

Couple clarifying questions (sorry if these were already asked prior):

1) When specifying "Guess Time to FI" is the assumption that the "Gross Salary/Wages" (B3 and C3) won't change?

2) What would the right 'procedure' be to map out a cash-out refi where you're taking the cash-out and reinvesting in index funds but also drawing-down from it year over year to supplement your income/earnings (whether pre or post retirement)? I noticed boxes D26 and D27 but if you plug a number into those, it seems the spreadsheet assumes that those are going to be year-over-year numbers versus one-time...?

[MDM]

Couple clarifying questions (sorry if these were already asked prior):

1) When specifying "Guess Time to FI" is the assumption that the "Gross Salary/Wages" (B3 and C3) won't change?

2) What would the right 'procedure' be to map out a cash-out refi where you're taking the cash-out and reinvesting in index funds but also drawing-down from it year over year to supplement your income/earnings (whether pre or post retirement)? I noticed boxes D26 and D27 but if you plug a number into those, it seems the spreadsheet assumes that those are going to be year-over-year numbers versus one-time...?

1) Yes.  More precisely, that they won't change in real terms: inflation is ignored for all calculations.
2) Ignore it?  Probably depends on how significant it is relative to your overall finances.  You might need more sophisticated tools such as those described in Best and/or Recommended Retirement Calculator - Bogleheads.org if fine details are needed.  Note that the error band on assumed return rates usually dwarfs all other items....

[jeromedawg]

Couple clarifying questions (sorry if these were already asked prior):

1) When specifying "Guess Time to FI" is the assumption that the "Gross Salary/Wages" (B3 and C3) won't change?

2) What would the right 'procedure' be to map out a cash-out refi where you're taking the cash-out and reinvesting in index funds but also drawing-down from it year over year to supplement your income/earnings (whether pre or post retirement)? I noticed boxes D26 and D27 but if you plug a number into those, it seems the spreadsheet assumes that those are going to be year-over-year numbers versus one-time...?

1) Yes.  More precisely, that they won't change in real terms: inflation is ignored for all calculations.
2) Ignore it?  Probably depends on how significant it is relative to your overall finances.  You might need more sophisticated tools such as those described in Best and/or Recommended Retirement Calculator - Bogleheads.org if fine details are needed.  Note that the error band on assumed return rates usually dwarfs all other items....

Ah okay thanks.

One other question/clarification: I'm having trouble understanding how a mortgage impacts the "Time to FI" section and particularly the "Projected Expenses in Retirement" - how/where does that all factor in or relate?

[MDM]

One other question/clarification: I'm having trouble understanding how a mortgage impacts the "Time to FI" section and particularly the "Projected Expenses in Retirement" - how/where does that all factor in or relate?

The assumption is that the "Stash needed for retirement" is composed of two terms:
a) (Retirement expenses) / (Safe Withdrawal Ratio), plus
b) Loan balances
A further assumption is that if one has invested the loan balance, the return on those investments will be at least as high as the mortgage interest rate.

So the mortgage doesn't impact the expenses in retirement, because the payments
- do not increase with inflation, and
- usually end prior to the 30 years usually associated with "Safe Withdrawal Ratios".

Make sense?

[jeromedawg]

One other question/clarification: I'm having trouble understanding how a mortgage impacts the "Time to FI" section and particularly the "Projected Expenses in Retirement" - how/where does that all factor in or relate?

The assumption is that the "Stash needed for retirement" is composed of two terms:
a) (Retirement expenses) / (Safe Withdrawal Ratio), plus
b) Loan balances
A further assumption is that if one has invested the loan balance, the return on those investments will be at least as high as the mortgage interest rate.

So the mortgage doesn't impact the expenses in retirement, because the payments
- do not increase with inflation, and
- usually end prior to the 30 years usually associated with "Safe Withdrawal Ratios".

Make sense?

Stupid me... I overlooked plugging in the number to "Current Principal" which was throwing things off. Haha. I think I have a better understanding now...

[johnhenry]

Hi.

I'm using V20.13 (3/3/2021) of the 2020 CSS.  If that's not the latest, could someone point me to it?

I'm having trouble with my

1040 Total Income (cell D42)
1040 AGI  (cell D52)
Taxable (cell G16)

all being "off" (over in the CSS) by the amount of my Qualified Dividends.  Basically, the CSS seems to be including them where the tax return I'm entering them from has them excluded.

thanks in advance....

[MDM]

Hi.

I'm using V20.13 (3/3/2021) of the 2020 CSS.  If that's not the latest, could someone point me to it?

I'm having trouble with my

1040 Total Income (cell D42)
1040 AGI  (cell D52)
Taxable (cell G16)

all being "off" (over in the CSS) by the amount of my Qualified Dividends.  Basically, the CSS seems to be including them where the tax return I'm entering them from has them excluded.

thanks in advance....

If your 1099-DIV has $1000 in box 1a (Total ordinary dividends) and $925 in box 1b (Qualified dividends) the spreadsheet entries would be
Cell D23: $75
Cell D25: $925

Does that make sense?

[johnhenry]

If your 1099-DIV has $1000 in box 1a (Total ordinary dividends) and $925 in box 1b (Qualified dividends) the spreadsheet entries would be
Cell D23: $75
Cell D25: $925

Does that make sense?

Thanks.  That got me fixed up.  I see the comment now that asks for "non-qualified dividends" as opposed to "total ordinary dividends".... I'm just used to entering the latter into tax software since that's what's reported in Box 1a.

[drumstache]

One question about the 'Safe Withdrawal Rate', Cell B168.  When I increase that value from 4% to say 6%, the value for 'Stash needed for retirement' in cell B194 decreases.  Shouldn't that number go up if I'm increasing the withdrawal rate?

Very possible I hosed something up, or misunderstanding how this works.

Appreciate the work put into this spreadsheet, great tool, Thanks!


 

[MDM]

One question about the 'Safe Withdrawal Rate', Cell B168.  When I increase that value from 4% to say 6%, the value for 'Stash needed for retirement' in cell B194 decreases.  Shouldn't that number go up if I'm increasing the withdrawal rate?

Very possible I hosed something up, or misunderstanding how this works.

Appreciate the work put into this spreadsheet, great tool, Thanks!

See Safe withdrawal rates (SWR) for details, but in short the equation is "Spending = SWR * Stash", or "Stash = Spending / SWR".  As your estimate of SWR goes up, the required stash goes down.

Of course, the "S" part of the acronym is known only in hindsight....

[drumstache]

See Safe withdrawal rates (SWR) for details, but in short the equation is "Spending = SWR * Stash", or "Stash = Spending / SWR".  As your estimate of SWR goes up, the required stash goes down.

Of course, the "S" part of the acronym is known only in hindsight....

Ah, makes sense now.  Thanks for the clarification!

[MDM]

Version 21.08 (2021)

2021

• Allow refundable dependent care credit for lower incomes
• Added cell B6 (Daycare FSA) as an allowable x-axis variable for the marginal rate chart

SEHI+PTC iteration capability update
"Worksheet Y" has been added, but none of its calculations are currently used.  With the elimination of the >400% FPL cliff, the IRS will be changing some things in this area.  If anyone is interested in collaborating on more work in this area, let me know (reply here or PM).  Otherwise, the fact that iteration converges reliably(?) for at least "straightforward" situations may be good enough for estimation purposes....

2021 taxes version: 2021 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 ;) - click the "Download" icon. 
  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.

[jeromedawg]

Kind of an off-topic but related question here but what is your guys' general opinion of the "Have $XXXXX extra" amount and what you *prefer* that number to be in order to feel comfortable about RE?

Was also wondering about the state abbreviation in H32 - I tried entering "CA" and it doesn't seem to be able to recognize it - EDIT: nm, I entered a couple other states and it eventually populated.

[MDM]

Version 21.09 (2021)

• Added entry for advance Child Tax Credit payments
• Updated line number references to some IRS forms
• Excluded pensions from Illinois state tax
• Added some options to the "Traditional assets needed to reach..." tables
• Added some options to allow more "exact estimates," e.g., number of blind filers, use of 2019 earned income, etc.
• Miscellaneous edits to some cell descriptions and comments
• Added "marginal tax rate cost of selling equities" to the "Roth vs. Traditional when contributing the maximum allowed" calculation in the 'Misc. calcs' tab

2021 taxes version: 2021 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 ;) - click the "Download" icon. 
  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.

[getmoneyeatpizza]

"Enter the amount of Child Tax Credit already received"

Should this be the amount expected to be received? Specifically for purposes of trying to estimate my tax return. I will get $3300 advance CTC this year so should I just deduct this amount from the expected return amount?

[MDM]

"Enter the amount of Child Tax Credit already received"

Should this be the amount expected to be received? Specifically for purposes of trying to estimate my tax return. I will get $3300 advance CTC this year so should I just deduct this amount from the expected return amount?

Yes, that's it for the CTC.

For the purpose of estimating the refund or amount owed when filing, one also needs to account for withholding and estimated tax payments.  See cells I70 and I71, and the rest of the "Form W-4" section for that.

In general I'd suggest calculating the tax liability and then adjusting W-4s as needed to owe ~$0 when filing, but individual circumstances can vary.

[MDM]

See How do RMDs affect Roth conversion choices? for some work to address that question.  Seems it ought to be a relatively straightforward calculation but something isn't adding up.  If you have some spreadsheet ability (or can do the analysis in any programming language) your input would be appreciated.

[kate1243hep]

There are Qualified Charitable Distributions (QCDs) but that's a different topic.

Do we just subtract QCDs from box 31D or do they go elsewhere?

[MDM]

There are Qualified Charitable Distributions (QCDs) but that's a different topic.

Do we just subtract QCDs from box 31D...?

Yes, exactly that!

[gamedays]

Hello and thank you for creating this spreadsheet, it is fantastic. Unless I'm doing something wrong, I see one error: it seems that you have unemployment income taxable on the California state taxes. UI is not taxable in CA - it is taken as a deduction on column B of the CA adjustments section of Schedule 540. If I am correct here, is this something you can fix? Thank you.

[MDM]

Hello and thank you for creating this spreadsheet, it is fantastic. Unless I'm doing something wrong, I see one error: it seems that you have unemployment income taxable on the California state taxes. UI is not taxable in CA - it is taken as a deduction on column B of the CA adjustments section of Schedule 540. If I am correct here, is this something you can fix? Thank you.

That's not an error, it's a missing feature. ;)

Without committing to incorporate all of the CA state tax code, that one seems simple enough to go in the next release.  Meanwhile, you could try the following to see if it gives the desired results:
- put =Calculations!D37 into 'State Tax'!E14
- put =-E14 into 'State Tax'!P27

Does that work?

[gamedays]

That highlighted the feature (or fixed the bug, tomato tomahto :)) perfectly! Thank you very much.

[MDM]

That highlighted the feature (or fixed the bug, tomato tomahto :)) perfectly! Thank you very much.

Thanks for the feedback!

State Income Tax calculations - Crowdsourcing request has other examples of state tax details that have (and have not) been added over time.

[john6221]

Hi MDM. Thanks so much for putting so much effort into this spreadsheet - it looks great.

I would like to use it to estimate my taxes for 2021, but I'm a little confused about if I need to do something for RSUs. I'm a little bit of a newbie to doing my own taxes, but it appears that RSUs are reported as regular income. So it would seem that I don't need to do anything special when filling out this spreadsheet, other than making sure that I'm reporting everything as it would show up on my W-2. Does that sound right? Is there anything that I need to consider for the Box 12 Code V, " Income from exercise of non-statutory stock option(s)"?  I just wasn't sure if there was some threshold that triggered something that I didn't consider.

Thank you.

[MDM]

So it would seem that I don't need to do anything special when filling out this spreadsheet, other than making sure that I'm reporting everything as it would show up on my W-2. Does that sound right?

Yes.

Is there anything that I need to consider for the Box 12 Code V, " Income from exercise of non-statutory stock option(s)"?

Not for the purposes of the spreadsheet.  That amount will already be included in your W-2 Box 1, so as far as the W-2 is concerned it's only for informational purposes.

But when you do your actual tax return, be sure to include that amount in the basis when you report the sale of those shares.  See http://www.ashmeadcpa.com/uploads/1/2/7/7/12773217/stock_options_-_same_day_sales.pdf and Your 1099-B form for an ESPP sale will probably be wrong for more on that.

[Mrs. Green]

@MDM I am so thankful you took the time to create Version 21.09 (2021).  Thank you so much!

[wbyoung]

@MDM this spreadsheet is great. Some users may have already mentioned the fact that functionality is limited within Google Sheets. The Tax Rate vs. tIRA Withdrawal graph is an example that I just bumped into. The source data appears to use a formula that isn't supported within Google Sheets.

I opened this up in the web version of Excel, but couldn't look much deeper because the web version doesn't let you unlock sheets. So all I see is that the source data for that graph (cells Q84:R584) all contain the "formula" {=TABLE(,D31)} which maybe is indicating that there's some even more advanced setup going on here since TABLE isn't even listed in the Excel function list I looked up.

I'm sure you're already aware that this spreadsheet is linked on the Bogleheads wiki. They do note that works best in Excel, but it's hosted on Google, so it gives options to open in Google Sheets. I wouldn't be surprised if others have opened it in Google Sheets as I had hoping it'd work quite well (and in fact, a lot does seem to work well).

I'd love to help in whatever way I can to make this more compatible with Google Sheets if you're open to it. If not, is there any insight you could share as to the functionality of the cells for that graph so I may be able to recreate something personally that works similarly to this?

[secondcor521]

@MDM this spreadsheet is great. Some users may have already mentioned the fact that functionality is limited within Google Sheets. The Tax Rate vs. tIRA Withdrawal graph is an example that I just bumped into. The source data appears to use a formula that isn't supported within Google Sheets.

I opened this up in the web version of Excel, but couldn't look much deeper because the web version doesn't let you unlock sheets. So all I see is that the source data for that graph (cells Q84:R584) all contain the "formula" {=TABLE(,D31)} which maybe is indicating that there's some even more advanced setup going on here since TABLE isn't even listed in the Excel function list I looked up.

I'm sure you're already aware that this spreadsheet is linked on the Bogleheads wiki. They do note that works best in Excel, but it's hosted on Google, so it gives options to open in Google Sheets. I wouldn't be surprised if others have opened it in Google Sheets as I had hoping it'd work quite well (and in fact, a lot does seem to work well).

I'd love to help in whatever way I can to make this more compatible with Google Sheets if you're open to it. If not, is there any insight you could share as to the functionality of the cells for that graph so I may be able to recreate something personally that works similarly to this?

TABLE is probably a named range, not a function.  I'd be surprised if Google Sheets didn't support named ranges, though, so I sort of doubt that's the compatibility problem.

I think the curly braces make it a matrix function, but it's been a while since I've had to do advanced Excel stuff.

I'd also offer to make it compatible with LibreOffice, but I think my lazy workaround is going to be to go to my local public library where I can use Official (tm) Excel.

And I think it's a great resource and am very appreciative to @MDM and anyone else who has contributed to it.

[MDM]

I'd love to help in whatever way I can to make this more compatible with Google Sheets if you're open to it.

I'd also offer to make it compatible with LibreOffice....

Happy to take whatever help either of you could provide.  Any Google Sheets knowledge? and Any LibreOffice Calc knowledge? didn't pan out, but that was a few years ago, so...?

Calculate multiple results by using a data table is one source that describes what the Excel "Table" function does.

[fdubz]

@MDM, thank you so much for creating and updating these sheets!  I depend on them every year.  Do you have an estimate roll out for the 2022 sheet?

[MDM]

@MDM, thank you so much for creating and updating these sheets!  I depend on them every year.  Do you have an estimate roll out for the 2022 sheet?

I was just thinking about this question yesterday. :)

Given all the uncertainty regarding which "only for 2021" tax changes would be extended to 2022, and that some 2021 things still aren't finalized, I'll probably wait until it appears the dust is settling in January.   Of course, Congress can change things whenever Congress wants to change things so...?  Best guess for the 2022 version at this point is "mid-January 2022" but if Congress is still embroiled in tax code debate, then...?

[2Muchfun]

I imagine that this is my error. I am plugging in various Roth Conversion amounts and when I go from ~$60K AGI to ~$70K AGI, my Excess APTC tax jumps from $2700 to $20,112. Can you suggest what I might be doing wrong? This would be reasonable last year with the ACA Cliff, but shouldn't jump this year. I am using V21.09 version of the spreadsheet. TIA.

[MDM]

I imagine that this is my error. I am plugging in various Roth Conversion amounts and when I go from ~$60K AGI to ~$70K AGI, my Excess APTC tax jumps from $2700 to $20,112. Can you suggest what I might be doing wrong? This would be reasonable last year with the ACA Cliff, but shouldn't jump this year. I am using V21.09 version of the spreadsheet. TIA.

You might check Harry Sit's excellent article, Roth Conversion and Capital Gains On ACA Health Insurance, that does a walk-through of the common inputs.

If that doesn't help, and comparing the Form 8962 section that starts in cell AC88 to how you would fill the 2021 version of 8962 (see https://www.irs.gov/pub/irs-dft/f8962--dft.pdf and https://www.irs.gov/pub/irs-dft/i8962--dft.pdf) doesn't shed any light, you could save a copy of your file to a google drive, dropbox, etc., and PM a link to me. 

[2Muchfun]

Thanks. I missed an entry for SLCSP. All is good now.

[BoonDogle]

This is a great tool.  Many thanks for doing this MDM.

[mangorunner]

MDM,

I cannot thank you enough for this phenomenal spreadsheet.  I know that an incredible amount of (ongoing) work goes into building it, and I found it invaluable in forecasting and trying "what-if" scenarios for this year's taxes.  Thank you so much!

[MDM]

Quick question:  I am using your Version 21.09 and cannot find where self-employment taxes are added to total tax.  I am referring to Form 1040, Line 23 "Other taxes, including self-employment tax, from Schedule 2, line 21". 

I see SE tax calculated in cell D66.  I see Form 1040 Line 16-1040 Tax (cell G17) and "Tax after n-r credit" (cell G25) and Net tax (cell G33).  But I don't see my SE tax added into the Net Tax.  Would you please help?  Thanks!

That's a good point, and I've more or less been waiting for someone to ask about that before fixing it.  So now that you have, .... :)

The CSS pays some attention to the Form 1040 structure, but ignores things such as amounts withheld (except in the W-4 suggestion section starting in F57) that go into the refund or amount owed when filing.  The SE tax is included if one plots "Total" taxes in the marginal rate chart starting in cell F82, but it wouldn't hurt to add it to G33 also.

For a temporary fix (who knows?  This might be how the "permanent" fix is implemented) you could change the formulas in the cells below:
G33: =G25+G26+G27-G28-G29-G30-G31-G32-G21+D66
D67: =SUM(D60:D65)
T45: =D63+D64  (in other words, delete +D66)
I69: =D63  (in other words, delete +D66)
C66: =IF($R$80="Y",...  (in other words, just change "S80" to "$R$80" and keep the rest of that formula intact)

That should not affect the "Total tax marginal rate" curves but should give you a better G33 result.  Please let me know how it goes if you try it.

Calculations when there is more than one Schedule C in a return, whether that is multiple distinct businesses for one person or spouses each with a business, can get complicated (or at least I perceive they can get complicated).  There are differences in how cell B30 influences other cells vs. how C30 influences other cells.  Those differences may be sufficient for some situations with two Schedules C, but perhaps not all.  Anyone dealing with those complications in real life should probably use an accountant specializing in small business tax rather than use the CSS, but I'm open to incremental suggestions in that area.

Thanks and good luck!

[mangorunner]

Quick question:  I am using your Version 21.09 and cannot find where self-employment taxes are added to total tax.  I am referring to Form 1040, Line 23 "Other taxes, including self-employment tax, from Schedule 2, line 21". 

I see SE tax calculated in cell D66.  I see Form 1040 Line 16-1040 Tax (cell G17) and "Tax after n-r credit" (cell G25) and Net tax (cell G33).  But I don't see my SE tax added into the Net Tax.  Would you please help?  Thanks!

That's a good point, and I've more or less been waiting for someone to ask about that before fixing it.  So now that you have, .... :)

The CSS pays some attention to the Form 1040 structure, but ignores things such as amounts withheld (except in the W-4 suggestion section starting in F57) that go into the refund or amount owed when filing.  The SE tax is included if one plots "Total" taxes in the marginal rate chart starting in cell F82, but it wouldn't hurt to add it to G33 also.

For a temporary fix (who knows?  This might be how the "permanent" fix is implemented) you could change the formulas in the cells below:
G33: =G25+G26+G27-G28-G29-G30-G31-G32-G21+D66
D67: =SUM(D60:D65)
T45: =D63+D64  (in other words, delete +D66)
I69: =D63  (in other words, delete +D66)
C66: =IF($R$80="Y",...  (in other words, just change "S80" to "$R$80" and keep the rest of that formula intact)

That should not affect the "Total tax marginal rate" curves but should give you a better G33 result.  Please let me know how it goes if you try it.

Calculations when there is more than one Schedule C in a return, whether that is multiple distinct businesses for one person or spouses each with a business, can get complicated (or at least I perceive they can get complicated).  There are differences in how cell B30 influences other cells vs. how C30 influences other cells.  Those differences may be sufficient for some situations with two Schedules C, but perhaps not all.  Anyone dealing with those complications in real life should probably use an accountant specializing in small business tax rather than use the CSS, but I'm open to incremental suggestions in that area.

Thanks and good luck!

Oh, wow, and see, I just now deleted my question because I thought I finally figured out that "final tax' is down in D67, (not G33).  Thank you for the detailed options to correct. I'll be about it, now and get back to you. 

Two more questions while I have you here:

1)  Is the correct way (in your spreadsheet) to enter $300 in contributions (for a Form 1040 Line 12b deduction), to enter $25 in cell B86?

2)  I earn some wages as an election/poll worker that are not subject to Social Security and Medicare (FICA) Withholding (https://www.irs.gov/government-entities/federal-state-local-governments/election-workers-reporting-and-withholding).  I found that putting them in cell B3 doesn't work (even though they are Box 1 wages on a W-2).  Should I put those in cell D40?  (That's what I did.)

Thanks, again, for all the help!  You are a godsend.

[MDM]

By the way, welcome to the forum mangorunner.

1)  Is the correct way (in your spreadsheet) to enter $300 in contributions (for a Form 1040 Line 12b deduction), to enter $25 in cell B86?

2)  I earn some wages as an election/poll worker that are not subject to Social Security and Medicare (FICA) Withholding (https://www.irs.gov/government-entities/federal-state-local-governments/election-workers-reporting-and-withholding).  I found that putting them in cell B3 doesn't work (even though they are Box 1 wages on a W-2).  Should I put those in cell D40? 

1) Yes
2) Yes, that will work.  Poll worker wages are an odd thing tax-wise.  They aren't subject to FICA but they do count as earned income for the earned income credit and can be used to justify IRA contributions.  If the earned income credit is of interest, doing odd things in the spreadsheet such as entering the poll worker wages as a negative number in cell B13 will work.  Otherwise just do what you did. :)

[mangorunner]

By the way, welcome to the forum mangorunner.

1)  Is the correct way (in your spreadsheet) to enter $300 in contributions (for a Form 1040 Line 12b deduction), to enter $25 in cell B86?

2)  I earn some wages as an election/poll worker that are not subject to Social Security and Medicare (FICA) Withholding (https://www.irs.gov/government-entities/federal-state-local-governments/election-workers-reporting-and-withholding).  I found that putting them in cell B3 doesn't work (even though they are Box 1 wages on a W-2).  Should I put those in cell D40? 

1) Yes
2) Yes, that will work.  Poll worker wages are an odd thing tax-wise.  They aren't subject to FICA but they do count as earned income for the earned income credit and can be used to justify IRA contributions.  If the earned income credit is of interest, doing odd things in the spreadsheet such as entering the poll worker wages as a negative number in cell B13 will work.  Otherwise just do what you did. :)

Thanks for the welcome to the forum (I'm a long-time lurker!) and your confirmation on those last two questions. 

[mangorunner]

Quick question:  I am using your Version 21.09 and cannot find where self-employment taxes are added to total tax.  I am referring to Form 1040, Line 23 "Other taxes, including self-employment tax, from Schedule 2, line 21". 

I see SE tax calculated in cell D66.  I see Form 1040 Line 16-1040 Tax (cell G17) and "Tax after n-r credit" (cell G25) and Net tax (cell G33).  But I don't see my SE tax added into the Net Tax.  Would you please help?  Thanks!

That's a good point, and I've more or less been waiting for someone to ask about that before fixing it.  So now that you have, .... :)

The CSS pays some attention to the Form 1040 structure, but ignores things such as amounts withheld (except in the W-4 suggestion section starting in F57) that go into the refund or amount owed when filing.  The SE tax is included if one plots "Total" taxes in the marginal rate chart starting in cell F82, but it wouldn't hurt to add it to G33 also.

For a temporary fix (who knows?  This might be how the "permanent" fix is implemented) you could change the formulas in the cells below:
G33: =G25+G26+G27-G28-G29-G30-G31-G32-G21+D66
D67: =SUM(D60:D65)
T45: =D63+D64  (in other words, delete +D66)
I69: =D63  (in other words, delete +D66)
C66: =IF($R$80="Y",...  (in other words, just change "S80" to "$R$80" and keep the rest of that formula intact)

I'm pretty good at Excel (not nearly as good as you, though) and I understand your formula changes.  However, I got tremendous use out of your spreadsheet and everything I needed, so I decided not to change any of your formulas.  Your CSS allowed me to pinpoint my Roth IRA conversion down to the last cent, so thank you very much.  You are my hero.

(It may be worth noting that in the last line of your instructions, above, I think you mean B66 rather than C66?)

[MDM]

I'm pretty good at Excel (not nearly as good as you, though) and I understand your formula changes.  However, I got tremendous use out of your spreadsheet and everything I needed, so I decided not to change any of your formulas.  Your CSS allowed me to pinpoint my Roth IRA conversion down to the last cent, so thank you very much.  You are my hero.

You are welcome!

(It may be worth noting that in the last line of your instructions, above, I think you mean B66 rather than C66?)

Ha!  That's what comes from having accumulated a bunch of changes while waiting for Congress and the IRS to make up their minds about 2022 and even 2021 tax laws.  The formula in C66 should be identical to what is in B66, just referencing different columns - except for the first "if" test that should reference the same cell.  Version 21.10 (in development, not yet released, and what I was looking at to address your question) includes some changes to address the "multiple Schedules C" issue mentioned earlier, and had all the C66 calculations correct but the first "if" test incorrect.  Would I have caught that when going through normal "new release due diligence" without looking at the changes your question prompted?  Don't know but thanks for the prompt!

[mangorunner]

Aw, that's fantastic!  I'm glad you got something good out of it, too!  :)

[MDM]

Version 21.10 (2021)

• Updated Social Security average wage index and maximum earnings for another year.
• Miscellaneous labeling changes (e.g., to conform to line numbers on newly-released IRS forms)
• Excluded unemployment from California state tax
• Implemented the new Child Tax Credit form 8812 for US residents
• Allowed more (but not complete) flexibility for Schedule C incomes for spouses
• Treat self-employment tax as "federal" instead of "payroll"
• Reordered credits to match new Form 1040 ordering better
• Keep endpoint constant on the marginal rate chart as the initial point changes

Although the IRS hasn't released all the 2021 forms and publications (in particular, Publication 974, Premium Tax Credit that I'd like to use for checking the iterative Self-Employed Health Insurance plus Affordable Care Act calculations), there don't appear to have been any significant changes needed in tax calculations from the previous version.  This release cleans up a few things (see above) and will make the next one (whether for 2021 or 2022) that much less of a chore to check for unintended changes.

Most of the bulleted points should be straightforward enough (if not, just ask here or via PM), but "Keep endpoint constant on the marginal rate chart as the initial point changes" probably deserves explanation.  Cell Calculations!P83 is the increment used for the marginal rate chart's X axis, and Calculations!P84 is the starting value for the X axis variable.  If one wants to look at a larger range for the X axis variable, just increase the P83 value.  But if one wants to keep the same ending X variable value while changing the starting value, the formula in P83 (=220-P84/500) does that.  In other words, if P84 is changed from the default of zero, the increment will decrease to keep the same ending value.  The "500" comes from there being 500 points on the chart.  If one wants to keep this functionality but wants to change the ending value, edit cell P83 and change "220" to whatever increment gives the desired result.  If one forgets there is an equation in P83 and simply enters a number, things will still work the same way they have in previous versions. 

2021 taxes version: 2021 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 ;) - click the "Download" icon. 
  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.

[skyFIdive]

Hey @MDM, do you have any update on when the 2022 version will be launched? Debating just adapting my old 2021 version to include confirmed 2022 changes (e.g. 401k limit increase), but figure I’ll probably miss something. Happy to use any draft version you may have! Thank you again for this incredible tool.

[MDM]

Hey @MDM, do you have any update on when the 2022 version will be launched? Debating just adapting my old 2021 version to include confirmed 2022 changes (e.g. 401k limit increase), but figure I’ll probably miss something. Happy to use any draft version you may have! Thank you again for this incredible tool.

I had hoped there would be more clarity out of Congress on which of the significant "2021 only" (e.g., Child Tax Credit, Child/Dependent Care Credit, etc.) tax law changes would in fact be made permanent (or at least extended) through 2022.  But the political gameplaying continues, with no such clarity in evidence....

I suppose if they are still posturing instead of legislating by February I'll bite the bullet and release a "subject to change..." version.

[skyFIdive]

Okay thanks for the update! Will stay tuned.

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