Laufzeiten von Verträgen mit Excel berechnen

Laufzeiten von Verträgen mit Excel berechnen

Wie Sie Laufzeiten von Verträgen, Krediten oder Projekten berechnen
Veröffentlicht am | 
Zuletzt aktualisiert am

In einer Tabelle soll das Ende eines 24 Monate laufenden Vertrags ermittelt werden, dessen Laufzeit in jedem Jahr für eine bestimmte Zeit unterbrochen wird. Die folgende Abbildung zeigt den Aufbau der Tabelle mit den für die Berechnung notwendigen Informationen:

 

Beachten Sie, dass die folgenden Formeln nur korrekte Ergebnisse liefern, wenn der Vertrag an einem Monatsersten beginnt und für volle Monate unterbrochen wird.

Die Berechnung des Vertragsendes ohne Unterbrechungen führen Sie in der Zelle B5 mit der folgenden einfachen Formel durch:

=EDATUM(B1;B2)

Die folgende Abbildung zeigt die Formel in einer Beispieltabelle:

Damit die Formeln besser nachvollziehbar und auch für andere Aufgaben übertragbar sind, wird die Gesamtberechnung in die Zeiträume der Monate bis zur ersten Unterbrechung, die kompletten Jahre und die restlichen Monate im letzten Jahr zerlegt. Dazu legen Sie in der Tabelle die folgenden Zellen an:

Für die Ermittlung der Monate im ersten Jahr tragen Sie die folgende Formel in die Zelle B8 ein:

=MONAT(B3)-MONAT(B1)

Für die Berechnung der kompletten Jahre verwenden Sie in der Zelle B9 die folgende Formel:

=GANZZAHL((B2-(MONAT(B3)-MONAT(B1)))/ABS(MONAT(B4)-MONAT(B3)))

Die Monate im letzten Jahr berechnen Sie in der Zelle B10 mit der Formel:

=B2-(GANZZAHL((B2-(MONAT (B3)-MONAT(B1)))/ABS(MONAT (B4)-MONAT(B3)))*ABS(MONAT (B4)-MONAT(B3)))-(MONAT(B3)-MONAT(B1))

Die Abbildung zeigt diese drei Berechnungen in der Beispieltabelle:

Auf der Grundlage der Zwischenergebnisse können Sie das Vertragsende nun mit der folgenden Formel berechnen:

=DATUM(JAHR(B1)+B9+1;4+B10;1)-1

Selbstverständlich können Sie das Vertragsende auch ganz ohne Hilfszellen mit einer Formel berechnen. Diese wird dann sehr lang und sieht folgendermaßen aus:

=DATUM(JAHR(B1)+(GANZZAHL((B2-(MONAT(B3)-MONAT(B1)))/ ABS(MONAT(B4)-MONAT(B3)))) +1;4+(B2-(GANZZAHL((B2-(MONAT(B3)-MONAT(B1)))/ ABS(MONAT(B4)-MONAT(B3))) *ABS(MONAT(B4)-MONAT(B3)))-(MONAT(B3)-MONAT(B1)));1)-1

Die folgende Abbildung zeigt diese Formel in der Beispieltabelle: