Urgent - Tiered Pricing

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

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
 
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.
 
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.
 
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...
 
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,

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,

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Urgent - Tiered Pricing VBA 2

Back
Top