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

MDM

  • Senior Mustachian
  • ********
  • Posts: 10664
Re: Case Study Spreadsheet (CSS) updates
« Reply #250 on: March 24, 2021, 09:15:06 PM »
Version 21.06 (2021)

2021
  • Child and Dependent Care Credit "smoothing"

Child and dependent care credit
Implemented a continuous approximation to the actual law.  This allows the marginal rate chart for a family with
- Earner #2 wages = $25,100
- One dependent age 10, another age 5
- Dependent care cost = $16,000

to look like


instead of



Those who prefer to see the discontinuous marginal rates of the tax code (for this and all other included features) in all their glory may change cell R80 from Y to N.

2021 taxes version: 2021 Case Study Spreadsheet


Note:

MDM

  • Senior Mustachian
  • ********
  • Posts: 10664
Re: Case Study Spreadsheet updates
« Reply #251 on: April 26, 2021, 02:51:21 PM »
Version 21.07 (2021)

2021
  • Improved the tax estimate in the "Time to FI" section
  • Added EIP#3 default payment.  Only those who did not receive the full $1400/person will need to override.
  • Updated childcare pre-tax limit to $10,500 in Form 2441
  • Provided an SEHI+PTC iteration capability
Thanks to MountainLakeMaka and Harry Sit for insights and suggestions on these updates.

SEHI+PTC iteration capability
The spreadsheet is now attempting to handle the iterative calculations when both Premium Tax Credits (PTC) and Self-Employed Health Insurance (SEHI) premiums are involved, at least for one Schedule C and one SEHI premium.  For people with both self-employment income and Affordable Care Act coverage, determining the optimal amount of the SEHI deduction can be an iterative process.  See pp. 57-75 of https://www.irs.gov/pub/irs-pdf/p974.pdf.

These calculations have been tested and appear to converge reliably over a wide range of conditions. To enable this capability,
    - Enter the appropriate values in cells B114, B115, AD123 and AE100
    - Enter the formula =AD159 in cell B48.  This enables the iteration
    - Entering a specific value, e.g., 0 (zero) in cell B48, or deleting the cell contents, will eliminate the iteration.
    - Ensure the "Enable iterative calculation" box is checked in File > Options > Formulas if the iterations should be, but aren't, occurring.
   Iteration requires significant extra calculation and decreases spreadsheet responsiveness.  Useful if needed, but avoid it otherwise.
   If many #Value, #DIVbyZero, etc., errors propagate, put a specific value in cell B48, fix the root cause, and re-enable iteration.  If errors persist, reply or PM.

At least a couple of things are not clear:
  • Whether the spreadsheet correctly implements the IRS iteration procedure.  AFAIK there are no 2021 examples for comparison yet.
  • Whether the IRS procedure always provides the most beneficial result for the taxpayer.  I may start a separate thread for this specific issue, but meanwhile if anyone knows of 2021 examples for comparison, please reply or PM.
In general, suggestions/comments/corrections on the SEHI+PTC issue are welcome.  Somewhat of a niche issue, but mathematically interesting and financially significant when it occurs.

2021 taxes version: 2021 Case Study Spreadsheet


Note:

jeromedawg

  • Magnum Stache
  • ******
  • Posts: 4150
  • Location: Orange County, CA
Re: Case Study Spreadsheet updates
« Reply #252 on: April 26, 2021, 04:39:05 PM »
Couple clarifying questions (sorry if these were already asked prior):

1) When specifying "Guess Time to FI" is the assumption that the "Gross Salary/Wages" (B3 and C3) won't change?

2) What would the right 'procedure' be to map out a cash-out refi where you're taking the cash-out and reinvesting in index funds but also drawing-down from it year over year to supplement your income/earnings (whether pre or post retirement)? I noticed boxes D26 and D27 but if you plug a number into those, it seems the spreadsheet assumes that those are going to be year-over-year numbers versus one-time...?

MDM

  • Senior Mustachian
  • ********
  • Posts: 10664
Re: Case Study Spreadsheet updates
« Reply #253 on: April 26, 2021, 04:54:24 PM »
Couple clarifying questions (sorry if these were already asked prior):

1) When specifying "Guess Time to FI" is the assumption that the "Gross Salary/Wages" (B3 and C3) won't change?

2) What would the right 'procedure' be to map out a cash-out refi where you're taking the cash-out and reinvesting in index funds but also drawing-down from it year over year to supplement your income/earnings (whether pre or post retirement)? I noticed boxes D26 and D27 but if you plug a number into those, it seems the spreadsheet assumes that those are going to be year-over-year numbers versus one-time...?
1) Yes.  More precisely, that they won't change in real terms: inflation is ignored for all calculations.
2) Ignore it?  Probably depends on how significant it is relative to your overall finances.  You might need more sophisticated tools such as those described in Best and/or Recommended Retirement Calculator - Bogleheads.org if fine details are needed.  Note that the error band on assumed return rates usually dwarfs all other items....

jeromedawg

  • Magnum Stache
  • ******
  • Posts: 4150
  • Location: Orange County, CA
Re: Case Study Spreadsheet updates
« Reply #254 on: May 02, 2021, 08:14:36 PM »
Couple clarifying questions (sorry if these were already asked prior):

1) When specifying "Guess Time to FI" is the assumption that the "Gross Salary/Wages" (B3 and C3) won't change?

2) What would the right 'procedure' be to map out a cash-out refi where you're taking the cash-out and reinvesting in index funds but also drawing-down from it year over year to supplement your income/earnings (whether pre or post retirement)? I noticed boxes D26 and D27 but if you plug a number into those, it seems the spreadsheet assumes that those are going to be year-over-year numbers versus one-time...?
1) Yes.  More precisely, that they won't change in real terms: inflation is ignored for all calculations.
2) Ignore it?  Probably depends on how significant it is relative to your overall finances.  You might need more sophisticated tools such as those described in Best and/or Recommended Retirement Calculator - Bogleheads.org if fine details are needed.  Note that the error band on assumed return rates usually dwarfs all other items....

Ah okay thanks.

One other question/clarification: I'm having trouble understanding how a mortgage impacts the "Time to FI" section and particularly the "Projected Expenses in Retirement" - how/where does that all factor in or relate?

MDM

  • Senior Mustachian
  • ********
  • Posts: 10664
Re: Case Study Spreadsheet updates
« Reply #255 on: May 02, 2021, 08:59:36 PM »
One other question/clarification: I'm having trouble understanding how a mortgage impacts the "Time to FI" section and particularly the "Projected Expenses in Retirement" - how/where does that all factor in or relate?
The assumption is that the "Stash needed for retirement" is composed of two terms:
a) (Retirement expenses) / (Safe Withdrawal Ratio), plus
b) Loan balances
A further assumption is that if one has invested the loan balance, the return on those investments will be at least as high as the mortgage interest rate.

So the mortgage doesn't impact the expenses in retirement, because the payments
- do not increase with inflation, and
- usually end prior to the 30 years usually associated with "Safe Withdrawal Ratios".

Make sense?


jeromedawg

  • Magnum Stache
  • ******
  • Posts: 4150
  • Location: Orange County, CA
Re: Case Study Spreadsheet updates
« Reply #256 on: May 02, 2021, 09:27:17 PM »
One other question/clarification: I'm having trouble understanding how a mortgage impacts the "Time to FI" section and particularly the "Projected Expenses in Retirement" - how/where does that all factor in or relate?
The assumption is that the "Stash needed for retirement" is composed of two terms:
a) (Retirement expenses) / (Safe Withdrawal Ratio), plus
b) Loan balances
A further assumption is that if one has invested the loan balance, the return on those investments will be at least as high as the mortgage interest rate.

So the mortgage doesn't impact the expenses in retirement, because the payments
- do not increase with inflation, and
- usually end prior to the 30 years usually associated with "Safe Withdrawal Ratios".

Make sense?

Stupid me... I overlooked plugging in the number to "Current Principal" which was throwing things off. Haha. I think I have a better understanding now...

johnhenry

  • Bristles
  • ***
  • Posts: 333
  • Age: 41
  • Location: Midwest
Re: Case Study Spreadsheet updates
« Reply #257 on: May 04, 2021, 02:51:17 PM »
Hi.

I'm using V20.13 (3/3/2021) of the 2020 CSS.  If that's not the latest, could someone point me to it?

I'm having trouble with my

1040 Total Income (cell D42)
1040 AGI  (cell D52)
Taxable (cell G16)

all being "off" (over in the CSS) by the amount of my Qualified Dividends.  Basically, the CSS seems to be including them where the tax return I'm entering them from has them excluded.

thanks in advance....

MDM

  • Senior Mustachian
  • ********
  • Posts: 10664
Re: Case Study Spreadsheet updates
« Reply #258 on: May 04, 2021, 03:16:11 PM »
Hi.

I'm using V20.13 (3/3/2021) of the 2020 CSS.  If that's not the latest, could someone point me to it?

I'm having trouble with my

1040 Total Income (cell D42)
1040 AGI  (cell D52)
Taxable (cell G16)

all being "off" (over in the CSS) by the amount of my Qualified Dividends.  Basically, the CSS seems to be including them where the tax return I'm entering them from has them excluded.

thanks in advance....
If your 1099-DIV has $1000 in box 1a (Total ordinary dividends) and $925 in box 1b (Qualified dividends) the spreadsheet entries would be
Cell D23: $75
Cell D25: $925

Does that make sense?

johnhenry

  • Bristles
  • ***
  • Posts: 333
  • Age: 41
  • Location: Midwest
Re: Case Study Spreadsheet updates
« Reply #259 on: May 05, 2021, 07:54:16 AM »
If your 1099-DIV has $1000 in box 1a (Total ordinary dividends) and $925 in box 1b (Qualified dividends) the spreadsheet entries would be
Cell D23: $75
Cell D25: $925

Does that make sense?

Thanks.  That got me fixed up.  I see the comment now that asks for "non-qualified dividends" as opposed to "total ordinary dividends".... I'm just used to entering the latter into tax software since that's what's reported in Box 1a.


drumstache

  • 5 O'Clock Shadow
  • *
  • Posts: 56
Re: Case Study Spreadsheet updates
« Reply #260 on: May 05, 2021, 10:23:14 AM »
One question about the 'Safe Withdrawal Rate', Cell B168.  When I increase that value from 4% to say 6%, the value for 'Stash needed for retirement' in cell B194 decreases.  Shouldn't that number go up if I'm increasing the withdrawal rate?

Very possible I hosed something up, or misunderstanding how this works.

Appreciate the work put into this spreadsheet, great tool, Thanks!


 

MDM

  • Senior Mustachian
  • ********
  • Posts: 10664
Re: Case Study Spreadsheet updates
« Reply #261 on: May 05, 2021, 10:49:30 AM »
One question about the 'Safe Withdrawal Rate', Cell B168.  When I increase that value from 4% to say 6%, the value for 'Stash needed for retirement' in cell B194 decreases.  Shouldn't that number go up if I'm increasing the withdrawal rate?

Very possible I hosed something up, or misunderstanding how this works.

Appreciate the work put into this spreadsheet, great tool, Thanks!
See Safe withdrawal rates (SWR) for details, but in short the equation is "Spending = SWR * Stash", or "Stash = Spending / SWR".  As your estimate of SWR goes up, the required stash goes down.

Of course, the "S" part of the acronym is known only in hindsight....

drumstache

  • 5 O'Clock Shadow
  • *
  • Posts: 56
Re: Case Study Spreadsheet updates
« Reply #262 on: May 06, 2021, 06:51:07 AM »

See Safe withdrawal rates (SWR) for details, but in short the equation is "Spending = SWR * Stash", or "Stash = Spending / SWR".  As your estimate of SWR goes up, the required stash goes down.

Of course, the "S" part of the acronym is known only in hindsight....

Ah, makes sense now.  Thanks for the clarification!

MDM

  • Senior Mustachian
  • ********
  • Posts: 10664
Re: Case Study Spreadsheet updates
« Reply #263 on: May 11, 2021, 10:25:15 PM »
Version 21.08 (2021)

2021
  • Allow refundable dependent care credit for lower incomes
  • Added cell B6 (Daycare FSA) as an allowable x-axis variable for the marginal rate chart

SEHI+PTC iteration capability update
"Worksheet Y" has been added, but none of its calculations are currently used.  With the elimination of the >400% FPL cliff, the IRS will be changing some things in this area.  If anyone is interested in collaborating on more work in this area, let me know (reply here or PM).  Otherwise, the fact that iteration converges reliably(?) for at least "straightforward" situations may be good enough for estimation purposes....

2021 taxes version: 2021 Case Study Spreadsheet


Note:

jeromedawg

  • Magnum Stache
  • ******
  • Posts: 4150
  • Location: Orange County, CA
Re: Case Study Spreadsheet updates
« Reply #264 on: June 27, 2021, 09:37:58 PM »
Kind of an off-topic but related question here but what is your guys' general opinion of the "Have $XXXXX extra" amount and what you *prefer* that number to be in order to feel comfortable about RE?

Was also wondering about the state abbreviation in H32 - I tried entering "CA" and it doesn't seem to be able to recognize it - EDIT: nm, I entered a couple other states and it eventually populated.
« Last Edit: June 27, 2021, 09:54:13 PM by jeromedawg »

MDM

  • Senior Mustachian
  • ********
  • Posts: 10664
Re: Case Study Spreadsheet updates
« Reply #265 on: July 11, 2021, 04:56:37 PM »
Version 21.09 (2021)
  • Added entry for advance Child Tax Credit payments
  • Updated line number references to some IRS forms
  • Excluded pensions from Illinois state tax
  • Added some options to the "Traditional assets needed to reach..." tables
  • Added some options to allow more "exact estimates," e.g., number of blind filers, use of 2019 earned income, etc.
  • Miscellaneous edits to some cell descriptions and comments
  • Added "marginal tax rate cost of selling equities" to the "Roth vs. Traditional when contributing the maximum allowed" calculation in the 'Misc. calcs' tab
2021 taxes version: 2021 Case Study Spreadsheet

Note:

getmoneyeatpizza

  • 5 O'Clock Shadow
  • *
  • Posts: 75
Re: Case Study Spreadsheet updates
« Reply #266 on: July 14, 2021, 12:52:29 PM »
"Enter the amount of Child Tax Credit already received"

Should this be the amount expected to be received? Specifically for purposes of trying to estimate my tax return. I will get $3300 advance CTC this year so should I just deduct this amount from the expected return amount?

MDM

  • Senior Mustachian
  • ********
  • Posts: 10664
Re: Case Study Spreadsheet updates
« Reply #267 on: July 14, 2021, 01:15:39 PM »
"Enter the amount of Child Tax Credit already received"

Should this be the amount expected to be received? Specifically for purposes of trying to estimate my tax return. I will get $3300 advance CTC this year so should I just deduct this amount from the expected return amount?
Yes, that's it for the CTC.

For the purpose of estimating the refund or amount owed when filing, one also needs to account for withholding and estimated tax payments.  See cells I70 and I71, and the rest of the "Form W-4" section for that.

In general I'd suggest calculating the tax liability and then adjusting W-4s as needed to owe ~$0 when filing, but individual circumstances can vary.