Case Study Spreadsheet updates

[MDM]

The Case Study Spreadsheet (CSS) contains various calculations applicable to personal finance, including

• Tax planning, including many common credits and extra taxes, for both federal and state taxes.
• Traditional vs. Roth vs. non-deductible IRA vs. taxable investment choice
• Social Security benefit estimates
• "HDHP plus HSA" vs. "non-HDHP and no HSA" comparison
• Lump sum vs. pension

and others.

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, and follow the outline in the "How to Write a Case Study" sticky.

See this post and the 'Instructions' tab in the spreadsheet itself for - you guessed it - instructions.

For more instructions with accompanying screen shots, the "Using a spreadsheet" section of the Roth IRA conversion - Bogleheads wiki discusses that topic, and perhaps best of all Harry Sit at The Finance Buff has two great step-by-step articles: Roth Conversion and Capital Gains On ACA Health Insurance and Roth Conversion with Social Security and Medicare IRMAA.  All the above show how to provide common inputs such as filing status, age, income types, etc.

2022/2023/2024 taxes version: Case Study Spreadsheet

Note:

• Rather than "Request Edit Access" to the file on Google Drive - which, if granted, would allow one to edit the same file that everyone else sees, so it will not be granted ;)
  - Click File>Download>Microsoft Excel (xls)
  
  Then, either open in Excel immediately and later save from Excel to your computer, or save immediately to your computer and later open that file with Excel.

• See https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg2138684/#msg2138684 and https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/msg1879496/#msg1879496 regarding warnings one might see, and why those are spurious warnings for these files.

In early 2023, Microsoft added a protection/roadblock to using downloaded Excel files containing macros.  Details can be found at A potentially dangerous macro has been blocked - Microsoft Support.

Most of the CSS can be used just fine without having macros enabled.  If you want to change the x-axis or y-axis variable for the marginal rate chart, however, you either need to know how Excel data tables work and edit the formulas directly, or enable macros and use the Calculations!G107 or L107 cells along with clicking the "Update chart" button near cell L114.

It appears the simplest way to get around Microsoft's "helpful" macro block is described in the "Details can be found at..." link above (see that for a screen shot of step #3 below):
  1. Open Windows File Explorer and go to the folder where you saved the file.
  2. Right-click the file and choose Properties from the context menu.
  3. At the bottom of the General tab, select the Unblock checkbox and select OK.

[MDM]

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]

Version 8.02

• Minor clean-up/reformatting on several tabs
• Minor graph improvements

[MDM]

Version 8.03

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

[MDM]

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]

Version 8.05

    Minor edits and formatting - no functionality change.

[swick]

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]

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.

[MDM]

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.... 

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.

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.

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.

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]

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?

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?

[MDM]

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.

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]

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).

[MDM]

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. :)

You actually set sc equal to sw (probably as a short cut)...

Actually sw equal to sc, but yes it's another heuristic.

...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]

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

[MDM]

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

[MDM]

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

[MDM]

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

[handsnhearts]

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]

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]

Perfect!  That worked beautifully!

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

[MDM]

Version 8.09

• Added SandP500_5-Year_Rolling_Returns_with-CPI_calendar_year.pdf to the Investment Order tab.
  
• Added note that using cell B26 as the "table column input cell" provides marginal rates vs. Long-Term Capital Gains & Qualified Dividends
• Modified the 'Misc. calcs' Investing vs. mortgage payoff calculation to allow mortgage comparisons.  See 20 year or 30 year mortgage?
• Miscellaneous clean-up

Case Study Spreadsheet

[MDM]

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

[MDM]

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

[MDM]

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]

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.

[MDM]

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]

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]

MDM, thank you for your contributions to these forums. They are appreciated!

[MDM]

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]

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]

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]

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]

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]

Version 9.02

• Added state tax brackets.  See State Income Tax calculations - Crowdsourcing request for more on this change.

• 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]

Version 9.03

• Updated state tax calculations to
  - Allow for earned income and child tax credits as a percent of federal
  - Handle various approaches to social security benefits
  - Include comments made in State Income Tax calculations - Crowdsourcing request

• Fix calculations for some EIC and taxable SS situations.
• Minor fixes and formatting changes.

Case Study Spreadsheet

[MDM]

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]

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]

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]

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

[MDM]

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]

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]

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

[MDM]

Version 9.11

• Allow more automated x-axis variables for the marginal rate chart.

Intent is for this to be the last version for 2017 tax law. 

Federal 2018 changes will be released "soon."

State 2018 updates will come when the 2018 version of https://taxfoundation.org/state-individual-income-tax-rates-brackets-2017/ (or similar reference) is published.

Case Study Spreadsheet

[KindaichiShota]

Ver 9.11 works great :O

[MDM]

Version 10.00

• 2018 tax law changes.

AFAIK this incorporates all the 2018 tax law changes that affect items already handled in previous versions, plus a few new items.

These items include
- brackets, rates, and standard deductions
- dependent care, education, saver's, earned income, and child tax credits
- Alternative Minimum, Net Investment Income, Social Security, and Medicare taxes
- Medicare Part B premiums based on IRMAA
- SALT $10K limit for itemized deductions; no miscellaneous itemized deductions
 
As with any major software update, caveat user.  Reports of errors, omissions, etc. appreciated.

Most recent (2018 or later) version: Case Study Spreadsheet

The final 2017 version (9.11) is here: https://drive.google.com/file/d/1Kxk0_A1gEzg9BB8OFUZeXl_DqitYQIa2/view?usp=sharing

[Gin1984]

How do you specific a state in the excel doc?

[MDM]

How do you specific a state in the excel doc?

Enter state postal abbreviation in cell H32 (on the 'Calculations' tab).

[nolesrule]

There is a bug in the Itemized Deduction calculation. Cell G38 should contain the formula "=SUM(G31,MIN(10000,SUM(G32:G34)),G35:G37)".

Right now "G35:G37" is inside the MIN() function, which is not correct. So effectively the current calculation takes the minimum of $10k, SALT or Charity+Medical and adds it to Mortgage Interest.

Also, I would like to request the addition of a tax line for calculating Additional Medicare Tax due with the 1040 return based on Form 8959.

[MDM]

Version 10.01

• Fixed SALT limit (thanks nolesrule!)
• Added a 60% of AGI limit on charitable contributions.

Note that more restrictive limits on charitable contributions, such as the 30% of AGI on appreciated stock, are not included.

[doneby35]

This is one of the best spreadsheets I've seen!
Does the HSA employer match go in B19 "employer match" along with the 401k employer match, or do you just put the total (contributions + employer match) in "employer-sponsored HSA"?