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

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #150 on: March 19, 2019, 12:57:23 PM »
Versions 11.23 and 12.05
  • Updated AMT calculations to match the new form 6251.
  • Updated some URLs to point to the most recent IRS forms.
The AMT calculations are affected if one has a QBI deduction.

To Paul der Krake:  Looked at that, and you are correct - it's rather complicated.  Too complicated for the relatively small audience. ;)

2018 taxes version: 2018 Case Study Spreadsheet
2019 taxes version: 2019 Case Study Spreadsheet
2020 taxes version: 2020 Case Study Spreadsheet
« Last Edit: August 11, 2020, 05:43:15 PM by MDM »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #151 on: March 20, 2019, 05:52:15 PM »
Version 12.06
  • Updated state income tax calculations for 2019.
Thanks to 2019 State Individual Income Tax Rates and Brackets | Tax Foundation for all the legwork.

Note that states may change 2019 law throughout the year, there may have been copy/paste errors, etc.  State Income Tax calculations - Crowdsourcing request is a good place to note errors & omissions so they may be corrected in future versions.


2018 taxes version: 2018 Case Study Spreadsheet
2019 taxes version: 2019 Case Study Spreadsheet
2020 taxes version: 2020 Case Study Spreadsheet
« Last Edit: August 11, 2020, 05:43:27 PM by MDM »

FireAnt

  • Bristles
  • ***
  • Posts: 334
  • Age: 39
  • Location: Michigan
Re: Case Study Spreadsheet updates
« Reply #152 on: April 07, 2019, 12:44:44 PM »
Doing the 2018 version.

Filing status- We're MFS, should that be marked #3 HOH?

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #153 on: April 07, 2019, 01:06:59 PM »
Doing the 2018 version.

Filing status- We're MFS, should that be marked #3 HOH?
Unfortunately it isn't set up to do MFS.  Too many exceptions in the tax code for too few people using this status to make it worth doing - at least, that's been the perception....

The spreadsheet is fully changeable by anyone, so if you choose to modify it to handle MFS and the changes aren't too extensive, let me know and perhaps we can release a new version.

FireAnt

  • Bristles
  • ***
  • Posts: 334
  • Age: 39
  • Location: Michigan
Re: Case Study Spreadsheet updates
« Reply #154 on: April 07, 2019, 02:00:17 PM »
Doing the 2018 version.

Filing status- We're MFS, should that be marked #3 HOH?
Unfortunately it isn't set up to do MFS.  Too many exceptions in the tax code for too few people using this status to make it worth doing - at least, that's been the perception....

The spreadsheet is fully changeable by anyone, so if you choose to modify it to handle MFS and the changes aren't too extensive, let me know and perhaps we can release a new version.

I'm not smart enough to modify it. :) The calculations from my 2018 taxes were about $1000 off when I used option #3.

PCOwner

  • 5 O'Clock Shadow
  • *
  • Posts: 3
Re: Case Study Spreadsheet updates
« Reply #155 on: April 21, 2019, 03:47:52 PM »
Hello,

This is my first post. I found your site and especially this thread --priceless !!!

I do a little of tax preparation through AARP foundation, VITA (volunteer tax assistance program), but do have the breadth to whip up this kind of spreadsheet. It takes a lot of time to read the publications and educate on tax laws.  I potentially could, but it will take some time. I work full time in a non financial sector and volunteering when I can.  Just there isn't all time t o educate yourself in all areas of the tax law.

Curious.. I read that Mr Moneymustache had sold this site, what will happen to the thread? I would love to continue to collaborate with you, friends in the future, especially on this topic. 

Also, is a way to remove all the verification questions before I can post? I can't find answers to all the questions in captcha.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #156 on: April 21, 2019, 04:44:04 PM »
Hello,

This is my first post. I found your site and especially this thread --priceless !!!

I do a little of tax preparation through AARP foundation, VITA (volunteer tax assistance program), but do have the breadth to whip up this kind of spreadsheet. It takes a lot of time to read the publications and educate on tax laws.  I potentially could, but it will take some time. I work full time in a non financial sector and volunteering when I can.  Just there isn't all time t o educate yourself in all areas of the tax law.

Curious.. I read that Mr Moneymustache had sold this site, what will happen to the thread? I would love to continue to collaborate with you, friends in the future, especially on this topic. 

Also, is a way to remove all the verification questions before I can post? I can't find answers to all the questions in captcha.
Apparently you did enough to allow posting. :)

Note the date on which MMM posted that he had sold the site: April ____!

PCOwner

  • 5 O'Clock Shadow
  • *
  • Posts: 3
Re: Case Study Spreadsheet updates
« Reply #157 on: April 21, 2019, 10:51:49 PM »
Yes, I know, I  got through the captcha "wall"... :)

He sold the site, just recently (hopefully, this question will not on the captcha list) .

April 1, are you saying, it's his joke ?


MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #158 on: April 21, 2019, 11:07:29 PM »
Yes, I know, I  got through the captcha "wall"... :)

He sold the site, just recently (hopefully, this question will not on the captcha list) .

April 1, are you saying, it's his joke ?
Yes - April Fools!

See How I Sold This Website for $9 Million for the first-person account.

PCOwner

  • 5 O'Clock Shadow
  • *
  • Posts: 3
Re: Case Study Spreadsheet updates
« Reply #159 on: April 22, 2019, 08:14:45 PM »
Thank you for setting the clarity to this article.  As far as this work that you'd done. Simply amazing.   How did you come to creating this spreadsheet? Lots of reading on IRS site or you have a background in taxes? Also, how long did it take you to whip this up?

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #160 on: April 22, 2019, 08:50:19 PM »
Thank you for setting the clarity to this article.  As far as this work that you'd done. Simply amazing.   How did you come to creating this spreadsheet? Lots of reading on IRS site or you have a background in taxes? Also, how long did it take you to whip this up?
I'll send a PM.  Don't mind answering, but it would be good to keep this thread mostly for issues on the tool itself.

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074
Re: Case Study Spreadsheet updates
« Reply #161 on: July 02, 2019, 12:58:57 PM »
How do I classify Roth conversions, ie, what line do I enter them on?
Since I have never done one, I assume, if I remove $20k from an IRA or a SEP/IRA and put it into a Roth/IRA, I then need to enter $20,000 into some income box on the spreadsheet.

                       Thanks.

OK, I need help, I put $12,000 into line 23 Taxable interest, non qualified dividends.
And I put varying numbers in LTCG (line 27) or Short term cap gain (line 26),
$20,000, $50,000, $100,000 and $1,000,000, but I can't generate any taxes due.
 Here I'm assuming Line I67 is my tax due line.
« Last Edit: July 02, 2019, 01:34:33 PM by BTDretire »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #162 on: July 02, 2019, 01:52:56 PM »
How do I classify Roth conversions, ie, what line do I enter them on?
Since I have never done one, I assume, if I remove $20k from an IRA or a SEP/IRA and put it into a Roth/IRA, I then need to enter $20,000 into some income box on the spreadsheet.
Cell D31.  Money coming from a traditional IRA is taxed the same, regardless of whether it is converted to Roth or not.

There are Qualified Charitable Distributions (QCDs) but that's a different topic.

Quote
OK, I need help, I put $12,000 into line 23 Taxable interest, non qualified dividends.
And I put varying numbers in LTCG (line 27) or Short term cap gain (line 26),
$20,000, $50,000, $100,000 and $1,000,000, but I can't generate any taxes due.
 Here I'm assuming Line I67 is my tax due line.
Yes on I67.

For MFJ, $12000 in D23 and $50000 in D27 does indeed lead to $0 tax, due to the standard deduction and the way LTCGs are taxed.

But $100,000 or $1,000,000 in D27, or any of those four amounts in D26, produces a non-zero amount in I67 for me.

Are you using an Excel version that you have downloaded to your computer, or something else?

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074
Re: Case Study Spreadsheet updates
« Reply #163 on: July 02, 2019, 02:50:22 PM »
How do I classify Roth conversions, ie, what line do I enter them on?
Since I have never done one, I assume, if I remove $20k from an IRA or a SEP/IRA and put it into a Roth/IRA, I then need to enter $20,000 into some income box on the spreadsheet.
Cell D31.  Money coming from a traditional IRA is taxed the same, regardless of whether it is converted to Roth or not.

There are Qualified Charitable Distributions (QCDs) but that's a different topic.

Quote
OK, I need help, I put $12,000 into line 23 Taxable interest, non qualified dividends.
And I put varying numbers in LTCG (line 27) or Short term cap gain (line 26),
$20,000, $50,000, $100,000 and $1,000,000, but I can't generate any taxes due.
 Here I'm assuming Line I67 is my tax due line.
Yes on I67.

For MFJ, $12000 in D23 and $50000 in D27 does indeed lead to $0 tax, due to the standard deduction and the way LTCGs are taxed.

But $100,000 or $1,000,000 in D27, or any of those four amounts in D26, produces a non-zero amount in I67 for me.

Are you using an Excel version that you have downloaded to your computer, or something else?
As to your last question, I think I'm in Google sheets.
 I may need a walk through just to open a sheet that 'I' can put my numbers in.
 I click on this link, '2019 Case Study Spreadsheet'
https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing
So first thing, I can't enter any numbers, so I see I'm in "View Only Mode"
I click the drop down and click "Request Edit Access' and write a note.
Hmm, I thought that's what I did last time so I could edit, not working this time.
 How do I get access to enter my numbers.
                    Thanks, for your help.
 btw, I added regular payroll income and did generate some taxes due, but even $1,000,000 in short term gains didn't add to the taxes.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #164 on: July 02, 2019, 03:10:55 PM »
As to your last question, I think I'm in Google sheets.
 I may need a walk through just to open a sheet that 'I' can put my numbers in.
 I click on this link, '2019 Case Study Spreadsheet'
https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing
So first thing, I can't enter any numbers, so I see I'm in "View Only Mode"
I click the drop down and click "Request Edit Access' and write a note.
Hmm, I thought that's what I did last time so I could edit, not working this time.
 How do I get access to enter my numbers.
                    Thanks, for your help.
 btw, I added regular payroll income and did generate some taxes due, but even $1,000,000 in short term gains didn't add to the taxes.
Rather than "Request Edit Access" - which, if granted, would allow one to edit the same file that everyone else sees, so unlikely to be granted ;) - click the "Download" icon (the one with the downward arrow in the image below).  That image is probably in the upper right of your screen after you click on the link you described.


Then, either open in Excel immediately and later save from Excel to your computer, or save immediately to your computer and later open that file with Excel.

Does that work better?

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074
Re: Case Study Spreadsheet updates
« Reply #165 on: July 02, 2019, 04:54:42 PM »
As to your last question, I think I'm in Google sheets.
 I may need a walk through just to open a sheet that 'I' can put my numbers in.
 I click on this link, '2019 Case Study Spreadsheet'
https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing
So first thing, I can't enter any numbers, so I see I'm in "View Only Mode"
I click the drop down and click "Request Edit Access' and write a note.
Hmm, I thought that's what I did last time so I could edit, not working this time.
 How do I get access to enter my numbers.
                    Thanks, for your help.
 btw, I added regular payroll income and did generate some taxes due, but even $1,000,000 in short term gains didn't add to the taxes.
Rather than "Request Edit Access" - which, if granted, would allow one to edit the same file that everyone else sees, so unlikely to be granted ;) - click the "Download" icon (the one with the downward arrow in the image below).  That image is probably in the upper right of your screen after you click on the link you described.


Then, either open in Excel immediately and later save from Excel to your computer, or save immediately to your computer and later open that file with Excel.

Does that work better?
Now I recall from last year, I used Openoffice to use it, but had a few minor issues, so I need to get Excel to do this right.
 I did as you requested above, opened it in Excel, saved it and then open the saved file.
 Now when I try to put data in a cell, I get "Protected Cells can not be modified"
 What now, or is a copy of Excel needed?
EDIT,
"I found my answer, "Remove Protection Using OpenOffice
Click the "Tools" menu, select "Protect Document," and then click "Sheet" to unlock the spreadsheet. OpenOffice does not require the password. Select the "File" menu, click "Save As," and then change the file type to "Excel 97/2000/XP (.xls)."
« Last Edit: July 02, 2019, 06:48:26 PM by BTDretire »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #166 on: July 02, 2019, 06:54:11 PM »
"I found my answer, "Remove Protection Using OpenOffice
Click the "Tools" menu, select "Protect Document," and then click "Sheet" to unlock the spreadsheet. OpenOffice does not require the password. Select the "File" menu, click "Save As," and then change the file type to "Excel 97/2000/XP (.xls)."
If this is what you need to make changes in cells with a green background in OpenOffice, then thanks for sharing. 

Most non-green-background cells are protected by default because in most situations they shouldn't be changed.  But not all situations, so if one needs to remove protection it's easy enough.

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074
Re: Case Study Spreadsheet updates
« Reply #167 on: July 02, 2019, 07:36:25 PM »
"I found my answer, "Remove Protection Using OpenOffice
Click the "Tools" menu, select "Protect Document," and then click "Sheet" to unlock the spreadsheet. OpenOffice does not require the password. Select the "File" menu, click "Save As," and then change the file type to "Excel 97/2000/XP (.xls)."
If this is what you need to make changes in cells with a green background in OpenOffice, then thanks for sharing.
  Yes, that allowed me to edit green cells, BUT it also allows brown cells to be altered and I have enough misunderstanding, I'm not ready to do that. However, I did find I57 Federal Withholding is $4,200 and G55 HSA is $9,000. I don't see what caused those numbers because I think they should be $0.
 Your thoughts?
Quote
Most non-green-background cells are protected by default because in most situations they shouldn't be changed.  But not all situations, so if one needs to remove protection it's easy enough.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #168 on: July 02, 2019, 07:59:08 PM »
"I found my answer, "Remove Protection Using OpenOffice
Click the "Tools" menu, select "Protect Document," and then click "Sheet" to unlock the spreadsheet. OpenOffice does not require the password. Select the "File" menu, click "Save As," and then change the file type to "Excel 97/2000/XP (.xls)."
If this is what you need to make changes in cells with a green background in OpenOffice, then thanks for sharing.
  Yes, that allowed me to edit green cells, BUT it also allows brown cells to be altered and I have enough misunderstanding, I'm not ready to do that. However, I did find I57 Federal Withholding is $4,200 and G55 HSA is $9,000. I don't see what caused those numbers because I think they should be $0.
 Your thoughts?
Brown cells should not be changed.  They are the ones I (might) need to update every new tax year, based on inflation, etc., as the IRS dictates.

The $4200 is the annual amount per W-4 allowance that is to be used by an employer for withholding calculations.  See p. 45 of https://www.irs.gov/pub/irs-pdf/p15.pdf.

The $9000 is the maximum HSA amount for MFJ both 55 or older.

You might find Case Study Spreadsheet club (like a book club...only with a spreadsheet...) useful.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #169 on: July 05, 2019, 12:34:42 PM »
Version 12.07

2018 taxes version: 2018 Case Study Spreadsheet
2019 taxes version: 2019 Case Study Spreadsheet
2020 taxes version: 2020 Case Study Spreadsheet
« Last Edit: August 11, 2020, 05:43:53 PM by MDM »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #170 on: October 17, 2019, 04:08:30 AM »
Version 12.08
  • Slight modifications to some state tax calculations
  • Small wording and format changes
  • Include FEIE in Medicare MAGI
  • Add Roth distribution table to 'Basic Terms' tab (more 'advanced' than 'basic' but it seems as good a place as any....
2018 taxes version: 2018 Case Study Spreadsheet
2019 taxes version: 2019 Case Study Spreadsheet
2020 taxes version: 2020 Case Study Spreadsheet

Note:
« Last Edit: August 11, 2020, 05:44:09 PM by MDM »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #171 on: November 01, 2019, 10:45:45 PM »
Version 12.09
  • Added 529 deductions/credits to state tax calculations. See State Income Tax calculations - Crowdsourcing request.
  • Updated IRMAA tiers to the 2020 values.  It will be the 2021 tier definitions that are used to compare with 2019 MAGI, but the 2021 values aren't available yet.
  • Small wording and format changes
  • Updated Social Security calculations to match the October 2019 updates
2018 taxes version: 2018 Case Study Spreadsheet
2019 taxes version: 2019 Case Study Spreadsheet
2020 taxes version: 2020 Case Study Spreadsheet

Note:
« Last Edit: August 11, 2020, 05:44:24 PM by MDM »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #172 on: December 03, 2019, 04:13:49 PM »
Version 12.10
  • Updated IRMAA premium amounts to the 2020 values.  It will be the 2021 premium amounts that result from 2019 MAGI, but the 2021 values aren't available yet.
  • Small wording and format changes
  • Fixed income reference for Form 2441 (Child/Dependent Care credit)
2018 taxes version: 2018 Case Study Spreadsheet
2019 taxes version: 2019 Case Study Spreadsheet
2020 taxes version: 2020 Case Study Spreadsheet

Note:
« Last Edit: August 11, 2020, 05:44:39 PM by MDM »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #173 on: December 03, 2019, 04:14:49 PM »
Version 20.00
  • First version with all 2020 tax updates.
  • Most changes are simply one number for another number.  E.g., the start and end of the 12% bracket for single filers, etc.
  • The W-4 form and associated withholding calculations will be significantly different from 2019.  At this time a draft version from the IRS is used.
Until at least the latter part of January, the 2019 taxes version will remain the default.  At some point, people should be using real tax software for their actual 2019 returns and the default for "case studies" will change to the 2020 version.  "Default" is defined as "the spreadsheet located at https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing."

As always, suggestions, and notification of errors (either in this thread or via PM), are appreciated.

2018 taxes version: 2018 Case Study Spreadsheet

2019 taxes version: 2019 Case Study Spreadsheet

2020 taxes version: 2020 Case Study Spreadsheet

Note:
« Last Edit: January 27, 2020, 09:41:44 PM by MDM »

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074
Re: Case Study Spreadsheet updates
« Reply #174 on: December 16, 2019, 07:36:16 AM »
 Hi all,
 I'm doing your end planning trying to use the CSS to help me plan.
I have two problems I don't have Excel, so it kinda works, but it's questionable.
and I just don't have enough understanding.

I have several questions, I was previously self employed so I had so many deductions.
Now I'm retired and only have investment income.
I wonder if I have any this year. I have earned interest on a property and have spent
$4,300 on a lawyer with some problems with that, are those deductible and where on the spreadsheet?
 My plan is to withdraw the max but stay in the 12% bracket. I figure that as paying $9,086 in tax. 10% of $19,400 = 1,940 and 12% of $59550 = 7,146 for a total of $9,086.
I'm looking at 3 inputs for income Taxable interest $4,650, tIRA withdrawal possibly $96,000, still to be decided because I can withdraw non tax deferred money, and other income, $2,600.
 I have about $31,000 of loss carryover and not sure how to use it or what line to use it on.
I can take distributions from non tax deferred money instead of IRA money.
 Can anyone give me some guidance.

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074
Re: Case Study Spreadsheet updates
« Reply #175 on: December 16, 2019, 08:20:29 AM »
Is there a line for carryover losses on the CSS? Found it.
But, If I put in $40,000 of LTCG;s it still says I only get to use $3,000 of my carryover.
« Last Edit: December 16, 2019, 08:25:21 AM by BTDretire »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #176 on: December 16, 2019, 08:27:33 AM »
Can anyone give me some guidance.
A few quick answers but if you want more it would be better to start a new thread, perhaps in the Case Study section:
- Unless it is an expense for your own business, the lawyer fee will not be deductible.
- $9086 is indeed the federal tax due for a 12% bracket full of ordinary income.
- See the Schedule D section starting in L54 for capital loss carryover entry.  And yes, the law says only $3K/yr can be used to offset ordinary income.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #177 on: December 16, 2019, 10:41:15 AM »
Version 20.01
  • Revisions to W-4 section.
New W-4 section starting in cell Calculations!F57.  See notes there for more information about data entry.

Some filers will have no income other than a single W-2, standard deduction, and no credits other than the Child or Other Dependent.  For them, filing the new Form W-4 using the number of child and other dependents for step 3 and nothing in step 4 will work well.

For filers with more complicated situations, the Tentative Withholding Amount as outlined on p. 5 of Pub. 15-T may be too inaccurate.  If the expected refund is too large, a positive number entered in Step 3 (without regard to the number of child and other dependents) will reduce annual withholding by that much (but no lower than to $0) and thus reduce the refund.

If the expected tax owed at filing is too large, a positive number entered in Step 4c will increase withholding per pay by that much and thus reduce the tax owed at filing.

Users may adjust the Step 3 or Step 4c numbers to reach a desired refund/tax owed target.  Of course, any needed W-4 changes will have to go through Payroll.


Until at least the latter part of January, the 2019 taxes version will remain the default.  At some point, people should be using real tax software for their actual 2019 returns planning for 2020 using the 2020 version.

As always, suggestions, and notification of errors (either in this thread or via PM), are appreciated.

2018 taxes version: 2018 Case Study Spreadsheet

2019 taxes version: 2019 Case Study Spreadsheet

2020 taxes version: 2020 Case Study Spreadsheet

Note:
« Last Edit: January 27, 2020, 06:10:15 PM by MDM »

mjb

  • Stubble
  • **
  • Posts: 100
Re: Case Study Spreadsheet (CSS) updates
« Reply #178 on: December 20, 2019, 06:02:17 AM »
Illinois does not tax Traditional IRA distributions (including Traditional -> Roth IRA conversions), cells A31 - D31 on the "Calculations" tab. See: https://www2.illinois.gov/rev/QuestionsAndAnswers/pages/99.aspx

My apologies if it's been addressed in the new version, but previous versions of the spreadsheet didn't take this into consideration so I had to modify it myself.

Thanks for your invaluable work!

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #179 on: December 21, 2019, 12:41:21 AM »
Illinois does not tax Traditional IRA distributions (including Traditional -> Roth IRA conversions), cells A31 - D31 on the "Calculations" tab. See: https://www2.illinois.gov/rev/QuestionsAndAnswers/pages/99.aspx

My apologies if it's been addressed in the new version, but previous versions of the spreadsheet didn't take this into consideration so I had to modify it myself.

Thanks for your invaluable work!
Thanks!  That one seems easy enough to add: putting =-B11 in cell 'State Tax'!O35 appears to do the trick.  Probably won't upload a new version just for that, but it will be in a new version when one comes out.

State Income Tax calculations - Crowdsourcing request is a good place to post other state return issues. 

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #180 on: January 27, 2020, 09:46:11 PM »
Version 20.02
  • Slight change to W-4 withholding calculations.  Final Publication 15-T differed from the draft version.
  • Incorporated changes to Idaho and Illinois state taxes discussed in recent posts

Might add a "Section 199A dividend" entry as those seem much more common on 2019 1099-DIVs than in 2018.  Although, the actual amounts are relatively small so...?

2018 taxes version: 2018 Case Study Spreadsheet

2019 taxes version: 2019 Case Study Spreadsheet

2020 taxes version: 2020 Case Study Spreadsheet

Note:

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #181 on: February 07, 2020, 07:12:57 PM »
Version 20.03
Thanks once again to the folks at Tax Foundation for aggregating at least the basics for all state and D.C. income taxes. 


2018 taxes version: 2018 Case Study Spreadsheet

2019 taxes version: 2019 Case Study Spreadsheet

2020 taxes version: 2020 Case Study Spreadsheet

Note:

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074
Re: Case Study Spreadsheet updates
« Reply #182 on: March 17, 2020, 07:25:29 AM »
Do you know if the program runs successfully in Office 2000 Pro?
Or Office 2000.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #183 on: March 17, 2020, 10:42:57 AM »
Do you know if the program runs successfully in Office 2000 Pro?
Or Office 2000.
Don't know.  See the two posts linked in the latest update for warnings that can be disregarded, but any others might indicate incompatibility with that version.

One quick test: put 40000 into cell B3 (and do nothing else).  If the marginal rate chart (over cell J100) shows -12% until a spike at $7500, and another spike at $18750 (the spikes are saver's credit tiers) then there is a good chance everything is working.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #184 on: April 07, 2020, 09:59:22 PM »
Version 12.11 (2019)
Version 20.04 (2020)
  • Added Section 199A dividend entry and its QBI deduction effect to both.
  • Added suggestions for form W-4 entries to the 2020 version.
2019 taxes version: 2019 Case Study Spreadsheet
2020 taxes version: 2020 Case Study Spreadsheet

Note:
« Last Edit: April 30, 2020, 08:03:38 PM by MDM »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #185 on: May 08, 2020, 10:38:25 PM »
Version 20.05 (2020)
  • Added $300 above-the-line charitable deduction.  Eliminated 60% AGI limit on charitable dedutions.
  • Assume an "off list" independent variable for the marginal rate chart is an annual amount.
  • Added the Capital Loss Carryover worksheet for the next year's return.
Choosing the $300 above-the-line charitable deduction forces use of the standard deduction (i.e., no itemized deduction used).

2019 taxes version: 2019 Case Study Spreadsheet
2020 taxes version: 2020 Case Study Spreadsheet

Note:

Jason95357

  • 5 O'Clock Shadow
  • *
  • Posts: 9
Question re:employer plans
« Reply #186 on: May 09, 2020, 10:13:08 PM »
New user of the CashFlow tool.  Very new to this level of finance and investing as well, so sorry if I'm ignorant in more than one area or using the wrong terms.  I have three clarifying questions:

I work for a local government entity.  In addition to having a (1.) pension "Basic Retirement Plan" and optional (2.) 457(b) Deferred Compensation, we do not pay Social Security.  Instead of paying SS, we have a (3.) "Supplemental Retirement Plan".  Employees contribute a mandatory 5% pre-tax to this SRP and the employer also contributes 5%.  Our W-2 form has in box 14. Other: "SRP" and the one of the 5% amounts (I'm assuming it is the employee's portion).  Hopefully this is enough detail, for my questions.

The first question: Where do I indicate this SRP pre-tax contribution on the tool?  This isn't a pre-tax pension contribution (we do not contribute to our pension), but that seems about the only place that it would "fit".  My W-2 box 1 wages is less all of my pre-tax payments.  My W-2 box 5 "Medicare wages and tips" includes a 5% SRP contribution (not sure if mine or my employer's part).

The second question: Do I need to account for the employer contributed 5% to the SRP?  I wouldn't think so at this point as it is pre-tax.  Some searching about Box 14 indicates it is informational only and not really used for tax calculation purposes.

The third question: what to do about the automatic SS calculation on the form?  Shall I just unlock the form and zero out the SS in B60?

Thanks in advance, and thanks for such a powerful tool.  It's making this whole experience and figuring out if we should max our tIRA vs. Roth IRAs and whatnot not so hard.
« Last Edit: May 09, 2020, 10:34:28 PM by Jason95357 »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Question re:employer plans
« Reply #187 on: May 10, 2020, 12:51:26 AM »
The first question: Where do I indicate this SRP pre-tax contribution on the tool?  This isn't a pre-tax pension contribution (we do not contribute to our pension), but that seems about the only place that it would "fit".
A rose by any other name....  That seems as good a place as any. 

Quote
My W-2 box 1 wages is less all of my pre-tax payments.  My W-2 box 5 "Medicare wages and tips" includes a 5% SRP contribution (not sure if mine or my employer's part).
Can you get the spreadsheet to match those?  I.e., row 14 for W-2 box 1 and row 9 for W-2 box 5?

Quote
The second question: Do I need to account for the employer contributed 5% to the SRP?  I wouldn't think so at this point as it is pre-tax.  Some searching about Box 14 indicates it is informational only and not really used for tax calculation purposes.
Correct, it seems irrelevant for current year taxes.  If you want to use the simple "Time to FI" section (~rows 166-194) then including whatever you expect from the SRP may be worthwhile.  Might mean "some assembly required."

Quote
The third question: what to do about the automatic SS calculation on the form?  Shall I just unlock the form and zero out the SS in B60?
Sure could.  If appropriate, perhaps you could change the 0.062 in cells B60 and C60 to 0.05.

Quote
Thanks in advance, and thanks for such a powerful tool.  It's making this whole experience and figuring out if we should max our tIRA vs. Roth IRAs and whatnot not so hard.
You're welcome. 

If the paycheck and retirement treatment of those throughout the country who don't pay into SS is mostly standardized, it might be feasible to modify the spreadsheet to handle that automatically, using some input cell to indicate "SRP instead of SS" or similar.  But if the non-SS plans are dissimilar then it's probably not practical to code multiple permutations.

Any more questions or suggestions, please do respond here or using a Personal Message.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #188 on: June 12, 2020, 12:03:28 PM »
Version 20.06 (2020)
  • Medical expense deduction has a 7.5% AGI floor in 2020
  • Handle a "space" in the 529 contribution cell
Although google search results may say otherwise today, in Dec. 2019 congress did extend the 7.5% AGI floor for 2020.  See "(f) Temporary special rule" in [USC02] 26 USC 213: Medical, dental, etc., expenses.

Excel behavior when a cell contains a space may differ from what happens when that cell has a zero or has no entry at all.  Ran across the 529 contribution issue working with a case via PM.  One option would be to impose data validation on all cells expecting a numeric value, but unless this becomes a more frequent issue....

2019 taxes version: 2019 Case Study Spreadsheet
2020 taxes version: 2020 Case Study Spreadsheet

Note:

coderstache

  • 5 O'Clock Shadow
  • *
  • Posts: 5
Re: Case Study Spreadsheet updates
« Reply #189 on: June 24, 2020, 09:50:56 PM »
Thanks for all your hard work on this MDM.

I have a question about SEP IRA contributions, with a single-member LLC ("sole-proprietor"). In the worksheet, I see A47 for specifying self-employed SEP/SIMPLE contributions. Based on this detailed answer and The Balance SMB about whether SEP IRA contributions affect MAGI, I am not sure whether A47 is the right line item to put my "employer contributions" from my LLC as an owner, because it does not lower my MAGI in the sheet as I believe it should, in cell M50 (?). It does adjust M49 and M48 as I expect, but the Roth IRA maximum calculation uses M50.

Quote from: Intuit Forums
Deductible self-employed contributions that appear on (2018) Schedule 1 line 28, including a SEP contribution, do reduce your AGI and therefore reduce your MAGI for the purpose of determining the maximum Roth IRA contribution that you are eligible to make.  The amount on line 28 and the deductible portion of self-employment taxes on line 27 are not added back to AGI when calculating MAGI.

Quote from: The Balance SMB
For a self-employed person contributing to his or her own SEP IRA, contributions are deducted as an adjustment to income on Form 1040 line 28. SEP contributions reduce a person's adjusted gross income, reduce taxable income, and thereby reduce the federal income tax. SEP IRAs do not impact the calculation of the self-employment tax since the self-employment tax is calculated before SEP contributions are calculated. A self-employed person reduces income tax only by contributing to his or her own SEP IRA.

Is this just an issue with the tIRA MAGI vs. the Roth MAGI? Is there a different place you recommend putting SEP IRA contributions so they adjust M50 and therefore the Roth IRA maximum calculation?

I am still personally a ways away from creating a SEP IRA since my spouse still has plenty of room to max out tax-deferred accounts but I expect to deal with this in 2021 at least.

- IRS Publication 560

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #190 on: June 24, 2020, 10:09:19 PM »
Is this just an issue with the tIRA MAGI vs. the Roth MAGI? Is there a different place you recommend putting SEP IRA contributions so they adjust M50 and therefore the Roth IRA maximum calculation?
Great question.

In short: because full compliance with IRS income limits, particularly tIRA deductibility, requires "extra" information (e.g., covered by a retirement plan at work?), the first "Caveat User" on the Instructions tab is "No check is made to enforce compliance with the various IRS rules on IRA, 401k, HSA, etc. contributions."  In other words, there has been some, but not exhaustive, effort put into the "limit check" cells in the box starting in K40 and the MAGIs below that.

But it also says "Requests to overcome these (particularly if accompanied by Excel formulas) will be considered," so if you have studied this enough to have a suggestion I'd be happy to look at it.  Might look at it myself anyway, but even better if someone else does the legwork. :)
« Last Edit: June 24, 2020, 10:17:09 PM by MDM »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #191 on: July 04, 2020, 12:36:08 PM »
Version 20.07 (2020)
  • Correct a calculation in Form 2441 (Child/dependent care credit)
  • Add Roth IRA MAGI and contribution limit calculations
  • Add "future value of a growing annuity" to the Misc. calcs
  • Note that conventions in the "Solve for any of the five (FV, n, PMT, rate, PV)..." section of Misc. calcs differ from standard Excel.
2019 taxes version: 2019 Case Study Spreadsheet
2020 taxes version: 2020 Case Study Spreadsheet

Note:

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #192 on: July 07, 2020, 10:38:06 PM »
Version 20.08 (2020)
  • Implemented the "tax tables" for Earned Income Credit and tax calculations, as shown in 1040 instructions
2019 taxes version: 2019 Case Study Spreadsheet
2020 taxes version: 2020 Case Study Spreadsheet

This comes more from COVID boredom than significant functional improvement, but one now has the option for the CSS to use "tax table" methodology: see p. 46 and p. 62 of https://www.irs.gov/pub/irs-pdf/i1040gi.pdf for the 2019 tables.  The 2020 tables haven't been published yet, but using the midpoint in each "At least X But less than Y" row as input to the tax formulas is how the table values are calculated.

To enable this option, change cell R80 from Y to N.  This could allow one to match tax filing software results (given the same inputs), but the marginal rate chart will also show the actual tax code marginal rates in all their "glory".  To me, a clean marginal rate means more than being $4 off on the absolute tax estimate, but now the option is there.

Note:

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074
Re: Case Study Spreadsheet updates
« Reply #193 on: July 14, 2020, 06:35:40 PM »
I'm working on the spreadsheet again this year, I have a copy of Excel installed. (I used Openoffice last year and it created some problems).
 I don't seem to see where it will calculate 0% tax on my LTCGs under $80,000.
Line 25D Qualified Dividends $6,500
Line 27D LTCGs $78,000
Line 45D HSA    $8,100

Line 16G says I have a taxable income of $50,300.
How do I make it recognize 0% LTCGs?
« Last Edit: July 14, 2020, 07:03:52 PM by BTDretire »

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #194 on: July 14, 2020, 06:50:46 PM »
I'm working on the spreadsheet again this year, I have a copy of Excel installed. (I used Openoffice last year and it created some problems).

How do I make it recognize 0% LTCGs?
That happens automatically.  I'll send a PM.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #195 on: August 25, 2020, 03:31:06 PM »
Version 20.09 (2020)
  • Added the "Deduction Worksheet for Self-Employed" from IRS Pub. 560
  • Changed default x-axis variable for the marginal rate chart to "tIRA withdrawals"
  • Added cells to indicate whether one is covered by a retirement plan at work.
  • Updated logic for IRS maximums on IRA contributions/deductions.  Those remain advisory only.
  • Subtract pension amount from PA state taxable income
  • Other minor text edits and cosmetic changes, including
    • Added a few cell notes for more documentation
    • Restricted entry for max. mortgage principal for interest deductibility to $750K or $1000K only.
    • Handle "space" instead of a blank cell for state identifier

The "Deduction Worksheet for Self-Employed" could be useful for those making both employee and employer contributions.  The cell note for the "Self-employed SEP, SIMPLE, etc." row reads
Quote
For column B and C in this row, either delete the reference to the worksheet calculation and enter a fixed amount, or use the worksheet calculation (by deleting *0 from the formula) if intending to deduct the maximum allowed.

Self-employed Roth contributions should be entered on the appropriate row for the worksheet.  The worksheet itself is around cell X164.
The spreadsheet implementation has some descriptions, but generally relies on one reading the Pub. 560 instructions.

The change in the default x-axis variable is based on the probably-true-more-often-than-not assumption that those interested in other responses (e.g., 401k contribution marginal rates) are more familiar with spreadsheets and how to make changes.

2019 taxes version: 2019 Case Study Spreadsheet
2020 taxes version: 2020 Case Study Spreadsheet


Note:

RWTL

  • Pencil Stache
  • ****
  • Posts: 682
Re: Case Study Spreadsheet updates
« Reply #196 on: October 03, 2020, 04:22:41 AM »
Just want to say thanks for building this spreadsheet.  I've been using it all this year and it has really been invaluable for my tax planning.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet updates
« Reply #197 on: October 03, 2020, 06:59:54 AM »
Just want to say thanks for building this spreadsheet.  I've been using it all this year and it has really been invaluable for my tax planning.
You're welcome!  Any suggestions, just post here or send a PM.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #198 on: November 29, 2020, 08:20:55 PM »
Version 20.10 (2020)
2019 taxes version: 2019 Case Study Spreadsheet
2020 taxes version: 2020 Case Study Spreadsheet

Note:

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Case Study Spreadsheet (CSS) updates
« Reply #199 on: November 29, 2020, 08:31:41 PM »
Version 21.00 (2021)
  • Updated tax brackets and other changes announced by the IRS for 2021.
  • The IRS has not yet updated Pub. 15-T for 2021 withholding.  The 2020 tables still in place should be conservative (less likely to under-withhold)
2019 taxes version: 2019 Case Study Spreadsheet
2020 taxes version: 2020 Case Study Spreadsheet
2021 taxes version: 2021 Case Study Spreadsheet

Note: