Case Study Spreadsheet updates

[MDM]

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 of "time to FI" calculations, but the uncertainty in those is so large anyway that I'd stick with the simpler approach.

[nickybecky1]

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]

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]

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.

[jsa307]

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]

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]

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]

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]

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.

and no FICA taxes on the distributions.

As nolesrule noted, D40 should work for this.

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.

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]

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

[Teachstache]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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!

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]

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.

[nolesrule]

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]

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]

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]

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).

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]

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]

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.

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.

[MDM]

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.

[MDM]

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.

[nolesrule]

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]

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]

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.

[simonkkkkk]

Nice :)

[fiveoh]

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]

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.

[fiveoh]

Thanks MDM!

[MDM]

Version 11.00

• Adjusted heuristics in the '401k vs Taxable' tab regarding QD/LTCG rates based on ordinary tax bracket.
• Minor clarifications in the 'Instructions' tab.
• Significant change to the marginal rate chart infrastructure.  See below.

One particularly useful feature of this spreadsheet is the ability to generate marginal tax rate charts for any income, expense, contribution, etc., line item.  The recent update allowing different frequencies (bi-weekly, 2X/month, etc.) for paychecks and other items, apparently causes problems for this chart in older Excel versions (based on conversation in Social Security “Hump” and the new tax rates??? - Bogleheads.org).  The Data>What-If Analysis>Data Table method that generates the chart also requires somewhat advanced Excel knowledge.

Based on the above, a button has been added that one may click to update the chart.  Rather than going through Data>What-If Analysis>Data Table, one types the desired X-axis and Y-axis cells in the respective green boxes, then clicks the "Update chart" button.  The normal Excel chart tool will rescale the Y-axis if desired.  Cells P82 and P83 are used to adjust the X-axis.


One issue is that this approach introduces a macro (the thing that makes the button click work).  Consequently, Excel's security setting may create a warning.  I hope this will not dissuade anyone from using the tool - it really is just an automated way to update the What-If Data Table - but one needs to decide that for oneself.  Other fine spreadsheets (e.g., Retiree Portfolio Model - Bogleheads.org) are chock full of buttons and macros for just this purpose: speed and ease of use.

As always, suggestions for fixes/improvements/etc. are welcome.  It's a small beta test group here, so crowd-sourcing is good.

[hamiltop]

Found an issue with the self employment tax.
The original

Code: [Select]

=IF(B30*B162*0.9235<400,0,MIN(B30*B162*0.9235,MAX(0,$G$53-B9))*0.124+MAX(0,B30*B162*0.9235)*0.029)/B161The fix

Code: [Select]

=IF(B30*B162*0.9235<400,0,MIN(B30*B162*0.9235,MAX(0,$G$53-B9*B161))*0.124+MAX(0,B30*B162*0.9235)*0.029)/B161

B9 was not being adjusted for pay periods, which means it was not capping my self employment tax if I met my SS max on my W-2.

[MDM]

Version 11.01

• Fixed SE tax for earner #1 and #2 as noted by hamiltop - thanks!

As always, suggestions for fixes/improvements/etc. are welcome.  It's a small beta test group here, so crowd-sourcing is good.

[nolesrule]

I noticed in the most recent versions of the spreadsheet that the Additional Medicare tax isn't being added in to the total tax, so it's not actually included anywhere in the tax summary box since it was removed from the Medicare tax calculation.

G30 should be changed from:

Code: [Select]

=G24+G26-G27-G28-G29 to

Code: [Select]

=G24+G25+G26-G27-G28-G29

[MDM]

Version 11.02

• Changed default y-axis to "total tax" for the marginal rate chart
• Changed Child Tax Credit calculation to use a more favorable interpretation of the new law.  Still not sure about this....
• Added some calculations for high marginal rates related to SS benefits
• Fixed inclusion of add'l medicare tax as noted by nolesrules - thanks!

As always, suggestions for fixes/improvements/etc. are welcome.  It's a small beta test group here, so crowd-sourcing is good.

[MDM]

Version 11.03

• Assume age is between 25 and 50 if not entered
• Adjusted some tax items (HSA, EITC, AMT) due to IRB 2018-10 (Rev. March 5, 2018)

As always, suggestions for fixes/improvements/etc. are welcome.  It's a small beta test group here, so crowd-sourcing is good.

[Gin1984]

Version 11.02

• Changed default y-axis to "total tax" for the marginal rate chart
• Changed Child Tax Credit calculation to use a more favorable interpretation of the new law.  Still not sure about this....
• Added some calculations for high marginal rates related to SS benefits
• Fixed inclusion of add'l medicare tax as noted by nolesrules - thanks!

As always, suggestions for fixes/improvements/etc. are welcome.  It's a small beta test group here, so crowd-sourcing is good.

Most recent version: Case Study Spreadsheet

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

What is the "more favorable interpretation of the new law"?

[MDM]

What is the "more favorable interpretation of the new law"?

In short, the question is does the tax consume the refundable part first or the non-refundable part?

The example scenario is "...one has two children and $3000 in tax before the child tax credit. The CTC of $4000, with $2800 refundable, would reduce the tax to zero, with $1000 left over. How much would the additional tax credit be, assuming the ($3K + 15%) amount is greater than $5K? Would it be $0 because $3000>$2800 or would it be $1000 because $1000<$2800?"

See that Bogleheads thread for more details.

I had originally coded things along the lines of the "pessimistic" view in that thread.  Reading a few other places (don't remember exactly where) led to the speculation that the law either is, or would be interpreted as, more favorable to the taxpayer/voter.  But I've yet to see a definitive ruling, so if anyone knows of one...?

[MDM]

Version 11.04

• Fixed max tIRA deduction amount when in the MAGI phaseout zone.
• Updated state taxes based on https://taxfoundation.org/state-individual-income-tax-rates-brackets-2018/

Note that many state tax laws are still in flux due to the federal tax law changes to standard deduction and personal exemption amounts.  Anyone interested in more accurate state calculations (either now or as state legislatures do their thing) is invited to post in State Income Tax calculations - Crowdsourcing request.

Most recent version: Case Study Spreadsheet

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

[MDM]

Version 11.05

• Added an option to take the "optimistic" or "pessimistic" view of the Child Tax Credit implementation.
• Modified the IRA withdrawal taxation for a couple of states.
• Added a sheet for Form 8606, an important component of a Backdoor Roth IRA.

The Form 8606 (and Publication 590-B worksheet 1-1) calculations have been tested on a few situations, including the examples in 2017 Publication 590-B.  But the beta test group is small, so any testing by those with known correct Forms 8606 is welcome.

Most recent version: Case Study Spreadsheet

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

[MDM]

Version 11.06

• Mostly word crafting various documentation, particularly on the Instructions tab.
• Added an option for part-year withholding calculations.

Most recent version: Case Study Spreadsheet

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

[MDM]

Version 11.07

• Added a tab that evaluates a non-deductible IRA vs. taxable (and, while it's at it, Roth and deductible)
  
• Tweaks to the Form 8606 tab

Most recent version: Case Study Spreadsheet

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