State Income Tax calculations - Crowdsourcing request

[sustythemes]

On the California State tax return, it's sometimes preferrable to itemize even if you don't itemize on the Federal return. In my case this is due to a large Sales Tax event (large purchase). Is there a way to represent this State itemized deduction (or reduced taxable income), instead of the standard deduction in the Case Study Spreadsheet, due to choosing to claim the Sales Tax vs State Income Tax?

[MDM]

On the California State tax return, it's sometimes preferrable to itemize even if you don't itemize on the Federal return. In my case this is due to a large Sales Tax event (large purchase). Is there a way to represent this State itemized deduction (or reduced taxable income), instead of the standard deduction in the Case Study Spreadsheet, due to choosing to claim the Sales Tax vs State Income Tax?

The simplest way, if your itemized deduction is not income-dependent, is to put the itemized amount into cell 'State Tax'!E27 or 'State Tax'!F27 depending on your filing status.  Will that suffice?

Don't know if it is "the most", but California has at least "one of the most" complicated state tax codes, and IIRC they don't even tell people the bracket boundaries until the end of the tax year, so take any result with a few grains of salt until you can verify it.

There are enough residents in CA that I'd be willing to add some useful complexity if you or anyone can describe it, particularly if the description includes Excel formulas. ;)

Alternatively, elect different state legislators (of either party) who will simplify your state tax code. :)

[sustythemes]

Thank you! That'll suffice for my situation since it seems income independent.

[tj]

NARFE always has good information on state taxation of federal pensions and other types of retirement accounts that federal retirees have.

The April 2022 version is contained on page 25-26 of this PDF:  https://issuu.com/narfe/docs/narfe_0422_web

[gbronc]

Truly love this spreadsheet for tax calculations. I assume this feature I'm requesting does not exist, since I've been unable to find it. Having the ability to separate out US Treasury interest income from sources like T-Bills, Savings Bonds, etc. would allow you to exempt that income from State taxes. With the huge increase in interest rates over the last year, this becomes a much more valuable feature since there could be a significant state tax difference. I have workarounds, but would much prefer it to be integrated since California has so many brackets.

Thanks for all the great work on this.

[MDM]

Truly love this spreadsheet for tax calculations. I assume this feature I'm requesting does not exist, since I've been unable to find it. Having the ability to separate out US Treasury interest income from sources like T-Bills, Savings Bonds, etc. would allow you to exempt that income from State taxes. With the huge increase in interest rates over the last year, this becomes a much more valuable feature since there could be a significant state tax difference. I have workarounds, but would much prefer it to be integrated since California has so many brackets.

Thanks for all the great work on this.

You're welcome!

That would be doable, but it also starts down the slippery slope that includes municipal bond interest taxable in your state vs. municipal bond interest not taxable in your state, etc.

As an immediate "integrated workaround," would entering the US Treasury interest as "Rental income" in cell Calculations!B33, then putting =E10 in cell 'State Tax'!M27 give you all the functionality you need?  That wouldn't work if you have actual rental income, but...?

[gbronc]

Yes, I could see that would add some complexity for each state. I may be wrong, but seems like Treasury interest would be a more common scenario.

Unfortunately I do have Rental Income, so your workaround would be a bit trickier (considered using Alimony, which I don't have), but I think I could make it work if cell 'State Tax'!M27 was not protected. Am I missing something on how to enter the =E10?

On a separate California only point, I notice you deduct HSA contributions from Income which is not allowed in California and two other states.

I'm probably grossly oversimplifying, but maybe an income category called "Federal Only income (not included in State)"? The trick is the user has to be sure that income isn't in any other categories. In my case I'd have to combine expected HSA contributions and US Treasury interest to arrive at one number only and not use your HSA contribution cells.

Slippery slopes all over the place for you.

[MDM]

Unfortunately I do have Rental Income, so your workaround would be a bit trickier (considered using Alimony, which I don't have), but I think I could make it work if cell 'State Tax'!M27 was not protected. Am I missing something on how to enter the =E10?

There is no password on the protection.  Caveat user. 

Go to the 'State Tax' tab, then click Review > Unprotect Sheet (or Review > Protect > Unprotect Sheet, if your menu is condensed).  Does that work?

[smlute01]

I believe the KY State tax calculation is incorrect. KY residents get taxable SS excluded, plus an additional $31,110 per taxpayer excluded (Fed AGI - Taxable SS - $31,110 = KY AGI (Single)).  Can anyone else confirm or correct me? Thanks, great forum!

[MDM]

I believe the KY State tax calculation is incorrect. KY residents get taxable SS excluded, plus an additional $31,110 per taxpayer excluded (Fed AGI - Taxable SS - $31,110 = KY AGI (Single)).  Can anyone else confirm or correct me? Thanks, great forum!

Thanks for the feedback!

The taxable SS is already being excluded. 

2023 State Income Tax Rates and Brackets | Tax Foundation doesn't mention anything about it, but based on https://revenue.ky.gov/Forms/Schedule%20M%202022.pdf up to $31,110 of pension benefits and traditional account withdrawals may be subtracted.  There are some other wrinkles (disability income, extra subtraction for government retirees, etc.) but for your purposes would a formula of =MAX(-B11-E11,-31110) in cell 'State Tax'!O39 suffice?

Is the $2,770 standard deduction correct?

For 2023, a $10K tIRA withdrawal, $30K pension, and $25K SS benefit ($20,225 of which is taxable), federal AGI is $60,225.  With the above formula in place, KY taxable income is $6,120 ($60,225 - $20,225 - $31,110 - $2,770) - correct?

[smlute01]

That formula is close. I believe it's $31,110 per taxpayer, so I used =IF($B$2=1,MAX(-B11-E11,-31110),MAX(-B11-E11,-31110)*2).

The standard deduction is $2,980 for 2023 (double for MFJ).

For 2023, a $74,000 tIRA withdrawal, $7,200 pension, and $33,600 SS benefit ($28,560 of which is taxable), federal AGI is $109,760.  With the above formula in place, KY taxable income is $47,110 ($110,760 - $28,560 - $31,110 - $2,980). I'm not a CPA, so happy to be corrected if needed.

So glad I found this website, it is awesome. I'm actually a financial advisor and I've been building my own tax spreadsheet on and off for about 3 years. This one absolutely blows mine out of the water! So grateful to have found it.

[MDM]

That formula is close. I believe it's $31,110 per taxpayer

Indeed it is!

, so I used =IF($B$2=1,MAX(-B11-E11,-31110),MAX(-B11-E11,-31110)*2).

We can't double the actual pension amount, so does =MAX(-B11-E11,-31110*IF($B$2=2,2,1)) work?

The standard deduction is $2,980 for 2023 (double for MFJ).

Was $2770 correct for 2022?  Any projection for 2024 yet? Perhaps something like =IF($B$21=2022,2770,IF($B$21=2023,2980,3190)) in cell 'State Tax'!E39 and =E39*2 in F39?

For 2023, a $74,000 tIRA withdrawal, $7,200 pension, and $33,600 SS benefit ($28,560 of which is taxable), federal AGI is $109,760.  With the above formula in place, KY taxable income is $47,110 ($110,760 - $28,560 - $31,110 - $2,980).

Now we're poised at the top of a slippery slope. ;)

That calculation works for a single filer, but becomes problematic for MFJ, because the KY law says the $31,110 limit must be imposed separately on each spouse.  Thus out of the $81,200 total retirement income amount, anywhere from $31,110 to $62,220 could be subtractable depending on how that income was split between the spouses.  Commercial tax software for filing purposes should easily make that distinction. 

It's almost easily doable here, because we already have separate entries for pension income by spouse, but only one entry (Calculations!D31) for traditional account withdrawals.  It wouldn't be too hard to change Calculations!D31 to be calculated as B31+C31, but that could cause confusion for folks following nice tutorials such as Roth Conversion and Capital Gains On ACA Health Insurance and Roth Conversion with Social Security and Medicare IRMAA.

Your thoughts?

[MDM]

That calculation works for a single filer, but becomes problematic for MFJ, because the KY law says the $31,110 limit must be imposed separately on each spouse.  Thus out of the $81,200 total retirement income amount, anywhere from $31,110 to $62,220 could be subtractable depending on how that income was split between the spouses.  Commercial tax software for filing purposes should easily make that distinction. 

It's almost easily doable here, because we already have separate entries for pension income by spouse, but only one entry (Calculations!D31) for traditional account withdrawals.  It wouldn't be too hard to change Calculations!D31 to be calculated as B31+C31, but that could cause confusion for folks following nice tutorials such as Roth Conversion and Capital Gains On ACA Health Insurance and Roth Conversion with Social Security and Medicare IRMAA.

NY (and maybe other states?) also has a pension and retirement account income subtraction that has limits for each individual, instead of a total MFJ limit that some other states have.  I'm leaning toward handling the individual calculations, even though one would then need to use B31 (or C31) instead of D31 for the marginal rate chart....

[smlute01]

Great info on the replies. I agree that we'll need to separate out the income by individuals. Easily done on the Calculations tab, but still trying to figure out the best way to do it on the State Tax tab. Any thoughts here? I'm probably overcomplicating it and maybe it's just a formula or two.

[MDM]

Great info on the replies. I agree that we'll need to separate out the income by individuals. Easily done on the Calculations tab, but still trying to figure out the best way to do it on the State Tax tab. Any thoughts here? I'm probably overcomplicating it and maybe it's just a formula or two.

Do the separate entries for each spouse's pension and IRA/401k/403b/etc. withdrawals in the latest version work for you?

[MDM]

Idaho only applies the 5.8% flat tax above $2500 (single) and $5,000 (MFJ).  The $2,500/$5,000 acts like an additional Idaho-only standard deduction.  You probably only have a handful of Idaho filers, but I think it's an easy fix.  Maybe in State Tax!H34 and State Tax!I34?

I looked at https://tax.idaho.gov/wp-content/uploads/forms/EFO00089/EFO00089_12-30-2022.pdf and https://tax.idaho.gov/wp-content/uploads/forms/EFO00089/EFO00089_12-30-2022.pdf but didn't see the personal exemptions.  Could you point me toward an Idaho state document regarding those?

Yes, it would be an easy fix and I'd be happy to do it.  Some of the other intricacies, e.g., the retirement income subtraction for Public Service retirees (Form 29R line B8) would be a bit much. ;)

[secondcor521]

Idaho only applies the 5.8% flat tax above $2500 (single) and $5,000 (MFJ).  The $2,500/$5,000 acts like an additional Idaho-only standard deduction.  You probably only have a handful of Idaho filers, but I think it's an easy fix.  Maybe in State Tax!H34 and State Tax!I34?

I looked at https://tax.idaho.gov/wp-content/uploads/forms/EFO00089/EFO00089_12-30-2022.pdf and https://tax.idaho.gov/wp-content/uploads/forms/EFO00089/EFO00089_12-30-2022.pdf but didn't see the personal exemptions.  Could you point me toward an Idaho state document regarding those?

Yes, it would be an easy fix and I'd be happy to do it.  Some of the other intricacies, e.g., the retirement income subtraction for Public Service retirees (Form 29R line B8) would be a bit much. ;)

It's a new law, so you won't see it in the 2022 tax documents, and Idaho apparently hasn't published 2023 documents yet.

The law is HB1, here's the text:

https://legislature.idaho.gov/wp-content/uploads/sessioninfo/2022extra1/legislation/H0001.pdf

See page 3 of that PDF, lines 26 through 35.

They did this because Idaho wanted to go to a flat tax rate, but they also wanted to promise that everyone would pay lower taxes.  Since Idaho had some very small low rate brackets at the beginning of their bracket structure, they had to add this additional exemption/standard deduction thing to accomplish both those goals.

[MDM]

Idaho only applies the 5.8% flat tax above $2500 (single) and $5,000 (MFJ).  The $2,500/$5,000 acts like an additional Idaho-only standard deduction.  You probably only have a handful of Idaho filers, but I think it's an easy fix.  Maybe in State Tax!H34 and State Tax!I34?

I looked at https://tax.idaho.gov/wp-content/uploads/forms/EFO00089/EFO00089_12-30-2022.pdf and https://tax.idaho.gov/wp-content/uploads/forms/EFO00089/EFO00089_12-30-2022.pdf but didn't see the personal exemptions.  Could you point me toward an Idaho state document regarding those?

Yes, it would be an easy fix and I'd be happy to do it.  Some of the other intricacies, e.g., the retirement income subtraction for Public Service retirees (Form 29R line B8) would be a bit much. ;)

It's a new law, so you won't see it in the 2022 tax documents, and Idaho apparently hasn't published 2023 documents yet.

The law is HB1, here's the text:

https://legislature.idaho.gov/wp-content/uploads/sessioninfo/2022extra1/legislation/H0001.pdf

See page 3 of that PDF, lines 26 through 35.

They did this because Idaho wanted to go to a flat tax rate, but they also wanted to promise that everyone would pay lower taxes.  Since Idaho had some very small low rate brackets at the beginning of their bracket structure, they had to add this additional exemption/standard deduction thing to accomplish both those goals.

Yes, $2,500 in H34 and $5,000 in I34 should work just fine and will be in the next release.  You could edit the current version just as well - thanks for the update!

[sk_y]

In Indiana,
... State tax rate is 3.23%, 'State Brackets'!D58. Currently in spreadsheet it is 3.15%
... 529 Contribution limit (for tax credit) for MFJ couple is 7500$,  'State Tax'!T36. Currently in spreadsheet it is 5000$
... It appears that Local Tax Calculation G39 is based on Federal AGI, I believe for Indiana Local Tax is actually based on Indiana State AGI( Federal AGI minus state deductions & exemptions)

[MDM]

sk_y, welcome to the forum.

In Indiana,
... State tax rate is 3.23%, 'State Brackets'!D58. Currently in spreadsheet it is 3.15%
... 529 Contribution limit (for tax credit) for MFJ couple is 7500$,  'State Tax'!T36. Currently in spreadsheet it is 5000$

Thanks!  These will be in the next release.
 

... It appears that Local Tax Calculation G39 is based on Federal AGI, I believe for Indiana Local Tax is actually based on Indiana State AGI( Federal AGI minus state deductions & exemptions)

This one is on the slippery slope that leads to commercial grade tax software that covers all possible state and local tax situations, so I leave it to individuals to modify the CSS as needed for their own use.  As is, the CSS will still include Indiana Local Tax correctly for marginal tax rates, provided there would be a non-zero tax amount in reality.

The tweaks on state tax rate and the 529 contribution limit are exactly the sort of things this thread is for.  2023 State Income Tax Rates and Brackets | Tax Foundation is the default source, but things can and do change.

[westernViews]

In California, the $140 exemption credit for each taxpayer (two for MFJ) are doubled for age 65 and over.

In my copy of CashFlow (the 2022 2023 2024 version), I changed H27 from it's prior entry of '$140' to '=140+if(C5>=65,140,0)' and I27 from it's prior entry of '$280' to '=280+if(C5>=65,140,0)+if(and(B2=2,D5>=65),140,0)'

You may wish to adopt this or something similar.

[MDM]

In California, the $140 exemption credit for each taxpayer (two for MFJ) are doubled for age 65 and over.

In my copy of CashFlow (the 2022 2023 2024 version), I changed H27 from it's prior entry of '$140' to '=140+if(C5>=65,140,0)' and I27 from it's prior entry of '$280' to '=280+if(C5>=65,140,0)+if(and(B2=2,D5>=65),140,0)'

You may wish to adopt this or something similar.

Thanks, it's great when suggestions are accompanied by solutions!

[MDM]

sk_y, welcome to the forum.

In Indiana,
... State tax rate is 3.23%, 'State Brackets'!D58. Currently in spreadsheet it is 3.15%

Per DOR: Tax Rates Fees & Penalties, the 2024 rate is 3.05% so that will be the number in the release to be made shortly.

... 529 Contribution limit (for tax credit) for MFJ couple is 7500$,  'State Tax'!T36. Currently in spreadsheet it is 5000$

Appears the single filer (not MFS) limit is also $7500.

[MDM]

In California, the $140 exemption credit for each taxpayer (two for MFJ) are doubled for age 65 and over.

In my copy of CashFlow (the 2022 2023 2024 version), I changed H27 from it's prior entry of '$140' to '=140+if(C5>=65,140,0)' and I27 from it's prior entry of '$280' to '=280+if(C5>=65,140,0)+if(and(B2=2,D5>=65),140,0)'

You may wish to adopt this or something similar.

Thanks, it's great when suggestions are accompanied by solutions!

Done for the version about to be uploaded.  Thanks again.

[MDM]

Idaho only applies the 5.8% flat tax above $2500 (single) and $5,000 (MFJ).  The $2,500/$5,000 acts like an additional Idaho-only standard deduction.  You probably only have a handful of Idaho filers, but I think it's an easy fix.  Maybe in State Tax!H34 and State Tax!I34?

Based on the worksheet on p. 9 of https://tax.idaho.gov/wp-content/uploads/forms/EIN00046/EIN00046_11-06-2023.pdf, $4489 and $8978 are the 2023 numbers.  Let me know if you want better ones for 2024.

[MDM]

Version 22.17 (2022/2023/2024)

• 2024 State tax updates

Thanks once again to the Tax Foundation folks who compile 2024 State Income Tax Rates and Brackets, the values in that publication are now included.

The state tax numbers are taken on faith from the Tax Foundation spreadsheet.  I leave it to you residents of the various states to check for typos or describe simple calculations that would make the results more accurate.  State Income Tax calculations - Crowdsourcing request is the preferred place for those responses.

For those who live in states with tax codes rivaling the federal system (e.g., California et al.), well, what's in here should at least be approximately correct....


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

2022/2023/2024 taxes version: Case Study Spreadsheet

[secondcor521]

Idaho only applies the 5.8% flat tax above $2500 (single) and $5,000 (MFJ).  The $2,500/$5,000 acts like an additional Idaho-only standard deduction.  You probably only have a handful of Idaho filers, but I think it's an easy fix.  Maybe in State Tax!H34 and State Tax!I34?

Based on the worksheet on p. 9 of https://tax.idaho.gov/wp-content/uploads/forms/EIN00046/EIN00046_11-06-2023.pdf, $4489 and $8978 are the 2023 numbers.  Let me know if you want better ones for 2024.

Those are perfect, thanks!

[Orichalcum]

The NY CTC ('State Tax'!L54) is ~$330 ea. An approximate calculation would be
=MAX(0.33*MIN(B14*1000-MAX(ROUNDUP((B3-IF(B1=2,110000,IF(B1=3,55000,75000)))/1000,0),0)*50,Calculations!D63-Calculations!H25),B14*100)
https://www.tax.ny.gov/pit/credits/empire_state_child_credit.htm

Also, NY has a non refundable payroll PFL tax which changes each year and could be put in AG54. For 2023
=MIN(0.455%*Calculations!B3,399.43)+MIN(0.455%*Calculations!C3,399.43)
For 2024
=MIN(0.373%*Calculations!B3,333.25)+MIN(0.373%*Calculations!C3,333.25)
https://paidfamilyleave.ny.gov/cost

[MDM]

The NY CTC ('State Tax'!L54) is ~$330 ea. An approximate calculation would be
=MAX(0.33*MIN(B14*1000-MAX(ROUNDUP((B3-IF(B1=2,110000,IF(B1=3,55000,75000)))/1000,0),0)*50,Calculations!D63-Calculations!H25),B14*100)
https://www.tax.ny.gov/pit/credits/empire_state_child_credit.htm

Thanks for the heads-up!  Using that formula as is creates a circular reference.  Instead of Calculations!D63-Calculations!H25, would Calculations!AC191 work well enough?  In other words,
=MAX(0.33*MIN(B14*1000-MAX(ROUNDUP((B3-IF(B1=2,110000,IF(B1=3,55000,75000)))/1000,0),0)*50,Calculations!AC191),B14*100)

Also, NY has a non refundable payroll PFL tax which changes each year and could be put in AG54. For 2023
=MIN(0.455%*Calculations!B3,399.43)+MIN(0.455%*Calculations!C3,399.43)
For 2024
=MIN(0.373%*Calculations!B3,333.25)+MIN(0.373%*Calculations!C3,333.25)
https://paidfamilyleave.ny.gov/cost

OK, the 2024 version is there for the next release.  It's not too bad allowing for multiple federal years, but in general catering to multiple state years is too much. ;)

Thanks again for the feedback!

[Orichalcum]

Woops, I missed that. Calculations!AC191 should work (I don't file Form 8812 so I made some assumptions). Actually I forgot to divide by the number of children, so the formula should be
=MAX(0.33*MIN(1000-MAX(ROUNDUP((B3-IF(B1=2,110000,IF(B1=3,55000,75000)))/1000,0),0)*50/B14,Calculations!AC191),100)

No, I don't expect you to cater to multiple state years. It will be there to remind me next year. But it would be nice if you could compare states....

[MDM]

Woops, I missed that. Calculations!AC191 should work (I don't file Form 8812 so I made some assumptions). Actually I forgot to divide by the number of children, so the formula should be
=MAX(0.33*MIN(1000-MAX(ROUNDUP((B3-IF(B1=2,110000,IF(B1=3,55000,75000)))/1000,0),0)*50/B14,Calculations!AC191),100)

Added "IF(B14=0,0," at the start (and a closing parenthesis at the end) and the result is what is in there now for the next release.

No, I don't expect you to cater to multiple state years. It will be there to remind me next year. But it would be nice if you could compare states....<snip>

You can. :)

But it takes a little Excel knowledge. ;)

In the spreadsheet section below (I started at cell D243 in the upper left but you could pick any open space), the upper left cell has =G38.  The rest of the first column and first row are entered by hand.  To fill in the numbers, select the whole rectangular section then click Data>What-If Analysis>Data Table... and use H38 for the "Row input cell" and B3 for the "column input cell" (if you want "Earner #1 Gross Salary/Wages" to be the dollar amount).

Then select the whole rectangular section again, click "Insert" (next to "Home" in the main menu) and select the type of chart you want.

[Orichalcum]

Thanks. That's basically how I got my chart. I just have to set it up again each year I want it.