Gebühren und Zeitraum

[Ronesch]

Super Arbeit funktioniert jetzt

[Ronesch]

Jetzt kommt mein nächstes Problem, dass etwas kniffliger sein dürfte. Es geht darum, einen Betrag  zu errechnen der unter bestimmten Voraussetzungen zustande kommt. Ich arbeite in einem Pfandbetrieb und wir wollen eine Tabelle erstellen, die im Falle eines totalen Ausfalls unserer Software den Betrieb ohne große Probleme und fehlerfrei am Laufen hält. Wir bieten den Kunden unter anderem an ihren Darlehensbetrag zu verringern, um so die monatlichen Kosten für ihr Pfand zu senken. Das passiert wie folgt. Nehmen wir einmal an ein Kunde hat folgende Daten auf seinem Pfandschein.

Erstversatz: 01.01.2017
Datum der Verlängerung/Reduzierung: 01.06.2017
Darlehensbetrag: 160,00€
Zinsen: 1,60€
Gebühren: 4,50€
Monatliche Zin/Geb: 6,10€
Monate: 5
Fälliger Betrag:
6,10€ x 5 = 30,50€

Nehmen wir nun an, dass der Kunde anstatt 30,50€ zum Verlängern mehr zahlen möchte um seinen Pfandbetrag zu reduzieren. Er bezahlt 80,00€ und daraus ergibt sich dann folgendes.
80,00€-30,50€ = 49,50€

Diese 49,50€ müssen jetzt von dem Pfandbetrag über 160,00€ abgezogen werden jetzt könnte man sagen das es ja total einfach wäre einfach zu subtrahieren und man hätte den richtigen Betrag. Das Problem ist, dass wir eine bestimmte Tabelle benutzen die gewisse Sprünge hat. Die Darlehenssprünge sehen wie folgt aus.
16,00€ - 20,00€ - 25,00€ - 35,00€ - 40,00€ - 55,00€ - 60,00€ - 70,00€ - 80,00€ - 105,00€
110,00€ - 120,00€ - 130,00€ - 155,00€ - 160,00€ - 170,00€ - 180,00€ - 210,00€ - 220,00€
230,00€ - 255,00€ - 260,00€ - 270,00€ - 310,00€

(Darlehnssprünge sind in Spalte U hinterlegt)

Wenn wir dann das Beispiel nehmen über die 160,00€ ergibt sich daraus das Ergebnis, dass der Kunde einen neuen Pfandbetrag über 120,00€ erhält.
160,00€-49,50€=110,50€
Da wir keinen Pfandbetrag über 110,50€ ausgeben geht es auf das nächst höhere Darlehen was in diesem Fall 120,00€ wären. Den Rest des Geldes über 9,50€ bekommt er natürlich als Wechselgeld zurück.
 
Meine Frage ist jetzt, gibt es eine Formel die aus den vorgegeben Daten automatisch den richtigen Betrag bei einer Reduzierung errechnet. Also man gibt den Betrag X den der Kunde zusätzlich bezahlen möchte an und daraus errechnet die Formel dann den richtigen neuen Darlehensbetrag in den vorgegebenen Sprüngen.
 
Vielen Dank schon mal im Voraus

[Elex]

Hi

=INDEX(U2:U26;VERGLEICH(110,50;U2:U26;1)+1;)    ' an die Stelle wo jetzt der Betrag (110,50) steht schreibst du deine Formel die den Betrag ermittelt.

die Tabelle mit 0 beginnen lassen. 0; 16; 20 usw...

[Ronesch]

Die Formel ist schon sehr gut. Aber ein paar Bedingungen, die wir nicht erwähnt haben, kann man noch angepassen. dazu melde ich mich noch. aber erst mal vielen dank für die Super Arbeit. Gruss

[Skankhunt42]

Hallo Elex,

ich bin ein Arbeitskollge von Ronesch und wir machen das zusammen mit der Tabelle. Er meinte ja gestern schon, dass es da noch ein paar Probleme mit einigen Sachen gibt. An sich funktioniert die Formel super nur eben nicht in allen Fällen die wir haben könnten. Wir haben in dem Post davor nicht daran gedacht. Ich schreibe die Fälle nacheinander auf und über die Bilder kann man genau sehen was ich meine um die Sache etwas zu verdeutlichen. Ich denke mal es liegt daran, dass die Formel nur bis zu einem Wert von 310€ ausgelegt ist da wir aber auch höhere Pfänder beleihen funktioniert sie wahrscheinlich nicht bei allen. Wir haben die Grundformel erweitert auf einen Wert bis 17.000€ also in dem Fall der Bereich U2:U1695. Ab einem Pfandbetrag von 310,00€ runden wir immer auf den nächsten vollen Zehner auf.

Fall 1: Der Kunde hat ein Darlehen über 750,00€ und zahlt 400,00€. Die Zinsen und Gebühren belaufen sich auf 90€ was dann einen Restbetrag von 310€ ergibt den man vom Darlehen abziehen könnte. Mit der normalen Formel -> =INDEX($U$2:$U$1695;VERGLEICH(B5-J5;$U$2:$U$1695;1)+1;) sieht das Ergebnis wie folgt aus.

   

Wenn man jetzt aber die +1;) am Ende durch ein +0;) ersetzt kommt das richtige Ergebnis.

   

Das Problem ist das man so die Formel nicht ziehen kann da sonst jenachdem die Ergebnisse falsch sind.

Fall 2: Der Kunde hat ein Darlehen über 16,00€ und zahlt 8,00€. Die Zinsen und Gebühren belaufen sich auf 8,30€ was dann einen Restbetrag von -0,30€ ergibt. Sprich der Kunde hat zu wenig Geld dabei und kann weder verlängern noch Reduzieren. Die normale Formel =INDEX($U$2:$U$1695;VERGLEICH(B5-J5;$U$2:$U$1695;1)+1;) ergibt folgendes.

   

Wenn man jetzt wieder die +1;) durch ein +0;) ersetzt kommt das raus.

   

Mit dem +1;) ist es komplett falsch und mit dem +0;) ist die tatsächliche Reduzierung und der neue Darlehensbetrag richtig.

Fall 3: Der Kunde hat einen Darlehen über 55,00€ und zahlt 20,00€. Die Zinsen und Gebühren belaufen sich auf 6,10€ was einen Restbetrag von 13,90€ ergibt. Da der nächst kleinste Darlehensbetrag unter 55,00€ 40,00€ sind kann man leider nicht reduzieren. Mit der normalen Formel also diese =INDEX($U$2:$U$1695;VERGLEICH(B5-J5;$U$2:$U$1695;1)+1;) kommt auch das richtige ergebnis raus wenn man die 1 wieder durch eine 0 ersetzt am ende ist es falsch.

Mit +1 das richtige

   

Mit +0 das falsche

   

Also mal ist die Formel mit +1;) richtig und mal die mit +0;) kann man die beiden Formeln irgendwie über eine zusätzliche Wenn Formel zusammen bringen das immer das richtige rauskommt? Wir haben das gestern mal versucht selbst zu machen aber ja da reichen die Excel Kenntnisse einfach nicht aus. Vielen Dank schon mal im voraus.

[Elex]

Versuch es mal mit der Formel.
=WENNFEHLER(WENN(J3<0;B3;INDEX($U$2:$U$1695;VERGLEICH(B3-J3-1%;$U$2:$U$1695;1)+1;));"")

[Skankhunt42]

Danke danke danke :D es funktioniert!!