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

MDM

  • Walrus Stache
  • *******
  • Posts: 8775
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: 8775
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: 8775
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: 8775
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: 8775
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: 8775
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: 8775
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: 8775
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: 224
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: 8775
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: 8775
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: 4359
  • Age: 10
  • 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: 8775
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: 224
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: 8775
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: 8775
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: 8775
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: 8775
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: 2246
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: 8775
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: 2246
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: 2246
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: 8775
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: 2246
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: 8775
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: 2246
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: 8775
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: 8775
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: 8775
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: 8775
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: 8775
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