Case Study Spreadsheet updates

[passionfruit17]

where do you put treasuries dividends that are not subject to state sales tax?

[MDM]

where do you put treasuries dividends that are not subject to state sales tax?

For federal purposes, that interest amount should be included in cell Calculations!D23.

There is no pre-configured cell for federally taxable but not state taxable interest.  Assuming you have a specific amount in mind, you could use column M on the 'State Tax' tab to enter that amount for your state.  Does that work for you?

[passionfruit17]

YEs, unlocking the sheet and entering the amount of interest that is not taxable by state in column M for my state seem to work. Thanks for the prompt answer.

I would have thought it was a common use case, iBonds, treasuries (including TIPS) are exempt from state and local taxes, and some MMFs like VUSXX are 80-90% state/local tax exempt so it might be worth making it official, or add yet another column past AG for manually entered adjustments to the taxable income

[MDM]

YEs, unlocking the sheet and entering the amount of interest that is not taxable by state in column M for my state seem to work. Thanks for the prompt answer.

I would have thought it was a common use case, iBonds, treasuries (including TIPS) are exempt from state and local taxes, and some MMFs like VUSXX are 80-90% state/local tax exempt so it might be worth making it official, or add yet another column past AG for manually entered adjustments to the taxable income

Or maybe unlock column M and let that be for any adjustment?  Or move the box that currently starts in Calculations!K39 a couple columns to the right and allow a state income adjustment there?  Or...?

At this point I don't remember why the 'State Tax'!Column M is being used only for rent subtraction on a MA return.  Probably someone asked for it and it was simple enough to do, but in hindsight it is a very narrow niche....

[passionfruit17]

I also noticed that CO income tax is set to a flat 4.4%. it was changed to 4.25% for 2024  see https://dhub.deloitte.com/Newsletters/Tax/2024/STM/240524_1.html

I'm going to change 'State Brackets"!D22 and 'State Brackets"!G22 in my copy.

[MDM]

On a related topic, a couple years ago I built a tool that combines a bunch of related functions: calculating the performance of different kinds of accounts (taxable, non-deductible, pre-tax, Roth, tax-free), estimating future tax rates, and calculating TvsR comparisons. Most of these are already done in the CSS, but not pulled together in a cohesive way. I suggested you consider merging it into the CSS and you declined. Since then I've developed and polished it quite a bit. It might be worth a second look.

Won't hurt to take a second look. :)

Having recently done the Version 22.18 release, I went back to look at your tool.  Best I can tell it is designed for a niche currently occupied by tools such as Pralana and Bogleheads' RPM, and I'm not looking for the CSS to compete in that space.  Best wishes for success with it for your own use, and for whoever else picks it up from Bogleheads.

[p1s13ee]

When do you anticipate updating the tool for the 2025 tax year? I use your amazing tool for my years tax planning and 2025 is almost here.  If you don't plan to make an update, I may tweak the tax tables myself, but didn't want to undertake that until I asked ;)

[MDM]

When do you anticipate updating the tool for the 2025 tax year? I use your amazing tool for my years tax planning and 2025 is almost here.  If you don't plan to make an update, I may tweak the tax tables myself, but didn't want to undertake that until I asked ;)

That version has already been completed, but I haven't uploaded it yet.  Probably soon....

[MDM]

Version 22.19 (2022/2023/2024/2025)

• Calculations
  Added QSS as a filing status option
  Added 2025 as an optional tax year.
  Removed 2026 as an optional tax year.  Too much uncertainty about how that will work.
  
• SocialSecurity
  Updated with announced inflation numbers for 2025.
• Tax Code
  Added 2025 inflation-adjusted brackets and other numbers.
  

Some significant changes: adding tax year 2025, allowing Qualified Surviving Spouse (QSS) filing status, and updated Social Security COLA and wage index.

See State Income Tax calculations - Crowdsourcing request for comments about how the QSS status may or may not translate well to state tax estimates, and that thread for state tax issues in general.

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

2022/2023/2024/2025 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.

[dandarc]

Getting to be that time of year isn't it. Wow is it gonna be complex for 2024 for us - moved to a state with an income tax, may (or may not) sell old house in 2024 which would be simple except that we rented the thing out for 17 months back in 2018-19. At least the move I can say happened on July 1 - half and half split on the year is only superseded by a move effective January 1 for simplicity.

[frugalor]

I just found out about this sheet.  It looks so comprehensive and great.  But at the same time, I can get lost very quickly, lol.  For example, why is second Qualified dividends field a percentage?

[MDM]

I just found out about this sheet.  It looks so comprehensive and great.  But at the same time, I can get lost very quickly, lol.  For example, why is second Qualified dividends field a percentage?

Hah!  Best guess is that once upon a time I did some percentage calculation in that cell and the format stuck.

Given that currently that cell
a) is not intended for input (the cell background is not light green), and
b) there is no formula there, and no other cell on the Calculations tab refers to it (in other words, cell Calculations!C25 is supposed to be a completely unused cell)
the real problem is that the "Protection" attribute for the cell isn't set to "Locked" so you can't (as distinct from shouldn't) enter anything there without Unprotecting the sheet.  :)

But this is exactly the kind of feedback that leads to continuing improvement, so thanks! Doesn't seem worth uploading a new version, but I'll update the protection on that and similar cells for inclusion in the next release.

In addition to the "Instructions" tab, there are some good walkthroughs:
- Roth Conversion and Capital Gains On ACA Health Insurance,
- Roth Conversion with Social Security and Medicare IRMAA, and
- Using a spreadsheet

If you run into other issues, please do post back here or send me a PM.

Good luck,

MDM

[frugalor]

I just found out about this sheet.  It looks so comprehensive and great.  But at the same time, I can get lost very quickly, lol.  For example, why is second Qualified dividends field a percentage?

Hah!  Best guess is that once upon a time I did some percentage calculation in that cell and the format stuck.

Given that currently that cell
a) is not intended for input (the cell background is not light green), and
b) there is no formula there, and no other cell on the Calculations tab refers to it (in other words, cell Calculations!C25 is supposed to be a completely unused cell)
the real problem is that the "Protection" attribute for the cell isn't set to "Locked" so you can't (as distinct from shouldn't) enter anything there without Unprotecting the sheet.  :)

But this is exactly the kind of feedback that leads to continuing improvement, so thanks! Doesn't seem worth uploading a new version, but I'll update the protection on that and similar cells for inclusion in the next release.

In addition to the "Instructions" tab, there are some good walkthroughs:
- Roth Conversion and Capital Gains On ACA Health Insurance,
- Roth Conversion with Social Security and Medicare IRMAA, and
- Using a spreadsheet

If you run into other issues, please do post back here or send me a PM.

Good luck,

MDM

Got it. Only light green cells are meant to receive inputs.  Thanks!

[secondcor521]

@MDM, the cell just to the left of that one (B25 I think) is also "weird" to me.

@frugalor, I just put my applicable number in the green cell to the right of that one (D25 I think).

[MDM]

@MDM, the cell just to the left of that one (B25 I think) is also "weird" to me.

@frugalor, I just put my applicable number in the green cell to the right of that one (D25 I think).

Yes, it is "weird" - it's used for the quick 'n' dirty LTCG/QD marginal rate displayed in cell U49.  Pay no attention to that cell behind the curtain ;) and just use D25 as you suggest for qualified dividends. :)

And then be sure to add non-qualified dividends to interest and put the total into D23.

[iluvzbeach]

MDM, like so many of us, we purchased I-Bonds in 2022 when the rates were so high but we've now cashed out most of them.  Is there any place on the spreadsheet where we can indicate redemption of those I-Bonds so that it reflects them being tax-free at the state level?  I looked but am not seeing anything.

ETA: Also, on the "Calculations" tab, should the formula in Cell B65 "=IF(G9>=63,AD77,0)*12/B162" be "=IF(G9>=65,AD77,0)*12/B162" instead?  Or, am I overlooking something?

[MDM]

MDM, like so many of us, we purchased I-Bonds in 2022 when the rates were so high but we've now cashed out most of them.  Is there any place on the spreadsheet where we can indicate redemption of those I-Bonds so that it reflects them being tax-free at the state level?  I looked but am not seeing anything.

That looks similar to the request in this post.  Now that twice as many people are asking, I'll look closer at making this more obvious. :)

Meanwhile, does the workaround suggested for that post work for you?

ETA: Also, on the "Calculations" tab, should the formula in Cell B65 "=IF(G9>=63,AD77,0)*12/B162" be "=IF(G9>=65,AD77,0)*12/B162" instead?  Or, am I overlooking something?

Great question.  Unfortunately, the way IRMAA works is that your income two years ago is what determines how much, if any, extra you pay for Medicare premiums.  Thus, when you are age 63 your income will affect your age 65 premium....

[iluvzbeach]

MDM, like so many of us, we purchased I-Bonds in 2022 when the rates were so high but we've now cashed out most of them.  Is there any place on the spreadsheet where we can indicate redemption of those I-Bonds so that it reflects them being tax-free at the state level?  I looked but am not seeing anything.

That looks similar to the request in this post.  Now that twice as many people are asking, I'll look closer at making this more obvious. :)

Meanwhile, does the workaround suggested for that post work for you?

Just to confirm by column "M" you're referring to the Rent Deduction column?  I can change the formatting from % to $ and, yes, this will work.  Thank you for providing an option and I apologize for not seeing the previously provided workaround.

ETA: Also, on the "Calculations" tab, should the formula in Cell B65 "=IF(G9>=63,AD77,0)*12/B162" be "=IF(G9>=65,AD77,0)*12/B162" instead?  Or, am I overlooking something?

Great question.  Unfortunately, the way IRMAA works is that your income two years ago is what determines how much, if any, extra you pay for Medicare premiums.  Thus, when you are age 63 your income will affect your age 65 premium....

Okay, I guess what threw me off here is that I was only expecting field B65 on Calculations to show a number if one of the filers was 65+, so I wouldn't expect anything there since DH is not 65 in 2024; therefore, we won't be subject to any Medicare premium and certainly not IRMAA.  I was just assuming it was traditional Medicare premiums that were noted in this field.  Thank you for clarifying.

[MDM]

MDM, like so many of us, we purchased I-Bonds in 2022 when the rates were so high but we've now cashed out most of them.  Is there any place on the spreadsheet where we can indicate redemption of those I-Bonds so that it reflects them being tax-free at the state level?  I looked but am not seeing anything.

That looks similar to the request in this post.  Now that twice as many people are asking, I'll look closer at making this more obvious. :)

Meanwhile, does the workaround suggested for that post work for you?

Just to confirm by column "M" you're referring to the Rent Deduction column?  I can change the formatting from % to $ and, yes, this will work.  Thank you for providing an option and I apologize for not seeing the previously provided workaround.

Yes to the question, and no apology needed - thanks for asking!  I'll see what can be done to make the 2x2 fed/state taxable/nontaxable possibilities more straightforward.  Off the top of my head I'm thinking of leaving D23 and D24 as is, and maybe using K33:M36 to enter state additions/subtractions.  Any other suggestions?

ETA: Also, on the "Calculations" tab, should the formula in Cell B65 "=IF(G9>=63,AD77,0)*12/B162" be "=IF(G9>=65,AD77,0)*12/B162" instead?  Or, am I overlooking something?

Great question.  Unfortunately, the way IRMAA works is that your income two years ago is what determines how much, if any, extra you pay for Medicare premiums.  Thus, when you are age 63 your income will affect your age 65 premium....

Okay, I guess what threw me off here is that I was only expecting field B65 on Calculations to show a number if one of the filers was 65+, so I wouldn't expect anything there since DH is not 65 in 2024; therefore, we won't be subject to any Medicare premium and certainly not IRMAA.  I was just assuming it was traditional Medicare premiums that were noted in this field.  Thank you for clarifying.

If you know for sure that you won't be subject to IRMAA, changing "63" to "65" will work.  For the purpose of calculating marginal tax rate, or federal income tax alone, either 63 or 65 will give the same answer.

[jimlenz]

I just downloaded the spreadsheet and this is amazing.  Nothing like I have ever seen.  I entered all my information and only have 1 question. 

For non-cash charitable contributions, how should I enter this so it populates into my itemized deductions.  If I enter this into the monthly expenses, it shows additional monthly expenses that will not be incurred. 

Thanks again for the great work. 

[secondcor521]

I just downloaded the spreadsheet and this is amazing.  Nothing like I have ever seen.  I entered all my information and only have 1 question. 

For non-cash charitable contributions, how should I enter this so it populates into my itemized deductions.  If I enter this into the monthly expenses, it shows additional monthly expenses that will not be incurred. 

Thanks again for the great work.

If you want that effect, perhaps put your entry into $Calculations.G41.

[MDM]

I just downloaded the spreadsheet and this is amazing.  Nothing like I have ever seen.  I entered all my information and only have 1 question. 

For non-cash charitable contributions, how should I enter this so it populates into my itemized deductions.  If I enter this into the monthly expenses, it shows additional monthly expenses that will not be incurred. 

Thanks again for the great work.

If you want that effect, perhaps put your entry into $Calculations.G41.

Or Calculations!I43.

[lhamo]

@MDM, Thank you so much for creating and continuing to maintain this tool!  It is such a valuable and helpful resource....

[jimlenz]

Once again, thanks for this amazing tool. 

There. has been changes to state taxes in MI, allowing for a significant portion of retirement earnings to not be taxed. 

Here is a link to the new law, which took effect in 2023 and fully rolls out by in 2025. 

https://www.michigan.gov/taxes/iit/retirement-and-pension-benefits

Here is the table with exclusions


Tax Year   Retiree Date of Birth   Phase-In Subtraction
2023           Jan 1, 1946 - Dec 31, 1958   up to 25%
2024           Jan 1, 1946 - Dec 31, 1962   up to 50%
2025           Jan 1, 1946 - Dec 31, 1966   up to 75%
2026            N/A   up to 100%

There is a limit on the amount of retirement benefits that are excluded

20223 S 61518; MFJ 123,036

I don't believe the state has released the 2024 limit as I have looked everywhere for it.  I am surprised that is not the case. 

I hope this helps. 

[MDM]

There has been changes to state taxes in MI, allowing for a significant portion of retirement earnings to not be taxed. 

Here is a link to the new law, which took effect in 2023 and fully rolls out by in 2025. 

https://www.michigan.gov/taxes/iit/retirement-and-pension-benefits

Hmm...might be able to do something like what's in cells 'State Tax'!O29 and O32.  Any thoughts about that?

I looked a little further and probably won't be implementing the full law, including whether to take a standard deduction or a pension deduction.  E.g., it seems the two page flowchart that starts on p. 22 of https://www.michigan.gov/taxes/-/media/Project/Websites/taxes/Forms/2023/2023-IIT-Forms/BOOK_MI-1040-instructions-only.pdf would be a bit much. ;)

[bberdani]

Colorado has an exemption for the first $20k of retirement income for ages 55 to 64 and an exemption of $24k for ages 65 and older. I just updated Cell O28 in the State Tax tab to -24,000 for me. I'm  not sure the best solution of the spreadsheet.

Thanks,
Barry

[sustythemes]

I just started experimenting with 2025 planning, including ACA premiums, and think I ran into a problem with the formula in AD94.  (I'm using V22.19)

I was getting a calculated value of "0" in AD100 when using "2025" as the year in R2, causing my Excess APTC Tax in G19 to be incorrect.

So I added R2<>2025 into this AD94 formula.
=IF(OR(AND(R2<>2022,R2<>2023,R2<>2024,R2<>2025,AD93>400),AND(AI100=0,AD93<100)),"Yes"," ")

Now my APTC values seem to be correct, reflecting that the subsidy cliff is eliminated through 2025.
Am I missing something? Thanks

[MDM]

So I added R2<>2025 into this AD94 formula.
=IF(OR(AND(R2<>2022,R2<>2023,R2<>2024,R2<>2025,AD93>400),AND(AI100=0,AD93<100)),"Yes"," ")

Right you are!  And thanks for providing the solution. :)  Already coded into the next version.

Speaking of new versions, any thoughts from @jimlenz (or anyone else in MI) about how to code that state's retirement earnings?  Same question to @bberdani (or anyone else in CO).  And thoughts from anyone in MA about how important the "rent subtraction" ability is for that state?

[secondcor521]

@MDM, thanks for all you do with this spreadsheet.

An Idaho fix and two improvements:

The first $4489/$8978 of Idaho taxable income appears to be exempted from tax twice (incorrectly):  Once by the personal exemptions in State Tax!H34:I34, and also in State Brackets row 51.  I recommend changing State Tax!H34:I34 both to $0, since the way Idaho handles this is on the tax calculation line.  See Idaho Form 40 line 20.

As a minor improvement, you might also put $10 in State Tax!AG34.  Everyone who files an Idaho return (well, almost everyone) has to pay a $10 permanent building fund tax.  See Idaho Form 40 line 32.

For the grocery tax credit, each person on the Idaho return gets $120 as a refundable credit.  I suggest putting "=SUM(E1,IF(B1=2,2,1))*120" in State Tax!AB34.  (People over 65 get $20 more, but at least this simple logic gets the resulting calculation closer to correct.)  See Idaho Form 40 line 43.

[passionfruit17]

I am constantly amazed when this sheets matches up my 1040 to the dollar as a build it (actually they're off by a dollar so far)
When there is a discrepancy it's usually because I missed something on the spreadsheet (e.g. Section 199A REIT deduction goes in G14)
As I work through my taxes, it looks like form 8859 is telling me we had a little too much Medicare taken out from our paychecks, the difference between the W2s and the calculation as a percentage of income is a whopping $122.
It looks like I can simply add it to tax withheld (I70)?

[MDM]

I am constantly amazed when this sheets matches up my 1040 to the dollar as a build it (actually they're off by a dollar so far)
When there is a discrepancy it's usually because I missed something on the spreadsheet (e.g. Section 199A REIT deduction goes in G14)
As I work through my taxes, it looks like form 8859 is telling me we had a little too much Medicare taken out from our paychecks, the difference between the W2s and the calculation as a percentage of income is a whopping $122.
It looks like I can simply add it to tax withheld (I70)?

Yes, if line 24 of your Form 8959 is >$0 that can be added to your other withholding, with the total going in either I70 or G31.

[passionfruit17]

Not quite as accurate for my state taxes (CO), I think I pointed out earlier CO tax rate was lowered to 4.25% from 4.40% this year, so I edited the Tax Brackets sheet to reflect the change.

The taxable income is off slightly: the spreadsheet computed "CO taxable income = Fed AGI - CO Std deduction"
But in this case my Fed AGI is NOT my Fed taxable income because my wife's small business QBI deduction (Calculations!G15), so Fed Taxable income = AGI-QBI deduction and that's what should get fed into the state calculation
I tried changing "State Tax"!B3 to reference Calculations!G16 but it creates a circular reference, I'm horrible at understanding the jumble of arrows and troubleshooting circular references, seem to be related to REITS Section 199A. In the end the only way I could get my CO taxable income to look correct was to manually subtract the QBI $ in C28.

Also per a previous post I have interest from treasuries, which is exempt from State tax and I simply entered the amount of interest in M28 "Rent Deduction"

CO also refunds its excess sales tax (TABOR), which I entered in column AB

Then the total tax amount in B19 is correct.

[MDM]

We are reaching the time of year when Tax Foundation's State Individual Income Tax Rates and Brackets gets updated for the new tax year.  When that happens, that's when I'll consider accumulated requests for specific state tax changes.  The more requests for a particular state change, and the easier to implement, the more likely the change. ;)

All feedback appreciated, and if reasonably doable I will....

[Arbitrage]

We are reaching the time of year when Tax Foundation's State Individual Income Tax Rates and Brackets gets updated for the new tax year.  When that happens, that's when I'll consider accumulated requests for specific state tax changes.  The more requests for a particular state change, and the easier to implement, the more likely the change. ;)

All feedback appreciated, and if reasonably doable I will....

If you get to it, I did notice an error in the spreadsheet for WA, perhaps just flowed down from an error on the Tax Foundation site.  The capital gains tax deduction is inflation-adjusted, but they still have the original $250,000 value. 

The exclusion for 2024 is $270,000.  Source: https://dor.wa.gov/taxes-rates/other-taxes/capital-gains-tax

Not something that matters for anyone who is remotely mustachian, of course.

[MDM]

We are reaching the time of year when Tax Foundation's State Individual Income Tax Rates and Brackets gets updated for the new tax year.  When that happens, that's when I'll consider accumulated requests for specific state tax changes.  The more requests for a particular state change, and the easier to implement, the more likely the change. ;)

All feedback appreciated, and if reasonably doable I will....

If you get to it, I did notice an error in the spreadsheet for WA, perhaps just flowed down from an error on the Tax Foundation site.  The capital gains tax deduction is inflation-adjusted, but they still have the original $250,000 value. 

The exclusion for 2024 is $270,000.  Source: https://dor.wa.gov/taxes-rates/other-taxes/capital-gains-tax

Not something that matters for anyone who is remotely mustachian, of course.

Thanks. The latest Tax Foundation (TF) table, https://taxfoundation.org/data/all/state/state-income-tax-rates/, has $270K, so that's what will be in the upcoming update.  But is that correct for 2025?

I noticed that TF's Utah rate is not correct, and it should be 4.55% for 2025 (I think).  Otherwise I'm taking TF numbers as correct and relying on interested parties (e.g., you and others who have commented here and in State Income Tax calculations - Crowdsourcing request) in the various states to let me know of any errors.  State tax returns that approach federal law in complexity (CA, MI, and NY, I'm thinking of you from previous posts) will probably not be 100% accurate, but "reasonably easy to implement" adjustments to state calculations remain possible....

[Arbitrage]

Thanks. The latest Tax Foundation (TF) table, https://taxfoundation.org/data/all/state/state-income-tax-rates/, has $270K, so that's what will be in the upcoming update.  But is that correct for 2025?

I noticed that TF's Utah rate is not correct, and it should be 4.55% for 2025 (I think).  Otherwise I'm taking TF numbers as correct and relying on interested parties (e.g., you and others who have commented here and in State Income Tax calculations - Crowdsourcing request) in the various states to let me know of any errors.  State tax returns that approach federal law in complexity (CA, MI, and NY, I'm thinking of you from previous posts) will probably not be 100% accurate, but "reasonably easy to implement" adjustments to state calculations remain possible....

It gets adjusted annually, and not necessarily in round numbers (e.g. the 2023 deduction was $262,000), so I imagine it'll be different in 2025.  I haven't followed it closely enough to know when they announce the new number; I just knew that it was inflation-adjusted.  $270k seems like the best number to expect for now.

[MDM]

Version 22.20 (2022/2023/2024/2025)

• Calculations
  Added input cells K33:M35 for interest & dividend taxability different betwee federal and state.
  Maintain availability for PTC above 400% FPL in 2025.
  Update taxable SS benefit worksheet line numbers.
  Update SS tax marginal rate boundary equations.
  Added an alternate QBI calculation that assumes use of the federal standard, not itemized, deduction.
  Changed default tax year (cell R2) to 2025.
  
• State Tax
  Added calculations for interest & dividend taxability different betwee federal and state.
  Various Colorado and Idaho changes. :)
  New Std. Ded. and exemption amounts for TY2025.
  Used the alternate QBI calculation (see above) for CO and ID.
  
• State Brackets
  New brackets and rates for TY2025.
  
• Tax Code
  Updated withholding tables for 2025; tweaked IRMAA guesses for 2024/2025 income.

I think this incorporates all the reasonably straightforward suggestions received to date, including those in State Income Tax calculations - Crowdsourcing request.  If I overlooked any, just remind me.

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

2022/2023/2024/2025 taxes version: Case Study Spreadsheet

Notes:

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

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

[MDM]

Version 22.20 (2022/2023/2024/2025)

Some testing inputs inadvertently left in the version uploaded at 1:50AM EST (thanks secondcor521 for noticing).  Corrected version uploaded 4AM EST.

[MDM]

Version 22.21 (2022/2023/2024/2025)

• Calculations
  Changes to ACA calculations when SEHI deduction also present and income near an integer multiple of FPL.
  
• State Tax
  More calculations for interest & dividend taxability different between federal and state.
  Various CT and MA changes - see this post in State Income Tax calculations - Crowdsourcing request and following.  Thanks to mmm999.
  Some (not all) MI changes suggested in that post.  Thanks to jimlenz.
  

I think this incorporates all the reasonably straightforward suggestions received to date, including those in State Income Tax calculations - Crowdsourcing request.  If I overlooked any, just remind me.

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

2022/2023/2024/2025 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.
  

[passionfruit17]

I noticed that Colorado disappeared from the list of states?
I can probably try and reproduce last year's (v21.19) math
TBH I don't think the 2025 tax rate is known yet. It looks like it will not be finalized until October 2025, and employers are instructed to use 4.4% in the meantime

[MDM]

I noticed that Colorado disappeared from the list of states?

Not that I know of, so...?

[passionfruit17]

lol I didn't notice that the top of the page was a frozen header and the bottom half was scrolled down a bit when I opened it, so I found CO, thanks!

[alife]

Newbie would appreciate some clarification on using the new cells K33:M33 in the Calculations sheet for dealing with interest income that is only subject to federal/state tax.

Suppose I have 100,000 in interest income that is subject only to federal tax. Would I enter 100,000 in cell D23 and then also enter 100,000 in cell L34 and nothing in cell M34?

And what if, instead, I had 30,000 in interest income that was subject only to state income tax? Would I enter nothing in cell D23 and 30,000 in cell M34?

Thanks!

[MDM]

Newbie would appreciate some clarification on using the new cells K33:M33 in the Calculations sheet for dealing with interest income that is only subject to federal/state tax.

Suppose I have 100,000 in interest income that is subject only to federal tax. Would I enter 100,000 in cell D23 and then also enter 100,000 in cell L34 and nothing in cell M34?

Yes.

And what if, instead, I had 30,000 in interest income that was subject only to state income tax? Would I enter nothing in cell D23 and 30,000 in cell M34?

Close.  You should enter 30,000 in D24, because some MAGIs include federally tax-exempt income, and then the 30,000 in M34.

And welcome to the forum - thanks for asking!

[alife]

Thanks for the clarification, and for creating this great tool!

If I could ask a follow-up question:  I notice that changes in the amount entered in L34 will affect federal taxes, when I would only expect them to affect state taxes. (The state taxes are always well above the $10,000 SALT limit, so it can't be that federal itemized deductions are changing.)

For example, if I enter $80,000 in cell D23 and $60,000 in cell L34, I get a somewhat different estimate of federal taxes than if I put a zero in cell L34. 

Suspect I am missing something simple!

[MDM]

If I could ask a follow-up question:

Of course!

For example, if I enter $80,000 in cell D23 and $60,000 in cell L34, I get a somewhat different estimate of federal taxes than if I put a zero in cell L34.

Hmmm.  I tried that and see no change in the federal tax.

Given all the possible permutations among states, income amounts, and types, it's possible that your scenario is one that wasn't tested.  Could you provide all the inputs you have used?  Feel free to PM if you would prefer.  Thanks!

[MDM]

Version 26.00 (2023/2024/2025/2026)
This is a big one.  Of course, mistakes may have been made interpreting the new law, coding in Excel, or just simple typos.  Enough testing has been done that it seems worth publishing, but as always it's the feedback from all of you that helps the most.

Updates were done using the most recent version of https://www.congress.gov/bill/119th-congress/house-bill/1/text as a reference.

Things affecting the Case Study Spreadsheet (CSS) seem to start with Title VII--Finance.  Sections before 70001 were assumed not to apply.

The Modified Adjusted Gross Income (MAGI) used in the BBB is AGI plus
a) Foreign Earned Income Exclusion
b) Income from Guam, American Samoa, or the Northern Mariana Islands
c) Income from Puerto Rico
Only item a) is covered by the CSS.

Not all TY2026 numbers are known, because many are subject to inflation adjustments that the IRS usually releases starting in October of the previous tax year.  TY2025 numbers or rough guesses are used as placeholders until then.

Below is a list of sections in the bill and what changes, if any, were made in the CSS to reflect the new law.  You can search for the section number in the document linked above to see what it covers.

70101: Will apply to inflation adjustments for 2026.
70102: Standard deductions for 2025 updated.
70103: Added extra deductions for seniors in 2025 & 2026.
70104: Increased max. CTC to 2200 for 2025.  Max refundable stays at 1700 for 2025.
70105: For 2026, updated the phase-out start to $150K MFJ and $75K others; added the $400 minimum if business income >= $1K.
70106: No change to the CSS.
70107: No AMT change for 2025.  Changes to inflation adjustments will apply to 2026.
70108-70110: No change to the CSS.
70111: Reduce itemized deduction by 2/37 if in the 37% bracket.
70112-70119: No change to the CSS.
70120: Added higher SALT limit and phaseout.
70201: Addressed "no tax on tips" but for W-2 only - haven't included tips as part of Schedule C income.  Assumptions: tips will be reported separately from other W-2 income, and FICA tax remains due on tips.
70202: Addressed "no tax on overtime" with the same assumptions about overtime as about tips above.
70203: Assumed only one car loan with interest estimated as G134*I134.
70204: Assuming any such contributions will be added to tIRA amounts.
70301-70403: No change to the CSS
70404-70405: Status quo for 2025. Updates made for 2026.
70411-70423: No change to the CSS
70424-70425: Status quo for 2025. Updates made for 2026.
70426-71304: No change to the CSS
71305: Status quo for 2025. Updates made for 2026. Note that the 400% FPL "cliff" for the ACA returns in 2026.
71306-end: No change to the CSS

Aside from that:
- Minor changes to NJ state tax calculations
- Eliminated the Windfall Elimination Provision (WEP) calculations on the SocialSecurity tab that were added just before congress eliminated the WEP

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

2023/2024/2025/2026 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.