Fee Schedule Question

[LuckyGuesser]

I am attempting to create a formula that will calculate fees based on the following information:

Fee schedule (Columns A,B,C)

6.0% 0 to 5,000,000
5.0% 5,000,000 to 7,500,000
4.0% 7,500,000 to 10,000,000
3.0% 10,000,000 to 12,500,000
2.0% 12,500,000 to 15,000,000
1.0% 15,000,000 to 200,000,000

These #'s are on an annual schedule.

Net Revenue (Column G)

Jan 900,000
Feb 990,000
Mar 1,089,000
Apr 1,197,900
May 1,317,690
Jun 1,449,459
Jul 1,594,405
Aug 1,753,845
Sep 1,929,230
Oct 2,122,153
Nov 2,334,368
Dec 2,567,805

I'm trying to find the Fee (per month, Column H). So I'm trying to find a formula that will do that for me.

History:

At first, I thought that if I found the YearToDate by month and break it out by %'s per the fee schedule ($ figure and then convert to %), that I could then apply that mix of %'s back to the monthly Net Revenue that it would do this for me. However, that is not an accurate and I'm sure there is an easier way.

Please help if you can!!!

(If you need more info let me know)

 

[dave3009]

Hi

Welcome to the board

=LOOKUP(G2, $C$2:$C$8, $A$2:$A$8)

Assuming the value you want to return on is in G2, the range you're looking at comparing it to is C2:C8 and the return range is A2:A8

HTH

Dave

 

[LuckyGuesser]

Unfortunately, that isn't going to work. It's a little more complex than that.

Any other thoughts/questions about the situation???

 

[njimack]

The below solution works providing your fee schedule list is sorted descending by Fee...

Formula in I2:=H2*INDEX($A$2:$A$7,MATCH($H$2,$B$2:$B$7,-1)) (copy down to I13)

Book5
	A	B	C	D	E	F	G	H	I
1									Fee
2	1%	200,000,000					Jan	900,000	54,000
3	2%	15,000,000					Feb	990,000	59,400
4	3%	12,500,000					Mar	1,089,000	65,340
5	4%	10,000,000					Apr	1,197,900	71,874
6	5%	7,500,000					May	1,317,690	79,061
7	6%	5,000,000					Jun	1,449,459	86,968
8							Jul	1,594,405	95,664
9							Aug	1,753,845	105,231
10							Sep	1,929,230	115,754
11							Oct	2,122,153	127,329
12							Nov	2,334,368	140,062
13							Dec	2,567,805	154,068
Sheet1

 

[LuckyGuesser]

Neil,
Thank you for the response. I wish I could post an image like you did, but I'm not sure how. In any case, first maybe insert a column to the left of B titled "Minimum". Then label the new Column C "Maximum". So any Net Rev monthly amounts between $0-$5,000,000 have an associated fee of 6%, $5,000,000-$7,500,000 @ 5%, and so on.

Also as %'s are associated with Year to Date totals, it may be helpful to insert a column to the left of Column I (per your image) titled "YTD" and then have running YTD totals to the right of the monthly net revenue amounts.

Okay, now that the formatting information above is taken care of, here is the main issue.

For example, in the month of May the YTD total ($5,494,590) illustrates that we are rolling from a 6% fee to a 5% fee. I ORIGINALLY thought that if you found the % of the YTD (which comes out to approx. 91% associated with the 6% and approx 9% at 5% that I could take that YTD % mix and apply that to the monthly net revenue which is NOT correct)

What needs to happen is that, based on the YTD, what is identified is everytime a monthly net revenue is rolling from one % to another. Then, the monthly fee must be broken out by the amounts that are in each % range.

For example, for May:

We know that the YTD total is rolling from 6% to 5% (as YTD we are crossing over 5,000,000)

For this particular month, 62% of the monthly net revenue needs to be applied at the 6% fee and the remaining 38% needs to be applied at the 5% fee.

Hopefully this gives a better picture of what I'm trying to accomplish. It's trying to find a reasonable way to make this happen that is the challenge.

If anyone can solve this I'd appreciate it (and would be impressed)!!!

 

[LuckyGuesser]

Is this too difficult a task for you all?
If I need to clarify anything let me know!

 

[LuckyGuesser]

Still looking for anyone who might have a solution. If I knew how to put an image in my post I could illustrate where I'm at in finding a solution.

In any case, any assistance would be appreciated!

 

[njimack]

To post a sample of your file, download the add-in from http://puremis.net/excel/cgi-bin/cl...tp://puremis.net/excel/soft/HtmlMaker2.42.zip

As it is, I'm struggling to understand what you're after.