Formula gives circular error

[Cammandk]

I have sales revenues which have a commission % deducted to leave an net amount that repays an investment.
The total investment to be repaid is $5000.
Until 50% of this is repaid ie $2500 the commission to be charged should be 5%.
Once $2500 has been repaid any further sales amounts should have 10% deducted until the $5000 is repaid.
Because the net amount is effected by the distribution fee I run into a circular error in trying to use the correct distribution %.

 

[Deepak]

Hi,

Pls do some manual calculation with scope & upload it.

 

[Luke M]

Hi Cammandk.

I think the problem is easier to visualize in terms of a graph, where we can analyze how the output changes as input increases. See attached. In col J:M, I attempt to show current methodology. Fee is manually entered, starting at 5%. At yellow highlight, we hit our problem in the circular logic. Net output is over 2500, so fee should be 10%...but that would then make net output lower than 2500, so fee should be 5% (our contradiction). What to do?

I might propose the right columns, P:T. We start by taking a "management reserve" of 90%. This will be enough to cover the investment payout no matter what. The fee percentage is then based on the amount in mgmt reserve. Then, you subtract fee from initial sales to get actual payout. You can follow the numbers down to see when transition point from 5% to 10% occurs. This would be my best guess as to how to restate the problem to make it solvable. Your investors get repaid, calculation is doable, and having a management reserve is usually a good thing. In reality, you could always make it so that the total mgmt reserve gets paid to investors at end.