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

RWTL

  • Bristles
  • ***
  • Posts: 271
Re: Case Study Spreadsheet updates
« Reply #200 on: November 30, 2020, 04:02:33 AM »
Thanks for continuing to work on this.  I use this tool religiously throughout the year to track my withholdings.

It probably wouldn't apply to most people, but I also manually track my solar credit (form 5695). 

M

dandarc

  • Magnum Stache
  • ******
  • Posts: 4283
  • Age: 38
Re: Case Study Spreadsheet updates
« Reply #201 on: December 02, 2020, 05:41:13 PM »
MDM's spreadsheet is better than early release of Turbotax Online.

green2bend

  • 5 O'Clock Shadow
  • *
  • Posts: 1
Re: Case Study Spreadsheet updates
« Reply #202 on: December 26, 2020, 05:19:57 PM »
Thanks for this great resource.

In the 2020 version (the only one I've looked at), The Earned Income Tax Credit calculation seems not to apply the investment income restriction properly. From Worksheet 1 in Pub596:

Quote
5.   Enter the amount from Form 1040 or 1040-SR, line 6. If the amount on that line is a loss, enter -0-

I think this means that the G28 cell needs to change from

Quote
=IF(AND((D23+D24+B25*B163+D28+D36)<3650,OR(AND(G8>=25,G8<65),AND(H8>=25,H8<65),G6>0)),MAX(0,Z26),0)

to

Quote
=IF(AND((D23+D24+B25*B163+MAX(0,D28)+D36)<3650,OR(AND(G8>=25,G8<65),AND(H8>=25,H8<65),G6>0)),MAX(0,Z26),0)

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet updates
« Reply #203 on: December 26, 2020, 06:25:08 PM »
I think this means that the G28 cell needs to change....
Right you are!

Not only that limit, but the rental income needs to be limited similarly.  Had a thing or two ready for a new version, so I'll add this and release the new version soon.

Gotta love crowd sourcing!

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet (CSS) updates
« Reply #204 on: December 26, 2020, 07:18:13 PM »
Version 20.11 (2020)
Version 21.01 (2021)

2020
  • Allow for less than 12 months in form 1095-A
  • Lower limit of 0 for EIC investment income items
2021
  • Updated withholding tables to match 2021 Pub. 15-T
  • Allow for less than 12 months in form 1095-A
  • Lower limit of 0 for EIC investment income items
2020 taxes version: 2020 Case Study Spreadsheet
2021 taxes version: 2021 Case Study Spreadsheet

Note:

fuzzy math

  • Handlebar Stache
  • *****
  • Posts: 1198
  • Age: 39
  • Location: PNW ---> Midwest (for now)
Re: Case Study Spreadsheet updates
« Reply #205 on: January 03, 2021, 11:28:54 AM »
After a few failed tries over the years, I finally got my spreadsheet working! I even got 2021 up and running so I can estimate my taxes. Its going to save me a massive headache come April 2022. Thanks MDM!

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet updates
« Reply #206 on: January 03, 2021, 11:42:55 AM »
After a few failed tries over the years, I finally got my spreadsheet working! I even got 2021 up and running so I can estimate my taxes. Its going to save me a massive headache come April 2022. Thanks MDM!
Nice going!

Speaking of 2021, congress has once again changed the 10% of AGI floor for itemized medical expenses to 7.5% of AGI.  Doesn't seem worth a whole version update to change that, so until the next update if anyone care about this nuance then change cell 'Calculations'G39 to replace 10% with 7.5% as shown below:
=MAX(0,SUM(D95:D96)+SUM(D113:D116)+(B48*$B$164+C48*$C$164-D48)+G19-G31-7.5%*G11)

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet (CSS) updates
« Reply #207 on: January 03, 2021, 08:19:28 PM »
Version 20.12 (2020)
Version 21.02 (2021)

2020
  • Fix typo in EIC table
2021
  • 7.5% of AGI floor for itemized medical expenses
2020 taxes version: 2020 Case Study Spreadsheet
2021 taxes version: 2021 Case Study Spreadsheet

Ah, what the heck, the changes are minimal but anyway....

Note:

nolesrule

  • 5 O'Clock Shadow
  • *
  • Posts: 9
Re: Case Study Spreadsheet updates
« Reply #208 on: January 17, 2021, 01:19:02 PM »
The Cash donation tax credit has changed in 2021.

It is now $600 for MFJ. Also it is now a post-AGI deduction to income, so it is subtracted after AGI is calculated.

fyre4ce

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: Case Study Spreadsheet updates
« Reply #209 on: January 24, 2021, 10:18:52 PM »
Hi there!

I'm new to MMM. I've been active on Bogleheads for a few years, and have done some editing to the wiki. I'm working on a revision to the Traditional versus Roth wiki page. It's become clear that one of the biggest issues for readers is how to to estimate their future marginal tax rate. The wiki page lays out a good method, but it's long enough that only the most ambitious readers are making their own spreadsheets to make the calculation. We discussed adding some kind of tool, and I have my own stand-alone spreadsheet that we could polish and upload. But, given the popularity of the CSS tool, I much prefer the idea of adding this capability to CSS, and then the wiki page can point to there.

So, I took a swing at adding a new worksheet to the CSS tool, which estimates current and future marginal tax rates, and then calculates the future values for five different tax structures: tax-free (eg. HSA for qualifying expenses), Roth, pre-tax, non-deductible (eg. ND-tIRA, VA), taxable. Future marginal rates are estimated by calculating future pre-tax and taxable balances using Future Value functions, then adding in Social Security and other forms of income. It can import data from the Calculations worksheet, or accept over-riding user inputs. It handles special cases of contributing the account maximum, and also not getting the full employer match. I also added a "Social Security Spike Checker" where it runs the math in reverse, to check the case of trying to come in just under the spike in SS taxation (22.2% or 40.7%), then calculating future balances, tax due, and after-tax income in retirement to see if it's a better strategy. Overall, I think this would be a valuable toolset to add to the CSS.

Link: https://drive.google.com/file/d/1InHgvpUOmwAujWAREoV20v7PYpupJA1I/view?usp=sharing

If this addition is accepted, it would replace the "Non-ded. IRA" worksheet completely, and also the "Growth in a taxable account," "Roth vs. Traditional when contributing the maximum allowed," and "Estimating withdrawal tax rates calculators" on the "Misc. Calcs" worksheet. It would also probably be wise to adapt the "401k vs Taxable" worksheet into mine, so they could work from a common set of inputs, but I decided not to spend the time to do that until it seemed likely that my worksheet would be accepted in some form.

Two difficulties I had: The CSS has a sophisticated tax calculator I didn’t figure out how to use, so I “rolled my own” on that worksheet off to the side. I think I got it pretty close, but if it’s easier to call already existing tax functions, then that’s clearly the right move. I also wasn’t able to have a comprehensive state tax model (like the existing sheet, where you put in the state postal code and it does the rest); mine requires the user to input the state marginal rate manually. These are areas for improvement.

I added a feature where a period of early withdrawal (early retirement, pre-SS Roth conversions) can be added to the FV calculation. This presents a problem for the case of coming in under the SS spike, because it's not clear what the equivalent decision would be in that case. I coded it now to take early withdrawals in proportion to the smaller pre-tax balance, but I'm not 100% sure this is the best approach. Feedback on this would be appreciated, and it could just make sense to eliminate the early withdrawal feature altogether.

Anyway, that's my suggestion; looking forward to hearing the group's thoughts on it.

Best,
"fyre4ce"

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet updates
« Reply #210 on: January 25, 2021, 07:20:54 AM »
Thanks for the reminders/suggestions.  nolesrule, I'm somewhat waiting to see if Congress throws any more curve balls for 2021, but the QBI-ish charitable item should be in the next update.

fyre4ce, welcome to the forum.  It is a bit different than Bogleheads, especially in the off-topic board....  Always nice to get suggestions with a potential solution included!

The CSS has developed to where it is thanks to crowd-sourcing such as this.  I'll take a look at the details.

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet updates
« Reply #211 on: January 26, 2021, 12:56:28 PM »
So, I took a swing at adding a new worksheet to the CSS tool....
Nice work!  That must have taken some effort!

If you had to pick the most important one or two or three concepts you are proposing, what would they be?

fyre4ce

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: Case Study Spreadsheet updates
« Reply #212 on: January 26, 2021, 03:17:34 PM »
So, I took a swing at adding a new worksheet to the CSS tool....
Nice work!  That must have taken some effort!

If you had to pick the most important one or two or three concepts you are proposing, what would they be?

Here are what I think are the most important concepts, in decreasing order of importance:

1) Powerful and usable tool to estimate future marginal tax rates. This is an input to many types of investment problems, and in my experience most people just guess. This was the motivator behind making this proposed update in the first place. It synergizes well with the tax model in the CSS, because it can do a direct lookup of the current marginal rate for a given contribution size. But, I wrote it so that the user can manually override most of the numbers at various points through the calculation. The core of the math is using a Future Value function to estimate future taxable income:

[estimated future taxable withdrawals] = FV([expected real return],[expected years to withdrawal],-[expected future annual contributions],-[current pre-tax account balance]) * [expected future withdrawal rate]

A similar calculation is repeated for taxable investments. Then, add SS income and other income, subtract the age 65+ standard deduction, and do a lookup in the tax rate table to get an estimated marginal future tax rate.
2) Once you have current and future marginal rates, I like having future values for the different tax structures all in one place. Users can compare pre-tax to Roth, pre-tax and Roth to taxable, non-deductible to taxable, etc. Having them all in one spot makes the spreadsheet more compact and takes the place of separate calculators (like "Growth in a taxable account"). Also, I've noticed not all taxable calculators get the basis correct. I believe I have the most accurate formula (derived here). I coded it to do either annual or continuous compounding, but I'd be OK removing that feature and only offering annual, if it were an issue.
3) A traditional versus Roth calculator should definitely have a "contributing the maximum" feature, which is very easy to add once the other pieces are there. This is partly why it makes a lot of sense to include the taxable formula, because this is necessary for this math. Formulas used are here.
4) I've never seen a formula for deciding between traditional and Roth when you're not able to get the full employer match, so I derived one here. I think it's a nice feature that could help some users, and it makes sense to have it along with the other features of that sheet. But, it probably affects only a small number of users, so that's why I list it further down in my priority list.
5) The "Social Security Spike Checker" is actually the most complicated sub-tool on the page, and the easiest to separate if necessary. But, it could be useful to a lot of users so I included it. It runs the same calculation for an alternate scenario of trying to go underneath the tallest spike of SS taxation in retirement. The algorithm goes like this:

a) Future out the amount of other (non-SS) income in retirement necessary to get the user just barely under the 22.2% or 40.7% spike in SS taxation. Formulas for these values are derived here.
b) Given a withdrawal rate, figure out the future pre-tax balance that will generate this amount of income.
c) Figure out a reduced pre-tax contribution that will get the user to this balance, and contribute the rest to Roth. If this change leaves extra room in the Roth account to switch some taxable investments to Roth, do it, but taxable contributions can't go below 0.
d) Using the new pre-tax, Roth, and possibly taxable contributions, and keeping the other variables the same, calculate the total tax due for this alternate scenario.
e) Calculate the total income available after taxes for this alternate scenario, and compare it to the baseline. The lower-performing scenario is indicated with a "-X.XX%" to show the relative size of the difference.

Hope that helps!
« Last Edit: January 26, 2021, 03:25:59 PM by fyre4ce »

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet updates
« Reply #213 on: January 27, 2021, 06:59:22 PM »
I went into a little more detail in a PM to fyre4ce, but some of these items may be added piecemeal along with updates for the 2021 state taxes, etc.

dandarc

  • Magnum Stache
  • ******
  • Posts: 4283
  • Age: 38
Re: Case Study Spreadsheet updates
« Reply #214 on: February 01, 2021, 07:59:38 AM »
Apologies if this is not a good place to ask this. I'm trying to figure out how much tax savings there would be being taxed as an S-Corp vs. Sole proprietor and I'm wondering if the case study spreadsheet is a good place to do that. Looking specifically to include self employed health insurance, soloK, QBI in the analysis, and it is unclear where I might enter everything I need to get a good estimate on taxes in this spreadsheet. I'm not having success figuring out where to put the 'business profit' or how to do soloK in the spreadsheet. But I also haven't found a good calculator elsewhere online - so far every one I've tried seems to leave some significant parts out of the analysis.

This spreadsheet has proven better even than early-release tax software in the past, so I'm thinking it is just user error. Need to figure this out as I'm going to have one chance with my pending "highly-paid-barista-FIRE" opportunity, that notably due to the part-time hours will put me under the social security wage base on gross. In the past when I've been on 1099, I've been far enough over that line that the usual marginal rate on "save self employment tax" was quite a bit lower than it would be for the usual analysis and I've always landed on "eh not worth it" in terms of actually setting up the S-Corp. Was probably a mistake to be lazy about this even then, but it is potentially an even bigger mistake for this.

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet updates
« Reply #215 on: February 01, 2021, 03:32:34 PM »
Apologies if this is not a good place to ask this. I'm trying to figure out how much tax savings there would be being taxed as an S-Corp vs. Sole proprietor and I'm wondering if the case study spreadsheet is a good place to do that. Looking specifically to include self employed health insurance, soloK, QBI in the analysis, and it is unclear where I might enter everything I need to get a good estimate on taxes in this spreadsheet.
I'm not at all expert on small business taxes (there are some on this forum who are), but perhaps some of the items in this post would be relevant?

There will be state tax updates from Individual Income and Payroll Taxes | Tax Foundation, some of fyre4ce's suggestions, etc., that will probably happen over the next few weeks (maybe sooner, but...?) so as long as I'm making changes, if some incremental ones would be useful for your question, this could be a good time.  But I may need "guidance". :)

dandarc

  • Magnum Stache
  • ******
  • Posts: 4283
  • Age: 38
Re: Case Study Spreadsheet updates
« Reply #216 on: February 03, 2021, 01:28:43 PM »
So what I wound up doing (this is nowhere near the level of quality MDM provides, just how I got the spreadsheet to help me say "yes, this is going to be worth the effort"):

1. Create a "S-Corp" flag in cell E3. If this is 1, then the Social Security & Medicare line items (B60 & B61) double to reflect that I'm paying for both sides.

2. Enter "Salary" + "Heath Insurance" + "HSA" in B3 - in line with guidance that all these will wind up on W-2

3. Enter "Health Insurance" in B4 - again in line with guidance that while we report health insurance premiums reimbursed by the S-Corp as Income on our W-2, it is not subject to FICA and we deduct this on the self-employed health insurance deduction line. Need to put it into this section so we get FICA to calculate right.

4. Enter "HSA" in B7 for same reasoning as for "Health Insurance"

5. Enter Employee-deferral SoloK amount in B11

6. Calculate expected 'profit' from the S-Corp as follows:
Gross - Employer half of FICA - Health Insurance - HSA - Employer SoloK - Corp Fees - Insurance

Basically gross receipts minus expenses. Gets confusing because of how all this is reported to IRS, but end of day, that's what I'm expecting to be treated as business profit.

7. Enter the amount from step 6 as an addition to cell D23 (taxable interest, non qualified dividends and so on). Really put this anywhere that is subject to income tax but not FICA.

8. To simulate QBI deduction, cell B51 for now is "= 6000 + .2 * amount from step 6". I haven't dug far enough into the details on this, but I'm expecting to now be limited by S-Corp profit instead of taxable income for QBI deduction.

So I then take cell D67 for "total taxes" and compare that to what I get for the sole proprietor version (which the spreadsheet handles well already). That gives us 'tax savings for going S-Corp', and then I can decide if that is enough to justify the the additional cost / hassle of setting this all up. Spoiler: it is. About $7500 top line tax savings which means even after LLC fees and so on, I'm expecting $6,000 + per year net benefit. I think I'll also owe unemployment tax, but that's at most about $500 / year. I'd have to be missing a whole lot for this to not be worth an hour or two per month in additional bookkeeping and tax filing. Real happy I'm in Florida - no state income tax oddities to worry about from what I've read.

A whole lot of work to get this to be robust to where it might be considered for inclusion in the case study spreadsheet - maybe I'll spend some of the 2 additional days per week off in the near future doing that.

SomedayStache

  • Pencil Stache
  • ****
  • Posts: 795
  • Live Long and Prosper
Re: Case Study Spreadsheet updates
« Reply #217 on: February 05, 2021, 07:22:21 AM »
Super duper minor comment (prob not even worth posting due to the target audience of this spreadsheet), but cell 16A `Roth 401k/403b` could have `Roth TSP` added to the description.

YummyRaisins

  • Stubble
  • **
  • Posts: 145
Re: Case Study Spreadsheet updates
« Reply #218 on: February 10, 2021, 08:16:53 PM »
Thank you so much for this fantastic tool, MDM!

My employer has a tax-qualified employee stock purchase plan. Where should I put contributions to that in the spreadsheet? Would it interfere with any other formulas if I stuck it in Employer-sponsored HSA or Pretax Commuter Costs?

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet updates
« Reply #219 on: February 10, 2021, 09:59:58 PM »
Thank you so much for this fantastic tool, MDM!

My employer has a tax-qualified employee stock purchase plan. Where should I put contributions to that in the spreadsheet? Would it interfere with any other formulas if I stuck it in Employer-sponsored HSA or Pretax Commuter Costs?
You're welcome!

By "tax qualified" I'm guessing you mean "as opposed to non-qualified" (see Qualified vs Non-Qualified ESPPs)?

For your contributions, row 17, ESPP/After-tax 401k, seems appropriate.  The amount you invest does not affect your AGI.

What to do with the bargain element and any short/long term capital gains depends on when you sell the shares.  To get accurate taxes, those would be added to Gross Salary/Wages and/or the appropriate capital gain item.

Or is your employer's plan something altogether different?  They do come in various shapes and sizes....

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet updates
« Reply #220 on: February 10, 2021, 10:05:46 PM »
Super duper minor comment (prob not even worth posting due to the target audience of this spreadsheet), but cell 16A `Roth 401k/403b` could have `Roth TSP` added to the description.
Thanks!  Sure could, and why not, given it's already in the traditional description a few rows above? 

It's in there now for the next version.  Also added "457" to the Roth row.  Roth 457s are rare enough that it doesn't seem worth adding a separate row for them.

YummyRaisins

  • Stubble
  • **
  • Posts: 145
Re: Case Study Spreadsheet updates
« Reply #221 on: February 11, 2021, 05:14:49 AM »
By "tax qualified" I'm guessing you mean "as opposed to non-qualified" (see Qualified vs Non-Qualified ESPPs)?

For your contributions, row 17, ESPP/After-tax 401k, seems appropriate.  The amount you invest does not affect your AGI.

What to do with the bargain element and any short/long term capital gains depends on when you sell the shares.  To get accurate taxes, those would be added to Gross Salary/Wages and/or the appropriate capital gain item.

Or is your employer's plan something altogether different?  They do come in various shapes and sizes....

I guess tax qualified was the wrong description to use. It would be more accurate to call it a pre-tax ESPP.

Private company stock is purchased with pre-tax income. The income used to purchase the shares doesn't show up in FICA wages on my W2.

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet updates
« Reply #222 on: February 11, 2021, 02:36:26 PM »
I guess tax qualified was the wrong description to use. It would be more accurate to call it a pre-tax ESPP.

Private company stock is purchased with pre-tax income. The income used to purchase the shares doesn't show up in FICA wages on my W2.
Yes, that's different - appears good for you. :)

In terms of tax calculations, including that amount in both row 3 and either row 7 or row 8 will provide a correct bottom line.

In terms of the simplified "Time to FI?" calculations, there is no mechanism in the CSS that covers an amount
- not subject to FICA or income tax now, but
- investable now and taxable when withdrawn later.

Does that help?


YummyRaisins

  • Stubble
  • **
  • Posts: 145
Re: Case Study Spreadsheet updates
« Reply #223 on: February 11, 2021, 06:30:45 PM »
Yes, that's different - appears good for you. :)

In terms of tax calculations, including that amount in both row 3 and either row 7 or row 8 will provide a correct bottom line.

In terms of the simplified "Time to FI?" calculations, there is no mechanism in the CSS that covers an amount
- not subject to FICA or income tax now, but
- investable now and taxable when withdrawn later.

Does that help?

Yes this makes sense. Thank you for the feedback.

I guess for the purpose of tax calculation I could leave it out altogether and for FI calculations I could add the account value to my taxable stocks & bonds (row 174).

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet updates
« Reply #224 on: February 11, 2021, 08:56:11 PM »
Yes, that's different - appears good for you. :)

In terms of tax calculations, including that amount in both row 3 and either row 7 or row 8 will provide a correct bottom line.

In terms of the simplified "Time to FI?" calculations, there is no mechanism in the CSS that covers an amount
- not subject to FICA or income tax now, but
- investable now and taxable when withdrawn later.

Does that help?

Yes this makes sense. Thank you for the feedback.

I guess for the purpose of tax calculation I could leave it out altogether and for FI calculations I could add the account value to my taxable stocks & bonds (row 174).
Cell B72, "Other Untaxed Income", is another possibility.  It's just there as a catch-all, and you'll have to divide by 12 because it's a "per month" cell, but it might fit your needs.

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet (CSS) updates
« Reply #225 on: February 12, 2021, 12:25:10 AM »
Version 21.03 (2021)
  • 2021 version of non-itemized charitable deduction
  • Labeling changes

2020 taxes version: 2020 Case Study Spreadsheet
2021 taxes version: 2021 Case Study Spreadsheet

The taxfoundation.org state tax update for 2021 hasn't come out yet.  It was released February 4 in 2020, but there have been a few other tax things afoot so we won't begrudge their free compilation being a little later this year.

For those of you in states like California that don't see fit to release rates and brackets until November of that tax year, I suppose you are used to guessing....

Thanks to nolesrule and SomedayStache for the suggestions listed above.

As for the more comprehensive suggestions by dandarc and fyre4ce...not yet.  ;)

dandarc: as you are already far down the S-corp road, I'm happy to let you press ahead and see if a generic and tractable implementation is feasible.

fyre4ce: The "relatively simple and easy to use" philosophy you mentioned in our PMs seems more appropriate for the CSS than trying for something "powerful" when it comes to estimating withdrawal tax rates.  Rather than using the marginal rate chart to feed a detailed calculation table, it seems better to use the existing "Estimating withdrawal tax rates" table that starts on row 198 of the 'Misc. calcs' tab to feed the marginal rate chart.  But now that I write this, perhaps moving that table to the 'Calculations' tab so people don't have to flip between tabs....  OK, that's one for the next version.

Note:

Retireatee1

  • Stubble
  • **
  • Posts: 157
  • Location: Fort Mill, SC
    • Retireator.org
Re: Case Study Spreadsheet updates
« Reply #226 on: February 13, 2021, 06:53:20 AM »
I believe there is a small error on the SocialSecurity sheet.  If you enter birth year 1/1/1955, it is using the years 2015/2017 for wage indexing and bend points.  I believe this is incorrect as a birth date of Jan 1 triggers a special case:

https://www.ssa.gov/benefits/retirement/planner/ageincrease.html

So these should be 2014/2016.

This issue is exacerbated by the Maximum-Taxable Earnings page which you probably use to calibrate your results:

https://www.ssa.gov/OACT/COLA/examplemax.html

That page specifically says "initial benefit amounts shown in the table below assume retirement in January of the stated year".  To retire in January precisely at these ages requires your birthdate to be on January 1st, otherwise February 1st is the earliest you can file.  I believe they triggered the special case inadvertently in this documentation.  That shift of the base years throws everything off.

EDIT: This page has the clearest language.  "Retirement benefits can begin the first month a person is age 62 throughout the entire month. (See RS 00201.001C.) Social Security follows English common law that finds that a person attains an age on the day before the birthday."

https://secure.ssa.gov/poms.nsf/lnx/0300615015

So using the "English common law" definition, the Jan 1 birthdate special case rolls everything back 1 year.
« Last Edit: February 13, 2021, 08:43:12 AM by Retireatee1 »

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet updates
« Reply #227 on: February 13, 2021, 10:37:33 AM »
That's correct, it Doesn't do "born on the first of the month" adjustments - including "born on the first of the year" adjustments. :)

Retireatee1

  • Stubble
  • **
  • Posts: 157
  • Location: Fort Mill, SC
    • Retireator.org
Re: Case Study Spreadsheet updates
« Reply #228 on: February 13, 2021, 11:34:59 AM »
That's correct, it Doesn't do "born on the first of the month" adjustments - including "born on the first of the year" adjustments. :)

I believe you just have to change that C102 calculation to "=YEAR(B102-1)"

mjb

  • 5 O'Clock Shadow
  • *
  • Posts: 92
Re: Case Study Spreadsheet updates
« Reply #229 on: February 13, 2021, 12:24:25 PM »
Hello MDM! Thank you, yet again, for this amazing spreadsheet. It makes tax time in our household a breeze.

One small bug (that I was just bit by): Self-employed health insurance premiums (D48) are only deductible up to the limit of:
  • Schedule C net profit (D30) minus Deductible SE tax (D46) minus Self-employed SEP, SIMPLE, etc. (D47)
(This year I had very low Schedule C income and maxed out my solo 401(k), hence my Self-employed health insurance premiums were not deductible.)

I think this updated equation for cell D48 should do the trick:

Code: [Select]
=MAX(MIN(B48*$B$164,B30*$B$163-B46*$B$164-B47*$B$164),0)+MAX(MIN(C48*$C$164,C30*$C$163-C46*$C$164-C47*$C$164),0)
« Last Edit: February 13, 2021, 12:28:18 PM by mjb »

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet updates
« Reply #230 on: February 13, 2021, 12:49:16 PM »
That's correct, it Doesn't do "born on the first of the month" adjustments - including "born on the first of the year" adjustments. :)
I believe you just have to change that C102 calculation to "=YEAR(B102-1)"
Thanks!  If that's all that is needed, it will go in the next version.

Do you have a similarly easy fix for the "first of the month" situation? :)

MDM

  • Senior Mustachian
  • ********
  • Posts: 10484
Re: Case Study Spreadsheet updates
« Reply #231 on: February 13, 2021, 01:02:26 PM »
I think this updated equation for cell D48 should do the trick:
Indeed it does - thanks!  Already changed for the next version.

mjb

  • 5 O'Clock Shadow
  • *
  • Posts: 92
Re: Case Study Spreadsheet updates
« Reply #232 on: February 13, 2021, 02:49:11 PM »
I think this updated equation for cell D48 should do the trick:
Indeed it does - thanks!  Already changed for the next version.

Awesome, thank you!

Retireatee1

  • Stubble
  • **
  • Posts: 157
  • Location: Fort Mill, SC
    • Retireator.org
Re: Case Study Spreadsheet updates
« Reply #233 on: February 14, 2021, 07:18:51 AM »
That's correct, it Doesn't do "born on the first of the month" adjustments - including "born on the first of the year" adjustments. :)
I believe you just have to change that C102 calculation to "=YEAR(B102-1)"
Thanks!  If that's all that is needed, it will go in the next version.

Do you have a similarly easy fix for the "first of the month" situation? :)

Try these:

F115: =YEAR(EDATE(DATE(YEAR($B$102)+$B115,MONTH($B$102),1),IF(DAY($B$102)=1,0,1)+ROUNDUP($C115,0)))
F116: =YEAR(EDATE(DATE(YEAR($B$102)+$B116,MONTH($B$102),1),IF(DAY($B$102)=1,0,1)+ROUNDUP($C116,0)))
F117: =YEAR(EDATE(DATE(YEAR($B$102)+$B117,MONTH($B$102),1),IF(DAY($B$102)=1,0,1)+ROUNDUP($C117,0)))

rae09

  • 5 O'Clock Shadow
  • *
  • Posts: 54
Re: Case Study Spreadsheet updates
« Reply #234 on: February 16, 2021, 08:46:57 PM »
@MDM , You're a life saver! Thank you so much for making tax planning so easy!


So what I wound up doing (this is nowhere near the level of quality MDM provides, just how I got the spreadsheet to help me say "yes, this is going to be worth the effort"):

1. Create a "S-Corp" flag in cell E3. If this is 1, then the Social Security & Medicare line items (B60 & B61) double to reflect that I'm paying for both sides.

2. Enter "Salary" + "Heath Insurance" + "HSA" in B3 - in line with guidance that all these will wind up on W-2

3. Enter "Health Insurance" in B4 - again in line with guidance that while we report health insurance premiums reimbursed by the S-Corp as Income on our W-2, it is not subject to FICA and we deduct this on the self-employed health insurance deduction line. Need to put it into this section so we get FICA to calculate right.

4. Enter "HSA" in B7 for same reasoning as for "Health Insurance"

5. Enter Employee-deferral SoloK amount in B11

6. Calculate expected 'profit' from the S-Corp as follows:
Gross - Employer half of FICA - Health Insurance - HSA - Employer SoloK - Corp Fees - Insurance

Basically gross receipts minus expenses. Gets confusing because of how all this is reported to IRS, but end of day, that's what I'm expecting to be treated as business profit.

7. Enter the amount from step 6 as an addition to cell D23 (taxable interest, non qualified dividends and so on). Really put this anywhere that is subject to income tax but not FICA.

8. To simulate QBI deduction, cell B51 for now is "= 6000 + .2 * amount from step 6". I haven't dug far enough into the details on this, but I'm expecting to now be limited by S-Corp profit instead of taxable income for QBI deduction.

So I then take cell D67 for "total taxes" and compare that to what I get for the sole proprietor version (which the spreadsheet handles well already). That gives us 'tax savings for going S-Corp', and then I can decide if that is enough to justify the the additional cost / hassle of setting this all up. Spoiler: it is. About $7500 top line tax savings which means even after LLC fees and so on, I'm expecting $6,000 + per year net benefit. I think I'll also owe unemployment tax, but that's at most about $500 / year. I'd have to be missing a whole lot for this to not be worth an hour or two per month in additional bookkeeping and tax filing. Real happy I'm in Florida - no state income tax oddities to worry about from what I've read.

A whole lot of work to get this to be robust to where it might be considered for inclusion in the case study spreadsheet - maybe I'll spend some of the 2 additional days per week off in the near future doing that.

Thank you for posting what you did. I'm on the same boat so this is very helpful for us.
Can I ask where the $6,000 on step #8 comes from?
Also, I believe on step #6, you'd need to deduct the W2 wages as well (I think you did in your calc, just forgot to note it here)?

dandarc

  • Magnum Stache
  • ******
  • Posts: 4283
  • Age: 38
Re: Case Study Spreadsheet updates
« Reply #235 on: February 17, 2021, 09:31:42 AM »
$6000 is a normal traditional IRA contribution - I'm basically semi-retiring in May if the paperwork goes through on this new gig (just got my "Articles of Incorporation are approved" email today - exciting times), so no doubt at all I'll be able to deduct that under the new lower income.

dandarc

  • Magnum Stache
  • ******
  • Posts: 4283
  • Age: 38
Re: Case Study Spreadsheet updates
« Reply #236 on: February 17, 2021, 09:41:21 AM »
You are correct about #6 - and I did do that. Just forgot to put it on the post.

rae09

  • 5 O'Clock Shadow
  • *
  • Posts: 54
Re: Case Study Spreadsheet updates
« Reply #237 on: February 17, 2021, 02:48:03 PM »
$6000 is a normal traditional IRA contribution - I'm basically semi-retiring in May if the paperwork goes through on this new gig (just got my "Articles of Incorporation are approved" email today - exciting times), so no doubt at all I'll be able to deduct that under the new lower income.

I see. So, I shouldn't enter the $6k IRA contribution in cell B50 and enter it in B51 instead?

From the standard Sch C sheet, the QBI deduction is reduced by 20% of the IRA contribution amount in cell B50, but you mentioned put $6,000 + 20% of the S-corp distribution amount in B51. Does it mean the QBI is taking the whole $6k deduction instead of 20% of it?
« Last Edit: February 17, 2021, 02:58:15 PM by rae09 »

dandarc

  • Magnum Stache
  • ******
  • Posts: 4283
  • Age: 38
Re: Case Study Spreadsheet updates
« Reply #238 on: February 17, 2021, 03:08:13 PM »
Bah - I see it is cell B50 too. Screwed that line up coming and going in the post.

I also did this specifically for my situation - I'm expecting QBI to be the limiting factor vs. taxable income. In the past for me it has been the other way around. You are right that if 'taxable income' is your limiting factor, then a traditional IRA deduction would reduce your QBI deduction.

dandarc

  • Magnum Stache
  • ******
  • Posts: 4283
  • Age: 38
Re: Case Study Spreadsheet updates
« Reply #239 on: February 17, 2021, 03:30:57 PM »
I see another issue - QBI deduction does not reduce AGI, but the way I put it in that IRA line item, it does. AGI has downstream impacts, not the least of which is ACA premium tax credit. Note on that - if stimulus passes the way it was written, then for 2021 and 2022, PTC goes up a little bit and (this next part is absolutely huge for me) the cliff where you go from a $4,000 PTC to $0 with one additional dollar of AGI at the 400% of FPL level goes away.

Fixed that by just making a cell to manually enter S-Corp profit and modifying the existing QBI cell to take 20% of that for now.

Like I said - I was just trying to figure out if S-Corp hassle was worth it.

rae09

  • 5 O'Clock Shadow
  • *
  • Posts: 54
Re: Case Study Spreadsheet updates
« Reply #240 on: February 18, 2021, 01:36:32 PM »
I see another issue - QBI deduction does not reduce AGI, but the way I put it in that IRA line item, it does. AGI has downstream impacts, not the least of which is ACA premium tax credit. Note on that - if stimulus passes the way it was written, then for 2021 and 2022, PTC goes up a little bit and (this next part is absolutely huge for me) the cliff where you go from a $4,000 PTC to $0 with one additional dollar of AGI at the 400% of FPL level goes away.

Fixed that by just making a cell to manually enter S-Corp profit and modifying the existing QBI cell to take 20% of that for now.

Like I said - I was just trying to figure out if S-Corp hassle was worth it.

Yup, I'm also on the same boat, hence thought we can team up and cover what the other misses.

I played around some more with the S-corp version and here are the changes I made:

1. Move the S-corp distribution from cell D23 to D40, then change the formula in Y137 to =MAX(0,D40-D46-D47-D48) to update the QBI calculation.

2. Enter the S-corp gross income in E1 and have the formula in D40 deduct all the deductions on your step 6 from the number in E1. This way, I can play around with different income scenarios easily.

3. Put t-IRA as normally in B50 & C50 so AGI is reduced by any t-IRA contribution.


Using a hypothetical scenario for DINK, MFJ with $100k S-corp income, $60k salary, max 401(k) contribution ($19.5k deferral + $15k co matching), $7,100 HSA, $12k tIRA, I'm getting a total tax due of $9,693 in D67, while using Sole Proprietorship, the tax balance becomes $14,130. So, a saving of almost $4,500 but we'll have to do the 1120S, issue K1, and run payrolls.
 
This scenario is disregarding the health insurance reimbursement to the 2% shareholder that needs to be added to the W2.