• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula gives circular error

Cammandk

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

Attachments

  • Return issue.xlsx
    8.4 KB · Views: 0
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.
 

Attachments

  • Return issue LM.xlsx
    104.5 KB · Views: 0
Back
Top