Custom VBA Function - Tax Calculator - Need Help!

JohnP423

New Member
Joined
Apr 19, 2011
Messages
2
First, I tried downloading the VB-HTML Maker add in and couldn't get it to work. So I apologize if I am breaking the rules.

PROBLEM: Create a VBA function called TaxCalculator that accepts the taxable income from a cell and status from another one and generates the Federal Income Tax using tables

Relevant Information: In my work book I have created two tables. One for filing "Single" (named ScheduleX) and one for filing "Married" (named ScheduleY).

The fist column is "Taxable Income over.." (i.e the BaseIncome). The second column is the applicable tax rate (i.e TaxRate). The third column is the base amount of tax owed (i.e BaseAmt)

What I Got:

Function TaxCalculator(x, y)

'x= Taxable Income
'y = Filing Status


BaseAmt = WorksheetFunction.VLookup(x, Range("ScheduleX"), 3)
BaseIncome = WorksheetFunction.VLookup(x, Range("ScheduleX"), 1)
TaxRate = WorksheetFunction.VLookup(x, Range("ScheduleX"), 2) / 100

Tax = BaseAmt + (x - BaseIncome) * TaxRate

TaxCalculator = Tax

End Function
The problem with this function is it won't change use the applicable table based on the contents of y. Thus when the cell that y references contains "Single" I need the function to use Range("ScheduleX") in the VLookup and when the cell contains "Married" I need the function to use Range("ScheduleY") in the VLookup.

Could anyone please offer advice? Thanks much in advance for your time and help.

I am using Excel 2007 on a Windows Vista laptop.
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Here is what I used:

Function TaxCalculator(x, y)

'x= Taxable Income
'y = Filing Status

If y = "Single" Then
BaseAmt = WorksheetFunction.VLookup(x, Range("ScheduleX"), 3)
BaseIncome = WorksheetFunction.VLookup(x, Range("ScheduleX"), 1)
TaxRate = WorksheetFunction.VLookup(x, Range("ScheduleX"), 2) / 100
Tax = BaseAmt + (x - BaseIncome) * TaxRate
TaxCalculator = Tax

ElseIf y = "Married" Then
BaseAmt = WorksheetFunction.VLookup(x, Range("ScheduleY"), 3)
BaseIncome = WorksheetFunction.VLookup(x, Range("ScheduleY"), 1)
TaxRate = WorksheetFunction.VLookup(x, Range("ScheduleY"), 2) / 100
Tax = BaseAmt + (x - BaseIncome) * TaxRate
TaxCalculator = Tax

ElseIf y = "Widow" Then
BaseAmt = WorksheetFunction.VLookup(x, Range("ScheduleY"), 3)
BaseIncome = WorksheetFunction.VLookup(x, Range("ScheduleY"), 1)
TaxRate = WorksheetFunction.VLookup(x, Range("ScheduleY"), 2) / 100
Tax = BaseAmt + (x - BaseIncome) * TaxRate
TaxCalculator = Tax

ElseIf y = "Married Seperate" Then
BaseAmt = WorksheetFunction.VLookup(x, Range("ScheduleXX"), 3)
BaseIncome = WorksheetFunction.VLookup(x, Range("ScheduleXX"), 1)
TaxRate = WorksheetFunction.VLookup(x, Range("ScheduleXX"), 2) / 100
Tax = BaseAmt + (x - BaseIncome) * TaxRate
TaxCalculator = Tax

ElseIf y = "Head of Household" Then
BaseAmt = WorksheetFunction.VLookup(x, Range("ScheduleYY"), 3)
BaseIncome = WorksheetFunction.VLookup(x, Range("ScheduleYY"), 1)
TaxRate = WorksheetFunction.VLookup(x, Range("ScheduleYY"), 2) / 100
Tax = BaseAmt + (x - BaseIncome) * TaxRate
TaxCalculator = Tax

Else
Msg = "Please use Single, Married, Widow, Married Seperate, or Head of Household as the filing status"

MsgBox Msg

End If

End Function
 
Upvote 0
As we all know, an income is really taxable. That is an infallible truth. All revenue a person earns should be paid or else he may end up needing cash advances to pay all those extra taxes especially if he haven't been reporting all of the revenues. Therefore, having this income calculator, a person can easily calculate his income. It will be a good intervention to monitor the taxes to be paid.
https://personalmoneynetwork.com/cash-advance/
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

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
Back
Top