VLOOKUP in tax table help NEEDED

[CARTMONKEY]

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!

 

[phantom1975]

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

 

[inarbeth]

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.

 

[wellinth]

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