Makro zur Berechnung des internen Zinsfußes in LibreOffice

[hatnix]

Hallo zusammen,

 

gängiges Instrument zur Berechnung einer Portfoliorendite ist ja die Excel- bzw. Calc-Formel XINTZINSFUSS(). Da sie aber einen entscheidenden Nachteil hat, habe ich mir selbst ein Makro geschrieben, dass ich hier zur Verfügung stellen möchte.

 

Also, Problembeschreibung:

Für XINTZINSFUSS muss man eine Liste aller Cash-Flows anlegen. Aus dieser Liste lassen sich aber jetzt nicht nur einzelne Werte verwenden, um die Performance z.B. auch nur für einzelne Wertpapiere zu berechnen. Prinzipiell wäre das nämlich mit einer einfachen Matrix-Formel möglich. Aber Matrix-Bezüge akzeptiert XINTZINSFUSS nicht.

 

Ich habe eine Formel erstellt, die folgende Parameter entgegennimmt:

 

ZINSFUSS(Buchungszeitpunkte; Buchungen; Startdatum; Enddatum; Startwert; Endwert)

D.h. man kann nicht nur die Buchungen und die dazugehörigen Zeitpunkte definieren, sondern auch angeben, welcher Datumsbereich berücksichtigt werden soll. Die Parameter Startwert und Endwert sind nützlich, um den Kurswert eines Wertpapiers oder Depots irgendwo separat nachhalten zu können und ihn nicht in die Liste der Buchungen aufnehmen zu müssen. Wenn man das nicht möchte oder es keinen Wert gibt weil das Wertpapier innerhalb des Zeitraums gekauft und wieder verkauft wurde, trägt man einfach jeweils "0" ein.

 

So und dann noch der größte Vorteil, der mit den Matrixbezügen: Für die Portfolioauswertung kann man sich eine Buchungstabelle, z.B. wie die folgende mit allen Buchungen erstellen:

 

 

Wenn man nun die Performance eines einzelnen Wertpapieres in einem bestimmten Zeitraum haben möchte verwendet man meine Formel ZINSFUSS:

 

{=ZINSFUSS(($Buchungen.$A$2:$A$400)*($Buchungen.$C$2:$C$400=XYZ);$Buchungen.$G$2:$G$400;Startdatum;Enddatum;0;Kurswert)}

Man beachte: Matrixformel! Also die Eingabe STRG+Shift+Enter bestätigen (Oder den Formeleditor öffnen und "Matrix" ankreuzen).

Was passiert hier? Die Matrix mit den Buchungsdaten ($A$2:$A$400) wird mit einer Matrix multipliziert, die dort einsen enthält, wo die "InvestitionsID" den gewünschten Wert "XYZ" hat, sonst nullen. Dadurch haben die nicht benötigten Buchungen ein Datum von null und werden in meinem Skript ignoriert. D.h. aus meiner Liste mit allen Buchungen werden die gewünschten extrahiert, die anderen bleiben unberücksichtigt. XINTZINSFUSS akzeptiert diese Schreibweise leider nicht. Natürlich kann man statt einer "InvestitionsID" auch die ISIN oder irgendein anderes Kriterium nehmen.

 

So kann man sich nun sehr übersichtlich die Renditen einzelner Wertpapiere und des Gesamtportfolios darstellen lassen:

 

 

 

Was benötigt man nun, um sich diese Formel verfügbar zu machen?

Ich habe die Formel in Python geschrieben, da Basic zu langsam war. Deshalb muss man folgendes Python-Skript in das OpenOffice/Libreoffice-Skript-Verzeichnis schieben. Bei mir ist das der Ordner "~/.config/libreoffice/4/user/Scripts/python/". Ich arbeite unter Linux. Unter Windows muss sich ein ähnliches Verzeichnis unter "Dokumente und Einstellungen" verstecken. Den letzten Unterordner "python" muss man eventuell selbst anlegen.

Nun erstellt man in diesem Ordner eine Datei namens "zinsfuss.py" und kopiert folgenden Quelltext hinein:

 

def _barwert(zins, daten, zahlungen, basisdatum, enddatum, startzahlung, endzahlung):
if not len(daten) == len(zahlungen):
	return "Unterschiedliche Anzahl an Daten und Zahlungen!"
if zins < -1.0:
	return "Der Zins muss >= -1 sein; " + str(zins) + " ist ungültig."
if basisdatum > enddatum:
	return "Das Enddatum darf nicht vor dem Startdatum liegen!"
discount = (1.0 / (1.0+zins))**(1.0/365.0)
sum = 0
for i, d in enumerate(daten):
	if isinstance(d, float) and isinstance(zahlungen[i], float) and not (d > enddatum or d < basisdatum):
		sum += zahlungen[i] * discount**(d-basisdatum)
sum += startzahlung
if endzahlung > 0:
	sum += endzahlung * discount**(enddatum-basisdatum)
return sum

def barwert(zins, daten, zahlungen, basisdatum, enddatum, startzahlung, endzahlung):
if basisdatum > enddatum:
	return "Das Enddatum darf nicht vor dem Startdatum liegen!"
if zins < -1.0:
	return "Der Zins muss >= -1 sein; " + str(zins) + " ist ungültig."
daten = [d[0] for d in daten]
zahlungen = [z[0] for z in zahlungen]
return _barwert(zins, daten, zahlungen, basisdatum, enddatum, startzahlung, endzahlung)

def endwert(zins, daten, zahlungen, basisdatum, enddatum, startzahlung, endzahlung):
if basisdatum > enddatum:
	return "Das Enddatum darf nicht vor dem Startdatum liegen!"
if zins < -1.0:
	return "Der Zins muss >= -1 sein; " + str(zins) + " ist ungültig."
daten = [d[0] for d in daten]
zahlungen = [z[0] for z in zahlungen]
q = (1.0+zins)**(1.0/365.0)
return q**(enddatum-basisdatum) * _barwert(zins, daten, zahlungen, basisdatum, enddatum, startzahlung, endzahlung)

def zinsfuss(daten, zahlungen, basisdatum, enddatum, startzahlung, endzahlung):
basisdatum = basisdatum
daten = [d[0] for d in daten]
zahlungen = [z[0] for z in zahlungen]
pos = False
neg = False
for z in zahlungen:
	if z > 0:
		pos = True
	if z < 0:
		neg = True
	if pos and neg:
		break
if not (pos and neg):
	return "Es müssen positive und negative Zahlungen enthalten sein!"

i0 = 0.05
i1 = 0.06
b0 = _barwert(i0, daten, zahlungen, basisdatum, enddatum, startzahlung, endzahlung)
if not isinstance(b0, float):
	return b0
if b0 < 0:
	i1 = 0.04
maxiter = 10000
epsilon = 0.0001
i = 0
while abs(i0-i1) > epsilon and i < maxiter:
	b1 = _barwert(i1, daten, zahlungen, basisdatum, enddatum, startzahlung, endzahlung)
	if b0 == b1:
		return 0
	if not isinstance(b1, float):
		return b1
	i2 = i0 - b0 / (b1-b0) * (i1-i0)
	if i2 < -1.0:
		return -1 #str((-1, i0, i1, i2, b0, b1))
	if i2 > 1000000:
		if b0 < 0:
			return -1
		return "∞"
	b0 = b1
	i0 = i1
	i1 = i2
	i += 1
if i == maxiter:
	return "∞"
return i1

Hier auch nochmal zum herunterladen (Achtung, noch umbenennen!):

zinsfuss.py.txt

 

 

Man sieht, es gibt hier auch Formeln für Barwert und Endwert. Wer möchte, kann sie sich auch verfügbar machen. Und das geht wie folgt: Man öffnet ein Spreadsheet seiner Wahl und legt dort unter "Extras -> Makros -> Makros verwalten -> LibreOffice Basic" ein neues Modul an, wahlweise im Dokument selbst oder global. Das neue Modul öffnet man dann und den Quellcode aus folgender Datei hinein:

basic-quellcode.txt

(Wenn man den Code hier darstellt werden leider die Sonderzeichen verunglimpft. Deshalb nur zum Download.)

 

Nun hat man die Funktionen barwert(), endwert() und zinsfuss() als Tabellenformeln zur Verfügung.

 

So, ich hoffe für den Einen oder Anderen ist es nützlich.

 

Viel Spaß damit

 

P.S.

Hier nochmal ein Spreadsheet mit einem Minimalbeispiel. Basic-Makro bereits drin, das Python Skript fehlt noch. Nachdem Ihr sicherheitshalber einmal den Quelltext angesehen habt, ob ich Euch 'nen Virus unterschiebe könnt Ihr beim zweiten Öffnen dann die "Makros aktivieren" :-)

zinsfussbeispiel.ods

 

Typische Probleme:

• Wirklich eine Matrixformel genommen?
• Matrixformeln lassen sich nicht "ziehen", sie müssen in neue Zellen kopiert werden.
• Datum als Datum formatiert?
• ... to be completed ;-)

[Schinzilord]

Top!

Ich werds am WE ausprobieren.

Kannst du bitte noch ein einfaches Calc Sheet hochladen mit deinem Beispiel?

[Schinzilord]

Ich habs doch gleich probiert:

Das zinsfuss.py Skript habe ich nach

C:\Users\XYZXYZXYZXYZX\AppData\Roaming\LibreOffice\4\user\scripts\python

kopiert.

 

Als ich das Makro gespeichert habe und ausführen lassen wollte,

kommt Fehlermeldung in der ersten Funktion invokePyFunc:

Basic Syntax Error: unexpected symbol: ?

 

Windows 7, Libreoffice 4.1.0.4

 

Any ideas?

[hatnix]

Ich habs doch gleich probiert:

Das zinsfuss.py Skript habe ich nach

C:\Users\XYZXYZXYZXYZX\AppData\Roaming\LibreOffice\4\user\scripts\python

kopiert.

 

Als ich das Makro gespeichert habe und ausführen lassen wollte,

kommt Fehlermeldung in der ersten Funktion invokePyFunc:

Basic Syntax Error: unexpected symbol: ?

 

Windows 7, Libreoffice 4.1.0.4

 

Any ideas?

 

Ja, Basic-Quelltext beim Kopieren verunglimpft. Problem hatte ich auch gerade beim Testen. Habe deshalb den Ausgangspost geändert und auch ein Beispiel ergänzt.

[Schinzilord]

Passt. funzt einwandfrei

Quellcode ist clean, wenn ich das laienhaft bestätigen darf

Das eröffent doch neue, kompaktere Möglichkeiten.

Aktuell habe ich halt für jedes Wertpapier eine eigene Spalte mit den Buchungen.

[PopOff]

Hi,

 

wollte mal nachfragen obman so ein Makro auch für MS Excel erstellen kann?

[hatnix]

Man kann. Da MS Excel aber nur VBA unterstützt, müsste man die paar Zeilen in VBA nachimplementieren.

Aber VBA mag ich nicht, also muss sich dafür jemand anderes finden ;-)

[PopOff]

Ist hier jmd der das kann? Suche eine solche Funktion schon länger für Excel

[sparfux]

Gibt es doch in Excel schon: xintzinsfuss bzw. in Englisch Xirr

 

Irgendein Modul oder so muss dafür aber noch aktiviert werden.

[PopOff]

Mir ist schon klar dass es in Excel die Funktion xintzinsfuss gibt

Allerdings kann man mit dem Makro von hatnix auswählen von welcher "InvestitionsID" man den Internen Zinsfuß wissen möchte. Hier kann man alle Zu und Verkäufe in einer Spalte auflisten und gezielt von einem Wertpapier den irr berechnen. Die Funktion xintzinsfuss gibt nur den gesamten irr von allen Wertpapieren an.

[sparfux]

Mir ist schon klar dass es in Excel die Funktion xintzinsfuss gibt

Allerdings kann man mit dem Makro von hatnix auswählen von welcher "InvestitionsID" man den Internen Zinsfuß wissen möchte. Hier kann man alle Zu und Verkäufe in einer Spalte auflisten und gezielt von einem Wertpapier den irr berechnen. Die Funktion xintzinsfuss gibt nur den gesamten irr von allen Wertpapieren an.

OK, sorry. Missverständnis.

 

Ich mache sowas ähnliches aber auch in meinen Excel-Auswertungen ohne VB-Makro oder ähnliches.

 

Ich baue mir dazu eine Hilfsspalte mittels der "Investment-IDs" (bei mir ein wenig anders, sollte aber so auch gehen), den gewünschten Start- und Endwerten zusammen und wende darauf dann wieder XIRR eindimensional (sprich ohne Matrix an). Ist zwar ein wenig von hinten durch die Brust. Funzt aber einwandfrei.

[PopOff]

Mir ist schon klar dass es in Excel die Funktion xintzinsfuss gibt

Allerdings kann man mit dem Makro von hatnix auswählen von welcher "InvestitionsID" man den Internen Zinsfuß wissen möchte. Hier kann man alle Zu und Verkäufe in einer Spalte auflisten und gezielt von einem Wertpapier den irr berechnen. Die Funktion xintzinsfuss gibt nur den gesamten irr von allen Wertpapieren an.

OK, sorry. Missverständnis.

 

Ich mache sowas ähnliches aber auch in meinen Excel-Auswertungen ohne VB-Makro oder ähnliches.

 

Ich baue mir dazu eine Hilfsspalte mittels der "Investment-IDs" (bei mir ein wenig anders, sollte aber so auch gehen), den gewünschten Start- und Endwerten zusammen und wende darauf dann wieder XIRR eindimensional (sprich ohne Matrix an). Ist zwar ein wenig von hinten durch die Brust. Funzt aber einwandfrei.

 

Wie setzt du dies ohne Makro und Matrix um? Ich als Amateur Excel Spezialist habe dies zwar auch versucht aber bin nie weitergekommen da xintzinsfuss ja nur zwei Spalten zulässt.

Wäre nett von dir wenn du eine kleine Beispieldatei hochladen könntest. Ich könnte dies gut gebrauchen.

[sparfux]

Ein Beispiel ist mir zu viel Arbeit ehrlich gesagt.

Du musst einfach eine zusätzliche Hilfsspalte erzeugen, mit der Du Dir die gewünschte Spalte für die "Werte" für XIRR zusammen baust (über Formeln in dieser Hilfsspalte). Nichts anderes macht das LibreOffice Makro intern, wenn ich das richtig verstehe. Dann rufst Du eben XIRR mit XIRR($Hilfsspalte; $Datumsspalte) auf.

[PopOff]

Ein Beispiel ist mir zu viel Arbeit ehrlich gesagt.

Du musst einfach eine zusätzliche Hilfsspalte erzeugen, mit der Du Dir die gewünschte Spalte für die "Werte" für XIRR zusammen baust (über Formeln in dieser Hilfsspalte). Nichts anderes macht das LibreOffice Makro intern, wenn ich das richtig verstehe. Dann rufst Du eben XIRR mit XIRR($Hilfsspalte; $Datumsspalte) auf.

 

Hab ich mal probiert allerdings kommt beim internen Zinsfuß nicht der korrekte Wert raus. Evtl. könntest du ja mal drüberschauen

Versuch.xlsx

[Ramstein]

Ist doch logisch, da der Endewert in Zeile 25 durch das Wenn(...) nie in die Berechnung einbezogen wird.

[sparfux]

Aber so in der Art meinte ich das schon.

 

Den Start und den Endwert kannst Du mit der Funktion "indirekt" rausziehen. Allerdings ist es dann ungünstig, wenn Du 2x das gleiche Datum in der Tabelle hast. Ich meine Zeile 24 & 25.

[Ramstein]

Den Endewert kann er nicht rausziehen, der muss für jede InvestitionsID angegeben werden.

[sparfux]

Kommt drauf an was man macht. Ich kann den Endwert bei mir mit "indirekt" raus ziehen, weil ich zu jedem Datum einen eindeutigen Depotstand habe. Der Endwert ist ja der jetzige Wert des Wertpapiers. Wenn er irgndwo eine Liste der täglichen Kurse (* Anazahl der Papiere) mit dem zugehörigen Datum hat kann er sich den Endwert mit "inidrekt" und der Datumsangabe in G15 oder G16 holen.

 

Falls er so eine Wertpapierkursreihe nicht hat, geht das natürlich nicht und er muss den Endwert manuell dazu pfriemeln.

[PopOff]

Ja, ich habe für jede InvestitionsID den Endwert bzw. Startwert. Habe diese nur mal manuell in I15 und J 15 angegeben.

Nur weiß ich nicht wie die Formel in Excel aussehen soll damit xintzinsfuss auch die Werte in I15 und J15 als Start und Endwerte erkennt.

Versuch-2.xlsx

[Ramstein]

Ja, ich habe für jede InvestitionsID den Endwert bzw. Startwert. Habe diese nur mal manuell in I15 und J 15 angegeben.

Nur weiß ich nicht wie die Formel in Excel aussehen soll damit xintzinsfuss auch die Werte in I15 und J15 als Start und Endwerte erkennt.

Du musst 2 Endwertzeilen in die andere Tabelle einfügen.