JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,676
- Office Version
- 365
- Platform
- Windows
I would appreciate any comments on this UDF to calculate taxes. Once I am sure that it is working, I will move the constants (brackets and rates) to the calling sheet and pass them to the UDF. I'll also have the calling sheet calculate the cumulative maximum taxes for each bracket, so the UDF doesn't have to do it over and over.
Here's a minisheet calling it.
Thanks
VBA Code:
Function TaxCalc(pIncome As Double) As Double
Dim BracketTop(0 To 7) As Double 'The upper end of the brackets
Dim BracketWid(1 To 6) As Double 'The width of each bracket
Dim Rate(1 To 7) As Double 'The tax rates in each bracket
' Bracket constants. Move these to the sheet and pass them as a range
BracketTop(0) = 0
BracketTop(1) = 10000: Rate(1) = 0.1 '10% in bracket 1
BracketTop(2) = 40000: Rate(2) = 0.12 '12% in bracket 2
BracketTop(3) = 86000: Rate(3) = 0.22 '22% in bracket 3
BracketTop(4) = 165000: Rate(4) = 0.24 '24% in bracket 4
BracketTop(5) = 209000: Rate(5) = 0.32 '32% in bracket 5
BracketTop(6) = 524000: Rate(6) = 0.35 '35% in bracket 6
BracketTop(7) = 0: Rate(7) = 0.37 '37% in bracket 7
'The cumulative maximum taxes. Do this in the sheet, too
Dim CumTax(0 To 6) As Double
CumTax(0) = 0
Dim iLoop As Long
For iLoop = 1 To 7
If iLoop = 7 Or pIncome <= BracketTop(iLoop) Then
TaxCalc = CumTax(iLoop - 1) + (pIncome - BracketTop(iLoop - 1)) * Rate(iLoop)
Exit For
End If
BracketWid(iLoop) = BracketTop(iLoop) - BracketTop(iLoop - 1)
CumTax(iLoop) = CumTax(iLoop - 1) + BracketWid(iLoop) * Rate(iLoop)
Next iLoop
End Function
Here's a minisheet calling it.
Wages by Profession.xlsx | |||||
---|---|---|---|---|---|
C | D | E | |||
3 | Gross Income | Taxes | % | ||
4 | $0.00 | $0.00 | #DIV/0! | ||
5 | $1,000.00 | $100.00 | 10.00% | ||
6 | $10,000.00 | $1,000.00 | 10.00% | ||
7 | $11,000.00 | $1,120.00 | 10.18% | ||
8 | $20,000.00 | $2,200.00 | 11.00% | ||
9 | $40,000.00 | $4,600.00 | 11.50% | ||
10 | $86,000.00 | $14,720.00 | 17.12% | ||
11 | $165,000.00 | $33,680.00 | 20.41% | ||
12 | $209,000.00 | $47,760.00 | 22.85% | ||
13 | $524,000.00 | $158,010.00 | 30.15% | ||
14 | $1,000,000.00 | $334,130.00 | 33.41% | ||
15 | $5,000,000.00 | $1,814,130.00 | 36.28% | ||
Sheet5 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D4:D15 | D4 | =taxcalc(C4) |
E4:E15 | E4 | =[@Taxes]/[@[Gross Income]] |
Thanks