VLOOKUP in tax table help NEEDED

CARTMONKEY

New Member
Here's the situation. I have a tax table. I need to calculate the taxes that a person must pay, given their taxable income.
Here's basically how it is setup:

The IF is the main function. The argument of the IF function is whether the person is single or married(I know how to do that).
The second variable to the IF function is three VLOOKUPS for the single person table, and the third variable for the if function is the three VLOOKUPS for the married person table.

All together there are six vlookups and one if function. I hope this helps and I'm sorry if it sounds confusing!

I just need the first 3, for the single person, and I can figure out the rest.

The mathmatical formula is:
Taxable Income - Threshold Taxable Income(This is Vlookup #1) * Marginal Rate(this is #2) + Basic Amount(this is #3).

Taxable income is given in a single cell. The Threshold Taxable Incomes, Marginal Rates, and Basic Amounts are all in a table, and VLookup seeks out the correct location for them.

Here is the psuedocode for the part I need:
IF(tax status="single", (Taxable Income - VLOOKUP(Threshold Taxable Income) * VLOOKUP(Marginal Rate) + VLOOKUP(Basic Amount)).

I have figured out everything upto calculating the Taxable Income-Threshold income, and that code is:
IF(C16="Single", D16-VLOOKUP (D16,\$B\$8:\$D\$13,1).

I cannot for the life of me figure out the rest of this code(for the final 2 parts).

Here is the Excel Table setup:
C16=Single
D16=\$43,245. This is the Taxable Income.

COLUMN 1 of the Table is Threshold Taxable Income.
COLUMN 2 is Basic Amount.
COLUMN 3 is Marginal Rate.

\$B\$8:\$D\$13 is the Vlookup table area.

Just for reference, a line on EXCEL looks like:
Threshold Taxable Income B19=\$24,000
Basic Amount C19=\$3,900
Marginal Rate D19=15.0%
Taxable Income D16=\$43,245

Math would be (D16-B19)*(D19)+C19.
The Vlookup needed is above.

HELP!

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
WELCOME TO THE BOARD!

I must admit that it is sorta tough to sort out all that you have listed and get to the core issue. What might make it easier to help you is if you refrain from terms and jargon and just get to the basics. Basically, state where the information is and what you want done with it. If you'd like to email your spreadsheet to me, I'll gladly take a look at it and help you with the formula you need. billy.rowe@cox.net

CARTMONKEY, I agree with Phantom 1975 about the complexity of your question. You may get closer to an answer by thinking about the design of your lookup table. One possibility is that if you put the rates for a single person in one column and rates for a married person in the next column you can then nest an IF function within the col_index_num argument of the VLOOKUP function to give either a 1 or a 2 to determine which column in the table_array will be used. Tha t keeps the table and the formulas simple.

I am not familiar with the US tax system (for which I am eternally grateful) and so would need more information to be able to help.

Dear Cartmonkey,

Rather than using a nested if function to calculate marginal tax rates, why not write a VBA function to do the job? The code below should work. (I don't use VBA much, so any comments about my code are welcome.) If you need a hand adding this code to your Excel file, please let me know. Hope this helps.

Sincerely,

Tom Wellington

Here's the code:

Public Function marg_tax(salary As Variant) As Variant
'Computes the total due on taxable income using the US 2007 tax schedules found in Wikipedia
'For single filers.

Dim bracket As Double, tax As Double

If Not IsNumeric(salary) Or salary <= 0 Then 'No income or invalid income
marg_tax = Format(0, "Currency")
Exit Function
End If

tax = WorksheetFunction.Min(salary, 8350) * 0.1 'First \$8350 taxed at 10%

If salary > 8350 Then '\$8351 to \$33590 taxed at 15%
bracket = (WorksheetFunction.Min(salary, 33590) - 8350) * 0.15
tax = tax + bracket
End If

If salary > 33590 Then '\$33591 to \$82250 taxed at 20%
bracket = (WorksheetFunction.Min(salary, 82250) - 33590) * 0.2
tax = tax + bracket
End If

If salary > 82251 Then '\$82,251 – \$171,550 taxed at 28%
bracket = (WorksheetFunction.Min(salary, 171550) - 82251) * 0.28
tax = tax + bracket
End If

If salary > 171550 Then '\$171,551 – \$372,950 taxed at 33%
bracket = (WorksheetFunction.Min(salary, 372950) - 171551) * 0.33
tax = tax + bracket
End If

If salary > 372950 Then '\$372,951+ taxed at 35%
bracket = (salary - 372950) * 0.35
tax = tax + bracket
End If

marg_tax = Format(tax, "Currency")

End Function

Replies
6
Views
330
Replies
9
Views
597
Replies
0
Views
203
Replies
27
Views
739
Replies
4
Views
474

1,218,746
Messages
6,144,265
Members
450,533
Latest member
xoxo1998

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back