LuckyGuesser
New Member
- Joined
- Aug 23, 2007
- Messages
- 12
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)
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)