UDF that takes a value, finds it within an array and then multiplies it by the last column

[txwinder]

So I am working on what seemed to be a simple project. I have the following matrix:

$0 $40,000,000 0.200%
$40,000,001 $80,000,000 0.175%
$80,000,001+ 0.125%

If I have a value of $50,000,000 then the function must perform the following calculation:

($40,000,000 * .2%) + (($50,000,000 - $40,000,001) * .175%)

The UDF must be dynamic so that it can work on any number of rows, and the columns will always be (LBound, UBound, %Rate), so no more than 3 columns.

I was able to do this with INDEX and the SUMPRODUCT function but if I add rows then I would have to change the formula and since this is going to someone who is not excel savvy that is not a workable solution.

 

[steve the fish]

Re: Need a UDF that takes a value, finds it within an array and then multiplies it by the last column

What is in the 2nd column of the last row? Empty?

 

[txwinder]

Re: Need a UDF that takes a value, finds it within an array and then multiplies it by the last column

What is in the 2nd column of the last row? Empty?

Steve the Fish,
Thx for the quick response. The 2nd column in the last row will always be blank and represent the UBound of that row. It is an iterative calc, so if the value lands in the last row then the calc needs to look like this:

Ex. #1
Value $50,000,000
($40,000,000 * .2%) + (($50,000,000 - $40,000,001) * .175%)

Ex. #2
Value $90,000,000
($40,000,000 * .2%) + (($80,000,000 - $40,000,001) * .175%) + (($90,000,000 - $80,000,001)*.125%)

 

[txwinder]

Re: Need a UDF that takes a value, finds it within an array and then multiplies it by the last column

I meant to say here are examples as the value hits the different rows. But yes the 2nd column of the last row will always be empty...

 

[Marcelo Branco]

Re: Need a UDF that takes a value, finds it within an array and then multiplies it by the last column

Maybe this can help
http://www.mcgimpsey.com/excel/variablerate.html

M.

 

[txwinder]

Re: Need a UDF that takes a value, finds it within an array and then multiplies it by the last column

Marcelo B.,
The formula works almost but not quite there. The main problem is that the user of this is excel light (I'm realizing I am rusty myself) and he wants to go in and punch in a value and get a result, even if the schedule/matrix size changes, so that's why I need a dynamic function, which takes an array as an argument. Using your suggestion I get $20,000 when I enter a value of $50,000,000. The return should be $97,500. We tried this a number of ways using the native functions and there was always a situation whereby the formula needed to be re-written, when either the schedule/matrix changed or the value changed.

I found the below code in another thread. The only thing I do not like is that the schedule values are hard coded into the function. I'd assume the argument would be an array as a range...

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

 

[Marcelo Branco]

Re: Need a UDF that takes a value, finds it within an array and then multiplies it by the last column

This solution using formulas worked for me - observe the 1 in A2


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C
​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Lower​

[/TD]
[TD]

Higher​

[/TD]
[TD]

%
​

[/TD]
[TD]

Marginal​

[/TD]
[TD][/TD]
[TD]

Value​

[/TD]
[TD]

Result
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

40000000​

[/TD]
[TD]

0,200%
​

[/TD]
[TD]

0,200%​

[/TD]
[TD][/TD]
[TD]

50000000​

[/TD]
[TD]

97500,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

40000001​

[/TD]
[TD]

80000000​

[/TD]
[TD]

0,175%
​

[/TD]
[TD]

-0,025%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

80000001​

[/TD]
[TD][/TD]
[TD]

0,125%​

[/TD]
[TD]

-0,050%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in D2 copied down
=C2-N(C1)

Value of interest in F2

Formula in G2
=SUMPRODUCT(--(F2>=A2:A4),F2+1-A2:A4,D2:D4)

Hope this helps

M.

 

[steve the fish]

Re: Need a UDF that takes a value, finds it within an array and then multiplies it by the last column

If i have my calcs right this works:

Function margtax(salary As Range, salary_table As Range) As Double

arr = salary_table

For i = LBound(arr) To UBound(arr)
    If i = UBound(arr) Then
        myVal = myVal + Application.Max(0, Application.Min(salary - arr(i, 1), salary - arr(i, 1))) * arr(i, 2)
    Else
        myVal = myVal + Application.Max(0, Application.Min(salary - arr(i, 1), arr(i + 1, 1) - arr(i, 1))) * arr(i, 2)
    End If
Next

margtax = myVal

End Function

where the function just requires this table:

[TABLE="width: 134"]
<tbody>[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0.200%[/TD]
[/TR]
[TR]
[TD="align: right"]40000000[/TD]
[TD="align: right"]0.175%[/TD]
[/TR]
[TR]
[TD="align: right"]80000000[/TD]
[TD="align: right"]0.125%[/TD]
[/TR]
</tbody>[/TABLE]

 

[txwinder]

Re: Need a UDF that takes a value, finds it within an array and then multiplies it by the last column

Steve the Fish & Marcelo B.
You two are lifesavers. You do not understand how hard I was banging my head against the wall trying to get this done. I tried something close toe Marcelo's solution but it wasn't dynamic enough and I didn't think of manipulating the schedule/matrix to make my life easier. That's what happens when you haven't coded in 10yrs and try to pull an all nighter!

Much appreciated fellas!

 

[Marcelo Branco]

Re: Need a UDF that takes a value, finds it within an array and then multiplies it by the last column

I'm glad we could help

M.