Zusammenfassung
Programmieren Sie eine Excel-Anwendung, die Daten in Access speichert und ausliest.
Techniken
Excel, VBA
Voraussetzungen
Access 2000 und höher
Beispieldatenbank
Darlehenskalkulator.mdb, Darlehenskalkulator.xls
André Minhorst, Duisburg
Wer mal eine größere Sache wie ein Haus oder ähnliches kauft, muss ordentlich rechnen – zumindest, wenn er nicht gerade im Lotto gewonnen hat. Dabei geht es darum, das Darlehen, Zinssatz, Zahlungszeitraum, Sondertilgungen und mehr unter einen Hut zu bringen – eigentlich kein Fall für Access, sondern für Excel. Dennoch kommt hier aus Access zum Zuge – wie genau, erfahren Sie in diesem Beitrag.
Die in diesem Beitrag vorgestellte Lösung berechnet Annuitätendarlehen. Diese Art des Darlehens kommt zum Beispiel oft bei der Immobilienfinanzierung vor.
Die wichtigste Eigenschaft einer solchen Finanzierung ist eine konstante Annuitätenrate, die sich aus einem Zins- und einem Tilgungsanteil zusammensetzt, wobei der Zinsanteil durch das schrumpfende Restdarlehen ab- und der Tilgungsanteil zunimmt.
Formeln
Für die Umsetzung einer automatischen Berechnung eines solchen Kredits sind einige Formeln notwendig.
Laufzeit
Die erste Formel bestimmt die Laufzeit eines Kredits anhand des Zinssatzes und der Tilgung:
Dabei ist n die Laufzeit in Jahren, i der Zinssatz und t der Tilgungssatz.
Mit einem Zinssatz von 4 % und einer Tilgung von 1 % liefert diese Formel etwa eine Laufzeit von etwas mehr als 41 Jahren.
Gegebenenfalls erfolgt die Zahlung nicht einmal, sondern mehrmals im Jahr – in dem Fall gilt die folgende Formel für die Berechnung der Laufzeit:
Dabei entspricht m der Anzahl der Zahlungen pro Jahr.
Bild 1: Darlehensberechnung mit Excel
Annuität
Mit der Laufzeit lässt sich die Annuität, also der jährlich zu zahlende Betrag, berechnen:
Dabei ist R die Annuität, S0 die Kreditsumme, i der Zinssatz und n die Laufzeit.
Wenn man die aus der obigen Formel berechnete Laufzeit einsetzt, lässt sich die Formel vereinfachen:
Für die monatliche Annuität teilt man das Ergebnis noch durch die Anzahl der Monate.
Umsetzung in Excel
Excel ist prädestiniert für die Berechnung von Annuitätendarlehen. Dazu benötigt man nur die Angabe von drei Werten (Darlehen, Zinssatz, Tilgung) und eine Reihe Formeln. Bild 1 zeigt die Berechnung in Excel, die Sie auch in der Beispieldatei finden. Die oberen drei Zeilen enthalten die für die Berechnung notwendigen Informationen; wenn Sie diese ändern, passt Excel die in der darunter befindlichen Tabelle enthaltenen Werte ebenfalls an.
Die Tabelle mit dem Verlauf des Annuitätendarlehens legen Sie folgendermaßen an: Die erste Spalte enthält die Monate. Dabei ist der nullte Monat in der ersten Zeile derjenige, in dem die Auszahlung erfolgt. Die Rückzahlung beginnt nach dem ersten Monat. Tragen Sie in die Spalte mit den Monaten den Wert 1 ein. Nun könnten Sie die übrigen Werte händisch eintragen, eine hilfreiche Funktion von Excel nimmt Ihnen diese Arbeit jedoch ab: Tragen Sie für den zweiten Monat eine Formel ein, die den Inhalt der darüber liegenden Zelle um eins erhöht – hier etwa =A6+1.
Diesen Vorgang sollen Sie natürlich nicht endlos wiederholen. Stattdessen markieren Sie die Zelle A7 und ziehen das Viereck in der linken unteren Ecke der Zelle soweit nach unten, wie Sie Monate benötigen (siehe Bild 2).
Wenn Sie noch nicht wissen, wie viele das sind, setzen Sie ruhig ab – Sie können den Inhalt der untersten kopierten Zelle in einem weiteren Schritt weiter „nach unten ziehen“. Der Clou ist, das Excel nicht nur den Zelleninhalt kopiert, sondern auch die relativen Bezüge beibehält – jede der neu gefüllten Zellen addiert den Wert 1 zum Wert der darüber liegenden Zelle.
Bild 2: Kopieren von Zellen nach unten
Die zweite und dritte Spalte sollen die im Kopf des Tabellenblatts enthaltenen Werte für Zinssatz und Tilgung enthalten. Dementsprechend legen Sie für die Zelle B7 zunächst den Inhalt =$B$2 und für C7 den Inhalt =$B$3 fest. Was sollen nun die Dollar-Zeichen ($) in den Formeln Ganz einfach: Sie erzeugen absolute statt relativer Bezüge zu den Zellen, damit beim Kopieren dieser Zellen nach unten jeweils der Wert der Zellen B2 und B3 erhalten bleibt. Wenn Sie die Dollar-Zeichen weglassen und die Felder wie bereits bei den Monaten nach unten kopieren, würde beispielsweise die Zelle B8 nicht mehr den Inhalt von B2, sondern den von B3 anzeigen.
Aber ist dies überhaupt so sinnvoll Wenn der Benutzer etwa für den Zeitraum ab dem zehnten Jahr einen anderen Zinssatz eintragen möchte, müsste er dies für jede Zeile einzeln tun. Wenn Sie erreichen möchten, dass die änderung in dieser Zelle in alle darunter befindlichen Zeilen übertragen wird, setzen Sie doch wieder eine Formel mit Bezug zur darüber liegenden Zelle ein. Die Zelle B7 enthielte dann nach wie vor den Wert =$B$2, die Zelle B8 den Wert =B7, die Zelle B9 den Wert =B8 und so weiter.
Die Spalte Sondertilgungen bleibt zunächst leer, hier kann der Benutzer später nach Belieben Sondertilgungen eintragen.
Die Annuität berechnet man mit der Formel =$B$1*(B7+C7)/12. Eigentlich könnte man hier auch =$B$1*($B$2+$B$3)/12 verwenden. Normalerweise ist aber beispielsweise der Zinssatz nur auf eine Laufzeit von 5, 10 oder 15 Jahren festgelegt. Durch die hier verwendete Formel können Sie nach Ablauf dieser Zeit einen neuen Zinssatz festlegen, die Annuität wird entsprechend angepasst. Auch diese Formel tragen Sie in der ersten Zeile in der Zelle E7 ein und kopieren diese in die darunter liegenden Zellen.
Der Zins wird ganz einfach aus dem Produkt des Restdarlehens und des Zinssatzes ermittelt und lautet für die erste Zeile =H6*B7/12. Auch den Inhalt dieses Feldes kopieren Sie nach unten.
Da die Annuität über die gesamte Laufzeit konstant bleiben soll, der Zins sich aber gemeinsam mit dem Restdarlehen vermindert, wächst die Tilgung. Die Formel für die entsprechende Spalte lautet ganz einfach =E7-F7.
Bleibt noch das Restdarlehen: Dabei handelt es sich um die Differenz des Restdarlehens des vorherigen Monats und der Summe der Tilgungen, also der Tilgung und der Sondertilgung des aktuellen Monats – für die erste Zahlungsperiode also etwa =H6-G7-D7.
Mit dem Kopieren aller Zeilen nach unten ist die erste Fassung dieser Berechnung fertig. Erste Fassung deshalb, weil man der Tabelle doch noch einiges an Komfort hinzufügen könnte – beispielsweise sollte diese möglichst automatisch die benötigte Anzahl Zeilen anzeigen. Bevor Sie solche kosmetischen Veränderungen vornehmen, bauen wie die Tabelle jedoch zunächst in eine Access-Anwendung ein.
Damit Sie später per VBA auf das hier angelegte Tabellenblatt zugreifen können, vergeben Sie dafür den Namen Darlehenskalkulator. Am einfachsten geht dies, wenn Sie einfach doppelt auf den Reiter mit der Beschriftung Tabelle 1 im unteren Bereich des Excel-Fensters klicken und diese Bezeichnung anpassen.
Fixieren für bessere übersicht
Die Tabelle kann mitunter einige hundert Zeilen enthalten. Beim Herunterscrollen nervt es dann unter Umständen, wenn man die Spaltenüberschriften und die übrigen Darlehensinformationen nicht mehr lesen kann. Sie können allerdings einen bestimmten Bereich der Tabelle fixieren. Dazu markieren Sie einfach die Zeile, oberhalb derer alle Informationen beim Scrollen stehen bleiben sollen und wählen den Menübefehl Fenster/Fenster fixieren aus.
Da Excel genauso wie Access VBA unterstützt, könnte man die notwendigen Erweiterungen zum Steigern des Komforts auch komplett in Excel vornehmen. Access bietet jedoch eine sehr angenehme Eigenschaft: die Möglichkeit, Daten in relationalen Tabellen zu speichern. Wenn Sie mit der aktuellen Fassung der Excel-Anwendung zum Berechnen von Finanzierungen herumspielen und diverse Varianten durchprobieren, löschen Sie entweder jeweils die zuvor erstellte Berechnung oder Sie kopieren das Arbeitsblatt und erzeugen so verschiedene Berechnungen.
Bild 3: Ein neues Steuerelement im Excel-Tabellenblatt und seine Eigenschaften
übersichtlich ist das alles allerdings nicht. Immerhin ließe sich auch mit Excel eine übersichtsseite bauen, mit der man auf die unterschiedlichen Varianten verweisen kann, aber dazu bräuchten Sie ja kein Access.
Bild 4: Die Toolbox mit der Schaltfläche zum Aktivieren und Deaktivieren des Entwurfsmodus
Die nachfolgend vorgestellte Lösung soll nun so aussehen: Access arbeitet lediglich im Hintergrund und speichert die Daten der unterschiedlichen Berechnungen.
Excel bietet einige Steuerelemente, mit denen sich die aktuelle Berechnung speichern oder eine alte Berechnung wiederherstellen lässt. Damit greifen Sie also vom Excel-Tabellenblatt direkt auf die Datenbank zu.
Um die Anwendung möglichst einfach zu halten, sollen nur zwei Steuerelemente zum Einsatz kommen:
Die notwendigen Steuerelemente sind schnell angelegt: Dazu aktivieren Sie einfach die Symbolleiste Steuerelement-Toolbox (Menüeintrag Ansicht ( Symbolleisten ( Steuerelement-Toolbox), klicken auf die gewünschten Steuerelemente und ziehen Rahmen in der passenden Größe auf (siehe Bild 4).
Entwurf ein/aus
Die Steuerelement-Toolbox unterscheidet sich im übrigen von der von Access bekannten Toolbox durch eine Schaltfläche zum An- und Ausschalten der Entwurfsansicht. Um etwa das Beim Klicken-Ereignis einer Schaltfläche per Mausklick auslösen zu können, müssen Sie zunächst den Entwurfsmodus beenden.
Eigenschaftsfenster
Das ändern wichtiger Eigenschaften wie dem Namen oder der Bezeichnung nehmen Sie im Eigenschaftsfenster vor, dass Sie im Kontextmenü des jeweiligen Steuerelements über den Eintrag Eigenschaften anzeigen (siehe Bild 3).
Excel ist nicht standardmäßig für den Zugriff auf Access-Datenbanken vorbereitet, daher müssen Sie noch den fehlenden Verweis einstellen. Dazu wechseln Sie in den VBA-Editor: ändern Sie zunächst den Namen der neuen Schaltfläche in cmdSpeichern und klicken Sie dann doppelt auf diese, dann zeigt der VBA-Editor direkt die beim Klicken auf diese Schaltfläche ausgelöste Routine an.
Dort öffnen Sie über den von der VBA-Entwicklungsumgebung gewohnten Menüeintrag Extras/Verweise den entsprechenden Dialog und fügen den Verweis auf die Bibliothek Microsoft DAO 3.6 Object Library hinzu (siehe Bild 5).
Bild 5: Hinzufügen eines Verweises auf die DAO-Bibliothek zum VBA-Projekt der Excel-Anwendung
Speichern der Darlehen-Basisdaten
Die Basisdaten eines Darlehens befinden sich im oberen Bereich des Excel-Tabellenblatts. Per Mausklick auf die Schaltfläche Speichern sollen die passenden Daten in einer Tabelle namens tblDarlehen gespeichert werden. Diese Tabelle ist wie in Bild 6 aufgebaut.
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
den kompletten Artikel im PDF-Format mit Beispieldatenbank
diesen und alle anderen Artikel mit dem Jahresabo