calculating incentive fees

Hi
I want formula to calculate incentive fees as % of revenue by brackets as per the below example:


2015 2016 2017 2018 2019 2020 2021
Revenues 3,000,000 4,500,000 5,500,000 6,000,000 7,500,000 9,000,000 11,000,000
incentive fees ? ? ? ? ? ? ?



Rev From To incentive %
1st Bracket 0 5,000,000 3%
2nd Tranch 5,000,000 10,000,000 4%
3rd Tranch 10,000,000 & Above 5%

Try this:

Please Login or Register  to view this content.

Please adjust the ranges accordingly.

thanks, but dose this account for (if revenue is 6,000,000, the fee is 3% of 5,000,000 plus 4% of 1,000,000)?

Originally Posted by shukla.ankur281190

Try

=IF(AND(B$2>=$B$9,B$2<=$C$9),$D$9,IF(AND(B$2>=$B$10,B$2<=$C$10),$D$10,IF(AND(B$2>=$B$11,B$2<=$C$11),$D$11)))*B2

or

=IFERROR(VLOOKUP(B2,{5000000,0.03;5000001,0.04;10000000,0.05},2,TRUE),3%)*B2

Check the attachment.

it works great ..... Many Thanks

Please find a link to a similar post to get your solution:

http://www.excelforum.com/showthread.php?t=1121657&p=4288393&highlight=#post4288393

Originally Posted by excelfandoo

Please find a link to a similar post to get your solution:

http://www.excelforum.com/showthread.php?t=1121657&p=4288393&highlight=#post4288393

Thanks for your reply