Fee Schedule Question

LuckyGuesser

New Member
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.

(If you need more info let me know)

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

dave3009

Well-known Member
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

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

Any other thoughts/questions about the situation???

njimack

Well-known Member
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
ABCDEFGHI
1Fee
21%200,000,000Jan900,00054,000
32%15,000,000Feb990,00059,400
43%12,500,000Mar1,089,00065,340
54%10,000,000Apr1,197,90071,874
65%7,500,000May1,317,69079,061
76%5,000,000Jun1,449,45986,968
8Jul1,594,40595,664
9Aug1,753,845105,231
10Sep1,929,230115,754
11Oct2,122,153127,329
12Nov2,334,368140,062
13Dec2,567,805154,068
Sheet1

LuckyGuesser

New Member
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

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

LuckyGuesser

New Member
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!

1,190,761
Messages
5,982,768
Members
439,796
Latest member
Chassee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back