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

RWTL

  • Pencil Stache
  • ****
  • Posts: 682
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

  • Walrus Stache
  • *******
  • Posts: 5454
  • Age: 41
  • Pronouns: he/him/his
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: 2
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: 11477
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: 11477
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: 1726
  • Age: 42
  • Location: PNW
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: 11477
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: 11477
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: 11
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: 12
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: 11477
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: 11477
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: 12
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: 11477
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

  • Walrus Stache
  • *******
  • Posts: 5454
  • Age: 41
  • Pronouns: he/him/his
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: 11477
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

  • Walrus Stache
  • *******
  • Posts: 5454
  • Age: 41
  • Pronouns: he/him/his
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: 924
  • 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: 153
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: 11477
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: 11477
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: 153
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: 11477
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: 153
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: 11477
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: 11477
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: 209
  • 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: 11477
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: 209
  • 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

  • Stubble
  • **
  • Posts: 100
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: 11477
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: 11477
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

  • Stubble
  • **
  • Posts: 100
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: 209
  • 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: 68
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

  • Walrus Stache
  • *******
  • Posts: 5454
  • Age: 41
  • Pronouns: he/him/his
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

  • Walrus Stache
  • *******
  • Posts: 5454
  • Age: 41
  • Pronouns: he/him/his
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: 68
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

  • Walrus Stache
  • *******
  • Posts: 5454
  • Age: 41
  • Pronouns: he/him/his
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

  • Walrus Stache
  • *******
  • Posts: 5454
  • Age: 41
  • Pronouns: he/him/his
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: 68
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.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #241 on: March 03, 2021, 03:02:25 PM »
Version 20.13 (2020)
Version 21.04 (2021)

2020
  • A couple of Soc. Sec. tweaks: include the above the line charitable deduction in SS taxability calculation, and the 1-Jan b'day for benefit calculation.
2021
  • Updated state brackets, etc., for 2021.
  • Fixed SEHI deduction to consider SEP, SIMPLE, solo 401k, etc. contributions.
  • Included 1-Jan b'day fix for SS benefit calculations.
  • Added an option to use "if the law doesn't change for 2026" brackets and rates for future estimations.
  • Copied the "Estimating withdrawal tax rates" box from the 'Misc. calcs' tab to the 'Calculations' tab.
2020 taxes version: 2020 Case Study Spreadsheet
2021 taxes version: 2021 Case Study Spreadsheet

As in past years, thanks to the folks at taxfoundation.org for their State Individual Income Tax Rates and Brackets for 2021.  For those of you familiar with your own state's tax calculations, use State Income Tax calculations - Crowdsourcing request or a PM for "relatively simple" ;) suggested improvements.  Blatant numerical errors count as relatively simple.

Thanks to mjb for the SEHI fix, Retireatee1 for the 1-Jan b'day fix, and fyre4ce for ideas on future marginal rate estimation.

See posts from a few weeks ago for details on the SEHI and 1-Jan changes.

The section on estimating future marginal tax rates (a few calculations and instructions for which cells to place results) remains simple, as suggested in Estimating withdrawal tax rates.

Allowing folks to use "2026" brackets and rates has two purposes:
  • Caters to those who assume "no change to the law" (meaning a reversion to 2017 regulations) is more likely than "no change to the current rates" (meaning the law will change to keep the rates the same).
  • Might be the first step toward allowing use of the same spreadsheet version to calculate federal taxes for different years.
The current implementation affects only ordinary income brackets and rates.  True fidelity to 2017 regulations would require changes to exemptions, deductions, alternative minimum tax, etc.

My personal opinion is that, come 2026, neither assumption will have been 100% correct, but I have no better idea of what reality will be.

As always, large changes such as these make fat-fingered and other errors more likely.  Corrections/suggestions welcome.

Note:
« Last Edit: August 03, 2021, 03:18:41 PM by MDM »

kimura

  • 5 O'Clock Shadow
  • *
  • Posts: 50
  • Location: PNW
Re: Case Study Spreadsheet updates
« Reply #242 on: March 08, 2021, 07:09:48 PM »
I love this spread sheet. I live in WA and there is no state tax. Can I delete the "State Tax' and "State Brackets" without screwing up the entire spreadsheet? Just wanted to to delete those since I dont need them.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #243 on: March 08, 2021, 08:05:55 PM »
I love this spread sheet. I live in WA and there is no state tax. Can I delete the "State Tax' and "State Brackets" without screwing up the entire spreadsheet? Just wanted to to delete those since I dont need them.
Yes, I just tried and it appears you can do that.  Deleting the formula in cell G35 (leave it blank or enter zero) will prevent a bunch of #N/As should you enter WA in cell H35 to do ACA premium tax credit calculations.

For that matter, if all you want are the tax calculations you could delete all the other sheets except the 'Form 6251' and 'Tax Code' sheets. :)

kimura

  • 5 O'Clock Shadow
  • *
  • Posts: 50
  • Location: PNW
Re: Case Study Spreadsheet updates
« Reply #244 on: March 08, 2021, 08:24:16 PM »
Thanks for the help!

Retireatee1

  • Stubble
  • **
  • Posts: 209
  • Location: Fort Mill, SC
    • Retireator.org
Re: Case Study Spreadsheet updates
« Reply #245 on: March 15, 2021, 12:38:00 PM »
OK I went back and revisited the Social Security date arithmetic again.

I see the C102 fix to subtract 1 for English common law is in.  That is good.

Really now you just need to revise these three cells slightly with the same fix (disregard my earlier post):

F115: =YEAR(EDATE($B$102-1,B115*12+C115))
F116: =YEAR(EDATE($B$102-1,B116*12+C116))
F117: =YEAR(EDATE($B$102-1,B117*12+C117))

I'd add some rounding to the month count as this should never be a fraction:

C115: =ROUND((B119-B115)*12,0)

You really don't need to worry about the day you were born as all you care about are years, unless it is Jan 1 which is handled.  For most people, they cannot retire at age 62.  62 plus 1 month is the earliest and 62 is an invalid entry (I wouldn't try to "fix" it).  As you are not performing data validation, you get a slightly inaccurate result if you enter that combination.  This is why the government examples all start at 62 + 1/12.

So that should do it.  Feel free to compare any results with my calculator:

https://www.retireator.org/

HudsonMK

  • 5 O'Clock Shadow
  • *
  • Posts: 3
Re: Case Study Spreadsheet updates
« Reply #246 on: March 15, 2021, 07:43:13 PM »
Is there anywhere in the spreadsheet that accounts for current cash savings in a bank account? I looked a few times, but apologies if this has already been answered. Thank-you!

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #247 on: March 15, 2021, 08:17:50 PM »
Is there anywhere in the spreadsheet that accounts for current cash savings in a bank account? I looked a few times, but apologies if this has already been answered. Thank-you!
Great question!  Cash savings in a bank account are assumed to be the "emergency fund" and not part of "invested assets".

The comment for cell A174 is "Ignore cash.  Calculations assume cash sits in an emergency fund, or otherwise serves as a buffer to dampen market fluctuations, so it can and should be ignored for withdrawal rate purposes."  Not front and center, but it's in there.... ;)

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #248 on: March 15, 2021, 08:59:31 PM »
OK I went back and revisited the Social Security date arithmetic again.

I see the C102 fix to subtract 1 for English common law is in.  That is good.

Really now you just need to revise these three cells slightly with the same fix (disregard my earlier post):

F115: =YEAR(EDATE($B$102-1,B115*12+C115))
F116: =YEAR(EDATE($B$102-1,B116*12+C116))
F117: =YEAR(EDATE($B$102-1,B117*12+C117))
Thanks, looks good and those are in the version I'm about to post.  That version is primarily for the recent law changes, so I'll discuss the SS ones here.

Quote
I'd add some rounding to the month count as this should never be a fraction:

C115: =ROUND((B119-B115)*12,0)
True.  The main use of the continuous nature of this cell was to generate a smoothed quadratic fit (see cells G119:I119 and the graph over cell AD107) for SS benefits vs. start date, with the idea of using that as part of some overall retirement planning optimizer.  That idea bore some fruit, but nothing suitable for general use.  And in the table generation for the curve fit the points are spaced "even twelfths" of a year apart so those are "integer months".  I'll think about this one.  One can specify year and month directly in row 117.

Quote
You really don't need to worry about the day you were born as all you care about are years, unless it is Jan 1 which is handled.  For most people, they cannot retire at age 62.  62 plus 1 month is the earliest and 62 is an invalid entry (I wouldn't try to "fix" it).  As you are not performing data validation, you get a slightly inaccurate result if you enter that combination.  This is why the government examples all start at 62 + 1/12.
Most, but not all.  E.g., footnote "a" on Maximum-taxable benefit examples does have "Retirement at age 62 is assumed here to be at exact age 62 and 1 month,"  but Social Security Retirement Benefit Calculation has "We assume the worker in case A begins receiving benefits at the earliest possible age, which is age 62."

Again, thanks and good to know that two different tools get the same answer!

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #249 on: March 15, 2021, 09:45:32 PM »
Version 21.05 (2021)

2020
  • Just a note that the recent retroactive law changes are not, and likely will not be (anytime soon, at least), included in the 2020 version.  At this point people should be using real tax software (not that all those commercial programs have themselves been updated, but that's another issue).
2021
Various changes for 2021 included in the American Rescue Plan Act:
  • Child Tax Credit
  • Third stimulus / 2021 Recovery Rebate Credit
  • Unemployment benefits
  • Child and Dependent Care Credit
  • Earned Income Credit
  • Affordable Care Act Premium Tax Credit

Child Tax Credit
- Include age 17 child for Child Tax Credit
- Include extra $1000 CTC for lower incomes (and $1600 for children under 6)
- Remove $1400 limit on refundable CTC

Third stimulus / 2021 Recovery Rebate Credit
- Provide a place to enter how much EIP#3 was received (cell Calculations!I31), as input to the 2021 Recovery Rebate Credit (RRC)
- Add phase-out incomes for RRC calculation, in case the maximum EIP#3 had not already been received

Unemployment benefits
- Add an input row (Calculations row 37) for unemployment benefits
- Limit household income to 133% of the federal poverty line for ACA purposes if unemployment income > 0

Child and dependent care credit
- Change this credit from non-refundable to refundable
- Increase the maximum credit amount
- Implement a new "fraction of expenses vs. income" table
- Implement a phase-out above $400K income

Earned Income Credit
- Change investment income cliff from $3650 to $10000
- Change minimum eligibility age to 19 if not a student, and 24 if a student
- Remove maximum age
- Change income break points and maximum credit for "no children" filers

Affordable Care Act Premium Tax Credit
- Extend eligibility for this credit to income above 400% of the federal poverty level
- Change the "fraction of income allowed for insurance premiums" table

In addition to the usual susceptibility of new releases to error, interpreting the language of the law itself (Text - H.R.1319 - 117th Congress (2021-2022): American Rescue Plan Act of 2021 | Congress.gov | Library of Congress) instead of relying on the IRS interpretation in forms and instructions adds another possible source of error.

In other words, if those of you with particular interest/expertise in specific parts of the new law cast a critical eye over this implementation and find deficiencies, that wouldn't be a total surprise. :)  Reports of those, either here or via PM, will be appreciated (and useful to others when fixed).


2021 taxes version: 2021 Case Study Spreadsheet


Note: