Downloadable Excel Spreadsheet

Disclaimer 1: The Federal Tax Laws and Regulations are over 10 million words in length, and that number does not include the laws and regulations created by each of our 50 States, over 3,000 Counties, and who knows how many Cities and Townships! The spreadsheet included with this website is extremely simplified and only covers the Federal Taxes. It is only designed to give you a perspective of the Marginal Tax Rates that you might face during your retirement and to provide you with some ideas on things you might do to avoid those extreme rates. You can use it at your own risk, but we highly recommend that you double check the results with other sources.

Disclaimer 2: And the tax laws are constantly changing. A few years ago the normal rates were 10%, 15%, and 25% with Tax Hump rates of 46.25% and 55.5% after the 27.75% Sweet Spot. Then the rates were changed to 10%, 12% and 22% which lowered the Tax Hump rates to 40.7% and 49.95% after the 22.2% Sweet Spot. My advice back then was that doing a Roth Conversion at 25% was better than paying 27.75% during retirement. The 25% conversions I did 3 years ago were profitable compared to the 27.75% Sweet Spot that existed at that time, but are now a small loss compared to the 22.2% Sweet Spot that exists today. That is OK with me because it also means that the RMDs that I have to withdraw each year are smaller and I have more tax free income available.

The spreadsheet

The spreadsheet attached to this website is designed to give you a vivid picture of Your Personal Marginal Tax Rates and the Personal Tax Hump that they create. It consists of four tabs:

  1. The [Marginal] tab accepts your personal tax related input and calculates your taxes with and without including your optional “What If” data. We will discuss this tab in great detail shortly.
  2. The [Brackets] tab captures the Married TRUE/FALSE information from the Marginal tab, then copies the appropriate tax bracket information from the bottom of the page to the top where it will then be used for all of the tax calculations.
  3. The [Graph] tab uses 1,000 data points to create a graph of your personal Tax Hump.
  4. The [Graph2] tab creates a parallel personal Tax Hump including your What If data.

Downloading

Before I give you the link to my Parallel Taxes Excel Spreadsheet, read the following instructions so you can properly save it to your hard drive.

Your browser does not properly emulate Excel, so the graph that is displayed on the google drive page will probably have a few extra strange lines in it. Just ignore the extra lines for now and do the following after you follow the link!

Hit the down arrow in the upper right corner of the page to download the file to your computer.

Then open the file with Excel, if you have it:

It will open read only, so hit the [Enable Editing] button before you save the file to your disk.

OK, here is the link to download the spreadsheet, ParallelTaxes.xlsx, from my google drive. As I understand it, everything on google drive is auto scanned for viruses, but use it at your own risk!

https://drive.google.com/open?id=1k_3sVyEoyD6l7qv_B7GeRgyoSrgLaUqp

This is what you get!

You enter your personal information in the yellow User Input frames at the top left of the Marginal tab.

The Tax Calculator is the primary purpose of this spreadsheet. It uses the Tax Brackets selected in the Brackets tab and the personal information that you entered in the User Input frames to calculate the taxes you will owe based on data you entered for You and your Spouse in the first tax calculator column plus the data you enter in the What If column in the last tax calculator column. This data is then used to give you the following information:

The Extra Income frames below your User Input will detect the increase in your taxes from the Tax Calculator, compare it to the amount of extra income you are looking at in the What If column and calculate your Federal Marginal Tax Rate and your total marginal rate including your personal state and local tax rates.

The To LTCG and To Hump frames let you know how much additional income you can afford before entering your personal tax hump. This information is supplied for both your now and What If situations. Red numbers are negative and let you know how far your income is into the hump, how much you have to cut back to avoid the hump!

It is important to note that the To Hump and To LTCG numbers represent the available increase in your "Taxable Income". Just remember that while you are in the 85% taxability bracket for your Social Security Benefits, each additional dollar of actual income increases your taxable income by $1.85. The amount of additional income / IRA or 401k withdrawals is the second number in each case, the at 85% number!

You are also given the information on how much of your Tax Delayed Social Security has been taxed and how much is still tax free income!

A picture is worth a thousand words!

But in this case it could be worth thousands of dollars!

The extra income frames indicated that the extra $5,000 would cost $2,028 in extra Federal taxes. If this extra cash had been withdrawn or converted before retirement at the 22% Federal tax bracket, the Federal tax would have only been $1,100, a $928 savings if you were aware of the future interaction of your desired standard of living and your personal tax hump.

Your Tax Hump did not change in this example because the What If data only included $5,000 of extra taxable income, so the solid purple marginal bracket line is directly over the solid red line. The dotted red line illustrates your Federal Tax Brackets while the dotted green line illustrates the 50% and 85% parallel taxation of your Social Security benefits for the combined Spouse and You data.

The important data is represented by the tic marks. The solid green tick mark illustrates the gross income data that you entered in the Spouse and You columns. Note its relationship to the solid red marginal tax line, you are very close to your personal Tax Hump. Also note the distance between the solid green gross income tick and the solid blue After Fed Tax tick.

What If your situation at the end of the tax year was a little different?

You enjoyed a comfortable year on a $59,000 gross income, paying only $2,704 in Federal taxes.

Looking at your solid green tick mark, and the To LTCG (at 85%) data, you can take additional taxable income out of your IRA at the 22.2% marginal tax rate this year which could help you to avoid the 40.7% marginal tax hump next year!

The spreadsheet says that you can take about $6,000 out of your IRA, but you decide to play it safe and take out only $5,000 and the spreadsheet indicates that you will only pay 22.2% in Federal taxes.

If you take the extra $5,000 out of your IRA in December, you could take that much less out in January! Next year, if all goes as normal, you would be able to do the same for an extra $10,000, or, if an unexpected expense happened during that year, you could have covered it without paying any of your huge Hump Taxes!

As with everything else on this website, verify your estimated taxes with and without the extra income from other sources.

Federal Tax Brackets

The [Brackets] tab of the spreadsheet is used to create the proper tax brackets, deductions, and long term gain tax levels.

Both of the tax data tables at the top of this page are created by your input on two entries just below the tax data tables.

As indicated, the Married True/False value is copied from cell H3 on the [Marginal] tab which is defined by if you have entered any non-zero dollar amounts in the Spouse column at the top left of the [Marginal] tab.

You can select the year cell, the safest way to do this is to click on the cell, a small down arrow will appear next to the cell, click on the down arrow, then select a valid year or COLA adjusted percentage from the drop down list.

Now that we have a spreadsheet that will calculate Your Personal Marginal Tax Rates, let's examine how this spreadsheet can be used to avoid your Personal Tax Hump.

Next - Your What If calculations!