Das Portal zum Thema Excel-Formeln

Die Funktionen NBW und IKV unterscheiden nicht zwischen Soll- und Habenzinssätzen. Außerdem wird beim IKV unterstellt,
daß Ein- bzw. Auszahlungsüberschüsse während der Laufzeit zum internen Zinsfuß angelegt bzw, aufgenommen werden können.

Die Funktion QIKV berechnet ein modifiziertes Modell*) des IKV, in dem zwischen Soll- und Habenzinsen unterschieden wird.

Eine analoge Erweiterung der NBW-Funktion, also eine Differenzierung zwischen Soll- und Habenzinsen bei Ermittlung
des Nettobarwertes stellt Excel nicht zur Verfügung.

Wie kann man trotzdem den Barwert einer Zahlungreihe bei unterschiedlichen Soll- und Haben-Zinssätzen berechnen ?

A               B               C               D               E        1 P  CF    S-Zins  6%   2 1  -1000    H-Zins  5%   3 2  -200    Perioden  6   4 3  700    fällig  0   5 4  -50    NBW  QNBW   6 5  500    245,9256  283,1784   7 6  600

Lösung

In B2 bis B7 stehen die Zahlungen
in E1 steht der Soll-Zinssatz (für Investitionen)
In E2 steht der Haben-Zinssatz (für Reinvestitionen)
In E3 steht die Anzahl an Zahlungsperioden
In E4 steht die Fälligkeit, 0=nachschüssig (wie NBW) 1=vorschüssig

in D6 steht der klassische Kapitalwert (NBW) =NBW((E1+E2)/2;B2:B7) mit einem Durchschnittszinssatz von E1 und E2
in E6 steht der modifzierte Kapitalwert

{=SUMME(B2:B7/(1+WENN(B2:B7<0;E1;E2))^(ZEILE(INDIREKT("1:6"))-E4))}

soll die Anzahl Perioden dynamisch sein, dann:

{=SUMME(BEREICH.VERSCHIEBEN(B2;;;E3)/(1+WENN(BEREICH.VERSCHIEBEN(B2;;;E3)<0;E1;E2))^(ZEILE(INDIREKT("1:"&E3))-E4))}

Erläuterung

*) Wie wird der QIKV überhaupt berechnet ?

Die Zahlungsreihe ={-1000;-200;700;-50;500;600} ergibt einen IKV von 12,1%

=QIKV({-1000;-200;700;-50;500;600};6%;5%) ergibt 9,5%

Der QIKV geht dabei wie folgt vor:

In einem 1. Zwischenschritt werden alle negativen Zahlungen mit dem S-Zins auf Po abgezinst,
alle positiven Zahlungen mit dem H-Zins auf Pn aufgezinst.
es ergibt sich also die Zahlungsreihe

={-1000;-188,68;810,34;-41,98;525;600}(gerundet)

dann addiert man alle negativen Zahlungen in Po und alle positiven in Pn und erhält

={-1230,66;0;0;0;0;1935,34}

Bei dieser transformierten Zahlungsreihe sind IKV und QIKV identisch:

=QIKV({-1230,66;0;0;0;0;1935,34};6%;5%)=9,48%

und

=IKV({-1230,66;0;0;0;0;1935,34})=9,48%