Excel und Stückzins

[Delphin]

Muss mal die Anleihe-Freunde hier um Rat fragen: ich versuche die eingebaute Funktion für Stückzinsen zu verstehen und stehe irgendwie auf dem Schlauch.

 

In OpenOffice Clac - ich vermute Excel ist identisch - gibt's die folgende Funktion:

 

AUFGELZINS(Emission; ErsterZins; Abrechnung; Zins; Nennwert; Häufigkeit; Basis)

 

Basis ist "1" für act/act, "4" für europäisches 30/360.

 

1. Frage: In der Hilfe kommen nur Beispiele vor, in denen der Abrechnungstermin zwischen erstem Zins und Emission liegen. Wenn ich selbst mit Daten rumspiele ergibt sich folgendes:

* das Emissionsdatum wird behandelt als ob es das letzte Zinsdatum wäre (also der Tag, von dem an die Zinsesn auflaufen)

* das erster-Zins-Datum wird ignoriert(?)

Was hat das zu bedeuten? Benutze ich die falsche Funktion?

 

2. Frage: Für die act/act-Methode kommen z.T. Stückzinsen raus, die keinem ganzen Tag entsprechen. Wie kommt das? Nehmen wir folgendes Beispiel (zums Nachvollziehen einfach in eine Zelle kopieren):

 

=AUFGELZINS(DATUM(2009;1;1);;DATUM(2009;1;21);365%;100;1;1)

 

Die 365% nehme ich nur damit ich am Ergebnis sehen kann, mit wíeviel Tagen die Funktion intern rechnet. Das Ergebnis ist hier korrekt 20, genau wie wenn man selbst rechnte:

 

=TAGE(DATUM(2009;1;21);DATUM(2009;1;1))

 

Wenn ich die Formel leicht modifiziere und den 31. Dezember nehme, funktioniert irgendwas nicht mehr...

 

=AUFGELZINS(DATUM(2008;12;31);;DATUM(2009;1;21);365%;100;1;1)

 

Das Ergebnis ist 20,94! Sollte natürlich 21 sein. Ist das ein Programierfehler?

 

EDIT: Sehe gerade, dass 20,94 = 21/366 * 365. Das heißt, es ist wirklich ein bug! Der Algorithmus erkennt, dass 2008 ein Schlatjahr war, aber nicht, dass der Schalttag vor dem relevanten Datum lag... Na prima!

 

Kann ich ja gleich eine Frage anhängen: habt ihr eine gute Idee, wie ich aus der Fälligkeit einer Anleihe auf den letzten zurückliegenden Zinstermin schließen kann (ineiner Programiersprache würde man das wohl meist iterativ lösen)? Dann wäre es ja recht einfach selbst die Stückzinsen zu berechnen.

[Fleisch]

also ich versuch mich mal auf deine Frage einzustellen. Ich nutze Excel 2007

 

für die Tage dazwischen nutze ich die Funktion TAGE360 in Verbindung mit ZINSTERMVZ

 

Formel sieht dann in meinem Sheet, was du dir in meinem Musterdepotthread auf Seite 6 oder 7 runterladen kannst so aus:

 

=((D17/E17)*(TAGE360(ZINSTERMVZ(N17-1;G17;1);N17))*F17)/360

 

Das zu erklären wäre jetzt glaube zu kompliziert. Guck mal rein, ob du meine Getippe deuten kannst

[Torman]

Wenn es nicht auf den letzten Cent ankommt, hilft vielleicht diese Näherung, um die Zinstage seit der letzten Ausschüttung zu berechnen.

 

Zinstage=365-REST(A1-A2;365)

 

A1 Zelle mit Datum der Fälligkeit

A2 Zelle mit Datum des Bewertungstages

 

Ergänzung: Dafür gibt es wohl auch die Funktion ZINSTERMTAGVA.

[vanity]

=AUFGELZINS(DATUM(2008;12;31);;DATUM(2009;1;21);365%;100;1;1)

...

EDIT: Sehe gerade, dass 20,94 = 21/366 * 365. Das heißt, es ist wirklich ein bug! Der Algorithmus erkennt, dass 2008 ein Schlatjahr war, aber nicht, dass der Schalttag vor dem relevanten Datum lag... Na prima!

...

Bei ACT/366 scheint es tatsächlich so zu sein, dass der Schalttag auch gerechnet wird, wenn er in nicht innerhalb der Kuponperiode liegt. Schau mal in den Anleihen/Dt. Telekom-Thread mit Beiträgen von heute.

 

Telekom-Zinsabrechnung (kurzer Kupon)

[Delphin]

Danke für die Antworten!

 

@Torman:

Zinstage=365-REST(A1-A2;365)

So einfach hatte ich gar nicht zu denken gewagt, weil das ja immer mal um ein paar Tage abweichen kann - aber du hast Recht, eigentlich teicht die Genaugkeit ja für mich locker aus.

 

@Schnitzel, du schreibst (frei modifiziert):

Stückzins = Nennwert * Kupon * TAGE360(ZINSTERMVZ(Kaufzeitpunkt-1;Fälligkeit;1);Kaufzeitpunkt) / 360

ZINSTERM...() kannte ich noch gar nicht, diese Funktionen sind im Grunde genau die Lösung nach der ich gesucht hatte:

 

ZINSTERMVZ - Tag der letzten Zinszahlung

ZINSTERMNZ - Tag der nächsten Zinszahlung

ZINSTERMTAGVA - Anzahl der Tage seit der letzten Zinszahlung (warum endet diese Funktion nicht auf "z"?!)

ZINSTERMTAGNZ - Anzahl der Tage bis zur nächsten Zinszahlung

ZINSTERMTAGE - Anzahl der Tage der aktuellen Zinsperiode

 

Damit könnte man also die Stückzinsberechnung so schreiben:

 

[1] Stückzins = Nennwert * Kupon * ZINSTERMTAGVA(Abrechnungstag; Fälligkeit; Häufigkeit; Basis) / ZINSTERMTAGE(Abrechnungstag; Fälligkeit; Häufigkeit; Basis)

 

Das wäre sozusagen auch deine Formel, nur dass du dann die Zinsmethode wählen könntest.

 

Außerdem bieten die oben genannten Formeln auch die Möglichkeit die Funktion AUFGELZINS() zu verwenden, die offenbar - entgegen der Dokumentation? - den letzten Zinstag benötigt:

 

[2] Stückzins = AUFGELZINS(ZINSTERMTAGVZ(Abrechnungstag; Fälligkeit; Häufigkeit; Basis);;Fälligkeit;Kupon;Nennwert;Häufigkeit;Basis)

 

Interessanterweise sind die Ergebnissse nicht immer identisch.

 

 

@vanity:

Habe das gelesen und auch noch ein paar andere Renditerechner im Netz bemüht - im Detail ergeben sich hier tatsächlich oft Unterschiede. Kennt jemand eigentlich die offiziellen Definitionen der Zinsmethoden, falls es sowas gibt?

 

Z.B. schreibt zinsen-berechnen.de, dass man bei 30.12.08 bis 21.1.09 wie folgt rechnen muss: 1/366 + 21/365. Die Börse Stuttgart sagt: 22/365, der würde ich eigentlich am ehesten vertrauen. OpenOffice sagt: 22/366. Alle drei jeweils mit act/act-Einstellung. Lustig was?

 

Übersehe ich was? Gibt es verschiedene act/act-Konventionen?

[Fleisch]

warum die nicht immer identisch sind hab ich auch noch nicht verstanden muss ich ehrlich sagen, aber freut mich dir mit der formel weitergeholfen zu haben. die formel hab ich auch bissl gesucht muss ich gestehen

[webber]

ich bekomme noch mal zu viel.

irgendwie ralle ich das mit Excel nicht, um die Rendite jährlich zu berechnen.

Nennwert 1000

Kaufkurs 105

Rücknahmekurs 100

Kaufdatum 5.2.09

Rückgabedatum 6.5.10

Kupon 5%

Kupontermin 6.5.10

 

sind natürlich Beispieldaten.

Wie berechne ich nun die jährliche Rendite p.a. mit Excel.

Ausgegangen wird von einem festgelegtem Kupon.

[Delphin]

ich bekomme noch mal zu viel.

irgendwie ralle ich das mit Excel nicht, um die Rendite jährlich zu berechnen.

Nennwert 1000

Kaufkurs 105

Rücknahmekurs 100

Kaufdatum 5.2.09

Rückgabedatum 6.5.10

Kupon 5%

Kupontermin 6.5.10

 

sind natürlich Beispieldaten.

Wie berechne ich nun die jährliche Rendite p.a. mit Excel.

Ausgegangen wird von einem festgelegtem Kupon.

Fehlt noch das Kaufdatum, aber ich nehme an, du meinst einfach heute, sprich Abrechnung am Di 10.02.2009.

 

Ich nehme an, die Rendite, die du berechnen willst ist die "Rendite bis Fälligkeit" (YTM)? Dafür kannst du die Formel "RENDITE()" benutzen:

 

=RENDITE("10.2.2009";"6.5.2010";"5%";105;100;1;1)

 

 

EDIT: übrigens hab ich mir jetzt nach Ergebnis der Stückzins-Diskussion einen kleinen Anleiherechner gemacht.

DelphinAnleiherechner.xls

[webber]

Fehlt noch das Kaufdatum, aber ich nehme an, du meinst einfach heute, sprich Abrechnung am Di 10.02.2009.

 

Ich nehme an, die Rendite, die du berechnen willst ist die "Rendite bis Fälligkeit" (YTM)? Dafür kannst du die Formel "RENDITE()" benutzen:

 

=RENDITE("10.2.2009";"6.5.2010";"5%";105;100;1;1)

 

 

EDIT: übrigens hab ich mir jetzt nach Ergebnis der Stückzins-Diskussion einen kleinen Anleiherechner gemacht.

DelphinAnleiherechner.xls

Ich habe mal ne kleine Excel Datei geschrieben, irgendwie kommt mir die reale Rendite p.a. ein bissl wenig vor. Hab Datei mal angehangen. Kann die ma einer überprüfen??

[Delphin]

Ich habe mal ne kleine Excel Datei geschrieben, irgendwie kommt mir die reale Rendite p.a. ein bissl wenig vor. Hab Datei mal angehangen. Kann die ma einer überprüfen??

Wenn du mir erklären könntest, was du in den Spalten ab "O" berechnen möchtest? Ich kann da nicht so ganz folgen.

 

Du summierst alle Kupon-Zahlungen auf., wofür? Das riecht für mich nach einem Kardinalfehler, nämlich Zahlungen zu addieren, die nicht gleichzeitig sattfinden. Ein kleiner Fahler ist da übrigens der Term "Jahr(Fälligkeit)-Jahr(Valuta)", da funktioniert in deinem Fall, weil du nach dem Zinstermin gekauft hast, würdest du aber ein paar Tage vor dem Zinstermin kaufen, klappt die Formel bedauerlicherweise nicht mehr.

 

Schreib doch am besten die Formeln mal auf Papier auf, dann können wir besser drüber reden.

 

Eins kann ich dir aber gleich sagen, so etwa funktioniert nicht:

i = Rendite

T = Tilgunsbetrag, also Rückzahlungsbetrag

K = Kuponzahlung

n = Jahre

P = Kaufpreis (inkl. Stückzinsen)

 

i = (T + n*K) / (P * n)

 

Wenn alle Zinsen am Schluss gezahlt würden, dann könnte man immerhin die Kupons mit der Tilgung zusammenzählen.

 

(T + n*K) / P

 

Aber um das auf Jahresrenditen umzurechnen, kann man nicht einfach durch die Anzahl der Jahre teilen, sondern man muss korrekt rechnen:

 

i = ((T + n*K) / P)^(1/n) - 1

 

Denn 10% in 10 Jahren sind ja nicht 1% pro Jahr!

 

--

 

Ich glaube, was du noch nicht kennst, ist das Konzept des Abzinsens und Aufzinsen. Um zwei Zahlungen vergleichen zu können, muss man sie auf einen einheitlichen Zeitpunkt bringen. Wenn ich mehr Zeit hab, kann ich das vielleicht mal erklären, die Renditeberechnung (u.a. von Anleihen) ist nichts weiter als eine Anwendung diese Prinzips (zusammen mit ein paar Vereinfachungen).

 

Die Stichworte, die dich interessieren, sind glaube ich: Finanzmathematik, Zinsrechnung, Barwertmethode.

[webber]

Die Stichworte, die dich interessieren, sind glaube ich: Finanzmathematik, Zinsrechnung, Barwertmethode.

richtig

 

Ab Spalte O versuchte ich zu die Rendite p.a. abzgl. der Kosten, die ich am Anfang hatte zu berechnen, also "(E-D)-F-G", und das mit dem internen Zinsatz .p.a zu verrechnen. Interessant ist nämlich, was bekomme ich mehr, als auf einem kostenlosen Festgeld p.a.

[webber]

Benutzt die FED momentan eigentlich klassische Mittel für eine außergewöhnliche Krise??

Wenn nun 1 Bil. $ in den Markt kommt, wie kann man diese wieder raus nehmen? geht das überhaupt, außer Inflation??

Nach längerer Zeit habe ich noch mal nen bissl gebastelt. und alles etwas umgeändert. Die Rendite habe ich mit dem Rechner der Börse Stuttgart ausgerechnet, Gebühren habe ich in Prozent auf den Kurs drauf geschlagen.

Freue mich über Fehlerbehebungen der angehängten Excel Datei. Mich interessieren halt die letzten 3 Spalten der Tabelle.

[vanity]

In OpenOffice gibt es diese nette Funktion

 

=KURS(1;E44*365;C43;C44;100;1)

 

um bei gegebener Restlaufzeit (E44*365), festem Kupon (C43) und Marktzins (C44) den dazu passenden Kurs des Papiers zu ermitteln. Also sozusagen die Umkehrfunktion zum Internen Zins.

 

Klappt das bei MS-Excel auch? Ich frage deshalb

 

Okay, und wenn ich jetzt noch was sehen könnte ...

[Lobster]

In OpenOffice gibt es diese nette Funktion

 

 

 

um bei gegebener Restlaufzeit (E44*365), festem Kupon (C43) und Marktzins (C44) den dazu passenden Kurs des Papiers zu ermitteln. Also sozusagen die Umkehrfunktion zum Internen Zins.

 

Klappt das bei MS-Excel auch? Ich frage deshalb

Also die Formel gibt es in Excel 2007, aber ich bekomme immer "Zahlenfehler". Habe als Parameter 1, 5% und 6%.

 

Edit: Als ersten und zweiten Parameter muss man ein Datum eingeben. Beispiel:

B4: 24.05.2009

C4: 01.08.2018

D4: 5,00%

E4: 6,00%

F4: 100

G4: 1

 

Formel: =KURS(B4;C4;D4;E4;F4;G4)

Ergebnis: 93,06970598

[vanity]

Also die Formel gibt es in Excel 2007, aber ich bekomme immer "Zahlenfehler". Habe als Parameter 1, 5% und 6%.

 

Edit: Als ersten und zweiten Parameter muss man ein Datum eingeben. Beispiel:

B4: 24.05.2009

C4: 01.08.2018

D4: 5,00%

E4: 6,00%

F4: 100

G4: 1

 

Formel: =KURS(B4;C4;D4;E4;F4;G4)

Ergebnis: 93,06970598

 

Stimmt, die ersten beiden Parameter sind Datumsangaben. OpenOffice macht aus der Eingabe 01.01.2009 bzw. 01.01.2010

 

=KURS(39814;40179;C43;C44;100;1)

 

also Anzahl Tage seit 01.01.1900. Ist das in MS-Excel auch so?

[Lobster]

Stimmt, die ersten beiden Parameter sind Datumsangaben. OpenOffice macht aus der Eingabe 01.01.2009 bzw. 01.01.2010

 

 

 

also Anzahl Tage seit 01.01.1900. Ist das in MS-Excel auch so?

Also in Excel kann man ja auch einfach nur ein Datum angegeben... ohne die Anzahl der Tage seit 01.01.1900. Das mit den Tagen scheint aber auch zu klappen.

Mit 5 und 6% und deinen Tagen kommt Excel auf 99,05660377.

[vanity]

Also in Excel kann man ja auch einfach nur ein Datum angegeben... ohne die Anzahl der Tage seit 01.01.1900. Das mit den Tagen scheint aber auch zu klappen.

Mit 5 und 6% und deinen Tagen kommt Excel auf 99,05660377.

Passt! Eingeben kann man es in OO auch als Datum, es wird aber automatisch umgewandelt und steht dann als Anzahl Tage in der Formel. Vielleicht lässt es sich aber auch konfigurieren, ich bin nicht so der Excel-Held.

[Lobster]

Passt! Eingeben kann man es in OO auch als Datum, es wird aber automatisch umgewandelt und steht dann als Anzahl Tage in der Formel. Vielleicht lässt es sich aber auch konfigurieren, ich bin nicht so der Excel-Held.

Jedenfalls finde ich die Formel auch sehr interessant, wenn man sich selbst für eine Anleihe einen Renditen-Schwellenwert vorgibt... so kann man dann sehr schön schauen, wann der Kurs unter den ermittelten Kurs fällt. Für eine Watchlist ideal!

[Fleisch]

Zur Emittlung der "Restrendite" ausgehend vom aktuellen Kursniveau des betreffenden Papiers meinst du nehm ich mal an, oder ? Ist sowas nicht eher für gehaltene Papiere interessant ? Bei der Watchlist gehe ich ja bereits von jetzt bis zur Fälligkeit aus.

[vanity]

Zur Emittlung der "Restrendite" ausgehend vom aktuellen Kursniveau des betreffenden Papiers meinst du nehm ich mal an, oder ? Ist sowas nicht eher für gehaltene Papiere interessant ? Bei der Watchlist gehe ich ja bereits von jetzt bis zur Fälligkeit aus.

Andersrum:

 

85,95=KURS(1;5*365;4,375;5,000;100;1)

 

würde bedeuten: ein Papier mit Kupon 4,375% (P3), welches in 5 (P2) Jahren fällig wird, soll mindestens 5% (P4) Rendite bringen. Was darf es höchstens kosten? - 85,95%

 

Also nicht mit dem aktuellen Kurs die Restrendite errechnen, sondern mit der Zielrendite den max. Kaufpreis (und den dann mit dem akt. Preis vergleichen).

 

Das kann man natürlich auch für die gehaltenen Papiere so machen: Überschreitet der aktuelle Kurs den so errechneten Kurs, unterschreitet die Restrendite des Papiers den gewünschten Schwellwert und das Papier wird zum Austauschkandidat.

[Fleisch]

achso, dann hab ich falsch gedacht jetzt mit deiner erklärung wird's mir auch klar. mal schauen, ob ich das vlt. auch irgendwie in meine watchlist eingearbeitet bekomme. wäre ne nette ergänzung

[vanity]

achso, dann hab ich falsch gedacht jetzt mit deiner erklärung wird's mir auch klar. mal schauen, ob ich das vlt. auch irgendwie in meine watchlist eingearbeitet bekomme. wäre ne nette ergänzung

So könnt's gehen:

 

 

Damit:

 

watchlist.xls (OpenOffice)

[Fleisch]

hmm bei mir kommt, wo bei dir "BUY" steht nur die doofe Excelfehlermeldung "#NV"

 

könntest du die Formel mal so als Text posten ?

[Sven82]

Zelle K9:

=WENN(F9<I9;" BUY";WENN(F9>J9;" SELL";" HOLD"))

[Fleisch]

merci