Revenue Share - Excel Question

[bballfan23]

Hi - I am trying to create a formula to determine revenue share for a company I work with.

The initial revenue share percentage is 5%. On a yearly basis, Company X will receive 5% of Company Y's revenue until a $12 million cumulative number is hit. Once $12 million is hit, the revenue share % would decrease to 3.5%.

If revenues were $100 million, this target of $12 million would be hit after Year 3. Is there a formula that can be written (perhaps a nested IF) to properly switch the calculation from 5% to 3.5% once the $12 million is hit and not after the fact?

Thanks!

 

[sergioMabres]

Hi Ballfan,
You could use a formula like =IF(SUM($B$6:B6)>=$B$4,B6*$B$3,B6*$B$2) in C6

In a Sheet like this

Parameter	Value
Initial revenue share percentage	5.00%
Second revenue share percentage	3.50%
Cumulative revenue	$ 12,000,000
Year	Revenue	Share
Year 1	$ 4,000,000	$ 200,000	<--C6
Year 2	$ 4,000,000	$ 200,000
Year 3	$ 4,000,000	$ 140,000
Year 4	$ 4,000,000	$ 140,000
Year 5	$ 4,000,000	$ 140,000
Year 6	$ 4,000,000	$ 140,000
Year 7	$ 4,000,000	$ 140,000
Year 8	$ 4,000,000	$ 140,000
Year 9	$ 4,000,000	$ 140,000
Year 10	$ 4,000,000	$ 140,000

<tbody>
</tbody>

Cheers
Sergio