Thread where new versions are announced:

Case Study Spreadsheet updates (mrmoneymustache.com)

Enter filing status and related information in

in G2 through I12.

Enter significant income and contributions in

in columns B-D, from B2 down through D60. Use positive numbers unless directed otherwise.

- Entries are arranged in similar order to what goes on Form 1040. E.g., W-2 income followed by interest, dividends, etc.

- Some columns have selectable frequencies (annual, monthly, biweekly, etc.). Adjust as convenient, or mulitply/divide by 12, etc., for your numbers.

- Enter state postal abbreviation in cell H38.

- Enter the tax year of interest in cell R2.

- That will do a quick tax calculation, with summary results in cells D59:D67, more details in G11:G42, and even more details in the various "IRS form" sections

For more detailed cash flow (and tax, particularly if itemizing deductions) calculations, enter values in

wherever appropriate.

- For mortgage and personal loans, enter the original principal, length, and interest rate in cols. E, F, and I in the appropriate rows. Excel will calculate the payment.

That's all - you can always add more, but above is all that is needed to get started on a case study (or your own financial self-exam).

General Information:

This workbook helps one organize income, tax, expense, and investment cash flows.

Once organized, one can do various "what if?" (e.g., what if 401k withholding is maximized?) studies.

By default it uses continuous tax formulas to give smooth marginal rates. To get accurate-to-the-dollar tax results but noisy marginal rates, change R83 from Y to N.

There is also a simplified evaluation of "how long to Financial Independence?".

A chart with marginal and cumulative total tax rates as a function of traditional account withdrawals starts at cell F85.

- Other independent variables and tax categories can be chosen by modifying the entries in cells G110 and L110, then

clicking the "Update chart" button near cell L118.

- Change the value in cell P87 to start the marginal rate calculation at that value.

- Change the value in cell P86 to change the range over which the x-axis variable will move.

User entries go in the cells with green shaded backgrounds. Other cells will be calculated based on user entries.

The worksheet is protected to prevent unintentional changes to calculations.

If you want to change a "non green" cell, simply remove protection by going to the Excel Menu:

Depending on the version of Excel, that might be

Tools>Protection>Unprotect Sheet, or

Review>Unprotect Sheet

There is no password.

Caveat User. Some assumptions and limitations are below. Requests to overcome these (particularly if accompanied by Excel formulas) will be considered.

- No check is made to enforce compliance with the various IRS rules on IRA, 401k, HSA, etc. contributions

- State taxes are estimated using state tax brackets with standard deductions and exemptions, if the state postal code is entered in cell H37 and

a local tax as percent of federal AGI is entered in H39.

- See https://forum.mrmoneymustache.com/taxes/state-income-tax-calculations-crowdsourcing-request/

- Some state estimations will be better than others, depending on the complexity of the state's tax code and the individual's situation.

- Foreign Earned Income Exclusion (FEIE) is considered. It may even be considered correctly for straightforward tax situations,

e.g. ones with no significant capital gains or qualified dividends. Again, caveat user.

- You may enter your own estimate of overall tax rate in retirement (cell B174), or accept the result shown in cell C192 if estimating "time to FI".

- Assumes no non-wage dependent care benefits

- Foreign tax credits may be entered in cell G21.

- HSA contributions are assumed to grow and be withdrawn tax free.

- For those age 63 or older, Medicare premiums are calculated using current MAGI. 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.

- The Premium Tax Credit (PTC) for those with "simple" ACA insurance will be calculated if one enters the

- Enrollment Premium (aka the cost if one would receive no Premium tax credit) in cell B117.

- Advance Premium Tax Credit in cell B118. Enter this as a negative number.

- Second Lowest Cost Silver Plan (SLCSP) cost for the year in cell AE103.

- See https://thefinancebuff.com/tax-calculator-aca-obamacare-subsidy.html for a nice "how to" with pictures. Thanks, Harry Sit (aka thefinancebuff).

- Premiums possibly eligible for the Self-Employed Health Insurance (SEHI) subtraction may be entered in row 48.

- A limit of Schedule C net income minus 1/2 SE tax is enforced. This will be too generous if one has multiple Schedule Cs.

- Any premiums not eligible for the SEHI subtraction will be added to the itemized medical expense calculation. Premiums entered in row 51 should be less than or equal to row 117.

- The spreadsheet will handle the iterative calculations when both PTC and SEHI are involved, at least for one Schedule C and one SEHI premium.

At this time it does not use Worksheets Y and Z "for Self-Employed Individuals Who Claim Certain Deductions/Exclusions". See https://www.irs.gov/pub/irs-pdf/p974.pdf.

In other words, the calculations handle only tIRA contributions that are fully deductible. Set L40 (and M40 if MFJ) to "N" if you have tIRA contributions.

While exhaustive testing is not possible, these calculations have been tested successfully over a wide range of conditions. To enable,

- Enter the appropriate values in cells B117, B118, AD126, and AE103

- Enter the formula =AD162 in cell B51.

- Entering a specific value, e.g., 0 (zero) in cell B51 will eliminate iteration.

- Ensure the "Enable iterative calculation" box is checked in File > Options > Formulas

Iteration requires significant extra calculations, slowing results. If the SEHI/PTC iterations will converge they do so quickly. Setting "Maximum iterations" to 10 should suffice.

Estimating traditional withdrawal marginal tax rates

See the box starting at P54.

This can be used to take your current situation and project how it might look in the future, as an aid to the traditional vs. Roth choice for this year.

To use it, make entries in this box, then remove the "*0" from the formulas in the cells indicated in S58:S66. Adjust entries for W-2, self-employment, etc., to match what you expect (e.g., "zero").

The marginal rate chart, with traditional withdrawals as the X-axis variable, will give an overall picture of marginal rates for various traditional withdrawal amounts.

See resources such as

https://forum.mrmoneymustache.com/investor-alley/estimating-withdrawal-tax-rates/

https://forum.mrmoneymustache.com/investor-alley/investment-order/msg1333153/#msg1333153

https://www.bogleheads.org/wiki/Traditional_versus_Roth

https://www.bogleheads.org/wiki/Roth_IRA_conversion

for more detailed discussion.

W-4 choices, based on one's overall tax situation, may be tested in cells F57:I72.

Cell I66 and I71 are prefilled with defaults but may be overriden as needed.

For the simplest yet accurate approach, leave W-4 lines 4a and 4b blank.

Enter the amount already withheld for the year in I70, and the fraction of the year remaining (for the new withholding) in I71.

Adjust G63 (step 3) or G66 (step 4c) to drive the expected refund at filing (cell I69) to ~$0.

Step 3 entries are not restricted to Child Tax Credits - one may enter any number, regardless of the number of dependents.

Note that there are various "safe harbors" to avoid penalties due to under-withholding. See https://www.bogleheads.org/wiki/Estimated_tax#Safe_harbors.

Always check the results of this or any other W-4 estimator against your actual withholding and expected tax due.

There are sixteen tabs (aka sheets) in this workbook

Instructions - What you are reading now

Calculations - The main tab. "Current cash flow" and "time to FI" calculations, including many common federal tax credits and limits.

Posting - Formatted for use in Simple Machines forum posts

State Tax - State income tax calculations. The simpler the state tax code, the more accurate this calculation here.

State Brackets - State income tax brackets, thanks to https://taxfoundation.org

Tax Rates - Calculation and graph for marginal and overall tax rates

Form6251 - Alternative Minimum Tax calculation

Form8606 - Nondeductible IRAs. Also includes Publication 590-B worksheet 1-1. Part of the Backdoor Roth IRA process.