The Money Mustache Community

General Discussion => Forum Information & FAQs => Topic started by: MDM on March 16, 2016, 08:23:57 PM

Title: Case Study Spreadsheet updates
Post by: MDM on March 16, 2016, 08:23:57 PM
The Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing) (CSS) contains various calculations applicable to personal finance, including
and others.

This thread is intended for updates, questions, comments, suggestions, etc. about the spreadsheet tool itself. 

Questions about one's own finances should go in a thread dedicated to that topic, and follow the outline in the "How to Write a Case Study (http://forum.mrmoneymustache.com/ask-a-mustachian/how-to-write-a-'case-study'-topic/)" sticky.

See this post (http://forum.mrmoneymustache.com/ask-a-mustachian/how-to-write-a-'case-study'-topic/msg274228/#msg274228) and the 'Instructions' tab in the spreadsheet itself for - you guessed it - instructions.

For more instructions with accompanying screen shots, the "Using a spreadsheet" section of the Roth IRA conversion - Bogleheads (https://www.bogleheads.org/wiki/Roth_IRA_conversion) wiki discusses that topic, and perhaps best of all Harry Sit at The Finance Buff (https://thefinancebuff.com/) has a great step-by-step for ACA situations: Roth Conversion and Capital Gains On ACA Health Insurance (https://thefinancebuff.com/tax-calculator-aca-obamacare-subsidy.html).  Both of those show common inputs such as filing status, age, income types, etc.

2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)

2021 taxes version: 2021 Case Study Spreadsheet (https://drive.google.com/file/d/18uN77cP1m9ztZ5boHmqMnQ3CngkaDf6p/view?usp=sharing)

Note:
Title: Re: Case Study Spreadsheet updates
Post by: MDM on April 07, 2016, 03:23:37 PM
Version 8.01
Title: Re: Case Study Spreadsheet updates
Post by: MDM on April 23, 2016, 03:50:38 AM
Version 8.02
Title: Re: Case Study Spreadsheet updates
Post by: MDM on April 28, 2016, 07:27:49 PM
Version 8.03

    Changed input to saver's credit calculation to use "AGI + Foreign Income exclusion"
Title: Re: Case Study Spreadsheet updates
Post by: MDM on May 05, 2016, 06:16:31 PM
Version 8.04

    Added an adult earner's age test to the Earned Income Credit.
    Added a "fixed vs. variable rate" calculation to the 'Misc. calcs' tab
    Added 'Basic Terms' tab: Chart of some basic investing terms.  Target audience: someone who asks "is that a Roth or a Vanguard?".  Don't laugh, we (at least, most of us) were all there once….
    Other miscellaneous edits.

    Any thoughts on the 'Basic Terms' chart? 
    Not interested in replicating Investopedia, but:
      - Would also be happy to defer to an existing web page, etc., if there is one that strikes a good balance between simplicity and helpfulness. 
      - Said balance, however, is likely in the eye of the beholder.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on May 16, 2016, 01:34:50 AM
Version 8.05

    Minor edits and formatting - no functionality change.
Title: Re: Case Study Spreadsheet updates
Post by: swick on May 16, 2016, 07:35:57 AM
Thank you, MDM! It is so awesome that you have created this spreadsheet and are keeping it updated for our community to use! :D
Title: Case Study Spreadsheet: Using '401k or taxable' to decide bonds v stocks in ira
Post by: jmk on May 31, 2016, 08:10:38 PM
I've been playing around with the "401k or Taxable" sheet in the CSS to try to figure out whether to fill my 401k (and Roth) with Bonds or Stocks or both.

My question is whether I'm using the spreadsheet correctly.

Since my AA is 50%TSM/50%TBM, I simply ran it once with stocks, and once with bonds, and then added up the various totals in all the permutations to see which mix of taxable and iras was optimal.

My questions:
1) For TSM held long term, is "current marginal tax rate" calculated before or after all the deductions for using any 401ks?
2) Is the "investment taxable yield (if in taxable account)" for TSM 2% or 0% (since it gives off mostly qualified dividends)?
3) If TBM yields 2%, would the "total pretax return" be 2% and would the "investment taxable yield" be 2% (since it distributes mostly non-qualified interest)?  Can this spreadsheet be used for bond funds at all?
4) Since my ultimate concern is to compare Stocks in Taxable/Bonds 401k, versus Bonds in taxable/Stocks in 401k, I ran figures for Stocks and then ran figures for Bonds.
Since my allocation is 50/50 I simply added the final total $ from $10,000 for each permutation to figure what my final total would be.  To calculate using a mix of stocks/bonds in both taxable and 401k I simply averaged the totals for both in taxable and both in 401ks.  Is this methodology correct?   

Thank you.

ps. I hope this is the correct forum; if not I can move it to either investment or taxation.
Title: Re: Case Study Spreadsheet: Using '401k or taxable' to decide bonds v stocks in ira
Post by: MDM on May 31, 2016, 09:12:40 PM
I've been playing around with the "401k or Taxable" sheet in the CSS to try to figure out whether to fill my 401k (and Roth) with Bonds or Stocks or both.
My question is whether I'm using the spreadsheet correctly.  ps. I hope this is the correct forum; if not I can move it to either investment or taxation.
jmk, welcome to the forum.  Yes, you picked a good place to ask.

It wasn't originally designed for stocks vs. bonds, but let's see.... 

Quote
My questions:
1) For TSM held long term, is "current marginal tax rate" calculated before or after all the deductions for using any 401ks?
It is the tax rate you save by using a traditional account.  See https://www.bogleheads.org/wiki/Marginal_tax_rate#Traditional_vs._Roth for more.

Quote
2) Is the "investment taxable yield (if in taxable account)" for TSM 2% or 0% (since it gives off mostly qualified dividends)?
2%.  You distinguish between qualified vs. non-qualified by the "Tax rate on annual investment returns while contributing" entry.

Quote
3) If TBM yields 2%, would the "total pretax return" be 2% and would the "investment taxable yield" be 2% (since it distributes mostly non-qualified interest)?  Can this spreadsheet be used for bond funds at all?
If you are assuming no capital gains at all, then yes.

Quote
4) Since my ultimate concern is to compare Stocks in Taxable/Bonds 401k, versus Bonds in taxable/Stocks in 401k, I ran figures for Stocks and then ran figures for Bonds.
Since my allocation is 50/50 I simply added the final total $ from $10,000 for each permutation to figure what my final total would be.  To calculate using a mix of stocks/bonds in both taxable and 401k I simply averaged the totals for both in taxable and both in 401ks.  Is this methodology correct?
Hmmm.

My knee-jerk reaction was "no, it wasn't designed for that!"  The designed use for that tab is to see if a 401k (or 403b, etc.) plan with egregious fees is still worth using.

But maybe...?  Sometimes things can work quite well, even for situations not originally envisioned.  Given the answers to questions 1-3, I'll toss number 4 back to you for now - what do you think?
Title: Re: Case Study Spreadsheet: Using '401k or taxable' to decide bonds v stocks in ira
Post by: jmk on June 05, 2016, 12:32:23 PM
Quote
2) Is the "investment taxable yield (if in taxable account)" for TSM 2% or 0% (since it gives off mostly qualified dividends)?
2%.  You distinguish between qualified vs. non-qualified by the "Tax rate on annual investment returns while contributing" entry.

But that line is a calculation, not an entry.  It doesn't adjust for qualified v non-qualified--assumes all is ltcg/qualified.  No?

Quote
Quote
4) Since my ultimate concern is to compare Stocks in Taxable/Bonds 401k, versus Bonds in taxable/Stocks in 401k, I ran figures for Stocks and then ran figures for Bonds.
Since my allocation is 50/50 I simply added the final total $ from $10,000 for each permutation to figure what my final total would be.  To calculate using a mix of stocks/bonds in both taxable and 401k I simply averaged the totals for both in taxable and both in 401ks.  Is this methodology correct?
Hmmm.

My knee-jerk reaction was "no, it wasn't designed for that!"  The designed use for that tab is to see if a 401k (or 403b, etc.) plan with egregious fees is still worth using.  But maybe...?  Sometimes things can work quite well, even for situations not originally envisioned.  Given the answers to questions 1-3, I'll toss number 4 back to you for now - what do you think?

It works, I think, with a minor adjustment to one formula. You need to change the calculation for how bond final returns are calculated, to reflect that TSM in taxable has all its dividends qualified (and is taxed at your marginal rate if in an tIRA), but TBM in taxable has none of its interest qualified (and is taxed at your marginal tax rate if in an IRA).  So we need a "flag" to distinguish the two cases.  As you noted, we'd have to assume none of the TBM return is capital gain (you'd still list the total return though, since it gets subtracted out in the calculations.)

If I appropriate the flag in H3 to be a "TBM flag" (0=TSM, 1=TBM), then you'd need to modify the formulas:

Code: [Select]
"Tax rate on LTCG at taxable withdrawal" to "=IF(H3=1,e+sw,IF('Bond v Stock IRA'!a<=15%,0+sc,15%+sc))"
"Tax rate on annual investment returns while contributing" to "=IF(H3=1,a+sc,IF('Bond v Stock IRA'!a<=15%,0+sc,15%+sc))"
"Tax rate on annual investment returns after contributing" to "=IF(H3=1,e+sw,IF('Bond v Stock IRA'!e<=15%,0+sw,15%+sw))"

Not at all sure these fomulas are correct. What  do you think?

Title: Re: Case Study Spreadsheet: Using '401k or taxable' to decide bonds v stocks in ira
Post by: MDM on June 05, 2016, 12:58:15 PM
But that line is a calculation, not an entry.  It doesn't adjust for qualified v non-qualified--assumes all is ltcg/qualified.  No?
Yes, it is a calculation, intended to reduce the number of entries needed for a first-time user by using a heuristic based on the ordinary tax rate.  The heuristic assumes all is ltcg/qualified and any state tax also applies.  You may simply override the calculation if needed.

Quote
It works, I think, with a minor adjustment to one formula. You need to change the calculation for how bond final returns are calculated, to reflect that TSM in taxable has all its dividends qualified (and is taxed at your marginal rate if in an tIRA), but TBM in taxable has none of its interest qualified (and is taxed at your marginal tax rate if in an IRA).  So we need a "flag" to distinguish the two cases.  As you noted, we'd have to assume none of the TBM return is capital gain (you'd still list the total return though, since it gets subtracted out in the calculations.)

If I appropriate the flag in H3 to be a "TBM flag" (0=TSM, 1=TBM), then you'd need to modify the formulas:

Code: [Select]
"Tax rate on LTCG at taxable withdrawal" to "=IF(H3=1,e+sw,IF('Bond v Stock IRA'!a<=15%,0+sc,15%+sc))"
"Tax rate on annual investment returns while contributing" to "=IF(H3=1,a+sc,IF('Bond v Stock IRA'!a<=15%,0+sc,15%+sc))"
"Tax rate on annual investment returns after contributing" to "=IF(H3=1,e+sw,IF('Bond v Stock IRA'!e<=15%,0+sw,15%+sw))"

Not at all sure these fomulas are correct. What  do you think?
That may work, thanks! 

Haven't looked in full detail yet, but at first glance it seems doable.  One quick question: would 'sw' be better to use than 'sc' in the first line of code?
Title: Re: Case Study Spreadsheet: Using '401k or taxable' to decide bonds v stocks in ira
Post by: jmk on June 05, 2016, 04:55:21 PM
Code: [Select]
"Tax rate on LTCG at taxable withdrawal" to "=IF(H3=1,e+sw,IF('Bond v Stock IRA'!a<=15%,0+sc,15%+sc))"
"Tax rate on annual investment returns while contributing" to "=IF(H3=1,a+sc,IF('Bond v Stock IRA'!a<=15%,0+sc,15%+sc))"
"Tax rate on annual investment returns after contributing" to "=IF(H3=1,e+sw,IF('Bond v Stock IRA'!e<=15%,0+sw,15%+sw))"

Haven't looked in full detail yet, but at first glance it seems doable.  One quick question: would 'sw' be better to use than 'sc' in the first line of code?

I think you're right. But then you're gonna need to change your equation, cause that part I left from you. :)  You actually set sc equal to sw (probably as a short cut) in your spreadsheet, and I just left it that way for simplicity.   To be frank, I'm confused about the difference between "tax rate on ltcg at withdrawal" and "tax rate on annual investment returns after contribution" as the two always seem the same in your original spreadsheet (and mine too).
Title: Re: Case Study Spreadsheet: Using '401k or taxable' to decide bonds v stocks in ira
Post by: MDM on June 05, 2016, 06:13:02 PM
I think you're right. But then you're gonna need to change your equation, cause that part I left from you. :)
Guess I fixed it already, as my local version has "0+sw" etc. in the first and third line, with "0+sc" etc. in the second. :)

Quote
You actually set sc equal to sw (probably as a short cut)...
Actually sw equal to sc, but yes it's another heuristic.

Quote
...in your spreadsheet, and I just left it that way for simplicity.   To be frank, I'm confused about the difference between "tax rate on ltcg at withdrawal" and "tax rate on annual investment returns after contribution" as the two always seem the same in your original spreadsheet (and mine too).
Under current federal tax law those are the same.  I don't even pretend to know whether that is true for all state income taxes, and the federal law could change....
Title: Re: Case Study Spreadsheet updates
Post by: jmk on June 05, 2016, 08:20:13 PM
And assuming my modifications are correct, here is an odd finding. At 15% tax rate (which I get down to with a 401k) and 15% in retirement, it doesn't matter much what I do:
At stocks=6% for 20 years, Trad=$26619, Roth=$26619,  Taxable $26176.  The power of compounding doesn't seem to amount to much.

And, frankly, it doesn't matter much where I put my bonds or my stocks:
Code: [Select]
$28132 stocks tax bonds ira
$27807 stocks roth bonds tax
$28132 stocks tax bonds roth
$27807 stocks ira bonds t
$28357 stocks r bonds ira

So I might as well, just keep all my buckets with the 50/50 asset allocation rather than try to get fancy with optimal asset location:
simply keeping everything at 50/50 is a nice tax-diversified $27,969.

Things change rapidly if my current tax goes up or my after-retirement tax goes up to 25%.  The latter is the most probably, as I have a small $13000 pension that will kick in at age 62, and my wife and I each will get $15-23k of SS.  So Roth is probably the safest.

j
Title: Re: Case Study Spreadsheet updates
Post by: MDM on July 17, 2016, 08:15:58 PM
Version 8.06


Case Study Spreadsheet (https://drive.google.com/file/d/0Bxe0EgraZFRBT2pGYjBEbG1qYlk/edit?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on August 17, 2016, 11:18:02 AM
Version 8.07


Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on August 21, 2016, 09:35:06 PM
Version 8.08


Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: handsnhearts on September 12, 2016, 10:19:05 PM
Thank you so much for this.  I am figuring out how to use it now. 
Something seems to be off with Box B7 (dependent FSA) as I can't put an equation into it.  (Just a simple equation because I get paid 2x/mo so I want to put =$$*2)  It keeps responding with 1901/7...

Thanks for the hard work on this.  I don't pretend to understand it all, but at least it is a start.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on September 12, 2016, 10:35:35 PM
Something seems to be off with Box B7 (dependent FSA) as I can't put an equation into it.  (Just a simple equation because I get paid 2x/mo so I want to put =$$*2)  It keeps responding with 1901/7...
Do you get the correct value in D7 (the annual amount)?

It appears B7 may have received a "date" format in your copy.  Can you change the cell format back to "currency"?
Title: Re: Case Study Spreadsheet updates
Post by: handsnhearts on September 12, 2016, 10:42:38 PM
Perfect!  That worked beautifully!

Now only if I could understand my spending as easily... sigh...
Title: Re: Case Study Spreadsheet updates
Post by: MDM on October 11, 2016, 10:07:59 PM
Version 8.09


Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on October 26, 2016, 07:38:15 PM
Version 8.10


Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on November 23, 2016, 09:24:17 PM
Version 8.11
Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 08, 2017, 02:33:46 PM
Version 8.12
Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on March 08, 2017, 11:36:49 PM
Version 8.13
Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)

ETA: Attached version 8.12 in case a 2016 retrospective is useful.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on March 16, 2017, 07:28:56 PM
Version 8.14
Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on March 30, 2017, 06:47:10 PM
Version 8.15
Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: 2Birds1Stone on April 03, 2017, 03:58:34 AM
MDM, thank you for your contributions to these forums. They are appreciated!
Title: Re: Case Study Spreadsheet updates
Post by: MDM on May 12, 2017, 04:55:36 PM
Version 8.16
Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: nickybecky1 on May 17, 2017, 12:55:08 PM
Hi MDM!

Thank you so much for the amazing case study spreadsheet!! My husband and I have used an older version of the spreadsheet for awhile and I just stumbled across the "updates" thread and downloaded the latest version. It's so great!! I had an old spreadsheet I'd used to compare HDHP options at my employer to the others, but yours takes into account more tax savings than I'd been able to calculate and the graph is SO helpful!
Title: Re: Case Study Spreadsheet updates
Post by: MDM on May 17, 2017, 04:08:02 PM
Hi MDM!

Thank you so much for the amazing case study spreadsheet!! My husband and I have used an older version of the spreadsheet for awhile and I just stumbled across the "updates" thread and downloaded the latest version. It's so great!! I had an old spreadsheet I'd used to compare HDHP options at my employer to the others, but yours takes into account more tax savings than I'd been able to calculate and the graph is SO helpful!
You're very welcome.

If there are any other features that would be useful, let me know.  No guarantees - e.g., I'm not likely to code every state tax algorithm ; - but if it would be broadly useful.... :)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on June 03, 2017, 02:26:18 PM
Version 9.0
Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on June 07, 2017, 11:04:57 PM
Version 9.01
Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on September 15, 2017, 11:34:19 PM
Version 9.02
Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on September 28, 2017, 01:14:02 AM
Version 9.03
Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on October 14, 2017, 12:14:56 AM
Version 9.04
Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on October 24, 2017, 11:48:11 PM
Version 9.05
The beta testing group thinks it's good to go, but there were many changes.  Thus a reminder, caveat user, seems appropriate. ;)

Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on October 31, 2017, 02:52:48 AM
Version 9.06
Federal tax calculations should remain good for a wide (but not all-inclusive) variety of cases.

State calculations should be good for "simple" cases.  State Income Tax calculations - Crowdsourcing request (https://forum.mrmoneymustache.com/taxes/state-income-tax-calculations-crowdsourcing-request/new/#new) remains available for suggestions.

Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on November 19, 2017, 10:10:46 PM
Version 9.07

Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on November 30, 2017, 03:20:38 PM
Version 9.08

Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 04, 2017, 01:03:37 AM
Version 9.09

Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 14, 2017, 11:41:48 PM
Version 9.10

Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 23, 2017, 08:02:35 AM
Version 9.11

Intent is for this to be the last version for 2017 tax law. 

Federal 2018 changes will be released "soon."

State 2018 updates will come when the 2018 version of https://taxfoundation.org/state-individual-income-tax-rates-brackets-2017/ (or similar reference) is published.

Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: KindaichiShota on December 24, 2017, 06:51:31 PM
Ver 9.11 works great :O
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 27, 2017, 08:24:20 AM
Version 10.00

AFAIK this incorporates all the 2018 tax law changes that affect items already handled in previous versions, plus a few new items.

These items include
- brackets, rates, and standard deductions
- dependent care, education, saver's, earned income, and child tax credits
- Alternative Minimum, Net Investment Income, Social Security, and Medicare taxes
- Medicare Part B premiums based on IRMAA
- SALT $10K limit for itemized deductions; no miscellaneous itemized deductions
 
As with any major software update, caveat user.  Reports of errors, omissions, etc. appreciated.

Most recent (2018 or later) version: Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)

The final 2017 version (9.11) is here: https://drive.google.com/file/d/1Kxk0_A1gEzg9BB8OFUZeXl_DqitYQIa2/view?usp=sharing
Title: Re: Case Study Spreadsheet updates
Post by: Gin1984 on December 27, 2017, 02:37:11 PM
How do you specific a state in the excel doc?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 27, 2017, 06:46:02 PM
How do you specific a state in the excel doc?
Enter state postal abbreviation in cell H32 (on the 'Calculations' tab).
Title: Re: Case Study Spreadsheet updates
Post by: nolesrule on December 28, 2017, 08:12:41 AM
There is a bug in the Itemized Deduction calculation. Cell G38 should contain the formula "=SUM(G31,MIN(10000,SUM(G32:G34)),G35:G37)".

Right now "G35:G37" is inside the MIN() function, which is not correct. So effectively the current calculation takes the minimum of $10k, SALT or Charity+Medical and adds it to Mortgage Interest.

Also, I would like to request the addition of a tax line for calculating Additional Medicare Tax due with the 1040 return based on Form 8959.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 28, 2017, 08:50:13 AM
Version 10.01
Note that more restrictive limits on charitable contributions, such as the 30% of AGI on appreciated stock, are not included.


Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: doneby35 on December 28, 2017, 09:30:37 AM
This is one of the best spreadsheets I've seen!
Does the HSA employer match go in B19 "employer match" along with the 401k employer match, or do you just put the total (contributions + employer match) in "employer-sponsored HSA"?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 28, 2017, 10:12:52 AM
This is one of the best spreadsheets I've seen!
Does the HSA employer match go in B19 "employer match" along with the 401k employer match, or do you just put the total (contributions + employer match) in "employer-sponsored HSA"?
Either can work.  I think "employer match" is more straightforward, as one would have to add the HSA match to "gross salary/wages" if that amount goes into "employer-sponsored HSA".

Doing it the latter way might be slightly more accurate in terms of "time to FI" calculations, but the uncertainty in those is so large anyway that I'd stick with the simpler approach.
Title: Re: Case Study Spreadsheet updates
Post by: nickybecky1 on December 28, 2017, 11:08:40 AM
Is there a place that makes sense to input an estimated annual bonus, or is it best to just amortize that into gross wages if you want to include it in calculations?

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

Thanks for such a great spreadsheet - I hadn't updated in awhile since a lot of the changes were small enough it wasn't worth re-entering everything for us, but I'm now loving the separated earners and ability to do different paycheck frequencies.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 28, 2017, 11:23:43 AM
Is there a place that makes sense to input an estimated annual bonus, or is it best to just amortize that into gross wages if you want to include it in calculations?

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

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

Yes, allowing different income frequencies is one of those "why didn't I do this a long time ago?" things.  Haven't decided whether to do the same for expenses....
Title: Re: Case Study Spreadsheet updates
Post by: nickybecky1 on December 28, 2017, 02:16:21 PM
In the summary section on the calculations sheet, it looks like cells B150-152 are leaving out the 2nd earner's information. Am I reading that correctly or am I misunderstanding how the sheet should be working?

ETA: Of course - they're right in the next column - exactly where I'd look for them if I weren't an idiot.
Title: Re: Case Study Spreadsheet updates
Post by: jsa307 on December 29, 2017, 11:24:25 AM
MDM, thanks for all your work on this great spreadsheet!

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

If someone is, and can provide either (or both) of
- a succint description of how to use the existing format, and/or
- a few minor tweaks to the existing format in order to accommodate S-corp filing,
there is a reasonable chance those will be included.
Title: Re: Case Study Spreadsheet updates
Post by: nolesrule on December 29, 2017, 08:45:42 PM
S-corp income is just taxable ordinary income. However it's not clear what calculations may change things with the pass-through adjustments in the new tax law. For now either include it in D40 or add another line for it.
Title: Re: Case Study Spreadsheet updates
Post by: jsa307 on December 30, 2017, 07:46:03 AM
thanks guys. the only relevant distinctions for s-corps and the spreadsheet are
1) income gets split between w2 salary and distributions. you pay both sides (employer/employee) of FICA tax on the w2 part (except for the .9% surtax on the employer part), and no FICA taxes on the distributions. I was handling this by entering the w2 info into the appropriate boxes, the distributions into "sched c net profit", and then calculating my employer share of FICA on the w2 and overriding that number into the self employment tax box. i think that works?

2) business deductions are also taken out before any tax implications are considered -- it seemed like entering those into "rental real expenses" would do the trick?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 30, 2017, 08:06:33 AM
thanks guys. the only relevant distinctions for s-corps and the spreadsheet are
1) income gets split between w2 salary and distributions. you pay both sides (employer/employee) of FICA tax on the w2 part (except for the .9% surtax on the employer part)
Seems putting this into the "sched c net profit" would work.

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

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

Quote
2) business deductions are also taken out before any tax implications are considered -- it seemed like entering those into "rental real expenses" would do the trick?
Not sure what "taken out" means here.  If the W-2 salary and distributions come after business deductions are subtracted, then entering them isn't needed at all, etc.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 30, 2017, 05:16:55 PM
Version 10.02
Title: Re: Case Study Spreadsheet updates
Post by: Teachstache on January 01, 2018, 07:58:47 AM
I am trying my 5 digit zip code in H32 and it says N/A. Do I need my 9 digit zip code instead? I can't get my state income tax calculated without this part.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 01, 2018, 08:08:10 AM
I am trying my 5 digit zip code in H32 and it says N/A. Do I need my 9 digit zip code instead? I can't get my state income tax calculated without this part.
Try the two character state postal abbreviation.
Title: Re: Case Study Spreadsheet updates
Post by: Teachstache on January 01, 2018, 09:00:08 AM
I am trying my 5 digit zip code in H32 and it says N/A. Do I need my 9 digit zip code instead? I can't get my state income tax calculated without this part.
Try the two character state postal abbreviation.

It worked, thanks!

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

Thanks for the clarification.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 01, 2018, 09:13:15 AM
Question about the 401k & 457 amounts: are eligible employees still able to contribute $18,500 to both a 401k and a 457? I thought so, but the spreadsheet indicates that I may be exceeding allowable maximums in both.
(https://s13.postimg.cc/9gu46c3yv/screenshot_259.png)
leads to
(https://s13.postimg.cc/lipi0ifs7/screenshot_260.png)
in the latest version I have.

Does the same input give you different results?
Title: Re: Case Study Spreadsheet updates
Post by: Teachstache on January 01, 2018, 09:25:00 AM
Ah, just checked & I am using the version just prior to the latest version. So, I'll need to use the latest version. Thanks for clarifying!
Title: Re: Case Study Spreadsheet updates
Post by: nolesrule on January 01, 2018, 11:29:14 AM
thanks guys. the only relevant distinctions for s-corps and the spreadsheet are
1) income gets split between w2 salary and distributions. you pay both sides (employer/employee) of FICA tax on the w2 part (except for the .9% surtax on the employer part), and no FICA taxes on the distributions. I was handling this by entering the w2 info into the appropriate boxes, the distributions into "sched c net profit", and then calculating my employer share of FICA on the w2 and overriding that number into the self employment tax box. i think that works?

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

For an S-Corp, FICA taxes employer-side taxes are a straight-up business expense. So you should just use the Paycheck section for your W-2 wages and D40 for the net business  income from the 1120S / K-1.
Title: Re: Case Study Spreadsheet updates
Post by: Gin1984 on January 05, 2018, 09:17:54 PM
Just a heads up, the 529 deduction is not coming off my state part, so you may have to do that math or override that part. 
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 05, 2018, 09:32:11 PM
Just a heads up, the 529 deduction is not coming off my state part, so you may have to do that math or override that part.
Good point.  With the variety in State Tax Deductions for 529 Contributions (http://www.finaid.org/savings/state529deductions.phtml), 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 (https://forum.mrmoneymustache.com/taxes/state-income-tax-calculations-crowdsourcing-request/).
Title: Re: Case Study Spreadsheet updates
Post by: nolesrule on January 06, 2018, 08:59:39 AM
We need to work on the Medicare calculations. Your calculations only work correctly for a single earner with a single W-2 income source. MFJ calculation is incorrect, and I don't see any accounting for SE income and the Additional Medicare Tax

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

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

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

The Additional Medicare Tax form is designed to calculate the correct Additional Medicare Tax liability (since it can differ from what is withheld) and reconcile with medicare taxes withheld, properly adjusting the tax liability up or down.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 06, 2018, 09:29:01 AM
The Additional Medicare Tax form is designed to calculate the correct Additional Medicare Tax liability....
Excellent point!  And it happens to coincide with the version under development - see the attachment to https://forum.mrmoneymustache.com/taxes/child-care-credit-vs-dependent-care-fsa/msg1842186/#msg1842186, which (I think) addresses both this issue and those raised by FIRE_Wannabe and Gin1984.

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

If no errors in the medicare and child care calculations are noted, after a while (days? hours?) I'll take the development copy and upgrade that to a released version.
Title: Re: Case Study Spreadsheet updates
Post by: nolesrule on January 06, 2018, 10:27:14 AM
The Additional Medicare Tax form is designed to calculate the correct Additional Medicare Tax liability....
Excellent point!  And it happens to coincide with the version under development - see the attachment to https://forum.mrmoneymustache.com/taxes/child-care-credit-vs-dependent-care-fsa/msg1842186/#msg1842186, which (I think) addresses both this issue and those raised by FIRE_Wannabe and Gin1984.

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

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


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

I've thought about helping out with your crowdsourcing for NJ taxes, but it treats so many different items differently from the way Federal and/orother states handle them that I'm not sure you really want to tackle it.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 06, 2018, 10:39:44 AM
That sheet looks ok to me. I think if you sum up the Medicare plus the calculation you have for Additional Medicare Tax, you do get the correct Medicare tax total in aggregate. So for yearly cash flow purposes in aggregate, it's probably accurate.
Great - thanks!

Quote
I've thought about helping out with your crowdsourcing for NJ taxes, but it treats so many different items differently from the way Federal and/orother states handle them that I'm not sure you really want to tackle it.
That is probably correct.  For similar reasons, I avoided any state tax calculations (beyond a flat percentage estimate) until finding https://taxfoundation.org/state-individual-income-tax-rates-brackets-2017/ with it's state-by-state brackets.  Likelihood of implementation for any specific issue is proportional to how easy the calculation, how likely it will be a common situation, and inversely proportional to how nice the weather is outside.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 06, 2018, 12:35:20 PM
Version 10.03
Title: Re: Case Study Spreadsheet updates
Post by: nolesrule on January 06, 2018, 02:01:42 PM
The SS Wage Base for 2018 was revised at the end of November. The correct cap number is $128,400. G53 needs to be updated.

See https://www.ssa.gov/news/press/releases/2017/#11-2017-1
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 06, 2018, 02:19:07 PM
The SS Wage Base for 2018 was revised at the end of November. The correct cap number is $128,400. G53 needs to be updated.

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

Also affects SocialSecurity!C74.

Small enough effect on either current year SS withholding or future SS benefit projections that I'll wait and include these in the next official release.  One can always make the changes in a downloaded copy.
Title: Re: Case Study Spreadsheet updates
Post by: stormbard on January 08, 2018, 03:50:16 PM
Why are the fields for Roth401k/403b, ESPP/After-tax 401k, and After-tax pension contributions subtracted from the value for W-2 Box 1 to get Net paycheck before tax? Aren't these values all taken from your paycheck after all taxes have been calculated?

(https://imgur.com/nhYMzt2)

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.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 08, 2018, 04:27:58 PM
Why are the fields for Roth401k/403b, ESPP/After-tax 401k, and After-tax pension contributions subtracted from the value for W-2 Box 1 to get Net paycheck before tax? Aren't these values all taken from your paycheck after all taxes have been calculated?
Good question!

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

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

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


Title: Re: Case Study Spreadsheet updates
Post by: stormbard on January 08, 2018, 06:35:21 PM
MDM, thank you for all your work on this and for answering my questions. I think the answers fit my situation, at least it fits my current understanding of the situation and gives me enough to continue playing with the numbers and see how things play out.

One other thing I'm not seeing is a field for employer contributions to an HSA. My understanding is that their contribution does not affect my taxes now or in the future, but it should affect total net worth and time to FI.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 08, 2018, 07:17:49 PM
MDM, thank you for all your work on this and for answering my questions. I think the answers fit my situation, at least it fits my current understanding of the situation and gives me enough to continue playing with the numbers and see how things play out.
Great!  See also some of the discussion with nolesrule a couple of days ago regarding withholding.

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

Quote
One other thing I'm not seeing is a field for employer contributions to an HSA. My understanding is that their contribution does not affect my taxes now or in the future, but it should affect total net worth and time to FI.
Adding it to row 19, "Employer Match", may be the easiest and likely accurate enough way.  One could also add the amount both to Gross Salary and Employer-sponsored HSA.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 10, 2018, 12:49:02 PM
Version 10.04
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 11, 2018, 04:24:30 PM
Version 10.05
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.
Title: Re: Case Study Spreadsheet updates
Post by: nolesrule on January 13, 2018, 08:43:04 AM
The Earner #2 withholding (H62) is calculating based off the filing status rather than off the M or S (H59) in the calculator.

I'm trying to roughly estimate switching between M or S on either/or/both of our W-4s and right now that's not possible.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 13, 2018, 08:59:54 AM
As a quick fix, just copy cell G62 to H62.  That's what I did at some point, but apparently did not save the file after doing so. :(

New version coming....
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 13, 2018, 09:28:37 AM
Version 10.06
Title: Re: Case Study Spreadsheet updates
Post by: simonkkkkk on January 14, 2018, 04:45:11 AM
(http://goo.gl/gqqjFD) Nice :)
Title: Re: Case Study Spreadsheet updates
Post by: fiveoh on January 22, 2018, 06:47:31 PM
Awesome spreadsheet!

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

I did it that way and it calculated a number for "self employment tax".   Does this include the medicare/ss that I will have to pay or do I need to calculate those on my own and put them in to the SS/Medicare boxes?  Thanks.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 23, 2018, 04:44:14 AM
Awesome spreadsheet!

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

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

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

When you do your real 2017 return, you could compare those numbers to the final 2017 version, attached in this post (https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1825323/#msg1825323).  How closely the results match might indicate how well the 2018 version will predict for you.
Title: Re: Case Study Spreadsheet updates
Post by: fiveoh on January 23, 2018, 05:09:02 AM
Thanks MDM!
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 31, 2018, 11:46:46 AM
Version 11.00
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 (https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1745720/#msg1745720) 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 (https://www.bogleheads.org/forum/viewtopic.php?f=2&t=237823)).  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.
(https://s13.postimg.cc/47is4k3x3/screenshot_290.png)

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 (https://www.bogleheads.org/forum/viewtopic.php?t=97352)) 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.
Title: Re: Case Study Spreadsheet updates
Post by: hamiltop on February 12, 2018, 09:01:56 AM
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.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 12, 2018, 09:42:26 AM
Version 11.01
As always, suggestions for fixes/improvements/etc. are welcome.  It's a small beta test group here, so crowd-sourcing is good.
Title: Re: Case Study Spreadsheet updates
Post by: nolesrule on February 21, 2018, 07:13:45 AM
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
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 26, 2018, 11:20:14 AM
Version 11.02
As always, suggestions for fixes/improvements/etc. are welcome.  It's a small beta test group here, so crowd-sourcing is good.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on March 12, 2018, 06:07:37 PM
Version 11.03
As always, suggestions for fixes/improvements/etc. are welcome.  It's a small beta test group here, so crowd-sourcing is good.
Title: Re: Case Study Spreadsheet updates
Post by: Gin1984 on March 13, 2018, 04:32:50 AM
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 (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)

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"?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on March 13, 2018, 08:26:28 AM
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? (https://www.bogleheads.org/forum/viewtopic.php?f=2&t=240176)

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...?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on March 20, 2018, 01:47:30 PM
Version 11.04
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 (https://forum.mrmoneymustache.com/taxes/state-income-tax-calculations-crowdsourcing-request/).

Most recent version: Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)

The last 2017 version (9.11) is attached to post https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1825323/#msg1825323.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on April 01, 2018, 06:37:25 PM
Version 11.05
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 (https://www.irs.gov/pub/irs-pdf/p590b.pdf).  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 (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)

The last 2017 version (9.11) is attached to post https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1825323/#msg1825323.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on April 09, 2018, 07:19:59 PM
Version 11.06

Most recent version: Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)

The last 2017 version (9.11) is attached to post https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1825323/#msg1825323.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on April 14, 2018, 03:38:46 PM
Version 11.07

Most recent version: Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)

The last 2017 version (9.11) is attached to post https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1825323/#msg1825323.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on April 21, 2018, 07:53:30 PM
Version 11.08
The estimates on the 'SocialSecurity' tab match the version 2018.1 anypia.exe numbers within $1/mo for all examples tested, e.g., https://www.ssa.gov/oact/ProgData/retirebenefit1.html.

2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on April 24, 2018, 04:17:17 PM
Version 11.09
This allows one to see the impact of various Affordable Care Act phaseouts and cliffs on overall marginal rates.

The Premium Tax Credit (PTC) for those with "simple" ACA insurance will be calculated if one enters the   
    - Enrollment Premium (aka the cost if one would receive no Premium tax credit) in cell B113.
    - Advance Premium Tax Credit in cell B114.  Enter this as a negative number.
    - Second Lowest Cost Silver Plan (SLCSP) cost for the year in cell AE99.

At this time, the calculation does not explicitly consider
    - situations not the same for all 12 months (taking monthly averages might or might not be accurate)
    - self-employment health insurance requiring iterative calculations to determine PTC and Form 1040 line 29 amounts.

2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on May 02, 2018, 10:50:33 AM
Version 11.10

2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on May 30, 2018, 05:26:18 PM
Version 11.11

2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on June 27, 2018, 05:48:22 PM
Version 11.12
Please use QBI Pass-through deduction in the case study spreadsheet (https://forum.mrmoneymustache.com/taxes/qbi-pass-through-deduction-in-the-case-study-spreadsheet/) for suggestions, corrections, and comments on the Section 199A calculations.  Although, any such replies made here won't be ignored. ;)

2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: HudsonMK on July 07, 2018, 07:10:00 AM
I searched a bit in the forums and on the spreadsheet but could not find it myself. Where does one just put their existing savings on the spreadsheet? I expected to see it near A172, but the accompanying cell has no calculation included. I am using a google sheets version of the spreadsheet, so I thought i'd double check if this was correct or not.  Edit: Oh, I see the calculations tab is primarily for tax calculations with some convenient calculations. I misunderstood that when I first asked this question, but I'll leave this post up in hopes someone can point me to other portions of the spreadsheet properly. Thank-you!
Title: Re: Case Study Spreadsheet updates
Post by: MDM on July 07, 2018, 01:10:14 PM
Where does one just put their existing savings on the spreadsheet? I expected to see it near A172....
Correct!

Specifically, in B173:B176, depending on what type of accounts one has.

It's probably best to ignore emergency funds held in savings accounts, as those pretty much just sit there and (at best) keep up with inflation.

Using Excel definitely works best.  Based on the lack of responses to https://forum.mrmoneymustache.com/ask-a-mustachian/any-google-sheets-knowledge/ and https://forum.mrmoneymustache.com/ask-a-mustachian/any-libreoffice-calc-knowledge-86335/, it seems Excel is just better.

Title: Re: Case Study Spreadsheet updates
Post by: MDM on July 20, 2018, 06:36:01 PM
Version 11.13
The non-COLA pension calculation uses input values for nominal return, inflation, and life expectancy to determine the first year's spending that, increased each year by inflation, will exactly deplete at the end of life a fixed annual pension invested at the given nominal return.

See this post in Pension with no COLA. How much to spend? (https://forum.mrmoneymustache.com/welcome-to-the-forum/pension-with-no-cola-how-much-to-spend/msg2071538/#msg2071538) for some math background.

2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: jpdx on July 24, 2018, 09:35:37 PM
Is there a way to migrate entries from an older version to the latest version?

Where should we enter partnership income?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on July 24, 2018, 10:05:58 PM
Is there a way to migrate entries from an older version to the latest version?
Copy & Paste?  In many (but not all) cases, input cells remain the same from one version to the next.

Quote
Where should we enter partnership income?
Don't know offhand.  Where does it go on Form 1040?

ETA: Perhaps row 33 ("Rental income") or row 40 ("Other taxable income")?  Would either of those work in your situation?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on July 25, 2018, 07:09:45 PM
Version 11.14

2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: Paul der Krake on July 29, 2018, 12:33:06 PM
I'm trying to model various scenarios where Earner 1 is self-employed and Earner 2 has a W-2.

I'm unsure whether the self-employment 401(k) deduction for Earner 1 should go in box B11, because I'm using the box B30 to show self-employment income (and get the calculation for deductible SE tax).

Am I doing this right? For simplicity, let's ignore that employer SE 401(k) contributions are a thing and assume everything comes out of the employee side of things.

(https://i.imgur.com/DZq7E33.png)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on July 29, 2018, 12:56:40 PM
I'm trying to model various scenarios where Earner 1 is self-employed and Earner 2 has a W-2.

I'm unsure whether the self-employment 401(k) deduction for Earner 1 should go in box B11, because I'm using the box B30 to show self-employment income (and get the calculation for deductible SE tax).

Am I doing this right? For simplicity, let's ignore that employer SE 401(k) contributions are a thing and assume everything comes out of the employee side of things.
Ideally it would go on a currently-not-present line in the "Subtractions for AGI" section, similar to line 28 of Form 1040 (https://www.irs.gov/pub/irs-pdf/f1040.pdf).

Absent that, it seems where you have it is fine.  Putting it there would not be fine if the Earned Income Credit (EIC) is a factor, but at $51K AGI the EIC is $0.

You could also add it to the tIRA box if you want it in the same general area it would appear on the 1040 form.  That would interact correctly with EIC calculations if needed, at the cost of "looking strange" for a tIRA number. ;)
Title: Re: Case Study Spreadsheet updates
Post by: jpdx on July 31, 2018, 05:02:48 PM
@MDM, partnership income is reported on Schedule E and goes on 1040 line 17 (2017 version). Unlike rental income, it's non-passive income so it is subject to SE tax. So...put it with "Other taxable income"?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on July 31, 2018, 05:15:00 PM
@MDM, partnership income is reported on Schedule E and goes on 1040 line 17 (2017 version). Unlike rental income, it's non-passive income so it is subject to SE tax. So...put it with "Other taxable income"?
Ok, if it is subject to SE tax then it would have to go in row 30, Schedule C net profit.  This would be similar to what happens in line 2 of Schedule SE (https://www.irs.gov/pub/irs-pdf/f1040sse.pdf).

Does that work for you?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on August 15, 2018, 12:24:12 PM
Version 11.15

More on the new tab
  This addresses the situation of being in a low bracket (12% or less) this year and having two possibilities:
  1) Tax Gain Harvest: incur Long Term Capital gains but pay $0 federal tax
  2) Convert traditional to Roth at 12% federal, with the expectation of being in a higher tax bracket after retirement
      It generates a table listing, by years from the current year, whether TGH or t->R conversion is favored.

  In general, the longer one can wait, the more the t->R conversion is favored over TGH - and in many cases it doesn't take many years at all. 
  Of course, this is based on the assumptions in the model, and that the model is coded correctly - always a consideration in a new release. ;)

2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on September 09, 2018, 03:50:45 PM
Version 11.16

2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on September 16, 2018, 10:23:49 PM
Version 11.17

Note that when saving the CSS, if one is not on the 'Calculations' tab, the following scary-looking message may appear:
(https://i.postimg.cc/Y0h80wch/screenshot_411.png)

For versions 20.09 and later, there are 7 instead of 6 instances.

It comes from the use of drop-down lists to enter paycheck frequency, etc., and is actually not a problem at all.  Microsoft has confirmed that the warning is incorrect, there is no loss of functionality. (https://support.microsoft.com/en-us/help/2757267/compatibility-checker-warning-for-data-validation) 

Thanks to the Bogleheads forum finding the error in my federal tax return - Bogleheads.org (https://www.bogleheads.org/forum/viewtopic.php?f=11&t=257517&p=4111949#p4111558) for bringing this up.
Title: Re: Case Study Spreadsheet updates
Post by: BTDretire on September 20, 2018, 08:18:19 PM
On your next update, can you add a Cells for Earner # 1 and Earner #2 for SEPs under 'Subtractions for AGI'?
I'm assuming that's the right place.
Also, a place for healthcare insurance premium for those that are self employed? Again, I'm assuming under
'Subtractions for AGI'
                                     Thanks

What does the abbreviation in Cell A49, SL int (Approx)  stand for?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on September 20, 2018, 09:32:37 PM
What does the abbreviation in Cell A49, SL int (Approx)  stand for?
Student Loan interest (Approximate calculation).  It is calculated based on student loan balance and interest amounts given in rows 132-136.

Quote
On your next update, can you add a Cells for Earner # 1 and Earner #2 for SEPs under 'Subtractions for AGI'?
I'm assuming that's the right place.
That seems doable.  Similar to 401k and IRA contributions, the program would not enforce contribution limits because there are too many possible scenarios.  That look OK to you?

Quote
Also, a place for healthcare insurance premium for those that are self employed? Again, I'm assuming under
'Subtractions for AGI'
I suppose the same caveat user philosophy could be used here, but I've been reluctant to include this due to the difficulty some could have determining the actual limits.  See brief comments in these posts: What comes after the ACA? (https://forum.mrmoneymustache.com/welcome-to-the-forum/what-comes-after-the-aca/msg2018185/#msg2018185) and Case Study Spreadsheet updates (https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1983651/#msg1983651).

If you (or anyone) has a spreadsheet version of any method for Self-Employed Health Insurance Deduction and PTC (https://taxmap.irs.gov/taxmap/pubs/p974-010.htm)...?
Title: Re: Case Study Spreadsheet updates
Post by: BTDretire on September 21, 2018, 08:01:28 AM
What does the abbreviation in Cell A49, SL int (Approx)  stand for?
Student Loan interest (Approximate calculation).  It is calculated based on student loan balance and interest amounts given in rows 132-136.

Quote
On your next update, can you add a Cells for Earner # 1 and Earner #2 for SEPs under 'Subtractions for AGI'?
I'm assuming that's the right place.
That seems doable.  Similar to 401k and IRA contributions, the program would not enforce contribution limits because there are too many possible scenarios.  That look OK to you?

Yes that would be fine, I just need separation because when I go back and see way to much under tIRA contribution, I then have to recall, what the heck did I do there?
 As it stands I added my SEP and tIRA together and put it in under Traditional IRA, row 48. However as you would expect Cell L42 puts a Question about limits, and that's OK and well done.

Quote
Also, a place for healthcare insurance premium for those that are self employed? Again, I'm assuming under
'Subtractions for AGI'
Quote
I suppose the same caveat user philosophy could be used here, but I've been reluctant to include this due to the difficulty some could have determining the actual limits.  See brief comments in these posts: What comes after the ACA? (https://forum.mrmoneymustache.com/welcome-to-the-forum/what-comes-after-the-aca/msg2018185/#msg2018185) and Case Study Spreadsheet updates (https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1983651/#msg1983651).

If you (or anyone) has a spreadsheet version of any method for Self-Employed Health Insurance Deduction and PTC (https://taxmap.irs.gov/taxmap/pubs/p974-010.htm)...?

  I'll need to read those links, I didn't know about limits, I doubt I'm close, but for others...
You could also add, "you must check your contribution limit" under the Columns
K,L and M and the low 40s rows, regarding that deduction.
 As it stands, I put it under Alimony.

  Thank you for the CSS, I'm finding it very useful. I find something new everytime I use it. So many details :-)
 
Title: Re: Case Study Spreadsheet updates
Post by: BTDretire on September 21, 2018, 10:02:31 AM
I note I have $17,355 in Cell D28, 'Form 1040 Line 13'
It seems like it is calculated, but I can't figure out from what.
 Can you help?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on September 21, 2018, 10:29:03 AM
I note I have $17,355 in Cell D28, 'Form 1040 Line 13'
It seems like it is calculated, but I can't figure out from what.
 Can you help?
Here's one approach.

First, "Unprotect" the sheet by clicking that icon (bottom right in the screen shot below) after clicking the Review tab in the Excel main menu:
(https://i.postimg.cc/k4KYdScM/screenshot_413.png)

Then select (e.g., click on) cell D28 (or whatever the cell of interest).

To find the cells that directly affect the cell of interest, click the Trace Precedents icon (bottom right in the screen shot below) after clicking the Formulas tab in the Excel main menu:
(https://i.postimg.cc/g2w2Hcsq/screenshot_414.png)

In this case, you should see the following blue arrows, showing that cell D28 comes from Schedule D calculations.
(https://i.postimg.cc/d1z2JQ6q/screenshot_415.png)

Does that help?

To avoid inadvertently changing a calculation, you can toggle the sheet status back to "Protected" using the Review tab shown above.  Just accept the suggested settings (including no password) when doing so.  Of course, if you want to change a calculation, Unprotect the sheet and have at it!
Title: Re: Case Study Spreadsheet updates
Post by: BTDretire on September 21, 2018, 10:41:39 AM
Cool!
 Thanks.
 Oh thought was going to be easy.
How do I get to the -Review View Developer Add-ins Team, section?
All I see is File Edit View Insert Format Tools Data.
 Sorry.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on September 21, 2018, 11:12:08 AM
Cool!
 Thanks.
 Oh thought was going to be easy.
How do I get to the -Review View Developer Add-ins Team, section?
All I see is File Edit View Insert Format Tools Data.
 Sorry.
OK, that means you have a different version of Excel (https://www.asap-utilities.com/download-help-excel-version.php). 

Try
Tools>Protection>Unprotect Sheet (instead of the Review tab), and
Tools>Formula Auditing>Trace Precedents (instead of Formulas>Trace Precedents)

E.g., see the section starting at ~1:35 in https://www.youtube.com/watch?v=k580CiR5lfY.
Title: Re: Case Study Spreadsheet updates
Post by: BTDretire on September 21, 2018, 11:34:05 AM
Oh, ya, I'm using Open Office.
For the CSS, it might be worth me splurging for a version of Excel.
 I don't want to waste your time on my OpenOffice problem, I'll go looking for the solution.

               Thanks,
Title: Re: Case Study Spreadsheet updates
Post by: fiveoh on September 25, 2018, 07:35:54 AM
Any clarification on the CTC and whether it will be like the optimistic or unoptimistic calculations in the spreadsheet? 
Title: Re: Case Study Spreadsheet updates
Post by: MDM on September 25, 2018, 08:27:37 AM
Any clarification on the CTC and whether it will be like the optimistic or unoptimistic calculations in the spreadsheet?
I'm still optimistic, but so far the only applicable Draft Tax Forms (https://apps.irs.gov/app/picklist/list/draftTaxForms.html?value=8812&criteria=formNumber&submitSearch=Find) that have been published require numbers from as yet unpublished forms.
Title: Re: Case Study Spreadsheet updates
Post by: fiveoh on September 25, 2018, 08:38:01 AM
Any clarification on the CTC and whether it will be like the optimistic or unoptimistic calculations in the spreadsheet?
I'm still optimistic, but so far the only applicable Draft Tax Forms (https://apps.irs.gov/app/picklist/list/draftTaxForms.html?value=8812&criteria=formNumber&submitSearch=Find) that have been published require numbers from as yet unpublished forms.

Thanks, makes a huge difference for my numbers/planning.  I wish they would clarify already!
Title: Re: Case Study Spreadsheet updates
Post by: MDM on September 27, 2018, 10:48:27 PM
Any clarification on the CTC and whether it will be like the optimistic or unoptimistic calculations in the spreadsheet?
I'm still optimistic, but so far the only applicable Draft Tax Forms (https://apps.irs.gov/app/picklist/list/draftTaxForms.html?value=8812&criteria=formNumber&submitSearch=Find) that have been published require numbers from as yet unpublished forms.

Thanks, makes a huge difference for my numbers/planning.  I wish they would clarify already!
https://www.irs.gov/pub/irs-dft/f1040--dft.pdf is now available.  In short, optimism wins!
Title: Re: Case Study Spreadsheet updates
Post by: MDM on October 07, 2018, 06:38:42 PM
Quote
Also, a place for healthcare insurance premium for those that are self employed? Again, I'm assuming under
'Subtractions for AGI'
Quote
I suppose the same caveat user philosophy could be used here, but I've been reluctant to include this due to the difficulty some could have determining the actual limits.  See brief comments in these posts: What comes after the ACA? (https://forum.mrmoneymustache.com/welcome-to-the-forum/what-comes-after-the-aca/msg2018185/#msg2018185) and Case Study Spreadsheet updates (https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1983651/#msg1983651).

If you (or anyone) has a spreadsheet version of any method for Self-Employed Health Insurance Deduction and PTC (https://taxmap.irs.gov/taxmap/pubs/p974-010.htm)...?

  I'll need to read those links, I didn't know about limits, I doubt I'm close, but for others...
You could also add, "you must check your contribution limit" under the Columns
K,L and M and the low 40s rows, regarding that deduction.
 As it stands, I put it under Alimony.

  Thank you for the CSS, I'm finding it very useful. I find something new everytime I use it. So many details :-)
 

For various reasons, it seems a full treatment of the interplay between self-employed health insurance and the ACA is more than I'll tackle anytime soon for the CSS.  Some relevant articles if one is interested:
https://obamacareguide.wordpress.com/2014/07/25/self-employed-health-insurance-deduction-the-iterative-calculation/
http://time.com/money/5237795/irs-tax-problem-obamacare-subsidy/

And a calculator (see second article above) that apparently has heuristics to overcome non-convergence problems that can occur with the iterative process:
https://cims.nyu.edu/~ferguson/Calculator%20SE%20ACA.html

But for simpler cases, some approximate solution is possible - stay tuned....

Title: Re: Case Study Spreadsheet updates
Post by: wjmano on October 10, 2018, 03:35:31 PM
I'm a new user of this spreadsheet trying to forecast ACA subsidy impact for 2018.  The latest spreadsheet seems to be based on IRS data applicable to 2019.  Is there possibly an older version of the spreadsheet that may include IRS data for 2018?  The IRS data I'm interested in changing to 2018 info is in range AD110:AH118 on the Calculations tab.  Thanks


Title: Re: Case Study Spreadsheet updates
Post by: MDM on October 10, 2018, 04:04:34 PM
I'm a new user of this spreadsheet trying to forecast ACA subsidy impact for 2018.  The latest spreadsheet seems to be based on IRS data applicable to 2019.  Is there possibly an older version of the spreadsheet that may include IRS data for 2018?  The IRS data I'm interested in changing to 2018 info is in range AD110:AH118 on the Calculations tab.  Thanks
Good point!

The numbers were the estimates available in early 2018 (late 2017?) for what the 2018 Form 8962 would use.  Probably the best numbers to use now are the ones on page 7 of https://www.irs.gov/pub/irs-dft/i8962--dft.pdf, published ~5 days ago.  I'll update those in the next version, but meanwhile you could simply unprotect the worksheet and enter the correct value (family size and home state) for your situation.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on November 05, 2018, 08:16:57 PM
Version 11.18
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on January 11, 2019, 07:47:18 PM
Version 11.19

The next planned update will be for 2019 tax changes (brackets, limits, etc.).

If, however, anyone would like to compare the 2018 results from this spreadsheet to results from commercial software, advice on any discrepancies is appreciated and may be helpful for future revisions. 

Note that under $100K taxable income the IRS uses table lookups but the CSS uses the tax bracket formulas, so differences of a few dollars may occur.
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on January 12, 2019, 01:17:52 PM
Version 12.00

This does not include any revisions to 2019 state tax laws.  It usually takes a few months for the Tax Foundation (https://taxfoundation.org/) to publish its handy compilation.

Feedback on any significant differences between 2018 CSS results and 2018 commercial package (TurboTax, TaxSlayer, H&RBlock, etc.) results would be appreciated.  And, of course, similar feedback on any errors/omissions introduced with the 2019 changes.

2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on January 18, 2019, 12:35:04 AM
Versions 11.20 and 12.01
Premiums possibly eligible for the Self-Employed Health Insurance (SEHI) subtraction may be entered in row 48.   
 - A limit of Schedule C net income minus 1/2 SE tax is enforced.  This will be too generous if one has multiple Schedule Cs.
 - Any premiums not eligible for the SEHI subtraction will be added to the itemized medical expense calculation.
 - Premiums entered in row 48 should not also be entered in row 114.
 - In other words, the spreadsheet will handle either the PTC or the SEHI, but not both.

2018 taxes version: 2018 Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on January 20, 2019, 02:48:24 PM
Versions 11.21 and 12.02
Per paragraph (vi) on p. 198 of https://www.irs.gov/pub/irs-drop/td-reg-107892-18.pdf,
Quote
For purposes of section 199A only, deductions such as the deductible portion of the tax on
self-employment income under section 164(f), the self-employed health insurance deduction
under section 162(l), and the deduction for contributions to qualified retirement plans
under section 404 [must be subtracted from Schedule C net income for purposes of computing QBI].

2018 taxes version: 2018 Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on January 23, 2019, 09:44:54 PM
Version 12.03

2018 taxes version: 2018 Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: Orichalcum on January 24, 2019, 01:38:54 PM
NY State Dependent Exemption is $1000, not 0. Otherwise matches H&R Block, +/-$1 due to tax tables vs formulas.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 24, 2019, 03:38:26 PM
NY State Dependent Exemption is $1000, not 0. Otherwise matches H&R Block, +/-$1 due to tax tables vs formulas.
Thanks!

In State Individual Income Tax Rates and Brackets for 2018 | Tax Foundation (https://taxfoundation.org/state-individual-income-tax-rates-brackets-2018/), the personal exemptions for NY are listed as "n.a." with this footnote:
(hh) State provides a state-defined personal exemption amount for each exemption available and/or deductible under the Internal Revenue Code. Under the new federal tax law, the personal exemption is set at $0 until 2026, but not eliminated. Because it is still available, these state-defined personal exemptions could remain in effect, though uncertainty remains in some cases.

One can change the NY state exemption amounts in row 54 of the 'State Tax' tab (after unprotecting that sheet - no password needed).  Not going to publish a new version just for that change, but it will be included whenever the next update is published. 

Is there only a dependent exemption, or do single and MFJ filers also have an exemption amount for NY?

Feedback on any other state issues is welcome, either here or, particularly for more complex issues, in State Income Tax calculations - Crowdsourcing request (https://forum.mrmoneymustache.com/taxes/state-income-tax-calculations-crowdsourcing-request/).
Title: Re: Case Study Spreadsheet updates
Post by: Joshua on January 27, 2019, 04:13:03 PM
So in Ohio we have a special tax provision that is not calculated in the sheet but has a large impact on some returns.

Ohio has a "Business income credit", basically the first $250,000 of income from businesses (sole proprietors, LLC etc) is deductible from Ohio taxes. So anything listed as "rental income" or "schedule C net profits" up to $250,000 should be removed from the state tax calculations if OH is the filing state.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 27, 2019, 05:02:36 PM
So in Ohio we have a special tax provision that is not calculated in the sheet but has a large impact on some returns.

Ohio has a "Business income credit", basically the first $250,000 of income from businesses (sole proprietors, LLC etc) is deductible from Ohio taxes. So anything listed as "rental income" or "schedule C net profits" up to $250,000 should be removed from the state tax calculations if OH is the filing state.
That looks doable.  The schedule C amount seems straightforward: Calculations!D30.

For the rental income (or this could be "schedule E income") should the amount be (all from the Calculations tab) D33, or D33-D34, or D33-D34-D35?  Or other?

If there is a loss for either schedule C or E, does Ohio ignore those and let the effect on federal AGI suffice?
Title: Re: Case Study Spreadsheet updates
Post by: Lhall on February 01, 2019, 03:21:33 PM
I think I found a glitch in your area for Form 8962, Premium Tax Credit if I'm receiving an ACA subsidy, where you omitted one of the income cliffs.  It does a nice job showing the subsidy repayment limits and amount owed if I end up with higher than estimated income, but if I go over the 400% FPL by any amount, the repayment amount on your worksheet goes to zero, whereas I believe I should then be obligated to repay the entire amount of the subsidy received, which should greatly increase my total tax owed. 

I'm trying to do an optimization looking at the balance between my ACA subsidy received, and long term capital gains received within the 0% rate.  Obviously, 0% on LTCG is good, but a big chunk of the LTCG could be wiped out if that gain pushes me over the ACA subsidy cliff.  Knowing my net earned income, it would be nice to be able to increment my LTCG and see the increasing hits I'd take on ACA subsidy repayment, to best plan what to sell in a year and pay the lowest overall tax rate. 
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 01, 2019, 03:42:58 PM
I think I found a glitch in your area for Form 8962, Premium Tax Credit if I'm receiving an ACA subsidy, where you omitted one of the income cliffs.  It does a nice job showing the subsidy repayment limits and amount owed if I end up with higher than estimated income, but if I go over the 400% FPL by any amount, the repayment amount on your worksheet goes to zero, whereas I believe I should then be obligated to repay the entire amount of the subsidy received, which should greatly increase my total tax owed. 

I'm trying to do an optimization looking at the balance between my ACA subsidy received, and long term capital gains received within the 0% rate.  Obviously, 0% on LTCG is good, but a big chunk of the LTCG could be wiped out if that gain pushes me over the ACA subsidy cliff.  Knowing my net earned income, it would be nice to be able to increment my LTCG and see the increasing hits I'd take on ACA subsidy repayment, to best plan what to sell in a year and pay the lowest overall tax rate.
Well, it used to work. ;)

What pertinent inputs (SLCSP, family size, monthly premiums, etc.) are you using?
Title: Re: Case Study Spreadsheet updates
Post by: Lhall on February 01, 2019, 04:35:33 PM
The SLCSP is 525, just me alone, premium is 528 (I pay just $3/mo net). 
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on February 01, 2019, 08:58:03 PM
Versions 11.22 and 12.04
2018 taxes version: 2018 Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: Orichalcum on February 11, 2019, 11:19:03 AM
No, there is no other exemption. However, there is a NY child tax credit of $330/child above age 4.

On another topic, a nice easy addition to this spreadsheet would be the amount of payroll taxes your employer pays on your behalf, as these increase your effective tax rate.

Social Security   =0.062*MIN(B3,$G$54/B170)
Medicare   =0.0145*B3+MAX(0,(B3-IF(G2=2,250000,200000))*0.009)
Federal Unemployment Tax   =0.06*MIN(B3,7000)
State Unemployment Tax   complicated....
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 11, 2019, 12:01:27 PM
No, there is no other exemption. However, there is a NY child tax credit of $330/child above age 4.
Thanks, good to know that we're all set on the exemption.  Probably won't add the extra input cell needed to do the NY CTC.

Quote
On another topic, a nice easy addition to this spreadsheet would be the amount of payroll taxes your employer pays on your behalf, as these increase your effective tax rate.

Social Security   =0.062*MIN(B3,$G$54/B170)
Medicare   =0.0145*B3+MAX(0,(B3-IF(G2=2,250000,200000))*0.009)
Federal Unemployment Tax   =0.06*MIN(B3,7000)
State Unemployment Tax   complicated....
As tax law currently stands, those have no effect on the employee's take home pay, so ignoring them for cash flow purposes seems best.  They are included for self-employment tax, for which a person is both employee and employer.
Title: Re: Case Study Spreadsheet updates
Post by: Paul der Krake 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.


Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on March 19, 2019, 12:57:23 PM
Versions 11.23 and 12.05
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 (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on March 20, 2019, 05:52:15 PM
Version 12.06
Thanks to 2019 State Individual Income Tax Rates and Brackets | Tax Foundation (https://taxfoundation.org/2019-state-individual-income-tax-rates-brackets) 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 (https://forum.mrmoneymustache.com/taxes/state-income-tax-calculations-crowdsourcing-request/msg1697962/#msg1697962) is a good place to note errors & omissions so they may be corrected in future versions.


2018 taxes version: 2018 Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: FireAnt on April 07, 2019, 12:44:44 PM
Doing the 2018 version.

Filing status- We're MFS, should that be marked #3 HOH?
Title: Re: Case Study Spreadsheet updates
Post by: MDM 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.
Title: Re: Case Study Spreadsheet updates
Post by: FireAnt 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.
Title: Re: Case Study Spreadsheet updates
Post by: PCOwner 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.
Title: Re: Case Study Spreadsheet updates
Post by: MDM 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 ____!
Title: Re: Case Study Spreadsheet updates
Post by: PCOwner 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 ?

Title: Re: Case Study Spreadsheet updates
Post by: MDM 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 (http://www.mrmoneymustache.com/2019/04/01/how-i-sold-this-website-for-9-million/) for the first-person account.
Title: Re: Case Study Spreadsheet updates
Post by: PCOwner 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?
Title: Re: Case Study Spreadsheet updates
Post by: MDM 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.
Title: Re: Case Study Spreadsheet updates
Post by: BTDretire 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.
Title: Re: Case Study Spreadsheet updates
Post by: MDM 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?
Title: Re: Case Study Spreadsheet updates
Post by: BTDretire 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.
Title: Re: Case Study Spreadsheet updates
Post by: MDM 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.
(https://i.postimg.cc/t4Kmx7qf/screenshot-492.png)

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?
Title: Re: Case Study Spreadsheet updates
Post by: BTDretire 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.
(https://i.postimg.cc/t4Kmx7qf/screenshot-492.png)

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)."
Title: Re: Case Study Spreadsheet updates
Post by: MDM 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.
Title: Re: Case Study Spreadsheet updates
Post by: BTDretire 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.
Title: Re: Case Study Spreadsheet updates
Post by: MDM 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...) (https://forum.mrmoneymustache.com/ask-a-mustachian/case-study-spreadsheet-club-(like-a-book-club-only-with-a-spreadsheet-)/) useful.
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on July 05, 2019, 12:34:42 PM
Version 12.07

2018 taxes version: 2018 Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on October 17, 2019, 04:08:30 AM
Version 12.08
2018 taxes version: 2018 Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)

Note:
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on November 01, 2019, 10:45:45 PM
Version 12.09
2018 taxes version: 2018 Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)

Note:
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on December 03, 2019, 04:13:49 PM
Version 12.10
2018 taxes version: 2018 Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)

Note:
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on December 03, 2019, 04:14:49 PM
Version 20.00
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 (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)

2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)

2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)

Note:
Title: Re: Case Study Spreadsheet updates
Post by: BTDretire 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.
Title: Re: Case Study Spreadsheet updates
Post by: BTDretire 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.
Title: Re: Case Study Spreadsheet updates
Post by: MDM 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 (https://forum.mrmoneymustache.com/case-studies/how-to-write-a-'case-study'-topic/) 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.
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on December 16, 2019, 10:41:15 AM
Version 20.01
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 (https://www.irs.gov/pub/irs-pdf/fw4.pdf) 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 (https://www.irs.gov/pub/irs-dft/p15t--dft.pdf) 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 (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)

2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)

2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)

Note:
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: mjb 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!
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM 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 (https://forum.mrmoneymustache.com/taxes/state-income-tax-calculations-crowdsourcing-request/msg1697962/#msg1697962) is a good place to post other state return issues. 
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on January 27, 2020, 09:46:11 PM
Version 20.02

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 (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)

2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)

2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)

Note:
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on February 07, 2020, 07:12:57 PM
Version 20.03
Thanks once again to the folks at Tax Foundation (https://taxfoundation.org/) for aggregating at least the basics for all state and D.C. income taxes. 


2018 taxes version: 2018 Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)

2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)

2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)

Note:
Title: Re: Case Study Spreadsheet updates
Post by: BTDretire on March 17, 2020, 07:25:29 AM
Do you know if the program runs successfully in Office 2000 Pro?
Or Office 2000.
Title: Re: Case Study Spreadsheet updates
Post by: MDM 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.
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on April 07, 2020, 09:59:22 PM
Version 12.11 (2019)
Version 20.04 (2020)
2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)

Note:
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on May 08, 2020, 10:38:25 PM
Version 20.05 (2020)
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 (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)

Note:
Title: Question re:employer plans
Post by: Jason95357 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.
Title: Re: Question re:employer plans
Post by: MDM 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.
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on June 12, 2020, 12:03:28 PM
Version 20.06 (2020)
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 (https://uscode.house.gov/view.xhtml?req=granuleid:USC-prelim-title26-section213&num=0&edition=prelim).

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 (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)

Note:
Title: Re: Case Study Spreadsheet updates
Post by: coderstache 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 (https://ttlc.intuit.com/community/taxes/discussion/re-does-contributing-to-my-sep-ira-affect-my-magi-for-roth-ira-contribution-limits-if-i-am-solely/01/958978/highlight/true#M345069) and The Balance SMB (https://www.thebalancesmb.com/tax-planning-with-sep-iras-3193211) 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 (https://www.irs.gov/pub/irs-pdf/p560.pdf)
Title: Re: Case Study Spreadsheet updates
Post by: MDM 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. :)
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on July 04, 2020, 12:36:08 PM
Version 20.07 (2020)
2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)

Note:
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on July 07, 2020, 10:38:06 PM
Version 20.08 (2020)
2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)

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:
Title: Re: Case Study Spreadsheet updates
Post by: BTDretire 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?
Title: Re: Case Study Spreadsheet updates
Post by: MDM 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.
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on August 25, 2020, 03:31:06 PM
Version 20.09 (2020)

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 (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)


Note:
Title: Re: Case Study Spreadsheet updates
Post by: RWTL 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.
Title: Re: Case Study Spreadsheet updates
Post by: MDM 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.
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on November 29, 2020, 08:20:55 PM
Version 20.10 (2020)
2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)

Note:
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on November 29, 2020, 08:31:41 PM
Version 21.00 (2021)
2019 taxes version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
2021 taxes version: 2021 Case Study Spreadsheet (https://drive.google.com/file/d/18uN77cP1m9ztZ5boHmqMnQ3CngkaDf6p/view?usp=sharing)

Note:
Title: Re: Case Study Spreadsheet updates
Post by: RWTL on November 30, 2020, 04:02:33 AM
Thanks for continuing to work on this.  I use this tool religiously throughout the year to track my withholdings.

It probably wouldn't apply to most people, but I also manually track my solar credit (form 5695). 

M
Title: Re: Case Study Spreadsheet updates
Post by: dandarc on December 02, 2020, 05:41:13 PM
MDM's spreadsheet is better than early release of Turbotax Online.
Title: Re: Case Study Spreadsheet updates
Post by: green2bend on December 26, 2020, 05:19:57 PM
Thanks for this great resource.

In the 2020 version (the only one I've looked at), The Earned Income Tax Credit calculation seems not to apply the investment income restriction properly. From Worksheet 1 (https://www.irs.gov/publications/p596#en_US_2019_publink1000297452) in Pub596:

Quote
5.   Enter the amount from Form 1040 or 1040-SR, line 6. If the amount on that line is a loss, enter -0-

I think this means that the G28 cell needs to change from

Quote
=IF(AND((D23+D24+B25*B163+D28+D36)<3650,OR(AND(G8>=25,G8<65),AND(H8>=25,H8<65),G6>0)),MAX(0,Z26),0)

to

Quote
=IF(AND((D23+D24+B25*B163+MAX(0,D28)+D36)<3650,OR(AND(G8>=25,G8<65),AND(H8>=25,H8<65),G6>0)),MAX(0,Z26),0)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 26, 2020, 06:25:08 PM
I think this means that the G28 cell needs to change....
Right you are!

Not only that limit, but the rental income needs to be limited similarly.  Had a thing or two ready for a new version, so I'll add this and release the new version soon.

Gotta love crowd sourcing!
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on December 26, 2020, 07:18:13 PM
Version 20.11 (2020)
Version 21.01 (2021)

202020212020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
2021 taxes version: 2021 Case Study Spreadsheet (https://drive.google.com/file/d/18uN77cP1m9ztZ5boHmqMnQ3CngkaDf6p/view?usp=sharing)

Note:
Title: Re: Case Study Spreadsheet updates
Post by: fuzzy math on January 03, 2021, 11:28:54 AM
After a few failed tries over the years, I finally got my spreadsheet working! I even got 2021 up and running so I can estimate my taxes. Its going to save me a massive headache come April 2022. Thanks MDM!
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 03, 2021, 11:42:55 AM
After a few failed tries over the years, I finally got my spreadsheet working! I even got 2021 up and running so I can estimate my taxes. Its going to save me a massive headache come April 2022. Thanks MDM!
Nice going!

Speaking of 2021, congress has once again changed the 10% of AGI floor for itemized medical expenses to 7.5% of AGI.  Doesn't seem worth a whole version update to change that, so until the next update if anyone care about this nuance then change cell 'Calculations'G39 to replace 10% with 7.5% as shown below:
=MAX(0,SUM(D95:D96)+SUM(D113:D116)+(B48*$B$164+C48*$C$164-D48)+G19-G31-7.5%*G11)
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on January 03, 2021, 08:19:28 PM
Version 20.12 (2020)
Version 21.02 (2021)

202020212020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
2021 taxes version: 2021 Case Study Spreadsheet (https://drive.google.com/file/d/18uN77cP1m9ztZ5boHmqMnQ3CngkaDf6p/view?usp=sharing)

Ah, what the heck, the changes are minimal but anyway....

Note:
Title: Re: Case Study Spreadsheet updates
Post by: nolesrule on January 17, 2021, 01:19:02 PM
The Cash donation tax credit has changed in 2021.

It is now $600 for MFJ. Also it is now a post-AGI deduction to income, so it is subtracted after AGI is calculated.
Title: Re: Case Study Spreadsheet updates
Post by: fyre4ce on January 24, 2021, 10:18:52 PM
Hi there!

I'm new to MMM. I've been active on Bogleheads for a few years, and have done some editing to the wiki. I'm working on a revision to the Traditional versus Roth wiki page (https://www.bogleheads.org/forum/viewtopic.php?f=2&t=335502). It's become clear that one of the biggest issues for readers is how to to estimate their future marginal tax rate. The wiki page lays out a good method, but it's long enough that only the most ambitious readers are making their own spreadsheets to make the calculation. We discussed adding some kind of tool, and I have my own stand-alone spreadsheet that we could polish and upload. But, given the popularity of the CSS tool, I much prefer the idea of adding this capability to CSS, and then the wiki page can point to there.

So, I took a swing at adding a new worksheet to the CSS tool, which estimates current and future marginal tax rates, and then calculates the future values for five different tax structures: tax-free (eg. HSA for qualifying expenses), Roth, pre-tax, non-deductible (eg. ND-tIRA, VA), taxable. Future marginal rates are estimated by calculating future pre-tax and taxable balances using Future Value functions, then adding in Social Security and other forms of income. It can import data from the Calculations worksheet, or accept over-riding user inputs. It handles special cases of contributing the account maximum, and also not getting the full employer match. I also added a "Social Security Spike Checker" where it runs the math in reverse, to check the case of trying to come in just under the spike in SS taxation (22.2% or 40.7%), then calculating future balances, tax due, and after-tax income in retirement to see if it's a better strategy. Overall, I think this would be a valuable toolset to add to the CSS.

Link: https://drive.google.com/file/d/1InHgvpUOmwAujWAREoV20v7PYpupJA1I/view?usp=sharing (https://drive.google.com/file/d/1InHgvpUOmwAujWAREoV20v7PYpupJA1I/view?usp=sharing)

If this addition is accepted, it would replace the "Non-ded. IRA" worksheet completely, and also the "Growth in a taxable account," "Roth vs. Traditional when contributing the maximum allowed," and "Estimating withdrawal tax rates calculators" on the "Misc. Calcs" worksheet. It would also probably be wise to adapt the "401k vs Taxable" worksheet into mine, so they could work from a common set of inputs, but I decided not to spend the time to do that until it seemed likely that my worksheet would be accepted in some form.

Two difficulties I had: The CSS has a sophisticated tax calculator I didn’t figure out how to use, so I “rolled my own” on that worksheet off to the side. I think I got it pretty close, but if it’s easier to call already existing tax functions, then that’s clearly the right move. I also wasn’t able to have a comprehensive state tax model (like the existing sheet, where you put in the state postal code and it does the rest); mine requires the user to input the state marginal rate manually. These are areas for improvement.

I added a feature where a period of early withdrawal (early retirement, pre-SS Roth conversions) can be added to the FV calculation. This presents a problem for the case of coming in under the SS spike, because it's not clear what the equivalent decision would be in that case. I coded it now to take early withdrawals in proportion to the smaller pre-tax balance, but I'm not 100% sure this is the best approach. Feedback on this would be appreciated, and it could just make sense to eliminate the early withdrawal feature altogether.

Anyway, that's my suggestion; looking forward to hearing the group's thoughts on it.

Best,
"fyre4ce"
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 25, 2021, 07:20:54 AM
Thanks for the reminders/suggestions.  nolesrule, I'm somewhat waiting to see if Congress throws any more curve balls for 2021, but the QBI-ish charitable item should be in the next update.

fyre4ce, welcome to the forum.  It is a bit different than Bogleheads, especially in the off-topic board....  Always nice to get suggestions with a potential solution included!

The CSS has developed to where it is thanks to crowd-sourcing such as this.  I'll take a look at the details.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 26, 2021, 12:56:28 PM
So, I took a swing at adding a new worksheet to the CSS tool....
Nice work!  That must have taken some effort!

If you had to pick the most important one or two or three concepts you are proposing, what would they be?
Title: Re: Case Study Spreadsheet updates
Post by: fyre4ce on January 26, 2021, 03:17:34 PM
So, I took a swing at adding a new worksheet to the CSS tool....
Nice work!  That must have taken some effort!

If you had to pick the most important one or two or three concepts you are proposing, what would they be?

Here are what I think are the most important concepts, in decreasing order of importance:

1) Powerful and usable tool to estimate future marginal tax rates. This is an input to many types of investment problems, and in my experience most people just guess. This was the motivator behind making this proposed update in the first place. It synergizes well with the tax model in the CSS, because it can do a direct lookup of the current marginal rate for a given contribution size. But, I wrote it so that the user can manually override most of the numbers at various points through the calculation. The core of the math is using a Future Value function to estimate future taxable income:

[estimated future taxable withdrawals] = FV([expected real return],[expected years to withdrawal],-[expected future annual contributions],-[current pre-tax account balance]) * [expected future withdrawal rate]

A similar calculation is repeated for taxable investments. Then, add SS income and other income, subtract the age 65+ standard deduction, and do a lookup in the tax rate table to get an estimated marginal future tax rate.
2) Once you have current and future marginal rates, I like having future values for the different tax structures all in one place. Users can compare pre-tax to Roth, pre-tax and Roth to taxable, non-deductible to taxable, etc. Having them all in one spot makes the spreadsheet more compact and takes the place of separate calculators (like "Growth in a taxable account"). Also, I've noticed not all taxable calculators get the basis correct. I believe I have the most accurate formula (derived here (https://www.bogleheads.org/wiki/Taxable_account)). I coded it to do either annual or continuous compounding, but I'd be OK removing that feature and only offering annual, if it were an issue.
3) A traditional versus Roth calculator should definitely have a "contributing the maximum" feature, which is very easy to add once the other pieces are there. This is partly why it makes a lot of sense to include the taxable formula, because this is necessary for this math. Formulas used are here (https://www.bogleheads.org/wiki/User:Fyre4ce/Retirement_plan_analysis#Maxing_out_returement_accounts).
4) I've never seen a formula for deciding between traditional and Roth when you're not able to get the full employer match, so I derived one here (https://www.bogleheads.org/wiki/User:Fyre4ce/Retirement_plan_analysis#Employer_match). I think it's a nice feature that could help some users, and it makes sense to have it along with the other features of that sheet. But, it probably affects only a small number of users, so that's why I list it further down in my priority list.
5) The "Social Security Spike Checker" is actually the most complicated sub-tool on the page, and the easiest to separate if necessary. But, it could be useful to a lot of users so I included it. It runs the same calculation for an alternate scenario of trying to go underneath the tallest spike of SS taxation in retirement. The algorithm goes like this:

a) Future out the amount of other (non-SS) income in retirement necessary to get the user just barely under the 22.2% or 40.7% spike in SS taxation. Formulas for these values are derived here (https://www.bogleheads.org/wiki/Taxation_of_Social_Security_benefits#Heat_map_representation).
b) Given a withdrawal rate, figure out the future pre-tax balance that will generate this amount of income.
c) Figure out a reduced pre-tax contribution that will get the user to this balance, and contribute the rest to Roth. If this change leaves extra room in the Roth account to switch some taxable investments to Roth, do it, but taxable contributions can't go below 0.
d) Using the new pre-tax, Roth, and possibly taxable contributions, and keeping the other variables the same, calculate the total tax due for this alternate scenario.
e) Calculate the total income available after taxes for this alternate scenario, and compare it to the baseline. The lower-performing scenario is indicated with a "-X.XX%" to show the relative size of the difference.

Hope that helps!
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 27, 2021, 06:59:22 PM
I went into a little more detail in a PM to fyre4ce, but some of these items may be added piecemeal along with updates for the 2021 state taxes, etc.
Title: Re: Case Study Spreadsheet updates
Post by: dandarc on February 01, 2021, 07:59:38 AM
Apologies if this is not a good place to ask this. I'm trying to figure out how much tax savings there would be being taxed as an S-Corp vs. Sole proprietor and I'm wondering if the case study spreadsheet is a good place to do that. Looking specifically to include self employed health insurance, soloK, QBI in the analysis, and it is unclear where I might enter everything I need to get a good estimate on taxes in this spreadsheet. I'm not having success figuring out where to put the 'business profit' or how to do soloK in the spreadsheet. But I also haven't found a good calculator elsewhere online - so far every one I've tried seems to leave some significant parts out of the analysis.

This spreadsheet has proven better even than early-release tax software in the past, so I'm thinking it is just user error. Need to figure this out as I'm going to have one chance with my pending "highly-paid-barista-FIRE" opportunity, that notably due to the part-time hours will put me under the social security wage base on gross. In the past when I've been on 1099, I've been far enough over that line that the usual marginal rate on "save self employment tax" was quite a bit lower than it would be for the usual analysis and I've always landed on "eh not worth it" in terms of actually setting up the S-Corp. Was probably a mistake to be lazy about this even then, but it is potentially an even bigger mistake for this.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 01, 2021, 03:32:34 PM
Apologies if this is not a good place to ask this. I'm trying to figure out how much tax savings there would be being taxed as an S-Corp vs. Sole proprietor and I'm wondering if the case study spreadsheet is a good place to do that. Looking specifically to include self employed health insurance, soloK, QBI in the analysis, and it is unclear where I might enter everything I need to get a good estimate on taxes in this spreadsheet.
I'm not at all expert on small business taxes (there are some on this forum who are), but perhaps some of the items in this post (https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg2688700/#msg2688700) would be relevant?

There will be state tax updates from Individual Income and Payroll Taxes | Tax Foundation (https://taxfoundation.org/state-tax/individual-income-payroll-taxes/), some of fyre4ce's suggestions, etc., that will probably happen over the next few weeks (maybe sooner, but...?) so as long as I'm making changes, if some incremental ones would be useful for your question, this could be a good time.  But I may need "guidance". :)
Title: Re: Case Study Spreadsheet updates
Post by: dandarc on February 03, 2021, 01:28:43 PM
So what I wound up doing (this is nowhere near the level of quality MDM provides, just how I got the spreadsheet to help me say "yes, this is going to be worth the effort"):

1. Create a "S-Corp" flag in cell E3. If this is 1, then the Social Security & Medicare line items (B60 & B61) double to reflect that I'm paying for both sides.

2. Enter "Salary" + "Heath Insurance" + "HSA" in B3 - in line with guidance that all these will wind up on W-2

3. Enter "Health Insurance" in B4 - again in line with guidance that while we report health insurance premiums reimbursed by the S-Corp as Income on our W-2, it is not subject to FICA and we deduct this on the self-employed health insurance deduction line. Need to put it into this section so we get FICA to calculate right.

4. Enter "HSA" in B7 for same reasoning as for "Health Insurance"

5. Enter Employee-deferral SoloK amount in B11

6. Calculate expected 'profit' from the S-Corp as follows:
Gross - Employer half of FICA - Health Insurance - HSA - Employer SoloK - Corp Fees - Insurance

Basically gross receipts minus expenses. Gets confusing because of how all this is reported to IRS, but end of day, that's what I'm expecting to be treated as business profit.

7. Enter the amount from step 6 as an addition to cell D23 (taxable interest, non qualified dividends and so on). Really put this anywhere that is subject to income tax but not FICA.

8. To simulate QBI deduction, cell B51 for now is "= 6000 + .2 * amount from step 6". I haven't dug far enough into the details on this, but I'm expecting to now be limited by S-Corp profit instead of taxable income for QBI deduction.

So I then take cell D67 for "total taxes" and compare that to what I get for the sole proprietor version (which the spreadsheet handles well already). That gives us 'tax savings for going S-Corp', and then I can decide if that is enough to justify the the additional cost / hassle of setting this all up. Spoiler: it is. About $7500 top line tax savings which means even after LLC fees and so on, I'm expecting $6,000 + per year net benefit. I think I'll also owe unemployment tax, but that's at most about $500 / year. I'd have to be missing a whole lot for this to not be worth an hour or two per month in additional bookkeeping and tax filing. Real happy I'm in Florida - no state income tax oddities to worry about from what I've read.

A whole lot of work to get this to be robust to where it might be considered for inclusion in the case study spreadsheet - maybe I'll spend some of the 2 additional days per week off in the near future doing that.
Title: Re: Case Study Spreadsheet updates
Post by: SomedayStache on February 05, 2021, 07:22:21 AM
Super duper minor comment (prob not even worth posting due to the target audience of this spreadsheet), but cell 16A `Roth 401k/403b` could have `Roth TSP` added to the description.
Title: Re: Case Study Spreadsheet updates
Post by: YummyRaisins on February 10, 2021, 08:16:53 PM
Thank you so much for this fantastic tool, MDM!

My employer has a tax-qualified employee stock purchase plan. Where should I put contributions to that in the spreadsheet? Would it interfere with any other formulas if I stuck it in Employer-sponsored HSA or Pretax Commuter Costs?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 10, 2021, 09:59:58 PM
Thank you so much for this fantastic tool, MDM!

My employer has a tax-qualified employee stock purchase plan. Where should I put contributions to that in the spreadsheet? Would it interfere with any other formulas if I stuck it in Employer-sponsored HSA or Pretax Commuter Costs?
You're welcome!

By "tax qualified" I'm guessing you mean "as opposed to non-qualified" (see Qualified vs Non-Qualified ESPPs (https://support.carta.com/s/article/qualified-vs-non-qualified-espps))?

For your contributions, row 17, ESPP/After-tax 401k, seems appropriate.  The amount you invest does not affect your AGI.

What to do with the bargain element (https://turbotax.intuit.com/tax-tips/investments-and-taxes/employee-stock-purchase-plans/L8NgMFpFX) and any short/long term capital gains depends on when you sell the shares.  To get accurate taxes, those would be added to Gross Salary/Wages and/or the appropriate capital gain item.

Or is your employer's plan something altogether different?  They do come in various shapes and sizes....
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 10, 2021, 10:05:46 PM
Super duper minor comment (prob not even worth posting due to the target audience of this spreadsheet), but cell 16A `Roth 401k/403b` could have `Roth TSP` added to the description.
Thanks!  Sure could, and why not, given it's already in the traditional description a few rows above? 

It's in there now for the next version.  Also added "457" to the Roth row.  Roth 457s are rare enough that it doesn't seem worth adding a separate row for them.
Title: Re: Case Study Spreadsheet updates
Post by: YummyRaisins on February 11, 2021, 05:14:49 AM
By "tax qualified" I'm guessing you mean "as opposed to non-qualified" (see Qualified vs Non-Qualified ESPPs (https://support.carta.com/s/article/qualified-vs-non-qualified-espps))?

For your contributions, row 17, ESPP/After-tax 401k, seems appropriate.  The amount you invest does not affect your AGI.

What to do with the bargain element (https://turbotax.intuit.com/tax-tips/investments-and-taxes/employee-stock-purchase-plans/L8NgMFpFX) and any short/long term capital gains depends on when you sell the shares.  To get accurate taxes, those would be added to Gross Salary/Wages and/or the appropriate capital gain item.

Or is your employer's plan something altogether different?  They do come in various shapes and sizes....

I guess tax qualified was the wrong description to use. It would be more accurate to call it a pre-tax ESPP.

Private company stock is purchased with pre-tax income. The income used to purchase the shares doesn't show up in FICA wages on my W2.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 11, 2021, 02:36:26 PM
I guess tax qualified was the wrong description to use. It would be more accurate to call it a pre-tax ESPP.

Private company stock is purchased with pre-tax income. The income used to purchase the shares doesn't show up in FICA wages on my W2.
Yes, that's different - appears good for you. :)

In terms of tax calculations, including that amount in both row 3 and either row 7 or row 8 will provide a correct bottom line.

In terms of the simplified "Time to FI?" calculations, there is no mechanism in the CSS that covers an amount
- not subject to FICA or income tax now, but
- investable now and taxable when withdrawn later.

Does that help?

Title: Re: Case Study Spreadsheet updates
Post by: YummyRaisins on February 11, 2021, 06:30:45 PM
Yes, that's different - appears good for you. :)

In terms of tax calculations, including that amount in both row 3 and either row 7 or row 8 will provide a correct bottom line.

In terms of the simplified "Time to FI?" calculations, there is no mechanism in the CSS that covers an amount
- not subject to FICA or income tax now, but
- investable now and taxable when withdrawn later.

Does that help?

Yes this makes sense. Thank you for the feedback.

I guess for the purpose of tax calculation I could leave it out altogether and for FI calculations I could add the account value to my taxable stocks & bonds (row 174).
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 11, 2021, 08:56:11 PM
Yes, that's different - appears good for you. :)

In terms of tax calculations, including that amount in both row 3 and either row 7 or row 8 will provide a correct bottom line.

In terms of the simplified "Time to FI?" calculations, there is no mechanism in the CSS that covers an amount
- not subject to FICA or income tax now, but
- investable now and taxable when withdrawn later.

Does that help?

Yes this makes sense. Thank you for the feedback.

I guess for the purpose of tax calculation I could leave it out altogether and for FI calculations I could add the account value to my taxable stocks & bonds (row 174).
Cell B72, "Other Untaxed Income", is another possibility.  It's just there as a catch-all, and you'll have to divide by 12 because it's a "per month" cell, but it might fit your needs.
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on February 12, 2021, 12:25:10 AM
Version 21.03 (2021)

2020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
2021 taxes version: 2021 Case Study Spreadsheet (https://drive.google.com/file/d/18uN77cP1m9ztZ5boHmqMnQ3CngkaDf6p/view?usp=sharing)

The taxfoundation.org state tax update for 2021 hasn't come out yet.  It was released February 4 (https://taxfoundation.org/publications/state-individual-income-tax-rates-and-brackets/) in 2020, but there have been a few other tax things afoot so we won't begrudge their free compilation being a little later this year.

For those of you in states like California that don't see fit to release rates and brackets until November of that tax year (https://www.ftb.ca.gov/about-ftb/newsroom/tax-news/november-2020/standard-deductions-exemption-amounts-and-tax-rates-for-2020-tax-year.html), I suppose you are used to guessing....

Thanks to nolesrule and SomedayStache for the suggestions listed above.

As for the more comprehensive suggestions by dandarc and fyre4ce...not yet.  ;)

dandarc: as you are already far down the S-corp road, I'm happy to let you press ahead and see if a generic and tractable implementation is feasible.

fyre4ce: The "relatively simple and easy to use" philosophy you mentioned in our PMs seems more appropriate for the CSS than trying for something "powerful" when it comes to estimating withdrawal tax rates.  Rather than using the marginal rate chart to feed a detailed calculation table, it seems better to use the existing "Estimating withdrawal tax rates" table that starts on row 198 of the 'Misc. calcs' tab to feed the marginal rate chart.  But now that I write this, perhaps moving that table to the 'Calculations' tab so people don't have to flip between tabs....  OK, that's one for the next version.

Note:
Title: Re: Case Study Spreadsheet updates
Post by: Retireatee1 on February 13, 2021, 06:53:20 AM
I believe there is a small error on the SocialSecurity sheet.  If you enter birth year 1/1/1955, it is using the years 2015/2017 for wage indexing and bend points.  I believe this is incorrect as a birth date of Jan 1 triggers a special case:

https://www.ssa.gov/benefits/retirement/planner/ageincrease.html

So these should be 2014/2016.

This issue is exacerbated by the Maximum-Taxable Earnings page which you probably use to calibrate your results:

https://www.ssa.gov/OACT/COLA/examplemax.html

That page specifically says "initial benefit amounts shown in the table below assume retirement in January of the stated year".  To retire in January precisely at these ages requires your birthdate to be on January 1st, otherwise February 1st is the earliest you can file.  I believe they triggered the special case inadvertently in this documentation.  That shift of the base years throws everything off.

EDIT: This page has the clearest language.  "Retirement benefits can begin the first month a person is age 62 throughout the entire month. (See RS 00201.001C.) Social Security follows English common law that finds that a person attains an age on the day before the birthday."

https://secure.ssa.gov/poms.nsf/lnx/0300615015

So using the "English common law" definition, the Jan 1 birthdate special case rolls everything back 1 year.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 13, 2021, 10:37:33 AM
That's correct, it Doesn't do "born on the first of the month" adjustments (https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1041979/#msg1041979) - including "born on the first of the year" adjustments. :)
Title: Re: Case Study Spreadsheet updates
Post by: Retireatee1 on February 13, 2021, 11:34:59 AM
That's correct, it Doesn't do "born on the first of the month" adjustments (https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1041979/#msg1041979) - including "born on the first of the year" adjustments. :)

I believe you just have to change that C102 calculation to "=YEAR(B102-1)"
Title: Re: Case Study Spreadsheet updates
Post by: mjb on February 13, 2021, 12:24:25 PM
Hello MDM! Thank you, yet again, for this amazing spreadsheet. It makes tax time in our household a breeze.

One small bug (that I was just bit by): Self-employed health insurance premiums (D48) are only deductible up to the limit of:
(This year I had very low Schedule C income and maxed out my solo 401(k), hence my Self-employed health insurance premiums were not deductible.)

I think this updated equation for cell D48 should do the trick:

Code: [Select]
=MAX(MIN(B48*$B$164,B30*$B$163-B46*$B$164-B47*$B$164),0)+MAX(MIN(C48*$C$164,C30*$C$163-C46*$C$164-C47*$C$164),0)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 13, 2021, 12:49:16 PM
That's correct, it Doesn't do "born on the first of the month" adjustments (https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1041979/#msg1041979) - including "born on the first of the year" adjustments. :)
I believe you just have to change that C102 calculation to "=YEAR(B102-1)"
Thanks!  If that's all that is needed, it will go in the next version.

Do you have a similarly easy fix for the "first of the month" situation? :)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 13, 2021, 01:02:26 PM
I think this updated equation for cell D48 should do the trick:
Indeed it does - thanks!  Already changed for the next version.
Title: Re: Case Study Spreadsheet updates
Post by: mjb on February 13, 2021, 02:49:11 PM
I think this updated equation for cell D48 should do the trick:
Indeed it does - thanks!  Already changed for the next version.

Awesome, thank you!
Title: Re: Case Study Spreadsheet updates
Post by: Retireatee1 on February 14, 2021, 07:18:51 AM
That's correct, it Doesn't do "born on the first of the month" adjustments (https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1041979/#msg1041979) - including "born on the first of the year" adjustments. :)
I believe you just have to change that C102 calculation to "=YEAR(B102-1)"
Thanks!  If that's all that is needed, it will go in the next version.

Do you have a similarly easy fix for the "first of the month" situation? :)

Try these:

F115: =YEAR(EDATE(DATE(YEAR($B$102)+$B115,MONTH($B$102),1),IF(DAY($B$102)=1,0,1)+ROUNDUP($C115,0)))
F116: =YEAR(EDATE(DATE(YEAR($B$102)+$B116,MONTH($B$102),1),IF(DAY($B$102)=1,0,1)+ROUNDUP($C116,0)))
F117: =YEAR(EDATE(DATE(YEAR($B$102)+$B117,MONTH($B$102),1),IF(DAY($B$102)=1,0,1)+ROUNDUP($C117,0)))
Title: Re: Case Study Spreadsheet updates
Post by: rae09 on February 16, 2021, 08:46:57 PM
@MDM , You're a life saver! Thank you so much for making tax planning so easy!


So what I wound up doing (this is nowhere near the level of quality MDM provides, just how I got the spreadsheet to help me say "yes, this is going to be worth the effort"):

1. Create a "S-Corp" flag in cell E3. If this is 1, then the Social Security & Medicare line items (B60 & B61) double to reflect that I'm paying for both sides.

2. Enter "Salary" + "Heath Insurance" + "HSA" in B3 - in line with guidance that all these will wind up on W-2

3. Enter "Health Insurance" in B4 - again in line with guidance that while we report health insurance premiums reimbursed by the S-Corp as Income on our W-2, it is not subject to FICA and we deduct this on the self-employed health insurance deduction line. Need to put it into this section so we get FICA to calculate right.

4. Enter "HSA" in B7 for same reasoning as for "Health Insurance"

5. Enter Employee-deferral SoloK amount in B11

6. Calculate expected 'profit' from the S-Corp as follows:
Gross - Employer half of FICA - Health Insurance - HSA - Employer SoloK - Corp Fees - Insurance

Basically gross receipts minus expenses. Gets confusing because of how all this is reported to IRS, but end of day, that's what I'm expecting to be treated as business profit.

7. Enter the amount from step 6 as an addition to cell D23 (taxable interest, non qualified dividends and so on). Really put this anywhere that is subject to income tax but not FICA.

8. To simulate QBI deduction, cell B51 for now is "= 6000 + .2 * amount from step 6". I haven't dug far enough into the details on this, but I'm expecting to now be limited by S-Corp profit instead of taxable income for QBI deduction.

So I then take cell D67 for "total taxes" and compare that to what I get for the sole proprietor version (which the spreadsheet handles well already). That gives us 'tax savings for going S-Corp', and then I can decide if that is enough to justify the the additional cost / hassle of setting this all up. Spoiler: it is. About $7500 top line tax savings which means even after LLC fees and so on, I'm expecting $6,000 + per year net benefit. I think I'll also owe unemployment tax, but that's at most about $500 / year. I'd have to be missing a whole lot for this to not be worth an hour or two per month in additional bookkeeping and tax filing. Real happy I'm in Florida - no state income tax oddities to worry about from what I've read.

A whole lot of work to get this to be robust to where it might be considered for inclusion in the case study spreadsheet - maybe I'll spend some of the 2 additional days per week off in the near future doing that.

Thank you for posting what you did. I'm on the same boat so this is very helpful for us.
Can I ask where the $6,000 on step #8 comes from?
Also, I believe on step #6, you'd need to deduct the W2 wages as well (I think you did in your calc, just forgot to note it here)?
Title: Re: Case Study Spreadsheet updates
Post by: dandarc on February 17, 2021, 09:31:42 AM
$6000 is a normal traditional IRA contribution - I'm basically semi-retiring in May if the paperwork goes through on this new gig (just got my "Articles of Incorporation are approved" email today - exciting times), so no doubt at all I'll be able to deduct that under the new lower income.
Title: Re: Case Study Spreadsheet updates
Post by: dandarc on February 17, 2021, 09:41:21 AM
You are correct about #6 - and I did do that. Just forgot to put it on the post.
Title: Re: Case Study Spreadsheet updates
Post by: rae09 on February 17, 2021, 02:48:03 PM
$6000 is a normal traditional IRA contribution - I'm basically semi-retiring in May if the paperwork goes through on this new gig (just got my "Articles of Incorporation are approved" email today - exciting times), so no doubt at all I'll be able to deduct that under the new lower income.

I see. So, I shouldn't enter the $6k IRA contribution in cell B50 and enter it in B51 instead?

From the standard Sch C sheet, the QBI deduction is reduced by 20% of the IRA contribution amount in cell B50, but you mentioned put $6,000 + 20% of the S-corp distribution amount in B51. Does it mean the QBI is taking the whole $6k deduction instead of 20% of it?
Title: Re: Case Study Spreadsheet updates
Post by: dandarc on February 17, 2021, 03:08:13 PM
Bah - I see it is cell B50 too. Screwed that line up coming and going in the post.

I also did this specifically for my situation - I'm expecting QBI to be the limiting factor vs. taxable income. In the past for me it has been the other way around. You are right that if 'taxable income' is your limiting factor, then a traditional IRA deduction would reduce your QBI deduction.
Title: Re: Case Study Spreadsheet updates
Post by: dandarc on February 17, 2021, 03:30:57 PM
I see another issue - QBI deduction does not reduce AGI, but the way I put it in that IRA line item, it does. AGI has downstream impacts, not the least of which is ACA premium tax credit. Note on that - if stimulus passes the way it was written, then for 2021 and 2022, PTC goes up a little bit and (this next part is absolutely huge for me) the cliff where you go from a $4,000 PTC to $0 with one additional dollar of AGI at the 400% of FPL level goes away.

Fixed that by just making a cell to manually enter S-Corp profit and modifying the existing QBI cell to take 20% of that for now.

Like I said - I was just trying to figure out if S-Corp hassle was worth it.
Title: Re: Case Study Spreadsheet updates
Post by: rae09 on February 18, 2021, 01:36:32 PM
I see another issue - QBI deduction does not reduce AGI, but the way I put it in that IRA line item, it does. AGI has downstream impacts, not the least of which is ACA premium tax credit. Note on that - if stimulus passes the way it was written, then for 2021 and 2022, PTC goes up a little bit and (this next part is absolutely huge for me) the cliff where you go from a $4,000 PTC to $0 with one additional dollar of AGI at the 400% of FPL level goes away.

Fixed that by just making a cell to manually enter S-Corp profit and modifying the existing QBI cell to take 20% of that for now.

Like I said - I was just trying to figure out if S-Corp hassle was worth it.

Yup, I'm also on the same boat, hence thought we can team up and cover what the other misses.

I played around some more with the S-corp version and here are the changes I made:

1. Move the S-corp distribution from cell D23 to D40, then change the formula in Y137 to =MAX(0,D40-D46-D47-D48) to update the QBI calculation.

2. Enter the S-corp gross income in E1 and have the formula in D40 deduct all the deductions on your step 6 from the number in E1. This way, I can play around with different income scenarios easily.

3. Put t-IRA as normally in B50 & C50 so AGI is reduced by any t-IRA contribution.


Using a hypothetical scenario for DINK, MFJ with $100k S-corp income, $60k salary, max 401(k) contribution ($19.5k deferral + $15k co matching), $7,100 HSA, $12k tIRA, I'm getting a total tax due of $9,693 in D67, while using Sole Proprietorship, the tax balance becomes $14,130. So, a saving of almost $4,500 but we'll have to do the 1120S, issue K1, and run payrolls.
 
This scenario is disregarding the health insurance reimbursement to the 2% shareholder that needs to be added to the W2.
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on March 03, 2021, 03:02:25 PM
Version 20.13 (2020)
Version 21.04 (2021)

202020212020 taxes version: 2020 Case Study Spreadsheet (https://drive.google.com/file/d/1klX_6nqOZG-CkGccNQcurzVWjfV5QXoU/view?usp=sharing)
2021 taxes version: 2021 Case Study Spreadsheet (https://drive.google.com/file/d/18uN77cP1m9ztZ5boHmqMnQ3CngkaDf6p/view?usp=sharing)

As in past years, thanks to the folks at taxfoundation.org for their State Individual Income Tax Rates and Brackets for 2021 (https://taxfoundation.org/publications/state-individual-income-tax-rates-and-brackets/).  For those of you familiar with your own state's tax calculations, use State Income Tax calculations - Crowdsourcing request (https://forum.mrmoneymustache.com/taxes/state-income-tax-calculations-crowdsourcing-request/msg1697962/#msg1697962) or a PM for "relatively simple" ;) suggested improvements.  Blatant numerical errors count as relatively simple.

Thanks to mjb for the SEHI fix, Retireatee1 for the 1-Jan b'day fix, and fyre4ce for ideas on future marginal rate estimation.

See posts from a few weeks ago for details on the SEHI and 1-Jan changes.

The section on estimating future marginal tax rates (a few calculations and instructions for which cells to place results) remains simple, as suggested in Estimating withdrawal tax rates (https://forum.mrmoneymustache.com/investor-alley/estimating-withdrawal-tax-rates/).

Allowing folks to use "2026" brackets and rates has two purposes:
The current implementation affects only ordinary income brackets and rates.  True fidelity to 2017 regulations would require changes to exemptions, deductions, alternative minimum tax, etc.

My personal opinion is that, come 2026, neither assumption will have been 100% correct, but I have no better idea of what reality will be.

As always, large changes such as these make fat-fingered and other errors more likely.  Corrections/suggestions welcome.

Note:
Title: Re: Case Study Spreadsheet updates
Post by: kimura on March 08, 2021, 07:09:48 PM
I love this spread sheet. I live in WA and there is no state tax. Can I delete the "State Tax' and "State Brackets" without screwing up the entire spreadsheet? Just wanted to to delete those since I dont need them.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on March 08, 2021, 08:05:55 PM
I love this spread sheet. I live in WA and there is no state tax. Can I delete the "State Tax' and "State Brackets" without screwing up the entire spreadsheet? Just wanted to to delete those since I dont need them.
Yes, I just tried and it appears you can do that.  Deleting the formula in cell G35 (leave it blank or enter zero) will prevent a bunch of #N/As should you enter WA in cell H35 to do ACA premium tax credit calculations.

For that matter, if all you want are the tax calculations you could delete all the other sheets except the 'Form 6251' and 'Tax Code' sheets. :)
Title: Re: Case Study Spreadsheet updates
Post by: kimura on March 08, 2021, 08:24:16 PM
Thanks for the help!
Title: Re: Case Study Spreadsheet updates
Post by: Retireatee1 on March 15, 2021, 12:38:00 PM
OK I went back and revisited the Social Security date arithmetic again.

I see the C102 fix to subtract 1 for English common law is in.  That is good.

Really now you just need to revise these three cells slightly with the same fix (disregard my earlier post):

F115: =YEAR(EDATE($B$102-1,B115*12+C115))
F116: =YEAR(EDATE($B$102-1,B116*12+C116))
F117: =YEAR(EDATE($B$102-1,B117*12+C117))

I'd add some rounding to the month count as this should never be a fraction:

C115: =ROUND((B119-B115)*12,0)

You really don't need to worry about the day you were born as all you care about are years, unless it is Jan 1 which is handled.  For most people, they cannot retire at age 62.  62 plus 1 month is the earliest and 62 is an invalid entry (I wouldn't try to "fix" it).  As you are not performing data validation, you get a slightly inaccurate result if you enter that combination.  This is why the government examples all start at 62 + 1/12.

So that should do it.  Feel free to compare any results with my calculator:

https://www.retireator.org/
Title: Re: Case Study Spreadsheet updates
Post by: HudsonMK on March 15, 2021, 07:43:13 PM
Is there anywhere in the spreadsheet that accounts for current cash savings in a bank account? I looked a few times, but apologies if this has already been answered. Thank-you!
Title: Re: Case Study Spreadsheet updates
Post by: MDM on March 15, 2021, 08:17:50 PM
Is there anywhere in the spreadsheet that accounts for current cash savings in a bank account? I looked a few times, but apologies if this has already been answered. Thank-you!
Great question!  Cash savings in a bank account are assumed to be the "emergency fund" and not part of "invested assets".

The comment for cell A174 is "Ignore cash.  Calculations assume cash sits in an emergency fund, or otherwise serves as a buffer to dampen market fluctuations, so it can and should be ignored for withdrawal rate purposes."  Not front and center, but it's in there.... ;)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on March 15, 2021, 08:59:31 PM
OK I went back and revisited the Social Security date arithmetic again.

I see the C102 fix to subtract 1 for English common law is in.  That is good.

Really now you just need to revise these three cells slightly with the same fix (disregard my earlier post):

F115: =YEAR(EDATE($B$102-1,B115*12+C115))
F116: =YEAR(EDATE($B$102-1,B116*12+C116))
F117: =YEAR(EDATE($B$102-1,B117*12+C117))
Thanks, looks good and those are in the version I'm about to post.  That version is primarily for the recent law changes, so I'll discuss the SS ones here.

Quote
I'd add some rounding to the month count as this should never be a fraction:

C115: =ROUND((B119-B115)*12,0)
True.  The main use of the continuous nature of this cell was to generate a smoothed quadratic fit (see cells G119:I119 and the graph over cell AD107) for SS benefits vs. start date, with the idea of using that as part of some overall retirement planning optimizer.  That idea bore some fruit, but nothing suitable for general use.  And in the table generation for the curve fit the points are spaced "even twelfths" of a year apart so those are "integer months".  I'll think about this one.  One can specify year and month directly in row 117.

Quote
You really don't need to worry about the day you were born as all you care about are years, unless it is Jan 1 which is handled.  For most people, they cannot retire at age 62.  62 plus 1 month is the earliest and 62 is an invalid entry (I wouldn't try to "fix" it).  As you are not performing data validation, you get a slightly inaccurate result if you enter that combination.  This is why the government examples all start at 62 + 1/12.
Most, but not all.  E.g., footnote "a" on Maximum-taxable benefit examples (https://www.ssa.gov/oact/cola/examplemax.html) does have "Retirement at age 62 is assumed here to be at exact age 62 and 1 month,"  but Social Security Retirement Benefit Calculation (https://www.ssa.gov/oact/ProgData/retirebenefit2.html) has "We assume the worker in case A begins receiving benefits at the earliest possible age, which is age 62."

Again, thanks and good to know that two different tools get the same answer!
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on March 15, 2021, 09:45:32 PM
Version 21.05 (2021)

20202021
Various changes for 2021 included in the American Rescue Plan Act:

Child Tax Credit
- Include age 17 child for Child Tax Credit
- Include extra $1000 CTC for lower incomes (and $1600 for children under 6)
- Remove $1400 limit on refundable CTC

Third stimulus / 2021 Recovery Rebate Credit
- Provide a place to enter how much EIP#3 was received (cell Calculations!I31), as input to the 2021 Recovery Rebate Credit (RRC)
- Add phase-out incomes for RRC calculation, in case the maximum EIP#3 had not already been received

Unemployment benefits
- Add an input row (Calculations row 37) for unemployment benefits
- Limit household income to 133% of the federal poverty line for ACA purposes if unemployment income > 0

Child and dependent care credit
- Change this credit from non-refundable to refundable
- Increase the maximum credit amount
- Implement a new "fraction of expenses vs. income" table
- Implement a phase-out above $400K income

Earned Income Credit
- Change investment income cliff from $3650 to $10000
- Change minimum eligibility age to 19 if not a student, and 24 if a student
- Remove maximum age
- Change income break points and maximum credit for "no children" filers

Affordable Care Act Premium Tax Credit
- Extend eligibility for this credit to income above 400% of the federal poverty level
- Change the "fraction of income allowed for insurance premiums" table

In addition to the usual susceptibility of new releases to error, interpreting the language of the law itself (Text - H.R.1319 - 117th Congress (2021-2022): American Rescue Plan Act of 2021 | Congress.gov | Library of Congress (https://www.congress.gov/bill/117th-congress/house-bill/1319/text#toc-HB3932D87AB6840A6AE50E45116688CB1)) instead of relying on the IRS interpretation in forms and instructions adds another possible source of error.

In other words, if those of you with particular interest/expertise in specific parts of the new law cast a critical eye over this implementation and find deficiencies, that wouldn't be a total surprise. :)  Reports of those, either here or via PM, will be appreciated (and useful to others when fixed).


2021 taxes version: 2021 Case Study Spreadsheet (https://drive.google.com/file/d/18uN77cP1m9ztZ5boHmqMnQ3CngkaDf6p/view?usp=sharing)


Note:
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on March 24, 2021, 09:15:06 PM
Version 21.06 (2021)

2021

Child and dependent care credit
Implemented a continuous approximation to the actual law.  This allows the marginal rate chart for a family with
- Earner #2 wages = $25,100
- One dependent age 10, another age 5
- Dependent care cost = $16,000

to look like
(https://lh3.googleusercontent.com/d/1IWxyTpXRi5y1MJI8bzcfRnmNSDRsq3Uf)

instead of
(https://lh3.googleusercontent.com/d/11luQoLVccGa_bo-7b4R1gO6-wCLvneI2)


Those who prefer to see the discontinuous marginal rates of the tax code (for this and all other included features) in all their glory may change cell R80 from Y to N.

2021 taxes version: 2021 Case Study Spreadsheet (https://drive.google.com/file/d/18uN77cP1m9ztZ5boHmqMnQ3CngkaDf6p/view?usp=sharing)


Note:
Title: Re: Case Study Spreadsheet updates
Post by: MDM on April 26, 2021, 02:51:21 PM
Version 21.07 (2021)

2021
Thanks to MountainLakeMaka and Harry Sit for insights and suggestions on these updates.

SEHI+PTC iteration capability
The spreadsheet is now attempting to handle the iterative calculations when both Premium Tax Credits (PTC) and Self-Employed Health Insurance (SEHI) premiums are involved, at least for one Schedule C and one SEHI premium.  For people with both self-employment income and Affordable Care Act coverage, determining the optimal amount of the SEHI deduction can be an iterative process.  See pp. 57-75 of https://www.irs.gov/pub/irs-pdf/p974.pdf.

These calculations have been tested and appear to converge reliably over a wide range of conditions. To enable this capability,
    - Enter the appropriate values in cells B114, B115, AD123 and AE100
    - Enter the formula =AD159 in cell B48.  This enables the iteration
    - Entering a specific value, e.g., 0 (zero) in cell B48, or deleting the cell contents, will eliminate the iteration.
    - Ensure the "Enable iterative calculation" box is checked in File > Options > Formulas if the iterations should be, but aren't, occurring.
   Iteration requires significant extra calculation and decreases spreadsheet responsiveness.  Useful if needed, but avoid it otherwise.
   If many #Value, #DIVbyZero, etc., errors propagate, put a specific value in cell B48, fix the root cause, and re-enable iteration.  If errors persist, reply or PM.

At least a couple of things are not clear:
In general, suggestions/comments/corrections on the SEHI+PTC issue are welcome.  Somewhat of a niche issue, but mathematically interesting and financially significant when it occurs.

2021 taxes version: 2021 Case Study Spreadsheet (https://drive.google.com/file/d/18uN77cP1m9ztZ5boHmqMnQ3CngkaDf6p/view?usp=sharing)


Note:
Title: Re: Case Study Spreadsheet updates
Post by: jeromedawg on April 26, 2021, 04:39:05 PM
Couple clarifying questions (sorry if these were already asked prior):

1) When specifying "Guess Time to FI" is the assumption that the "Gross Salary/Wages" (B3 and C3) won't change?

2) What would the right 'procedure' be to map out a cash-out refi where you're taking the cash-out and reinvesting in index funds but also drawing-down from it year over year to supplement your income/earnings (whether pre or post retirement)? I noticed boxes D26 and D27 but if you plug a number into those, it seems the spreadsheet assumes that those are going to be year-over-year numbers versus one-time...?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on April 26, 2021, 04:54:24 PM
Couple clarifying questions (sorry if these were already asked prior):

1) When specifying "Guess Time to FI" is the assumption that the "Gross Salary/Wages" (B3 and C3) won't change?

2) What would the right 'procedure' be to map out a cash-out refi where you're taking the cash-out and reinvesting in index funds but also drawing-down from it year over year to supplement your income/earnings (whether pre or post retirement)? I noticed boxes D26 and D27 but if you plug a number into those, it seems the spreadsheet assumes that those are going to be year-over-year numbers versus one-time...?
1) Yes.  More precisely, that they won't change in real terms: inflation is ignored for all calculations.
2) Ignore it?  Probably depends on how significant it is relative to your overall finances.  You might need more sophisticated tools such as those described in Best and/or Recommended Retirement Calculator - Bogleheads.org (https://www.bogleheads.org/forum/viewtopic.php?t=115839#p1686175) if fine details are needed.  Note that the error band on assumed return rates usually dwarfs all other items....
Title: Re: Case Study Spreadsheet updates
Post by: jeromedawg on May 02, 2021, 08:14:36 PM
Couple clarifying questions (sorry if these were already asked prior):

1) When specifying "Guess Time to FI" is the assumption that the "Gross Salary/Wages" (B3 and C3) won't change?

2) What would the right 'procedure' be to map out a cash-out refi where you're taking the cash-out and reinvesting in index funds but also drawing-down from it year over year to supplement your income/earnings (whether pre or post retirement)? I noticed boxes D26 and D27 but if you plug a number into those, it seems the spreadsheet assumes that those are going to be year-over-year numbers versus one-time...?
1) Yes.  More precisely, that they won't change in real terms: inflation is ignored for all calculations.
2) Ignore it?  Probably depends on how significant it is relative to your overall finances.  You might need more sophisticated tools such as those described in Best and/or Recommended Retirement Calculator - Bogleheads.org (https://www.bogleheads.org/forum/viewtopic.php?t=115839#p1686175) if fine details are needed.  Note that the error band on assumed return rates usually dwarfs all other items....

Ah okay thanks.

One other question/clarification: I'm having trouble understanding how a mortgage impacts the "Time to FI" section and particularly the "Projected Expenses in Retirement" - how/where does that all factor in or relate?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on May 02, 2021, 08:59:36 PM
One other question/clarification: I'm having trouble understanding how a mortgage impacts the "Time to FI" section and particularly the "Projected Expenses in Retirement" - how/where does that all factor in or relate?
The assumption is that the "Stash needed for retirement" is composed of two terms:
a) (Retirement expenses) / (Safe Withdrawal Ratio), plus
b) Loan balances
A further assumption is that if one has invested the loan balance, the return on those investments will be at least as high as the mortgage interest rate.

So the mortgage doesn't impact the expenses in retirement, because the payments
- do not increase with inflation, and
- usually end prior to the 30 years usually associated with "Safe Withdrawal Ratios".

Make sense?

Title: Re: Case Study Spreadsheet updates
Post by: jeromedawg on May 02, 2021, 09:27:17 PM
One other question/clarification: I'm having trouble understanding how a mortgage impacts the "Time to FI" section and particularly the "Projected Expenses in Retirement" - how/where does that all factor in or relate?
The assumption is that the "Stash needed for retirement" is composed of two terms:
a) (Retirement expenses) / (Safe Withdrawal Ratio), plus
b) Loan balances
A further assumption is that if one has invested the loan balance, the return on those investments will be at least as high as the mortgage interest rate.

So the mortgage doesn't impact the expenses in retirement, because the payments
- do not increase with inflation, and
- usually end prior to the 30 years usually associated with "Safe Withdrawal Ratios".

Make sense?

Stupid me... I overlooked plugging in the number to "Current Principal" which was throwing things off. Haha. I think I have a better understanding now...
Title: Re: Case Study Spreadsheet updates
Post by: johnhenry on May 04, 2021, 02:51:17 PM
Hi.

I'm using V20.13 (3/3/2021) of the 2020 CSS.  If that's not the latest, could someone point me to it?

I'm having trouble with my

1040 Total Income (cell D42)
1040 AGI  (cell D52)
Taxable (cell G16)

all being "off" (over in the CSS) by the amount of my Qualified Dividends.  Basically, the CSS seems to be including them where the tax return I'm entering them from has them excluded.

thanks in advance....
Title: Re: Case Study Spreadsheet updates
Post by: MDM on May 04, 2021, 03:16:11 PM
Hi.

I'm using V20.13 (3/3/2021) of the 2020 CSS.  If that's not the latest, could someone point me to it?

I'm having trouble with my

1040 Total Income (cell D42)
1040 AGI  (cell D52)
Taxable (cell G16)

all being "off" (over in the CSS) by the amount of my Qualified Dividends.  Basically, the CSS seems to be including them where the tax return I'm entering them from has them excluded.

thanks in advance....
If your 1099-DIV has $1000 in box 1a (Total ordinary dividends) and $925 in box 1b (Qualified dividends) the spreadsheet entries would be
Cell D23: $75
Cell D25: $925

Does that make sense?
Title: Re: Case Study Spreadsheet updates
Post by: johnhenry on May 05, 2021, 07:54:16 AM
If your 1099-DIV has $1000 in box 1a (Total ordinary dividends) and $925 in box 1b (Qualified dividends) the spreadsheet entries would be
Cell D23: $75
Cell D25: $925

Does that make sense?

Thanks.  That got me fixed up.  I see the comment now that asks for "non-qualified dividends" as opposed to "total ordinary dividends".... I'm just used to entering the latter into tax software since that's what's reported in Box 1a.

Title: Re: Case Study Spreadsheet updates
Post by: drumstache on May 05, 2021, 10:23:14 AM
One question about the 'Safe Withdrawal Rate', Cell B168.  When I increase that value from 4% to say 6%, the value for 'Stash needed for retirement' in cell B194 decreases.  Shouldn't that number go up if I'm increasing the withdrawal rate?

Very possible I hosed something up, or misunderstanding how this works.

Appreciate the work put into this spreadsheet, great tool, Thanks!


 
Title: Re: Case Study Spreadsheet updates
Post by: MDM on May 05, 2021, 10:49:30 AM
One question about the 'Safe Withdrawal Rate', Cell B168.  When I increase that value from 4% to say 6%, the value for 'Stash needed for retirement' in cell B194 decreases.  Shouldn't that number go up if I'm increasing the withdrawal rate?

Very possible I hosed something up, or misunderstanding how this works.

Appreciate the work put into this spreadsheet, great tool, Thanks!
See Safe withdrawal rates (https://www.bogleheads.org/wiki/Safe_withdrawal_rates) (SWR) for details, but in short the equation is "Spending = SWR * Stash", or "Stash = Spending / SWR".  As your estimate of SWR goes up, the required stash goes down.

Of course, the "S" part of the acronym is known only in hindsight....
Title: Re: Case Study Spreadsheet updates
Post by: drumstache on May 06, 2021, 06:51:07 AM

See Safe withdrawal rates (https://www.bogleheads.org/wiki/Safe_withdrawal_rates) (SWR) for details, but in short the equation is "Spending = SWR * Stash", or "Stash = Spending / SWR".  As your estimate of SWR goes up, the required stash goes down.

Of course, the "S" part of the acronym is known only in hindsight....

Ah, makes sense now.  Thanks for the clarification!
Title: Re: Case Study Spreadsheet updates
Post by: MDM on May 11, 2021, 10:25:15 PM
Version 21.08 (2021)

2021

SEHI+PTC iteration capability update
"Worksheet Y" has been added, but none of its calculations are currently used.  With the elimination of the >400% FPL cliff, the IRS will be changing some things in this area.  If anyone is interested in collaborating on more work in this area, let me know (reply here or PM).  Otherwise, the fact that iteration converges reliably(?) for at least "straightforward" situations may be good enough for estimation purposes....

2021 taxes version: 2021 Case Study Spreadsheet (https://drive.google.com/file/d/18uN77cP1m9ztZ5boHmqMnQ3CngkaDf6p/view?usp=sharing)


Note:
Title: Re: Case Study Spreadsheet updates
Post by: jeromedawg on June 27, 2021, 09:37:58 PM
Kind of an off-topic but related question here but what is your guys' general opinion of the "Have $XXXXX extra" amount and what you *prefer* that number to be in order to feel comfortable about RE?

Was also wondering about the state abbreviation in H32 - I tried entering "CA" and it doesn't seem to be able to recognize it - EDIT: nm, I entered a couple other states and it eventually populated.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on July 11, 2021, 04:56:37 PM
Version 21.09 (2021)
2021 taxes version: 2021 Case Study Spreadsheet (https://drive.google.com/file/d/18uN77cP1m9ztZ5boHmqMnQ3CngkaDf6p/view?usp=sharing)

Note:
Title: Re: Case Study Spreadsheet updates
Post by: getmoneyeatpizza on July 14, 2021, 12:52:29 PM
"Enter the amount of Child Tax Credit already received"

Should this be the amount expected to be received? Specifically for purposes of trying to estimate my tax return. I will get $3300 advance CTC this year so should I just deduct this amount from the expected return amount?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on July 14, 2021, 01:15:39 PM
"Enter the amount of Child Tax Credit already received"

Should this be the amount expected to be received? Specifically for purposes of trying to estimate my tax return. I will get $3300 advance CTC this year so should I just deduct this amount from the expected return amount?
Yes, that's it for the CTC.

For the purpose of estimating the refund or amount owed when filing, one also needs to account for withholding and estimated tax payments.  See cells I70 and I71, and the rest of the "Form W-4" section for that.

In general I'd suggest calculating the tax liability and then adjusting W-4s as needed to owe ~$0 when filing, but individual circumstances can vary.