Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Finanzmathematik

Beitrag: Zinssatz bei abweichenden Zahlungs- und Verrechnungsterminen *

Aufgabe
Über eine bestimmte Laufzeit soll ein einmaliger Anlagebetrag und zusätzlich regelmäßige Einzahlungen verzinst werden. Die Termine der Zahlungen und der Zinseszinsverrechnung weichen voneinander ab.
Zahlungen und Endwert sind bekannt. Rückgerechnet werden soll auf den Zinssatz. Die Funktion ZINS kann den Zinssatz standardmäßig nur berechnen wenn Zahlungen und Verrechnungszeitpunkte identisch sind.
Autor: Ulrich Schneider


A B
1 Anfangskap 0
2 Rate 1.000,00
3 Jahre 15
4 Zahlung p.a. 12
5 Fälligkeit 1
6 Endkapital 300.000,00
7
8 KnKorr 306.500,00
9 KoKorr 6500
10 Rate 12000
11
12 Zinssatz 6,4836%
13 Zinssatz 6,4836%
14
15 Endw. (Probe) 300.000,00


Lösung
Die Zellen B1:B6 sind die Eingabefelder
Die Fälligkeit in B5 ist 1 für vorschüssig und -1 für nachschüssig

B8:=B6+B2*B5/2+B2*B4/2
B9:=B1+B2*B5/2+B2*B4/2
B10:=B2*B4

Das Ergebnis in B12 lautet
=ZINS(B3;-B10;-B9;B8;0)

ohne die Hilfszellen B8:B10
=ZINS(B3;-B2*B4;-(B1+B2*B5/2+B2*B4/2);B6+B2*B5/2+B2*B4/2;0)

Zur Kontrolle wird in B15 nochmal der in B6 vorgegebene Endwert ermittelt (ohne Anfangskapital):
=(B4*B2+(B4+B5)/2*B2*B12)*((1+B12)^B3-1)/B12
oder
=-ZW(B12;B3;B2*B4+B2*(B4+B5)/2*B12;0;0)

Erläuterung
Hallo alle Interessierte - hier die mathematische Herleitung:
Grundformel für Kapitalaufbau ohne Zinseszins innerhalb eines Jahres (Kombination von arithmetischer Reihe innerhalb eines Jahres und geometrischer Reihe über die Jahre) â?? mit der Anwendung dieser Formel habe ich jahrelang landwirtschaftliche Fachschüler geärgert! (Ulrich Schneider)

P=Zinssatz, q= 1+p/100; R= regelmäßige Rate, K0 = Anfangskapital, n = Laufzeit in Jahren, m = Anzahl der Zahlungen pro Jahr, Kn = Endkapital, v =1 für vorschüssig und â??1 für nachschüssig

Grundformel lautet:

Kn= K0*q^n + R [m+p(m+v)/200]*(q^n-1)/(q-1)

Und jetzt die Umstellung, um mit ZINS rechnen zu können:

Kn = K0*q^n + R*m*(q^n-1)/(q-1)+R*p*m/200*(q^n-1)/(q-1)+R*v/200*(q^n-1)/(q-1)

Kn = K0*q^n + R*m*(q^n-1)/(q-1)+R*m/2*(q^n-1)+R*v/2*(q^n-1)

Kn = K0*q^n + R*m*(q^n-1)/(q-1)+R*m/2*q^n- R*m/2+R*v/2*q^n- R*v/2

Kn + R*m/2 + R*v/2= K0*q^n +R*m/2*q^n+R*v/2*q^n + R*m*(q^n-1)/(q-1)

Kn + R*m/2 + R*v/2=(K0+ R*m/2 + R*v/2) *q^n + R*m*(q^n-1)/(q-1)

Und mit Knkorr = Kn + R*m/2 + R*v/2, K0korr = K0+ R*m/2 + R*v/2 und Rkorr=R*m

erhält man die "klassische" Formel

KnKorr=K0Korr*q^n+ RKorr*(q^n-1)/(q-1)

die man dann mit der Excel-Funktion ZINS auflösen kann.