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

MDM

  • Walrus Stache
  • *******
  • Posts: 7218
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 relied on for actual tax returns.
« Last Edit: September 10, 2017, 10:28:11 AM by MDM »

MDM

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

  • Walrus Stache
  • *******
  • Posts: 6118
  • 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: 7218
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: 7218
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: 7218
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: 7218
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: 7218
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: 7218
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: 285
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: 7218
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: 285
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: 7218
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: 7218
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: 7218
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: 7218
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: 7218
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

ETA: Attached version 8.12 in case a 2016 retrospective is useful.
« Last Edit: May 01, 2017, 01:09:14 PM by MDM »

MDM

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

MDM

  • Walrus Stache
  • *******
  • Posts: 7218
Re: Case Study Spreadsheet updates
« Reply #27 on: March 30, 2017, 06:47:10 PM »
Version 8.15
  • Added curve fitting for starting annual SS benefits as a function of age.  This is a step toward
    - optimum withdrawal strategy for traditional accounts, Roth accounts, and taxable accounts with significant unrealized capital gains,
    - including when to start SS and/or pensions,
    - with "reasonably rigorous" tax effects considered.
    No idea when (or if) a working version will be done.
  • Minor formatting changes
Case Study Spreadsheet

2Birds1Stone

  • Magnum Stache
  • ******
  • Posts: 3212
  • Age: 30
  • Location: New York
  • Peter Gibbons
Re: Case Study Spreadsheet updates
« Reply #28 on: April 03, 2017, 03:58:34 AM »
MDM, thank you for your contributions to these forums. They are appreciated!
"A small house can hold as much happiness as a big one." - Fortune Cookie

29 Months till FI - Stop by, or stay a while.....
https://forum.mrmoneymustache.com/journals/fire-by-thirty-five-chronicles-36-months-till-sabbatical!/

MDM

  • Walrus Stache
  • *******
  • Posts: 7218
Re: Case Study Spreadsheet updates
« Reply #29 on: May 12, 2017, 04:55:36 PM »
Version 8.16
  • Added a screen shot to the instructions for the marginal rate chart, with supporting text.
  • Corrected an error in the COLA modification needed for some situations on the SocialSecurity tab.
  • Fixed the incorrect equation in the previous point so the root cause (adding some documentation thus changing the offset needed in the INDIRECT function) won't recur.
  • Updated the examples used on the ssa.gov site and confirmed the spreadsheet matches those results.
Case Study Spreadsheet

nickybecky1

  • Stubble
  • **
  • Posts: 163
  • Location: PNW
Re: Case Study Spreadsheet updates
« Reply #30 on: May 17, 2017, 12:55:08 PM »
Hi MDM!

Thank you so much for the amazing case study spreadsheet!! My husband and I have used an older version of the spreadsheet for awhile and I just stumbled across the "updates" thread and downloaded the latest version. It's so great!! I had an old spreadsheet I'd used to compare HDHP options at my employer to the others, but yours takes into account more tax savings than I'd been able to calculate and the graph is SO helpful!

MDM

  • Walrus Stache
  • *******
  • Posts: 7218
Re: Case Study Spreadsheet updates
« Reply #31 on: May 17, 2017, 04:08:02 PM »
Hi MDM!

Thank you so much for the amazing case study spreadsheet!! My husband and I have used an older version of the spreadsheet for awhile and I just stumbled across the "updates" thread and downloaded the latest version. It's so great!! I had an old spreadsheet I'd used to compare HDHP options at my employer to the others, but yours takes into account more tax savings than I'd been able to calculate and the graph is SO helpful!
You're very welcome.

If there are any other features that would be useful, let me know.  No guarantees - e.g., I'm not likely to code every state tax algorithm ; - but if it would be broadly useful.... :)

MDM

  • Walrus Stache
  • *******
  • Posts: 7218
Re: Case Study Spreadsheet updates
« Reply #32 on: June 03, 2017, 02:26:18 PM »
Version 9.0
  • Added income options for interest and tax-exempt interest.
  • Added calculations for Medicare insurance premiums if one is age 63 or older.  In reality, current MAGI affects premiums
    two years later, not the current year, but this could help one avoid an inadvertent move to a higher premium.
Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 7218
Re: Case Study Spreadsheet updates
« Reply #33 on: June 07, 2017, 11:04:57 PM »
Version 9.01
  • Added table (Calculations!T22:U28) for Head of Household tIRA assets needed to reach various tax brackets, assuming no other income and 4% withdrawal rate.
  • Corrected cell reference in AMT calculations for itemized deduction limitation.
Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 7218
Re: Case Study Spreadsheet updates
« Reply #34 on: September 15, 2017, 11:34:19 PM »
Version 9.02
  • Added some accounting for paying rental mortgage principal.
  • Minor fixes and formatting changes.
  • Check both wages and SE income for 401k contribution limit.
  • Added "state" tax rate to the automated display options for the marginal rate chart Y-axis.
  • Added "457" and "employer-sponsored HSA" to the automated display options for the marginal rate chart X-axis.
  • Added an annuity option to the "Growth in a taxable account" section of the 'Misc. calcs' tab.
Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 7218
Re: Case Study Spreadsheet updates
« Reply #35 on: September 28, 2017, 01:14:02 AM »
Version 9.03
  • Fix calculations for some EIC and taxable SS situations.
  • Minor fixes and formatting changes.
Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 7218
Re: Case Study Spreadsheet updates
« Reply #36 on: October 14, 2017, 12:14:56 AM »
Version 9.04
  • Minor updates to some state tax calculations
  • Eliminate option for user input of a flat state tax rate
  • Allow education credits for more than one person
  • Updated Soc. Sec. Average Wage Index, COLA, and maximum taxed earnings per yesterday's government announcement.
  • Use current effective state tax rate to estimate state tax rate in retirement (in the "Time to FI" section)
  • Minor fixes and formatting changes.
Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 7218
Re: Case Study Spreadsheet updates
« Reply #37 on: October 24, 2017, 11:48:11 PM »
Version 9.05
  • Significant revision to 'Calculations' sheet input
    • Separate earning and contribution amounts for MFJ filers
    • Choose among monthly, annual, bi-weekly, etc. for many inputs
    • Probably others not listed
  • Corresponding changes to 'Instructions' and 'Posting' tabs
  • Minor fixes and formatting changes.
The beta testing group thinks it's good to go, but there were many changes.  Thus a reminder, caveat user, seems appropriate. ;)

Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 7218
Re: Case Study Spreadsheet updates
« Reply #38 on: October 31, 2017, 02:52:48 AM »
Version 9.06
  • Formatting and minor fixes to 'Calculations' sheet input
  • Corresponding changes to 'Posting' tabs
  • Added options for annuity vs. annuity due for growth of a taxable investment on the 'Misc. calcs' tab
Federal tax calculations should remain good for a wide (but not all-inclusive) variety of cases.

State calculations should be good for "simple" cases.  State Income Tax calculations - Crowdsourcing request remains available for suggestions.

Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 7218
Re: Case Study Spreadsheet updates
« Reply #39 on: November 19, 2017, 10:10:46 PM »
Version 9.07
  • More complete treatment of capital gains: allowing entries for short and long term, separate from qualified dividends.
  • Added option for local (e.g., city) income tax as a percentage of federal AGI.
  • Various clean-ups from the income frequency changes

Case Study Spreadsheet
« Last Edit: November 19, 2017, 10:12:31 PM by MDM »

MDM

  • Walrus Stache
  • *******
  • Posts: 7218
Re: Case Study Spreadsheet updates
« Reply #40 on: November 30, 2017, 03:20:38 PM »
Version 9.08
  • Added cells showing phase-out marginal rates for EITC.
  • Improved formatting for various lines in the Posting tab.
  • Show Medicare premiums separately if applicable.

Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 7218
Re: Case Study Spreadsheet updates
« Reply #41 on: December 04, 2017, 01:03:37 AM »
Version 9.09
  • Include employer match in pre-tax amount for FI calc'n.
  • Improve formatting for various lines in the Posting tab.
  • Add another automatic title option to marginal rate chart.

Case Study Spreadsheet

MDM

  • Walrus Stache
  • *******
  • Posts: 7218
Re: Case Study Spreadsheet updates
« Reply #42 on: December 14, 2017, 11:41:48 PM »
Version 9.10
  • Allow forced use of itemized deductions (can be useful to avoid AMT when charitable contributions are significant).
  • Include local tax in AMT calculations.
  • Reformat "pension vs. annuity" section in 'Misc. calcs'.
  • Add "Estimating withdrawal tax rates" section in 'Misc. calcs'.

Case Study Spreadsheet