Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Finanzmathematik

Beitrag: Ein- Auszahlungsplan bei wechselnden Zinssätzen per Stichtag

Aufgabe
Die Summe von Einzahlungen und Auszahlungen soll zu einem bestimmten Stichtag berechnet werden - dabei kann der Zinssatz variieren.

       A               B               C               D               E               F               G               H       
1 Datum  Ein/Ausz.  Verr.  Summe  Zinsen  Zinssatz  Abrechnung zum  Hilfsspalte  
2 07.01.2009  300,00    300,00  2,83  10,00  31.12.2009  10  
3 10.02.2009  100,00    400,00  1,56      10  
4 24.02.2009  500,00    900,00  8,75    Zahlungen gesamt  10  
5 31.03.2009    v  913,14  17,31  7,50  150,00  7,5  
6 30.06.2009    v  930,45  1,74      7,5  
7 09.07.2009  250,00    1.180,45  1,23    Zinsen gesamt  7,5  
8 14.07.2009  -750,00    430,45  4,66  5,00  43,07  5  
9 30.09.2009  100,00  v  538,09  3,96      5  
10 22.11.2009  -350,00    188,09  1,02      5  
11     v  193,07        5  
12               5  
13               5 



Lösung
Spalten A, B, C, F und G2 sind Eingabezellen.

in D2 steht:
=WENN(ZEILE(A1)>ANZAHL(A:A;G$2);"";SUMME(D1;B2;WENN(C2="v";INDIREKT("E"&VERWEIS(2;1/(C$1:C1<>"");ZEILE(A$1:A$999))&":E"&ZEILE(A1));0)))
runterkopieren

in E2 steht:
=WENN(ZEILE(A2)>ANZAHL(A:A;G$2);"";D2*H2*(MIN(A3:A$999;G$2)-A2)/36000)
runterkopieren

in H2 (Hilfsspalte - ausblenden) steht:
=F2
und in H3:
=WENN(F3="";H2;F3)
runterkopieren

in G5: =SUMME(B:B) und in G8: =SUMME(E:E)


Erläuterung
Ist G2 leer, erfolgt die Abrechnung zum letzten Datum in Spalte A.
Ein in F2 eingegebener Zinssatz (Eingabe ohne % auch 0,00) gilt so lange, bis ein neuer kommt. Löschen dementsprechend mit Entf - nicht 0.
Die Berechnung erfolgt mit Zinseszins zu allen Terminen in Spalte A, bei denen in Spalte C ein v (Verrechnung) eingegeben wurde. Zinseszins zu jedem Termin: in C also überall v eingeben - bzw. ohne jeglichen Zinseszins ist C komplett leer.

Die Berechnungsart ist: Tage genau durch 360 = Euromethode (nachschüssig).
Wird die deutsche Methode gewünscht (Jahr 360, Monat 30 Tage durch 360), dann in E2 eingeben:
=WENN(A2="";"";D2*H2*(TAGE360(A2;MIN(A3:A$1000;G$2)+(TAG(MIN(A3:A$1000;G$2)+1)=1))-(TAG(MIN(A3:A$1000;G$2)+1)=1))/36000)