• Aktien
  • Bewertungsmodelle
  • Checklisten
  • Portfoliokonstruktion
  • Stock Screens
  • Top-Investoren
  • Daten & Tools

DIY Investor

Mit einem Value Investing Ansatz viel erreichen

  • Start
  • About
  • Blog
  • Bücher
  • Ideen
  • Toolbox
  • Lexikon
  • Kontakt
Du befindest dich hier: Home / Daten & Tools / Nur eine Zeile VBA-Code: IRR-Berechnung bzw. Goal Seek in Excel automatisieren

Jun 27 2021

Nur eine Zeile VBA-Code: IRR-Berechnung bzw. Goal Seek in Excel automatisieren


In einem der letzten Artikel auf DIY Investor hatte ich euch die Goal Seek Funktion vorgestellt, eine Excel-Funktion, die es uns ermöglicht, den IRR, also den durchschnittlichen jährlichen Return oder CAGR, eines Investments zu ermitteln.

Warum ist der IRR nochmal eine relevante Größe?

Weil er uns ermöglicht, einzelne Investments direkt miteinander zu vergleichen. Ein DCF-Modell gibt uns zwar den inneren Wert einer Aktie aus, sagt uns aber noch nichts darüber, welchen Return wir tatsächlich erzielen würden und wie attraktiv die Option im Vergleich zu den vorhandenen Alternativen ist (für weitere Informationen zu dieser Logik, lest euch am besten einmal den DIY Investor Artikel zum Thema Opportunitätskosten durch).

Ich weiß nicht, ob ihr die Goal Seek Funktion vielleicht bereits ausprobiert habt oder nicht. Ein großes Manko der Funktion im Standardfall besteht jedenfalls darin, dass wir die Inputdaten bei jedem Durchlauf immer wieder neu eingeben müssen.

Heißt: Wenn wir einen Parameter an unserer Cash Flow Abschätzung ändern, müssen wir anschließend die Goal Seek Funktion wieder neu aufrufen, die entsprechenden Zellen auswählen und den Zielwert manuell eingeben…

Das schreit ja förmlich nach einer weiteren Automatisierung mittels eines einfachen Makros!

In diesem Artikel möchte ich euch deshalb einmal kurz zeigen, wie ihr die Berechnung des IRR mithilfe der Goal Seek Funktion in eurem Bewertungstool quasi automatisieren könnt (ich sage „quasi“, weil ich immer noch einen Update-Button vorsehe, damit Excel nicht permanent im Hintergrund neu iteriert, obwohl wir vielleicht gerade eine ganze Zeitreihe aktualisieren).


Beispiel für die Automatisierung von Goal Seek in Excel

Wenn die IRR-Berechnung innerhalb eines größeren Excel-Modells stattfinden und keine anderen Berechnungen beeinflussen soll (wie bei mir der Fall), dann sollten wir zunächst die wesentlichen Parameter, nämlich die zukünftigen Cash Flows, in einen separaten Bereich in unserem Excel Sheet erfassen und eine Bewertung mit einem fiktiven WACC durchführen.

Gleichzeitig sollten wir die aktuelle Marktkapitalisierung, also den aktuellen Marktwert des Unternehmens an der Börse, in einer separaten Zelle erfassen.

Hier seht ihr einmal ein Beispiel eines solchen Modells bzw. Abschnitts eines Modells:

Ausgangssituation für Goal Seek: DCF-Modell

Hier ein paar Kommentare dazu:

  • Die jährlichen freien Cash Flows (in diesem Fall die allen Kapitalgebern zur Verfügung stehenden Cash Flows bzw. FCFF) habe ich mithilfe eines detaillierteren 3-Statement Modells ermittelt und nur an die entsprechende Stelle verlinkt
  • Die Discount Rate bzw. der Abzinsungsfaktor basiert auf einem fiktiven (d.h. ausgedachten) WACC in Höhe von 8% (Eingabe in Zelle E276)
  • Falls ihr euch über den konstanten Abzinsungsfaktor wundert: Ich nutze hier das Roll Back Verfahren für die Barwertberechnung
  • Unter Berücksichtigung der Nettoverschuldung und der nicht-beherrschenden Anteile ergibt sich ein fairer Wert für das Eigenkapital in Höhe von ca. 35,8 Mrd. USD (Zelle E271)
  • DIe tatsächliche Marktkapitalisierung zum Zeitpunkt der Bewertung lag bzw. liegt bei ca. 108 Mrd. USD (Zelle E274)

Vergleichen wir nun also den fairen Wert (35,8 Mrd. USD) mit der tatsächlichen Marktkapitalisierung (108 Mrd. USD), dann stellen wir fest, dass der reale Return weitaus geringer sein muss, als die eher willkürlich angesetzten 8% für die Kapitalkosten.

Um zu ermitteln, um wie viel geringer dieser Return ausfällt, automatisieren wir nun also einmal Goal Seek in Excel. Dafür nutzen wir – wie bereits eingangs erwähnt – ein einfaches Makro.


#1: Makroaufzeichnung starten

Die einfachste Art und Weise, um ein solches Makro zu erstellen, besteht darin, einfach einmalig ein Goal Seek durchzuführen und diesen Prozess parallel aufzuzeichnen.

Dafür gehen wir zunächst in Excel auf das DEVELOPER Tab und wählen anschließend den Punkt „Record Macro“ bzw. „Makro aufzeichnen“ aus:

Goal Seek automatisieren - Makro aufzeichnen


#2: Goal Seek in Excel durchführen

Nachdem wir die Makroaufzeichnung gestartet haben, führen wir die Goal Seek Funktion einmalig aus. Wir gehen dazu zunächst unter DATA zu dem Punkt „What If Analysis“ und wählen die Goal Seek Funktion aus:

Goal Seek Funktion aufrufen

Anschließend tragen wir die entsprechenden Zellen bzw. Werte in das sich öffnende Fenster ein:

Goal Seek Parameter

Zielwert ist die Zelle E271, also das Ergebnis unserer Barwertberechnung. Dieses soll gleich der aktuellen Marktkapitalisierung von 107.809 Mio. USD gesetzt werden… und zwar mittels einer Iteration / Anpassung der Kapitalkosten (Zelle E276).

Wie ihr sehen könnt, müssen wir hier den Zielwert (107.809 Mio. USD) manuell eingeben und können diesen nicht auf eine bestimmte Zelle verlinken. Diesen Umstand werden wir nach Abschluss der Makroaufzeichnung als erstes anpassen.

Haben wir alle Parameter eingegeben, bestätigen wir mit OK und erhalten das folgende Resultat:

Goal Seek Ergebnis

Da das Ergebnis der DCF-Bewertung identisch ist zum Zielwert, d.h. der aktuellen Marktkapitalisierung, hat Goal Seek also ein Ergebnis gefunden.

Wenn wir die Meldung wiederum mit OK bestätigen, dann sehen wir, dass sich die Berechnung entsprechend verändert hat:

Ergebnis DCF Bewertung nach Goal Seek

Der interne Return bzw. IRR, bei dem der Barwert der zukünftigen Cash Flows und die Marktkapitalisierung genau identisch sind, beträgt also ~4,5% (und nicht 8%, wie ursprünglich einmal angesetzt.


#3: Makroaufzeichnung stoppen und Makro bearbeiten

Nachdem wir nun also die Goal Seek Funktion einmal ausgeführt haben, können wir zum DEVELOPERS Tab zurückkehren und die Aufzeichnung des Makros abschließen. Dazu klicken wir entsprechend auf die „Stop Recording“ Schaltfläche:

Makro Aufzeichnung stoppen

Anschließend können wir dem Makro direkt einen Namen geben (in diesem Fall „GoalSeek“), es auswählen und direkt editieren:

In VBA Code übersetzt sieht der Goal Seek Vorgang folgendermaßen aus:

Es handelt sich also wie ihr sehen könnt, um ein ganz simples Makro, welches im Wesentlichen nur aus einer Zeile besteht.

Wesentlich ist hier der in rot umkringelte Bereich, denn dieser zeigt den Zielwert an, den wir bei der ersten Durchführung des Makros noch manuell eingeben mussten… die anderen Zellen sind bereits entsprechend verlinkt nach E271 (Ergebnis der DCF-Bewertung) und E276 (WACC).

Wir ersetzen nun also schlicht und einfach den Zahlenwert durch die entsprechende Zelle in unserem Excel (E274):

Haben wir die Anpassung durchgeführt, klicken wir zum Speichern noch oben links auf das Diskettensymbol und fertig.


#4: Update-Button erstellen und verlinken

Als letzten Schritt müssen wir nur noch einen entsprechenden Button in unserem Tabellenblatt einfügen, um zur Durchführung des Makros nicht immer in den Makro-Editor rein zu müssen.

Wir gehen also wieder auf das DEVELOPER Tab, wählen dort die Schaltfläche „Insert“ bzw. „Einfügen“ aus und klicken auf das Button-Symbol oben links (oder alternativ auch auf das Button-Symbol weiter unten unter ActiveX Controls… ist eine andere Art von Button mit etwas mehr Flexibilität):

Button einfügen

Anschließend können wir den Button direkt in unserem Tabellenblatt an der von uns gewünschten Stelle platzieren. Haben wir das getan, dann öffnet sich automatisch das folgende Fenster mithilfe dessen wir dem Button direkt unser Makro (weiter oben bereits mit „GoalSeek“ benannt) zuordnen können:

Nachdem wir die Auswahl des Makros mit OK bestätigt haben, sind wir auch schon fertig. Zukünftig können wir nach jeder Anpassung unserer Cash Flow Schätzung bzw. jeder Veränderung der Marktkapitalisierung den IRR auf Knopfdruck neu ermitteln lassen.

Aus meiner Sicht eine sehr komfortable Art, um eine DCF-Bewertung um eine Return-Sicht zu erweitern.


Mögliche Erweiterung

Für die grundsätzliche Funktionsweise des Makros ist das zwar nicht relevant… wenn wir unser Modell allerdings regelmäßig anpassen und hier und da mal eine Zeile hinzufügen, löschen oder verschieben, dann könnte es schonmal sein, dass die im Makro verlinkten Zellen nicht mehr passen und wir eine Fehlermeldung erhalten.

Anzeige

Um dieses Problem zu umgehen, können wir die relevanten Zellen einfach individuell benennen (also ein so genanntes „Named Range“ bzw. einen „Benannten Bereich“ erstellen). Dies ist ganz einfach möglich, indem wir mit dem Cursor oben links in den Bereich klicken, in dem die Zellbezeichnung angegeben ist (also z.B. „E271“), einen für uns passenden Namen eintragen (z.B. „DCFErgebnis“) und mit „Enter“ bestätigen. Im Makro müssen wir die Zellbezeichnung dann natürlich noch entsprechend ersetzen (also z.B. „E271“ durch „DCFErgebnis“).

Zukünftig sollte das Makro dann immer auf die entsprechend benannten und daher „richtigen“ Zellen verweisen.


Zusammenfassung Goal Seek automatisieren

Um einen „Like-for-Like“ Vergleich mit anderen Investment-Opportunitäten zu ermöglichen, bietet sich die Ermittlung des so genannten IRR (steht für Internal Rate of Return) an.

Für eine bekannte Marktkapitalisierung und eine erwartete zukünftige Cash Flow Zeitreihe lässt sich der IRR einfach per Goal Seek in Excel ermitteln.

Da das Aufrufen der Funktion und das manuelle Auswählen bzw. Eintippen der Parameter allerdings immer etwas umständlich und zeitaufwendig ist, können wir den Vorgang einfach – wie in diesem Artikel beschrieben – mithilfe der Aufzeichnung eines kleinen Makros automatisieren.


Geschrieben von Axel · Kategorien: Daten & Tools

Anzeige

Schreibe einen Kommentar Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

I accept that my given data and my IP address is sent to a server in the USA only for the purpose of spam prevention through the Akismet program.More information on Akismet and GDPR.

ARTIKELSUCHE

TOP PICKS

Die Top Artikel auf DIY Investor aus meiner und aus Lesersicht


Aktienkurse mithilfe der Yahoo Finance API aus dem Netz ziehen bzw. Historische Kursdaten nach Excel importieren mit der Alpha Vantage API


Henry Singleton: Woran wir ein gutes Management erkennen


Deep Dive: Owner Earnings versus Free Cash Flow


Die Portfolio-Strategie von Mohnish Pabrai


Meine erste Eigentumswohnung


Die Basics über Kryptowährungen

MEISTGELESEN

Spardosen-GmbH Teil III: Steuerberater- und andere Strukturkosten

Spardosen-GmbH Teil III: Steuerberater- und andere Strukturkosten

Finanzen automatisieren

Finanzen automatisieren und Portfolio aufbauen: So gehts!

Die 7 wichtigsten Excel Formeln für Investoren

Die 7 wichtigsten Excel Formeln für Investoren

BOOKSHELF

Buch Review: Lights Out - Der lange Abstieg von General Electric

Buch Review: Lights Out – Der lange Abstieg von General Electric

Buch Review: Crash Landing - An Inside Account of the Fall of GPA

Buch Review: Crash Landing – An Inside Account of the Fall of GPA

Buch-Tipp: When the Wolves Bite von Scott Wapner

Buch-Tipp: When the Wolves Bite von Scott Wapner

Weitere Investment-Bücher >>

 

 
Weitere Artikel, die ich empfehle:

 

Color Coding & More: Ein übersichtliches Finanzmodell in Excel gestalten

Color Coding & More: Ein übersichtliches Finanzmodell in Excel gestalten

20. März 2022 / Excel, Daten & Tools
Geschäftsberichte mit Microsoft Word auf Veränderungen hin untersuchen

Geschäftsberichte mit Microsoft Word auf Veränderungen hin untersuchen

19. Dezember 2021 / Daten & Tools
10 empfehlenswerte Investing Podcasts

10 empfehlenswerte Investing Podcasts

6. Juni 2021 / Daten & Tools
Finbox: Die Investoren-Toolbox im Test

Finbox: Die Investoren-Toolbox im Test

23. August 2020 / Daten & Tools
Waterfall Charts in Excel: Veränderungen in EBIT und EBITDA verstehen

Waterfall Charts in Excel: Veränderungen in EBIT und EBITDA verstehen

17. Mai 2020 / Jahresabschlussanalyse, Daten & Tools
Update Yahoo Finance API 2020: Historische Aktienkurse per VBA Makro ins Excel laden

Update Yahoo Finance API 2020: Historische Aktienkurse per VBA Makro ins Excel laden

8. Mai 2020 / Daten & Tools
Excel Dashboard etc.: 4 hilfreiche Tools

Excel Dashboard etc.: 4 hilfreiche Tools

12. Oktober 2019 / Daten & Tools
Die 10 goldenen Regeln für die Erstellung eines Best Practice Excel-Modells

Die 10 goldenen Regeln für die Erstellung eines Best Practice Excel-Modells

6. September 2019 / Daten & Tools
Vergesst Makros: Mit dem Add-in von Alpha Vantage Aktienkurse direkt nach Excel importieren

Vergesst Makros: Mit dem Add-in von Alpha Vantage Aktienkurse direkt nach Excel importieren

23. August 2019 / Daten & Tools
Update: Das DIY Portfolio Analyse Tool 4.0 für Google Sheets

Update: Das DIY Portfolio Analyse Tool 4.0 für Google Sheets

31. Mai 2019 / Daten & Tools

Über uns
DIY Investing
FAQs

Wir sind auch hier gelistet

Blogverzeichnis Bloggerei.de

Finanzblogroll
Finanzblognews
valueDACH
Nachrichten-Fabrik
  • Impressum
  • Datenschutz
  • Kontakt
  • DIY Toolbox

© Copyright 2015-22 DIY Investor- Mit wenig Zeit viel erreichen · Alle Rechte vorbehalten