Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 12:09:19
Unique (Application.WorksheetFunction.Unique)
19.04.2024 11:51:37
Nächsten identischen Treffer suchen
19.04.2024 11:45:34
Feld im SAP auslesen
19.04.2024 10:59:29
Datei Auswählen und öffnen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
920to924
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
920to924
920to924
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Effizienzlinie

Effizienzlinie
03.11.2007 13:01:00
michele
Hi LEute,
wollte fragen.ob jemand weiss wie man eine Effizienzlinie im Excel konstruirt.Ich hab die Korr.matrix, die Var-Kov-Matrix, die Erwratungswerte der Aktien und die stetigen monatlichen Renditen,danke

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

Betreff
Datum
Anwender
Anzeige
AW: Effizienzlinie
03.11.2007 13:07:17
michele
Hier sind ein paar Einzelheiten von dem Bsp:
Hi, hier sind die stetigen Renditen der Aktien:
1,60% -2,70% 3,11% 2,60% -0,98%
7,77% 2,29% -14,30% 4,42% 4,59%
4,00% 9,63% -1,84% 3,95% 17,66%
-2,03% 12,62% 4,15% 8,30% 6,51%
2,53% 1,29% -5,03% 3,06% -0,95%
1,49% 13,34% 5,97% 5,47% 26,30%
3,01% 3,74% -6,59% 2,70% 1,39%
-10,15% 5,26% 2,45% -6,10% -14,13%
-1,01% 14,86% 10,81% 3,74% 3,55%
10,69% -8,75% -0,28% 1,23% -2,45%
-2,33% 8,12% 7,24% -1,23% 12,22%
-9,48% 14,69% 0,06% -0,86% 1,28%
Die Varianzen:
0,0034 0,0050 0,0043 0,0013 0,0099
und hier ist die Korrelationsmatrix:
1 -0,5962 -0,4466 0,5226 0,2541
-0,5962 1 0,4355 0,1786 0,4688
-0,4466 0,4355 1 -0,0916 0,1859
0,5226 0,1786 -0,0916 1 0,5712
0,2541 0,4688 0,1859 0,5712 1
die Var-Kov-Matrix
0,0034 -0,5962 -0,4466 0,5226 0,2541
-0,5962 0,0050 0,4355 0,1786 0,4688
-0,4466 0,4355 0,0043 -0,0916 0,1859
0,5226 0,1786 -0,0916 0,0013 0,5712
0,2541 0,4688 0,1859 0,5712 0,0099

Anzeige
AW: Effizienzlinie
03.11.2007 17:07:18
ingUR
Hallo, @michele,
wie groß hier die Schar derer ist, die sich mit den Grundlagen des Potfoliotheorie auskennen, vermag ich nicht abzuschätzen, meine jedoch, dass Dir bezogen auf EXCEL-Fragen bestimmt hier einige helfen können, wenn Du beschreibst, welche Grundlage bzw. welches Prinzip zur Konstruktion der Effizienzlinie mit den von Dir veröffentlichen Daten zu beachten sind.
Gruß,
Uwe

AW: Effizienzlinie
03.11.2007 22:21:00
michele
ich muss die Erwartung-Standardabweichung-Kombinationen für mind. 10 unterschiedlichen Portfolien,von denen eins das Minimum-Varianz-Portfolie ist. Das macht man mit der Funktion Solver beim TOOLS (Extras)
und checkt man das Kästchen und dann in der Dialogbox das Feld „Zielzelle“ jene Zelle eintragen, für die ein Optimum gesucht wird. Bei Zielwert angeben, ob ein Maximum/Minimum etc. angestrebt wird (wird in der Regel Minimum und daher „Min“ sein).Bei „Veränderbare Zellen“ jene Zellen eintragen, die die Variablen darstellen (Hinweis: die Portfolioanteile!)
Unter Optionen folgende Werte eintragen:
Höchstzeit: 1000 eintragen
Iterationen: 1000 eintragen
Genauigkeit: 0,000001 eintragen
Toleranz: 0,000001 eintragen
Konvergenz: 0,000001 eintragen
Wie man aber das eigentlich macht, weiss ich nicht, vielleicht kennt sich jemand aus, wird mir sehr hilfreich sein.
Für die Var-Kov-Matrix ist die oben dargestellte falsch, hier ist die neue:
0,0034 -0,0025 -0,0017 0,0011 0,0015
-0,0025 0,0050 0,0020 0,0004 0,0033
-0,0017 0,0020 0,0043 -0,0002 0,0012
0,0011 0,0004 -0,0002 0,0013 0,0020
0,0015 0,0033 0,0012 0,0020 0,0099
DAnke an allen, die hier posten.
michele

Anzeige
AW: Effizienzlinie
04.11.2007 03:36:00
ingUR
Hallo, @michele,
ich gehe einmal davon aus, das das Berechnen der Portfoliovarianz bewerkstelligt wurde, obwohl ich mir durchaus vorstellen kann, das dies ohne VBA mühsam ist.
Es existieren also auf Deinem Blatt die Werte der Erwartungswerte Ei (Mittelwerte oder gleichwertig) und der Varianzen si)2 aus den Einzelbeobachtungen i=1...n.
Ferner gibt es einen Wertevektor (Bereich) für die Gewichtungsfaktoren wi.
Um nun die Gewichtugnsfaktoren zu erhalten, für die die Portfoliovarianz zum Minimum wird, bei gegebenen Einzelkenngrößen, ist die Zelle der Portfoliovarianz als Zielzelle in den SOLVER zu setzen, während der Bereich der Gewichte der Variablenbereich darstellt. Der Zielwert soll minimiert werden. Soweit dürfte die Einstellung des SOLVER klar sein und nur von Deiner Bereichbelegung abhängig sein.
Nun sind jedoch Bedingungen bei der Lösung einzuhalten:
  • die Summe aller Gewichtsfaktoren (Anteile der Einzelwerte am Gesamtportfolio) darf nicht größer als 1 sein. Dazu ist eine Zelle mit der Summe der Gewichte vorzusehen, die dann in der Nebenbedingung eingefügt wird. Z.B. stehen im Bereich H2:H4 die Gewichte und in H5 die Summe =SUMME(H2:H4), dann ist als Bedingung einzutragen: $H$5=1 (oder 100%).
  • ferner darf jeder Einzelanteil für sich nicht größer als 1 werden. Dieser Umstand wird durch die Bedingung: $H$2:$H$4 <= 1 beschreiben.
  • sofern keine Shortinstrumente berücksichtigt werden können, muß jeder Einzelanteil für sich größer oder gleich Null sein, was zu der dritten Bedingung führt: $H$2:$H$4 >= 0. Andernfalls gilt als Begrenzung für jeden Einzelwert die -1.
    Sofern der Stand Deiner Arbeit noch nicht bis zur Ermittlung der Potfolivarianz angelangt sein sollte, dann wäre es nützlich, diesen Schritt vorab zu erledigen. Daher habe cih mich auch jetz mit einem konkteten Beispiel zurückgehalten. Auf jeden Fall wird es hilfreich sein, wenn Du eine Beispielmappe mit den Daten und bisherigen Ergbnissen zur Verfügung stellen kannst, denn dadurch kann auf Deinen Bearbeitungsstand dirket bezug genommen werden, denn das Ermitteln weitere Portfolien bedingt wiederun die Benutzung des SOLVERs, jedoch nun mit einer anderen Zielwertgröße.
    Gruß,
    Uwe

    • Anzeige
    AW: Effizienzlinie
    04.11.2007 20:28:39
    ingUR
    Hallo, @michele,
    hier eine Grundarbeitsmappe zur Verdeutlichung des Einsatzes des SOLVER, ohne das ich die Richtigkeit des Ergebnisses, die damit zu entwickelnde Effiziensline, beurteilen kann.
    EffLine01.xls
    Ausgangswerte sind die von Dir vorgestellten Beispieldaten (in der Tabelle Bereich C4:G15) aus denen die Erwartungswerte, (Zeile 18), Varianzen (Zeile 19) und die Streueung (Standardabweichung; Zeile 20). Korrelations- (Bereich C24:G28) und Kovarinazmatrix (Bereich C32:G36) hergeleitet werden können.
    Je nach Gewichtung der Anteil A1 bis A5 im Portfolio, wird sich der Erwartungwert der Rendite und die Varianz des Portfolios verändern (Bereich C41:G41 := Wichtung W, die zu den Ergebnissen im Bereich I41:K41 führen).
    EA := Erweartungswerte der Renditen der Einzelwerte
    W := Wichtung der Einzelwerte im Portfolio
    KVM := Kovariazionsmatrix mit den Element Cov(i,j)
    PE = EA x WT = SUMMENPRODUK(C18:G18;C41:G41)
    PV = W x ( KVM x WT ) = {=MMULT(C41:G41;MMULT(KVMtx;MTRANS(C41:G41)))}
    (die geschweiften äußeren Klammern sind hier durch die Tastenkombination [STRB][UMSCH][ENTER] zu erzeugen!)
    sV = PV1/2 = WURZEL(PV)
    Um nun die Zusammensetzung zu ermitteln, bei der die Varianz PV zum Minimum wird, wird der SOLVER eingesetzt. Hier wird die Zeile 44 als besondere "Rechenzeile" benutzt, um weitere Lösungen (Orte auf der Effizienslinie) zu finden.
    Folgende Arbeitschritte sind auszuführen:
    1. Bereich C44:G44 mit 0,2 bzw. 20% füllen
    2. Zelle J44 anwählen und SOLVER aufrufen
    3. Zielwert "Min" auswählen
    4. Zielzelle ist also $J$44, die "veränderbare Zellen" sind $C$44:$G$44"
    5. Nebenbedingungen hinzufügen:
      1. Zellenbezug: $C$44:$G$:44 < Nebenbedinung: 1
      2. Zellenbezug: $C$44:$G$:44 > Nebenbedinung: 1
      3. Zellenbezug: $H$44 = Nebenbedinung: 1

    6. Opftionen nach Anforderungen einstellen
    7. Rechengang mit "Lösen" einleiten und Ergebnis übernehemen
    8. Bereich C44:K44 koüieren und über Einfügen::Inhalt::Werte in die Zelle C47 kopieren

    Der gleich Vorgang kann für das Maximum nun in der gleichen Zeille (Zeile 44) wiederholt werden, nur dass im Punkt 2 als Zielwert "Max" abverlangt wird. Kopier (Inhalt einfügen" wird das Ergebnis z.B. in Zeile 56 (es sollen ja zehn Punkte auf der Linie bestimmt werden.
    Weiter Zwischenpunkte können nun nach gleichem Schma ermittelt werden, allerdings wird nun jeweils die Zusatzbedingung eingegeben, dass ein bestimmter Y-Wert (Rendite des Portfolios) erreicht werden soll. Das geschieht, indem mann vor dem Aufruf des SOLVERS in der Zelle M44 der Rechenzeile den Vorgabewert für PE einschreibt und im SOLVER die zusätzliche Nebenbedingung (Pkt. 5.4.) eingügt, dass Zelle I44 gleich dem Wert in Zelle M44 werden soll. Alles andere läuft gleich.
    Hier nocheinmal die drei SOLVER-Dialoge, wobei der vordere Dialog für jeden Vorgabewert von PE einzusetzen ist:
    Wie eingangs geschreiben, sollte hier nur die Vorgehnsweise beschreiben werden. Ob die verwendeten Ansätze und Zahlen die richtigen sind und ob das Ergenis, das gesichte ist, vermag ich nicht zu prüfen.
    Gruß,
    Uwe

    Anzeige
    AW: Effizienzlinie
    05.11.2007 19:26:00
    michele
    Hallo Uwe,
    danke für die Hilfe, es war sehr hilfreich, nur paar Fragen meinerseits, die Formel für den Erwartungswert des Portfolios = Erwartungswert Rendite (= Mittelwerte) * Gewichte Transponiert. Soll ich hier den Vektor der Erw. Im Excel speichern, weil du die Mittelwerte genommen hast, es ist, glaube ich, egal, weil die gleichen Werte sind, aber es heisst transponiert, daher korrekt wäre der Vektor.
    Dann bei dem Solver mache ich alles richtig (markiere ich die Varianz PV = 0,153%, mache ich die notw. Anforderungen), beim Short-Selling sollen die Nebenbedingungen >=-1 und Ich hab alles ausprobiert, wenn ich ok drücke , dann kopiere ich die Zeile inkl Standardabweichung und dann kommen bei mir die gleichen Werte, ich weiss nicht, wie du zu dem Wert 2,1221% kommst oder die anderen Werte.
    Vielleicht hilfst du mir, es war toll, dass was du da erklärt hast.
    Ach ja,und noch was, sind die Gewichte immer 20%, weil 5 Aktien, oder muss ich die ausrechnen, indem ich die Inversion von der Matrix ausrechne und da paar Schritte weiter

    Anzeige
    AW: Effizienzlinie
    05.11.2007 22:57:00
    ingUR
    Hallo, @michele,
    die Änderung in den Parametern der Nebenbedinungen zur Berücksichtung von Short-Selling-möglichkeiten hast Du richtig umgesetzt. Würde dies Möglichkeiten nur auf einen Teil der einsetzbaren Aktien beschränken, wären die entsprechenden Bedingungen gesondert anzugeben.
    Nach der Berechnung im SOLVER kannst Du, wenn Du die weiteren Auswertungen der Operationen nicht benögtigst, einfach den Dialog mit OK beenden und so die Ergebnisse übernehmen (Keep Solver). Was Du allerdings nun mit Deiner Bemerkung «...und dann kommen bei mir die gleichen Werte ... genau meinst, weiß ich nicht genau, denn aus dem Nachfolgenden kann herausgelesen werden, dass Du nicht eben nuihct auf die gleichen Werte wie ich komme, eben z.B. den Wert 2,1221%.
    Daher hier noch einem drei Bilder, die den ersten Durchlauf zur Bestimmung des Minimums der Portfolio-Varianz darstellen:

    Soweit sollte gleiche Vorgehnsweise mit gleichen Daten zum gleichen Ergebnis führen. Wenn dieses dann so geschafft ist, können weitere Werte der Effizenzlinie ermittelt werden.
    Hier habe ich mich in die Materie noch nicht genug eingelesen, um fachlich die exakten Antworten geben zu können, daher bitte ich meine weiteren Ausführungen kritisch zu bewerten.
    Die Darstellung der Effizienzlinie ist ein Variationsproblem. In meiner Beschreibung habe ich als Aufgabenstellung angenommen, dass zu bestimmten Portfoliorenditen, die Portfoliozusammensetzung (Gewichte wi) gesuht wird, die die kleinste Portfoliovarianz aufweist. Daher werden als veränderbare Zellen im SOLVER die Zellen mit den Einzelgewichten eingetragen.
    Eine andere Variationsmöglichkeit bestände in der Veränderung der Werte der Kovarianzmatrix bei konststant gehaltenen Gewichten, wie die Berechnungsformel für die Portfoliovarianz zeigt:
    PV = W x ( KVM x WT ) =
    SUMi=1..n( (wi)2 ) * vj +
    SUMi=1..n-1; j=i+1..n( 2*wi*wj * ci,j )
    mit
    vi als Varianz der Reihe i
    ci,j als Kovarianzwert der Reihen i und j.
    (Nebenbemerkung: ci,i = vi)
    Mit dieser Fragestellung: «...Soll ich hier den Vektor der Erw. Im Excel speichern, weil du die Mittelwerte genommen hast, es ist, glaube ich, egal, weil die gleichen Werte sind, aber es heisst transponiert, daher korrekt wäre der Vektor.» komme ich nicht klar. Erwartungswerte und Gewichte sind Vektoren, die zum Skalar werden, wenn man das Summenprodukt von ihnen bildet:
    / x \ | y | \ z / T +------------------------ { a; b; c } * { x; y; z } = { a; b; c } | ( a*x + b*y + c*z )


    Doch das kann ja wohl nicht Deine Frage beantworten.
    Gruß,
    Uwe

    Anzeige
    AW: Effizienzlinie
    05.11.2007 21:54:00
    michele
    und noch was wollte ich fragen, wie kann ich die Erwartungswert-Standardabweichungs-Kombinationen für jeweils mind. 10 unterschiedliche Portfolioen mit Solver ausrechnen, von denen eines das globale Minimum-Varianz-Portfolio ist, wenn ich nur einmal Min und einmal MAx. bei no Short-Selling und dann mache ich Min mit der zusätzlichen Bedingung Erw.werte Rendite müssen gleich sein Soll(PE) und einmal Min und Max bei Short-Selling und die zusätzliche Option.
    Beim Short selling die Nebenbedingung muss eine >=-1; No-Short : >=0; Ich bekomme diese Werte
    20,0% 20,0% 20,0% 20,0% 20,0% 1,0000 2,808% 0,153% 3,909%
    0,0% 0,0% 0,0% 100,0% 0,0% 1,0000 2,273% 0,153% 3,909% Min,no short, assume non-negative Werte, linear
    0,0% 0,0% 100,0% 0,0% 0,0% 1,0000 0,478% 0,153% 3,909% Min,no short, assume non-negative Werte, linear,ErwPV=SollPE
    0,0% 0,0% 0,0% 100,0% 0,0% 1,0000 2,273% 0,153% 3,909% Max
    Short_Selling:
    100,0% -100,0% 100,0% -100,0% 100,0% 1,0000 -2,905% 0,153% 3,909%
    20,0% 20,0% 20,0% 20,0% 20,0% 1,0000 2,808% 0,153% 3,909%
    20,0% 20,0% 20,0% 20,0% 20,0% 1,0000 2,808% 0,153% 3,909% 2,808%

    Anzeige
    AW: Effizienzlinie
    05.11.2007 23:22:23
    ingUR
    Hallo, @michele,
    bei der Berücksichtigung der Short-Selling-Möglichkeit ist zwar die Nebenbedingung korrekt eingetragen - jeder Einzelanteil kann zwischen 0% und 100% zum Portfoliowert beitragen, wobei negative Anteile, Short-Positionierungen bedeuten -, jedoch auch in diesem Fall kann ein voll investiertes Portfolio nur den Wert 100% einnehmen.
    Somit ist in den Zellen H41 bzw. H44 die Summe der Anteilsbeträge der Einzelanteile zu bilden. Das habe ich bei meinen zuvor veröffentlichten Beispielbildern auch nicht berücksichtigt gehabt. daher hier die korrikierte Rechenzeile 44:
     CDEFGHIJK
    4434%24%5%-28%-9%100%0,667%0,006%0,759%

    Formeln der Tabelle
    ZelleFormel
    H44{=SUMME(ABS(C44:G44))}
    I44=SUMMENPRODUKT($C44:$G44;EAi)
    J44{=MMULT(C44:G44;MMULT(KVMtx;MTRANS(C44:G44)))}
    K44=WURZEL(J44)
    Enthält Matrixformel:
    Umrandende     { } nicht miteingeben,
    sondern Formel mit STRG+SHIFT+RETURN abschließen!
    Matrix verstehen


    Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
    Gruß,
    Uwe

    Anzeige
    AW: Effizienzlinie
    06.11.2007 05:09:00
    ingUR
    Hallo, @michele,
    hier einmal das Ergebnis von zehn Portfolien aus einem Mix der fünf Aktien mit den benannten Kennwerten:
    Unterhalb der Effizienzkurve liegt also der Bereich, in dem bei 100% Investition der Portfoliokapitals, verteilt auf die fünf Einzelaktien gemäß den Gewichtungsfaktoren, das Verhältnis Rendite/Streuung liegt.
    Die Kurve selbst liefert den gemeinsamen Ort der minimalen Streuung bei einer vorgegeben Rendite.
    Im Bild also: bei einer gleichverteilung des Kapitals auf die fünf Werte, könnte eine Rendite von 2,808% erwartet werden. Dabei wäre die Streuung 3,81% (roter Datenpubnkt).
    Bei gleicher Renditeerwartung könnte die Streuungserwartung jedoch auf 2,118% gesenkt werden (aber auch nicht auf weniger), wenn man folgende Kapitalverteilung vorsehen würde:
    A1:=39%, A2:=50%, A3:=0%, A4:=0%, A5:=-11% (Eingabe in Zellen (C41:G41)
    Dies ist der grüne Datenpubkt auf der Effizienzkurve.
    Gruß,
    Uwe
    P.S.
    Wenn's trotz Bemühen mit der Ermittlung nicht klappen sollte, hier das fertige Tabellenblatt mit Diagramm:
    EffLine02.xls. Doch ich nehme an, dass wenn das Bearbeitungsprinzipp mit dem SOLVER klar geworden ist, dann bedarf es dieser Mappe allenfalls zur Kontrolle der eigenen Arbeit und das Lehrnziel ist erreicht.
    Wenn diese Aufgabe mit verschiedenen Kennwerten durchzuführen ist, dann lohnt sich auf jeden Fall die Ausarbeitung einer VBA-Anwendung, doch das ist ein anderes Thema.
    Anzeige

    Beliebteste Forumthreads (12 Monate)

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

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige