calculate fees from table

**jcavigli** · 05-29-2008, 11:56 AM

I am trying to automate something I've been doing manually. Let me explain.

1.) I am givien information about fees associated with a mutual fund product. A sample is reproduced in F4:F8.
2.) Next, this information is entered into the table. See A4:D12.
3.) Finally, from this table, I must calculate the fees associated with various account sizes.
4.) I did this manually in cells G15:G16 for 2 different account sizes. You can view my formulas.

Can something like this be automated? If so, can anyone help me get started? I don't know what code structures to use.

**royUK** · 05-29-2008, 11:58 AM

I would think that using formulas would be the way to go.

**royUK** · 05-29-2008, 12:25 PM

Here's how I would approach it

**jcavigli** · 05-29-2008, 01:24 PM

I like your approach where you are trying to calculate weights. I need to make this thing generic. I changed the example. I could get the weight of the first one correct. But the other ones I am still working on.

**royUK** · 05-29-2008, 01:41 PM

Try this in F23

=(IF($F$21>=$B$5,$B$5*$D$5,$F$21*$D$5))

In F24

=IF($F$21-SUM($B$5:$B$6)<=$B$6,($F$21-$B$5)*$D$6,$B$6*$D$6)

And so on

**fecurtis** · 05-29-2008, 04:37 PM

*Double Post...please delete*

**fecurtis** · 05-29-2008, 04:41 PM

Why not make a custom function that does this painlessly? Plus if you ever need to use it in more than one workbook, you can easily put it in any other workbook as an add-in. All you'd need is this:

Function Fee(Amount)
    Const Tier1 = 0.01
    Const Tier2 = 0.0075
    Const Tier3 = 0.00625
    Const Tier4 = 0.005
    
    Select Case Amount
        Case 0 To 15000000:  Fee = Amount * Tier1
        Case 15000001 To 30000000: Fee = (15000000 * Tier1) _
        + ((Amount - 15000000) * Tier2)
        Case 300000001 To 45000000: Fee = (15000000 * Tier1) _
        + (15000000 * Tier2) + ((Amount - 30000000) * Tier3)
        Case Is >= 45000001: Fee = (15000000 * Tier1) _
        + (15000000 * Tier2) + (15000000 * Tier3) _
        + ((Amount - 45000000) * Tier4)
    End Select
    
End Function

Just copy and paste that into the module portion of the VBA editor screen. To use the formula simply type in
=fee(*insert cell with the monetary value, or type in the monetary value yourself*) and it'll give you the applicable fees.

So for example, if cell A1 had 100,000,000 and you typed in cell B1 =fee(A1) you'd get 631,250. You could also type in =fee(100000000) and get the same result.

Hope this simplifies things for you. I also have the file attached demonstrating the formula.