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

doneby35

  • Stubble
  • **
  • Posts: 209
Re: Case Study Spreadsheet updates
« Reply #50 on: December 28, 2017, 09:30:37 AM »
This is one of the best spreadsheets I've seen!
Does the HSA employer match go in B19 "employer match" along with the 401k employer match, or do you just put the total (contributions + employer match) in "employer-sponsored HSA"?

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #51 on: December 28, 2017, 10:12:52 AM »
This is one of the best spreadsheets I've seen!
Does the HSA employer match go in B19 "employer match" along with the 401k employer match, or do you just put the total (contributions + employer match) in "employer-sponsored HSA"?
Either can work.  I think "employer match" is more straightforward, as one would have to add the HSA match to "gross salary/wages" if that amount goes into "employer-sponsored HSA".

Doing it the latter way might be slightly more accurate in terms to "time to FI" calculations, but the uncertainty in those is so large anyway that I'd stick with the simpler approach.

nickybecky1

  • Stubble
  • **
  • Posts: 204
  • Location: PNW
Re: Case Study Spreadsheet updates
« Reply #52 on: December 28, 2017, 11:08:40 AM »
Is there a place that makes sense to input an estimated annual bonus, or is it best to just amortize that into gross wages if you want to include it in calculations?

We're not counting on it for making our budget work, but would like to put an estimate (of a minimum amount typically expected) in since it's a large part of one of our compensations.

Thanks for such a great spreadsheet - I hadn't updated in awhile since a lot of the changes were small enough it wasn't worth re-entering everything for us, but I'm now loving the separated earners and ability to do different paycheck frequencies.

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #53 on: December 28, 2017, 11:23:43 AM »
Is there a place that makes sense to input an estimated annual bonus, or is it best to just amortize that into gross wages if you want to include it in calculations?

We're not counting on it for making our budget work, but would like to put an estimate (of a minimum amount typically expected) in since it's a large part of one of our compensations.

Thanks for such a great spreadsheet - I hadn't updated in awhile since a lot of the changes were small enough it wasn't worth re-entering everything for us, but I'm now loving the separated earners and ability to do different paycheck frequencies.
Assuming the bonus is subject to FICA, adding it into gross wages is best. 

Yes, allowing different income frequencies is one of those "why didn't I do this a long time ago?" things.  Haven't decided whether to do the same for expenses....

nickybecky1

  • Stubble
  • **
  • Posts: 204
  • Location: PNW
Re: Case Study Spreadsheet updates
« Reply #54 on: December 28, 2017, 02:16:21 PM »
In the summary section on the calculations sheet, it looks like cells B150-152 are leaving out the 2nd earner's information. Am I reading that correctly or am I misunderstanding how the sheet should be working?

ETA: Of course - they're right in the next column - exactly where I'd look for them if I weren't an idiot.
« Last Edit: December 28, 2017, 02:27:20 PM by nickybecky1 »

jsa307

  • 5 O'Clock Shadow
  • *
  • Posts: 5
Re: Case Study Spreadsheet updates
« Reply #55 on: December 29, 2017, 11:24:25 AM »
MDM, thanks for all your work on this great spreadsheet!

I have an S-corp and I enter the w-2 information into the appropriate boxes. For the the "distribution" income, would "schedule c net profit" be the best place to input that? And for my pre-tax business deductions, would "rental real expenses" make sense as a place to input?

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #56 on: December 29, 2017, 06:34:04 PM »
I have an S-corp and I enter the w-2 information into the appropriate boxes. For the the "distribution" income, would "schedule c net profit" be the best place to input that? And for my pre-tax business deductions, would "rental real expenses" make sense as a place to input?
I'm not familiar with S-corp taxes.

If someone is, and can provide either (or both) of
- a succint description of how to use the existing format, and/or
- a few minor tweaks to the existing format in order to accommodate S-corp filing,
there is a reasonable chance those will be included.

nolesrule

  • 5 O'Clock Shadow
  • *
  • Posts: 7
Re: Case Study Spreadsheet updates
« Reply #57 on: December 29, 2017, 08:45:42 PM »
S-corp income is just taxable ordinary income. However it's not clear what calculations may change things with the pass-through adjustments in the new tax law. For now either include it in D40 or add another line for it.

jsa307

  • 5 O'Clock Shadow
  • *
  • Posts: 5
Re: Case Study Spreadsheet updates
« Reply #58 on: December 30, 2017, 07:46:03 AM »
thanks guys. the only relevant distinctions for s-corps and the spreadsheet are
1) income gets split between w2 salary and distributions. you pay both sides (employer/employee) of FICA tax on the w2 part (except for the .9% surtax on the employer part), and no FICA taxes on the distributions. I was handling this by entering the w2 info into the appropriate boxes, the distributions into "sched c net profit", and then calculating my employer share of FICA on the w2 and overriding that number into the self employment tax box. i think that works?

2) business deductions are also taken out before any tax implications are considered -- it seemed like entering those into "rental real expenses" would do the trick?

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #59 on: December 30, 2017, 08:06:33 AM »
thanks guys. the only relevant distinctions for s-corps and the spreadsheet are
1) income gets split between w2 salary and distributions. you pay both sides (employer/employee) of FICA tax on the w2 part (except for the .9% surtax on the employer part)
Seems putting this into the "sched c net profit" would work.

Quote
and no FICA taxes on the distributions.
As nolesrule noted, D40 should work for this.

Quote
I was handling this by entering the w2 info into the appropriate boxes, the distributions into "sched c net profit", and then calculating my employer share of FICA on the w2 and overriding that number into the self employment tax box. i think that works?
Probably will.  Best way is in the eye of the beholder.

Quote
2) business deductions are also taken out before any tax implications are considered -- it seemed like entering those into "rental real expenses" would do the trick?
Not sure what "taken out" means here.  If the W-2 salary and distributions come after business deductions are subtracted, then entering them isn't needed at all, etc.

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #60 on: December 30, 2017, 05:16:55 PM »
Version 10.02
  • Removed Pease limitation on itemized deductions
  • Modified Child Tax Credit calculations.  Still not 100% sure how the IRS will implement the new law to cover all situations.
  • Edited Instructions to reflect new tax law
  • Calculate number of exemptions for state tax purposes


Case Study Spreadsheet

Teachstache

  • Stubble
  • **
  • Posts: 107
Re: Case Study Spreadsheet updates
« Reply #61 on: January 01, 2018, 07:58:47 AM »
I am trying my 5 digit zip code in H32 and it says N/A. Do I need my 9 digit zip code instead? I can't get my state income tax calculated without this part.

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #62 on: January 01, 2018, 08:08:10 AM »
I am trying my 5 digit zip code in H32 and it says N/A. Do I need my 9 digit zip code instead? I can't get my state income tax calculated without this part.
Try the two character state postal abbreviation.

Teachstache

  • Stubble
  • **
  • Posts: 107
Re: Case Study Spreadsheet updates
« Reply #63 on: January 01, 2018, 09:00:08 AM »
I am trying my 5 digit zip code in H32 and it says N/A. Do I need my 9 digit zip code instead? I can't get my state income tax calculated without this part.
Try the two character state postal abbreviation.

It worked, thanks!

Question about the 401k & 457 amounts: are eligible employees still able to contribute $18,500 to both a 401k and a 457? I thought so, but the spreadsheet indicates that I may be exceeding allowable maximums in both.

Thanks for the clarification.

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #64 on: January 01, 2018, 09:13:15 AM »
Question about the 401k & 457 amounts: are eligible employees still able to contribute $18,500 to both a 401k and a 457? I thought so, but the spreadsheet indicates that I may be exceeding allowable maximums in both.

leads to

in the latest version I have.

Does the same input give you different results?

Teachstache

  • Stubble
  • **
  • Posts: 107
Re: Case Study Spreadsheet updates
« Reply #65 on: January 01, 2018, 09:25:00 AM »
Ah, just checked & I am using the version just prior to the latest version. So, I'll need to use the latest version. Thanks for clarifying!

nolesrule

  • 5 O'Clock Shadow
  • *
  • Posts: 7
Re: Case Study Spreadsheet updates
« Reply #66 on: January 01, 2018, 11:29:14 AM »
thanks guys. the only relevant distinctions for s-corps and the spreadsheet are
1) income gets split between w2 salary and distributions. you pay both sides (employer/employee) of FICA tax on the w2 part (except for the .9% surtax on the employer part), and no FICA taxes on the distributions. I was handling this by entering the w2 info into the appropriate boxes, the distributions into "sched c net profit", and then calculating my employer share of FICA on the w2 and overriding that number into the self employment tax box. i think that works?

2) business deductions are also taken out before any tax implications are considered -- it seemed like entering those into "rental real expenses" would do the trick?

For an S-Corp, FICA taxes employer-side taxes are a straight-up business expense. So you should just use the Paycheck section for your W-2 wages and D40 for the net business  income from the 1120S / K-1.

Gin1984

  • Magnum Stache
  • ******
  • Posts: 4499
Re: Case Study Spreadsheet updates
« Reply #67 on: January 05, 2018, 09:17:54 PM »
Just a heads up, the 529 deduction is not coming off my state part, so you may have to do that math or override that part. 

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #68 on: January 05, 2018, 09:32:11 PM »
Just a heads up, the 529 deduction is not coming off my state part, so you may have to do that math or override that part.
Good point.  With the variety in State Tax Deductions for 529 Contributions, that probably won't be a programmed addition any time soon.  Unless someone has an Excel version...? 

See also State Income Tax calculations - Crowdsourcing request.

nolesrule

  • 5 O'Clock Shadow
  • *
  • Posts: 7
Re: Case Study Spreadsheet updates
« Reply #69 on: January 06, 2018, 08:59:39 AM »
We need to work on the Medicare calculations. Your calculations only work correctly for a single earner with a single W-2 income source. MFJ calculation is incorrect, and I don't see any accounting for SE income and the Additional Medicare Tax

It would be simplest to assume only one W-2 in the case of an earner over $200k, but that may not always be true, and I'm not sure how to handle that scenario in the spreadsheet.

Medicare taxes withheld  per employer W-2 are calculated at 1.45% of FICA wages and an additional 0.9%on FICA wages above $200k.

The Additional Medicare Tax itself is on total FICA Wages + Self-employment Taxable Income^ over $200k for Single or $250k MFJ.

The Additional Medicare Tax form is designed to calculate the correct Additional Medicare Tax liability (since it can differ from what is withheld) and reconcile with medicare taxes withheld, properly adjusting the tax liability up or down.

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #70 on: January 06, 2018, 09:29:01 AM »
The Additional Medicare Tax form is designed to calculate the correct Additional Medicare Tax liability....
Excellent point!  And it happens to coincide with the version under development - see the attachment to https://forum.mrmoneymustache.com/taxes/child-care-credit-vs-dependent-care-fsa/msg1842186/#msg1842186, which (I think) addresses both this issue and those raised by FIRE_Wannabe and Gin1984.

As with "normal" tax calculations, this spreadsheet ignores withholding.  Or, perhaps better stated, assumes withholding is correct.  Thus it doesn't attempt to cover penalties for under-withholding, nor attempt to reconcile SS and Medicare withholding.  It's more a cash flow planner that happens to be (with crowd-sourced help from all noted here and others!) a pretty good tax calculator.

If no errors in the medicare and child care calculations are noted, after a while (days? hours?) I'll take the development copy and upgrade that to a released version.

nolesrule

  • 5 O'Clock Shadow
  • *
  • Posts: 7
Re: Case Study Spreadsheet updates
« Reply #71 on: January 06, 2018, 10:27:14 AM »
The Additional Medicare Tax form is designed to calculate the correct Additional Medicare Tax liability....
Excellent point!  And it happens to coincide with the version under development - see the attachment to https://forum.mrmoneymustache.com/taxes/child-care-credit-vs-dependent-care-fsa/msg1842186/#msg1842186, which (I think) addresses both this issue and those raised by FIRE_Wannabe and Gin1984.

As with "normal" tax calculations, this spreadsheet ignores withholding.  Or, perhaps better stated, assumes withholding is correct.  Thus it doesn't attempt to cover penalties for under-withholding, nor attempt to reconcile SS and Medicare withholding.  It's more a cash flow planner that happens to be (with crowd-sourced help from all noted here and others!) a pretty good tax calculator.

If no errors in the medicare and child care calculations are noted, after a while (days? hours?) I'll take the development copy and upgrade that to a released version.


That sheet looks ok to me. I think if you sum up the Medicare plus the calculation you have for Additional Medicare Tax, you do get the correct Medicare tax total in aggregate. So for yearly cash flow purposes in aggregate, it's probably accurate.

I've thought about helping out with your crowdsourcing for NJ taxes, but it treats so many different items differently from the way Federal and/orother states handle them that I'm not sure you really want to tackle it.

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #72 on: January 06, 2018, 10:39:44 AM »
That sheet looks ok to me. I think if you sum up the Medicare plus the calculation you have for Additional Medicare Tax, you do get the correct Medicare tax total in aggregate. So for yearly cash flow purposes in aggregate, it's probably accurate.
Great - thanks!

Quote
I've thought about helping out with your crowdsourcing for NJ taxes, but it treats so many different items differently from the way Federal and/orother states handle them that I'm not sure you really want to tackle it.
That is probably correct.  For similar reasons, I avoided any state tax calculations (beyond a flat percentage estimate) until finding https://taxfoundation.org/state-individual-income-tax-rates-brackets-2017/ with it's state-by-state brackets.  Likelihood of implementation for any specific issue is proportional to how easy the calculation, how likely it will be a common situation, and inversely proportional to how nice the weather is outside.

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #73 on: January 06, 2018, 12:35:20 PM »
Version 10.03
  • Modified Child and Dependent Care Tax Credit calculations.
  • Added Form 8959, Additional Medicare Tax.  The result appears in cell G25.  The standard 1.45% tax continues to appear in the "payroll tax" section.
  • Minor formatting changes.

Case Study Spreadsheet

2017 version is attached to post https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1825323/#msg1825323.

nolesrule

  • 5 O'Clock Shadow
  • *
  • Posts: 7
Re: Case Study Spreadsheet updates
« Reply #74 on: January 06, 2018, 02:01:42 PM »
The SS Wage Base for 2018 was revised at the end of November. The correct cap number is $128,400. G53 needs to be updated.

See https://www.ssa.gov/news/press/releases/2017/#11-2017-1

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #75 on: January 06, 2018, 02:19:07 PM »
The SS Wage Base for 2018 was revised at the end of November. The correct cap number is $128,400. G53 needs to be updated.

See https://www.ssa.gov/news/press/releases/2017/#11-2017-1
Thanks!  Sneaky of them to change what they announced in October and the IRS codified earlier in November. ;)

Also affects SocialSecurity!C74.

Small enough effect on either current year SS withholding or future SS benefit projections that I'll wait and include these in the next official release.  One can always make the changes in a downloaded copy.

stormbard

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: Case Study Spreadsheet updates
« Reply #76 on: January 08, 2018, 03:50:16 PM »
Why are the fields for Roth401k/403b, ESPP/After-tax 401k, and After-tax pension contributions subtracted from the value for W-2 Box 1 to get Net paycheck before tax? Aren't these values all taken from your paycheck after all taxes have been calculated?



Is there a way to estimate what my paycheck will be each pay period? I'm not seeing it but then I could just be missing something.

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #77 on: January 08, 2018, 04:27:58 PM »
Why are the fields for Roth401k/403b, ESPP/After-tax 401k, and After-tax pension contributions subtracted from the value for W-2 Box 1 to get Net paycheck before tax? Aren't these values all taken from your paycheck after all taxes have been calculated?
Good question!

That may vary from employer to employer.  Megacorp would list a separate line item with the amount shown in the spreadsheet, and this is just a holdover from that.  Tax withholding would still be based on the "FICA base" for SS and medicare, and the "W-2 Box 1" amount for federal and state taxes (this may differ for those in states that do not follow federal guidelines for HSA, etc., deductions).

Quote
Is there a way to estimate what my paycheck will be each pay period? I'm not seeing it but then I could just be missing something.
The spreadsheet calculates the tax amount per paycheck that should be withheld, assuming one wants neither to pay nor to owe when filing taxes.  How one chooses to arrange actual withholding amounts is an individual decision.

One could look at the "Net paycheck before tax" amount, then subtract Social Security, Medicare, Federal tax, and State+local tax to approximate what the "net paycheck amount deposited to checking" might be.  Does that come close in your situation?



stormbard

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: Case Study Spreadsheet updates
« Reply #78 on: January 08, 2018, 06:35:21 PM »
MDM, thank you for all your work on this and for answering my questions. I think the answers fit my situation, at least it fits my current understanding of the situation and gives me enough to continue playing with the numbers and see how things play out.

One other thing I'm not seeing is a field for employer contributions to an HSA. My understanding is that their contribution does not affect my taxes now or in the future, but it should affect total net worth and time to FI.

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #79 on: January 08, 2018, 07:17:49 PM »
MDM, thank you for all your work on this and for answering my questions. I think the answers fit my situation, at least it fits my current understanding of the situation and gives me enough to continue playing with the numbers and see how things play out.
Great!  See also some of the discussion with nolesrule a couple of days ago regarding withholding.

Might not be too complicated to include a withholding estimator, depending on how the IRS reconfigures the W-4.  Let's see how that looks when they release it.

Quote
One other thing I'm not seeing is a field for employer contributions to an HSA. My understanding is that their contribution does not affect my taxes now or in the future, but it should affect total net worth and time to FI.
Adding it to row 19, "Employer Match", may be the easiest and likely accurate enough way.  One could also add the amount both to Gross Salary and Employer-sponsored HSA.
« Last Edit: January 10, 2018, 12:31:36 PM by MDM »

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #80 on: January 10, 2018, 12:49:02 PM »
Version 10.04
  • Modified Child Tax Credit calculations.  I think this now covers all possibilities for 2018, but will review the IRS instructions when they appear.
  • Made the $128,400 change to the SS wage index discussed previously.
  • Minor formatting and documentation changes.

Case Study Spreadsheet

2017 version is attached to post https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1825323/#msg1825323.

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #81 on: January 11, 2018, 04:24:30 PM »
Version 10.05
  • Added a W-4 allowance estimator.
From the Instructions tab:
W-4 allowances, based on one's overall tax situation, may be tested in cells Calculations!F56:I64.                              
   Enter "S" or "M" (for Single or Married) in row 59.                           
   Enter the number of allowances in row 60.                           
   See the expected refund due at filing in cell I64.  An amount due at filing will show as a negative number.                           
   Adjust the entries in cells Calculations!G59:H60 to achieve your desired result.                           
   Note that there are various "safe harbors" to avoid penalties due to under-withholding.  Caveat filer.                           
   Always check the results of this or any other W-4 estimator against the actual withholding and expected tax due.                           

It shows (other than at very low incomes when the Earned Income Credit is active) that for filers with no dependents, only one wage income, standard deduction, and no other income, credits, etc., filing "Single with 2 Allowances" or "Married with 3 Allowances" (as applicable) will withhold exactly the amount of federal tax due.  For other situations, see the short set of instructions above.

Case Study Spreadsheet

The last 2017 version is attached to post https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1825323/#msg1825323.

nolesrule

  • 5 O'Clock Shadow
  • *
  • Posts: 7
Re: Case Study Spreadsheet updates
« Reply #82 on: January 13, 2018, 08:43:04 AM »
The Earner #2 withholding (H62) is calculating based off the filing status rather than off the M or S (H59) in the calculator.

I'm trying to roughly estimate switching between M or S on either/or/both of our W-4s and right now that's not possible.

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #83 on: January 13, 2018, 08:59:54 AM »
As a quick fix, just copy cell G62 to H62.  That's what I did at some point, but apparently did not save the file after doing so. :(

New version coming....

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #84 on: January 13, 2018, 09:28:37 AM »
Version 10.06
  • Changed standard deduction adder for age 65+.  2018 IRS numbers not yet available at Standard Deduction? | IRS, and there is some discrepancy among non-IRS sites, but 1600 for single and 1300 for MFJ seem the consensus numbers.
  • Added some ad hoc calculation cells on the 'Misc. calcs' tab.  Reduces the need to unprotect that sheet.
  • Fixed earner #2 withholding calculation.

Case Study Spreadsheet

The last 2017 version is attached to post https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1825323/#msg1825323.

simonkkkkk

  • 5 O'Clock Shadow
  • *
  • Posts: 26
Re: Case Study Spreadsheet updates
« Reply #85 on: January 14, 2018, 04:45:11 AM »
Nice :)

fiveoh

  • Bristles
  • ***
  • Posts: 367
Re: Case Study Spreadsheet updates
« Reply #86 on: January 22, 2018, 06:47:31 PM »
Awesome spreadsheet!

I'm not too tax savvy so I have a few questions.  If I'm self employed where would I put my income?  Under Schedule C net profit? 

I did it that way and it calculated a number for "self employment tax".   Does this include the medicare/ss that I will have to pay or do I need to calculate those on my own and put them in to the SS/Medicare boxes?  Thanks.

MDM

  • Walrus Stache
  • *******
  • Posts: 7410
Re: Case Study Spreadsheet updates
« Reply #87 on: Today at 04:44:14 AM »
Awesome spreadsheet!

I'm not too tax savvy so I have a few questions.  If I'm self employed where would I put my income?  Under Schedule C net profit? 

I did it that way and it calculated a number for "self employment tax".   Does this include the medicare/ss that I will have to pay or do I need to calculate those on my own and put them in to the SS/Medicare boxes?  Thanks.
Yes, Schedule C net profit.

The self employment tax is indeed the medicare/ss you pay: both the employee and employer portion.  You should not need to enter those on your own.

When you do your real 2017 return, you could compare those numbers to the final 2017 version, attached in this post.  How closely the results match might indicate how well the 2018 version will predict for you.