Income tax

[jakjo]

Hello everyone
I have this formula to calculate the Income tax ... how do I convert it to UDF?

Code: Select all

=IF(A2<=21000;"";ROUND(IF(A2<=30000;(A2-21000)*2.5%;IF(A2<=45000;(A2-30000)*10%+225;IF(A2<=60000;(A2-45000)*15%+225+1500;IF(A2<=200000;(A2-60000)*20%+225+1500+2250;IF(A2<=400000;(A2-200000)*22.5%+225+1500+2250+28000;IF(A2<=600000;(A2-400000)*25%+225+1500+2250+28000+45000;IF(AND(A2>600000;A2<=1200000);(A2-400000)*25%+225+1500+2250+28000+45000+IFERROR(LOOKUP(A2;{600001;700001;800001;900001};{525;2775;5025;8025});0);IF(A2>1200000;((A2-1200000)*27.5%)+300000;0)
)))))));2)/12)

I attached sample file and in yellow column that result that i wish ...many thanks for your help,

[HansV]

It shouldn't be too hard to write out the nested If statements (or Select Case) in VBA. You should at least try to do that yourself.

[jakjo]

Welcome Mr. Hans
Here's my try but need more help Maybe I'm doing something wrong

Code: Select all

     Function IncomeTax(Discount As Double) As Variant
     Select Case Discount
        
        Case Is > 1200000
        IncomeTax = 0.275 * (Discount - 1200000) + 300000
        
        Case Is > 400000
        IncomeTax = 0.25 * (Discount - 400000) + 225 + 1500 + 2250 + 28000 + 45000
        
        Case Is > 200000
        IncomeTax = 0.225 * (Discount - 200000) + 225 + 1500 + 2250 + 28000
        
        Case Is > 60000
        IncomeTax = 0.2 * (Discount - 60000) + 225 + 1500 + 2250
        
        Case Is > 45000
        IncomeTax = 0.15 * (Discount - 45000) + 225 + 1500
        
        Case Is > 30000
        IncomeTax = 0.1 * (Discount - 30000) + 225
        
        Case Is > 21000
        IncomeTax = 0.025 * (Discount - 21000)
        
        Case Else
        IncomeTax = 0
    
    End Select
   Select Case Discount
        
        Case Is > 900000
        IncomeTax = IncomeTax + 8025
        
        Case Is > 800000
         IncomeTax = IncomeTax + 5025
        
        Case Is > 700000
        IncomeTax = IncomeTax + 2775
        
        Case Is > 600000
       IncomeTax = IncomeTax + 525
    
    End Select
     IncomeTax = Application.Round(IncomeTax, 2) / 12
    End Function

Can you please check it out ... Your help is highly appreciated and thank you in advance.

[HansV]

Code: Select all

Function IncomeTax(Discount As Double) As Variant
    Select Case Discount
        Case Is > 1200000
            IncomeTax = 0.275 * (Discount - 1200000) + 300000
        Case Is > 900000
            IncomeTax = 0.25 * (Discount - 400000) + 225 + 1500 + 2250 + 28000 + 45000 + 8025
        Case Is > 800000
            IncomeTax = 0.25 * (Discount - 400000) + 225 + 1500 + 2250 + 28000 + 45000 + 5025
        Case Is > 700000
            IncomeTax = 0.25 * (Discount - 400000) + 225 + 1500 + 2250 + 28000 + 45000 + 2775
        Case Is > 600000
            IncomeTax = 0.25 * (Discount - 400000) + 225 + 1500 + 2250 + 28000 + 45000 + 525
        Case Is > 400000
            IncomeTax = 0.25 * (Discount - 400000) + 225 + 1500 + 2250 + 28000 + 45000
        Case Is > 200000
            IncomeTax = 0.225 * (Discount - 200000) + 225 + 1500 + 2250 + 28000
        Case Is > 60000
            IncomeTax = 0.2 * (Discount - 60000) + 225 + 1500 + 2250
        Case Is > 45000
            IncomeTax = 0.15 * (Discount - 45000) + 225 + 1500
        Case Is > 30000
            IncomeTax = 0.1 * (Discount - 30000) + 225
        Case Is > 21000
            IncomeTax = 0.025 * (Discount - 21000)
        Case Else
            IncomeTax = 0
    End Select
    IncomeTax = Application.Round(IncomeTax, 2) / 12
End Function

[jakjo]

Thanks a lot Hans for helping me in solving this issue
Best Regards