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://docs.google.com/spreadsheets/d/19trTvaOPryFWiaup24dasbjJP6jl2SLB/edit?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.

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

2022 taxes version: 2022 Case Study Spreadsheet (https://docs.google.com/spreadsheets/d/19trTvaOPryFWiaup24dasbjJP6jl2SLB/edit?usp=sharing) (see Introduction of the 2022 version (https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg2970616/#msg2970616) and subsequent posts for more)


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.
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.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on August 31, 2021, 07:46:28 PM
See How do RMDs affect Roth conversion choices? (https://forum.mrmoneymustache.com/investor-alley/how-do-rmds-affect-roth-conversion-choices/new/#new) for some work to address that question.  Seems it ought to be a relatively straightforward calculation but something isn't adding up.  If you have some spreadsheet ability (or can do the analysis in any programming language) your input would be appreciated.
Title: Re: Case Study Spreadsheet updates
Post by: kate1243hep on September 27, 2021, 05:09:06 AM
There are Qualified Charitable Distributions (QCDs) but that's a different topic.

Do we just subtract QCDs from box 31D or do they go elsewhere?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on September 27, 2021, 01:54:43 PM
There are Qualified Charitable Distributions (QCDs) but that's a different topic.
Do we just subtract QCDs from box 31D...?
Yes, exactly that!
Title: Re: Case Study Spreadsheet updates
Post by: gamedays on October 04, 2021, 10:51:26 PM
Hello and thank you for creating this spreadsheet, it is fantastic. Unless I'm doing something wrong, I see one error: it seems that you have unemployment income taxable on the California state taxes. UI is not taxable in CA - it is taken as a deduction on column B of the CA adjustments section of Schedule 540. If I am correct here, is this something you can fix? Thank you.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on October 04, 2021, 11:44:38 PM
Hello and thank you for creating this spreadsheet, it is fantastic. Unless I'm doing something wrong, I see one error: it seems that you have unemployment income taxable on the California state taxes. UI is not taxable in CA - it is taken as a deduction on column B of the CA adjustments section of Schedule 540. If I am correct here, is this something you can fix? Thank you.
That's not an error, it's a missing feature. ;)

Without committing to incorporate all of the CA state tax code, that one seems simple enough to go in the next release.  Meanwhile, you could try the following to see if it gives the desired results:
- put =Calculations!D37 into 'State Tax'!E14
- put =-E14 into 'State Tax'!P27

Does that work?
Title: Re: Case Study Spreadsheet updates
Post by: gamedays on October 05, 2021, 04:24:55 PM
That highlighted the feature (or fixed the bug, tomato tomahto :)) perfectly! Thank you very much.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on October 05, 2021, 04:40:58 PM
That highlighted the feature (or fixed the bug, tomato tomahto :)) perfectly! Thank you very much.
Thanks for the feedback!

State Income Tax calculations - Crowdsourcing request (https://forum.mrmoneymustache.com/taxes/state-income-tax-calculations-crowdsourcing-request/) has other examples of state tax details that have (and have not) been added over time.
Title: Re: Case Study Spreadsheet updates
Post by: john6221 on October 28, 2021, 02:44:54 PM
Hi MDM. Thanks so much for putting so much effort into this spreadsheet - it looks great.

I would like to use it to estimate my taxes for 2021, but I'm a little confused about if I need to do something for RSUs. I'm a little bit of a newbie to doing my own taxes, but it appears that RSUs are reported as regular income. So it would seem that I don't need to do anything special when filling out this spreadsheet, other than making sure that I'm reporting everything as it would show up on my W-2. Does that sound right? Is there anything that I need to consider for the Box 12 Code V, " Income from exercise of non-statutory stock option(s)"?  I just wasn't sure if there was some threshold that triggered something that I didn't consider.

Thank you.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on October 28, 2021, 04:51:00 PM
So it would seem that I don't need to do anything special when filling out this spreadsheet, other than making sure that I'm reporting everything as it would show up on my W-2. Does that sound right?
Yes.

Quote
Is there anything that I need to consider for the Box 12 Code V, " Income from exercise of non-statutory stock option(s)"?
Not for the purposes of the spreadsheet.  That amount will already be included in your W-2 Box 1, so as far as the W-2 is concerned it's only for informational purposes.

But when you do your actual tax return, be sure to include that amount in the basis when you report the sale of those shares.  See http://www.ashmeadcpa.com/uploads/1/2/7/7/12773217/stock_options_-_same_day_sales.pdf and Your 1099-B form for an ESPP sale will probably be wrong (https://forum.mrmoneymustache.com/investor-alley/your-1099-b-form-for-an-espp-sale-will-probably-be-wrong/msg464676/#msg464676) for more on that.
Title: Re: Case Study Spreadsheet updates
Post by: Mrs. Green on November 06, 2021, 09:22:30 AM
@MDM I am so thankful you took the time to create Version 21.09 (2021).  Thank you so much!
Title: Re: Case Study Spreadsheet updates
Post by: wbyoung on November 12, 2021, 04:14:24 PM
@MDM this spreadsheet is great. Some users may have already mentioned the fact that functionality is limited within Google Sheets. The Tax Rate vs. tIRA Withdrawal graph is an example that I just bumped into. The source data appears to use a formula that isn't supported within Google Sheets.

I opened this up in the web version of Excel, but couldn't look much deeper because the web version doesn't let you unlock sheets. So all I see is that the source data for that graph (cells Q84:R584) all contain the "formula" {=TABLE(,D31)} which maybe is indicating that there's some even more advanced setup going on here since TABLE isn't even listed in the Excel function list I looked up.

I'm sure you're already aware that this spreadsheet is linked on the Bogleheads wiki (https://www.bogleheads.org/wiki/Tools_and_calculators#Personal_finance_toolbox). They do note that works best in Excel, but it's hosted on Google, so it gives options to open in Google Sheets. I wouldn't be surprised if others have opened it in Google Sheets as I had hoping it'd work quite well (and in fact, a lot does seem to work well).

I'd love to help in whatever way I can to make this more compatible with Google Sheets if you're open to it. If not, is there any insight you could share as to the functionality of the cells for that graph so I may be able to recreate something personally that works similarly to this?
Title: Re: Case Study Spreadsheet updates
Post by: secondcor521 on November 12, 2021, 05:06:06 PM
@MDM this spreadsheet is great. Some users may have already mentioned the fact that functionality is limited within Google Sheets. The Tax Rate vs. tIRA Withdrawal graph is an example that I just bumped into. The source data appears to use a formula that isn't supported within Google Sheets.

I opened this up in the web version of Excel, but couldn't look much deeper because the web version doesn't let you unlock sheets. So all I see is that the source data for that graph (cells Q84:R584) all contain the "formula" {=TABLE(,D31)} which maybe is indicating that there's some even more advanced setup going on here since TABLE isn't even listed in the Excel function list I looked up.

I'm sure you're already aware that this spreadsheet is linked on the Bogleheads wiki (https://www.bogleheads.org/wiki/Tools_and_calculators#Personal_finance_toolbox). They do note that works best in Excel, but it's hosted on Google, so it gives options to open in Google Sheets. I wouldn't be surprised if others have opened it in Google Sheets as I had hoping it'd work quite well (and in fact, a lot does seem to work well).

I'd love to help in whatever way I can to make this more compatible with Google Sheets if you're open to it. If not, is there any insight you could share as to the functionality of the cells for that graph so I may be able to recreate something personally that works similarly to this?

TABLE is probably a named range, not a function.  I'd be surprised if Google Sheets didn't support named ranges, though, so I sort of doubt that's the compatibility problem.

I think the curly braces make it a matrix function, but it's been a while since I've had to do advanced Excel stuff.

I'd also offer to make it compatible with LibreOffice, but I think my lazy workaround is going to be to go to my local public library where I can use Official (tm) Excel.

And I think it's a great resource and am very appreciative to @MDM and anyone else who has contributed to it.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on November 12, 2021, 05:44:26 PM
I'd love to help in whatever way I can to make this more compatible with Google Sheets if you're open to it.
I'd also offer to make it compatible with LibreOffice....
Happy to take whatever help either of you could provide.  Any Google Sheets knowledge? (https://forum.mrmoneymustache.com/ask-a-mustachian/any-google-sheets-knowledge/) and Any LibreOffice Calc knowledge? (https://forum.mrmoneymustache.com/ask-a-mustachian/any-libreoffice-calc-knowledge-86335/) didn't pan out, but that was a few years ago, so...?

Calculate multiple results by using a data table (https://support.microsoft.com/en-us/office/calculate-multiple-results-by-using-a-data-table-e95e2487-6ca6-4413-ad12-77542a5ea50b) is one source that describes what the Excel "Table" function does.
Title: Re: Case Study Spreadsheet updates
Post by: fdubz on December 20, 2021, 07:57:23 AM
@MDM, thank you so much for creating and updating these sheets!  I depend on them every year.  Do you have an estimate roll out for the 2022 sheet?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 20, 2021, 08:22:00 AM
@MDM, thank you so much for creating and updating these sheets!  I depend on them every year.  Do you have an estimate roll out for the 2022 sheet?
I was just thinking about this question yesterday. :)

Given all the uncertainty regarding which "only for 2021" tax changes would be extended to 2022, and that some 2021 things still aren't finalized, I'll probably wait until it appears the dust is settling in January.   Of course, Congress can change things whenever Congress wants to change things so...?  Best guess for the 2022 version at this point is "mid-January 2022" but if Congress is still embroiled in tax code debate, then...?
Title: Re: Case Study Spreadsheet updates
Post by: 2Muchfun on December 21, 2021, 07:30:34 AM
I imagine that this is my error. I am plugging in various Roth Conversion amounts and when I go from ~$60K AGI to ~$70K AGI, my Excess APTC tax jumps from $2700 to $20,112. Can you suggest what I might be doing wrong? This would be reasonable last year with the ACA Cliff, but shouldn't jump this year. I am using V21.09 version of the spreadsheet. TIA.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 21, 2021, 09:36:15 AM
I imagine that this is my error. I am plugging in various Roth Conversion amounts and when I go from ~$60K AGI to ~$70K AGI, my Excess APTC tax jumps from $2700 to $20,112. Can you suggest what I might be doing wrong? This would be reasonable last year with the ACA Cliff, but shouldn't jump this year. I am using V21.09 version of the spreadsheet. TIA.
You might check Harry Sit's excellent article, Roth Conversion and Capital Gains On ACA Health Insurance (https://thefinancebuff.com/tax-calculator-aca-obamacare-subsidy.html), that does a walk-through of the common inputs.

If that doesn't help, and comparing the Form 8962 section that starts in cell AC88 to how you would fill the 2021 version of 8962 (see https://www.irs.gov/pub/irs-dft/f8962--dft.pdf and https://www.irs.gov/pub/irs-dft/i8962--dft.pdf) doesn't shed any light, you could save a copy of your file to a google drive, dropbox, etc., and PM a link to me. 
Title: Re: Case Study Spreadsheet updates
Post by: 2Muchfun on December 21, 2021, 01:36:31 PM
Thanks. I missed an entry for SLCSP. All is good now.
Title: Re: Case Study Spreadsheet updates
Post by: BoonDogle on December 29, 2021, 12:54:58 PM
This is a great tool.  Many thanks for doing this MDM.
Title: Re: Case Study Spreadsheet updates
Post by: mangorunner on December 30, 2021, 03:38:27 PM
MDM,

I cannot thank you enough for this phenomenal spreadsheet.  I know that an incredible amount of (ongoing) work goes into building it, and I found it invaluable in forecasting and trying "what-if" scenarios for this year's taxes.  Thank you so much!
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 30, 2021, 05:02:16 PM
Quick question:  I am using your Version 21.09 and cannot find where self-employment taxes are added to total tax.  I am referring to Form 1040, Line 23 "Other taxes, including self-employment tax, from Schedule 2, line 21". 

I see SE tax calculated in cell D66.  I see Form 1040 Line 16-1040 Tax (cell G17) and "Tax after n-r credit" (cell G25) and Net tax (cell G33).  But I don't see my SE tax added into the Net Tax.  Would you please help?  Thanks!
That's a good point, and I've more or less been waiting for someone to ask about that before fixing it.  So now that you have, .... :)

The CSS pays some attention to the Form 1040 structure, but ignores things such as amounts withheld (except in the W-4 suggestion section starting in F57) that go into the refund or amount owed when filing.  The SE tax is included if one plots "Total" taxes in the marginal rate chart starting in cell F82, but it wouldn't hurt to add it to G33 also.

For a temporary fix (who knows?  This might be how the "permanent" fix is implemented) you could change the formulas in the cells below:
G33: =G25+G26+G27-G28-G29-G30-G31-G32-G21+D66
D67: =SUM(D60:D65)
T45: =D63+D64  (in other words, delete +D66)
I69: =D63  (in other words, delete +D66)
C66: =IF($R$80="Y",...  (in other words, just change "S80" to "$R$80" and keep the rest of that formula intact)

That should not affect the "Total tax marginal rate" curves but should give you a better G33 result.  Please let me know how it goes if you try it.

Calculations when there is more than one Schedule C in a return, whether that is multiple distinct businesses for one person or spouses each with a business, can get complicated (or at least I perceive they can get complicated).  There are differences in how cell B30 influences other cells vs. how C30 influences other cells.  Those differences may be sufficient for some situations with two Schedules C, but perhaps not all.  Anyone dealing with those complications in real life should probably use an accountant specializing in small business tax rather than use the CSS, but I'm open to incremental suggestions in that area.

Thanks and good luck!
Title: Re: Case Study Spreadsheet updates
Post by: mangorunner on December 30, 2021, 05:16:21 PM
Quick question:  I am using your Version 21.09 and cannot find where self-employment taxes are added to total tax.  I am referring to Form 1040, Line 23 "Other taxes, including self-employment tax, from Schedule 2, line 21". 

I see SE tax calculated in cell D66.  I see Form 1040 Line 16-1040 Tax (cell G17) and "Tax after n-r credit" (cell G25) and Net tax (cell G33).  But I don't see my SE tax added into the Net Tax.  Would you please help?  Thanks!
That's a good point, and I've more or less been waiting for someone to ask about that before fixing it.  So now that you have, .... :)

The CSS pays some attention to the Form 1040 structure, but ignores things such as amounts withheld (except in the W-4 suggestion section starting in F57) that go into the refund or amount owed when filing.  The SE tax is included if one plots "Total" taxes in the marginal rate chart starting in cell F82, but it wouldn't hurt to add it to G33 also.

For a temporary fix (who knows?  This might be how the "permanent" fix is implemented) you could change the formulas in the cells below:
G33: =G25+G26+G27-G28-G29-G30-G31-G32-G21+D66
D67: =SUM(D60:D65)
T45: =D63+D64  (in other words, delete +D66)
I69: =D63  (in other words, delete +D66)
C66: =IF($R$80="Y",...  (in other words, just change "S80" to "$R$80" and keep the rest of that formula intact)

That should not affect the "Total tax marginal rate" curves but should give you a better G33 result.  Please let me know how it goes if you try it.

Calculations when there is more than one Schedule C in a return, whether that is multiple distinct businesses for one person or spouses each with a business, can get complicated (or at least I perceive they can get complicated).  There are differences in how cell B30 influences other cells vs. how C30 influences other cells.  Those differences may be sufficient for some situations with two Schedules C, but perhaps not all.  Anyone dealing with those complications in real life should probably use an accountant specializing in small business tax rather than use the CSS, but I'm open to incremental suggestions in that area.

Thanks and good luck!

Oh, wow, and see, I just now deleted my question because I thought I finally figured out that "final tax' is down in D67, (not G33).  Thank you for the detailed options to correct. I'll be about it, now and get back to you. 

Two more questions while I have you here:

1)  Is the correct way (in your spreadsheet) to enter $300 in contributions (for a Form 1040 Line 12b deduction), to enter $25 in cell B86?

2)  I earn some wages as an election/poll worker that are not subject to Social Security and Medicare (FICA) Withholding (https://www.irs.gov/government-entities/federal-state-local-governments/election-workers-reporting-and-withholding).  I found that putting them in cell B3 doesn't work (even though they are Box 1 wages on a W-2).  Should I put those in cell D40?  (That's what I did.)

Thanks, again, for all the help!  You are a godsend.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 30, 2021, 05:49:42 PM
By the way, welcome to the forum mangorunner.

1)  Is the correct way (in your spreadsheet) to enter $300 in contributions (for a Form 1040 Line 12b deduction), to enter $25 in cell B86?

2)  I earn some wages as an election/poll worker that are not subject to Social Security and Medicare (FICA) Withholding (https://www.irs.gov/government-entities/federal-state-local-governments/election-workers-reporting-and-withholding).  I found that putting them in cell B3 doesn't work (even though they are Box 1 wages on a W-2).  Should I put those in cell D40? 
1) Yes
2) Yes, that will work.  Poll worker wages are an odd thing tax-wise.  They aren't subject to FICA but they do count as earned income for the earned income credit and can be used to justify IRA contributions.  If the earned income credit is of interest, doing odd things in the spreadsheet such as entering the poll worker wages as a negative number in cell B13 will work.  Otherwise just do what you did. :)

Title: Re: Case Study Spreadsheet updates
Post by: mangorunner on December 30, 2021, 06:50:45 PM
By the way, welcome to the forum mangorunner.

1)  Is the correct way (in your spreadsheet) to enter $300 in contributions (for a Form 1040 Line 12b deduction), to enter $25 in cell B86?

2)  I earn some wages as an election/poll worker that are not subject to Social Security and Medicare (FICA) Withholding (https://www.irs.gov/government-entities/federal-state-local-governments/election-workers-reporting-and-withholding).  I found that putting them in cell B3 doesn't work (even though they are Box 1 wages on a W-2).  Should I put those in cell D40? 
1) Yes
2) Yes, that will work.  Poll worker wages are an odd thing tax-wise.  They aren't subject to FICA but they do count as earned income for the earned income credit and can be used to justify IRA contributions.  If the earned income credit is of interest, doing odd things in the spreadsheet such as entering the poll worker wages as a negative number in cell B13 will work.  Otherwise just do what you did. :)

Thanks for the welcome to the forum (I'm a long-time lurker!) and your confirmation on those last two questions. 
Title: Re: Case Study Spreadsheet updates
Post by: mangorunner on December 30, 2021, 07:02:46 PM
Quick question:  I am using your Version 21.09 and cannot find where self-employment taxes are added to total tax.  I am referring to Form 1040, Line 23 "Other taxes, including self-employment tax, from Schedule 2, line 21". 

I see SE tax calculated in cell D66.  I see Form 1040 Line 16-1040 Tax (cell G17) and "Tax after n-r credit" (cell G25) and Net tax (cell G33).  But I don't see my SE tax added into the Net Tax.  Would you please help?  Thanks!
That's a good point, and I've more or less been waiting for someone to ask about that before fixing it.  So now that you have, .... :)

The CSS pays some attention to the Form 1040 structure, but ignores things such as amounts withheld (except in the W-4 suggestion section starting in F57) that go into the refund or amount owed when filing.  The SE tax is included if one plots "Total" taxes in the marginal rate chart starting in cell F82, but it wouldn't hurt to add it to G33 also.

For a temporary fix (who knows?  This might be how the "permanent" fix is implemented) you could change the formulas in the cells below:
G33: =G25+G26+G27-G28-G29-G30-G31-G32-G21+D66
D67: =SUM(D60:D65)
T45: =D63+D64  (in other words, delete +D66)
I69: =D63  (in other words, delete +D66)
C66: =IF($R$80="Y",...  (in other words, just change "S80" to "$R$80" and keep the rest of that formula intact)


I'm pretty good at Excel (not nearly as good as you, though) and I understand your formula changes.  However, I got tremendous use out of your spreadsheet and everything I needed, so I decided not to change any of your formulas.  Your CSS allowed me to pinpoint my Roth IRA conversion down to the last cent, so thank you very much.  You are my hero.

(It may be worth noting that in the last line of your instructions, above, I think you mean B66 rather than C66?)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on December 30, 2021, 08:48:23 PM
I'm pretty good at Excel (not nearly as good as you, though) and I understand your formula changes.  However, I got tremendous use out of your spreadsheet and everything I needed, so I decided not to change any of your formulas.  Your CSS allowed me to pinpoint my Roth IRA conversion down to the last cent, so thank you very much.  You are my hero.
You are welcome!

Quote
(It may be worth noting that in the last line of your instructions, above, I think you mean B66 rather than C66?)
Ha!  That's what comes from having accumulated a bunch of changes while waiting for Congress and the IRS to make up their minds about 2022 and even 2021 tax laws.  The formula in C66 should be identical to what is in B66, just referencing different columns - except for the first "if" test that should reference the same cell.  Version 21.10 (in development, not yet released, and what I was looking at to address your question) includes some changes to address the "multiple Schedules C" issue mentioned earlier, and had all the C66 calculations correct but the first "if" test incorrect.  Would I have caught that when going through normal "new release due diligence" without looking at the changes your question prompted?  Don't know but thanks for the prompt!
Title: Re: Case Study Spreadsheet updates
Post by: mangorunner on December 30, 2021, 08:54:51 PM
Aw, that's fantastic!  I'm glad you got something good out of it, too!  :)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 03, 2022, 01:23:46 AM
Version 21.10 (2021)
Although the IRS hasn't released all the 2021 forms and publications (in particular, Publication 974, Premium Tax Credit (https://www.irs.gov/forms-pubs/about-publication-974) that I'd like to use for checking the iterative Self-Employed Health Insurance plus Affordable Care Act calculations), there don't appear to have been any significant changes needed in tax calculations from the previous version.  This release cleans up a few things (see above) and will make the next one (whether for 2021 or 2022) that much less of a chore to check for unintended changes.

Most of the bulleted points should be straightforward enough (if not, just ask here or via PM), but "Keep endpoint constant on the marginal rate chart as the initial point changes" probably deserves explanation.  Cell Calculations!P83 is the increment used for the marginal rate chart's X axis, and Calculations!P84 is the starting value for the X axis variable.  If one wants to look at a larger range for the X axis variable, just increase the P83 value.  But if one wants to keep the same ending X variable value while changing the starting value, the formula in P83 (=220-P84/500) does that.  In other words, if P84 is changed from the default of zero, the increment will decrease to keep the same ending value.  The "500" comes from there being 500 points on the chart.  If one wants to keep this functionality but wants to change the ending value, edit cell P83 and change "220" to whatever increment gives the desired result.  If one forgets there is an equation in P83 and simply enters a number, things will still work the same way they have in previous versions. 

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: skyFIdive on January 19, 2022, 10:57:46 AM
Hey @MDM, do you have any update on when the 2022 version will be launched? Debating just adapting my old 2021 version to include confirmed 2022 changes (e.g. 401k limit increase), but figure I’ll probably miss something. Happy to use any draft version you may have! Thank you again for this incredible tool.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 19, 2022, 11:06:37 AM
Hey @MDM, do you have any update on when the 2022 version will be launched? Debating just adapting my old 2021 version to include confirmed 2022 changes (e.g. 401k limit increase), but figure I’ll probably miss something. Happy to use any draft version you may have! Thank you again for this incredible tool.
I had hoped there would be more clarity out of Congress on which of the significant "2021 only" (e.g., Child Tax Credit, Child/Dependent Care Credit, etc.) tax law changes would in fact be made permanent (or at least extended) through 2022.  But the political gameplaying continues, with no such clarity in evidence....

I suppose if they are still posturing instead of legislating by February I'll bite the bullet and release a "subject to change..." version.
Title: Re: Case Study Spreadsheet updates
Post by: skyFIdive on January 19, 2022, 12:34:14 PM
Okay thanks for the update! Will stay tuned.
Title: Re: Case Study Spreadsheet updates
Post by: john6221 on January 22, 2022, 06:28:08 PM
Hey MDM, I love this spreadsheet. One discrepancy that I'm seeing in the calculation for schedule 8812. The Line 5 calculation, according to the IRS website (https://www.irs.gov/instructions/i1040s8#en_US_2021_publink100077560) is giving me a $29 dollar difference from your spreadsheet. It's not a huge difference but I'm just trying to understand why. Unfortunately I can't really follow the cell calculation well enough to figure out the difference.

Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 22, 2022, 06:34:45 PM
Hey MDM, I love this spreadsheet. One discrepancy that I'm seeing in the calculation for schedule 8812. The Line 5 calculation, according to the IRS website (https://www.irs.gov/instructions/i1040s8#en_US_2021_publink100077560) is giving me a $29 dollar difference from your spreadsheet. It's not a huge difference but I'm just trying to understand why. Unfortunately I can't really follow the cell calculation well enough to figure out the difference.
If you could PM me (or post here) your inputs, I'd be happy to take a look.
Title: Re: Case Study Spreadsheet updates
Post by: john6221 on January 22, 2022, 07:57:40 PM
Hey MDM, I love this spreadsheet. One discrepancy that I'm seeing in the calculation for schedule 8812. The Line 5 calculation, according to the IRS website (https://www.irs.gov/instructions/i1040s8#en_US_2021_publink100077560) is giving me a $29 dollar difference from your spreadsheet. It's not a huge difference but I'm just trying to understand why. Unfortunately I can't really follow the cell calculation well enough to figure out the difference.
If you could PM me (or post here) your inputs, I'd be happy to take a look.

Thanks, I appreciate you taking a look. Here is the info for inputs:

Schedule 8812, Part 1-A
Line 1: 189430
Line 4a: 2
Line 4b: 2
Line 13: A checked, B unchecked

Schedule 8812, Part 1-B
Line f: 3600

Please let me know if that's all of the info that you need.

According to my calculations, for Part 1-A, Line 5, I get a result of $5200, while the Case Study spreadsheet gets $5229.

Thanks!

Title: Re: Case Study Spreadsheet updates
Post by: secondcor521 on January 22, 2022, 08:16:42 PM
Hey MDM, I love this spreadsheet. One discrepancy that I'm seeing in the calculation for schedule 8812. The Line 5 calculation, according to the IRS website (https://www.irs.gov/instructions/i1040s8#en_US_2021_publink100077560) is giving me a $29 dollar difference from your spreadsheet. It's not a huge difference but I'm just trying to understand why. Unfortunately I can't really follow the cell calculation well enough to figure out the difference.
If you could PM me (or post here) your inputs, I'd be happy to take a look.

Thanks, I appreciate you taking a look. Here is the info for inputs:

Schedule 8812, Part 1-A
Line 1: 189430
Line 4a: 2
Line 4b: 2
Line 13: A checked, B unchecked

Schedule 8812, Part 1-B
Line f: 3600

Please let me know if that's all of the info that you need.

According to my calculations, for Part 1-A, Line 5, I get a result of $5200, while the Case Study spreadsheet gets $5229.

Thanks!

Assuming you're MFJ and Schedule 8812 line 3 is the same as line 1 (which are both probable), then my results basically match the CSS (depending on rounding).  My Line 5 worksheet results are:

1   7200
2   0
3   7200
4   4000
5   3200
6   12500
7   3200
8   150000
9   39430
10   1971.5
11   1971.5
12   5228.5
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 22, 2022, 08:24:16 PM
Thanks for the question.  It is a great example of "it depends what you want to see," and cell Calculations!R80 controls that.

In case anyone else wants to look, putting 189430 in cell B3, and 2 in cells G2 through G7 will reproduce john6221's question.

If you change cell R80 from "Y" to "N", you should see the correct result of $5200.  But then look at the marginal rate chart and all those spikes for the first ~$24K of tIRA withdrawals/conversions.

The note for cell R80 is "Use Y for a "clean" graph (at left) and continuous derivatives.  Anything else (e.g., N) will use actual tax calculations that can have discontinuous derivatives."

The default is "Y" because the clean marginal rate chart seems, in general, the more useful output.  But if one wants to see exactly what the tax code does, "N" will do that.
Title: Re: Case Study Spreadsheet updates
Post by: john6221 on January 22, 2022, 09:11:29 PM
All makes sense to me now. Thank you for the clarification!


Sent from my iPhone using Tapatalk
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 30, 2022, 10:36:53 PM
Version 21.10 (2021)
Version 22.00 (2022)
The 2021 version appears to match all the TaxSlayer (several of the Tax-Aide Workbook exercises) and TurboTax (our own return) comparisons I've done, so that's good.

The 2022 version, as previous posts have mentioned, has to be considered "subject to change as Congress does whatever Congress will do" this year.  That's always true, but perhaps more so this year than most.  We'll see....

To prepare for changes, all of the inflation-indexed numbers (and some of the tax law affected ones) are gathered in the 'Tax Code' sheet.  The corresponding HLOOKUPs needed to bring those to the 'Calculations' sheet may be slowing calculations a bit so this strategy may or may not persist. 

If it is working as designed, setting cell Calculation!R2 to 2021 should provide the same results in the 2022 version for 2021 taxes as the 2021 version calculates.  Of course, setting that cell to 2022 would be the usual mode for the 2022 version.

Still haven't seen a 2021 Pub. 974, so I'm not 100% sure the CSS can be used to get an accurate-to-the-dollar answer to the iterative calculations needed when one has both self-employed health insurance (SEHI) and premium tax credits (PTC) with an Affordable Care Act policy - at least if one's tax situation is covered by the CSS - but it is looking good. 

Another task it may be able to do - although it will take a little jury-rigging - is determine the amount of unrestricted scholarship money that should be declared as income in order to increase the American Opportunity Tax Credit and have a net tax saving.

More on both the SEHI/PTC iteration and the education credit hacking in future versions.

As always (and particularly after major changes such the 2022 version), comments/suggestions/bug identifications/etc. are appreciated.  Either PM me or drop a note here.

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


Note:
Title: Re: Case Study Spreadsheet updates
Post by: dandarc on January 31, 2022, 09:31:56 AM
Hurray for 2022!

Is there a way built-in to enter the energy-efficiency credit? I'm expecting to probably owe negative income tax to the extent we get a PTC for 2022, in large part due to the tax credit on our new solar panels they turned on earlier this month.

Off the bat, I think I could just write over the foreign tax credit in G21?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on January 31, 2022, 12:15:23 PM
Off the bat, I think I could just write over the foreign tax credit in G21?
That would work perfectly if you have no other non-refundable credits (see https://www.irs.gov/pub/irs-pdf/f1040s3.pdf).  There is a hierarchy to those credits in that the ones further down the list on Schedule 3 become worthless if the ones higher on the list have taken tax liability to zero.

Coding a full Schedule 3 with all the hierarchical limits is a bit more than I've contemplated, but maybe a catch-all "line 5 and below" item would suffice....
Title: Re: Case Study Spreadsheet updates
Post by: dandarc on January 31, 2022, 01:52:36 PM
Off the bat, I think I could just write over the foreign tax credit in G21?
That would work perfectly if you have no other non-refundable credits (see https://www.irs.gov/pub/irs-pdf/f1040s3.pdf).  There is a hierarchy to those credits in that the ones further down the list on Schedule 3 become worthless if the ones higher on the list have taken tax liability to zero.

Coding a full Schedule 3 with all the hierarchical limits is a bit more than I've contemplated, but maybe a catch-all "line 5 and below" item would suffice....
Knocking on all the wood - I think 2021 was the last year to feature the Lifetime Learning Credit for a good while. Wife should graduate this at the end of April, which we paid for before year end (knocking on wood again).

Reading through all that and looking at the most relevant form to me - I don't think I'd attempt to get this into the spreadsheet either. One of the reasons I pulled the trigger on the panels finally is this tax credit is going to not only offset our entire tax bill this year, the carryforward is likely to offset a good chunk or all of next year too. Even more so if they somehow get something through congress that bumps 26% to 30%.
Title: Re: Case Study Spreadsheet updates
Post by: Mrs Brightside on February 16, 2022, 12:37:35 PM
This is probably a tax ignorance problem rather than a spreadsheet problem, but I was surprised that my backdoor Roth contribution increased the taxes due. For "tIRA distribution (converted to Roth or not)" in Calculations cell D31, I put 12000 total Annual. I also filled in the sheet for form 8606, not sure whether that feeds into the Calculations tab or is totally separate. For 8606, I have this: (we did the contribution and the conversion in 2021)

Year:   2021
31-Dec-2020 tIRA basis (after-tax amount in all tIRAs) 0
Contributions made for this tax year in this calendar year   
Pre-tax   0
Post-tax   12000

Distributions made in this calendar year   
Not converted to Roth   0
Converted to Roth   12000
31-Dec tIRA balance   0


End result was to increase my Net Tax by $4440. Fair warning, I am using the spreadsheet in Google sheets so I understand macros might be broken e.g. the total tax rate chart update button.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 16, 2022, 12:49:20 PM
This is probably a tax ignorance problem rather than a spreadsheet problem, but I was surprised that my backdoor Roth contribution increased the taxes due. For "tIRA distribution (converted to Roth or not)" in Calculations cell D31, I put 12000 total Annual. I also filled in the sheet for form 8606, not sure whether that feeds into the Calculations tab or is totally separate. For 8606, I have this: (we did the contribution and the conversion in 2021)

Year:   2021
31-Dec-2020 tIRA basis (after-tax amount in all tIRAs) 0
Contributions made for this tax year in this calendar year   
Pre-tax   0
Post-tax   12000

Distributions made in this calendar year   
Not converted to Roth   0
Converted to Roth   12000
31-Dec tIRA balance   0


End result was to increase my Net Tax by $4440. Fair warning, I am using the spreadsheet in Google sheets so I understand macros might be broken e.g. the total tax rate chart update button.
Thanks, good feedback for how one could interpret that line.

Based on that, the note in cell A31 will read "Use this for any taxable traditional withdrawal from an IRA, 401k, etc., whether converted to Roth or not.  This does not include the non-taxable amounts from a backdoor Roth process.  Use line 55, Roth IRA, for those amounts" when a new version is released.

The Form8606 sheet is completely separate from the Calculations sheet.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 22, 2022, 02:27:17 AM
Version 22.01 (2022)
Thanks to dandarc and Mrs Brightside for their suggestions that have been incorporated in this version.  So far no errors have surfaced, so this release is all new functionality and documentation.

As a reminder, setting cell Calculation!R2 to 2021 should provide the same results in the 2022 version for 2021 taxes as the 2021 version calculates.  One could do that to check whether this tool gives a "close enough" 2021 calculation when compared with your commercial tax software of choice.  If so, there is a good chance it will provide (to the extent income is predicted correctly) a good estimate of 2022 tax liability when R2 = 2022.

Here's the "More on both the SEHI/PTC iteration and the education credit hacking in future versions" mentioned in the last update.
------------------------------
I'll use Harry Sit's 2021 Self-Employed ACA Health Insurance Deduction In TurboTax (https://thefinancebuff.com/self-employed-health-insurance-aca-taxes.html) article for an SEHI/PTC example.
          (https://lh3.googleusercontent.com/d/1ulezUEO2S6diIfvtKjmVDOgGhHf2vmDS)
That should give the result of $1,388 for the SEHI deduction, and a total PTC (cell AD102) of $4,612, same as in the linked example.  Deleting the formula in B48 and entering 1388 as a value shouldn't change the PTC, and thus confirm the calculations.

Things get trickier if the selected premium is $498/mo instead of $500/mo.  Change B114 to 498, put =AD159 into B48, and the SEHI result is $1,377 with a total PTC of $4,612.  This is not a legal result because $1377+$4612=$5989 and that is more than $498*12=$5976.  At least it's closer than what TurboTax calculates (see comment #3 in the linked article).  But there is a way to get the exact best legal answer:
------------------------------

See p. 16-17 of Pub. 970, and the Education Benefits Calculator (https://cotaxaide.org/tools/Education%20Calculator.html) tool page, for background on choosing to make some types of college scholarship money taxable in order to receive more in education credits than the additional tax on that amount.

Picking up where the SEHI/PTC example left off (i.e., $498/mo premium), assume the filer has $6000 in qualified education expenses and a $4000 unrestricted (e.g., it can be used for room & board in addition to tuition) scholarship.  This would be simpler without the SEHI/PTC complication, but let's do both at once:
In words, what the above accomplished was to check every $8 increment of taxable scholarship money from $0 to $4000 (there are ~500 points in the chart) for the lowest overall tax bill, including all credits.  Assuming $1,896 is close to "the middle of the ballpark", now try P84 = 1650 and P83 = 1.  That refines the answer to $1,899. 

The iterated SEHI amount comes to $1618, but that is not a converged solution, as one can verify using the SEHI/PTC procedure above.  The dollar-by-dollar test gives $1612 for the SEHI amount.

Cells P76:R78, along with this write-up, can help as a guide for what needs doing on both the SEHI/PTC and education credit issues.
------------------------------

Don't know if anyone will use either of these (let along both at the same time), but maybe....

As always, comments/suggestions/bug identifications/etc. are appreciated.  Either PM me or drop a note here.

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


Note:
Title: Re: Case Study Spreadsheet updates
Post by: noppenbd on February 22, 2022, 06:56:37 AM
Love the case study spreadsheet.  Is it possible to add C47 (spouse SE,SEP) to the X-axis options for the tax rate graph?  Thanks!
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 22, 2022, 01:06:01 PM
Love the case study spreadsheet.  Is it possible to add C47 (spouse SE,SEP) to the X-axis options for the tax rate graph?  Thanks!
noppenbd, welcome to (at least your first post on) the forum.

There are two ways to do this:
1. Swap your definition of "Earner #1" and "Earner #2" and then use B47 for the X-axis. :)
2. Excel will allow you to bypass the data validation if, instead of trying to type C47 into G107, you enter C47 into another cell and then copy that into G107.  E.g., type C47 into cell B107 (yes, that makes no sense by itself, but wait...) then copy B107 into G107, click the "Update chart" button, and go back and delete the B107 entry.  The chart title will now say "... vs. Something" but you'll know what that Something is and the chart numbers/curves will be correct.
Title: Re: Case Study Spreadsheet updates
Post by: mmm999 on February 28, 2022, 02:54:53 PM
Hi MDM,
This is my first post.  I have question about Line 10 Form 8812 (cell Y190 in the 2021 spreadsheet).  The IRS instructions for line 10 is

"Subtract line 9 from line 3.
• If zero or less, enter -0-.
• If more than zero and not a multiple of $1,000, enter the next multiple of $1,000. For
example, if the result is $425, enter $1,000; if the result is $1,025, enter $2,000, etc.
"


Currently, cell Y190 has
     =MAX(0,Y181-Y189)

should it be something like
    =MAX(0,IF(Y181>Y189,CEILING(Y181-Y189,1000)))
to match with the instructions?
Thank you.
     
Title: Re: Case Study Spreadsheet updates
Post by: MDM on February 28, 2022, 04:38:04 PM
Currently, cell Y190 has
     =MAX(0,Y181-Y189)

should it be something like
    =MAX(0,IF(Y181>Y189,CEILING(Y181-Y189,1000)))
to match with the instructions?
mmm999, welcome to the forum and thanks for the observation.

Yes, for those of you filing MFJ with AGI>$400K or single/HOH with AGI>$200K and wanting an exact tax calculation, something like that is needed.  E.g., see cell Y185.

See replies #299-#304 in this thread for some discussion about exact tax calculations for specific inputs vs. smooth marginal rate charts for a range of values.

Shouldn't be hard to add similar "If smooth curves desired, don't use the CEILING function, otherwise do" logic for the next release.
Title: Re: Case Study Spreadsheet updates
Post by: mmm999 on March 03, 2022, 07:04:22 PM
Hi MDM,
Thanks for your answer.  I looked at replies #299-#304 and realized that I also had discrepancy on line 5 Form 8812 (cell Y185) too.  For my case, however, changing smoothing value did not help.

H&R Block: $2000
CSS No smoothing:  $1950
CSS Yes smoothing:  $1993

Here are my values for Form 8812
Line 1: 400144
Line 3: 400144
Line 4a: 1
Line 4b: 0

Thanks.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on March 03, 2022, 11:10:25 PM
LOL!  mmm999, you may be the only person with AGI > $400K and Child Tax Credits looking at this calculation (that may disappear after 2021), but I sincerely appreciate the feedback and will investigate.  Should be a quick fix once I get time but that may not be until the weekend or early next week. 
Title: Re: Case Study Spreadsheet updates
Post by: mmm999 on March 04, 2022, 08:54:00 AM
LOL!  mmm999, you may be the only person with AGI > $400K and Child Tax Credits looking at this calculation (that may disappear after 2021), but I sincerely appreciate the feedback and will investigate.  Should be a quick fix once I get time but that may not be until the weekend or early next week. 

First attempt doing Roth conversion to top 24% + LTCG which pushed my AGI > $400K.  I was aiming for <= $400K to maximize the CTC but forgot to add foreign tax $ to my income estimate.  This resulted in a loss of $50 tax credit or $50/$144= 44% tax rate on my last $114 ($400,144 - $400,000) conversion 8-(.  Lesson learned!

No rush.  I love your spreadsheet and greatly appreciate your time and effort.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on March 08, 2022, 05:48:19 PM
Version 22.02 (2022)
Version 21.12 (2021)
Thanks to mmm999 for noticing the 8812 issue. 

As always, comments/suggestions/bug identifications/etc. are appreciated.  Either PM me or drop a note here.

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


Note:
Title: Re: Case Study Spreadsheet updates
Post by: Paul der Krake on April 03, 2022, 07:01:01 PM
Feature request!

In California there is a 1.1% disability tax on wage income, up to the usual social security cap of $145k and some change. Washington also has 0.58% flat tax on wage income, but this one has no cap. I realize it'd be impractical to model every single county and city taxes out there, but perhaps focusing on the statewide ones is doable?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on April 03, 2022, 07:30:58 PM
Feature request!

In California there is a 1.1% disability tax on wage income, up to the usual social security cap of $145k and some change. Washington also has 0.58% flat tax on wage income, but this one has no cap. I realize it'd be impractical to model every single county and city taxes out there, but perhaps focusing on the statewide ones is doable?
Those might be doable, especially if you could provide/confirm some specific Excel cell formulas.  E.g., I got a private request for a (simplified) MI property tax credit calculation, including cell formulas and comments, so that will probably make it into the next version.

On the 'State Tax' tab would those CA and WA taxes be =0.011*MIN(E8,some_cap) and =0.0058*E8 respectively?
Title: Re: Case Study Spreadsheet updates
Post by: Paul der Krake on April 04, 2022, 08:11:06 AM
Feature request!

In California there is a 1.1% disability tax on wage income, up to the usual social security cap of $145k and some change. Washington also has 0.58% flat tax on wage income, but this one has no cap. I realize it'd be impractical to model every single county and city taxes out there, but perhaps focusing on the statewide ones is doable?
Those might be doable, especially if you could provide/confirm some specific Excel cell formulas.  E.g., I got a private request for a (simplified) MI property tax credit calculation, including cell formulas and comments, so that will probably make it into the next version.

On the 'State Tax' tab would those CA and WA taxes be =0.011*MIN(E8,some_cap) and =0.0058*E8 respectively?
As far as I understand it, both of these taxes are assessed on gross wages, just like FICA, whereas E8 links to W-2 Box 1.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on April 04, 2022, 01:25:54 PM
Feature request!

In California there is a 1.1% disability tax on wage income, up to the usual social security cap of $145k and some change. Washington also has 0.58% flat tax on wage income, but this one has no cap. I realize it'd be impractical to model every single county and city taxes out there, but perhaps focusing on the statewide ones is doable?
Those might be doable, especially if you could provide/confirm some specific Excel cell formulas.  E.g., I got a private request for a (simplified) MI property tax credit calculation, including cell formulas and comments, so that will probably make it into the next version.

On the 'State Tax' tab would those CA and WA taxes be =0.011*MIN(E8,some_cap) and =0.0058*E8 respectively?
As far as I understand it, both of these taxes are assessed on gross wages, just like FICA, whereas E8 links to W-2 Box 1.
Looking at DB101 California - California State Disability Insurance (SDI): Eligibility and Application (https://ca.db101.org/ca/programs/income_support/sdi/program2.htm) it says "Your paycheck is $1,000 before taxes. Every time you get a paycheck, 1.1% of that goes automatically to SDI."  Usually the "paycheck" amount goes to W-2 Box 1, because employer-based HSA, medical insurance, etc., have already been subtracted.

New State Employee Payroll Tax Law for Long-Term Care Benefits - Washington State Hospital Association (https://www.wsha.org/articles/new-state-employee-payroll-tax-law-for-long-term-care-benefits/) refers to "wages reported on a Form W-2" which also implies W-2 box 1.

Those articles also note many reasons why a W-2 wage earner would not be subject to the taxes, so if they get added to the CSS it will be (same as I'll do for the MI credit) with a "*0" at the end so users will have to take action beyond entering the state ID to included the calculation.

All I did, however, was quickly skim those two linked articles.  Does a more detailed study lead to a different conclusion?

Title: Re: Case Study Spreadsheet updates
Post by: Paul der Krake on April 04, 2022, 07:29:59 PM
Regarding California: it's on gross wages, not after deductions. I have confirmed this empirically by looking at a recent CA paycheck with a sizeable 401(k) contribution where the amount withheld is exactly 1.1% of the gross wages.

Regarding WA: oh boy it's complicated. I'm pretty sure it's also on gross wages BUT it turns out there's not one but two separate taxes in different life stages, with different rules.

All this to say: perhaps instead of opening this can of worms, the best way forward is to provide a free-form row (around F37?) where users can enter their own formula.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on April 04, 2022, 07:56:44 PM
Regarding California: it's on gross wages, not after deductions. I have confirmed this empirically by looking at a recent CA paycheck with a sizeable 401(k) contribution where the amount withheld is exactly 1.1% of the gross wages.

Regarding WA: oh boy it's complicated. I'm pretty sure it's also on gross wages BUT it turns out there's not one but two separate taxes in different life stages, with different rules.

All this to say: perhaps instead of opening this can of worms, the best way forward is to provide a free-form row (around F37?) where users can enter their own formula.
Can you tell if in CA it is wages before any deductions (including medical insurance, etc.)?

In any case, providing free form calculation space seems a good idea.  E.g., it would be easy enough to make cells X23:AG73 on the 'State Tax' tab editable, with column AB taken as a refundable credit and column AG as an additional tax.  The specific state result would go via cells H17 and I17 to B19 and then to Calculations!G37.  Seem reasonable?

Of course, one can always remove sheet protection and edit anything....
Title: Re: Case Study Spreadsheet updates
Post by: Paul der Krake on April 04, 2022, 08:45:38 PM
Can you tell if in CA it is wages before any deductions (including medical insurance, etc.)?
Yep, it's unambiguously before medical/dental/FSA/401(k) deductions. This is from a leading payroll provider so I'd bet good money that they have this right. I think it's safe to assume any other type of deduction would be ignored as well.

In any case, providing free form calculation space seems a good idea.  E.g., it would be easy enough to make cells X23:AG73 on the 'State Tax' tab editable, with column AB taken as a refundable credit and column AG as an additional tax.  The specific state result would go via cells H17 and I17 to B19 and then to Calculations!G37.  Seem reasonable?
Yes!
Title: Re: Case Study Spreadsheet updates
Post by: Telecaster on April 04, 2022, 08:57:01 PM
Washington also has 0.58% flat tax on wage income, but this one has no cap. I realize it'd be impractical to model every single county and city taxes out there, but perhaps focusing on the statewide ones is doable?

FWIW, that tax has been delayed until July 2023 (and I'm guessing might die before then) and there are ways to opt out as well.   
Title: Re: Case Study Spreadsheet updates
Post by: Jason95357 on April 12, 2022, 08:55:45 PM
Regarding California: it's on gross wages, not after deductions. I have confirmed this empirically by looking at a recent CA paycheck with a sizeable 401(k) contribution where the amount withheld is exactly 1.1% of the gross wages.

Regarding WA: oh boy it's complicated. I'm pretty sure it's also on gross wages BUT it turns out there's not one but two separate taxes in different life stages, with different rules.

All this to say: perhaps instead of opening this can of worms, the best way forward is to provide a free-form row (around F37?) where users can enter their own formula.
Can you tell if in CA it is wages before any deductions (including medical insurance, etc.)?

In any case, providing free form calculation space seems a good idea.  E.g., it would be easy enough to make cells X23:AG73 on the 'State Tax' tab editable, with column AB taken as a refundable credit and column AG as an additional tax.  The specific state result would go via cells H17 and I17 to B19 and then to Calculations!G37.  Seem reasonable?

Of course, one can always remove sheet protection and edit anything....

Looking at my returns, California's "Schedule 540 (CA)" starts with the Federal income and itemized deductions, and has a columns for subtractions and additions.  Without adding customization for each state, the simplest thing would be to provide a free form that allowed amounts to be added/subtracted from the Federal income and itemized deductions.  Personally, I'd then customize my own document and make those free form field to calculate the exact income additions/subtractions and deductions additions/subtractions.

The CA tax form is here:
https://www.ftb.ca.gov/forms/2021/2021-540-ca.pdf (https://www.ftb.ca.gov/forms/2021/2021-540-ca.pdf)
Title: Re: Case Study Spreadsheet updates
Post by: mangorunner on April 27, 2022, 10:12:49 PM
Thanks for this thing of BEAUTY!

Using Version V22.02 dated 03/08/2022.  One minor thing I noticed:
Cells AD98 and AE98 are locked (even though green).  I do realize they can be unlocked manually.

Thanks again for the tremendous effort you have put into this extremely helpful planning tool!
Title: Re: Case Study Spreadsheet updates
Post by: mangorunner on April 27, 2022, 10:34:49 PM
Another item:

Cell AD100 is using a full-year amount (12 months) even if "No" is entered in cell AD98 and number of months in cell AE98.  Of course, it's easy enough to game the system (spreadsheet) to get around that.

Same with cell AI100.  It's also using a full-year amount. 

If we only have 11 months of coverage (registered in January 2022; coverage started on Feb 1), should we be taking the total of 11 months and dividing that total by 12 to enter a monthly amount in Column B (cells B114 and B115)?  That's how I'm gaming the system to get it to work right now.  Thanks in advance for your advice.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on April 27, 2022, 11:27:45 PM
Thanks for this thing of BEAUTY!

Using Version V22.02 dated 03/08/2022.  One minor thing I noticed:
Cells AD98 and AE98 are locked (even though green).  I do realize they can be unlocked manually.

Thanks again for the tremendous effort you have put into this extremely helpful planning tool!
Thanks - easy fix (already done) for the next release.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on April 27, 2022, 11:58:05 PM
Another item:

Cell AD100 is using a full-year amount (12 months) even if "No" is entered in cell AD98 and number of months in cell AE98.  Of course, it's easy enough to game the system (spreadsheet) to get around that.

Same with cell AI100.  It's also using a full-year amount. 

If we only have 11 months of coverage (registered in January 2022; coverage started on Feb 1), should we be taking the total of 11 months and dividing that total by 12 to enter a monthly amount in Column B (cells B114 and B115)?  That's how I'm gaming the system to get it to work right now.  Thanks in advance for your advice.
This one is less clear, because there can be a conflict between using the CSS for cash flow analysis vs. tax estimation.  Yes, it would be nice if those goals always coincide :) and see below for how we might do this.

One way the CSS is tested is by comparing its results to the annual Tax-Aide Workbook problem answers.  Don't recall offhand what I did to get a "less than 12 month" 1095-A situation to match, but it may have been exactly as you are "gaming it."  Or maybe it didn't matter because...?

Anyway, at a quick glance the following changes might allow you to enter your actual monthly cash flow numbers in B114 and B115 while also matching what will be on your 2022 1095-A.  Could you test and let me know?

AD100:  =IF(AD94="Yes","",IF(AD98="Yes",D114,AE98/12*D114))
AI100:   =IF(AD98="Yes",-D115,-AE98/12*D115)
You would also have to enter 11/12 of the annual SLCSP in AE100 to match what the 1095-A will show, because there is no formula in that cell - it's a pure input.

That could fit your situation of expecting ACA premiums to continue.  For someone expecting ACA coverage to stop later in the year (e.g., anticipating a shift to employer-provided coverage), however, that may not be appropriate.  Any thoughts on that?
Title: Re: Case Study Spreadsheet updates
Post by: mangorunner on May 13, 2022, 10:12:48 AM
Anyway, at a quick glance the following changes might allow you to enter your actual monthly cash flow numbers in B114 and B115 while also matching what will be on your 2022 1095-A.  Could you test and let me know?

AD100:  =IF(AD94="Yes","",IF(AD98="Yes",D114,AE98/12*D114))
AI100:   =IF(AD98="Yes",-D115,-AE98/12*D115)
You would also have to enter 11/12 of the annual SLCSP in AE100 to match what the 1095-A will show, because there is no formula in that cell - it's a pure input.

That could fit your situation of expecting ACA premiums to continue.  For someone expecting ACA coverage to stop later in the year (e.g., anticipating a shift to employer-provided coverage), however, that may not be appropriate.  Any thoughts on that?

Thank you so much for your reply and input!  I let this get away from me but I will test and get back to you over the weekend.
Title: Re: Case Study Spreadsheet updates
Post by: passionfruit17 on June 18, 2022, 03:53:53 PM
I'm just discovering this amazing tool, it's obviously complex and I'm running into issues that are likely my misunderstanding on how to use it, but I can't seem to figure out on my own.

My employer pays $1500 towards my HSA when I choose a HDHP and I pay the rest to max it out including the catchup ($5800): I think I need to put the $1500 (is that what "employee-sponsored means? I would have thought is simply meant offered by your employer and taken from your paycheck rather than paid for separately) and I put the $5800 in B45, Personal HSA? I supposed it doesn't really matter except to check that the tool estimated paycheck matches my actual check to show everything was accounted for.

I TLH'd this year and have about $50K in STCG (no carry-overs). when I enter -50,000 in cell D26, I properly see the 1040 income D42 go down by $3000 as expected, but the amount in B42 goes down by $50K, which I did not expect. As a result, the monthly budget calculations are thrown off (B73 and below) because hte tool thinks my income is %50K lower than it is. What am I doing wrong?
Title: Re: Case Study Spreadsheet updates
Post by: MDM on June 18, 2022, 04:26:06 PM
I'm just discovering this amazing tool, it's obviously complex and I'm running into issues that are likely my misunderstanding on how to use it, but I can't seem to figure out on my own.
Good that you found this thread - that's what it's here for!

Quote
My employer pays $1500 towards my HSA when I choose a HDHP and I pay the rest to max it out including the catchup ($5800): I think I need to put the $1500 (is that what "employee-sponsored means? I would have thought is simply meant offered by your employer and taken from your paycheck rather than paid for separately) and I put the $5800 in B45, Personal HSA? I supposed it doesn't really matter except to check that the tool estimated paycheck matches my actual check to show everything was accounted for.
"Employer-sponsored" means the HSA contribution occurs via payroll deduction.   Row 45 is for those who contribute to an HSA by sending a check to Fidelity (or whomever).

The HSA employer match can go in B19 "employer match" along with the 401k employer match, or the total (contributions + employer match) can go in "employer-sponsored HSA".   If the latter, you have to add the HSA match to "gross salary/wages" to keep everything consistent.

Quote
I TLH'd this year and have about $50K in STCG (no carry-overs). when I enter -50,000 in cell D26, I properly see the 1040 income D42 go down by $3000 as expected, but the amount in B42 goes down by $50K, which I did not expect. As a result, the monthly budget calculations are thrown off (B73 and below) because hte tool thinks my income is %50K lower than it is. What am I doing wrong?
Hah, good point!  Maybe I should just delete the calculations in B42 and C42, because those cells' results aren't used anywhere.  I'll think about that some more...might just have to included a note saying "one time events, especially those that occur within one's investment accounts, may incorrectly affect budget calculations" or something like that.

If the "-50,000 in cell D26" leads to a correct tax calculation for this year (I think it does - do you see anything to the contrary?) then that may be as good as we can hope for.

Presumably (and we hope) the $50K TLH is a one time event.  I.e., you don't plan on losing $50K in each subsequent year.  Thus that amount would not be a useful input to the "simplified evaluation of 'how long to Financial Independence?'" section in row 165 and below.

Feedback from first-time users such as yourself is great, because fresh eyes see things "more experience" eyes glaze over.  Any specific suggestions on documentation/feature additions that would be (or would have been) helpful?  Thanks!
Title: Re: Case Study Spreadsheet updates
Post by: passionfruit17 on June 18, 2022, 10:01:51 PM
I'm mostly using this for looking at this year (taxes, will I exceed the MAGI for IRA if I switch to Roth 401(k) for example, etc...) so I'm fine informing the budgeting/projections. I know what I spend pretty well and how much is leftover to invest in taxable for example, so I was more exploring the sheet than trying to get budget right.

Thanks for the help, I wouldn't be surprised if I have more questions as I play with it more. I've barely glanced at the SD tab for example although I'm pretty sure I have another spreadsheet that does the same but it's always fun to compare different tools and understand how they differ.

EDIT: the tax calculations see reasonable, so I have no reason to suspect they are messed up, but not qualified to know if they're right when I add ST/LT losses in, that's the main reason I started looking at this spreadsheet :)
Title: Re: Case Study Spreadsheet updates
Post by: passionfruit17 on June 19, 2022, 12:17:53 AM
It looks like B63:B64 show income taxes for both earners, I expected to see the tax share of earner 2 in C63:64 instead of instructions/info.
It makes sense though, for MFJ it's the total of both incomes that matter, each individual is not taxed separately, but at first it was confusing me.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on June 19, 2022, 12:18:20 AM
EDIT: the tax calculations see reasonable, so I have no reason to suspect they are messed up, but not qualified to know if they're right when I add ST/LT losses in, that's the main reason I started looking at this spreadsheet :)
They should be fine for that scenario, because that's something that has been tested multiple times.  The rest of the calculations should also be fine, based on testing a variety of inputs against the results from a couple of commercial programs in previous years.

When the tax law itself changes, that opens more room for error, but when there aren't any law changes except for inflation adjustment of brackets, etc., it's highly likely that what worked in previous years continues to work for the current tax year.  Not an ironclad guarantee (pretty much any software ever written has a bug or two), but.... :)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on June 19, 2022, 12:24:06 AM
It looks like B63:B64 show income taxes for both earners, I expected to see the tax share of earner 2 in C63:64 instead of instructions/info.
It makes sense though, for MFJ it's the total of both incomes that matter, each individual is not taxed separately, but at first it was confusing me.
Exactly.  There just aren't that many Married Filing Separately - and for that matter Qualifying Widow(er) - situations to add the complexity of forcing all income to be split between "Spouse A" and "Spouse B" so the total MFS tax could be compared with the MFJ tax.
Title: Re: Case Study Spreadsheet updates
Post by: MDM on June 19, 2022, 10:53:31 AM
I TLH'd this year and have about $50K in STCG (no carry-overs). when I enter -50,000 in cell D26, I properly see the 1040 income D42 go down by $3000 as expected, but the amount in B42 goes down by $50K, which I did not expect. As a result, the monthly budget calculations are thrown off (B73 and below) because hte tool thinks my income is %50K lower than it is. What am I doing wrong?
Hah, good point!  Maybe I should just delete the calculations in B42 and C42, because those cells' results aren't used anywhere.  I'll think about that some more...might just have to included a note saying "one time events, especially those that occur within one's investment accounts, may incorrectly affect budget calculations" or something like that.

If the "-50,000 in cell D26" leads to a correct tax calculation for this year (I think it does - do you see anything to the contrary?) then that may be as good as we can hope for.

Presumably (and we hope) the $50K TLH is a one time event.  I.e., you don't plan on losing $50K in each subsequent year.  Thus that amount would not be a useful input to the "simplified evaluation of 'how long to Financial Independence?'" section in row 165 and below.

Feedback from first-time users such as yourself is great, because fresh eyes see things "more experience" eyes glaze over.  Any specific suggestions on documentation/feature additions that would be (or would have been) helpful?  Thanks!
After thinking for a bit, deleting the calculations in B42 and C42 seems appropriate because they weren't adding any value and could cause distractions. 

It's also likely that any capital loss is a one-time thing (often for TLH), and plays no part in current or future cash flows.  Given that, using "the larger of zero or the entered value" for STCG and LTCG in D73 and D147 seems appropriate.  It's possible that some capital gains would also be one-time things, but not as likely as for capital losses.

At least, that's where a little thinking has led and those changes are in the test version for the next release....
Title: Re: Case Study Spreadsheet updates
Post by: passionfruit17 on June 19, 2022, 09:36:32 PM
I just got a bonus and it pushed me over the MAGI to qualify for tIRA/Roth IRA ($204K to $214K this year). The only "trick" I think I can use was to contribute to my traditional 401(k) (my employer offers both), but it looks like I have crossed the line where I will qualify for IRA contributions for the foreseeable future unless I don't get a bonus next year. By the way, the "over IRA limit?" message in L42 was confusing me. I thought it meant I was contributing too much to the IRA ($7K since I'm over 50) when I think it was telling me I was over the MAGI limit to qualify for IRA. Maybe the message should say "over MAGI IRA limit" in one case and "over IRA contribution limit" in the other?

Since I was doing traditional (pre-tax) 401(k) to be able to contribute to IRAs, and that reason had gone away, I now need to decide if it's worth my time doing Roth 401(k). I think the tool for that is "Misc Calcs" row 150? I need to read through the Boglehead content and others to understand what numbers I need to plug in (I think B162 for me should be 15% not 20% for example but need to figure out what they mean). Today my income (with trad 401k) puts me at the bottom of the 24% bracket whereas I would be at the bottom of the 22% bracket in retirement (using today's numbers even though they are likely to change)

I also plan to retire at 62, so possibly 10 years before RMDs and want to understand if/how much I should do Roth conversions during that time. In today's number, my retirement needs ($100 post-tax as a round number) put me at the top of the 12% bracket or the bottom of 22% depending on where I draw the money from (how much in taxes to get to $100K post taxes). This means that Roth conversion would likely be in the 22% bracket and if worth doing at that rate could go all the way to the top of the bracket (about $90K in conversion every year?). Is there a tool to decide whether I should plan on converting to the top of 22% or even at 24%?

Also is there a tool to decide if it's worth doing a backdoor roth? I'd have to move a sizable rollover IRA ($800K+) into my 401(k) and my 401(k) as has a 0.04% administration fee on top of what a similar fund would cost in my Fidelity rollover. so it's costing me $3200 a year (compounded) to hold the money in the 401(k), so the advantage of a backdoor Roth needs to be at least that much to break even, so I think I shouldn;t backdoor.  My 401(k) plan won't support mega backdoor.

All rates are federal only, add 4.55% for state

I realize these are both questions about the tool and advice about my particular situation, so I need ot post somewhere else for non-generic tool questions (I'm new to this forum)

Title: Re: Case Study Spreadsheet updates
Post by: MDM on June 20, 2022, 01:43:54 PM
By the way, the "over IRA limit?" message in L42 was confusing me. I thought it meant I was contributing too much to the IRA ($7K since I'm over 50) when I think it was telling me I was over the MAGI limit to qualify for IRA. Maybe the message should say "over MAGI IRA limit" in one case and "over IRA contribution limit" in the other?
Thanks for that feedback.  I'll have to think about that, especially for the phase-out situations in which one could say "income too high" or "deduction (for tIRA) / contribution (for Roth) too high", depending on which way one looks at it.

Quote
Since I was doing traditional (pre-tax) 401(k) to be able to contribute to IRAs, and that reason had gone away, I now need to decide if it's worth my time doing Roth 401(k). I think the tool for that is "Misc Calcs" row 150? I need to read through the Boglehead content and others to understand what numbers I need to plug in (I think B162 for me should be 15% not 20% for example but need to figure out what they mean). Today my income (with trad 401k) puts me at the bottom of the 24% bracket whereas I would be at the bottom of the 22% bracket in retirement (using today's numbers even though they are likely to change)
Yes, if you can contribute the maximum to the Roth choice, the fair comparison to traditional includes the need for some taxable contribution to make the total pre-tax amount going to Roth equal the total pre-tax amount going to traditional+taxable.  When entering tax rates, include any state tax rates that apply.

Quote
I also plan to retire at 62, so possibly 10 years before RMDs and want to understand if/how much I should do Roth conversions during that time. In today's number, my retirement needs ($100 post-tax as a round number) put me at the top of the 12% bracket or the bottom of 22% depending on where I draw the money from (how much in taxes to get to $100K post taxes). This means that Roth conversion would likely be in the 22% bracket and if worth doing at that rate could go all the way to the top of the bracket (about $90K in conversion every year?). Is there a tool to decide whether I should plan on converting to the top of 22% or even at 24%?
The Roth conversion choice is similar to the traditional vs. Roth contribution choice: compare a known marginal tax rate now to an estimated tax rate later.  There is even a similarity to the "contribute the maximum" situation discussed above, if you pay the conversion tax out of taxable funds.  The Bogleheads wiki on Roth IRA conversion (https://www.bogleheads.org/wiki/Roth_IRA_conversion) goes into more detail.

Quote
Also is there a tool to decide if it's worth doing a backdoor roth? I'd have to move a sizable rollover IRA ($800K+) into my 401(k) and my 401(k) as has a 0.04% administration fee on top of what a similar fund would cost in my Fidelity rollover. so it's costing me $3200 a year (compounded) to hold the money in the 401(k), so the advantage of a backdoor Roth needs to be at least that much to break even, so I think I shouldn;t backdoor.  My 401(k) plan won't support mega backdoor.
I think your analysis here is all the tool you need. :)

Quote
I realize these are both questions about the tool and advice about my particular situation, so I need ot post somewhere else for non-generic tool questions (I'm new to this forum)
Whether new or not, all good questions!
Title: Re: Case Study Spreadsheet updates
Post by: dandarc on June 20, 2022, 02:46:18 PM
Is that a 0.04% fee ($320 per year on an $800K balance) or 0.4% ($3,200)?

No idea where the line is for you on this, but if your 'additional fees to hold in 401K' is off by an order of magnitude, I could see that swaying the call for you regarding the backdoor Roth.
Title: Re: Case Study Spreadsheet updates
Post by: passionfruit17 on June 20, 2022, 03:35:23 PM
sorry, 0.04% / $320 I messed up. Less clearcut now :)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on June 20, 2022, 04:31:49 PM
sorry, 0.04% / $320 I messed up. Less clearcut now :)
You're probably in coin-flip territory in that case.

For a quick approximation, you could use the "Growth in a taxable account" section (Misc. calcs tab rows 129-148).  Changing C131 and C139 to zero makes "Case B" act like a Roth.  Changing C137 to =B137-320 approximates the cost of the 401k .04% fee.

Setting B136=0, B137=7000, and using the other numbers already entered for Case A (without any comment on the likelihood they will be correct going forward), facilitating the backdoor Roth loses for several years, then catches up between 7 and 8 years, and stays favorable after that.  But it's not a huge effect, so either way is defensible.
Title: Re: Case Study Spreadsheet updates
Post by: passionfruit17 on June 23, 2022, 11:48:18 AM
Another question:

My wife started a business this year (in addition to a part-time W2 job) and according to her accountant we can deduct enough of her startup costs to get our MAGI back into IRA contribution territory, so I was trying to plug those numbers in the sheet.
I am covered by a 401(k) at work but my spouse is not, even though she has W2 income, my understanding is that it changes the tIRA contribution income limit from $109k to $204k (which happens to match the Roth limit). I got the non-covered spouse numbers from here: https://www.forbes.com/advisor/retirement/ira-contribution-limits
I think that means that G45 should be $204k, not $109K? How do I tell the spreadsheet my spouse is not covered by a plan at work? or am I confused?

My widf has enough W2 wages to fully fund a tIRA, although I think that even if she didn't we can complement under "spousal IRA".

Title: Re: Case Study Spreadsheet updates
Post by: MDM on June 23, 2022, 04:18:14 PM
I think that means that G45 should be $204k, not $109K? How do I tell the spreadsheet my spouse is not covered by a plan at work? or am I confused?
Enter what you know is correct.  As it says on row 40 of the Instructions, " - No check is made to enforce compliance with the various IRS rules on IRA, 401k, HSA, etc. contributions"

But if you want the - advisory only - comments in K41:M46 to be more accurate, use L39 and M39 to denote work plan coverage.
Title: Re: Case Study Spreadsheet updates
Post by: passionfruit17 on June 23, 2022, 09:29:29 PM
Thanks for the help, I wish I *knew* that what I think is correct, so second-guessing of/when the magic spreadsheet doesn't say the same thing :)
I had missed the L39/M39 section, perfect, it was user error!
Title: Re: Case Study Spreadsheet updates
Post by: MDM on July 23, 2022, 08:41:33 PM
Version 22.03 (2022)
Mostly a bunch of niche issues, but always of interest to those in such a niche. ;)

Use of the free form input for the marginal rate chart is limited only by one's imagination.  One example:
   - MFJ both under age 50, neither participating in a workplace retirement plan, and $50K W-2 income.
   - Use cell F129 as "Total IRA contribution" and enter that text in H129.
   - Enter F129 in cell G107 and click the "Update chart" button
   - in cell B50, enter =IF(F129<=2000,F129,IF(AND(F129>2000,F129<=4000),2000,IF(AND(F129>4000,F129<=8000),F129-2000,6000)))
   - in cell C50, enter =F129-B50
      - Those formulas give the maximum saver's credit for the couple as their combined tIRA contribution increases.
   - Change cell P83 to 24.
   - This gives the chart below:
(https://lh3.googleusercontent.com/d/1l5QPnVVC_SB2UQyA8Juob4dZePrHYc7k)


As always, comments/suggestions/bug identifications/etc. are appreciated.  Either PM me or drop a note here.

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


Note:
Title: Re: Case Study Spreadsheet updates
Post by: jheez on July 24, 2022, 07:05:58 PM
Thank you for all the hard work on this. This is an invaluable tool!

I'm not savy enough to diagnose, but it appears LT Capital Gains and Qualified Dividends are showing as taxed in the 0% LTCG/QD bracket in Version 22.03
Title: Re: Case Study Spreadsheet updates
Post by: MDM on July 24, 2022, 07:14:54 PM
Thank you for all the hard work on this. This is an invaluable tool!

I'm not savy enough to diagnose, but it appears LT Capital Gains and Qualified Dividends are showing as taxed in the 0% LTCG/QD bracket in Version 22.03
Changing only cells D25 and D27 from the template by entering 20000 in each gives $0 for federal tax in cell G35.  Do you see something different?
Title: Re: Case Study Spreadsheet updates
Post by: jheez on July 24, 2022, 09:08:12 PM
My apologies. Complete user error! Feel free to remove my post
Title: Re: Case Study Spreadsheet updates
Post by: MDM on July 24, 2022, 09:30:54 PM
Not a problem - better the occasional false alarm than real problems continuing unnoticed. 

And welcome to the forum!