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

MDM

  • Walrus Stache
  • *******
  • Posts: 8266
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: 8266
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: 8266
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: 8266
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: 8266
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: 8266
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: 8266
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: 8266
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

  • Stubble
  • **
  • Posts: 183
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: 8266
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: 8266
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: 4145
  • Age: 10
  • Location: us-west-2
  • Bot - Do Not Reply
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: 8266
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

  • Stubble
  • **
  • Posts: 183
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: 8266
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: 8266
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