Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Mathematische Funktionen

Beitrag: Nullstellen von Polynomen mit IKV-Funktion berechnen

Aufgabe
Die Finanzmathematische Funktion IKV, die eigentlich interne Zinsfüsse von Zahlungsreihen berechnen soll, kann auf bislang nicht bekannte Weise Nullstellen von Polynomen höherer Ordnung ermitteln.

       A               B               C               D               E               F               G               H       
1 1,00  2,0367    380  380    Schätzwert:  3  
2 1,20689655  -9,468877    -857,6  857,6    0-Stelle:  3,362939  
3 1,4137931  -12,60632    713,1  713,1       
4 1,62068966  -11,00784    -288  288       
5 1,82758621  -7,272992    60,67  60,67       
6 2,03448276  -3,149371    -6,4  6,4       
7 2,24137931  0,301743    0,2667  0,2667       
8 2,44827586  2,554807             
9 2,65517241  3,484489             
10 2,86206897  3,245217             



Lösung
In D1:D7 stehen die Polynom-Koeffizienten einer Gleichung 6.Grades, beginnend mit der kleinsten Potenz.
Die Funktion im Beispiel lautet also
y=0,2667x^6-6,4x^5+60,67x^4-288x^3+713,1x^2-857,6x+380

Schreibe in E1 =-1^(-1+ANZAHL(D$1:D1))*D1
und kopiere die Formel bis E7. Dies ist die Funktions-Negierung (Spiegelung auf der Y-Achse)*)

In A1:A30 steht ein X-Werte-Bereich
Da die Nullstellen im Bereich von 1 bis 7 liegen, schreibe in A1:=1, in A30:=7 in A2 steht =($A$30-$A$1)/29+A1, dies kopierst Du bis A29

In B1:B30 stehen die Y-Werte mit =NBW(1/(A1)-1;D$1:D$7)/A1
oder alternativ das Polynom 6. Ordnung (s.o.)

Erzeuge ein Punkt(xy)-Diagramm basierend auf Spalte A:B.
Spalte A und B sind zur weiteren Berechnung nicht nötig, sie dienen nur zum grafischen Nachweis.

Gebe in H1 einen Schätzwert der ungefähren Nullstellenposition ein (Im Beispiel 3)
In H2 steht die Nullstelle
=WENN(H1<0;-1/(1+IKV(E1:E7;1/(-H1)-1));1/(1+IKV(D1:D7;1/H1-1)))

Siehe dazu auch Tip und Download Nr. 40





Erläuterung
Was haben die Finanzmathematischen Funktionen
NBW bzw. IKV mit Polynomen zu tun?

Eine Zahlungsreihe der FI-Math. über n-Perioden ist eigentlich nichts anderes als ein Polynom n-ter Ordnung

Du hast n Zahlungen a,b,c,d,e,...,n in n Perioden.

Willst Du den Barwert ausrechnen, mußt Du jede Zahlung mit dem Faktor
1/(1+zinssatz) multiplizieren(abzinsen) und zwar a 1mal b 2mal und so weiter:

NBW = a*1/(1+zinssatz)^1+b*1/(1+zinssatz)^2+c*1/(1+zinssatz)^3+d*1/(1+zinssatz)^4+e*1/(1+zinssatz)^5+...+n*1/(1+zinssatz)^n

Genau so macht das die Excel-Funktion NBW.
siehe dazu auch die Formel Nr.266

Setzt Du nun 1/(1+zinssatz)=x

ergibt sich
NBW=a*x+b*x^2+c*x^3+d*x^4+e*x^5+...+n*x^n
teilt man die Gleichung durch x ergibt sich
NBW/x=a+b*x+c*x^2+d*x^3+e*x^4+...+n*x^(n-1)
und bei NBW/x = y.
ergibt sich das Polynom in allgemeiner Form
y = a+b*x+c*x^2+d*x^3+e*x^4+...+n*x^(n-1)

In das erste Argument von NBW gibt man den Zinssatz ein. Nirgendwo ist aber vorgeschrieben, dass er zwischen 0% und 100% liegt.
Die Formel funktioniert nämlich für alle Werte.

Löst man nun 1/(1+zinssatz)=x nach zinssatz (i) auf ergibt sich:
i=1/x-1

Will man nun den Y-Wert für x=-15 ausrechnen, gibt man als Zinssatz
1/x-1=-106,666667%

das macht natürlich finanzmathematisch keinen Sinn, funktioniert aber trotzdem.

So, IKV macht nichts anderes, als NBW=0 zu setzen und die Gleichung nach x aufzulösen, genauer gesagt nach i, also 1/x-1.
IKV verwendet dazu höchstwahrscheinlich das Newton'sche Näherungsverfahren.
Jetzt muß man nur den richtigen Schätzwert eingeben und bekommt alle positiven i Werte. Die muß man dann nur noch nach x auflösen.

*)IKV kann nur positive Nullpunkte ermitteln.
Wenn also eine Funktion einen negativen Schnittpunkt hat, muß man die Funktion erst auf der Y-Achse spiegeln und den nun postiven Nullpunkt ermittelten und dann mit -1 multiplizieren.
Beispiel:
Gegeben sei die Quadratische Gleichung(siehe Nr.157) y =3x^2 + 7x -26 mit den Lösungen X1=2; X2 = -4,333
IKV kann nur die 2 ermitteln.
Wie kann man nun trotzdem die -4,333 finden?
Ganz einfach, wenn ich die Funktion auf der Y-Achse spiegele, muß die alte Nullstelle -4,333 nun bei +4,333 liegen.
Wie spiegelt man eine Funktion auf der Y-Achse? In dem man alle ungeraden Potenzen mit -1 multipliziert.
Die Spiegelfunktion lautet also y=3x^2 - 7x -26.
Nun läßt sich mit unserer Nr. 157 leicht beweisen, daß das Ergebnis X1=4,333; X2=-2 lautet.
Nun muß man X1 mit -1 multiplizieren und hat die negative Nullstelle der Ursprungsfunktion gefunden.