First, I want to point out the prominent disclaimer on the first sheet of the “Tax” workbook. I am not a CPA or a tax attorney. I was a CFO of a Credit Union that was a “not for profit” and therefore exempt from federal income tax. One of the reasons I have collected the tax calculators in this workbook is that I was once asked to build a model to help the board of directors estimate the impact of a retirement package that combined equity (stock) prices, and a federal and state tax “True-Up”. Sometimes referred to as “grossing up”, this is the process of providing the employee an after-tax benefit. There are many examples of companies that true-up benefits. For example, many corporations provide a car allowance for employees that are expected to use their cars for business. The company might give $300 a month to the employee and also pay the state and federal taxes on this benefit so that the employee is actually receiving $300. True-Up will be discussed more later.
The “Tax Table” sheet shows how to use a sumproduct function to calculate your tax liability, given your net income. Net income is after deductions and credits. You can search the web for the tax tables that apply to you, both state and federal. Enter the high limit for each bracket in the yellow cells (Tax_To) along with the tax rate (Rate). Note that I have turned the tax tables into Excel tables. I did both State and Federal to calculate the Effective Tax Rate, which is on it’s own sheet. The Net Income entry is for both tables. Also, the marginal tax rates are found with a vlookup function. The marginal rate is the highest rate you paid. In the federal table below, $100,000 went as high as the 28% tax rate. Marginal tax rates will be used in the “Muni Taxable Equivalents” sheet.
The image below shows how the sumproduct function worked on the Federal table:
The sheet “Effective Tax Rate” is next. The callouts explain the values:
The next sheet is “Muni Taxable Equivalents”. Here we show the formulas for converting both In-state municipal bond yields (free of both state and federal taxes) and Out-Of State municipal bonds (No federal tax, but you have to pay state tax). Both are compared to buying corporate bonds, and Treasury securities. In addition a table shows how to compare In-State to Out-of-State municipals. This is a partial example of the sheet:
The last sheet is the True-Up sheet I mentioned before. It too is self-explanatory:
Download workbook “Taxes” from:
Downloads Written in Excel 2013