• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

User Defined Function for Fee calculation

David Evans

David Evans

Active Member
I've seen some attempts at creating a UDF for this calculation, and I thought I'd offer a challenge to the group to come up with an elegant solution based on the type of calculation illustrated in the attached spreadsheet.
I'm willing to pay for the "best" solution, but I'm not sure how/who decides what will be the best .... any ideas on that and the amount also appreciated!

dai
 

Attachments

  • UDF for Fees Calculation.xlsm
    10.1 KB · Views: 7
Please use this formula
=SUMPRODUCT(C2:C6,D2:D6) no need for UDF and u don't have to pay me ;)
 
Thanks for the suggestion, Nebu.
I want a UDF so that we can refer to the amount and calculate the net fees, without having to lay out the table of bands etc.
 
All right, Nebu - I thought more about your suggestion of SUMPRODUCT and came up with the following -
=SUMPRODUCT(--(Holdings>Threshold),(Holdings-Threshold),C3:C7)

It's on the sheet named array ....

Thanks again Nebu, and Smallman for his persuasive argument opposing UDFs on the grounds of their memory usage - i would still like to use them though to test his theory out ...
 

Attachments

  • UDF for Fees Calculation.xlsm
    13.1 KB · Views: 2
David
the following code will do what you want
Code: 
Private Function Fees(Holdings As Double) As Double

Fees = 0

If Holdings > 10000 Then
  Fees = 100
Else
  Fees = Fees + Holdings * 0.01
End If

If Holdings > 25000 Then
  Fees = Fees + 218.75
Else
  Fees = Fees + Holdings * 0.00875
End If

If Holdings > 50000 Then
  Fees = Fees + 375#
Else
  Fees = Fees + Holdings * 0.0075
End If

If Holdings > 100000 Then
  Fees = Fees + 625#
Else
  Fees = Fees + Holdings * 0.00625
End If

If Holdings > 250000 Then
  Fees = Fees + (Holdings - 185000) * 0.0055
End If

End Function

However I think your logic maybe incorrect
If the first 10,000 is at 1%
and the upper limit on the next section is 25,000, shouldn't the holdings allocated to the 0.875% be 15,000 not 25,000 ? because you have already apllied fees to the first 10,000!
same for the following ranges
 
Thanks Hui - you're definitely on the right track, but I want to have the code reference a table (which I did not make clear, I know ...) My users are not Excel wizards and changing the code would be a "Bridge too Far", I fear ....

Oh and you are correct in the banding error .... there are a couple of ways of illustrating ranges, and I've removed some of the verbiage to try to make it a little clearer ...
 

Attachments

  • UDF for Fees Calculation.xlsm
    13.1 KB · Views: 1
The following code works ok:

Code: 
Function Fees(Holdings As Double) As Double
Dim i As Integer
Dim FeeTable As Variant
FeeTable = [Fee_Table]

Fees = 0

For i = 2 To UBound(FeeTable, 1) - 1
  If Holdings >= FeeTable(i, 1) Then
  If Holdings > FeeTable(i + 1, 1) Then
  Fees = Fees + (FeeTable(i + 1, 1) - FeeTable(i, 1)) * FeeTable(i, 2)
  Else
  Fees = Fees + (Holdings - FeeTable(i, 1)) * FeeTable(i, 2)
  End If
  End If
Next

If Holdings > FeeTable(UBound(FeeTable, 1), 1) Then
  Fees = Fees + (Holdings - FeeTable(UBound(FeeTable, 1), 1)) * FeeTable(UBound(FeeTable, 1), 2)
End If

End Function

It relies on you having a Named Formula
Fee_Table =Array!$A$1:$B$7
where the first 2 rows are headings

See attached file:
 

Attachments

  • UDF for Fees Calculation-Hui.xlsm
    20.3 KB · Views: 3
Hui said:
The following code works ok:

Code: 
Function Fees(Holdings As Double) As Double
Dim i As Integer
Dim FeeTable As Variant
FeeTable = [Fee_Table]

Fees = 0

For i = 2 To UBound(FeeTable, 1) - 1
  If Holdings >= FeeTable(i, 1) Then
  If Holdings > FeeTable(i + 1, 1) Then
  Fees = Fees + (FeeTable(i + 1, 1) - FeeTable(i, 1)) * FeeTable(i, 2)
  Else
  Fees = Fees + (Holdings - FeeTable(i, 1)) * FeeTable(i, 2)
  End If
  End If
Next

If Holdings > FeeTable(UBound(FeeTable, 1), 1) Then
  Fees = Fees + (Holdings - FeeTable(UBound(FeeTable, 1), 1)) * FeeTable(UBound(FeeTable, 1), 2)
End If

End Function

It relies on you having a Named Formula
Fee_Table =Array!$A$1:$B$7
where the first 2 rows are headings

See attached file:
Click to expand...
I like it, Hui! It's an elegant piece of work .... have a tinny o me ;)
 
Hui -

How would you modify your code to accommodate Holdings being a referenced cell and not a named range? That's the only hurdle I'm unable to scale on a Friday afternoon, on the Eve of Hallows - a veritable nuthouse in this land I call home ...

Damn - I should have dressed as Chandoo :awesome::awesome: !!
 
Simply use =Fees(Named Cells Name)

eg:
If you have a cell call Holdings
where Holdings =Sheet1$A$1

=Fees(Holdings)

is the same as saying
=Fees(A1) if your on Sheet1
=Fees(Sheet1!A1) if your on any other worksheet
 
OK - I'm with you - the problem when I put the UDF into my actual worksheet is that I don't have a range named Holdings on it .... duh ... I'll pop it in shortly and see if that solves it, which I think it should ...
 
So ....
I took your original code and tweaked it to reflect that I shall have about 10 different UDF. The first three UDFs i created are below
Code: 
Function FeesNUSGE(Holdings As Double) As Double
Dim i As Integer
Dim FeeTable1 As Variant
FeeTable1 = [NUSGE] 'range name of area holding the table

FeesNUSGE = 0

For i = 2 To UBound(FeeTable1, 1) - 1
  If Holdings >= FeeTable1(i, 1) Then
  If Holdings > FeeTable1(i + 1, 1) Then
  FeesNUSGE = FeesNUSGE + (FeeTable1(i + 1, 1) - FeeTable1(i, 1)) * FeeTable1(i, 2)
  Else
  FeesNUSGE = FeesNUSGE + (Holdings - FeeTable1(i, 1)) * FeeTable1(i, 2)
  End If
  End If
Next

If Holdings > FeeTable1(UBound(FeeTable1, 1), 1) Then
  FeesNUSGE = FeesNUSGE + (Holdings - FeeTable1(UBound(FeeTable1, 1), 1)) * FeeTable1(UBound(FeeTable1, 1), 2)
End If

End Function

Function FeesAWEQ(Holdings As Double) As Double
Dim i As Integer
Dim FeeTable2 As Variant
FeeTable2 = [AWEQ] 'range name of area holding the table

FeesAWEQ = 0

For i = 2 To UBound(FeeTable2, 1) - 1
  If Holdings >= FeeTable2(i, 1) Then
  If Holdings > FeeTable2(i + 1, 1) Then
  FeesAWEQ = FeesAWEQ + (FeeTable2(i + 1, 1) - FeeTable2(i, 1)) * FeeTable2(i, 2)
  Else
  FeesAWEQ = FeesAWEQ + (Holdings - FeeTable2(i, 1)) * FeeTable2(i, 2)
  End If
  End If
Next

If Holdings > FeeTable2(UBound(FeeTable2, 1), 1) Then
  FeesAWEQ = FeesAWEQ + (Holdings - FeeTable2(UBound(FeeTable2, 1), 1)) * FeeTable2(UBound(FeeTable2, 1), 2)
End If

End Function

Function FeesWDIVG(Holdings As Double) As Double
Dim i As Integer
Dim FeeTable3 As Variant
FeeTable3 = [WDIVG] 'range name of area holding the table

FeesWDIVG = 0

For i = 2 To UBound(FeeTable3, 1) - 1
  If Holdings >= FeeTable3(i, 1) Then
  If Holdings > FeeTable3(i + 1, 1) Then
  FeesWDIVG = FeesWDIVG + (FeeTable3(i + 1, 1) - FeeTable3(i, 1)) * FeeTable3(i, 2)
  Else
  FeesWDIVG = FeesWDIVG + (Holdings - FeeTable3(i, 1)) * FeeTable3(i, 2)
  End If
  End If
Next

If Holdings > FeeTable3(UBound(FeeTable3, 1), 1) Then
  FeesWDIVG = FeesWDIVG + (Holdings - FeeTable3(UBound(FeeTable3, 1), 1)) * FeeTable3(UBound(FeeTable3, 1), 2)
End If

End Function


The first two are calculating correctly - the third FeesWDIVG is not

Is there anything really obvious in the code that I'm missing? Usually, you make a code error and it returns an error, not an incorrect answer ...
 
Have you stepped through the code line by line using F8?
Check variables as you go, using either a Watchlist or Debug.print variable
in your code

Without having data and knowing what your trying to achieve it is difficult to assess issues
 
Hui said:
Have you stepped through the code line by line using F8?
Check variables as you go, using either a Watchlist or Debug.print variable
in your code

Without having data and knowing what your trying to achieve it is difficult to assess issues
Click to expand...

Thanks mate - that was going to be my next step - I wasn't sure it would work with a function, but I don't know why I would have thought that ..... Let you know what I find .... appreciate your guidance
 
Easiest way to test a function is with a small sub like
Code: 
Sub Test()
FeesWDIVG(100000)
End Sub
 
Hui said:
Easiest way to test a function is with a small sub like
Code: 
Sub Test()
FeesWDIVG(100000)
End Sub
Click to expand...

Thanks Hui - the flaw is in the logic - They have a strange way of laying out their numeric ranges, and that's where the confusion arises.
I'm going to review it again this evening at home, and have my daughter help me with it. She's an Actuary in Training so is better than I am at this stuff :eek: - but she could still learn a lot from a Perth based Excel ninja (PBEN!)
 
OK- Here is my final answer ..... Thanks to Hui and Megan for their insight. I just kept slogging away at it .... the problem was in the banding/tiering as I had alluded to.
Have attached a final version - this may be useful to some other users who want to calculate commissions, tax rates and the like ....
 

Attachments

  • UDF Check Formula and Code Worksheet - FINAL.xlsm
    19.5 KB · Views: 3
You must log in or register to reply here.
Back
Top