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

MDM

  • Walrus Stache
  • *******
  • Posts: 6007
Case Study Spreadsheet updates
« on: March 16, 2016, 08:23:57 PM »
As noted in the "How to Write a Case Study" sticky, and a more detailed post in that thread, the Case Study Spreadsheet (CSS) contains various calculations applicable to personal finance in general and Financial Independence in particular.

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.

A new version was uploaded today.  The major change is the use of 2016 tax calculations.  By this time, folks ought to be using real tax calculations for their 2015 returns.  The tax calculation portion of the CSS can be useful for planning ahead, but should not be mistaken for commercial tax software.
« Last Edit: November 27, 2016, 07:49:26 PM by MDM »

MDM

  • Walrus Stache
  • *******
  • Posts: 6007
Re: Case Study Spreadsheet updates
« Reply #1 on: April 07, 2016, 03:23:37 PM »
Version 8.01
  • Improved Social Security calculations (SocialSecurity tab).  Within $1 vs. anypia and https://www.ssa.gov/oact/cola/examplemax.html results for spot checks.  Doesn't do "born on the first of the month" adjustments.
  • Changed comment on federal tax result to confirm it is a 2016 value.
  • Added a "pension income" line to the Calculations tab

MDM

  • Walrus Stache
  • *******
  • Posts: 6007
Re: Case Study Spreadsheet updates
« Reply #2 on: April 23, 2016, 03:50:38 AM »
Version 8.02
  • Minor clean-up/reformatting on several tabs
  • Minor graph improvements

MDM

  • Walrus Stache
  • *******
  • Posts: 6007
Re: Case Study Spreadsheet updates
« Reply #3 on: April 28, 2016, 07:27:49 PM »
Version 8.03

    Changed input to saver's credit calculation to use "AGI + Foreign Income exclusion"

MDM

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

MDM

  • Walrus Stache
  • *******
  • Posts: 6007
Re: Case Study Spreadsheet updates
« Reply #5 on: May 16, 2016, 01:34:50 AM »
Version 8.05

    Minor edits and formatting - no functionality change.

swick

  • Global Moderator
  • Magnum Stache
  • *****
  • Posts: 2766
Re: Case Study Spreadsheet updates
« Reply #6 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

jmk

  • 5 O'Clock Shadow
  • *
  • Posts: 6
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.

lhamo

  • Magnum Stache
  • ******
  • Posts: 4891
  • Location: Seattle
Re: Case Study Spreadsheet updates
« Reply #8 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.
Wherever you go, there you are

MDM

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

jmk

  • 5 O'Clock Shadow
  • *
  • Posts: 6
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?

« Last Edit: June 05, 2016, 12:37:53 PM by jmk »

MDM

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

jmk

  • 5 O'Clock Shadow
  • *
  • Posts: 6
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).
« Last Edit: June 05, 2016, 05:04:57 PM by jmk »

MDM

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

jmk

  • 5 O'Clock Shadow
  • *
  • Posts: 6
Re: Case Study Spreadsheet updates
« Reply #14 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
« Last Edit: June 05, 2016, 08:23:38 PM by jmk »

MDM

  • Walrus Stache
  • *******
  • Posts: 6007
Re: Case Study Spreadsheet updates
« Reply #15 on: July 17, 2016, 08:15:58 PM »
Version 8.06

  • Added cells for "full-time student" status and tied those into the saver's credit calculation.
  • Added more cells that can be used for the marginal tax rate calculations.  Any cell can - this merely documents some of the more common.
  • Added bond calculations to the '401k vs Taxable' tab as suggested by jmk.
  • Added tab 'HDHP Analysis' for comparing an HDHP plan with "normal" medical insurance to see which might work better for specific conditions.
  • Misc. edits and format changes.

Case Study Spreadsheet
« Last Edit: July 18, 2016, 09:38:27 AM by MDM »

MDM

  • Walrus Stache
  • *******
  • Posts: 6007
Re: Case Study Spreadsheet updates
« Reply #16 on: August 17, 2016, 11:18:02 AM »
Version 8.07

  • Updated Social Security calculations to use the new Average Wage Index numbers from the 2016 Trustees' report.  Due to lower inflation projections, monthly SS income projections have decreased.
    Also added some input test data from the ssa.gov website.
  • Added some pre-tax vs. after-tax differentiation to the HDHP analysis.

Case Study Spreadsheet
« Last Edit: November 27, 2016, 07:50:18 PM by MDM »

MDM

  • Walrus Stache
  • *******
  • Posts: 6007
Re: Case Study Spreadsheet updates
« Reply #17 on: August 21, 2016, 09:35:06 PM »
Version 8.08

  • Allowed a choice between "no increase" and "Alternative II" for future average wage increases in Social Security calculations.
  • Wordcrafting in a couple of the tabs.

Case Study Spreadsheet
« Last Edit: November 27, 2016, 07:50:34 PM by MDM »

handsnhearts

  • Bristles
  • ***
  • Posts: 283
Re: Case Study Spreadsheet updates
« Reply #18 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.

MDM

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

handsnhearts

  • Bristles
  • ***
  • Posts: 283
Re: Case Study Spreadsheet updates
« Reply #20 on: September 12, 2016, 10:42:38 PM »
Perfect!  That worked beautifully!

Now only if I could understand my spending as easily... sigh...

MDM

  • Walrus Stache
  • *******
  • Posts: 6007
Re: Case Study Spreadsheet updates
« Reply #21 on: October 11, 2016, 10:07:59 PM »
Version 8.09


Case Study Spreadsheet
« Last Edit: November 27, 2016, 07:51:12 PM by MDM »

MDM

  • Walrus Stache
  • *******
  • Posts: 6007
Re: Case Study Spreadsheet updates
« Reply #22 on: October 26, 2016, 07:38:15 PM »
Version 8.10

  • Added the "two break point" Social Security benefit graph on the 'SocialSecurity' tab for visualization.  Calculation methodology remains the same.
  • Updated the 2017 maximum wage base and 2015 wage index on the 'SocialSecurity' tab.
  • Added "tIRA contribution" and "SE income" as options for automatic title generation in the marginal rates chart on the 'Calculations' tab.
  • Revised the 'HDHP Analysis' tab to handle several more tax-related considerations.
  • Miscellaneous clean-up

Case Study Spreadsheet
« Last Edit: November 27, 2016, 07:51:29 PM by MDM »

MDM

  • Walrus Stache
  • *******
  • Posts: 6007
Re: Case Study Spreadsheet updates
« Reply #23 on: November 23, 2016, 09:24:17 PM »
Version 8.11
  • Added a table ('Calculations'!T1:U19) showing the tIRA balance needed, assuming a 4% WR and that the withdrawals are the only income, to reach various tax brackets.
  • Major revision of the 'HDHP Analysis' tab.  There may be too many different insurance terms to have a one-size-fits all analysis tool, but the present version isn't bad.  As always, caveat user.
  • Minor reformatting of the Basic Terms tab
Case Study Spreadsheet
« Last Edit: February 08, 2017, 01:38:01 PM by MDM »

MDM

  • Walrus Stache
  • *******
  • Posts: 6007
Re: Case Study Spreadsheet updates
« Reply #24 on: February 08, 2017, 02:33:46 PM »
Version 8.12
  • Changed the tIRA MAGI calculation to match the IRS, not Intuit, description
  • Provided additional "Table column input cell" choices for the automatic chart title
  • Minor fixes
Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 6007
Re: Case Study Spreadsheet updates
« Reply #25 on: March 08, 2017, 11:36:49 PM »
Version 8.13
  • Changed to use 2017 tax calculations.  By this time, folks ought to be using real tax calculations for their 2016 returns.  The tax calculation portion of the CSS can be useful for planning ahead, but should not be mistaken for commercial tax software.
  • Provided additional "Table column input cell" choices for the automatic chart title on the 'Calculations' tab, and switched to VLOOKUP instead of nested-IFs for this purpose.
  • Added sheet 'Tax Code' (hidden by default) as a step in the direction of automating the yearly tax update.  Not there yet.
  • Minor formatting changes
Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 6007
Re: Case Study Spreadsheet updates
« Reply #26 on: March 16, 2017, 07:28:56 PM »
Version 8.14
  • Added some calculations for the Child and Dependent Care credit, and Education credits.  Seems to work OK but if anyone has an actual situation to test....
  • Minor formatting changes
Case Study Spreadsheet