Verschatelte Wenn Dann Formel oder Matrix?

[Gringo13]

Hallo zusammen,

ich habe folgendes Problem. Wie unten dargestellt habe ich einen "Wasserfall," d.h. oben werden z.B. Erlöse reingekippt und entsprechend der Prozentsätze auf der Linkenseite an die z.B. 4 Vertragspartner ausgeschüttet. Soweit wäre das ja kein Problem, was das Ganze erschwert ist der Punkt, dass die 4 Vertragspartner nur Erlöse erhalten bis ihre Investition zurückgeführt wurde, sprich das Ganze ist gedeckelt. Da die Prozentsätze jedoch nicht den Prozentsätzen des Investments entsprechen sind die Rückführungen nicht gleichzeitig beendet, d.h. mit der Formel unten in Rot komme ich nicht weiter, da ein Restbetrag übrig bleiben würde. Was ist hier die beste Lösung? Sobald ein Vertragspartner rausfällt, weil er schon alles erhalten hat, sollte der Rest anteilsmäßig der Prozentsätze (die dann wieder angepasst werden müssten auf die verbleibenden 100%) verteilt werden.

Ich hoffe, ich habe mich verständlich ausgedrückt, sonst bitte einfach fragen.

Vielen Dank!

 

[Sneazel]

Was genau möchtest du denn?

Du hast jetzt 2 Millionen an Investitionen
Wo genau ist der Gewinn der aufgeteilt werden soll?

Wie genau soll der Gewinn denn aufgeteilt werden?
Hab das so verstanden, dass wenn der Gewinn = GesamtInvestition ist, müsste ja jeder seine Investition bekommen
Wenn der Gewinn < GesamtInvestition ist, soll jeder seinen prozentualen Anteil bekommen?
Was ist denn wenn der Gewinn größer ist? Soll alles überhalb der GesamtInvestition "wegfallen"?
Wenn ja, dann kannst du ja einfach
WENN Gewinn > GesamtInvestition DANN ZuVerteitelenderGewinn = Gewinn - GesamtInvestition
und dann ganz normal aufteilen

 

[Gringo13]

Also im Prinzip Wenn Gewinn <= Investition soll alles per Prozentsatz verteilt werden. Alles was > Investition ist fließt woanders hin (das ist aber nachher einfach...) Bei Gewinn= Investition würde meine Formel ja noch aufgehen. Bei dem Beispiel unten siehst Du evtl. das oben 2Mio reingekippt werden aber nur 1,8Mio gebraucht werden, da bereits die beiden unteren die Investition zurückbekommen haben und somit keinen Anspruch mehr haben. Wie bekomme ich jetzt Excel dazu ohne eine Zirkelreferenz zu prüfen ob noch was übrig ist und wie die dann verteilt wird (die beiden unten haben ja zusammen 30% und die 200k die übrig sind sind jetzt 100% die Prozentsätze von den zwei oberen müssten sich jetzt quasi auch neu errechnen). Hoffe das ergibt Sinn?

 

[Sneazel]

der % des Gewinnanteils ist unterschiedlich vom % der Investments?

 

[Gringo13]

Ja, leider. Ich ich so nicht verhandelt Sonst würde die "einfache" Formel ohne Probleme funktionieren, da alle gleichzeitig ausbezahlt würden...

 

[Sneazel]

Ich denke da wird man nur mit WENN nicht sehr weit kommen

dafür müsstest du das Ganze n mal durchlaufen
um zu überprüfen ob es nie Probleme gibt


noch ne Frage
angenommen du verteilst anstatt 2mio 1,9 mio
C und D bekommen ja jeweils wieder ihr eigenes Investment
und wenn man A und B nur normal verteilt ohne Überschuss bleiben 170.000 über
Wie werden die denn dann verteilt?

 

[Gringo13]

Ich denke da wird man nur mit WENN nicht sehr weit kommen

dafür müsstest du das Ganze n mal durchlaufen
um zu überprüfen ob es nie Probleme gibt


noch ne Frage
angenommen du verteilst anstatt 2mio 1,9 mio
C und D bekommen ja jeweils wieder ihr eigenes Investment
und wenn man A und B nur normal verteilt ohne Überschuss bleiben 170.000 über
Wie werden die denn dann verteilt?

Wenn ich mich gerade nicht total verrechnet habe müssten die "neuen" Prozentsätze so aussehen:


64,29% 109,29
35,71% 60,71

Die 170k entsprechen ja 70% (45+25) 100% wären dann 243k davon die 45 und 25% ergeben 109 und 61.

Hast Du eine alternative Idee wie ich an das ganze ran gehen könnte? Evtl. mit einer Hilfsspalte oder Matrix oder gibt es da was ganz anderes, was ich im Moment nicht auf dem Radar habe?

 

[Sneazel]

Ich denke, dass das kein einfaches Problem ist in Excel
du müsstest halt viele Hilfsspalten machen
aber dann musst du die Anzahl der Hilfsspalten an die Anzahl der Personen anpassen

 

[Jirko]

Das ist echt nicht so leicht. Für den vorliegenden Fall habe ich es - denke ich - gelöst. Aber das ist schon sehr verschachtelt (oder mir fiel auf den Abend keine bessere Lösung ein).

Was ich als erstes festgestellt habe ist, dass die Investoren in der Reihenfolge 4, 3, 1, 2 (von oben nach unten durchgezählt) ihr Geld am schnellsten zurückerhalten. Das habe ich mir zunutze gemacht und die Wenn-Abfragen so formuliert, dass immer erst geprüft wird, ob die Investitionen mit einer schnelleren Rückzahlung als die gerade geprüfte schon ihr Limit erreicht haben. Ist das der Fall, wird ein neuer Prozentwert für die Verteilung des Restbetrages (Einzahlung - alle Beträge der schnelleren Investitionen) ermittelt.

Konkret berechne ich also als erstes mit einer einfachen Wenn-Dann-Prüfung, was Investor 4 zurückerhält. Dann prüfe ich für Investor 3, ob Investor 4 schon die 100.000 erreicht hat. Wenn nicht, erfolgt die Berechnung mit den gegebenen Prozentwerten. Wenn doch, mit neuem Prozentwert. Wenn auch Investition 3 das Limit erreicht, wird der Limitbetrag genutzt.

Analog geht das für den Rest, nur das eben entsprechend mehr geprüft wird. Im Anhang meine Excel-Datei dazu. Bei meinen Testwerten für die Einzahlung scheint es zu klappen - nie wird der ursprüngliche Investitionsbetrag überschritten, stets werden die kompletten Einzahlungen verteilt, maximal jedoch in Höhe der Summe der Investitionen. 100 prozentig sicher bin ich mir aber nicht - es ist spät und die Lösung umständlich und damit nicht so leicht zu durchblicken.

 

[Sneazel]

Hey Jirko,

Bei deinem Beispiel klappt das nicht mehr, wenn Nummer 4 zB 4% gezahlt bekommt

 

[Gringo13]

Danke für Euer Feedback. Wollte mir jetzt gleich mal die Excel Datei ansehen.

@Sneazel: Meinst Du die Formel funktioniert nicht wenn weniger ausgeschüttet wird oder nur nicht, wenn die Prozentsätze verändert werden sollten? Die Prozentsätze sind fix und werde nicht mehr verändert, wäre zwar schön wenn es eine Lösung gebe die dies auch berücksichtigen würde, dann wird das ganze aber wahrscheinlich noch komplexer...

Ergänzung (22. Juli 2013)

Habe die 2. Formel von unten ergänzt, da bei mir u.U. ein Wert rauskam der höher war als das Investment.

Das Problem war hier das bei C9<G9 immer B8*F2 berechnet wurde, es aber einen kleine Bereich gibt bei dem dann B8*F2>Investment...

Jetzt muss ich mir die oberen beiden noch einmal ansehen, da ich z.Z. unter dem bleibe was insgesamt oben reingekippt wird.




=WENN(C9=G9;WENN(B8*1/(1-B9)*(F2-G9)<G8;B8*1/(1-B9)*(F2-G9);G8);WENN(B8*F2<G8;B8*F2;G8))