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

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #100 on: April 14, 2018, 03:38:46 PM »
Version 11.07
  • Added a tab that evaluates a non-deductible IRA vs. taxable (and, while it's at it, Roth and deductible)
  • Tweaks to the Form 8606 tab

Most recent version: Case Study Spreadsheet

The last 2017 version (9.11) is attached to post https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1825323/#msg1825323.

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #101 on: April 21, 2018, 07:53:30 PM »
Version 11.08
  • Updated the 2016 Average Wage Index for Social Security benefit estimates.
  • Minor formatting
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

The last 2017 version (9.11) is attached to post https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1825323/#msg1825323.

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #102 on: April 24, 2018, 04:17:17 PM »
Version 11.09
  • Added Form 8962, Premium Tax Credit.
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

The last 2017 version (9.11) is attached to post https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1825323/#msg1825323.

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #103 on: May 02, 2018, 10:50:33 AM »
Version 11.10
  • Put the HSA family limit back to $6900.  Thanks, IRS....
  • Added columns and fixed typos in the Form8606 tab.

Most recent version: Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #104 on: May 30, 2018, 05:26:18 PM »
Version 11.11
  • Added $25K rental loss limitation.
  • Correct cell reference in state tax estimate for time to FI.
  • Add local income tax to NIIT deductions.
  • Minor formatting changes.

Most recent version: Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #105 on: June 27, 2018, 05:48:22 PM »
Version 11.12
  • Added the new Section 199A deduction for qualified pass-through business income.
  • Minor formatting changes.
Please use 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

HudsonMK

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: Case Study Spreadsheet updates
« Reply #106 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!
« Last Edit: July 07, 2018, 07:54:03 AM by HudsonMK »

MDM

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


MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #108 on: July 20, 2018, 06:36:01 PM »
Version 11.13
  • Added a "how much can one spend per year using a pension with no COLA?" calculation to the 'Misc. calcs' tab.
  • Fixed formulas for some brackets in the HI and KS state tax calculations.
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? for some math background.


Most recent version: Case Study Spreadsheet

jpdx

  • Bristles
  • ***
  • Posts: 284
Re: Case Study Spreadsheet updates
« Reply #109 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?

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #110 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?
« Last Edit: July 24, 2018, 10:11:24 PM by MDM »

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #111 on: July 25, 2018, 07:09:45 PM »
Version 11.14
  • Modified the SocialSecurity tab so it will automatically select the top 35 years of indexed earnings - filtering no longer necessary.
  • Fixed the AMT breakpoints for capital gains on the Form6251 tab (thanks to Boglehead Electron).

Most recent version: Case Study Spreadsheet

Paul der Krake

  • Magnum Stache
  • ******
  • Posts: 4439
  • Age: 11
  • Location: USA
Re: Case Study Spreadsheet updates
« Reply #112 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.


MDM

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

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

jpdx

  • Bristles
  • ***
  • Posts: 284
Re: Case Study Spreadsheet updates
« Reply #114 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"?

MDM

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

Does that work for you?

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #116 on: August 15, 2018, 12:24:12 PM »
Version 11.15
  • Added some constraints to the 20% pass-thru business deduction calculation.  Calculations match all the in-scope examples in the recent IRS proposed regulations on this.
  • Added a link to a nice screen-shotted "how to" on the ACA premium tax credits (thanks to TheFinanaceBuff).  See the Instructions tab.
  • Added some brief documentation to the lines in the Qualified Dividends and Capital Gain Tax Worksheet.
  • Added a worksheet tab, 0% LTCG or t->R, to evaluate the choice between Tax Gain Harvesting (TGH) vs traditional to Roth conversions when one is in the 12% (or lower) federal bracket.

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

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #117 on: September 09, 2018, 03:50:45 PM »
Version 11.16
  • Added a cell, Calculations!V4, for "unavoidable" (e.g., pension, interest, dividends) income when determining how large a traditional balance is needed for a 4% withdrawal rate to reach various tax brackets.
  • Included ACA premium tax credit in the Alternative Minimum Tax calculation.
  • Adjusted "taxable income" calculation for the 20% pass-thru business deduction calculation.  Calculations continue to match all the in-scope examples in the recent IRS proposed regulations on this.
  • Minor formatting and documentation changes.

Most recent version: Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #118 on: September 16, 2018, 10:23:49 PM »
Version 11.17
  • Updated Idaho state tax calculations, per this post.
  • Updated Arkansas state tax calculations, per this post.

Note that when saving the CSS, if one is not on the 'Calculations' tab, the following scary-looking message may appear:


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. 

Thanks to the Bogleheads forum finding the error in my federal tax return - Bogleheads.org for bringing this up.


Most recent version: Case Study Spreadsheet

BTDretire

  • Handlebar Stache
  • *****
  • Posts: 2336
Re: Case Study Spreadsheet updates
« Reply #119 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?

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #120 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? and Case Study Spreadsheet updates.

If you (or anyone) has a spreadsheet version of any method for Self-Employed Health Insurance Deduction and PTC...?

BTDretire

  • Handlebar Stache
  • *****
  • Posts: 2336
Re: Case Study Spreadsheet updates
« Reply #121 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? and Case Study Spreadsheet updates.

If you (or anyone) has a spreadsheet version of any method for Self-Employed Health Insurance Deduction and PTC...?

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

BTDretire

  • Handlebar Stache
  • *****
  • Posts: 2336
Re: Case Study Spreadsheet updates
« Reply #122 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?

MDM

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


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:


In this case, you should see the following blue arrows, showing that cell D28 comes from Schedule D calculations.


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!

BTDretire

  • Handlebar Stache
  • *****
  • Posts: 2336
Re: Case Study Spreadsheet updates
« Reply #124 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.
« Last Edit: September 21, 2018, 10:53:00 AM by BTDretire »

MDM

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

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.

BTDretire

  • Handlebar Stache
  • *****
  • Posts: 2336
Re: Case Study Spreadsheet updates
« Reply #126 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,

fiveoh

  • Bristles
  • ***
  • Posts: 374
Re: Case Study Spreadsheet updates
« Reply #127 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? 

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #128 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 that have been published require numbers from as yet unpublished forms.

fiveoh

  • Bristles
  • ***
  • Posts: 374
Re: Case Study Spreadsheet updates
« Reply #129 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 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!

MDM

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

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #131 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? and Case Study Spreadsheet updates.

If you (or anyone) has a spreadsheet version of any method for Self-Employed Health Insurance Deduction and PTC...?

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


wjmano

  • 5 O'Clock Shadow
  • *
  • Posts: 1
Re: Case Study Spreadsheet updates
« Reply #132 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



MDM

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

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #134 on: November 05, 2018, 08:16:57 PM »
Version 11.18
  • Added entry for self-employed SEP, SIMPLE, etc.
  • Added entry for self-employed health insurance deduction.
    - The self-employed health insurance entry is accepted as entered.  As with the 2018 Volunteer Income Tax Assistance (VITA) program, interactions between SE health insurance and the ACA Premium Tax Credit remain out of scope.  I.e., caveat user if this applies to you, and see https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg2161492/#msg2161492 for references.
  • Added the Child Tax Credit (CTC) calculations as done in the CTC worksheet (form 1040 line 12a instructions) and schedule 8812.
  • Adjusted the Federal Poverty Line numbers to match page 7 of https://www.irs.gov/pub/irs-dft/i8962--dft.pdf
  • Updated Social Security benefit calculations to match version 2019.1 of anypia.exe, the program available from ssa.gov.
  • Updated the Instructions tab with changes to cell references.

Most recent version: Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet (CSS) updates
« Reply #135 on: January 11, 2019, 07:47:18 PM »
Version 11.19
  • Allow smooth marginal rate calculations for self-employment tax.
  • Correct display calculations for non-zero Foreign Earned Income Exclusion.
  • Added SE income as an X-axis choice for the marginal rate graph.
  • Added Medicare Part D changes to the IRMAA calculations.

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

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet (CSS) updates
« Reply #136 on: January 12, 2019, 01:17:52 PM »
Version 12.00
  • 2019 tax law changes
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 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

Most recent version: 2019 Case Study Spreadsheet
« Last Edit: January 12, 2019, 09:14:52 PM by MDM »

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet (CSS) updates
« Reply #137 on: January 18, 2019, 12:35:04 AM »
Versions 11.20 and 12.01
  • Improved handling of the Self-Employed Health Insurance subtraction.
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

Most recent version: 2019 Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet (CSS) updates
« Reply #138 on: January 20, 2019, 02:48:24 PM »
Versions 11.21 and 12.02
  • Subtract 1/2 SE tax, SE health insurance, and SE SEP/SIMPLE contributions from Schedule C amount to get QBI.
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

Most recent version: 2019 Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet (CSS) updates
« Reply #139 on: January 23, 2019, 09:44:54 PM »
Version 12.03
  • Fix minor typos in a few numbers.

2018 taxes version: 2018 Case Study Spreadsheet

Most recent version: 2019 Case Study Spreadsheet

Orichalcum

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: Case Study Spreadsheet updates
« Reply #140 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.

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet updates
« Reply #141 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, 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.

Joshua

  • 5 O'Clock Shadow
  • *
  • Posts: 28
Re: Case Study Spreadsheet updates
« Reply #142 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.

MDM

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

Lhall

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: Case Study Spreadsheet updates
« Reply #144 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. 

MDM

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

Lhall

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: Case Study Spreadsheet updates
« Reply #146 on: February 01, 2019, 04:35:33 PM »
The SLCSP is 525, just me alone, premium is 528 (I pay just $3/mo net). 

MDM

  • Walrus Stache
  • *******
  • Posts: 9101
Re: Case Study Spreadsheet (CSS) updates
« Reply #147 on: February 01, 2019, 08:58:03 PM »
Versions 11.22 and 12.04
  • Added (overridable) estimate of % paychecks left for withholding purposes.
  • Fix ACA premium tax credit (PTC) cutoff at >400% FPL when advance premium credits have been claimed.  Thanks Lhall!
  • Improve one-step iteration for SE health insurance + ACA PTC interaction.
  • Added $1000 dependent exemption for NY state tax.  Thanks Orichalcum!
  • Started work to improve OH state tax - need more info (see questions in reply #143) to complete.
2018 taxes version: 2018 Case Study Spreadsheet

Most recent version: 2019 Case Study Spreadsheet

Orichalcum

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: Case Study Spreadsheet updates
« Reply #148 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....

MDM

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