Mathematische Inhalte:

Mit Hilfe der Tabellenkalkulation Excel 97 und der integrierten Scriptsprache VBA ist es relativ einfach ein Programm zur Berechnung von Tilgungsplänen zu erstellen, mit dem man die Annuitätenschuld komfortabel berechnen kann.

2. Schuldtilgung

Wenn ein Darlehen aufgenommen wird, dann wird meist eine Rückzahlung in Annuitäten vereinbart. Das heißt, es müssen vom Kreditnehmer Zahlungen in gleicher Höhe und in gleichen Zeitabständen getätigt werden, damit am Ende der Laufzeit die Kreditschuld völlig getilgt ist. Diese Zahlungen werden als Annuitäten bezeichnet. Die Annuitäten bestehen aus einem Zinsanteil und einem Tilgungsanteil, jener Betrag, um den die Restschuld bei der Zahlung sinkt. In einem Tilgungsplan werden diese Werte zu jedem Zahlungszeitpunkt dargestellt.

Um einen Tilgungsplan berechnen zu können, sind die leistungsstarken finanzmathematischen Funktionen von Excel besonders hilfreich. Diese können entweder über den Funktionsassistenten abgerufen oder direkt eingegeben werden:

Beispiel Annuitätenschuld

Eine Schuld von S 100.000,- soll zu i = 5 % in 10 Jahren durch gleich hohe Annuitäten zurückgezahlt werden. Berechnen Sie die Annuität und erstellen Sie den kompletten Tilgungsplan!

Die Annuitätenschuld hat den Nachteil, dass sich keine "glatten" Annuitäten ergeben. In der Praxis gibt man lieber gerundete Annuitäten vor und nimmt dafür nichtganzzahlige Laufzeiten in Kauf. Am Schluß bleibt ein Restbetrag, der meistens ein Jahr nach der letzten vollen Annuität getilgt wird.

Es bietet sich die Entwicklung eines VBA-Programmes an, damit:

• bei unterschiedlichen Laufzeiten nicht jedesmal die Formeln auf die neue Länge des Tilgungsplanes nachgebessert werden müssen.
• die Eingabe komfortabler wird.

Da sich einige Leser vielleicht noch wenig mit VBA-Programmierung befasst haben, werde ich versuchen, das Projekt möglichst ausführlich zu beschreiben. Schauen wir uns das Projekt zuerst einmal im Überblick an und arbeiten dann jeden Projektbestandteil im Detail durch.

a) Tabelle (Tilgungsplan)

Der erste Schritt ist die Erstellung eines Rohgerüstes. Dazu müssen Sie nur die Leitwörter und die Überschriften genau wie oben gezeigt in einer neuen Excel-Tabelle eingeben, sämtliche Formeln werden später durch das VBA-Programm eingetragen. Ich empfehle der Tabelle den Namen "Tilgungsplan" zu geben (Format/Blatt/Umbenennen).

Die Befehlsschaltfläche "Neu" kann mit Hilfe der Steuerelement Toolbox (Ansicht/ Symbolleisten/Steuerelement Toolbox) gezeichnet werden. Der Name ist in der Eigenschaftsleiste (in der Steuerelement Toolbox) auf "cmdNeu" zu ändern, die Beschriftung (= Caption) sinnvollerweise auf "Neu". Der Code kann nach einem Doppelklick auf "cmdNeu" eingegeben werden, wobei die erste und letzte Zeile automatisch von Excel hinzugefügt werden.

Der Code für "cmdNeu" lautet:

Private Sub cmdNeu_Click()
 
 

frmST.Show

End Sub

b) Modul (Schuldtilgung)

Wir werden später eine Funktion benötigen, die uns bei der Übergabe es Kapitals, der Laufzeit und des Zinssatzes die Annuität berechnet. Diese Funktion ist jedoch noch nicht in Excel implementiert und so müssen wir diese selbst bereitstellen.

Damit die Funktion überall zugänglich ist und leicht in anderen Programmen verwendet werden kann, sollte sie in einem eigenen Modul definiert werden. Ausgangspunkt sämtlicher Programmierarbeiten ist der Microsoft Visual Basic Editor (Extras/Makro/Visual Basic Editor). Mit Einfügen/Modul wird ein neues Modul eingefügt. In dieses Modul können wir folgenden Code eintippen:
 

Const Zformat = "#,##0.00"     Function calcA(K, i, n) 'Algorithmus zum Berechnen der Annuität     Dim A If K <> "" And i <> "" And n <> "" Then K = CDbl(K) n = CDbl(n) i = i / 100 calcA = Format(Pmt(i, n, -K, 0), ZFormat) Else calcA = "" End If     End Function

Die Funktionen Pmt ist äquivalent zur Funktion RMZ, die in einer Tabelle mit Hilfe des Funktionsassistenten aufgerufen werden kann.

c) Form (frmST)

Der nächste Schritt ist die Erstellung eines Formulars (siehe Skizze), in dem der Benuzter später die benötigten Daten eingeben kann. Eine neue UserForm kann mit der Befehlsfolge Einfügen/UserForm dem Projekt zugefügt werden. Der Name der Form sollte im Eigenschaftsfenster (Ansicht/Eigenschaftsfenster) auf "frmST" geändert werden. Excel denkt mit und blendet die Werkzeugsammlung (Ansicht/Werkzeugsammlung) automatisch ein. Sämtliche Objekte der Form können leicht mit Hilfe der Werkzeugleiste gezeichnet werden. Im Eigenschaftsfenster (Ansicht/ Eigenschaftsfenster) muss der Name (= Name) und die Beschriftung (= Caption) der Textfelder und der Befehlsschaltflächen geändert werden. Bei den Bezeichnungsfeldern genügt es, wenn Sie nur die Beschriftung ändern, denn wir werden im Code nicht darauf zugreifen.
 

Objektname	Objektart	Objektbeschreibung
txtK	Textfeld	Eingabe der Anfangsschuld
txtI	Textfeld	Eingabe des Jahreszinsatzes in %
txtN	Textfeld	Textfeld zur Eingabe der Laufzeit in Jahren
txtA	Textfeld	Textfeld zur Ausgabe der Annuität
cmdOK	Befehlsschaltfläche	Bestätigung der Eingaben und zum Starten der Berechnungen
cmdCancel	Befehlsschaltfläche	Button zum Abbrechen der Eingabe
cmdLöschen	Befehlsschaltfläche	Button zum Löschen der getätigten Eingaben
-	Bezeichnungsfelder	zum Beschriften der verschiedenen Eingabefelder

2. Programmcode

Nun haben wir ein lauffähiges Eingabeformular erstellt, aber wir müssen ihm noch den Geist einhauchen, damit auch Aktionen beim Klicken auf die Schaltflächen bzw. bei Eingabe von Daten ausgeführt werden.

Den Code für die einzelnen Objekte geben Sie am besten ein, indem sie auf das Objekt, dem die Ereignisprozedur zugewiesen werden soll, doppelklicken. Im folgenden Eingabefenster können Sie das passende Ereignis im Dropdown-Menü für Ereignisse auswählen, also in unserem Fall bei den Textfeldern "Exit" und bei den Befehlsschaltflächen "Click" und dann den Code eingeben. Die erste und die letzte Zeile werden wieder automatisch von Excel hinzugefügt.

Der Code lautet:
 
 

Private Sub txtA_Exit(ByVal Cancel As MSForms.ReturnBoolean)     txtA.Text = calcA(txtK.Text, txtI.Text, txtN.Text)     End Sub     Private Sub txtI_Exit(ByVal Cancel As MSForms.ReturnBoolean) txtA.Text = calcA(txtK.Text, txtI.Text, txtN.Text)     End Sub     Private Sub txtK_Exit(ByVal Cancel As MSForms.ReturnBoolean)     txtA.Text = calcA(txtK.Text, txtI.Text, txtN.Text)     End Sub     Private Sub txtN_Exit(ByVal Cancel As MSForms.ReturnBoolean)     txtA.Text = calcA(txtK.Text, txtI.Text, txtN.Text) End Sub         Private Sub cmdOK_Click()     'Bei einem objektorientierten Fehler versuchen fortzufahren On Error Resume Next     'Neue Variablen anlegen und nötige Grundeinstellungen fixieren Dim Zeile     Zeile = 9 n = CDbl(txtN.Text)     'der Objektvariablen bl das Objekt Worksheets("Tilgungsplan") zuweisen Set bl = Worksheets("Tilgungsplan")     'Tilgungsplantabelle aktivieren bl.Activate     'eventuell vorhandenen alten Tilgungsplan löschen bl.Rows("9:16000").ClearContents 'Kapital eintragen bl.Cells(3, 3).Value = CDbl(txtK.Text) 'Zinssatz im Prozentformat eintragen bl.Cells(4, 3).Value = CDbl(txtI.Text / 100)     'Formeln für die Berechnung der Annuität eintragen bl.Cells(5, 3).Value = CDbl(txtN.Text) bl.Cells(6, 3).FormulaR1C1 = "=PMT(R[-2]C,R[-1]C,-R[-3]C)"     'Jahr 0 und Restschuld=Anfangsschuld im Jahr 0 eintragen bl.Cells(Zeile, 1).Value = "0" bl.Cells(Zeile, 6).Formula = "=$C$3" bl.Cells(Zeile, 2).Formula = "=$C$4" 'Zeile um 1 erhöhen, damit die Eintragung in der nächsten Zeile erfolgt Zeile = Zeile + 1     'Schleife für die restlichen Jahre Do 'Formeln für eine Zeile des Tilgungsplanes eintragen bl.Cells(Zeile, 1).Value = bl.Cells(Zeile - 1, 1).Value + 1 'h bl.Cells(Zeile, 2).FormulaR1C1 = "=R[-1]C" 'i bl.Cells(Zeile, 3).FormulaR1C1 = "=R[-1]C[+3]*RC[-1]" 'Zinsen bl.Cells(Zeile, 4).FormulaR1C1 = "=RC[+1]-RC[-1]" 'Tilgung bl.Cells(Zeile, 5).Formula = "=$C$6" 'Annuität bl.Cells(Zeile, 6).FormulaR1C1 = "=R[-1]C-RC[-2]" 'Restschuld  'Zeile um 1 erhöhen Zeile = Zeile + 1     Loop Until Zeile - 1 = n + 9 'Solange wiederholen bis alle Jahre eingetragen wurden     'es wurde alles erfolgreich berechnet, blende Dialogfeld aus frmST.Hide     End Sub     Private Sub BAbbrechen_Click()     Sheets("Tilgungsplan").Activate frmST.Hide     End Sub         Private Sub cmdLöschen_Click()     txtK.Text = "" txtA.Text = "" txtI.Text = "" txtN.Text = "" txtK.SetFocus     End Sub

Beim Verlassen der Textfelder soll die eingegebene Zahl auf Gültigkeit überprüft und die Annuität berechnet werden. Dies geschieht mit der selbstdefinierten Funktion calcA. Da bei der ereignisgesteuerten Programmierung die Eingabereihenfolge prinzipiell egal ist, muss bei jedem Verlassen eines Textfeldes versucht werden die Annuität zu berechnen.

Bei einem Klick auf die Schaltfläche OK werden, die in der UserForm eingegebenen Werte und die benötigten Formeln in die Tabelle "Tilgungsplan" eingetragen. Formeln haben den Vorteil, dass einzelne Zeilen des Tilgungsplanes im Nachhinein verändert werden können und der Tilgungsplan dann mit den neuen Parametern automatisch durchgerechnet wird.
Die Werte aus der Form sollten nicht direkt übergeben werden, weil Rundungsfehler entstehen könnten.
Mit Hilfe der Z1S1-Bezugsart können die relativen Zellbezüge leichter realisiert werden. Dabei bedeutet R[-2]C zB., dass auf den Wert 2 Zeilen über der angesprochen Zelle und in der gleichen Spalte verwiesen wird.
Bei einem Klick auf die Schaltfläche Abbrechen soll der aktuelle Tilgungsplan angezeigt werden und das Eingabeformular ausgeblendet werden.
Wenn auf die Schaltfläche Löschen geklickt wird, dann werden die Inhalte aller Textfelder gelöscht.

d) Arbeitsmappenobjekt

Damit das Eingabeformular beim Starten der Excel-Datei sofort angezeigt wird, ist es noch nötig eine Codezeile im Workbook_Open Ereignis einzugeben. Das Objekt Workbook finden Sie in der deutschen Version von Microsoft Excel unter dem Namen "DieseArbeitsmappe" der Projektleiste unter den Microsoft Excel Objekten.

 
 
 
 
 

Private Sub Workbook_Open() frmST.Show End Sub

Das Workbook_Open-Ereignis wird dafür verwendet die Form (frmST) einzublenden.

4. Testen des Projektes

Nachdem Sie nun eine ganze Menge Arbeit geleistet haben, möchten Sie jetzt wahrscheinlich testen, ob auch alles so funktioniert, wie wir uns das vorgestellt haben. Wählen Sie dazu ganz einfach im Projektfenster das Formular "frmST" mit einem Doppelklick aus und drücken Sie dann die Taste F5 oder klicken Sie in der Symbolleiste auf das Play-Symbol.

Wenn Sie alle Schritte genau ausgeführt und keine Fehler gemacht haben, dann sollte sich jetzt das Eingabeformular öffnen. Sollte dies so sein, dann dürfen Sie sich unter die Glücklichen einreihen, wenn nicht, dann ärgern Sie sich bitte nicht. Im Normalfall bringt der Excel-Debugger nämlich eine Fehlermeldung und springt automatisch zur falschen Codezeile oder in die Nähe der falschen Stelle. So sollte es Ihnen möglich sein, alle Ihre Fehler zu verbessern. Jetzt können Sie in das Eingabeformular die benötigten Daten eingeben. Nehmen wir folgende Zahlen an:

Anfangsschuld K: 100000

Zinssatz: 5

Laufzeit: 10

Sobald Sie nun das Feld für die Laufzeit verlassen, schreibt der Computer in das Textfeld Annuität A den Wert 12.950,46.

Bei einem Klick auf die Schaltfläche OK sollte Excel automatisch die Tabelle Tilgungsplan aktivieren und den kompletten Tilgungsplan anzeigen. Die Zahlenformate und die Formatierung werden noch nicht stimmen, Sie können die Tabelle jedoch ganz einfach wie jede gewöhnliche Excel-Tabelle formatieren. Falls die Zahlen nicht stimmen bzw. nur wirres Zeug aufscheint, haben Sie höchstwahrscheinlich einen Fehler im Programmcode gemacht.

5. Erweiterungsmöglichkeiten

Man sieht, wie schnell man in Visual Basic hilfreiche Programme schreiben kann. Dieses Programm habe ich aus didaktischen Gründen stark vereinfacht, so habe ich zum Beispiel bewusst auf an und für sich notwendige Fehlerbehandlungsroutinen, Formatierungen bzw. auf erweiterte Funktionalität verzichtet. In der Vollversion dieses Programmes kann auch die Prozentschuld komfortabel berechnet werden und die Interaktion mit dem Benutzer ist verbessert (Fehlerbehandlungsroutinen). Um diese Funktionen zu implementieren muss man ganz einfach... aber das würde den Rahmen dieses Artikels sprengen. Sie können sich jedoch sowohl diese Version als auch die Vollversion von meiner Homepage unter http://schris.virtualave.net herunterladen.

Nachtrag von Markus Paul, BHAK Schwaz:

Ich darf mich glücklich schätzen, als Mathematiklehrer und Klassenvorstand Christian Sporer begleiten zu dürfen. Ich scheue mich davor zu behaupten, ich würde Christian "unterrichten", denn Christian ist ein autodidaktisches Phänomen: Die gesamte Visual-Basic-Programmierung hat er sich allein und selbständig aus dicken Büchern angeeignet! Er ist ein Paradebeispiel, welch unerhörtes kreatives Potential bei Schülern durch die Beschäftigung mit Computern freigesetzt werden kann.

Christian besucht derzeit den Lehrgang "Jungmedia", eine vom Land Tirol gesponserte Multimedia-Ausbildung für begabte Jugendliche. Für Lehrer bietet Christian Ende Juni dieses Jahres ein Seminar zu "Flash-Technologie zur Homepage-Gestaltung" in Innsbruck (Medienzentrum Hofburg) an. Wenn Sie Interesse haben, an diesem Seminar teilzunehmen, können Sie mit Christian Sporer Kontakt aufnehmen:

m.sporer@tirol.com