Case Study Spreadsheet updates

[MDM]

#props to the CSS.

Just entered everything into Turbotax and the 2022 CSS (with some mods to handle my S-Corp and other random details about our specific tax situation), and initially it didn't match by a fair amount, and so I of course I investigated. About an hour to find a single checkbox in TurboTax that I had missed related to self-employed health insurance deduction in TurboTax and boom - matches within $1 (and with turbotax rounding all the inputs to the dollar, this level of discrepancy is almost expected). So CSS was correct right away and it was a data entry error I made in TurboTax.

I again will suggest you make a tip-jar for this because this is the best tax-planning thing out there.

Thanks for the endorsement - good to see that it matches the commercial software for your situation.

The Tax-Aide folks publish a bunch of practice returns for training volunteers in that program.  With some jury-rigging to cover oddball situations (e.g., a 10% penalty on the amount of early IRA distribution that exceeds the itemizable medical expense deduction), the CSS matched the answer key they provide for TY2022, so that was also good to see.

Knowing that it is useful to others is sufficient compensation. :)

[dandarc]

Been sick and tired the last week - wow that was some writing straight from the department of redundancy department yesterday.

[MDM]

Version 22.09 (2021/2022/2023)

• State tax parameters updated for 2023
• Default year for federal taxes changed to 2023
• Help with future marginal rate estimates, when considering qualified dividends, pension, Social Security, and traditional withdrawals
• Allow loss carryforward in the QBI deduction calculation

Thanks to the Tax Foundation's State Individual Income Tax Rates and Brackets for 2023, those values will be used for state tax estimates.  If there are some easily added features for your state's taxes, or if the Tax Foundation numbers are incorrect, comments in the State Income Tax calculations - Crowdsourcing request thread are welcome. 

Assuming that by now most people are using commercial tax software for their 2022 returns, the default year in Calculations!R2 is now 2023.  That can be changed if one wants to look at a different year's federal taxes.

When dealing with traditional vs. Roth contributions, or the timing/amount of traditional to Roth conversions, a comparison of current vs. future marginal tax rates is needed.  Current rates can be calculated exactly (that's the main feature of the CSS), but any estimate of future rates is subject to the vagaries of tax law, market returns, and individual circumstances.  See Estimating withdrawal tax rates for more discussion on that.

Although great detail in a future tax estimate isn't justified, some back-of-the-envelope calculations can be worthwhile.  That's what the "Estimating withdrawal tax rates" section that has been around cell Calculations!R60 since March 03, 2021 is for.  To make it clearer how to use this (I hope, although the potential for confusion also exists), formulas that reference those estimated cells have been added to the inputs for qualified dividends, pension, Social Security, and "other taxable income".  All these formulas (see cells D25, B32, B38, and D40 on the Calculations tab) are of the form =Rxx*0.  The "*0" must be deleted if one wants to use the future estimates.  For current year estimates, one can simply type numbers into those cells or add a number after the "*0".  For example, if one wants to enter $1287 for current year qualified dividends, cell D25 could have =R66*0+1287.

The future "other taxable income" entry is the annual traditional withdrawal one expects based on the growth of the current traditional balance.  Then the marginal rate chart that uses cell D31 [B31 since the November 07, 2023 update] for the x-axis shows the marginal rates that additional traditional contributions will encounter when they and their gains are withdrawn.

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

2021/2022/2023 taxes version: 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 ;)
  - After opening the file on Google Drive (link above), click File>Download>Microsoft Excel (xls)
  Then, either open in Excel immediately, edit, and later save from Excel to your computer, or save immediately to your computer and later open and edit that file with Excel.
  
• See
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg2138684/#msg2138684 and
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1879496/#msg1879496
  regarding warnings one might see, and why those are spurious warnings for these files.

[dandarc]

Many thanks for the update - getting to where I should probably be estimating 2023 taxes to decide on a plan regarding withholding should, as I suspect the case will be for 2023 for us, the federal tax figure come in above $0.

[catbend]

Hello I'm eager to keep learning more about my financials with this amazing spreadsheet.  My question is (and I'm sure I'm overlooking it, how do I enter SSN so that I retire at 50, and then begin withdrawals at 62. 

I entered estimated salary until 2030 (50yr) in column B, age in B102.

I'm a little confused by B117 and B119. Is that where I'd put "50" in?

How do I indicate that I intend to draw at 62. I think I understand that my benefit will sit unadjusted (no COLAs) from 2030 to 2042. 

Thanks for any support you might be able to offer. 
~catbend

[MDM]

Hello I'm eager to keep learning more about my financials with this amazing spreadsheet.  My question is (and I'm sure I'm overlooking it, how do I enter SSN so that I retire at 50, and then begin withdrawals at 62.

Great question.  For anyone following along, this refers to the SocialSecurity tab, not the Calculations tab.

I entered estimated salary until 2030 (50yr) in column B, age in B102.

By age I hope you mean birth date, e.g., 1-Jul-1980.

I'm a little confused by B117 and B119. Is that where I'd put "50" in?

No need to enter the age at which you expect to stop earnings for SS purposes.  The absence of any salary/wages in any given year is sufficient.  B117 and C117 are used to enter the age in years and months respectively for the start of benefits. 

B119 can also be used (e.g., enter =62+1/12 for a start age of 62 years and 1 month) with the results in B115:J115.  The main purpose of B119 is to be input for the table in V95:W192, where you can find the annual benefits for all starting months between age 62 and 70 inclusive.  The graph of that table is to the right of the table, with the table numbers in blue and a curve fit in orange.

How do I indicate that I intend to draw at 62. I think I understand that my benefit will sit unadjusted (no COLAs) from 2030 to 2042.

The short answer is "currently you can't, because the spreadsheet only goes through 2040."  I've thought about extending the range (and maybe deleting some of the 1960s rows) and might do so for the next version. 

Meanwhile, using an estimate that starts benefits 17 years from now instead of 19 years from now, and assuming a 1978 birth year instead of 1980, is probably within the error band on what those 2042 benefits (in today's dollars) will be anyway.

If I take the default earnings ("The template scenario is for someone born in 1957 who makes the SS maximum taxable earnings for 40 years, starting in 1983.  This is the same as "Case B" starting in cell T29.") and change the birth date to 1-Jul-1978, the age 62 benefit is $30,684.  Changing cell G104 to 0.5 (so the assumption becomes "...makes 50% of the SS maximum..."), the age 62 benefit is $22,128. 

The extent to which any such estimate is worth the pixels used to display it is debatable, but it is a place to start.  And I probably will extent the date range for the next version....

Thanks for the question!

[Jason95357]

Perhaps I'm not seeing it, but can the SocialSecurity tab add the ability to account for the Windfall Elimination Provision?  Like anything with the government, there are a ton of "if then" rules that apply.  This document appears to sum them up:

https://www.ssa.gov/pubs/EN-05-10045.pdf

In my specific case, I have 15 years of "substantial earnings" before I went to work for a government employer that doesn't pay into SS.  If I read that SS publication correctly, I believe means the first PIA will be reduced from 90% to 40%.

An update to account for WEP would need a handful of input questions that are allowed exceptions to WEP.

[MDM]

Perhaps I'm not seeing it, but can the SocialSecurity tab add the ability to account for the Windfall Elimination Provision?

It is not there now.  If someone were to suggest specific spreadsheet changes, that would greatly increase the likelihood it would be added. ;)

Off the top of my head, Benefits Planner: Retirement | Windfall Elimination Provision (WEP) Online Calculator | SSA is one existing tool that might suffice.

[Jerryday95]

Thank you for creating such an amazing resource.  I have a question re: IRMAA spikes.   Specifically, based on inputs whereby my 2023 MAGI is $58,500, the spreadsheet indicates the first IRMAA spike occurs if I withdraw an additional $143,900 of Traditional IRA income ("Calculations" tab, cell Q261).   Thus, I'm inferring the MAGI threshold assumption here (where IRMAA kicks in) is $202,400. I also assume $202,400 is the projected IRMAA MAGI threshold for 2025 (based on 2023 MAGI), is this correct?   And if so, how was the assumed $202,400 threshold derived?  I appreciate your help here - Thanks!

[MDM]

...how was the assumed $202,400 threshold derived?

That came from 2023 2024 2025 Medicare Part B IRMAA Premium MAGI Brackets when it was first updated for a 2025 estimate.  Inflation has continued to run high since that time, so if you check that article now, the author estimates the first IRMAA tier will occur between $204K (if future annual inflation is 0%) and $216K (if future annual inflation is 5%).

Roth Conversion with Social Security and Medicare IRMAA is an excellent guide if you haven't seen that already.

[Jerryday95]

Thanks MDM - Much appreciated.  So, just to confirm, is the MAGI threshold of 202,400 I inferred correct, in terms of what the spreadsheet is currently assuming? 

[MDM]

Thanks MDM - Much appreciated.  So, just to confirm, is the MAGI threshold of 202,400 I inferred correct, in terms of what the spreadsheet is currently assuming?

Actually $200K for 2022 income affecting 2024 IRMAA.  See the box starting in Calculations!AC76.  For 2023 (change cell Calculations!R2) income affecting 2025 IRMAA, it is $210K.

See the 'Tax Code' tab starting at row 180 for the raw numbers that go to the Calculations tab based on the year chosen.

[normalize]

Thanks so much for updating your spreadsheet to allow a proper estimate of 2023 taxes. One request however: in 2022 the state of New Mexico updated its income tax code to exempt Social Security proceeds for single filers up to $100K and joint filers up to $150K. This is not reflected in the current spreadsheet for either tax year 2022 or 2023. Is this an easy fix?

[MDM]

Thanks so much for updating your spreadsheet to allow a proper estimate of 2023 taxes. One request however: in 2022 the state of New Mexico updated its income tax code to exempt Social Security proceeds for single filers up to $100K and joint filers up to $150K. This is not reflected in the current spreadsheet for either tax year 2022 or 2023. Is this an easy fix?

Might be easy.

If you put
=IF(B3<=IF(B1=1,100000,150000),B12,0)
into cell 'State Tax'!N53, does that give the correct results?

[normalize]

If you put
=IF(B3<=IF(B1=1,100000,150000),B12,0)
into cell 'State Tax'!N53, does that give the correct results?

Thanks a lot, this works perfectly for my filing situation. (If you decide to incorporate this fix into a release version of your spreadsheet, note that there is also a third category: no NM tax on social security proceeds for a married individual filing separately with an income up to $75K.)

[MDM]

If you put
=IF(B3<=IF(B1=1,100000,150000),B12,0)
into cell 'State Tax'!N53, does that give the correct results?

Thanks a lot, this works perfectly for my filing situation. (If you decide to incorporate this fix into a release version of your spreadsheet, note that there is also a third category: no NM tax on social security proceeds for a married individual filing separately with an income up to $75K.)

Thanks for checking.  It's already in the next version, for whenever that comes out.  MFS is so rare, compared with S, MFJ, and HOH, that the effort to include it has never seemed worthwhile.  Gotta draw the line somewhere. ;)

[catbend]

Hello I'm eager to keep learning more about my financials with this amazing spreadsheet.  My question is (and I'm sure I'm overlooking it, how do I enter SSN so that I retire at 50, and then begin withdrawals at 62.

Great question.  For anyone following along, this refers to the SocialSecurity tab, not the Calculations tab.

I entered estimated salary until 2030 (50yr) in column B, age in B102.

By age I hope you mean birth date, e.g., 1-Jul-1980.

Yes, thanks, birthdate

I'm a little confused by B117 and B119. Is that where I'd put "50" in?

No need to enter the age at which you expect to stop earnings for SS purposes.  The absence of any salary/wages in any given year is sufficient.  B117 and C117 are used to enter the age in years and months respectively for the start of benefits. 

B119 can also be used (e.g., enter =62+1/12 for a start age of 62 years and 1 month) with the results in B115:J115.  The main purpose of B119 is to be input for the table in V95:W192, where you can find the annual benefits for all starting months between age 62 and 70 inclusive.  The graph of that table is to the right of the table, with the table numbers in blue and a curve fit in orange.

Ahhh... Now I get it.  Thanks again.

How do I indicate that I intend to draw at 62. I think I understand that my benefit will sit unadjusted (no COLAs) from 2030 to 2042.

The short answer is "currently you can't, because the spreadsheet only goes through 2040."  I've thought about extending the range (and maybe deleting some of the 1960s rows) and might do so for the next version. 

Meanwhile, using an estimate that starts benefits 17 years from now instead of 19 years from now, and assuming a 1978 birth year instead of 1980, is probably within the error band on what those 2042 benefits (in today's dollars) will be anyway.
I backed my actual earnings up two years (to 1978) so I could get all my data into the chart that currently ends in 2040, and with estimating my earned salary as static (even though I'll get the occasional government (read small) raise) I came out to $23,616 in today's dollars.  Side note for other government employees (non special status) the fixed payment part of your portfolio will sit unadjusted from the time you withdraw from govt service (if before the minimum retirement age) until the time when you are able to begin drawing on that fixed benefit.  (read no inflation increases, no cost of living increases).  So in my case (govt employee) it really does mean in today's dollars. 

If I take the default earnings ("The template scenario is for someone born in 1957 who makes the SS maximum taxable earnings for 40 years, starting in 1983.  This is the same as "Case B" starting in cell T29.") and change the birth date to 1-Jul-1978, the age 62 benefit is $30,684.  Changing cell G104 to 0.5 (so the assumption becomes "...makes 50% of the SS maximum..."), the age 62 benefit is $22,128. 

The extent to which any such estimate is worth the pixels used to display it is debatable, but it is a place to start.  And I probably will extent the date range for the next version....

This was actually fairly accurate compared to my data $22 vs $23k.  This work has already been a big help to validate I'm not way out in left field with my SS benefit estimates.  I appreciate you taking the time to tell me how I can "hack" the spreadsheet.  Thanks again MDM.

Cheers! (Class of 2030!)

Thanks for the question!

[fyre4ce]

I don't believe the NY income tax calculation is completely correct. Take a look at Form IT-201 instructions starting on page 45. Some of the tax computation worksheets include a "recapture base amount" and an "incremental benefit amount" that get folded into the tax calculation. The recapture base amount looks like a tax cliff, and the incremental benefit amount phases in over a $50,000 range. Maybe it could be argued that these are TECHNICALLY not income tax because of the name, but they are included in the calculation, are income-dependent, and behave exactly as a tax, so I would think they should be included.

[MDM]

I don't believe the NY income tax calculation is completely correct. Take a look at Form IT-201 instructions starting on page 45. Some of the tax computation worksheets include a "recapture base amount" and an "incremental benefit amount" that get folded into the tax calculation. The recapture base amount looks like a tax cliff, and the incremental benefit amount phases in over a $50,000 range. Maybe it could be argued that these are TECHNICALLY not income tax because of the name, but they are included in the calculation, are income-dependent, and behave exactly as a tax, so I would think they should be included.

It is possible, perhaps even likely, that all state (not to mention local) tax calculations have some deficiency.  I don't envy the commercial tax software vendors who have to keep up with all the different state laws.

But if a particular state situation is simple enough to handle, that can happen (especially if a solution couched in Excel is offered) so thanks for noting this one.

My first (and, so far, only) reaction, however, is that sixteen tax worksheets is not "simple enough" but if there is an Excel formula that would encompass those, then maybe....

If you would like to pursue this, State Income Tax calculations - Crowdsourcing request would be a better thread.  Not a big deal, but having state-specific discussions there is convenient.

[MDM]

Version 22.10 (2021/2022/2023)

• Social Security beginning benefit projections extended to 2050
• Minor changes to MI and NM state tax calculations

The SS benefit calculation extension is based on current SS law.  What benefit reduction, if any, that will occur sometime in the mid-2030s is left for the user to estimate. 

The NM change was the SS benefit subtraction described by normalize, and I've been told the MI flat rate for 2023 is 4.05% instead of 4.25%.

I looked more at the NY state tables.  Between adding HOH calculations (all state tax estimates currently assume either S or MFJ) and adding a calculation method unlike that present in any other state, that still appears too far down the slippery slope that leads to replicating the intricate details of every state's tax law.  But if there is a way to add it without much more complexity than the NM change it could still happen....  For those interested in more detailed state tax calculations, there are the free-form cells described in the Version 22.03 post, and of course there is "Unprotect Sheet" and have at it, for personal use.

Repeating from the last update: Thanks to the Tax Foundation's State Individual Income Tax Rates and Brackets for 2023, those values will be used for state tax estimates.  If there are some easily added features for your state's taxes, or if the Tax Foundation numbers are incorrect, comments in the State Income Tax calculations - Crowdsourcing request thread are welcome. 

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

2021/2022/2023 taxes version: 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 ;)
  - After opening the file on Google Drive (link above), click File>Download>Microsoft Excel (xls)
  Then, either open in Excel immediately, edit, and later save from Excel to your computer, or save immediately to your computer and later open and edit that file with Excel.
  
• See
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg2138684/#msg2138684 and
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1879496/#msg1879496
  regarding warnings one might see, and why those are spurious warnings for these files.

[mjb]

Hey there! I'm not sure when this error cropped up, but there's a problem in the calculation of "Federal tax", cell D63, when a user has self-employment income. This causes an incorrect calculation of "Total income taxes", cell B67.

It looks like D63 gets its value from G35, "Net tax", which adds up 1040 tax, savers credit, self-employment tax, credits, etc.

Self-employment tax is then also added in cell B66, causing a double-counting of tax in B67.

I think changing the calculation in G35 to remove Self-employment tax does the trick, but could someone double-check for me?

Confirmed this was not the case in v21.09, but was as of at least v22.0.

Screenshots attached. Thanks!

[MDM]

Hey there! I'm not sure when this error cropped up, but there's a problem in the calculation of "Federal tax", cell D63, when a user has self-employment income. This causes an incorrect calculation of "Total income taxes", cell B67.

Thanks for looking at details, and for the screen shots! 

Yes, if I recall correctly, at one time the SE tax was not being included in "federal tax", so a change was made to include it in the D63 calculation, primarily so the marginal rate chart would include any SE tax changes when plotting "federal tax".

But (I think) if you look closer at B67, that calculation is correct.  In your example, B67 is the sum of $2,065 + $483 + $1,740 + $630.  The $1,740 is only added once.  Yes, at first glance it appears that B67 would be the sum of B60:B66, but it isn't.

Perhaps B67 should be left blank to avoid this confusion.  It isn't used in any other calculation.  What do you think?

[mjb]

Hey there! I'm not sure when this error cropped up, but there's a problem in the calculation of "Federal tax", cell D63, when a user has self-employment income. This causes an incorrect calculation of "Total income taxes", cell B67.

Thanks for looking at details, and for the screen shots! 

Yes, if I recall correctly, at one time the SE tax was not being included in "federal tax", so a change was made to include it in the D63 calculation, primarily so the marginal rate chart would include any SE tax changes when plotting "federal tax".

But (I think) if you look closer at B67, that calculation is correct.  In your example, B67 is the sum of $2,065 + $483 + $1,740 + $630.  The $1,740 is only added once.  Yes, at first glance it appears that B67 would be the sum of B60:B66, but it isn't.

Perhaps B67 should be left blank to avoid this confusion.  It isn't used in any other calculation.  What do you think?

The calculation of B67 (Total income taxes) is indeed correct -- it adds up Social Security, etc just fine.

The problem is D67 (and therefore G35, "Federal Tax" and "Net Tax"). IMHO it should not include Self-Employment tax because it's not an income tax -- it's a replacement for Social Security and Medicare for self-employed folks, its own separate line items, as you can see from the screenshot of my 2021 1040.

The fact that it's it own separate line item -- but included in the "Federal Tax" total -- has been causing me a lot of confusion since the update, as the spreadsheet shows my Federal Tax == Self-employment tax, when I know my actual Federal income tax is $0.

Thanks!

[MDM]

The calculation of B67 (Total income taxes) is indeed correct -- it adds up Social Security, etc just fine.

The problem is D67 (and therefore G35, "Federal Tax" and "Net Tax"). IMHO it should not include Self-Employment tax because it's not an income tax -- it's a replacement for Social Security and Medicare for self-employed folks, its own separate line items, as you can see from the screenshot of my 2021 1040.

The fact that it's it own separate line item -- but included in the "Federal Tax" total -- has been causing me a lot of confusion since the update, as the spreadsheet shows my Federal Tax == Self-employment tax, when I know my actual Federal income tax is $0.

Thanks!

I suppose it is a matter of taste, and good arguments can be make for different perspectives.  See posts #288 - #295 for what, IIRC, led to the current treatment.

Open to thoughts from you or anyone else.

[mjb]

The calculation of B67 (Total income taxes) is indeed correct -- it adds up Social Security, etc just fine.

The problem is D67 (and therefore G35, "Federal Tax" and "Net Tax"). IMHO it should not include Self-Employment tax because it's not an income tax -- it's a replacement for Social Security and Medicare for self-employed folks, its own separate line items, as you can see from the screenshot of my 2021 1040.

The fact that it's it own separate line item -- but included in the "Federal Tax" total -- has been causing me a lot of confusion since the update, as the spreadsheet shows my Federal Tax == Self-employment tax, when I know my actual Federal income tax is $0.

Thanks!

I suppose it is a matter of taste, and good arguments can be make for different perspectives.  See posts #288 - #295 for what, IIRC, led to the current treatment.

Open to thoughts from you or anyone else.

While you could indeed leave B67 blank, that would still leave the rather confusing situation of having Self-employment tax (B66) the only item in the whole A59:D66 block that is its own line item, but also added into another line (D63, Federal Tax). But then again, I only use the spreadsheet as a tax return prep and AGI calculation -- obviously there's more to the spreadsheet than that.

I guess I don't understand the original question posed in the post you referenced. SE taxes have no effect on Marginal Tax rates, which seems to in question there?

[MDM]

While you could indeed leave B67 blank, that would still leave the rather confusing situation of having Self-employment tax (B66) the only item in the whole A59:D66 block that is its own line item, but also added into another line (D63, Federal Tax). But then again, I only use the spreadsheet as a tax return prep and AGI calculation -- obviously there's more to the spreadsheet than that.

I guess I don't understand the original question posed in the post you referenced. SE taxes have no effect on Marginal Tax rates, which seems to in question there?

Perhaps we should add "large spreadsheets" to "sausages and laws" as examples of things Better Not to See Them Being Made.

I don't recall all the discussion from 2021, some of which may have occurred via PM, but one other point: self-employment tax is included in the amount one must pay to avoid federal underpayment penalties.  See Form 2210, especially the Line 2 instructions that say to include Schedule 2 line 4 (SE tax) but not Schedule 2 lines 5-7 (uncollected Social Security and Medicare tax on tips and wages).

I agree that in many respects SE tax is "the same" as SS and Medicare payroll taxes.  The fact that one must withhold or make estimated payments to cover SE tax, however, tilts the scales toward including it in "federal tax".

Based on your feedback I have deleted the calculation in cell B67 and added a note to A66 that "These amounts are included in the 'Federal tax' amount 3 rows above."  These changes will appear in the next release.  Hope that helps!

[mjb]

While you could indeed leave B67 blank, that would still leave the rather confusing situation of having Self-employment tax (B66) the only item in the whole A59:D66 block that is its own line item, but also added into another line (D63, Federal Tax). But then again, I only use the spreadsheet as a tax return prep and AGI calculation -- obviously there's more to the spreadsheet than that.

I guess I don't understand the original question posed in the post you referenced. SE taxes have no effect on Marginal Tax rates, which seems to in question there?

Perhaps we should add "large spreadsheets" to "sausages and laws" as examples of things Better Not to See Them Being Made.

No kidding, ha!!

I don't recall all the discussion from 2021, some of which may have occurred via PM, but one other point: self-employment tax is included in the amount one must pay to avoid federal underpayment penalties.  See Form 2210, especially the Line 2 instructions that say to include Schedule 2 line 4 (SE tax) but not Schedule 2 lines 5-7 (uncollected Social Security and Medicare tax on tips and wages).

I agree that in many respects SE tax is "the same" as SS and Medicare payroll taxes.  The fact that one must withhold or make estimated payments to cover SE tax, however, tilts the scales toward including it in "federal tax".

Ahh yes, I can see how that makes sense then.

Actually, in previous versions of the spreadsheet I would add my own tab that included any W2 withholding and calculated/kept track of estimated tax payments. I haven't bothered in a while as my Schedule C income plummeted during COVID and I'm also incredibly lazy.

Based on your feedback I have deleted the calculation in cell B67 and added a note to A66 that "These amounts are included in the 'Federal tax' amount 3 rows above."  These changes will appear in the next release.  Hope that helps!

Thanks! If @mangorunner comes back with their hackles up I take full responsibility!

[droidmix]

Thank you for this awesome spreadsheet! I am trying to use the solo 401k contribution computation in the Deduction Worksheet for Self-Employed section and I'm not sure if there's an error or if I'm entering things incorrectly.

My spouse has no W-2 income but has a side business (a sole proprietorship with no employees). For the past several years, they have made the maximum solo 401k contribution that TurboTax reports when doing our return. The contribution is lower than the max of $20,500. I entered our 2022 numbers into the case study spreadsheet to try to plan for 2023 and it is reporting that the contribution is too high, but only when there is no W2 income (or W2 income < line 3 of the deduction worksheet).

I think the problem is cell Z165 (step 11 of the worksheet), which is supposed to be Step 9 - Step 3. If I enter W2 income equal to the net business income (Step 3/Z156), Z165 will be 0 which I believe is the right number. However, if you have $0 in W2 income, like we do, Z165 is equal to the contribution, leading to Z167 to be equal to Z161, which ends up being the reduced/allowable contribution calculated.

It doesn't seem correct that this is tied to W2 income since it's a side business, so I've either missed entering something somewhere else on the spreadsheet or there's an issue.

[MDM]

My spouse has no W-2 income but has a side business (a sole proprietorship with no employees).  [Etc.]

Thanks for taking the time to to comment!  If you don't mind spending a little more time - more information would help.  E.g., I put 20000 into cell C30.  That made Z175 3717.  Then I also put 20000 into cell C3.  Z175 did not change: still 3717.

This part of the spreadsheet probably hasn't received much use, so an error is certainly possible.  Can you provide a "worked example" with all applicable cell entries and actual vs. expected results?

[droidmix]

Here's a simple example starting from a blank spreadsheet.

Set G2 = 2 and B3 to anything.

Now for earner #2's business income, set C30 = 10,000

Scroll down to the Deduction Worksheet for Self-Employed
What I believe is the correct max contribution is shown in Z156 ($9294). Using our numbers, this matches what TurboTax tells us is the max contribution.
Put $9294 in Z163 for elective deferral - we have always used "employee contribution" as opposed to employer.

You'll see it give a warning in X164, and the Max Deduction in Z175 is shown as $1859 (Z175 should be $9294)

Where it goes wrong I believe is starting at Z165, which shows $9294, but I believe it should be 0. If you look at the worksheet in the IRS publication (Page 34 of the pdf linked in the spreadsheet),
you'll see that Z165 (Step 11) is supposed to be Step 3 - Step 9 (Z156 - Z163). Now look at the calculation for the value of Z165. You see it would be Z156-Z163, but only if Z163 is less then G52-C11*C162. G52 is 0 because person #2 has no W2 income so without W2 income this will be wrong.

So now, enter W2 income for person #2 in C3 - set it to $10,000 or anything greater. Now Z165 will be 0, and the cascading effect leads to $9294 in Z175, which I believe is correct.

[MDM]

Here's a simple example starting from a blank spreadsheet....

Take a look at <deleted: superseded by the release below>.

[droidmix]

Here's a simple example starting from a blank spreadsheet....

Take a look at this version.  Better?  Good enough?  Thanks!

That looks good to me! Thanks again for your work on this great tool.

[MDM]

Version 22.11 (2021/2022/2023)

• Updates to self-employment items: tax display and retirement plan contribution
• Updated IRMAA tier estimates

Thanks to mjb and droidmix for the self-employment items - see discussion in posts between this and the version 22.10 release.

Updates to 2024 IRMAA tier estimates (that will apply to 2022 earnings) are based on the 0% inflation numbers in 2023 2024 2025 Medicare Part B IRMAA Premium MAGI Brackets.  The 2025 IRMAA tier estimates (that will apply to 2023 earnings) in the CSS are a simple linear projection of the change from 2023 to 2024.  This puts the 2025 CSS estimates somewhere between the 0% and 5% inflation estimates in that article.  No idea what will end up being correct. ;)

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

2021/2022/2023 taxes version: 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 ;)
  - After opening the file on Google Drive (link above), click File>Download>Microsoft Excel (xls)
  Then, either open in Excel immediately, edit, and later save from Excel to your computer, or save immediately to your computer and later open and edit that file with Excel.
  
• See
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg2138684/#msg2138684 and
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1879496/#msg1879496
  regarding warnings one might see, and why those are spurious warnings for these files.

[mjb]

Just loaded up the new version, I see that G35 is still including Self-Employment tax in Net Tax, which adds SE tax to the "Federal Tax" amount in D63.

While on the subject, I'll mention I make a couple of modifications I make to A59:G67 (the "Payroll Taxes" block):

 - for cell D66 (Self-employment Tax), I add "=B66+C66" to add up the SE tax for both partners
 - for cell D67 (Total Income Taxes), I change the equation to =SUM(D60:D66), which adds back in the correct total SE Tax, giving an accurate calculation of the total taxes owed.
  -- I think this may also help the user who initially led you to add SE taxes to the "Net Tax" cell G35?

In our case, I have both W2 and self-employed income (column B). My partner (column C) is self-employed only. Screenshot attached so you can see the result.

Thanks!

[MDM]

Just loaded up the new version, I see that G35 is still including Self-Employment tax in Net Tax, which adds SE tax to the "Federal Tax" amount in D63.

As I thought you had agreed that makes sense.

If there is something clearly wrong, by all means let's get it fixed.  It's not clear to me whether that is the case or whether this is a semantic discussion.  Thanks!

[mjb]

Just loaded up the new version, I see that G35 is still including Self-Employment tax in Net Tax, which adds SE tax to the "Federal Tax" amount in D63.

As I thought you had agreed that makes sense.

If there is something clearly wrong, by all means let's get it fixed.  It's not clear to me whether that is the case or whether this is a semantic discussion.  Thanks!

Ahh, my apologies for the lack of clarity in my response. In the quoted post I was just saying I could see the reasoning, but I don't personally agree with the choice because it creates an inconsistency between the 1040 and the spreadsheet.

Not a big deal, I'm happy to make my own modifications as necessary for the sheet to make sense to me. Thanks!

[fyre4ce]

Just downloaded the new version, and it is not behaving as I would expect. From the default settings, all I did was change the age to 67 and input $43,500 in annual Social Security income. I was expecting to see the familiar 0%, 18.5% (=10%*1.85), 22.2% (=12%*1.85), and 40.7% (=22%*1.85) marginal brackets on the "total tax rate vs. tIRA withdrawal" plot, but instead there is something else: 0%, 22%, 26.4%, 48.4%, 52.8%, etc. Did something change in the way the tool gets used? Am I using it incorrectly? Have there been recent chances in tax law I missed? Thanks very much.

[MDM]

Just downloaded the new version, and it is not behaving as I would expect. From the default settings, all I did was change the age to 67 and input $43,500 in annual Social Security income. I was expecting to see the familiar 0%, 18.5% (=10%*1.85), 22.2% (=12%*1.85), and 40.7% (=22%*1.85) marginal brackets on the "total tax rate vs. tIRA withdrawal" plot, but instead there is something else: 0%, 22%, 26.4%, 48.4%, 52.8%, etc. Did something change in the way the tool gets used? Am I using it incorrectly? Have there been recent chances in tax law I missed? Thanks very much.

Appears that something in the way Google Drive stores or downloads or ? has changed.  It is not faithfully downloading what was uploaded.  Stay tuned....

[MDM]

Version 22.12 (2021/2022/2023)

• Overwrite previous version that had become corrupted (or something?)

Thanks to fyre4ce for noticing that something was amiss.  Somehow - despite there not being a .xlsm (special Excel macro-enabled file-type) file in the folder from which I upload - the file on Google Drive had type .xlsm, and that (or maybe other issues) caused strange (and incorrect) behavior. 

Rather than do a deep dive on the strange behavior, I uploaded the version in progress (that has no significant changes from the previous one).  It appears to download with the same appearance and functionality that was uploaded, except now Microsoft's macro warning seems more stringent: it wouldn't let me enable macros within the workbook until I had checked a box on the file Properties menu in Windows.  Maybe Microsoft's macro handling "upgrade" had other consequences...?

Anyway, if fyre4ce (or anyone) could try downloading from the link below and confirm (or deny) that things are working well (enough), that would be appreciated.

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

2021/2022/2023 taxes version: 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 ;)
  - After opening the file on Google Drive (link above), click File>Download>Microsoft Excel (xls)
  Then, either open in Excel immediately, edit, and later save from Excel to your computer, or save immediately to your computer and later open and edit that file with Excel.
  
• See
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg2138684/#msg2138684 and
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1879496/#msg1879496
  regarding warnings one might see, and why those are spurious warnings for these files.

[fyre4ce]

Version 22.12 (2021/2022/2023)

• Overwrite previous version that had become corrupted (or something?)

Thanks to fyre4ce for noticing that something was amiss.  Somehow - despite there not being a .xlsm (special Excel macro-enabled file-type) file in the folder from which I upload - the file on Google Drive had type .xlsm, and that (or maybe other issues) caused strange (and incorrect) behavior. 

Rather than do a deep dive on the strange behavior, I uploaded the version in progress (that has no significant changes from the previous one).  It appears to download with the same appearance and functionality that was uploaded, except now Microsoft's macro warning seems more stringent: it wouldn't let me enable macros within the workbook until I had checked a box on the file Properties menu in Windows.  Maybe Microsoft's macro handling "upgrade" had other consequences...?

Anyway, if fyre4ce (or anyone) could try downloading from the link below and confirm (or deny) that things are working well (enough), that would be appreciated.

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

2021/2022/2023 taxes version: 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 ;)
  - After opening the file on Google Drive (link above), click File>Download>Microsoft Excel (xls)
  Then, either open in Excel immediately, edit, and later save from Excel to your computer, or save immediately to your computer and later open and edit that file with Excel.
  
• See
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg2138684/#msg2138684 and
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1879496/#msg1879496
  regarding warnings one might see, and why those are spurious warnings for these files.

The behavior looks better, thanks!

[mistymoney]

I don't have any menu when I click into the sheet - to save as an excel.

I had tried to use this previously and just click click click, I had an excel. But it seemed too complicated to me. Wanted to try again and get the updated file, but can't find anyway to save it, outside of control save, but that only saves it as a webpage.

Anyone can point me to ssaving as excel?

[MDM]

I don't have any menu when I click into the sheet - to save as an excel.

I had tried to use this previously and just click click click, I had an excel. But it seemed too complicated to me. Wanted to try again and get the updated file, but can't find anyway to save it, outside of control save, but that only saves it as a webpage.

Anyone can point me to ssaving as excel?

Does "File>Download>Microsoft Excel (xls)" work (after opening the file using the Google Drive link: Case Study Spreadsheet)?

[mistymoney]

got it! thank you!

swear there wasn't anything there before :)

[MDM]

As mentioned a few posts back, Microsoft has added a protection/roadblock to using downloaded Excel files containing macros.  Details can be found at A potentially dangerous macro has been blocked - Microsoft Support.

Most of the CSS can be used just fine without having macros enabled.  If you want to change the x-axis or y-axis variable for the marginal rate chart, however, you either need to know how Excel data tables work and edit the formulas directly, or enable macros and use the Calculations!G107 or L107 cells along with clicking the "Update chart" button near cell L114.

It appears the simplest way to get around Microsoft's "helpful" macro block is described in the first link above (see that for a screen shot of step #3 below):
  1. Open Windows File Explorer and go to the folder where you saved the file.
  2. Right-click the file and choose Properties from the context menu.
  3. At the bottom of the General tab, select the Unblock checkbox and select OK.

If anyone knows a better approach, please advise.

Thanks,

MDM

[passionfruit17]

Tis the season to do an assessment of 2023 taxes, see if I need to withhold a little extra for safe harbor, get a sense of which side of the Roth IRA contributions limit I will fall into...
I just saw that the IRS released new tax brackets for 2023, should I wait for an update to this sheet? I think there is a way to carry info from an older version to a new one but I have never tried it, I always re-enter data in the new version.

[MDM]

Tis the season to do an assessment of 2023 taxes, see if I need to withhold a little extra for safe harbor, get a sense of which side of the Roth IRA contributions limit I will fall into...
I just saw that the IRS released new tax brackets for 2023, should I wait for an update to this sheet? I think there is a way to carry info from an older version to a new one but I have never tried it, I always re-enter data in the new version.

About this time of year is when the IRS releases bracket and other adjustments for next year's taxes.  I haven't looked to see what 2024 adjustments have been published, but the 2023 information has been there all this year. ;) 

The only 2023 amounts not already known for sure are the IRMAA tiers that will apply to 2025 Medicare recipients, based on their 2023 income.

No automated copy/paste macros exist for moving inputs from one version to another, but manual copy/paste (or typing in new estimates) works. :)

[passionfruit17]

That's what I thought but I saw this article from a few days ago: https://news.yahoo.com/irs-releases-income-tax-brackets-232359416.html which says "The income thresholds have increased for each bracket, which will apply for tax year 2023 and returns filed in 2024".
After your comment I checked and the reference it cites is indeed from October last year, another example of the press trying to make copy out of nothing.

I'll use the existing spreadsheet :)

[passionfruit17]

does the sheet automatically calculate the phased-out amount for Roth/tIRA contribution based on MAGI? Is L40/M40 the phased-out percentage can I contribute $7500 times that percentage, if I'm over 50 of course)
Also, L42 and L43 say "Increase?" what does that mean?

[MDM]

does the sheet automatically calculate the phased-out amount for Roth/tIRA contribution based on MAGI? Is L40/M40 the phased-out percentage can I contribute $7500 times that percentage, if I'm over 50 of course)

Yes.

Also, L42 and L43 say "Increase?" what does that mean?

It means you haven't shown that you are contributing the amount calculated above, so you might consider increasing your contribution up to that amount.  Or not. ;)

[MDM]

Version 22.13 (2021/2022/2023)

• Update the Social Security benefit estimation tab to reflect the Cost-of-Living Adjustment (COLA) Information for 2024
• Add MN's new state tax SS benefit deduction
• Use the "3%" estimate numbers for 2025 IRMAA Brackets (that will use 2023 income).
  

Another year, another few percent increases in the SS benefit Cost Of Living Adjustment, Average Wage Index, and the maximum amount of earnings subject to Social Security payroll tax.

If there are other simple state tax changes that you perceive as worthwhile to implement, such as MN's SS benefit deduction, post in State Income Tax calculations - Crowdsourcing request and that may happen. :)

See the linked article about 2025 IRMAA brackets and draw your own conclusions about how closely you want to approach any of them....

Tax year 2024 numbers will be added (and tax year 2021 numbers and logic removed) after the IRS releases the 2023 version of https://www.irs.gov/pub/irs-drop/rp-22-38.pdf.

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

2021/2022/2023 taxes version: Case Study Spreadsheet

Notes:

• Rather than "Request Edit Access" to the file on Google Drive - which, if granted, would allow one to edit the same file that everyone else sees, so it will not be granted ;)
  - After opening the file on Google Drive (link above), click File>Download>Microsoft Excel (xls)
  Then, either open in Excel immediately, edit, and later save from Excel to your computer, or save immediately to your computer and later open and edit that file with Excel.
  
• See
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg2138684/#msg2138684 and
  https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1879496/#msg1879496
  regarding warnings one might see, and why those are spurious warnings for these files.

• In early 2023, Microsoft added a protection/roadblock to using downloaded Excel files containing macros.  Details can be found at A potentially dangerous macro has been blocked - Microsoft Support.
  
  Most of the CSS can be used just fine without having macros enabled.  If you want to change the x-axis or y-axis variable for the marginal rate chart, however, you either need to know how Excel data tables work and edit the formulas directly, or enable macros and use the Calculations!G107 or L107 cells along with clicking the "Update chart" button near cell L114.
  
  It appears the simplest way to get around Microsoft's "helpful" macro block is described in the "Details can be found at..." link above (see that for a screen shot of step #3 below):
    1. Open Windows File Explorer and go to the folder where you saved the file.
    2. Right-click the file and choose Properties from the context menu.
    3. At the bottom of the General tab, select the Unblock checkbox and select OK.