The Money Mustache Community
General Discussion => Welcome and General Discussion => Topic started by: Outside the Box on December 19, 2017, 02:16:05 PM
-
I created a Google Sheet (https://docs.google.com/spreadsheets/d/1hksM45ooawa_LN5fNeeUEk6BCxbfCEdmaVUYAfMDrQU/edit?usp=sharing) that lets you play with how the taxation plan affects you for tax year 2018.
I could have done it a lot easier with javascript macros, but I did it all with cell formulas so that everything could be easily traced back and understood by anyone. Unfortunately, it leads to some complicated cell formulas.
Make a copy (File->Make a Copy) and then edit your "local" copy in your Google Sheets. Update cells B4 to B24 with your info.
Please let me know if you find any errors.
Again, here it is. (https://docs.google.com/spreadsheets/d/1hksM45ooawa_LN5fNeeUEk6BCxbfCEdmaVUYAfMDrQU/edit?usp=sharing)
Edit: A probably more comprehensive version is now available here: https://sites.google.com/site/excel1040/home/2018-tax-planner-new-tax-law
-
The excel nerd in me likes this a lot so far. Two things I noticed right away:
1. I don't think the drop down box for "Married" is doubling the standard deduction for married folks.
2. The Child Tax credits are only refundable up to $1,400. I did this by subtracting $1,400 per kid first, then $600 per kid up to the remaining tax liability. I am not sure how this would interact with other possible credits.
-
1. I don't think the drop down box for "Married" is doubling the standard deduction for married folks.
That's a huge problem and you're right. Fixed.
2. The Child Tax credits are only refundable up to $1,400. I did this by subtracting $1,400 per kid first, then $600 per kid up to the remaining tax liability. I am not sure how this would interact with other possible credits.
I thought about this and decided not to try to implement it. It would be somewhat difficult to back out the refundable portion, especially with cell formulas alone. This only matters if your taxes owed are negative, I think, unless I'm misunderstanding it. I don't have kids.
-
I think this calc does something very similar: http://taxplancalculator.com/
-
I think this calc does something very similar: http://taxplancalculator.com/
Looks like a nice simple one, but it doesn't do things like cap gains, 401k, ACA investment tax (from what I can tell), etc.
Plus mine lets you see all the calculations in detail. Different target audiences, I presume.
I'm not selling anything, so it's fine if something else works better for others. :) I just thought I'd put it out there.
I'm glad Hvillian pointed out my error on the standard deductions/exemptions (for married) for sure.
-
I just thought I'd put it out there.
Thanks for putting it out there
-
Very cool.
You may want to change the way 401k contributions work. My wife and I have two 403(b) and one 457 account which allowed us to shelter 54k this year.
-
You may want to change the way 401k contributions work. My wife and I have two 403(b) and one 457 account which allowed us to shelter 54k this year.
That’s a great situation to have. I like the way it is for simplicity’s sake, though.
What you can do is either:
- Add the extra in “Other Deductions”.
- Override the calculated 401k dollar amount. You’ll get a warning but you can click OK. You can then ignore or delete the percentage amount.
Thank you for your feedback!
-
Thank you for sharing this!
Do charitable and mortgage interest go in "other deductions"?
-
Thank you for sharing this!
Do charitable and mortgage interest go in "other deductions"?
That would be if you itemize. This only handles standard deduction.
The standard deduction for married is $24,000 now. It would be difficult for a mustachian couple to surpass it, but it’s possible.
With SALT limits and higher deduction, I’m interested if you’ll itemize for tax year 2018 if you are for tax year 2017.
In any case, this sheet only works for standard deduction. Sorry. There are too many rules for all the deductions for me to reliably handle in the time/energy I have for this. :)
-
Thanks for the reply. I am still trying to understand the "final" bill so I am not sure what I will do next year. The other calculator referenced told me I should switch and not itemize. Guess we'll see..
-
Hey, nice calculator. one thing to account for is the $10,000 limit on state and property taxes for deductions. You may want to have that as a separate field and if that amount + the other deductions is < standard deduction, have it automatically choose the standard deduction. It makes a big difference, for example with ours we have $293k - $43k in retirement plans (over-rode it), - $35k in state tax & other deductions currently. That comes out to $43k in taxes. Same scenario with standard deduction instead of state tax deduction gives $40k, but the calculator would incorrectly give $32k since it still thinks you can take the itemized deductions.
-
Hey, nice calculator. one thing to account for is the $10,000 limit on state and property taxes for deductions. You may want to have that as a separate field and if that amount + the other deductions is < standard deduction, have it automatically choose the standard deduction.
Hi. Thank you!
I should make it clear in bold at the top that this is for standard deduction only. It doesn’t handle the myriad itemized deductions at all. The “other deductions” field is basically there to allow you to adjust taxable income for situations I may not have anticipated. The other credits section is to similarly adjust the income tax directly for situations I didn’t anticipate.
You can’t use it for itemized returns, but many people will be switching to standard, with a larger deduction and SALT limitations.
I’ll update tomorrow when I’m on a computer again (mobile now).
-
Cool, I see. Thanks for the clarification. For what it's worth, I did some calculations using our specific tax scenario and applied various income levels from $200k-$600k, and found that even with a $50k itemized deductions (high state tax + property tax + mortgage interest). Scenarios with income greater than $300k filing jointly come out ahead with 2-5% tax savings, while filing single and making less than $750k pays more with the new tax plan.
-
Cool, I see. Thanks for the clarification. For what it's worth, I did some calculations using our specific tax scenario and applied various income levels from $200k-$600k, and found that even with a $50k itemized deductions (high state tax + property tax + mortgage interest). Scenarios with income greater than $300k filing jointly come out ahead with 2-5% tax savings, while filing single and making less than $750k pays more with the new tax plan.
I think you’d have to screenshot that. The increase in standard deduction, reduction in the brackets, and increase to the Child Tax Credit plus huge increase in CTC phase out levels means everyone pays less from what I saw.
Rich get way more out of it, of course. Especially pass through income.
Edit: I’m on a phone. Re reading I think you’re saying they pay more with high SALT. That’s probably true. I have no SALT (Texas) and very low mortgage interest. I don’t even itemize now.
-
Any chance to add in pass through income to calculate that along with W2 income?
-
Thanks for posting this! I plan to try it out between Christmas and New Year's
-
Thank you for putting this together, very helpful as I plan for next year.
Adding a calculation for the saver's tax credit may be a useful addition.
-
Any chance to add in pass through income to calculate that along with W2 income?
Adding a calculation for the saver's tax credit may be a useful addition.
I created a copy and started working on both, but there are many rules around phase outs on pass through income and type of business. Also, with Saver's Credit, you have phase outs that don't follow marginal rates as well.
I could probably do both, but it would be complicated and likely to be inaccurate on my first try.
I'll have to leave it to others to extend, if they wish; sorry. I have to be selective with how I spend the limited keystrokes and mental energy (for Excel-ish programs) I have before I die. :-)
I did add notes at the top and beside 401k to clarify the usage there, in response to other comments here last night.
-
Update: It wasn't calculating additional personal exemptions for children in tax year 2017. (I didn't really know how that worked since I don't have kids. I thought each filer got a deduction and exemption that were now replaced with 1 deduction in the new plan. I didn't realize in the old plan, children also got the $4,050 exemption in addition to child tax credit).
This has been fixed. Please make a new copy if you have children and put your data in again.
Or, you can copy Tax Rates 2017's cell C12 to your current Tax Rates 2017 sheet.
The part in red was added to add child personal exemptions.
=if(Income!$B$5="No",4050,4050*2)+(4050*Income!$B$6)
If you have children, this might significantly impact your results. If you don't, it doesn't matter.
-
Outside the Box - I hope you don't mind, I took some of your tax brackets and formulas and combined them with the 1040 workbook from excel1040.com. It does not do a lot of the background calculations (making sure you are eligible for various deductions and credits, AMT, etc.) that the full excel1040 does, but the layout makes the inputs easy to pull from a prior 1040 and make simple adjustments.
Google Sheet Link (https://docs.google.com/spreadsheets/d/1AKbqPCJVuJEq-ZcGCyWLrYcI0Sp36s4_JjGERMcswiQ/edit?usp=sharing)
File > Make a copy . . . and then edit your "local" copy in your Google sheets.
I am going to test it against your calculations and see what differences I find.
Yellow Fill with Blue font are inputs; Pink fill is formulas
Known Limitations:
- Head of Household is an option, but I haven't tested it at all
- Married filing Separately is NOT an option
- I haven't setup anything to actually use the Long Term Capital Gains Rate
- I haven't touched the AMT calculations
- There are other fields filled in gray that I haven't setup yet
- Child Tax Credit for 2018 not phased out for high incomes (new limit is $400,000 for Married Filing Jointly, so won't affect majority of people)
-
Outside the Box - I hope you don't mind, I took some of your tax brackets and formulas and combined them with the 1040 workbook from excel1040.com. It does not do a lot of the background calculations (making sure you are eligible for various deductions and credits, AMT, etc.) that the full excel1040 does, but the layout makes the inputs easy to pull from a prior 1040 and make simple adjustments.
Google Sheet Link (https://docs.google.com/spreadsheets/d/1AKbqPCJVuJEq-ZcGCyWLrYcI0Sp36s4_JjGERMcswiQ/edit?usp=sharing)
File > Make a copy . . . and then edit your "local" copy in your Google sheets.
I am going to test it against your calculations and see what differences I find.
Yellow Fill with Blue font are inputs; Pink fill is formulas
Known Limitations:
- Head of Household is an option, but I haven't tested it at all
- Married filing Separately is NOT an option
- I haven't setup anything to actually use the Long Term Capital Gains Rate
- I haven't touched the AMT calculations
- There are other fields filled in gray that I haven't setup yet
- Child Tax Credit for 2018 not phased out for high incomes (new limit is $400,000 for Married Filing Jointly, so won't affect majority of people)
That looks very cool! I didn't know about Excel 1040. I wish they'd update to do the 2018 on their own, but yours looks like a great start.
Mine is only for very simple returns. It should handle the phase out of the Child Tax Credit, according to the rules I read. I don't support anything but MFJ and Single. I don't handle AMT. I don't handle itemized deductions.
I basically made it for me, shared with some friends, extended it for some additional circumstances, shared it here, extended it for some more common and easier to implement circumstances, and there it stands. :)
Mine shouldn't be considered comprehensive, but it should be a good way to see how the changes will impact you for a lot of people. Once yours is more fully tested (or integrated with excel1040.com), it looks like it will be much better.
-
FYI - A significantly more comprehensive version is now available by the guy that does excel1040.
https://sites.google.com/site/excel1040/home/2018-tax-planner-new-tax-law (https://sites.google.com/site/excel1040/home/2018-tax-planner-new-tax-law)
-
FYI - A significantly more comprehensive version is now available by the guy that does excel1040.
https://sites.google.com/site/excel1040/home/2018-tax-planner-new-tax-law (https://sites.google.com/site/excel1040/home/2018-tax-planner-new-tax-law)
Awesome! I was kind of hoping for something like that when I made mine. I'm glad they updated it. I'll update my original post with a link.