Dynamic tax calculator

[Roofasa]

Hi Guys,

I have to write a sub that asks for income, and passes it to a function to calculate tax. My problem is that the function has to be dynamic, so that the various inputs (Eg. tax levels and tax %'s can be changed, or other rows added) can be altered.

How can I do this?
Thanks so much for the help!

Tax_Schedule.xlsm

[SamT]

I assume this is homework assignment because AFAIK, all revenue department tax tables have all that built in.

None of the below is executable code. It is just to give you an idea of the algorithm

Declare two arrays,arrBreakPoint and arrTaxPct.

Loop thru the arrays from Lbound Array to Ubound Array - 1
with
While Income > ArrBreakPoint(n)
TotalTax = TotalTax + (ArrBreakPoint(n) - ArrBreakPoint(n-1)) *arrTaxPct(n)
Loop

If Income > ArrBreakPoint(n+1) then TotalTax = Income - ArrBreakPoint(n+1) * arrTaxPct(n+1)

[Roofasa]

Yeh, homework assignment. Would have been nice if our professor had discussed arrays before making us do this.
Thanks, wasn't looking for executable code, just an idea of how I could go about solving it

[SamT]

Dim myArray As Variant
myArray = Range("A4:A10").Value

Array indices start at 0, so LBound(myArrayabove) = 0 and UBound(myArrayabove) = 6

Using For i = Lbound(array) to Ubound(Array) means that doesn't matter.

I bet he also didn't tell you that the only Variables that don't need meaningful names are i, j, and k, because since before the birth of UNIVAC, those have been used as counters and indices.

For extra points, place your function in a Standard Module and declare it like this

Public Function TotalTaxes(Income As Double) As Double

That makes TotalTaxes a User Defined Function to Excel and it will be available with Excel's Insert Function routine at the Formula Bar.

Then in Cell E5 on your attachment, put this formula

=TotalTaxes(D5)

Then you can place a list of incomes in D5 and down, and copy E5 down next to the list and see the taxes for each income.

[Roofasa]

I really appreciate the help, not gonna lie, even after reading the chapter on arrays in my textbook just now, i'm still pretty confused.
For my function, I tried to follow along your guidelines and wrote this:

 
   Dim arrBreakPoint() As Integer   Dim arrTaxPct() As Integer
   Dim i As Integer
   Dim totalTax As Integer
   
   
   For i = LBound(arrBreakPoint) To UBound(arrBreakPoint)
        Do While income > arrBreakPoint(i)
            totalTax = totalTax + (arrBreakPoint(i) - arrBreakPoint(i - 1) * arrTaxPct(i))
        Loop
        
        If income > arrBreakPoint(i + 1) Then
            totalTax = income - (arrBreakPoint(i + 1) * arrTaxPct(i))
        End If
        ReDim Preserve arrBreakPoint(i + 1)
        ReDim Preserve arrTaxPct(i + 1)
        
        Next

But as you can tell, it's not working. Where am I going so incredibly wrong?

[SamT]

That is not all the code.

Socratic Questions:
Is it supposed to be a Sub Procedure or a Function Procedure?
Where do the inputs come from?
Where do the results go?
How are the arrays filled with Values?

Home work question:
What is the difference between an Integer Type Variable and a Double Type Variable.

Grade = 40 (= Boo! Hissss)

For i = LBound(arrBreakPoint) To UBound(arrBreakPoint) 
    Do While income > arrBreakPoint(i) 
        totalTax = totalTax + (arrBreakPoint(i) - arrBreakPoint(i - 1) * arrTaxPct(i)) 
    Loop 
'
'
'
Next

When does the Do While exit?



Grade = 89 (=B++, <>A)

    Do While income > arrBreakPoint(i) 
        totalTax = totalTax + (arrBreakPoint(i) - arrBreakPoint(i - 1) * arrTaxPct(i)) 
    Loop

What happens at each point in the calculation when i = LBound(arrBreakPoint)

Can a If i = ??? Then. . . Else. . .End If fix that?

[Roofasa]

ok, so I'm going to work through this step by step.

1. It's a sub and a function - I have a sub that calls the tax function. Don't ask me why this is necessary, the book just wants it
2. I have income as the input coming in from the sub - input box to request income
3. added a message box at the end of the function to display the results - once I have a result :P
4. Ok this is where it gets harder. Bear with me <3
I tried to fill the arrays. I am on the right track or am I just leading myself down the garden path?

    Dim arrBreakPoint() As Integer
    Dim arrTaxPct() As Double
    Dim i As Integer
    Dim totalTax As Double
    Dim size As Long

   With Range("a3")        
           size = WorksheetFunction.CountA(Range(.Offset(1, 0)), Range(.Offset(.End(xlDown), 0)))
    End With
    
    ReDim arrBreakPoint(size)
    ReDim arrTaxPct(size)
    
    With Range("a3")
        For i = 1 To size
            arrBreakPoint(i) = Range(.Offset(i, 0)).Value
            arrTaxPct(i) = Range(.Offset(i, 1)).Value
        Next
    End With

Oh, an HW: looked up integer and double - looks like the difference is precision and the decimal place. Integer being whole numbers. So I changed my totalTax to double and the taxPct to double?

P.s I appreciate you basically teaching me arrays, I really do.

[SamT]

It's a sub and a function

Can't be both. Just like a Chicken can't be a Hen and a Rooster.

Sub Test()
MsgBox = TotalTaxes(Income)
End Sub

Function TotalTaxes(Income as ???) As ???
Code here
End Function

I tried to fill the arrays. I am on the right track or am I just leading myself down the garden path?

Go back to my post #4, it's got all the info you need. Minor detail changes is all it needs to be exactly what you want.

In all this I am assuming that the break point and tax rates are on the worksheet. another way to declare an array (mixed) is

Sub t()
COnst one as Integer = 1
Const Two as Long = 234567
Const Three as Double = 3.4

Dim myVariable as variant
Dim i as long

MyvaRIable = Array("Abc", "Bcd", "Cde", 123, 234, 345, OnE, Two, three)

For i = LBoUnd(myVariable) to UBound(myVariabe)
MsgBox myVariable(i)
Next i
End Sub

Contains deliberate Case errors. Paste into VBA as is and watch the magic.

VBA Editor tips:
Open the menu Tools >> Options >> Editor Tab. Check every box on that Tab except Drag and Drop, unless you like D&D. I set the Tab Width to 2. The Editor Format Tab will let you play with the colors and Fonts used in the VBA Editor. On the General Tab, I check all except Notify, including Break On all Errors. On the Docking Tab, I check all except Object Browser because I want to see all columns in it.

[Roofasa]

I'm pretty stuck and confused as hell
Every time I try to do something, i get subscript out of range.
So I think there is a problem here:

arrBreakPoint = Range("a4:a7").Value
arrTaxPct = Range("b4:b7").Value

For i = LBound(arrBreakPoint) To UBound(arrBreakPoint)

Am I setting up my for loop wrong?

[SamT]

That part looks good. Now show us the rest of the code, from "Sub. . ." to "End Sub," or from "Function..." to "End Function," inclusive.

[Roofasa]

This is what I am at right now. I know its not all the way there inside the for loop, but I think I'm heading in the right direction.

Public Sub calctax()    Dim income As Double
    
    income = InputBox("What was your income this year in $", "Tax Calculator")
    Call totalTax(income)


End Sub

Public Function totalTax(income As Double) As Double

    Dim arrBreakPoint() As Variant
    Dim arrTaxPct() As Variant
    Dim i As Integer
    Dim size As Long




      arrBreakPoint = Range("a4:a7").Value
      arrTaxPct = Range("b4:b7").Value




        For i = LBound(arrBreakPoint) To UBound(arrBreakPoint)
          
          
                   
          Do While income > arrBreakPoint(i)
            If income <= arrBreakPoint(1) Then
              totalTax = 0
            Else
              totalTax = totalTax + ((arrBreakPoint(i) - arrBreakPoint(i - 1)) * arrTaxPct(i))
            End If
            
          If income > arrBreakPoint(i + 1) Then
            totalTax = income - (arrBreakPoint(i + 1) * arrTaxPct(i))
          End If
          loop
        Next i


      End Function

[SamT]

This is the executing part of your function rewritten using the actual numbers extant at the start of execution. Assuming an income of 30K.

For i = 0 to 3
'i = 0
Do while 30K > 15K

If 30K < 45K then
TotalTax = 0
Else
TotalTax = TotalTax + (15K - arrBreakPoint(0 - 1)) * 0.00

That's all, folks

This is the executing part assuming an income of 50K or greater

For i = 0 to 3

Do while 50K > 15K
If 50K > 45> then
TotalTax = 50K - 45K * 0.0
End if 
Loop 'While 50K > 15K

That's all, folks

Now, assuming an income of 10K

For i = 0 to 3
Next i
'Function returns it's initial value of 0

'Whoa, that was fast. And it returned the correct result!

STOP! Copy this code to Notebook and think about it while reading my post #6 before reading my next post.

[Roofasa]

Ok, working on it.

Can you please tell me why

Do While income > arrBreakPoint(i)

Is giving me a subscript out of range error?

[SamT]

That post was to show you how to look at the logic of your code. That "Subscript Out of Range" error was because you were trying to use the subcript LBound() - 1 There ain't no such animal as a subscript smaller then LBound

Your code needs to know what sheet the Ranges are on.

There is no need for an inner loop, the For... Next loop is sufficient.

This line from your post #5 is just about all you need except when arrBreakPoint(i - 1) causes an error. That happens when i = LBound(arrBreakPoint) or 0

totalTax = totalTax + (arrBreakPoint(i) - arrBreakPoint(i - 1) * arrTaxPct(i))

The very first iteration thru the For... next loop needs special handling

If i = Lbound(array) Then TotalTax = Array1(i) * array2(i)

Note I didn't use TotalTax = TotalTax + etc? That is because the first time thru, there is no previous TotalTax to add to. We don't want to use any Magic Numbers because your professor will change all the values in the tax table and add a percentage to the bottom income level.

Now we have

 
For i = Etc
If i = Lbound then
Blah, Blah,
Else
Blah, Blah
End if
Next i

I suggest you copy all my posts to Notepad or Word and read them over before you try coding again.

Since we don't want to charge tax on 15K when the income is only 10K

TotalTax =  Min(Income, arrBreakPoint(LBound)) * arrTaxPct(LBound))

I'll let you figure out how to handle (i) <> LBound

Since we don't want to even enter into any tax calculations unless the Income is above a certain amount

If Income > breakpoint array(i-1) Then calculate for Arrays(i)

[Roofasa]

OK, this is starting to make sense. Another question I have, say income is greater than 65k, my final break point, (at which point everything above 65k is charged at a fixed %). Should I just add another scenario to my if statement?

for i = array1 to array 2
     if (i)= 1 then
           totaltax=array1*array2
     Elseif (i)> 1 and income < array1(i)
           totaltax=totaltax+((arrBreakPoint(i)-arrBreakPoint(i-1))*arrTaxPct(i))
     Else 
           totalTax=totalTax+((income-arrBreakPoint(i))*arrTaxPct(i))
     End If
Next i

Also your last two lines of code make sense, and I understand why I need them. But I'm a little confused as to where I would put them? Should I determine if income < 15k before starting my for loop?

[SamT]

Should I determine if income < 15k before starting my for loop?

No. What if there is a tax on income below the bottom breakpoint?

Income	Breakpoint	Tax pct	Common Array index
From 0 to	$15,000	0%	0
From 15K to	$45,000	15%	1
From 45K to	$65,000	20%	2
From 65K to	$85,000	25%	3
above 85K		???	N/A

You should check if Income is negative, otherwise the function will return a negative tax

Another question I have, say income is greater than 65k, my final break point, (at which point everything above 65k is charged at a fixed %). Should I just add another scenario to my if statement?

Technically, the Final Breakpoint is 85K.

That is a judgement call. Does the professor like you to follow the rules EXACTLY? Does he/she like to see you use some initiative? My answer might be inappropriate because I am an arrogant genius smart-ass showoff with no social sense at all.



I think you have a good beginner's levl of understanding of Arrays now, so I will attempt to explain the required algorithmic logic for this assignment. BTW, tell me before this assignment is due so I can cut to the chase, so to speak.

For Programming purposes, stop thinking of Taxes. Mentally refer to the Tax Pct column/Array as the Multiplier Array. Reason: Prgrmmers can't look at any regular value type like a member of society does. We must only see them as items to be manipulated. Contrary-wise, we must look at the values the same way others do while we run What-If scenarios thru our pointy little heads. Your professor does not see this as a Tax problem, but only as a numbers problem. So our What-Ifs must also see it as a numbers problem.

Refer to the expanded "Tax" table above.

Previous Breakpoint,AKA arrBreakpoint(i-1), herein referred to as FromPoint

Code algorithm:
First step: Run the Code only on positive Income

If Income <= 0 Then Exit Function

Then: For each income bracket, Multiply the TaxPct by only the part of Income that falls in that Bracket.
Special case of First array index. (if existed, FromPoint.Value = 0):

If i = LBound(Array) then
Min(Income, arrBreakPoint(i)) * TaxPct
End If

Other Indexes:

Min(Income - FromPoint, BreakPoint -FromPoint) * TaxPct

iterate thru Arrays, LOOP

Special case of Income > i = UBound(Array) AKA > 85K. If you decide to "Tax income above 85K:

If Income > arrBreakPoint(i) then
(Income - arrBreakPoint(i)) * arTaxPct(i)

End Function here

About "Min()": Min is a function that returns the minimum of values inside the brackets. Example:
A = 5
B = 3

X = Min(A, B, A/B, A-B, A+B, B/A, B-A)

X = -2

I have tried hard to NOT cheat for you so that if needed, you can point your professor at this thread.

[Roofasa]

Hi Sam,

Thanks for that. I fly out to Korea in two hours for work, and plan on working on this on the flight. My homework assignment is due several hours after I land. Perhaps sometime this evening you could write executionable code so I can cross check what I've got when I land?

I really appreciate you walking me through arrays. I know I could probably have solved this you just if statements with enough time, but I figured this was a good opportunity to learn arrays, and seeing as I had a couple of days before it was due, why not! It has been fun... and frustrating pushing myself. I've only been practicing VBA for about 6 days, so the 20 or so questions we had in these assignments have been a huge learning curve.

I've been considering getting in to programming, but wasn't sure if I would really like it. This past week helped me fall in love with the problem solving that has come with coding, so I think I am going to start working on a new language once I finish this class this week. If you have any suggestions on a good language to start with, I would love to hear it!

Once again, I can't thank you enough! Your mysterious and sometime riddling answers kept my cogs spinning for two days

[SamT]

All tested and working in Excel XP. Had to jump far ahead of my lesson plan.

[Nickcarter]

You can use cis tax calculator it will show probable calculations make sure do not seek to replace professional advice. All calculations are aimed to just give a fair idea only.