Die 7 wichtigsten Excel Formeln für Investoren

Wie ihr vielleicht bereits mitbekommen habt, ist Microsoft Excel eins meiner Lieblingsprogramme, vor allem weil wir damit recht viel automatisieren können und weil sich Excel gut für das Erstellen jeglicher Art von Bewertungsmodell eignet. In der Vergangenheit hatte ich ja schon das ein oder andere Mal etwas zum Thema Excel (z.B. Excel als Finanzrechner und Sensitivitätsanalyse in Excel) bzw zum Datenimport nach Excel (z.B. Alpha Vantage API) geschrieben. Noch nicht befasst hatte ich mich auf DIY Investor allerdings mit den wesentlichen “Standard” Excel Formeln, obwohl diese zu einem sehr großen Teil zu einem effizienten und automatisierten Bewertungstool beitragen können.

In diesem Artikel möchte ich euch deshalb einmal die 7 in meinen Modellen am häufigsten verwendeten Excel Formeln für Fortgeschrittene vorstellen.

Übersicht über die wichtigsten Excel Formeln

Excel stellt ja mit Sicherheit über 100 verschiedene Funktionen und Formeln zu allen möglichen Themenbereichen bereit (zusätzliche Funktionen möglicher Add-Ins noch gar nicht mitgerechnet). Da den Überblick zu behalten ist also gar nicht so einfach. Glücklicherweise kommen wir aber meist mit einer sehr überschaubaren Anzahl an Excel Formeln aus… jedenfalls wenn es um die Erstellung von Modellen zur Unternehmensbewertung wie DCF-Modellen etc. oder auch von Aktien Screenern geht. Neben ein paar der finanzmathematischen Funktionen sind dies vor allem Formeln für Fortgeschrittene, die uns die Datenverarbeitung erleichtern.

Hier schonmal eine kleine Übersicht über meine Top 7 Excel Formeln für Investoren, die ich euch in diesem Artikel einmal etwas näher bringen möchte:

1. SVERWEIS bzw. VLOOKUP
2. INDEX VERGLEICH bzw. INDEX MATCH
3. INDIREKT bzw. INDIRECT
4. VERKETTEN bzw. CONCATENATE
5. STEIGUNG bzw. SLOPE
6. BEREICH.VERSCHIEBEN + MITTELWERT bzw. OFFSET + AVERAGE
7. WAHL bzw. CHOOSE

Die ganzen finanzmathematischen Funktionen habe ich hier übrigens einmal bewusst ausgeklammert. Zum einen hatte ich in der Vergangenheit schonmal einen Artikel dazu verfasst (jedenfalls zum Spezialthema Annuitäten, Barwerte etc.: Excel als Finanzrechner: Wie wir Portfoliowerte auf Basis von Annuitäten ermitteln), zum anderen halte ich den Produktivitätsgewinn durch die Nutzung von ein paar allgemeinen Excel Formeln für höher und deshalb die Kenntnis der hier vorgestellten Funktionen für relevanter… wann benötigen wir schonmal eine Formel für die automatische Berechnung einer degressiven Abschreibung?

1. SVERWEIS bzw. VLOOKUP

Die SVERWEIS Formel ist vermutlich eine der am häufigsten verwendeten Formeln in Excel (jedenfalls in meinem Umfeld). Mithilfe von SVERWEIS bzw. VLOOKUP (Vertical-LOOKUP) in der englischen Excel Version lassen sich bestimmte Daten aus einer größeren Datentabelle (oder mehreren größeren Datentabellen) in eine andere Tabelle importieren.

Wenn ihr z.B. zwei oder noch mehr Tabellen mit verschiedenen Rohdaten zu einem oder mehreren Unternehmen habt, dann könnt ihr diese mithilfe des SVERWEIS in Excel automatisiert in eine einzige Tabelle konsolidieren.

Der SVERWEIS durchsucht die erste Spalte einer Tabelle zeilenweise, um dann – in der richtige Zeile angekommen – den Wert einer vorgegebenen Spalte zurückzugeben (das spaltenweise Durchsuchen funktioniert mit der Funktion WVERWEIS bzw. HLOOKUP).

SVERWEIS Formel

Hier die Formel für den SVERWEIS:

SVERWEIS bzw. VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]

wobei

• lookup_value: Der Wert (z.B. ein Unternehmensname) für den aus einer anderen Tabelle Daten importiert werden sollen bzw. der in einer anderen Tabelle nachgeschlagen werden soll
• table_array: Tabelle, in der nach dem Wert (z.B. dem Unternehmensnamen) gesucht werden soll. Um gefunden zu werden, muss dieser Wert in der ersten Spalte der markierten Tabelle vorkommen
• col_index_num: Nummer der Spalte, in der der gesuchte Datenpunkt zu finden ist (die Spalte mit dem lookup_value zählt als Spalte 1)
• range_lookup: Entweder TRUE oder FALSE. Wenn hier FALSE eingetragen wird, dann wird nach einer exakten Übereinstimmung der Werte (also z.B. der Unternehmensnamen) gesucht

Beispiel SVERWEIS: Unternehmensdaten zusammenfassen

Ihr habt zwei verschiedene Tabellen, welche ein paar Finanzdaten verschiedener Unternehmen enthalten. In einer der Tabellen werden verschiedene Bewertungskennzahlen dargestellt, in der anderen die wesentlichen Daten aus dem Jahresabschluss (Umsatz, Nettogewinn etc.).

Unglücklicherweise sind die Unternehmenslisten nicht identisch bzw. die Reihenfolge der Unternehmen unterscheidet sich:

Mithilfe des SVERWEIS könnt ihr nun Informationen aus beiden Tabellen in eine einzige zusammenführen… und zwar ohne aufwendiges “Copy und Paste”:

Wie ihr vielleicht erkennen könnt, sucht Excel in diesem Beispiel in der Tabelle 1 (rötlich hinterlegt) nach der Zeile, in der “Company 4” zu finden ist, und gibt dann den Wert zurück, der in der zweiten Spalte steht (in diesem Fall ein KGV von 70).

2. INDEX VERGLEICH bzw. INDEX MATCH

Die Kombination aus den zwei Excel Formeln bzw. Excel Funktionen INDEX und VERGLEICH bzw. INDEX und MATCH stellt eine weitere Automatisierung der SVERWEIS Funktion dar. Denn im Grunde genommen tut dieses Konstrukt genau das Gleiche wie ein SVERWEIS, nämlich Werte nachschlagen und zurückgeben.

Während wir allerdings beim SVERWEIS nur eine Dimension nachschlagen können, ermöglicht es uns die Kombination aus INDEX und MATCH, in einer Tabelle gleichzeitig nach einer bestimmten Zeile und einer bestimmten Spalte zu suchen und den Wert der resultierenden Zelle zurückzugeben.

INDEX MATCH Formel

Im Grunde genommen ist die Index Funktion dafür zuständig, innerhalb einer festgelegten Tabelle eine bestimmte Zelle zu identifizieren und den Wert dieser Zelle zurückzugeben.

Entsprechend der INDEX Funktion erfolgt dies über die Festlegung von Zeilen- und Spaltennummer innerhalb der Tabelle:

INDEX(array,row_num,column_num)

wobei

• array: Die Tabelle, in der gesucht werden soll
• row_num: Die Nummer der Zeile, die die gesuchte Zelle enthält (Ausgangspunkt mit Wert 1 ist die obere linke Ecke der Tabelle)
• col_num: Die Nummer der Spalte, die die gesuchte Zelle enthält

Die VERGLEICH bzw. MATCH Funktion hingegen sucht in einer Zeile oder Spalte nach einem vorgegebenen Wert und gibt die Spalten- bzw. Zeilennummer zurück:

MATCH(lookup_value, lookup_array, [match_type])

mit

• lookup_value: Der Wert, der gesucht werden soll
• row_num: Der Bereich, der durchsucht werden soll (hier eine Zeile oder eine Spalte)
• match_type (optional): Spezifiziert, welcher Wert genau zurückgegeben werden soll. Wir sollten hier immer eine 0 für eine exakte Übereinstimmung eintragen

Wenn wir also innerhalb der INDEX Funktion die Angabe von Zeilen- und Spaltennummer mithilfe der MATCH Funktion automatisieren, können wir ganz flexibel beliebige Zellen in einer Tabelle ansteuern.

Beispiel INDEX MATCH

Wir haben wieder die zwei Tabellen mit verschiedenen Unternehmensdaten aus dem SVERWEIS Beispiel und möchten für das Unternehmen 4 die EBIT-Marge aus Tabelle Nr. 2 auslesen.

Folgendermaßen sieht die Lösung hierfür aus:

Die MATCH Funktion identifiziert also die richtige Zeile (Company 4 = Zeile 2 der Tabelle bzw. Zeile 16 im Spreadsheet) sowie die richtige Spalte (EBIT-Marge = Spalte 5 der Tabelle bzw. Spalte F im Spreadsheet). Die INDEX Funktion gibt anschließend genau den Wert zurück, der sich in der ausgewählten Tabelle in der Zelle F16 befindet (nämlich in diesem Fall 1,0%).

Wichtig für die richtige Nutzung von INDEX MATCH ist, dass die Länge der jeweiligen Zeile bzw. Spalte innerhalb der MATCH Funktion mit den Abmessungen des Bereichs (Arrays) der INDEX Funktion übereinstimmt.

Dieses kleine Beispiel dient natürlich nur zu Demonstrationszwecken. Bei größeren Datenmengen und komplexen Tabellen kann INDEX MATCH durchaus die bessere Alternative zum SVERWEIS darstellen.

3. INDIREKT bzw. INDIRECT

Die INDIREKT Funktion ist eine meiner favorisierten Excel Formeln, weil sie gleichzeitig Daten aus verschiedenen Tabellenblättern auslesen kann… und zwar in Abhängigkeit von der Bezeichnung bzw. dem Namen des Tabellenblattes.

Stellt euch folgende Situation vor: Ihr habt ein Bewertungstool in Excel, wobei jedes Tabellenblatt die Bewertung eines anderen Unternehmens enthält. Ich habe mein Bewertungstool z.B. so aufgebaut, damit ich für eine neue Bewertung einfach nur ein Tabellenblatt kopieren und mit den Werten eines anderen Unternehmens füllen muss. In einem solchen Fall wäre natürlich ein zusammenfassendes Tabellenblatt, das die Bewertungsergebnisse, Kennzahlen, Risikoprofile etc. aller bewerteten Unternehmen enthält, sehr hilfreich.

Anstatt diese Zusammenfassung jedes Mal manuell um ein weiteres Unternehmen zu erweitern, können wir das mithilfe der INDIREKT Funktion einfach automatisieren.

INDIREKT Formel

Hier zunächst die erstmal ein wenig nichtssagende Struktur der INDIREKT Funktion:

INDIREKT(ref_text,[a1])

wobei

• ref_text: Die Referenz zu einer bestimmten Zelle als Textfolge (“String”)
• a1 (optional): Logischer Wert, der die Art der Referenz festlegt (A1 Style oder R1C1 Style)

Die INDIREKT Funktion erlaubt es uns, eine Referenz zu einer Zelle als beliebige Textfolge zusammenzusetzen und so einen dynamischen Link zu einem beliebigen Tabellenblatt zu erzeugen.

Beispiel INDIREKT: Zusammenfassungsseite Bewertungstool

Nehmen wir einmal an, wir haben wie oben beschrieben ein Bewertungstool mit einer ganzen Reihe an Tabellenblättern, d.h. einer Zusammenfassung sowie vielen individuellen Unternehmensbewertungen. Die Tabellenblätter mit den Unternehmensbewertungen sind jeweils folgendermaßen benannt:

• Valuation Company 1
• Valuation Company 2
• Valuation Company 3
• etc.

Jedes der Tabellenblätter ist gleich aufgebaut und der intrinsische Wert befindet sich jeweils in der Zelle H28.

Wir möchten nun den aktuellen intrinsischen Wert der einzelnen Unternehmen mithilfe der INDIREKT Funktion automatisch im Tabellenblatt “Zusammenfassung” anzeigen lassen. Hier die Lösung für dieses Problem:

Wie ihr sehen könnt, bilden wir mithilfe der INDIREKT Funktion den Link zu einem bestimmten Tabellenblatt nach (nämlich dem Tabellenblatt “Valuation Company 3”, wobei der Unternehmensname eine Verlinkung auf Zelle B25 darstellt) und verweisen dann in diesem Tabellenblatt auf die Zelle H28… die den intrinsischen Wert enthält, den wir gerne in unserer Zusammenfassung sehen möchten.

Im Zusammenspiel mit der Funktion INDIREKT nutzen wir hier übrigens bereits die Option der Verkettung verschiedener Texte und Zelleninhalte, welche ich euch im Folgenden vorstellen möchte.

4. VERKETTEN bzw. CONCATENATE

VERKETTEN bzw. CONCATENATE ist zwar auch eine Funktion in Excel. Im Grunde genommen lässt sich das Verketten von mehreren Zellen und Informationen aber auch ohne die Funktion sehr einfach gestalten, nämlich indem wir den Operator “&” für die Verknüpfung verwenden und Textfolgen in Anführungszeichen setzen.

Neben der oben bereits vorgestellten Anwendung innerhalb der INDIREKT Funktion gibt es für das Verketten verschiedener Zellen und/oder Texte viele weitere Anwendungsmöglichkeiten.

Beispiel VERKETTEN: Tickersymbol

Eine dieser möglichen Anwendungen des Verketten-Operators besteht z.B. in der Zusammensetzung eines Tickersymbols für den Download von Finanzdaten aus dem Internet. Das Tickersymbol könnte vom entsprechenden Anbieter z.B. als Kombination aus Börse und Unternehmenssymbol definiert worden sein. Beispielsweise hätte die Freenet Aktie auf Xetra dann das Tickersymbol XETR:FNTN (Morningstar) oder FNTN.DE (Yahoo! Finance).

Diese Abfolge können wir mithilfe des “&”-Operators wie folgt zusammensetzen (aus Zelle E4, einem Doppelpunkt in Anführungszeichen und Zelle E5):

5. STEIGUNG bzw. SLOPE

Die STEIGUNG bzw. SLOPE Funktion ist einigen von euch vielleicht bereits aus der Regressionsanalyse bekannt. Die Funktion ermittelt die Steigung der Regressionsgeraden, die durch eine Reihe an Datenpaaren (also i.W. eine Punktewolke) gelegt wird.

Beispiel STEIGUNG: Berechnung des Beta-Faktors

Die Funktion erlaubt z.B. die Ermittlung des Beta-Faktors ohne gleich eine komplette Regressionsanalyse oder graphische Darstellung durchführen zu müssen. Alles was wir dafür benötigen, sind die Zeitreihen der zwei voneinander abhängigen Variablen, nämlich in diesem Fall die Marktreturns und die Returns des Einzelwertes.

In meinem Artikel zur Ermittlung des Beta-Faktors hatte ich den Beta-Faktor einmal am Beispiel der Aktie von Daimler abgeleitet:

6. BEREICH.VERSCHIEBEN bzw. OFFSET kombiniert mit AVERAGE

Die Excel Formeln BEREICH.VERSCHIEBEN bzw. OFFSET (vielleicht versteht ihr jetzt, warum ich lieber mit den englischen Begriffen arbeite 🙂 ) und MITTELWERT sind für sich genommen nicht übermäßig interessant. Wenn wir die beiden Funktionen dagegen kombinieren, können wir damit einige ganz typische Berechnungen in unseren Finanzmodellen signifikant vereinfachen… wie ihr gleich sehen werdet.

BEREICH.VERSCHIEBEN Formel

Ich denke auf die MITTELWERT Funktion brauche ich hier nicht im Detail einzugehen. Die ist quasi selbsterklärend und berechnet das arithmetische Mittel aus einer vorgegebenen Reihe an Datenpunkten. Die OFFSET bzw. BEREICH.VERSCHIEBEN Funktion hingegen ist vielleicht aktuell noch nicht jedem ein Begriff.

Hier die Funktion einmal im Überblick:

BEREICH.VERSCHIEBEN(reference, rows, cols)

wobei

• reference: Referenzzelle als Ausgangspunkt für das Verschieben der Zelle
• rows: Anzahl an Zeilen, die von der Referenzzelle aus nach unten verschoben werden soll
• cols: Anzahl an Spalten nach rechts

Im Wesentlichen gibt die Funktion eine Zelle bzw. den Wert einer Zelle zurück, die von einem festgelegten Referenzpunkt aus gesehen eine festgelegte Anzahl an Zeilen nach unten und eine festgelegte Anzahl an Spalten nach rechts entfernt liegt.

Beispiel MITTELWERT + BEREICH VERSCHIEBEN: Durchschnittliches KGV

Ein gutes Beispiel für die kombinierte Nutzung der Funktionen MITTELWERT und BEREICH.VERSCHIEBEN ist die Berechnung eines durchschnittlichen KGV für eine flexible Anzahl an Jahren.

Angenommen wir haben eine 10-jährige Zeitreihe an KGVs für ein Unternehmen vorliegen und möchten hierfür flexibel einen Durchschnitt berechnen.

Um das umzusetzen, ersetzen wir einfach die Endreferenzzelle der MITTELWERT Funktion durch ein BEREICH.VERSCHIEBEN. Dadurch wird das Ende des Bereichs, über den der Mittelwert berechnet werden soll, flexibel.

In der BEREICH.VERSCHIEBEN Funktion verweisen wir dann auf die Zelle, in der wir angeben, über wie viele Jahre wir den Durchschnitt berechnen möchten:

Wie ihr seht, beginnt (bzw. endet) die MITTELWERT Formel in Zelle L5, endet (bzw. beginnt) jedoch mit einer Variablen. Dies ist die OFFSET Formel, die bei L5 beginnt und den Endpunkt um die in der Zelle D7 eingetragene Zahl (minus 1) nach links verschiebt.

Wählen wir im gelb hinterlegten Feld also aus, dass wir das durchschnittliche KGV über die letzten 5 Jahre ermitteln wollen, dann verschiebt die BEREICH.VERSCHIEBEN Funktion ausgehend von Zelle L5 den Endpunkt um 0 Zeilen nach unten und um -4 Spalten (-5+1) nach rechts (also effektiv um 4 nach links). Auf diese Weise wird effektiv, d.h. inkl. des letzten bzw. aktuellen Jahres, das durchschnittliche KGV der letzten 5 Jahre berechnet.

7. WAHL bzw. CHOOSE

Die WAHL bzw. CHOOSE Funktion eignet sich hervorragend für die Szenarioanalyse im Rahmen der Finanzmodellierung, weil sie uns erlaubt, zwischen einer bestimmten Anzahl von Optionen zu wählen. Die von uns gewählte Option wird dann von der CHOOSE Funktion zurückgegeben.

WAHL Formel

Die WAHL Funktion ist im Grunde genommen ganz simpel:

CHOOSE(choice, option1, option2, option3)

mit

• choice: Die getroffene Auswahl (also z.B. 1, 2 oder 3 für Szenario 1, Szenario 2 oder Szenario 3)
• option1, option2, etc.: Werte der verschiedenen Auswahlmöglichkeiten (also z.B. Szenarien für Wachstumsraten)

In Abhängigkeit von der getroffenen Auswahl gibt die WAHL Funktion also den entsprechenden Wert zurück.

Beispiel WAHL bzw. CHOOSE: Szenarioauswahl

Stellt euch vor, ihr habt drei verschiedene Annahmen für das Umsatzwachstum im nächsten Jahr: ein konservatives Szenario (Wachstum 1,5%), ein Basisszenario (5%) und ein optimistisches Szenario (8%). Mithilfe der CHOOSE Funktion könnt ihr euch den Wert des Basisszenarios zurückgeben lassen, wenn ihr Excel sagt, dass ihr das Szenario Nr. 2 auswählen möchtet:

Weitere Ressourcen

• Excel als Finanzrechner: Wie wir Portfoliowerte auf Basis von Annuitäten ermitteln
• Sensitivitätsanalyse in Excel: Datentabelle mit zwei Variablen

Habt ihr weitere Excel Formeln bzw. Funktionen, die ihr im Rahmen der Unternehmensbewertung bzw. Aktienanalyse in Excel oft verwendet und die in dieser Liste auf keinen Fall fehlen sollten? Schreibt mit einfach eine Nachricht (axel@diyinvestor.de) bzw. kommentiert unten.