VLOOKUP in tax table help NEEDED

CARTMONKEY

New Member
Joined
Oct 8, 2002
Messages
1
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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

Well-known Member
Joined
Apr 8, 2002
Messages
912
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

Board Regular
Joined
Aug 6, 2004
Messages
57
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
 

Forum statistics

Threads
1,144,770
Messages
5,726,186
Members
422,661
Latest member
foxleinlady

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?

Disable AdBlock

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

Disable AdBlock Plus

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
Top