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.