Hello,

I need help in updating an existing VBA macro. I need to add the following
calculation in row F7

March Mkt Value 192,073,275.92
25,000,000.00 0.80% 200,000.00
25,000,000.00 0.60% 150,000.00
50,000,000.00 0.40% 200,000.00
92,073,275.92 0.30% 276,219.83

Annual Fee 826,219.83

Quarterly Fee 138,080.57 =826,219.83*61/365
Discount 20% (27,616.11)
110,464.46

On Tue, 26 May 2009 08:50:07 -0700, Jeff
wrote:

Hello,

I need help in updating an existing VBA macro. I need to add the following
calculation in row F7

March Mkt Value 192,073,275.92
25,000,000.00 0.80% 200,000.00
25,000,000.00 0.60% 150,000.00
50,000,000.00 0.40% 200,000.00
92,073,275.92 0.30% 276,219.83

Annual Fee 826,219.83

Quarterly Fee 138,080.57 =826,219.83*61/365
Discount 20% (27,616.11)
110,464.46



There's no need for VBA, but you need to supply the "tiers", then you can set
up an appropriate table.

From what you post, it appears as if the following Fee Table and formula would
give the desired results, but you may have more tiers than what you have
posted.

To set this up, set up your Fee Table in an unused area of your worksheet. I
used H1:J4 but you could use any area, and NAME it FeeTbl.

H1: $ 0
H2: $ 25,000,000
H3: $ 50,000,000
H4: $100,000,000

I1: $ 0
I2: =(H2-H1)*J1+I1
(Fill down to I4)

J1: 0.80%
J2: 0.60%
J3: 0.40%
J4: 0.30%


Then, with your Market Value in A1, use this formula to compute the gross fee:


=VLOOKUP(A1,FeeTbl,2)+(A1-VLOOKUP(A1,FeeTbl,1))*VLOOKUP(A1,FeeTbl,3)

You can then compute the 20% discount and the net fee fairly easily.

If you MUST do it in VBA, you could use this User Defined Function

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like =Fee(MarketValue)
in some cell.


=====================================
Option Explicit
Function Fee(MarketValue As Double) As Double
Dim Base As Double
Dim Diff As Double

Select Case MarketValue
Case Is < 25000000
Base = 0
Diff = MarketValue * 0.008
Case Is < 50000000
Base = 200000
Diff = (MarketValue - 25000000) * 0.006
Case Is < 100000000
Base = 350000
Diff = (MarketValue - 50000000) * 0.004
Case Else
Base = 550000
Diff = (MarketValue - 100000000) * 0.003
End Select

Fee = Base + Diff
End Function
=================================
--ron