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

Paul der Krake

  • Magnum Stache
  • ******
  • Posts: 4647
  • Age: 11
  • Location: USA
Re: Case Study Spreadsheet updates
« Reply #150 on: February 13, 2019, 06:05:52 PM »
There's a new Washington payroll tax in town, but it's rather complicated.

It's 0.4% of wages up to the Social Security cap, but employers pay anywhere from 33% to 100% of it, the rest is paid by employees. And it doesn't apply to union shops, tribes, plus a bunch of other quirks.

Full deets: https://paidleave.wa.gov/premiums

Totally understand if you can't be arsed to put that in.



MDM

  • Walrus Stache
  • *******
  • Posts: 9688
Re: Case Study Spreadsheet (CSS) updates
« Reply #151 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

Most recent version: 2019 Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 9688
Re: Case Study Spreadsheet (CSS) updates
« Reply #152 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

Most recent version: 2019 Case Study Spreadsheet

FireAnt

  • Stubble
  • **
  • Posts: 230
  • Age: 34
  • Location: West Michigan
Re: Case Study Spreadsheet updates
« Reply #153 on: April 07, 2019, 12:44:44 PM »
Doing the 2018 version.

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

MDM

  • Walrus Stache
  • *******
  • Posts: 9688
Re: Case Study Spreadsheet updates
« Reply #154 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

  • Stubble
  • **
  • Posts: 230
  • Age: 34
  • Location: West Michigan
Re: Case Study Spreadsheet updates
« Reply #155 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 #156 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

  • Walrus Stache
  • *******
  • Posts: 9688
Re: Case Study Spreadsheet updates
« Reply #157 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 #158 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

  • Walrus Stache
  • *******
  • Posts: 9688
Re: Case Study Spreadsheet updates
« Reply #159 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 #160 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

  • Walrus Stache
  • *******
  • Posts: 9688
Re: Case Study Spreadsheet updates
« Reply #161 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: 2715
Re: Case Study Spreadsheet updates
« Reply #162 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

  • Walrus Stache
  • *******
  • Posts: 9688
Re: Case Study Spreadsheet updates
« Reply #163 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: 2715
Re: Case Study Spreadsheet updates
« Reply #164 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

  • Walrus Stache
  • *******
  • Posts: 9688
Re: Case Study Spreadsheet updates
« Reply #165 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: 2715
Re: Case Study Spreadsheet updates
« Reply #166 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

  • Walrus Stache
  • *******
  • Posts: 9688
Re: Case Study Spreadsheet updates
« Reply #167 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: 2715
Re: Case Study Spreadsheet updates
« Reply #168 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

  • Walrus Stache
  • *******
  • Posts: 9688
Re: Case Study Spreadsheet updates
« Reply #169 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

  • Walrus Stache
  • *******
  • Posts: 9688
Re: Case Study Spreadsheet (CSS) updates
« Reply #170 on: July 05, 2019, 12:34:42 PM »
Version 12.07

2018 taxes version: 2018 Case Study Spreadsheet

Most recent version: 2019 Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 9688
Re: Case Study Spreadsheet (CSS) updates
« Reply #171 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

Most recent version: 2019 Case Study Spreadsheet

Note:

MDM

  • Walrus Stache
  • *******
  • Posts: 9688
Re: Case Study Spreadsheet (CSS) updates
« Reply #172 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

Most recent version: 2019 Case Study Spreadsheet

Note: