Excel Tool: Prozentuale Verteilung in Portfolio ermitteln mit Gewichtung und Verteilung von Restsummen

[Baron1985]

Hallo,

 

ich versuche mich gerade mein Portfolio zu optimieren. Dafür hatte ich eine tolle Idee, die ich aber nicht umsetzen kann. Vielleicht könnt ihr mir weiterhelfen.

 

Angenommen ich möchte 10.000 € investieren.

 

Nun möchte in Excel etwas bauen um mögliche Szenarien durchzuspielen.

Irgendwo in Excel möchte ich Parameter einstellen können in welcher form und welcher Gewichtung ich den Betrag investieren möchte, z.B.

 

Gewichtung

ETF 10%

Rohstoffe 40%

Aktien 50%

 

Dann sollen diese Parameter nochmal aufgeteilt werden mit Einzeltitel inkl. aktuellen Kurs (den ich zur Not auch per Hand eintragen kan), z.B.

ETF

ETF 1 2%, xxx€

ETF 2 7%, xxx€

ETF 3 1%, xxx€

 

Rohstoffe

Gold 30%, xxx€

Silber 10%, xxx€

 

Aktien

Siemens 20%, xxx€

VW 10%, xxx€

BASF 5%, xxx€

Bayer 15%, xxx€

 

Nun soll sich auf diesem Blatt oder auf einem weiteren mein o.g. Anlagebetrag von 10.000 € anhand dieser Gewichtungen und Kurse durchrechnen. Soll heißen, ich möchte gerne wissen wieviele Titel ich anhand aktuellen Kurs und meiner Gewichtung kaufen kann. Her muss natürlich dann z.B. bei 1,6 abgerundet werden auf 1. Die Restlichen 0,6 will ich dann in einem zweiten Schritt bestmöglich anhand der o.g. Gewichtung und Auswahl verteilen sodass ich meiner Portfoliogewichtung sehr nah komme und die Restsumme möglichst gering ist. 

 

Ich hoffe ich konnte mein Idee und mein Problem einigermaßen verständlich rüberbringen.

 

Ich freue mich von euch zu hören

[west263]

sowas. Ich hoffe, meine geringen Excel Künste reichen aus.

Die aktuellen Preise und daraus resultierenden Anteile kannst Du Dir selber errechnen.

 

Gewichtung m. ETF B.xlsx 

[ameis]

Ich hab's ein bisschen komplizierter gemacht als west263. Einfach die markierten Felder ausfüllen. 

 

Baron1985.xlsx

[Baron1985]

Danke west263 und ameis!

 

ameis, dein Modell trifft es am ehest. Aber jetzt bräuchte ich noch ein Berechnung, die mir die Restsumme von 33,00 EUR irgendwie am idealisten aufteilt, das die Summe am Ende nahezu 0 ist

[west263]

vor 3 Minuten von ameis:

Ich hab's ein bisschen komplizierter gemacht als west263.

Du hast es nicht komplizierter sondern mundgerecht gemacht.

[Baron1985]

Nicht streiten :-) Oder, aber das ist für mich schon High-End Progammierung: Man gibt den Anlagebetrag, die Gewichtung der Instrumente ein und wählt die Titel inkl. Kurse. Und dann wird die ideale Gewichtung in den einzelnen Titel gezeigt, d.h. die Anzahl um die Restsumme möglichst 0 zu kriegen. Aber das ist wohl für Excel zu viel :-)

[ameis]

vor 4 Minuten von Baron1985:

Danke west263 und ameis!

 

ameis, dein Modell trifft es am ehest. Aber jetzt bräuchte ich noch ein Berechnung, die mir die Restsumme von 33,00 EUR irgendwie am idealisten aufteilt, das die Summe am Ende nahezu 0 ist

Kaufe einfach einen Anteil der vom Wert her knapp unter dem Restwert ist. Bei einer Anlagesumme von EUR 10k macht das prozentual kaum einen Unterschied.

 

PS. Bei meinem Modell sollte der Restbetrag immer nahezu 0 sein. Ist bestimmt auch noch genauer möglich, habe aber momentan keine Zeit (Lust) mich damit auseinander zu setzen ;-)

[Malvolio]

Ich kapere mal diesen Faden. Ich habe in meinem Excel Tool eine Grafik in der ich die Branchen darstelle:

 

 

Ich würde gerne die kleineren Felder (z.B. die Segmente unter 10%) zu einem Reste-Segment zusammenfassen, damit die Grafik übersichtlicher wird. Ich habe lange gesucht, aber im aktuellen Excel keine Funktion gefunden, um das zu erreichen. Man könnte natürlich eine entsprechende andere Datenquelle verwenden. Aber ich meine man konnte früher mal in Excel eine "Mindestgröße" der Segmente angeben. Aber das scheint beim heutigen Excel nicht mehr vorgesehen zu sein, oder finde ich sie nur nicht? Hat jemand eine Idee?

[xfklu]

Ich schätze, dass du dafür einen anderen Diagramm-Typ wählen musst:

 

[Malvolio]

vor 15 Stunden von xfklu:

Ich schätze, dass du dafür einen anderen Diagramm-Typ wählen musst:

 

Danke! Habe ich mir auch schon angesehen. Aber das ist nicht unbedingt das was ich suche. Ich will ja gerade die kleinen Felder durch die Zusammenfassung ausblenden und nicht noch hervorheben. 

[odensee]

vor 43 Minuten von Malvolio:

Danke! Habe ich mir auch schon angesehen. Aber das ist nicht unbedingt das was ich suche. Ich will ja gerade die kleinen Felder durch die Zusammenfassung ausblenden und nicht noch hervorheben. 

Verbindungslinien löschen, ansonsten alles im "Unterdiagramm" auf "unsichtbar" formatieren ("Keine Füllung", "Kein Rahmen" etc.) Dann musst du noch "sonstiges" händisch im Diagramm eintragen.

 

Ich würde mit einer zweiten Tabelle arbeiten, in die die vorhandenen Daten "gespiegelt" werden, sofern die Werte > 10 % sind und den Rest mit "SUMMEWENN" in einem Feld zusammenfassen. Müsste (<= Konjunktiv beachten ) gehen.

[Malvolio]

vor 1 Minute von odensee:

Verbindungslinien löschen, ansonsten alles im "Unterdiagramm" auf "unsichtbar" formatieren ("Keine Füllung", "Kein Rahmen" etc.) Dann musst du noch "sonstiges" händisch im Diagramm eintragen.

 

Ich würde mit einer zweiten Tabelle arbeiten, in die die vorhandenen Daten "gespiegelt" werden, sofern die Werte > 10 % sind und den Rest mit "SUMMEWENN" in einem Feld zusammenfassen. Müsste (<= Konjunktiv beachten ) gehen.

Das mit der zweiten Tabelle scheint mir auch die einzige akzeptable Lösung. Ich hatte nur die Hoffnung, es gäbe noch eine simplere Lösung bei der Diagrammformatierung. Ich habe irgendwie im Hinterkopf, dass das in früheren Excel-Versionen mal möglich war.