Automatisierte Zinsberechung

Computer: Software & Programmierung Office Software
1

Hallo Excelprofis,

ich soll für Kollegen eine Tabelle zu Berechnung von Zinsen bei ratenweiser Rückzahlung eines Betrages mit unregelmäßiger Ratenzahlung anfertigen.
Da sich meine Kollegen nicht so gut mit Excel auskennen, ist meine Grundidee, dass lediglich die einzelnen erfolgten Zahlungen in einem Tabellenblatt eingegeben werden und diese Werte dann in eine Berechnungstabelle übernommen werden.
Die Eingabe der Zahlungen gestaltet sich z.B. wie folgt:

 A B
1 Zahlungsdatum | Zahlungsbetrag
 --------------------------------
2 01.05.2005 | 10,00 €
3 01.06.2005 | 20,00 €
4 23.08.2005 | 15,00 €
5 02.09.2005 | 25,00 €
6 15.09.2005 | 20,00 €
u.s.w.

Die Verzinsung erfolgt nach folgenden Prinzipien (keine Abweichung mgl., da lt. gesetzlicher Regelung):

  1. der jeweils zum 1. des Monats bestehende Restbetrag + evtl. Zinsen aus Vormonaten wird für einen Zeitraum von einem Monat (30 Tage) mit 6 % verzinst
    (d.h. nach dem 1. eingehende Zahlungen mindern die zu verzinsende Restforderung erst im Folgemonat und in Monaten ohne Zahlung wird der volle Restbetrag verzinst)

  2. der für einen Monat anfallende Zinsbetrag wird auf die Restforderung aufgeschlagen.

Aus o.g. Grunddaten soll mit Hilfe von Formeln eine Ausgabetabelle nach folgendem Schema erzeugt werden:

 A B C D E F
1 Kapital\*| Gez. Betrag | Eingang | Verbleibend\*| Zinszeitraum\*| Zinsbetrag\*
 --------------------------------------------------------------------------
2 400,00 €| 10,00 € | 01.05.05| 390,00 € | 05/2005 | 1,95 €
4 391,95 €| 20,00 € | 01.06.05| 371,95 € | 06/2005 | 1,86 €
5 373,81 €| | | 373,81 € | 07/2005 | 1,86 €
6 375,68 €| | | 375,68 € | 08/2005 | 1,88 €
7 377,56 €| 15,00 € | 23.08.05| 362,56 € | 09/2005 | 
8 362,56 €| 25,00 € | 02.09.05| 337,56 € | 09/2005 | 1,69 €
9 339,25 €| 20,00 € | 15.09.05| 319,25 € | 10/2005 | 1,60 €
u.s.w.

* berechnete Werte

Mein großes Problem bei der Anfertigung ist die Übertragung der Zahlungsdaten per Formel in die Berechungstabelle und hier insbesondere die Auffüllung mit den Leerzeilen für Zinsmonate, in denen keine Zahlung erfolgt ist (sh. Zeilen 5 u. 6). In der Spalte Zinszeitraum muss jeder Monat im Rückzahlungszeitraum mindestens einmal aufgeführt sein.
Außerdem dürfen bei mehrfachen Zahlungseingängen im Monat die Zinsen nur nach dem jeweils letzten Zahlungseingang berechnet werden (sh. Zeilen 7 bis 8).

Darüber hinaus benötige ich eine Formel, mit der der Zinszeitraum (Spalte E) abhängig vom Zahlungsdatum ausgegeben wird (alles nach dem ersten in den Folgemonat).

Hat jemand eine Idee, wie sich die Zinsberechnung nach obigen Muster am besten realisieren lässt??
Vielen Dank für Eure Antworten

Th.!

2

keine Lösung aber rechtliches
Hi !

Die Verzinsung erfolgt nach folgenden Prinzipien (keine
Abweichung mgl., da lt. gesetzlicher Regelung):
2. der für einen Monat anfallende Zinsbetrag wird auf die
Restforderung aufgeschlagen.

Ich weiß ja nicht, wo du arbeitest. Daher weiß ich auch nicht, welche gesetzliche Regelung du meinst. Mir ist jedoch der § 289 BGB bekannt. Und nach diesem ist die Verzinsung von Zinsen verboten. Nur so als Hinweis.

http://bundesrecht.juris.de/bundesrecht/bgb/index.html

BARUL76

3

Ich weiß ja nicht, wo du arbeitest.

öffentliche Verwaltung

Daher weiß ich auch nicht,
welche gesetzliche Regelung du meinst.

(unsere Landeshaushaltsordnung)

Mir ist jedoch der § 289 BGB bekannt. Und nach diesem ist die Verzinsung von Zinsen
verboten. Nur so als Hinweis.

hier geht’s nach öffentlichem Recht, nicht nach BGB

nach den entsprechenden gesetzlichen Vorschriften werden die Zinsen eines Monats jeweils zum Monatsende fällig, so dass Sie im Folgemonat als weitere offene Forderung wiederum verzinst werden
Die Berechnung ist in gleicher Form in einem bundeseinheitlichen Programm enthalten, nur mit regelmäßiger Ratenzahlung; also wohl richtig
Aber trotzdem danke für das Interesse und den Hinweis.

4

Hallo Th.,
mit "einfachen Excel-Formel kann man es nicht realisieren, wenn die Einzahlungen in eine Tabelle geschrieben werden und dann daraus eine Tilgungstabelle berechnet werden soll.

Es ist kein Problem, in einer Tabelle Eingaben nur in bestimmten Zellen zuzulassen indem man die Eingabe zellen entsprechend formatiert und dastabellenblatt schützt. Mein Vorschlag:
Erstelle nur eine Tabelle, dann kann das von Dir Gewünschte umgesetzt werden. Der Einfachheit halber sollte der 1. eines Monats immer eingegeben werden, da die Formeln sonst zu kompliziert werden um ein Kriterium für die Zinsberechnung zu ermitteln. Das Ganze sieht dann etwa so aus:

Tabellenblattname: Tabelle1

 A B C D E F 
 1 Startkapital: 400 Zinsatz: 0,06 per annum 
 2 Kapital Gez. Betrag Datum Verbleibend Zinszeitraum Zinsbetrag 
 3 400 10 01.05.05 390 05/2005 1,95 
 4 391,95 20 01.06.05 371,95 06/2005 1,86 
 5 373,81 01.07.05 373,81 07/2005 1,87 
 6 375,68 01.08.05 375,68 08/2005 1,88 
 7 377,56 15 23.08.05 362,56 09/2005 
 8 362,56 25 01.09.05 337,56 09/2005 1,69 
 9 339,25 20 15.09.05 319,25 10/2005 
10 319,25 01.10.05 319,25 10/2005 1,6 
11 320,85 25 01.11.05 295,85 11/2005 1,48 
12 297,33 25 01.12.05 272,33 12/2005 1,36 
13 273,69 30 15.12.05 243,69 01/2006 
14 243,69 01.01.06 243,69 01/2006 1,22 

Benutzte Formeln:
A3: =B1
A4: =WENN(ISTLEER(C4);"";D3+WENN(F3="";0;F3))
D3: =WENN(ISTLEER(C3);"";A3-B3)
D4: =WENN(ISTLEER(C4);"";A4-B4)
E3: =WENN(ISTLEER(C3);"";WENN(TAG(C3)\>1;WENN(MONAT(C3)=12;"01/"&TEXT(JAHR(C3)+1;"0000"); TEXT(MONAT(C3)+1;"00")&"/"&TEXT(JAHR(C3);"0000"));TEXT(C3;"MM/JJJJ")))
E4: =WENN(ISTLEER(C4);"";WENN(TAG(C4)\>1;WENN(MONAT(C4)=12;"01/"&TEXT(JAHR(C4)+1;"0000"); TEXT(MONAT(C4)+1;"00")&"/"&TEXT(JAHR(C4);"0000"));TEXT(C4;"MM/JJJJ")))
F3: =WENN(TAG(C3)=1;RUNDEN(D3\*$E$1/12;2);"")
F4: =WENN(TAG(C4)=1;RUNDEN(D4\*$E$1/12;2);"")

Ab Zeile 4 können die Formeln nach unten kopiert werden.
Ich schicke Dir per e-mail eine kleine Beispiel-Datei.

Gruß
Franz

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

5

Danke
Vielen Dank für den funktionierenden Lösungsvorschlag