Urgent - Tiered Pricing
 

Hello,

I urgently need to update an existing VBA macro to run in rom 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 value in F7

Urgent - Tiered Pricing
 

What is the question?

"Jeff" wrote:

Hello,

I urgently need to update an existing VBA macro to run in rom 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 value in F7

Urgent - Tiered Pricing
 

The questions is: How can I write a VBA Macro that will calculate the
quarterly based on the tiered pricing?
The input wwould be the mkt value. the output would be the quarterly fee.

"Sean Timmons" wrote:

What is the question?

"Jeff" wrote:

Hello,

I urgently need to update an existing VBA macro to run in rom 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 value in F7

Urgent - Tiered Pricing
 

Do you need for it to be a macro? Seems like straightforward math there.

Course, I can't say I understand a few things here..

Are the column A numbers always going to be 25M, 25M, 50M, remainder of
balance?

Are the %'s always .8, .6 .4 .3?

Is a quarter always going to be 61 days?

Is the discount always 20%?

Seems a pretty easy calc once we know the missing parts...

"Jeff" wrote:

The questions is: How can I write a VBA Macro that will calculate the
quarterly based on the tiered pricing?
The input wwould be the mkt value. the output would be the quarterly fee.

"Sean Timmons" wrote:

What is the question?

"Jeff" wrote:

Hello,

I urgently need to update an existing VBA macro to run in rom 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 value in F7

Urgent - Tiered Pricing
 

Jeff,

With the percentages in B2:B5, and the step limits in A2:A5, and the total in B1

=0.8*(61/365)*(B2*MIN(B1,A2)+B3*MAX(0,MIN(B1-A2,A3))+B4*MAX(0,MIN(B1-A2-A3,A4))+B5*MAX(0,MIN(B1-A2-A3-A4,A5)))


HTH,
Bernie
MS Excel MVP


"Jeff" wrote in message
...
Hello,

I urgently need to update an existing VBA macro to run in rom 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 value in F7