help on circular reference and MINIMUM value to be found

[l]

Hi all

i have this proble,
the formula has to be circular, i cant think how to avoid.

Is a financial distribution model called catch-up.
Imagine 2 players.
The first money goes ONLY to player 1.
After he reached a threshold called "preferred return"
all the next incoming money is distributed 80% to Player 2 and 20% to
Player 1, UNTIL player 2 (TWO!!!) gets a TOTAL CUMULATIVE
distribution that is 20% of the total money distributed to the 2
players.

My problem is that i can't figure out the MINIMUM value that satisfies
this condition; because i built the model, but all the value above the
threshold do satisfy the requirement of the split 80/20

I figured out in this way:
When the new money is low, so that i am sure i didn't hit the
threshold, then you can split the new money 80% and 20%.
Otherwise: circular reference, because the money of player 2 is also
function of the money of player 1, which is function of player 2's
money.

I did this with a MIN function, (or a MAX, depending on the sign) For
example for player 2 could be:

MIN(cashflow*80%, ((oldmoneyPlayer1+newmoneyPlayer1)/(80%))*20%)

thanks in advance
hope it's not too confused



I am looking for the value of new money such that these two banal
formulas are equal. I imagine the Solver should be able to do it, but
i couldnt figure out.

 

[Zone]

It is rather confusing, but an interesting problem. I'm not sure how you're
going about this, but it seems it would be easier to figure using the total
distribution compared to Player 2's distribution and that should avoid the
circular reference. James

 

[l]

It is rather confusing, but an interesting problem. I'm not sure how you're
going about this, but it seems it would be easier to figure using the total
distribution compared to Player 2's distribution and that should avoid the
circular reference. James

Thanks.
I dont think you can get rid of the circularity, it's there "by
definition"...

If the "catch-up" were at full speed, I wouldnt have any problem.

It would be the value: ("preferred return"/(80))*20 = "preferred
return"/4

BUT the fact that the catch-up is at 80% speed (that means that also
Player 1 gets part of the money incoming after the Preferred return)
makes it circular.

There are a lot of value that satisfy the 80/20 split requirement. I
don't know how to find the minimum value.

Let's take as example an INITIAL cash flow of 113,333
Out of this, Player 1 gets his preferred return = 10,000
that leaves a residual cash flow of 3,333
this could be split 667 and 2,666 among the two players. Now the
requirement 80/20 is satisfied. This is the minimum amount required to
satisfy it.

BUT if I increase the initial cash flow (let's bring it to 200,000),
in my model also the distribution to the two players changes, and the
80/20 split is satisfied. While I am looking for the minimum amount
that satisfies the requirement, not at whatever split.
thanks

 

[Zone]

I think I understand it better now. One thing I don't quite get is Player
1's "preferred return". I presume this is a percentage, such as 10% of the
total initial cash flow. Is that right?

 

[l]

I think I understand it better now. One thing I don't quite get is Player
1's "preferred return". I presume this is a percentage, such as 10% of the
total initial cash flow. Is that right?

yes perfect.

I am getting crazy.
The formula are getting bigger, and i am very confused. As i change
some variables (preferred return, speed of the catchup) the model stop
working, as the 80/20 split is respected by many values....
For example now, i have Preferred return= 9% and Catch-up speed: 90%
Initial Cash Flow:435
The results are: Player 2 Catch-up: 2.25 and Zero for Player 1
This satisfies the 80/20 split (9/2.25) but Player 1 did not
participate in the split....

 

[l]

I THINK I MADE IT!!!!!

 

[Zone]

I'll give it some thought and get back to you -- maybe not today, though. I
still feel that if you go by Player 1 compared to the total, it should be
much easier than trying to add in Player 2. Maybe I'm simple-minded, but it
seems that if Player 1 is getting his proper percentage at any point, Player
2 is irrelevant; he simply gets what's "left" after Player 1 is paid-out.
Seems like if you try to balance against both players you could go nuts.
I'll be in touch. James

 

[Zone]

Great!! Want to explain how you did it? James

 

[l]

I made the mistake at the beginning to use almost the same formulas
for both players....
and i had the problem that i could not find the MINIMUM value that
satisfy the proportion 80/20

now i sat with paper and pen, and found this minimum cash flow value
as funciont of all the other variable

here it is:

((what_player1_get_as_preferred + what_he_gets_as_catchup) / (80%)) *
(20%/80%)

THIS is the minimum cash flow needed to satisfy the 80/20 split. Let's
call it CF* (cash flow star)

Then i coded Player 2 as:

Minimum ((cash_flow_available * Player2_share_during_catchup), "CF*" *
Player2_share_during_catchup)


NOW i simply said for player 1: get what allows to mantain the
relative proportion DURING THE CATCH-UP: that is, he gets 20% and
Player 2 80%, it means 20/80=25%
so i simply said: player 1 gets 25% of what Player 2 got
because i assured that Player 2 had the minimum

NOW looks so easy, it's a path i didn't think about before

if you want, i can send the model
tell me if i should use this email address
thanks a lot

 

[Dana DeLouis]

Player 1, UNTIL player 2 (TWO!!!) gets a TOTAL CUMULATIVE

distribution that is 20% of the total money distributed to the 2
players.

My problem is that i can't figure out the MINIMUM value that satisfies
this condition;

I don't follow exactly, but if you are trying to calculate Threshold #2,
perhaps the following...
tr1 is the first threshold, and 'x is the amount over this.
Are you trying to solve for x?

(0.8*x)/(tr1 + x) = 0.2

Hence, x (over threshold #1) is

x = tr1 / 3.

Hence, tr2 = tr1 + tr1 / 3 = (4/3)*tr1

Again, I'm not sure of the exact question.

 

[l]

(0.8*x)/(tr1 + x) = 0.2

Hence, x (over threshold #1) is

x = tr1 / 3.

Hence, tr2 = tr1 + tr1 / 3 = (4/3)*tr1

Again, I'm not sure of the exact question.

thanks
there are more variables involved.
The agreement sounds like:
Distribution is made following this order:
1- first 100% to Player1 until he gets back all his invested capita
2- then 100% to player 1 until he gets his preferre return
3- THEN 80% to player 2 and 20% to Player1 until Player 2 has received
20% of all the profit in excess of sub (1)
4- Then 80% to player 1 and 20% to player 2
-----
To notice that the two reported "80%" are two different things:
the first (sub 3) is the speed of the catchup clause. The second is
the split agreed.

Now, in my model i want to make it such a way that i can also change
the speed of the catch-up (sub 3): it may also say "100% to player 2"
or "50% to player 2", without changing the final split (sub 4)

so the threshold 2 is changing with the speed also!!!! the faster the
catchup, the quicker we reach the threshold!

 

[Dana DeLouis]

Would this work?
Since 20% was in reference to Player #2, I'll use p2 for the 20%
'x is the amount over the investment and Player #1 Return.

(Catchup*x) / (P1_Inv + P1_Rate + x) = p2

Solving for x

x = (P2 * ((P1_Inv) + (P1_Rate))) / (Catchup - P2)


Sub Test()
Dim P1_Inv, P1_Rate, Catchup, P2, Tr2, x

P1_Inv = 100
P1_Rate = 26
Catchup = 0.8
P2 = 0.2

x = (P2 * ((P1_Inv) + (P1_Rate))) / (Catchup - P2)
Tr2 = P1_Inv + P1_Rate + x
End Sub


As a check, x is 42.
Hence, Player #2 is getting:

(0.8*42)/(100 + 26 + 42)

0.2

whick checks ok at 20%

Hence Treshold #2 is 168.

 

[Zone]

Icnews, You needn't send it. I'm not sure I'd get it even if I saw it! I
wasn't able to help much, but sometimes just talking out a problem can be of
some help. Glad you figured it out. Cheers, James