Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
18.04.2024 19:30:20
VBA - Tabelle mit Mannschaftsergebnissen sortieren
18.04.2024 18:04:29
Formel fehler Wert#
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1480to1484
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1480to1484
1480to1484
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Asset Allokation

Asset Allokation
08.03.2016 08:41:47
Roman
Hallo
Ich habe folgendes Problem, welches ich schon versucht habe mit iterativer Berechnung und dem Solver zu lösen, aber trotzdem irgendwie nicht weiterkomme:
Ich bin Vermögensverwalter und habe ein Allokationsmodell (Summenprodukt) entwickelt, welches mir für jeden Kunden jeden Monat für die 4 verschiedenen Anlageklassen Geldmarkt, Renten, Aktien und Alternative Anlagen eine Zielallokation in Abhängigkeit von aktuellen Wirtschaftszyklus und der strategischen Asset Allokation der spezifischen Anlageklasse angibt.
Das Problem besteht nun darin, dass die verschiedenen Kunden für jede Anlageklassen maximale Bandbreiten haben und sämtliche Allokationen, welche diese Bandbreiten übertreffen, proportional auf die anderen Anlageklassen umverteilt werden sollen, was aber wiederum dazu führen kann, dass dadurch möglicherweise bei den anderen Anlageklassen die Bandbreiten verletzt werden. Somit beisst sich die Katze in den Schwanz.
Weiss hier jemand Rat?

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
wahrscheinlich ja,
08.03.2016 19:34:39
Michael
Roman,
sofern Du Dich so ausdrückst, daß ein einigermaßen intelligenter Programmierer, der von Deiner Branche keine Ahnung hat, auch versteht, worum es geht.
Dazu wäre eine Beispieldatei mit ein paar Erläuterungen sicher hilfreich.
Schöne Grüße,
Michael

AW: wahrscheinlich ja,
09.03.2016 09:22:50
Roman
Hallo Michael
Ja, da hast du recht; ich bin leider noch nicht wirklich geübt in Foren zu posten. Ich habe die Datei nun im Anhang beigefügt.
In Zelle H3:H6 siehst du die verschiedenen Assetklassen (GM Geldmarkt, BO Bonds (Renten/Obligationen), AK (Aktien), AL Alternative Anlagen).
Zur Erklärung: In einer konjunkturellen Erholungsphase (E3:E8) sind die Inflation und die Geldmarktzinsen niedrig (l=low)normalerweise am steigen, während die Bewertung, das Geschäftsklima und das Konsumvertrauen am steigen sind (r=rising). Für die anderen 3 konjunkturellen Phasen gelten andere Bedinungen (f=falling, h=high).
In den Zellen H8:H16 siehst du die Abkürzungen für die Kunden mit der Drop-Down liste oben und den entsprechenden Bandbrieten für die einzelnen Assetklassen in K8:S11.
Im Block mit der blauen Ueberschrift (TAA Strategie inkl. Bandbreiten) habe ich versucht das Problem mit einer Iteration zu lösen, was aber leider nicht zu funktionierten scheint, da die Summer nicht überall auf 100% kommt (warum weiss ich nichtt...).
Beste Danke und Gruss, Roman

Anzeige
komplex
09.03.2016 17:22:12
Michael
Hallo Roman,
ich habe mir die Tabelle mal angesehen.
Im Grund weiß ich immer noch nicht, worum es geht - das mal vorausgeschickt.
Gesehen habe ich bis jetzt:
a) in I3 läßt sich statt des WVerweis einfacher Index verwenden: 
=INDEX(K8:S8;;$H$7)

das läßt sich so auch zeilenweise runterkopieren und dürfte bei sehr vielen Daten schneller gehen
b) die Kundentabelle würde ich waagrecht anbringen, evtl. auf einem Extra-Blatt, und die Daten spaltenweise rechts vom Kunden anordnen - also pro Kunde eine Zeile
a+b) ist eher unwichtig, vielleicht ne Stilfrage...
c) ab N37 (und in analogen Zellen) ist im Prinzip ein Zirkelbezug, aber das ist wegen der Iteration wohl beabsichtigt.
d) in der Spalte ab O37 ist ein dicker Hund: 
=WENN(H37-N37>0;AD37AJ37;0)

zwischen AD37 und AJ37 fehlt das Minuszeichen!
e) in Y37 habe ich nur #BEZUG stehen - ist wohl unwichtig, wird nicht weiter verrechnet.
Kannst Du mir mal in Worten oder mit einem kleinen Rechenbeispiel verdeutlichen, worum es Dir geht?
Schöne Grüße,
Michael

Anzeige
AW: komplex
10.03.2016 10:46:09
Roman
Sorry, Michael. Hab hier etwas schlampig gearbeitet. Hab nun das File upgedated...
https://www.herber.de/bbs/user/104242.xlsx
Was ist eigentlich möchte ist, dass die Bandbreiten in jeder Assetklasse eingehalten werden. In H37:L37 und weiter runter findet man die 'optimale' Asset Allokation für jede Anlageklasse auf Basis des Konjunkturzyklus. Das Problem hier ist nur, dass unter Umständen die gewünschten Bandbreiten der einzelnen Anlageklassen bei den verschiedenen Kunden nicht eingehalten werden......dies versuche ich dann in den Zellen N37:V37 zu berücksichtigen, indem bei einer allfälligen Verletzung der Bandbreite bei einer Anlageklasse die überstehende Allokation anteilsmässig (prozentual zu ihrem bestehenden Gewicht) auf die anderen Anlageklassen verteilt werden, solange dort noch Platz ist. Dies führt dann in meinem Beispiel zum Zirkelbezug, welchen ich mit einer iterativen Berechnung zu lösen versuche. Leider gibt dies Summe daraus nicht überall 100%....warum ist mir nicht klar. Möglicherweise kann man dies besser mit dem Solver lösen, aber ich hab davon nicht wirklich eine Ahnung.
Hat dies zum Verständnis geholfen?

Anzeige
Also, mit VBA
10.03.2016 20:16:21
Michael
Hallo Roman,
ich hatte erst Mal damit zu tun, die Problematik zu durchschauen - das kannst Du Dir ja vorstellen, sonst hättest Du nicht auf das Forum zugegriffen.
Dabei habe ich eine vorläufige, aber prinzipiell funktionsfähige Lösung erarbeitet, die wie folgt arbeitet:
1. via Index (die 8 in M29) werden die grün markierten Daten H-K der 8. Datenzeile (also absolut Zeile 44) in die Zeile 29 "geholt"; darunter stehen die Limits (auch via Formel) des Kunden, und in den leeren, gelben Zellen darunter werden die errecheten Zwischenergebnisse dargestellt.
Die 8. Datenzeile war die erste, die einen Fehler angezeigt hatte - in W44
Userbild
2. nach Klicken auf "Rechnen" stehen die folgenden Werte in Zeile 10: hier liegt denn auch der Hase im Pfeffer, weshalb keine 100% rauskommen. In P31 wurden die 80% überschritten.
Userbild
3. das wird nach "Ok" im nächsten Durchlauf korrigiert:
Userbild
4. können die Ergebnisse in die betreffende Zeile via Button kopiert werden - dabei werden nur die vier gelben Ergebnisse berücksichtigt, die Formeln bleiben bestehen.
Userbild
Zum Testen schlage ich vor, daß Du die Werte NICHT übernimmst, weil dadurch Deine vorhandenen Formeln überschrieben werden würden.
Du kannst mal mit unterschiedlichen Kunden bzw. Zeilen (im grün hinterlegten Feld M29) testen, ob die Berechnung insgesamt hinhaut.
So läßt sich natürlich nicht sinnvoll arbeiten, aber mit der entwickelten Logik läßt das Ganze auf Knopfdruck für den kompletten Datenbestand in H-K 37 - 219 (oder wie weit runter auch immer) durchrechnen (wenn es denn programmiert ist): das sollte dann in max. 100 Millisekunden ablaufen.
Die Datei: https://www.herber.de/bbs/user/104257.xlsm
Schöne Grüße,
Michael

Anzeige
AW: Also, mit VBA
14.03.2016 10:30:41
Roman
Super herzlichen Dank, Michael!!! Das scheint zu klappen! Bin zwar ehrlich gesagt etwas überfordert, wenn ich mit Makros und VBA zu tun habe, aber es bleibt mir wohl nichts anderes übrig, als mich in dieses Thema einzuarbeiten.
Hast du aber vielleicht noch eine Idee, warum meine Iterationsberechnung nicht funktioniert d.h. nicht überall der Summenwert 100% resultiert?
Beste Grüsse, Roman

AW: Also, mit VBA
14.03.2016 16:40:40
Michael
Hi Roman,
Du mußt Dich ja nicht unbedingt selbst in VBA einarbeiten - ich würde das schon gerne fertig machen, weil es interessant klingt.
Ehrlich gesagt, bei Formeln mit Iterationen "verbiegt" es mir immer das Gehirn - daß ich keine Formellösung anbieten kann, heißt natürlich nicht, daß es keine gibt...
Der Knackpunkt ist, daß Du in Spalte V den Overflow "vor" der ersten Berechnung hast - die werden von der vorhandenen Formel auf "alle" umgelegt, sofern das Limit nicht überschritten wird.
Eine Umlage auf alle ist aber eben nicht immer möglich, wenn wie in diesem Fall ein weiteres Limit überschritten wird - d.h. man muß in jedem Schritt überprüfen, in welcher Spalte (GM, BO usw.) bereits das Limit erreicht wurde und diese Spalten "sperren", die %e der anderen Spalten addieren und den Overflow nach deren Verhältnis auf die nicht gesperrten Zeilen aufteilen.
[genau deshalb funktioniert die Formel nicht: weil die %e der jeweiligen Spalte immer auf 100% gerechnet werden statt nur auf die summierten %e der nicht gesperrten Spalten]
Die Essenz des vorhandenen Makros führt genau diese Schritte durch. Man kann es ohne weiteres so gestalten, daß es die kompletten Spalten N, P, R und T in einem Rutsch durchrechnet, und zwar
- ohne die eingefügten Hilfszellen, die ich nur zur "Umkreisung" bzw. Veranschaulichung des Problems eingefügt habe
- aufrufbar z.B. über Button, aber etwa auch "automatisiert", sobald ein anderer Kunde gewählt wird - oder Beides...
Wenn Du magst, schick mir halt ne E-Mail, siehe hier:
https://www.herber.de/cgi-bin/profile/call_profile.pl?user=1857094
Schöne Grüße,
Michael

Anzeige
AW: Also, mit VBA
15.03.2016 18:23:33
Roman
Hallo Michael
Vielen Dank für die Antwort. Ich hab mir das ganze nochmals überlegt: das Entscheidende und für die späteren Renditen des Portfolios zu 80-90% verantwortlich ist die Entscheidung Aktien oder Cash (Geldmarkt. Dies kann man überall in der einschlägigen Literatur bzgl. Finanztheorie nachlesen. Dies ist nachvollziehbar, da Aktien die höchste und der Geldmarkt das niedrigste Risiko (Volatilität) haben. Dies gibt bzgl. Risiko folgende Stufen: Aktien grösser als Alternative Anlagen grösser als Obligationen grösser als Geldmarkt. Somit muss ich bei der alles entscheidenden Anlageklasse Aktien beginnen. Wird die Bandbreite überschritten, dann will ich den Overflow selbstverständlich in der nächst-risikoreicheren Anlageklasse nämlich den Alternativen Anlagen alloziert haben. Habe ich hier wieder einen Breach dann kommt der Overflow in die Renten/Obligationen Klasse, womit der Geldmarkt der kein Risiko und praktisch auch keine Rendite hat, als Residualgrösse zum Einsatz!
https://www.herber.de/bbs/user/104362.xlsx

Anzeige
AW: komplex
10.03.2016 11:32:20
Roman
Sorry, Michael. Hab hier etwas schlampig gearbeitet. Hab nun das File upgedated...
https://www.herber.de/bbs/user/104242.xlsx
Was ist eigentlich möchte ist, dass die Bandbreiten in jeder Assetklasse eingehalten werden. In H37:L37 und weiter runter findet man die 'optimale' Asset Allokation für jede Anlageklasse auf Basis des Konjunkturzyklus. Das Problem hier ist nur, dass unter Umständen die gewünschten Bandbreiten der einzelnen Anlageklassen bei den verschiedenen Kunden nicht eingehalten werden......dies versuche ich dann in den Zellen N37:V37 zu berücksichtigen, indem bei einer allfälligen Verletzung der Bandbreite bei einer Anlageklasse die überstehende Allokation anteilsmässig (prozentual zu ihrem bestehenden Gewicht) auf die anderen Anlageklassen verteilt werden, solange dort noch Platz ist. Dies führt dann in meinem Beispiel zum Zirkelbezug, welchen ich mit einer iterativen Berechnung zu lösen versuche. Leider gibt dies Summe daraus nicht überall 100%....warum ist mir nicht klar. Möglicherweise kann man dies besser mit dem Solver lösen, aber ich hab davon nicht wirklich eine Ahnung.
Hat dies zum Verständnis geholfen?
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige