Author Topic: Case Study Spreadsheet updates  (Read 325461 times)

jheez

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: Case Study Spreadsheet updates
« Reply #350 on: July 24, 2022, 07:05:58 PM »
Thank you for all the hard work on this. This is an invaluable tool!

I'm not savy enough to diagnose, but it appears LT Capital Gains and Qualified Dividends are showing as taxed in the 0% LTCG/QD bracket in Version 22.03

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #351 on: July 24, 2022, 07:14:54 PM »
Thank you for all the hard work on this. This is an invaluable tool!

I'm not savy enough to diagnose, but it appears LT Capital Gains and Qualified Dividends are showing as taxed in the 0% LTCG/QD bracket in Version 22.03
Changing only cells D25 and D27 from the template by entering 20000 in each gives $0 for federal tax in cell G35.  Do you see something different?

jheez

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: Case Study Spreadsheet updates
« Reply #352 on: July 24, 2022, 09:08:12 PM »
My apologies. Complete user error! Feel free to remove my post

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #353 on: July 24, 2022, 09:30:54 PM »
Not a problem - better the occasional false alarm than real problems continuing unnoticed. 

And welcome to the forum!

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #354 on: August 29, 2022, 01:47:03 PM »
Version 22.04 (2022)
Very minor update: delete the formula in cell B72. 

IIRC, I was looking at cash flow for a sale of stock with a 50% basis.  Setting the "other untaxed income" equal to the LTCG was correct for that situation, but the formula should not have been left in the template for general use.

No effect on tax calculations, but the beta test team should have caught this. ;)

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

2021 taxes version: 2021 Case Study Spreadsheet
2022 taxes version: 2022 Case Study Spreadsheet

Note:

dandarc

  • Walrus Stache
  • *******
  • Posts: 5545
  • Age: 41
  • Pronouns: he/him/his
Re: Case Study Spreadsheet updates
« Reply #355 on: September 20, 2022, 02:34:35 PM »
Getting to be time for me to triple-check that in fact the solar tax credit + premium tax credits will actually fully wipe out our federal tax bill this year. Can I successfully copy my 2021 mods for the S-Corp and the iterative ACA premium tax credit into the 2022 sheet accurately? Stay tuned to find out.

I've paid nothing all year beyond FICA which payroll provider handles for me, but have not done anywhere near maxing tax-advantaged accounts this year. Going to be interesting to see how this comes out in the wash.

Mostly posting today with a heartfelt "Thank you" to @MDM for giving away for free the best tax estimating spreadsheet out there.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #356 on: September 20, 2022, 03:45:01 PM »
Getting to be time for me to triple-check that in fact the solar tax credit + premium tax credits will actually fully wipe out our federal tax bill this year. Can I successfully copy my 2021 mods for the S-Corp and the iterative ACA premium tax credit into the 2022 sheet accurately? Stay tuned to find out.
For those interested in the background, see this dandarc post and follow-ups on that page.

One may include the potential solar tax credit in the amount for cell Calculations!G25.  One reason this tool remains free is that some complicated math (e.g., multiplying the solar costs that go on https://www.irs.gov/pub/irs-pdf/f5695.pdf by 30% for 2022) must be done by the user outside the spreadsheet. ;)

JD Leonard

  • 5 O'Clock Shadow
  • *
  • Posts: 1
Re: Case Study Spreadsheet updates
« Reply #357 on: September 21, 2022, 03:47:19 PM »
Thanks to @MDM and everyone who has contributed to this sheet!

I already have an S-Corp so I'm just trying to get the 1040 tax calculations correct rather than compare Schedule C to S-Corp.

On V22.02 I performed the following steps:
  • Use a blank cell for "S-Corp net profit" near the "Non-paycheck income" section
  • Add the cell from step 1 to D42 "1040 Total Income"
  • Add the cell from step 1 to Y137 "QBI Deduction" "Business Income"
  • Add W-2 wages and any other "Paycheck Items" to the "Paycheck Items" section

Steps 1-3 seem nice and general so perhaps worth including in a future version of the sheet. However, this seems too simple so I'm probably missing something significant...

I know I haven't tried to tackle the Deduction Worksheet for Self-Employed

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #358 on: September 21, 2022, 04:58:49 PM »
Getting to be time for me to triple-check that in fact the solar tax credit + premium tax credits will actually fully wipe out our federal tax bill this year. Can I successfully copy my 2021 mods for the S-Corp and the iterative ACA premium tax credit into the 2022 sheet accurately? Stay tuned to find out.
I already have an S-Corp so I'm just trying to get the 1040 tax calculations correct rather than compare Schedule C to S-Corp.

On V22.02 I performed the following steps:
  • Use a blank cell for "S-Corp net profit" near the "Non-paycheck income" section
  • Add the cell from step 1 to D42 "1040 Total Income"
  • Add the cell from step 1 to Y137 "QBI Deduction" "Business Income"
  • Add W-2 wages and any other "Paycheck Items" to the "Paycheck Items" section

Steps 1-3 seem nice and general so perhaps worth including in a future version of the sheet. However, this seems too simple so I'm probably missing something significant...

I know I haven't tried to tackle the Deduction Worksheet for Self-Employed
If the two of you (and any others that care) can concur on some "relatively simple" additions that cover many "straightforward S-Corp" (if there is such a thing) tax situations, the theorem of "multiple people are unlikely to make the same mistake" could be used to support inclusion in the public template. :)

I have no knowledge of S-Corp details....

dandarc

  • Walrus Stache
  • *******
  • Posts: 5545
  • Age: 41
  • Pronouns: he/him/his
Re: Case Study Spreadsheet updates
« Reply #359 on: September 22, 2022, 10:17:59 AM »
Getting to be time for me to triple-check that in fact the solar tax credit + premium tax credits will actually fully wipe out our federal tax bill this year. Can I successfully copy my 2021 mods for the S-Corp and the iterative ACA premium tax credit into the 2022 sheet accurately? Stay tuned to find out.
For those interested in the background, see this dandarc post and follow-ups on that page.

One may include the potential solar tax credit in the amount for cell Calculations!G25.  One reason this tool remains free is that some complicated math (e.g., multiplying the solar costs that go on https://www.irs.gov/pub/irs-pdf/f5695.pdf by 30% for 2022) must be done by the user outside the spreadsheet. ;)
Is that retroactive to January 1? I was sort of pissed initially that the system wasn't actually in service until January 2022, but another 4% in tax credit would certainly be nice.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #360 on: September 22, 2022, 10:48:09 AM »
Getting to be time for me to triple-check that in fact the solar tax credit + premium tax credits will actually fully wipe out our federal tax bill this year. Can I successfully copy my 2021 mods for the S-Corp and the iterative ACA premium tax credit into the 2022 sheet accurately? Stay tuned to find out.
For those interested in the background, see this dandarc post and follow-ups on that page.

One may include the potential solar tax credit in the amount for cell Calculations!G25.  One reason this tool remains free is that some complicated math (e.g., multiplying the solar costs that go on https://www.irs.gov/pub/irs-pdf/f5695.pdf by 30% for 2022) must be done by the user outside the spreadsheet. ;)
Is that retroactive to January 1? I was sort of pissed initially that the system wasn't actually in service until January 2022, but another 4% in tax credit would certainly be nice.
Based on the (g) Applicable percentage section it appears the answer is "yes".

passionfruit17

  • 5 O'Clock Shadow
  • *
  • Posts: 19
Re: Case Study Spreadsheet updates
« Reply #361 on: September 26, 2022, 05:46:18 PM »
I can't seem to find how I tell the system my wife is self-employed?
her business will show a loss this year so she should not have additional self-employment taxes, but I want to be ready for next year when she will be profitable

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #362 on: September 26, 2022, 05:49:14 PM »
I can't seem to find how I tell the system my wife is self-employed?
her business will show a loss this year so she should not have additional self-employment taxes, but I want to be ready for next year when she will be profitable
Will either Calculations!B30 or C30, Schedule C net profit, work?

passionfruit17

  • 5 O'Clock Shadow
  • *
  • Posts: 19
Re: Case Study Spreadsheet updates
« Reply #363 on: September 26, 2022, 06:23:54 PM »
Ah, silly me, schedule C does mean self-employed!
We're new at this.
I had entered her first year losses I'm this very cells, so looks like we're good.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #364 on: September 26, 2022, 06:36:55 PM »
I had entered her first year losses in this very cells, so looks like we're good.
Good indeed!  And thanks for the feedback - the next version will have "Schedule C (Self-Employment) net profit" in A30.
"

dandarc

  • Walrus Stache
  • *******
  • Posts: 5545
  • Age: 41
  • Pronouns: he/him/his
Re: Case Study Spreadsheet updates
« Reply #365 on: September 28, 2022, 12:41:42 PM »
Thanks to @MDM and everyone who has contributed to this sheet!

I already have an S-Corp so I'm just trying to get the 1040 tax calculations correct rather than compare Schedule C to S-Corp.

On V22.02 I performed the following steps:
  • Use a blank cell for "S-Corp net profit" near the "Non-paycheck income" section
  • Add the cell from step 1 to D42 "1040 Total Income"
  • Add the cell from step 1 to Y137 "QBI Deduction" "Business Income"
  • Add W-2 wages and any other "Paycheck Items" to the "Paycheck Items" section

Steps 1-3 seem nice and general so perhaps worth including in a future version of the sheet. However, this seems too simple so I'm probably missing something significant...

I know I haven't tried to tackle the Deduction Worksheet for Self-Employed
This is an interesting thing - the way I've done it, FICA is wrong in the spreadsheet (handled by payroll so less concerned with this than income tax). Because I put the health insurance and HSA on the "Subtractions for AGI" section and using the iterative calculation between Self Employed Health Insurance deduction and the premium tax credit. I kinda need health insurance to be in that iterative calculation because I'm on the line this year to where that matters - if I don't fund any IRAs or soloK, then we are not going to get a premium tax credit. If I do fund traditional IRAs, then we will get a premium tax credit.

dandarc

  • Walrus Stache
  • *******
  • Posts: 5545
  • Age: 41
  • Pronouns: he/him/his
Re: Case Study Spreadsheet updates
« Reply #366 on: September 28, 2022, 02:09:53 PM »
Ok, so I guess I should submit work. Attached is what I have so far. Reminder for anyone this is in no way an "official" Case Study Spreadsheet. Just wanted to estimate 2022 taxes for our situation where I'm working via my one-person LLC taxed as an S-Corp to see if I need to adjust withholding.

Some complicating factors that are specific to us include

buying insurance on the marketplace which the LLC reimburses us for
Funding an HSA
have a solo-K
income in a range that we are eligible for ACA premium tax credits
solar tax credit (we over-payed but at least this mitigates that somewhat)


Note that some changes below required unprotecting the calculations worksheet - I'm sure this voids MDM's warranty, so you're warned.

List of Changes:
1. Added a worksheet for S-Corp and Misc. I just like to see all the numbers layed out this way, and then I can pull numbers in to the calculations tab so I can change them there and not have to remember where everything goes.

2. Earner 1 gross salary/wages includes B5 from the new S-Corp & Misc tab

3. Earner 1 Pretax Health/Dental includes B2 from the new S-Corp & Misc tab (this gets FICA right)

4. Earner 1 Employer-sponsored HSA includes B3 from the new S-Corp & Misc tab (this gets FICA right)

4. Change Calculations Cell B14 to add back B2 & B3 cells from the S-Corp & Misc tab - this makes the total match what your W-2 should show if you're self-employed through your own 1-person S-Corp. The deduction for income tax purposes with the HSA and Health Insurance happens later.

5. Insert a row (columns A-D only) at line 23 of the Non-paycheck income - label / format it S-Corp Net Profit. Note that cell references below in columns A-D are now 1 row down from where they are in MDM's official versions of this preadsheet.

6. Calculations B23 equals S-Corp cell B18 (S-Corp Profit)

7. Calculations B46 equals S-Corp cell B3 (Personal HSA)

8. Cell B49 =AD159 (also turn on iterative calculations - this is self-employed health insurance and it has a circular relationship with the premium tax credits)

9. Cell D49 - change B31 to SUM(B23,B31) and C31 to SUM(C23,C31) but leave remainder of formula the same

10. Enter monthly health insurance premium in B115 (for premium tax credit calculation)

11. Enter any advanced premium tax credit in B116 (for premium tax credit calculation)

12. Add the new S-Corp profit (D23) to Cell Y137 for QBI deduction purposes



13. Cell AD138 - modify from "N/A" to = B9. This is the IRS instructions for above 2% shareholders - skip to line 11 and put the "for FICA" amount here, but is not implemented in a robust way.

14. Cell AD140 - =MAX(AD137,AD138)-AD139 - just pulls in the number from AD138 in our situation.


So changes for PTC / SEHI deduction work for me with these changes, but probably not done in a way that would work for many others. Most other changes fairly generic for a single-person S-Corp, but I agree with MDM that small business taxes get awfully complex / specific, so maybe there isn't a good way to include all this in this spreadsheet?
« Last Edit: September 28, 2022, 02:13:55 PM by dandarc »

dandarc

  • Walrus Stache
  • *******
  • Posts: 5545
  • Age: 41
  • Pronouns: he/him/his
Re: Case Study Spreadsheet updates
« Reply #367 on: September 28, 2022, 02:20:44 PM »
Conclusion - unlikely that I need to increase withholding from $0. Would have to make $6,000 more than estimated for remainder of year and also not off-set that with things like "IRA" contribution for my wife, soloK for myself which could potentially even make it so my tIRA would be fully deductible, etc. So I guess I'm free to earn more than planned and not worry about whether I had enough federal income tax withheld.

Kind of a strange situation though - I overpaid for the solar panels, and took them up on 2% financing at a higher price than cash would have been that makes it really more like 5% financing. But those added together mean I'm likely to have a tax-credit carry forward with the non-refundable solar tax credit. So when I play around with numbers without using up the whole solar tax credit this year, marginal tax rate is 8.5% (PTC percentage) except I should be counting the carry-forward, which I am nearly certain to be able to use within the 5 years (really probably zero this out next year) which brings the marginal tax rate that initially looks like just 8.5% up to at least 20%. Anyway, good times with non-refundable tax credits that are kinda-sorta refundable.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #368 on: October 24, 2022, 05:15:33 PM »
Version 22.05 (2022/2023)
  • Added 2023 inflation-dependent updates, mostly using https://www.irs.gov/pub/irs-drop/rp-22-38.pdf as a source.  Two items are (AFAIK) currently unavailable from the usual sources:
    1) estimates of the 2025 IRMAA tiers which will be based on 2023 income
    2) how 2023 withholding will be calculated (Pub. 15-T).
    Some numbers have been entered for those, probably good enough for now seeing as it's only Oct. 2022.
  • Updated the SocialSecurity tab with the recently released COLA and AWI numbers, projections from the 2022 Trustees' report, and examples from the SSA web site.
Use cell Calculations!R2 to choose the federal tax year.  The ability to choose among multiple years is nice, but raises the minor irritation of how to describe the various release versions as they occur.  The state tax estimates will continue to be updated for only one tax year.  For example, choosing 2023 now will give a good federal estimate but will be using 2022 state tax estimates. 

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

2021 taxes version: 2021 Case Study Spreadsheet
2022/2023 taxes version: 2022 Case Study Spreadsheet

Note:

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #369 on: October 31, 2022, 01:38:31 PM »
Version 22.05a (2022/2023)

All inputs and formulas are identical to v22.05.  For some reason, Excel had not updated a variety of calculated cells.  Thanks to mmm999 for pointing out cells not updating in excel 2003 as one possibility.  Saving v22.05 as a .xlsm or .xlsx file, however, did not cause the calculations to update.

Using CTRL+ALT+F9 as suggested by the final post in the above article did work.  The result is v22.05a, and that is what the "2022/2023 taxes version" link below will retrieve.

I hope this was a one-time fluke.  It would be bad, not just for the CSS, but for any Excel user, if this were a recurring problem.

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

2021 taxes version: 2021 Case Study Spreadsheet
2022/2023 taxes version: 2022 Case Study Spreadsheet

Note:

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #370 on: November 25, 2022, 05:02:34 PM »
Version 22.06 (2022/2023)

Primarily a "maintainability" update: simplifying some cell formulas for the Earned Income and Child Tax credit eligibility and calculations. 

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

2021 taxes version: 2021 Case Study Spreadsheet
2022/2023 taxes version: 2022 Case Study Spreadsheet

Note:

fisherboy

  • 5 O'Clock Shadow
  • *
  • Posts: 3
Re: Case Study Spreadsheet updates - Macros
« Reply #371 on: November 27, 2022, 07:02:14 PM »
I'm a new user to to the Personal Finance Tookit, aka 2022 Cash Flow, and am on the Calculations tab.  There is a graph with various options for the X-Y axis, near cell G107.   If I change the values for the X or Y axis, a message "Click button below to update the  chart".  However, no button appears.  I enabled macros on the spreadsheet using the "Macro Security" option on the Developer tab (or think I did), but this did not help.  I'm using the latest version of excel that come with Microsoft 365.

Thanks,
Fisherboy

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates - Macros
« Reply #372 on: November 27, 2022, 07:12:02 PM »
If I change the values for the X or Y axis, a message "Click button below to update the  chart".  However, no button appears.  I enabled macros on the spreadsheet using the "Macro Security" option on the Developer tab (or think I did), but this did not help.  I'm using the latest version of excel that come with Microsoft 365.
See the Roth Conversion and Capital Gains On ACA Health Insurance article.  Find the "What happens if instead of Roth conversions they realize some long-term capital gains?" section. 

Are you saying that the "Update chart" button that appears on the screenshot there is not present for you?

fisherboy

  • 5 O'Clock Shadow
  • *
  • Posts: 3
Re: Case Study Spreadsheet updates
« Reply #373 on: November 27, 2022, 09:01:04 PM »
Quote
Are you saying that the "Update chart" button that appears on the screenshot there is not present for you?
Correct, there isn't a button, just some text "push button below to update chart" at cell K113.  Clicking the cells below has no effect.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #374 on: November 27, 2022, 09:23:01 PM »
Quote
Are you saying that the "Update chart" button that appears on the screenshot there is not present for you?
Correct, there isn't a button, just some text "push button below to update chart" at cell K113.  Clicking the cells below has no effect.
Could be that you are still working with the Google Sheets version of the file.  That is what appears when clicking on the link to the spreadsheet.  If that is the case, to get it into Excel use File > Download > Microsoft Excel (.xls).  Then things should work much better.

Does that work?

fisherboy

  • 5 O'Clock Shadow
  • *
  • Posts: 3
Re: Case Study Spreadsheet updates
« Reply #375 on: November 28, 2022, 05:12:42 PM »
I downloaded the latest CSS spreadsheet for 2022, saved it to excel.  Then I opened it, enabled macros, and pulled down and changed they Y axis of the graph, and the button appeared.  I'm not sure what happened to the earlier downloaded excel file.  I noticed the earlier version was only 525K, and the newer download 1457K.  Maybe some macros got deleted somehow.  Regardless, thanks for the help!

EscapeVelocity2020

  • Magnum Stache
  • ******
  • Posts: 4885
  • Age: 50
  • Location: Houston
    • EscapeVelocity2020
Re: Case Study Spreadsheet updates
« Reply #376 on: December 06, 2022, 12:04:06 PM »
FYI - this sheet / thread just got a shout-out from The Finance Buff (Roth Conversion with Social Security and Medicare IRMAA)  Good work MDM!

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #377 on: December 06, 2022, 02:40:40 PM »
FYI - this sheet / thread just got a shout-out from The Finance Buff (Roth Conversion with Social Security and Medicare IRMAA)  Good work MDM!
Thanks.

Harry Sit (aka The Finance Buff) does excellent work.  I use his projections for various inflation-indexed things (e.g., IRMAA tiers) and appreciate this and other great step-by-step articles he has published.

EchoStache

  • Pencil Stache
  • ****
  • Posts: 864
Re: Case Study Spreadsheet updates
« Reply #378 on: December 18, 2022, 10:34:58 AM »
Hi, I'm wondering where to put the Federal EV tax credit of $7500 and heat pump water heater of $300 into the spreadsheet?  I'm afraid my tax liability is too low this year to get the entire $7,800 and may need to do a Roth conversion to increase my taxable income.  Thanks..

P.S. I'm concerned I don't have the sheet filled out correctly in the taxes section.  I should probably do a trial run with turbo tax or something to double check?

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #379 on: December 18, 2022, 11:51:12 AM »
Hi, I'm wondering where to put the Federal EV tax credit of $7500 and heat pump water heater of $300 into the spreadsheet?  I'm afraid my tax liability is too low this year to get the entire $7,800 and may need to do a Roth conversion to increase my taxable income.  Thanks..

P.S. I'm concerned I don't have the sheet filled out correctly in the taxes section.  I should probably do a trial run with turbo tax or something to double check?
That would be $7800 into cell G25, "Sched. 3 Lines 5&6" - see https://www.irs.gov/pub/irs-pdf/f1040s3.pdf.

Screenshot examples others have done such as Roth Conversion with Social Security and Medicare IRMAA, and using a spreadsheet to evaluate Roth IRA conversions, might help for other entries.

EchoStache

  • Pencil Stache
  • ****
  • Posts: 864
Re: Case Study Spreadsheet updates
« Reply #380 on: December 18, 2022, 02:01:04 PM »
Hi, I'm wondering where to put the Federal EV tax credit of $7500 and heat pump water heater of $300 into the spreadsheet?  I'm afraid my tax liability is too low this year to get the entire $7,800 and may need to do a Roth conversion to increase my taxable income.  Thanks..

P.S. I'm concerned I don't have the sheet filled out correctly in the taxes section.  I should probably do a trial run with turbo tax or something to double check?
That would be $7800 into cell G25, "Sched. 3 Lines 5&6" - see https://www.irs.gov/pub/irs-pdf/f1040s3.pdf.

Screenshot examples others have done such as Roth Conversion with Social Security and Medicare IRMAA, and using a spreadsheet to evaluate Roth IRA conversions, might help for other entries.

Hmm, G25 for me is "Tax after n-r credit".  I'll keep looking as I'm sure I'm just overlooking it.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #381 on: December 18, 2022, 04:00:39 PM »
Version 22.07 (2021/2022/2023)
  • Update the 2023 withholding calculations per the recently released Pub. 15-T
  • Fix ACA lookup for Hawaii
  • Clarify that charitable "above the line" contributions apply only to 2021
The main purpose of this update is to enter the last IRS updates for TY2023 (the 15-T withholding calculations).  At this point, unless/until Congress changes the rules, the 2023 tax estimate should be good to go. 

One of these days I'll figure out how to name new versions, as use of the 'Tax Code' tab (and provided tax law changes don't become too extreme) means any of several tax year estimates can be done with the same spreadsheet.  The 2021 option will probably go away when the IRS 2024 numbers are released, and we'll see what happens as 2026 approaches, but until then....

When Harry Sit (aka The Finance Buff) estimates the 2025 IRMAA tiers, the impact of 2023 income will be updated.  Currently (see row 180 and below on the 'Tax Code' tab) those are merely a linear extrapolation from the 2023 and 2024 tiers (that apply to 2021 and 2022 income).  See 2022 2023 2024 Medicare Part B IRMAA Premium MAGI Brackets for those estimates.

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

2021/2022/2023 taxes version: 2022 Case Study Spreadsheet

Note:

nolesrule

  • 5 O'Clock Shadow
  • *
  • Posts: 11
Re: Case Study Spreadsheet updates
« Reply #382 on: January 08, 2023, 08:06:41 PM »
MDM, do you have a plan for handling changes to state taxes by year with the sheet?

I think it's been a couple of years since I've provided any feedback. I started filling out a 2023, and noticed it's using North Carolina's 2022 tax rate. They've been lowering the rate every year and plan to continue to do so for the foreseeable future

Current changes under NC law....

2021 - 5.25%
2022 - 4.99%
2023 -4.75%
2024 - 4.6%
2025 - 4.5%
2026 - 4.25%
2027+ - 3.99%

https://www.ncdor.gov/taxes-forms/tax-rate-schedules
« Last Edit: January 08, 2023, 08:08:48 PM by nolesrule »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #383 on: January 08, 2023, 11:18:05 PM »
MDM, do you have a plan for handling changes to state taxes by year with the sheet?
Good question!  Now that one can select different years for federal returns by changing cell Calculations!R2 (currently 2021, 2022, or 2023) that does leave the questions of state taxes.

Historically I wait for the Tax Foundation's State Individual Income Tax Rates and Brackets update, usually in February, and with a little reformatting that provides the update to the 'State Brackets' tab.  Presumably the 2023 NC rate will be updated then.

Currently there is no plan to allow the same choice of state tax year as there is for the federal tax year.  Although (and thanks in no small part to feedback from yourself and others) I believe the federal calculations to be extremely accurate, in general there isn't nearly the same effort expended for state calculations.  Even so, most if not all of the state calculations are "reasonably accurate" (or at least any egregious errors have gone unreported).

Thus the timing of the Tax Foundation updates works well: by ~mid-February of any year, people seeking "accuracy for federal and state filing purposes" should be using commercial tax software to prepare the previous year's taxes, and using the previous year's state tables for the first ~1.5 months of the new year is probably well within the margin of error for predicting brokerage income, etc.

Perhaps a longer reply than needed, but your question gave me the impetus to think through what I had only vaguely considered....  Further feedback welcome!

nolesrule

  • 5 O'Clock Shadow
  • *
  • Posts: 11
Re: Case Study Spreadsheet updates
« Reply #384 on: January 09, 2023, 07:48:51 AM »
That's a fair answer. I use your sheet  to compare the numbers with my owns sheet (which was built only for our specific situation and I have to add on new features as necessary). Fed has been pretty accurate lately and that's great and probably most important. When I see a discrepancy I try to figure out the root cause and if it's in my sheet I make adjustments and if it's in yours I provide feedback.

MarkVH0518

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: Case Study Spreadsheet updates
« Reply #385 on: January 09, 2023, 12:59:47 PM »
Firstly, Wowza!  Thank you very much for this tool.
I'm using the tool to understanding my marginal bracket when determining IRA contributions.
Sheepishly --  I think I found a bug in my situation.  But maybe there's a knob I don't see!  Here are the moving parts:
Filing Jointly; Wages $1234; Non-Qual divs $70 ; Qual Divs $480; STCG ($3000)
tIRA distribution $100
Pension $35857; SS Benefits $24343
Parameter under study tIRA contributions: Start with $1000
The tool calculates my tax bracket as -38%, while H&R Block SW says -34%
The discrepancy is saver's tax credit.
I've also received Roth IRA distributions larger than the proposed $1000 potential IRA contribution.
Therefore, according to 8880 I'm not eligible for Savers Credit.
Yet, I don't see how I can enter my Roth distributions and thereby disabling saver's credit in the tool.
Regardless of this issue the tool, via the graphic of tIRA contribution vs. tax, has demonstrated my marginal bracket sufficiently
for me to make any tax decisions.
I'm open to the likelihood that is not worth offering, but maybe it's there and I just don't see it.
Thank you so very much
Mark




MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #386 on: January 09, 2023, 04:26:08 PM »
I've also received Roth IRA distributions larger than the proposed $1000 potential IRA contribution.
Therefore, according to 8880 I'm not eligible for Savers Credit.
Thanks for the feedback!

Your situation is real but somewhat unusual, and how the CSS can handle it is there but somewhat obscure.  If one enters IRA distributions (but not trad->Roth conversions) in cell V58 (and W58 for MFJ) it should work correctly.  Do let us know if not.

dagraf63

  • 5 O'Clock Shadow
  • *
  • Posts: 4
Re: Case Study Spreadsheet updates
« Reply #387 on: January 10, 2023, 07:45:10 AM »
Has the spreadsheet been updated to eliminate the ACA cliff as was done in the “inflation reduction” act late last year?  I’m using v22.07 with the same inputs as last year and seeing a big Excess APTC tax that I didn’t have in 2022.  If I change cell AD94 so that tax year 2023 results in “No” then things seem to work as I expect.  Thanks!

MarkVH0518

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: Case Study Spreadsheet updates
« Reply #388 on: January 10, 2023, 08:04:29 AM »
Your situation is real but somewhat unusual, and how the CSS can handle it is there but somewhat obscure.  If one enters IRA distributions (but not trad->Roth conversions) in cell V58 (and W58 for MFJ) it should work correctly.  Do let us know if not.
That did it!  Now that I see the reference to the IRS form numbers that's what I'll look for when using the tool in the future.
Thanks for responding to my request and thanks again so much for creating and maintaining the tool.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #389 on: January 10, 2023, 05:31:56 PM »
Has the spreadsheet been updated to eliminate the ACA cliff as was done in the “inflation reduction” act late last year?  I’m using v22.07 with the same inputs as last year and seeing a big Excess APTC tax that I didn’t have in 2022.  If I change cell AD94 so that tax year 2023 results in “No” then things seem to work as I expect.  Thanks!
Thanks for the feedback - yes, "2023" was missing from AD94.

I and the other usual beta testers are unusually busy right now.  ETA: Found some time.  See update for version 2208 below.

« Last Edit: January 15, 2023, 03:01:03 PM by MDM »

dagraf63

  • 5 O'Clock Shadow
  • *
  • Posts: 4
Re: Case Study Spreadsheet updates
« Reply #390 on: January 11, 2023, 08:23:00 AM »
Relieved to confirm I wasn't so perilously close to falling off the ACA cliff!

I checked my situation using draft version 2208 and comparing to a hand calc using the 2022 form 8962.  I found that the poverty level in the spreadsheet is a bit higher than in 2022 (I'm assuming that's just an updated amount for 2023 and is correct).  It looks like the table of values in cells AG109:AH117 need to be checked though--in my situation (FPL% = 401) the Applicable Figure should be 0.085 (the limit in the 2022 legislation eliminating the cliff).  If I use 0.085 then the Form 8962 values calculated by draft version 2208 are correct for my situation.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #391 on: January 11, 2023, 03:06:34 PM »
I found that the poverty level in the spreadsheet is a bit higher than in 2022 (I'm assuming that's just an updated amount for 2023 and is correct).
Yes. 

Quote
It looks like the table of values in cells AG109:AH117 need to be checked though--in my situation (FPL% = 401) the Applicable Figure should be 0.085 (the limit in the 2022 legislation eliminating the cliff).
Always good to check!  In this case the numbers are correct: see Applicable Percentage Table for 2022 and p. 3 of https://www.irs.gov/pub/irs-drop/rp-22-34.pdf to confirm 2022 and 2023 respectively.

Thanks for the beta test!

dagraf63

  • 5 O'Clock Shadow
  • *
  • Posts: 4
Re: Case Study Spreadsheet updates
« Reply #392 on: January 12, 2023, 12:40:06 PM »
Always good to check!  In this case the numbers are correct: see Applicable Percentage Table for 2022 and p. 3 of https://www.irs.gov/pub/irs-drop/rp-22-34.pdf to confirm 2022 and 2023 respectively.

Thanks for the beta test!
Happy to help!

I think, though, that those numbers (from the American Recovery Plan) were superseded by the more recent "Inflation Reduction" Act.
See SEC. 12001.IMPROVE AFFORDABILITY AND REDUCE PREMIUM COSTS OF HEALTH INSURANCE FOR CONSUMERS at https://www.congress.gov/bill/117th-congress/house-bill/5376/text

And the IRS code it amended, at https://www.taxnotes.com/research/federal/usc26/36B (see the second table on that page).

Not a huge deal if I'm understanding it correctly, but it could lead to material changes in spreadsheet values.


secondcor521

  • Walrus Stache
  • *******
  • Posts: 5598
  • Age: 55
  • Location: Boise, Idaho
  • Big cattle, no hat.
    • Age of Eon - Overwatch player videos
Re: Case Study Spreadsheet updates
« Reply #393 on: January 12, 2023, 04:57:08 PM »
Always good to check!  In this case the numbers are correct: see Applicable Percentage Table for 2022 and p. 3 of https://www.irs.gov/pub/irs-drop/rp-22-34.pdf to confirm 2022 and 2023 respectively.

Thanks for the beta test!
Happy to help!

I think, though, that those numbers (from the American Recovery Plan) were superseded by the more recent "Inflation Reduction" Act.
See SEC. 12001.IMPROVE AFFORDABILITY AND REDUCE PREMIUM COSTS OF HEALTH INSURANCE FOR CONSUMERS at https://www.congress.gov/bill/117th-congress/house-bill/5376/text

And the IRS code it amended, at https://www.taxnotes.com/research/federal/usc26/36B (see the second table on that page).

Not a huge deal if I'm understanding it correctly, but it could lead to material changes in spreadsheet values.

No.  The Inflation Reduction Act did not change the more beneficial applicable figures for ACA subsidies that were initially implemented in ARPA (American Rescue Plan Act).  It merely extended them through the end of 2025.

I haven't checked the spreadsheet, but I'm sure @MDM has the most recent more beneficial figures already implemented since ARPA is almost two years old now.  The spreadsheet should be fine in this regard, at least until the law changes again.
« Last Edit: January 12, 2023, 04:58:59 PM by secondcor521 »

dagraf63

  • 5 O'Clock Shadow
  • *
  • Posts: 4
Re: Case Study Spreadsheet updates
« Reply #394 on: January 13, 2023, 01:45:08 PM »
The Inflation Reduction Act did not change the more beneficial applicable figures for ACA subsidies that were initially implemented in ARPA (American Rescue Plan Act).  It merely extended them through the end of 2025.

That’s why I questioned the “applicable percentage” reported on line 7 of form 8962.  For 2022 incomes over 400% of poverty level, it reports 8.5%, but 9.12% when I switch to 2023.  9.12% is the affordability percentage for 2023 employer sponsored plans, perhaps coincidentally.
« Last Edit: January 13, 2023, 01:52:47 PM by dagraf63 »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #395 on: January 13, 2023, 01:56:11 PM »
Thanks to dagraf63, I believe Congress did change the rules a few weeks after the IRS published the revenue procedure that updated the 2023 table.

Based on "Text contains those laws in effect on January 12, 2023" I'll modify the CSS to reflect 26 USC 36B: Refundable credit for coverage under a qualified health plan.

dagraf63, welcome to the informal beta test crew!

secondcor521

  • Walrus Stache
  • *******
  • Posts: 5598
  • Age: 55
  • Location: Boise, Idaho
  • Big cattle, no hat.
    • Age of Eon - Overwatch player videos
Re: Case Study Spreadsheet updates
« Reply #396 on: January 13, 2023, 04:06:13 PM »
The Inflation Reduction Act did not change the more beneficial applicable figures for ACA subsidies that were initially implemented in ARPA (American Rescue Plan Act).  It merely extended them through the end of 2025.

That’s why I questioned the “applicable percentage” reported on line 7 of form 8962.  For 2022 incomes over 400% of poverty level, it reports 8.5%, but 9.12% when I switch to 2023.  9.12% is the affordability percentage for 2023 employer sponsored plans, perhaps coincidentally.

Ah, OK.  I misunderstood the point you were making.  Sounds like you and MDM have it figured out anyway.

chasingsnow

  • Bristles
  • ***
  • Posts: 355
  • Location: Small Mountain Town British Columbia, Canada
Re: Case Study Spreadsheet updates
« Reply #397 on: January 13, 2023, 09:38:32 PM »
Man this case study spreadsheet is so cool, I wish their was a Canadian version of it!!

MDM

  • Senior Mustachian
  • ********
  • Posts: 11528
Re: Case Study Spreadsheet updates
« Reply #398 on: January 15, 2023, 03:00:54 PM »
Version 22.08 (2021/2022/2023)
  • Fixed issues with 2023 ACA premium tax credits described in recent posts.
Thanks again to dagraf63 for spotting the change Congress made.  Not only was the enormous 400% FPL cliff removed through 2025, any inflation-dependent increases to expected contribution percentages are also on hold through 2025.  Of course, that is subject to Congress not making further changes....

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

2021/2022/2023 taxes version: 2022 Case Study Spreadsheet

Note:

dandarc

  • Walrus Stache
  • *******
  • Posts: 5545
  • Age: 41
  • Pronouns: he/him/his
Re: Case Study Spreadsheet updates
« Reply #399 on: February 05, 2023, 05:22:11 PM »
#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.