[LAMBDA] SCAN (am Tilgungsplanbeispiel)

[LCohen]

Mit einer dynamischen Formel allein kann man einen ganzen annuitätischen Tilgungsplan erstellen, also mit Zins, Tilgung und Restbestand des Darlehens.

Hierfür bedarf es jedoch der Einstellung "Iteration".

Mit den LAMBDA-Helper-Funktionen kann man das auch ohne Iteration, da man diese in einer von sich selbst abhängigen Reihe mit einem externen Startwert versorgen kann.

Im nachfolgenden Beispiel funktioniert dies mit SCAN:

=LET(bw;100000;zins;3%;zzr;12;zw;0;f;0;rmz;RMZ(zins;zzr;-bw;zw;f);lfd;SEQUENZ(zzr);
x;SCAN(bw;lfd;LAMBDA(cum;arr;cum*(1+zins)-rmz));
z;zins*WENN(lfd=1;bw;INDEX(x;lfd-1));
t;rmz-z;
WAHL({1.2.3};z;t;x))

• Zeile 1: Versorgung der Parameter der Funktion RMZ (Annuität)
  
• Zeile 2: Ausgangspunkt (also auch Startwert: cum) ist der Barwert bw 100.000. Darüber wird ein SCAN-Array der Länge zzr gebildet. In diesem Array wird für den jeweiligen Folgewert die Annuität rmz abgezogen und um den anteiligen Zinsanteil cum*zins zurück erhöht.
  
• Zeile 3: Diese Zinsen beziehen sich aber (außerhalb des SCAN-Arrays) auf die falsche Zeile, nämlich auf den Restwert statt richtig den Barwert (da der von außen "unbekannt" ist). Deshalb muss vor die ermittelten Zinsen z eine Zeile eingefügt werden und somit der erste Restwertzins durch den Barwertzins (3000) ersetzt werden.
  
• Zeile 4: Dir Tilgung t ergibt sich nun einfach aus der Differenz der Annuität rmz und den Zinsen z.
  
• Zeile 5: WAHL stellt die Spalten wie gewünscht nebeneinander.
  

Das ganze kann man natürlich noch in eine UDF MORTGAGEPLAN verpacken. Hier diente es nur dafür, zu zeigen, wie man die Reihen korrekt nebeneinander stellt. Ungerundete Beträge, daher Centdifferenzen im Verlauf, nicht am Ende!

PHP-Code:

 3.000,00      7.046,21       92.953,79 
 2.788,61      7.257,59      85.696,20 
 2.570,89      7.475,32      78.220,87 
 2.346,63      7.699,58      70.521,29 
 2.115,64      7.930,57      62.590,72 
 1.877,72      8.168,49      54.422,23 
 1.632,67      8.413,54      46.008,69 
 1.380,26      8.665,95      37.342,75 
 1.120,28      8.925,93      28.416,82 
   852,50      9.193,70      19.223,12 
   576,69      9.469,52       9.753,60 
   292,61      9.753,60       -   


[LCohen]

Da aber letztlich jeder einzelne (!) Wert im Tilgungsplan auch über die vorhandenen Excel-Finanzfunktionen darstellbar ist, kann man das gleiche auch "traditionell" (ohne LAMBDA und auch ohne Iteration, aber wie gewünscht dynamisch, somit derzeit in xl2021, xl365 und xlWeb) eine Stufe niedriger erreichen:

A1: 3%
B1: 12
C1: 100000
D1: 0
E1: 0

A2: (oder an jeder beliebigen Stelle)
=LET(Zins;A1;Zzr;B1;Bw;C1;Zw;D1;F;E1;
Rmz;RMZ(Zins;Zzr;Bw;-Zw;F);
lfd;SEQUENZ(Zzr);
BwX;BW(Zins;Zzr-lfd+0;Rmz;-Zw;F);
TlgX;BW(Zins;Zzr-lfd+1;Rmz;-Zw;F)-BwX;
ZnsX;-TlgX-Rmz;
WAHL({1.2.3};ZnsX;TlgX;BwX))

Wichtig ist dabei die umgekehrte Reihenfolge der Ermittlung, also statt 
ZnsX->TlgX->BwX genau andersherum: 
BwX->TlgX->ZnsX

SCAN kommt somit eher bei anderen Beispielen, die sich nicht so wie hier einzeln iterationsfrei errechnen lassen, erst so richtig ins Spiel. Vielleicht fällt ja jmd. etwas ein?

[LCohen]

Hier die Excel-Web-Version (kann von jedem bearbeitet werden):
1drv.ms/x/s!AsnnAXrDppAdm0vMQcde-vbA9I3v?e=Y3l5lG

und hier die Offline-Version für xl2021, xl365, xlWeb:
  Annuitaetendarlehen.xlsx (Größe: 11,8 KB / Downloads: 0)

Bitte ausprobieren! Oft ist gerade bei Tilgungsplänen noch der Wurm drin.

Grün bedeutet: 5 von 6 Parametern sind gefüllt, der 6. wird errechnet (seine Eingabezelle in Zeile 3 muss mit ENTF geleert sein; 0 gilt nicht als leer!). Alles ist gut. Der Parameter F (vorschüssig=1, nachschüssig=0) ist hierbei kein errechenbarer Parameter, muss also immer mit 0 oder 1 gefüllt sein.

Rot bedeutet: 6 oder weniger als 5 Parameter sind gefüllt. Das Ergebnis könnte falsch sein oder müsste zumindest geprüft werden.

Ich habe die dynamische Formel hier auf die errechneten Werte in Zeile 2 als Argumente bezogen, statt nur die Möglichkeit zu haben, dass die Annuität RMZ frei bleiben muss (siehe #2).

Bei xlWeb fällt mir ein Bug bezogen auf die Auswahl nicht gesperrter Zellen auf; vielleicht Euch ja auch.

[LCohen]

Da fällt mir ein:

ms-office-forum.net/forum/showpost.php?p=2059303&postcount=5

zeigt eine sinnvolle Anwendung von SCAN, und zwar ein "Durchsuchen eines Musters bis zum Ende" nebst Wiedergabe aller "Fundorte". Und so verstehe ich jetzt auch die Namenswahl der Funktion.