Author Topic: Case Study Spreadsheet updates  (Read 220041 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: 11043
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: 11043
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: 11043
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: 5041
  • Age: 39
  • 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: 11043
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: 11043
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: 5041
  • Age: 39
  • 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: 11043
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: 10
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: 11043
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: 10
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: 11043
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: 5041
  • Age: 39
  • 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: 5041
  • Age: 39
  • 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: 5041
  • Age: 39
  • 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.