EInführung in Excel

RISIKOMANAGEMENT
Löffler
Wintersemester 2000/2001
Wichtige Funktionen und Arbeitsschritte in MS EXCEL
Beispielswerte, auf die Bezug genommen wird
A
1
2
3
4
B
1
1
0
C
1
2
2
Eingabe in Zelle
D
E
F
G
3
1
0
Beschreibung
Wichtige mathematische Funktionen:
=ln(x)
natürlicher Logarithmus von x
=exp(x)
e-Funktion
=x^y
x hoch y
Beispiel mit Werten aus Tabelle
ln(A1)=0
exp(1)=2,718
C1^B3=9
Wichtige statistische Funktionen
=Mittelwert(Bereich)
=Median(Bereich)
=Min(Bereich)
=Max(Bereich)
=Stabw(Bereich)
=Quantil(Bereich; Häufigkeit)
=Korrel(Bereich1;Bereich2)
=Anzahl(Bereich)
=Normvert(a; µ;σ; 1)
=Norminv(a; µ; σ)
=Normvert(a; µ; σ; 0)
=Standnormvert(a)
=Standnorminv(a)
arithm. Mittelwert
Mittelwert(A1:A3)=0,6667
Median
Minimum
Maximum
Max(A1:B3)=2
Standardabweichung
Quantilswert
Korrelationskoeffizient
Korrel(A1:A3;B1:B3)= -0,5
Zahl der nicht-leeren Zellen
Verteilungsfkt. der Normalverteilung mit Mittelwert µ und St.abw. σ an
der Stelle a
inverse Verteilungsfunktion der Normalverteilung
Dichtefunktion der Normalverteilung mit Mittelwert µ und St.abw. σ
Verteilungsfkt. der Standardnormalverteilung
inverse Verteilungsfkt. der Standardnormalverteilung
Lineare Regression, d.h. schätze die Koeffizienten bi in der Regressionsgleichung
Y = b0 + b1X1+b2 X2+...+u
=RGP(Y-Bereich;X-Bereich;1;1)
RGP(A1:A3;B1:C3;1;1)
In der Formel gibt es zwei Optionen, die man mit 1 oder 0 belegen kann. Eine 1 im ersten Optionsfeld
sorgt dafür, daß die Regression eine Konstante enthält (d.h. b0 wird geschätzt). Eine 1 im zweiten Feld
bewirkt, daß neben den Koeffizientenschätzern zusätzlicher statistischer Output ausgegeben wird.
Wichtig sind v.a. die ersten beiden Zeilen des Outputs, er enthält geschätzte Werte für
b2
St.fehler
von b 2
b1
St.fehler
von b1
b0
St.fehler
von b0
t-Werte erhält man, indem man die Koeffzienten durch ihre Standardfehler teilt.
Wichtig: RGP ist keine normale Formel, sondern eine Matrix-Formel. Bei diesen ist zu beachten:
Eingabe erfolgt mit Strg-Umschalten-Return, nicht allein mit Return.
Der Formeloutput erstreckt sich in der Regel auf mehr als eine Zelle. Daher vor Eingabe ausreichend Zellen markieren und dann erst Formel eingeben.
Matrixformeln sind teilweise „empfindlicher“ als andere Formeln. Zum Beispiel darf sich der Bereich in der RGP-Formel nicht auf leere Zellen erstrecken. RGP(A1:A4;B1:C4;1;1) würde im Beispiel einen Fehlerwert liefern.
RISIKOMANAGEMENT
Löffler
Zufallszahlen
=Zufallszahl()
=Standnorminv(Zufallszahl())
=µ+ σ*Standnorminv(Zufallszahl())
Wintersemester 2000/2001
im Intervall 0-1 gleichverteilte Zufallszahl
Liefert standardnormalverteilte Zufallszahlen
Liefert normalverteilte Zufallszahlen mit Mittelwert µ und Stabweichung σ
Makros
Makros sind kleine Programme, die beim Arbeiten mit Excel oft hilfreich sind. In die Liste mit verfügbaren Makros gelangen Sie mit Extras->Makros oder mit ALT-F8. Von dort aus können Sie bestehende
Makros bearbeiten, ein neues erstellen, oder bestehende Makros ablaufen lassen.
Beispiel
Sie schreiben in die Zellen von A1:A10 jeweils =Standnorminv(Zufallszahl()). In die Zelle B1 schreiben
Sie =Mittelwert(A1:A10). Nun wollen Sie den zufälligen Mittelwert 1000 Mal ermitteln und jedesmal in
der Spalte C abspeichern.
Das können Sie über folgendes Makro tun:
Zeile
Programmtext
1
2
3
4
5
6
7
8
Sub Sim()
Application.Screenupdating=False
For i=1 to 1000
Application.Statusbar=i
Calculate
Range(“C“ & i ) =Range(“B1“ )
Next i
End sub
Die Zeilen 2 und 4 sind nicht notwendig. Zeile 2 verhindert, daß der Bildschirm bei jedem Schritt aktualisiert wird, was insbesondere bei größeren Jobs Zeit spart. Zeile 4 schreibt den aktuellen Laufindex in die Excel-Statusleiste(links unten), so daß Sie immer wissen, wie weit Sie sind. Zeile 5
(„Calculate“) entspricht dem Drücken der F9-Taste. Sämtliche Daten im Tabellenblatt, d..h. auch Zufallszahlen, werden neu berechnet. In diesem Makro brauchen Sie Calculate nur, wenn Sie unter Extras->Optionen->Berechnen „Manuell“ eingestellt haben.
Wichtig: Sie müssen den Makro-Text nicht immer selbst schreiben, sondern können sich oft der Funktion Makro-Aufzeichnen bedienen. Wählen Sie über Extras->Optionen->Makros „Aufzeichnen“. Danach wird alles, was Sie in Excel machen, in die Programmiersprache VBA übersetzt. Wenn Sie die
Aufzeichnung beenden wollen, wählen Sie Extras->Optionen->Makros „Aufzeichnung beenden“. Das
aufgezeichnete Makro können Sie im Makro-Editor anschauen und verändern.
Funktionen
Sie können im Makro-Editor auch eigene Funktionen definieren, die Sie dann im Tabellenblatt wie
jede andere Excel-Funktion verwenden können. Eine Möglichkeit, die Black-Scholes-Formel für CallOptionen zu programmieren, wäre folgende:
Function dbs(S, x, t, r, si)
'S=Stock Price, x=Strike price, t= time to maturity(in years), r=interest, si=volatility
dbs = (Log(S / x) + (r + 0.5 * si ^ 2) * t) / (si * t ^ 0.5)
End Function
Function BSCall(S, x, t, r, si)
BSCall=S*Application.NormDist(dbs(S, x, t, r, si), 0, 1, 1) - x*Exp(-r * t)*Application.NormDist(dbs(S, x, t, r, si) - si*Sqr(t), 0, 1, 1)
End Function
Wenn Sie z.B. für eine Option mit Ausübungspreis 100, Laufzeit 6 Monate, Volatilität 20%, Zinssatz
0,05 und aktuellem Underlying-Kurs 105 den Black-Scholes-Wert haben möchten, geben Sie in eine
beliebige Zelle ein =BSCall(105;100;0,5;0,05;0,2)
RISIKOMANAGEMENT
Löffler
Wintersemester 2000/2001
Excel-Solver
Mit dem Solver, den Sie über Extras->Solver aufrufen, können Sie auf numerischem Weg Optimierungsprobleme oder Gleichungen lösen.
Wenn Sie beispielsweise die Gleichung 2x²-3x-2=0 lösen wollen, können Sie das auf folgende Weise
machen: Sie geben die Formel in eine Zelle ein, wobei Sie für X Bezug auf eine andere Zelle nehmen.
Sie schreiben etwa in Zelle A1: =2*B1^2-3*B1-2. Dann rufen Sie den Solver auf, wählen als Zielzelle
A1, als Wert 0, und als veränderbare Zelle B1. Wenn Sie „Lösen“ drücken, müßte Excel Ihnen die
Lösung präsentieren.
Wichtig: Oft ist der Solver nicht aktiviert, so daß Sie ihn unter Extras nicht finden. Dann müssen Sie
zunächst das Solver-Addin mit ExtrasàAdd-Inn-Manager aktivieren.