The Money Mustache Community

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

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

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

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

See also this post (http://forum.mrmoneymustache.com/ask-a-mustachian/how-to-write-a-'case-study'-topic/msg274228/#msg274228) and, last but not least, the 'Instructions' tab in the spreadsheet itself.

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

Most recent version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/view?usp=sharing)
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 updates
Post by: lhamo on May 31, 2016, 08:28:04 PM
Posting to follow (because I LOOOOOVE this template and somehow missed the earlier messages on this thread).

Also just PMed you about something related -- do what you will with the info!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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


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


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

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

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

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


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


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

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

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

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

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

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

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

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

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

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

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

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

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

Federal 2018 changes will be released "soon."

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

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

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

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

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

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

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

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


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

Doing it the latter way might be slightly more accurate in terms to "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


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

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

2017 version 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: 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

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

2017 version 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 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.

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

The last 2017 version 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: 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

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

The last 2017 version 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: 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.

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: 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.

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: 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.

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 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.

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: 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.


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 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.


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 May 02, 2018, 10:50:33 AM
Version 11.10

Most recent version: Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on May 30, 2018, 05:26:18 PM
Version 11.11

Most recent version: Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/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. ;)

Most recent version: Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/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.


Most recent version: Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/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

Most recent version: Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/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. ;)


Most recent version: Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet updates
Post by: MDM on September 09, 2018, 03:50:45 PM
Version 11.16

Most recent version: Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/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)

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.


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

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

Most recent 2018 version: Case Study Spreadsheet (https://drive.google.com/file/d/0B45krBaG0b6KTWZDeXEySVBHVXc/view?usp=sharing)
Title: Re: Case Study Spreadsheet (CSS) updates
Post by: MDM on January 12, 2019, 01:17:52 PM
Version 12.00
ETA: cell Calculations!O21 should be 488,850, not 48,850.  Correct now, but if uploaded in the past ~8 hours....

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.

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

Most recent version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/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)

Most recent version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/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)

Most recent version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/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)

Most recent version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/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)

Most recent version: 2019 Case Study Spreadsheet (https://drive.google.com/file/d/1aPE6MbD7TM-qp6dsHDWALlSFAaUhqINg/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.