Automatisierte Liquiditätsplanung in Excel mit LAMBDA-Funktion … nur eine einzige Formel in einer Zelle!

August 9, 2023
Alle Beiträge, Excel Best Practice, Financial Modelling

Eine neue Excel-Welt

Mit Office bzw. Excel 365 wurden dynamische Arrays (= dynamische Bereiche) eingeführt. Dazu viele neue Funktionen, welche nicht nur Dinge möglich machen, die bis dahin nur umständlich mit vielen Hilfszellen umgesetzt werden konnten, sondern die insbesondere auch bei umfangreichen Datenbeständen erhebliche Performancegewinne gegenüber den klassischen Matrixfunktionen gebracht haben.

Die Einführung der LAMBDA-Funktion in Excel (nur ab Excel 2021 bzw. Office 365) stellt einen weiteren Meilenstein dar. Damit lassen sich benutzerdefinierte, wiederverwendbare Funktionen erstellen, denen im Namensmanager ein benutzerfreundlicher Name zugewiesen werden kann. Die neue Funktion ist anschließend in der gesamten Arbeitsmappe verfügbar und wird wie native Excel-Funktionen aufgerufen. Im Gegensatz zu den seit vielen Jahren bekannten benutzerdefinierten Funktionen (= User Defined Functions bzw. UDF), handelt es sich bei LAMBDA-Funktionen aber nicht um VBA-Makros (und auch nicht um JavaScript). Damit kann sich auch kein potentiell gefährlicher Schadcode in den Excel-Dateien befinden und alle Administratoren in den Unternehmen schlafen besser.

Sebastian Grüner schrieb bei golem.de Ende 2020 bereits euphorisch:

„Excel bringt endlich das Ende von VBA – Dass Excel jetzt Turing-vollständig ist, ist deutlich weniger verrückt, als es klingt. Denn es hilft Nutzern und der Security.“

Ob wirklich alles nur positiv ist oder ob es auch Einschränkungen bzw. Nachteile gibt, erfahren Sie am Ende dieses Beitrages.

Warum erst jetzt bei Fimovi

Erstmalig angekündigt wurden LAMBDA-Funktionen bereits Ende 2020. Aus verschiedenen Gründen greifen wir bei Fimovi das Thema aber erst jetzt, also ca. 2 Jahre später, auf. Die Hauptgründe sind dabei neben Kinderkrankheiten in der Anfangszeit (z.B. kein IntelliSense, fehlende „Lokale“ im Editor, nur englische Funktionsnamen verwendbar (keine automatische Übersetzung), zu Beginn nur im Beta-Kanal bzw. Office-Insider-Programm verfügbar etc.) vor allem das Fehlen von sinnvollen betriebswirtschaftlichen Anwendungen.

Während viele Excel-Aficionados diese Neuerung mit teilweise trivialen Anwendungen (da wurde mit Kanonenkugeln auf Spatzen geschossen) schnell in ihren Blogs veröffentlichten (vermutlich um Klickzahlen zu generieren), fokussiert Fimovi immer auf betriebswirtschaftliche Anwendungen. Wir stellen also keine neuen Excel-Funktionen nur der Funktion wegen vor, sondern nur dann, wenn damit eine konkrete Aufgabenstellung besser (d.h. schneller od. einfacher) gelöst werden kann, als mit bereits etablierten Funktionen.

Als Financial Modelling Experten und Excel-Lösungsanbieter im betriebswirtschaftlichen Kontext analysieren und beobachten wir den Markt (auch international) genau. Leider haben wir auch nach etwa 2,5 Jahren LAMBDA-Historie erst wenige Anwendungen gefunden, die wiederkehrende Herausforderungen im betriebswirtschaftlichen Financial Modelling smart lösen.

Dazu zählen gute Lambda-Funktionen zur Berechnung verschiedener Abschreibungsvarianten, die Generierung von Zins- und Tilgungsplänen für bestimmte Darlehensarten sowie das im Folgenden vorgestellte elegante Beispiel zur einfachen Ermittlung wiederkehrender Zahlungstermine (siehe Quellen u. Links am Ende dieses Beitrages). Die finale Excel-Datei unseres Beispiels stellen wir ihnen wie üblich am Ende des Beitrags kostenlos zum Download zur Verfügung.

LAMBDA-Funktion: Das Grundprinzip

Bevor es ans Eingemachte geht, eine (ganz) kurze Beschreibung der Grundprinzipien von LAMBDA-Funktionen. Fortgeschrittene Excel-Nutzer sollen hier nicht gelangweilt werden und schließlich gibt es im Netz bereits zahlreiche gute Einführungs- bzw. Übersichtsartikel.

Die Syntax der LAMBDA-Funktion zeigt Abbildung 1. Dabei können bis zu 253 Parameter verwendet werden. Das letzte Argument dieser Funktion muss eine Berechnung sein. In dieser Berechnungsformel werden die vorher definierten Parameter eingesetzt bzw. verwendet. Normalerweise wird also in einem ersten Schritt eine LAMBDA-Funktion in einer Zelle oder besser in einem Editor erstellt und getestet. Im einem zweiten Schritt wird dann ein aussagekräftiger Funktionsname (in Abb. 1 bspw. „myLambda“) mit dem zugehörigen Code einmalig im Namensmanager von Excel hinterlegt.

Eine wesentliche Stärke von LAMBDA-Funktionen ist, dass diese – anders als im obigen trivialen Einführungsbeispiel – auch im Zusammenspiel mit anderen „Hilfsfunktionen“ genutzt werden können. Dazu gehören neben der LET-Funktion vor allem die Funktionen MAP, REDUCE, SCAN, MAKEARRAY, BYROW, BYCOL sowie ISOMITTED.

Schließlich lassen sich mit der LAMBDA-Funktion sogar Rekursionen implementieren. Dabei wird die LAMBDA-Funktion aus sich heraus erneut aufgerufen. Damit ist es möglich, auch Schleifen innerhalb der LAMBDA-Funktion zu verwenden. Man kann eine Aktion x-mal ausführen, ähnlich einer For-To-Next-Zählschleife. Wichtig ist dabei allerdings, eindeutige Abbruchbedingungen zu definieren (ähnlich While-Schleifen und Do…Until-Schleifen).

Liquiditätsplanung mit LAMBDA

a) Ausgangssituation

Am besten lernt man anhand einer praxisnahen Anwendung. Deshalb haben wir das Thema Liquiditätsplanung in Excel für ein Beispiel herausgepickt. Bei Controlling- und Finanzverantwortlichen steht eine solche Planung häufig auf der Agenda. Dabei sollte diese möglichst flexibel und rollierend möglich sein. Außerdem sollten wiederkehrende Zahlungen mit wenigen Eingaben einfach erfasst und dann automatisch den richtigen Zahlungszeitpunkten zugeordnet werden können (bspw. die monatliche Miete, die immer am 3. eines Monats zur Zahlung fällig ist oder quartalsweisen Tilgungen eines Darlehens etc.)

Startpunkt für unsere Liquiditätsplanung ist eine intelligente Tabelle (der wir den Tabellennamen Tab_Input gegeben haben) mit 5 Eingabemöglichkeiten pro Ein- bzw. Auszahlung (vgl. Abb. 2). Die erste Spalte ist rein deskriptiv und bezeichnet die jeweilige Position im Klartext. Die Betragsspalte definiert (in beliebiger Währung) den jeweiligen Zahlungsbetrag (positiv für Einzahlungen u. negativ für alle Auszahlungen). Der Serientyp kann über ein Dropdown für jede Zahlung individuell ausgewählt werden (die zugehörige Auswahlliste befindet sich übrigens auf dem ausgeblendeten Tabellenblatt „Formate“) und bestimmt das Intervall für wiederkehrende Zahlungen (Ausnahme: einmalig). In der Spalte Seriencode befinden sich keine Eingabezellen, sondern diese liefert uns über eine schlichte SVERWEIS-Funktion Kürzel für die spätere Funktion (es soll ja übersichtlich bleiben!). Schließlich lässt sich in den letzten beiden Eingabe-Spalten noch per Datumseingabe der Startzeitpunkt bzw. das Ende, also die letzte Zahlung festlegen. Während in der Spalte „Start“ zwingend ein Datum eingegeben werden muss, damit später alles ordnungsgemäß funktioniert, ist die Spalte „Ende“ optional. Wird diese leer gelassen, sollen die Zahlungen ewig weiterlaufen (Ausnahme: einmalig).

Am oberen Blattrand (Zeile 5) haben wir noch ein flexible Zeitleiste auf Tagesbasis integriert (Zeitraum 2 Jahre bzw. 730 Tage), die jederzeit schnell am rechten Ende erweitert bzw. verlängert werden kann (vgl. Abb. 3).

Das Startdatum kann individuell in D5 vorgegeben werden, so dass später in der fertigen Datei auch rollierende Liquiditätsplanungen möglich sind. Eigentlich sind nur die Datumswerte der einzelnen Tage in Zeile 5 relevant, wir haben aber noch zusätzliche Infos wie Monat, Wochentag und Kalenderwoche (nach ISO-Norm 8601) ergänzt. Diese Zeilen können, falls gewünscht einfach über Gruppieren ausgeblendet werden. Darunter zwei einfache Summenzeilen, die später jeweils alle positiven Werte (= Einzahlungen) bzw. alle negativen Werte (= Auszahlungen) summieren und eine dritte Zeile, die den Saldo, also den Netto-Cashflow pro Tag, ausweist.

So weit so unspektakulär. Bis zu diesem Punkt könnten wir auch noch mit Excel 2003 arbeiten.

Experten-Tipp

Einfache Erstellung & Verwaltung von LAMBDA-Funktionen in Excel

Mit dem offiziellen Microsoft Excel Add-in „Advanced Formula Environment“ (AFE) wird die Entwicklung und Verwaltung von komplexen Funktionen (bspw. auch der hier besprochenen LAMBDA-Funktionen) erheblich erleichtert.

Sie können das Add-In ganz einfach nutzen, in dem Sie in Excel im Menu „Einfügen“ in der Gruppe „Add-Ins“ auf „Add-ins abrufen“ klicken und oben in das Suchfeld den AFE oder „Microsoft Garage“ eingeben. Das Add-In ist in den „Excel Labs“ enthalten (vgl. Abb. 4), welches Sie durch einen Klick auf „Hinzufügen“ einfach installieren können.

Nach der Installation finden Sie das Add-In-Icon im Menu „Start“ ganz rechts.

b) LAMBDA-Funktion

Die Grundidee unserer LAMBDA-Funktion ist, ein Array aus WAHR/FALSCH- bzw. 1/0-Werten zurückzugeben. Dabei wird geprüft, ob ein wiederkehrendes Ereignis (hier eine regelmäßige/wiederkehrende Zahlung) an einem bestimmten Datum stattfindet und gibt für diesen Fall eine 1 (= WAHR) zurück.

Klingt kompliziert, ist aber im Grunde einfach zu verstehen. Wird bspw. eine bestimmte Zahlung monatlich immer am 3. Tag geleistet, so steht in jeder Zelle bei der dies mit dem Datum in der Zeitleiste oben (Zeile 5) übereinstimmt (also bspw. am 3. Jan, 3. Feb, 3. Mär etc.) eine 1, ansonsten überall 0 (Null). Wird am Ende noch mit dem jeweiligen Betrag multipliziert, erhält man das gewünschte Ergebnis (vgl. Funktionsweise in Abb. 5).

Syntax

Unsere neue LAMBDA-Funktion hat die folgende Syntax:

=IstZahlungstag(Datumswerte;SerienCode;Startdatum;[Enddatum])

Dabei bedeuten:

Datumswerte – ist ein eindimensionales Array von Datumswerten auf täglicher Basis
SerienCode – ist ein Kürzel welches für den Serientyp steht, also in welchem Intervall sich ein Ereignis wiederholt
Startdatum – ist ein eindimensionales Array (oder eine Tabellenspalte) in der das jeweilige Startdatum steht
[Enddatum] (optional) – ist ein eindimensionales Array (oder eine Tabellenspalte) in der das jeweilige Enddatum steht (leer bzw. weggelassen bedeutet unendliche Wiederholung)

Dabei wurden folgende Serientypen vorgesehen:

E einmalig (Standard/Default)

T täglich

WT werktäglich

W wöchentlich

2W 2-wöchentlich

M monatlich

Q quartalsweise

HJ halbjährig

J jährlich

Code mit Erläuterungen

Der folgende Block zeigt den Code zur Übersicht und Erläuterung.

Hinweis: Sollte es Probleme geben den hier gezeigten Code 1:1 nach Excel zu kopieren, nutzen Sie einfach unsere fertige Excel-Datei. Diese können Sie am Ende dieses Beitrages kostenlos anfordern.

				
					=LAMBDA(
Datumswerte;
SerienCode;
Startdatum;
[Enddatum];

LET(
    K_Jahre; 12; 
    K_Halbjahre; 6; 
    K_Quartale; 3; 
    K_Monate; 1; 
    K_ZweiWochen; 14; 
    K_Woche; 7; 

    V_Datumswerte; WENN(ODER(WURDEAUSGELASSEN(Datumswerte); Datumswerte = ""); #WERT!; Datumswerte); 
    V_SerienCode; WENN(ODER(WURDEAUSGELASSEN(SerienCode); SerienCode = ""); "E"; SerienCode); 
    V_Startdatum; WENN(WURDEAUSGELASSEN(Startdatum) * (Startdatum = ""); 0; Startdatum); 
    V_GleicherTag; TAG(V_Datumswerte) = TAG(V_Startdatum); 

    Berechnung; (V_Startdatum <= V_Datumswerte) * (ISTLEER(Enddatum) + (V_Datumswerte <= Enddatum)) * ERSTERWERT(V_SerienCode; 
       "E"; V_Datumswerte = V_Startdatum; 
       "J"; V_GleicherTag * (REST(MONAT(V_Datumswerte); K_Jahre) = REST(MONAT(V_Startdatum); K_Jahre)); 
       "HJ"; V_GleicherTag * (REST(MONAT(V_Datumswerte); K_Halbjahre) = REST(MONAT(V_Startdatum); K_Halbjahre)); 
       "Q"; V_GleicherTag * (REST(MONAT(V_Datumswerte); K_Quartale) = REST(MONAT(V_Startdatum); K_Quartale)); 
       "M"; V_GleicherTag * (REST(MONAT(V_Datumswerte); K_Monate) = REST(MONAT(V_Startdatum); K_Monate)); 
       "2W"; REST(V_Datumswerte; K_ZweiWochen) = REST(V_Startdatum; K_ZweiWochen); 
       "W"; REST(V_Datumswerte; K_Woche) = REST(V_Startdatum; K_Woche); 
       "WT"; WOCHENTAG(V_Datumswerte; 2) <= 5; 
       "T"; WAHR;
       0
       );
    Berechnung))

Nach der anfänglichen Definition der vier Funktionsargumente bzw. Parameter, beginnt der eigentliche Code. Die LET-Funktion erlaubt es uns Konstanten und auch (Zwischen-)Berechnungen innerhalb der Funktion einen Namen zuzuweisen und im Rest der Formel diese Namen beliebig oft weiterzuverwenden. Dies dient vor allem dem Zweck, eine Formel lesbarer zu machen, diese möglichst kurz zu halten und nachträgliche Anpassungen zu vereinfachen.

Während bei den ersten 4 Konstanten (Präfix: K_) die Anzahl der jeweiligen Monate definiert wurde, sind bei „K_ZweiWochen“ und „K_Woche“ jeweils Tage gemeint.

Anschließend haben wir noch einige Variablen (Präfix: V_) definiert, die in der späteren Berechnung weiter unten genutzt werden und zum Teil auch Fehlerprüfungen enthalten. Falls bspw. keine Datumswerte gefunden werden, wird ein #WERT-Fehler ausgegeben. Wenn kein Startdatum existiert, wird Null verwendet, um eine Fehlermeldung zu vermeiden. Wenn kein Seriencode gefunden wird, verwendet die Funktion „E“ (einmalig) als Standard.

Der Teil ab „Berechnung“ ist dann die Kernfunktion. Es wird zunächst (über eine multiplikative Verknüpfung) geprüft, ob das Datum zwischen Startdatum und Enddatum liegt (falls nicht wird der Rest mit 0 multipliziert und ergibt deshalb immer FALSCH als Ergebnis). Interessant hierbei, falls kein Enddatum definiert ist (wir erinnern uns, das Argument war optional), resultiert hier eine 1 (= WAHR), d.h. es wiederholt sich im Prinzip zeitlich unbegrenzt.

Die Berechnung der unterschiedlichen Serientypen erfolgt anhand der jeweiligen Seriencodes und sollte weitgehend selbsterklärend sein. Bei den Werktagen wird die Funktion WOCHENTAG genutzt. Verwendet man hier für das zweite Argument [Typ] die 2, dann bekommt der Wochentag Montag die 1, Dienstag die 2 etc., so dass <= 5 Montag bis Freitag bedeutet. Würde man hier bspw. >5 verwenden, dann wäre nur das Wochenende (Samstage + Sonntage) betroffen.

Neue Funktion im Namensmanager hinzufügen

Ruft man (bspw. mit [Strg] + [F3]) den Namensmanager auf. Kann man dort nach Definition des Namens den kompletten Code in das Feld „Bezieht sich auf:“ einfügen (vgl. Abb. 6). Ein Kommentar ist lediglich optional, hilft aber vor allem, wenn Sie später viele eigene Funktionen haben und auch Dritte mit diesen arbeiten sollen.

c) Komplette Liquiditätsplanung mit einer Formel

Um eine vollständige Liquiditätsplanung auf Basis unserer Vorgaben zu erstellen (vgl. Abb. 2), müssen wir die neue Funktion lediglich einmal in eine einzige Zelle einsetzen (in der Beispiel-Datei in Zelle J17). Da wir eine intelligente Tabelle („Tab_Input“) verwendet haben und unserer Zeitleiste in Zeile 5 den Namen „Datumswerte“ gegeben haben, lautet die einfache Formel:

=IstZahlungstag(Datumswerte;Tab_Input[Seriencode];Tab_Input[Start];Tab_Input[Ende])*Tab_Input[Betrag]*Tab_Input[Betrag]

Wie eingangs erläutert führt die reine LAMBDA-Funktion lediglich zu einem Array aus 1 und 0-Werten. Aus diesem Grunde multiplizieren wir am Ende noch mit dem Betrag (in der Formel oben rot markiert).

d) Ergänzende Hinweise zur Planungsdatei

Das Arbeiten mit Arrays (und intelligenten Tabellen) hat viele Vorteile aber auch einige Einschränkungen, die man kennen sollte. Dabei sind insbesondere die Überlauf-Problematik und die dynamische Größe von Arrays erwähnenswert.

In unserer Bespiel-Datei können Sie ganz einfach durch Selektion der letzten Zelle in der intelligenten Eingabe-Tabelle und drücken der „Tab-Taste“ weitere Zeilen einfügen. Sobald Sie die nötigen Eingaben für die neue Zahlung gemacht haben, passen sich die Berechnungen rechts automatisch an (ohne dass eine Formel geändert werden muss). Es vergrößert sich also das Array aber die Formatierungen werden standardmäßig nicht automatisch übernommen. Aus diesem Grunde empfiehlt es sich, bspw. über bedingte Formatierungen einen ausreichend großen Bereich (nach rechts und nach unten) vorzuformatieren (ist in Datei bereits enthalten).

Des Weiteren sollten auch die Summenformeln (bei uns in Zeilen 10 und 11) so formuliert werden, dass diese auch bei flexiblen Array-Größen korrekte Werte liefern. In der Beispiel-Datei wurde dies mittels einer dynamischen BEREICH.VERSCHIEBEN-Funktion mit enthaltener ANZAHL2-Funktion für das Argument [Höhe] gemacht.

Last but not least: Es empfiehlt sich immer, Fehleingaben durch Anwender entweder abzufangen oder zumindest kenntlich zu machen. In unserem Beispiel ist bspw. das Startdatum in der Eingabetabelle verpflichtend. Wird bspw. in einer Zeile ein Betrag aber kein Startdatum eingetragen, färbt sich die Eingabezelle für das Startdatum automatisch blau (wir haben dazu eine bedingte Formatierung hinterlegt).

Vorteile & Nachteile von LAMBDA-Funktionen

Seit der Einführung von Lambda-Funktionen in Excel sprechen viele Nutzer von einem neuen Zeitalter, manche gar von einer Revolution. JA, damit ist es nun möglich, eigene, komplexe, benutzerdefinierte Funktionen (ohne VBA) zu erstellen, aber jede Medaille hat bekanntlich zwei Seiten.

Die wichtigsten Vor- und Nachteile haben wir deshalb hier nochmal zusammengefasst. Dabei ist das unsere Sichtweise als Unternehmen, welches professionelles Financial Modelling mit betriebswirtschaftlichem Fokus betreibt. Mathematiker oder Programmierer mögen einige Punkte ggf. anders sehen.

Vorteile:

Erstellung eigener benutzerdefinierter Funktionen mit einfacher Syntax möglich
Excel-Dateien bleiben makrofrei (kein VBA) => höhere Sicherheit
Weder VBA- noch JavaScript-Kenntnisse erforderlich, daher können auch Programmier-Laien von Verwendung profitieren
Teilweise Performancegewinne gegenüber älteren (Matrix-)Funktionen, insbesondere bei umfangreichen Excel-Modellen

Nachteile:

Debugging und Testung umständlich
Funktion immer an (eine) Arbeitsmappe gebunden
Auditing von komplexen LAMBDA-Funktionen (z.B. in Finanzmodellen) zeit- und arbeitsaufwendig (insb. wenn Code nicht professionell entwickelt u. kommentiert wurde)
Keine Abwärtskompatibilität zu älteren Excel-Versionen (mind. Excel 2021 od. 365 erforderlich)
Bei Verwendung von Arrays in LAMBDAs werden auch deren Nachteile erkauft (z.B. #ÜBERLAUF-Fehler, Pre-Formatierung erforderlich da Größe der Arrays flexibel etc.)

Fazit

Der Beitrag hat gezeigt, wie leistungsfähig und hilfreich LAMBDA-Funktionen sein können. Zugegeben, die eigene Entwicklung derartiger Funktionen ist ein Thema für fortgeschrittene Excel-Anwender, aber in Zukunft werden immer mehr Funktionen von der Community bereitgestellt werden (z.B. über sog. GitHub Gists z.B. wie bei Havish Madhvapaty: https://gist.github.com/havishmad), die dann schnell (z.B. mit dem AFE) importiert, bei Bedarf angepasst und in eigenen Excel-Anwendungen genutzt werden können.

Wir werden dieses spannende Thema auf jeden Fall weiterverfolgen und dort wo sinnvoll, eigene LAMBDAs entwickeln. Etwas gewöhnungsbedürftig bleibt aber das Arbeiten mit und das Verhalten von dynamischen Überlaufbereichen (= Spill Range) sowie die erforderliche „Vor-Formatierung“ für automatische Größenänderungen der Arrays.

Wie üblich haben wir auch zu diesem Tutorial eine kostenlose Excel-Download-Datei vorbereitet. Diese erleichtert ihnen das Beispiel nachzuvollziehen. Die enthaltene LAMBDA-Funktion kann gerne für eigene Liquiditätsplanungen verwendet werden.