So I am trying to make a spreadsheet to track what is made on sold items with two investors. It needs to be designed in a way that two people can split the initial investment amount for a purchase, for example one pays $50 the other $150. I need a way so that until the initial investment capital is returned it splits the income inputted on one row next to the purchased item description and splits the income 50/50 until the lower of the two peoples investment amounts are reached. Then it needs to put 100% of the income towards the person with an amount remaining until both investors hit the break even line. Once it hits the break even line and becomes profit I need it to split in predetermined percentage such as 60/40.
A quick example
Investor A pays $100
Investor B pays $200
This purchases lets say 10 items.
The 10 items each sell for $40.
So one item sells for $40 and would give Invest A/B a 50/50 split so $20 each.
Same for item two, by item 3 it would only give investor A $10 and B gets the rest until 4 more sell and then they will have broken even. Once Investor A and B’s total income minus investments is 0 (they both broke even) then all the profit amounts from that point forward need to split 60% for investor A and 40% for investor B.
Splitting the profits after they break even is simply the income total minus investment total then divided by percentages which is is easy. The part I am not entirely sure how to do is the income split 50/50 until one breaks even then 100% all towards the other until they both break even without doing some massive IF(AND(InvestorA<0,InvestorB<0),50/50split,IF(AND(InvestorB<0,InvestorA=0),All to InvestorB, IF(InvestorA<0, InvestorB=0, all to InvestorA and so on type of formula, which would work but is a pain to tweak later and it seems like there must be a better way to do this. If anyone knows a better way for me to do this it would be greatly appreciated, thanks for the help!