解決XIRR與XNPV的問題

1、百度文庫解決XIRR與XNPV*的問題2007. 10. 15概述XNPV和XIRR如何工作，你可以參考本書第1和33章。這些函數(shù)解決了一個重要 問題（當現(xiàn)金流是日期時間時的折現(xiàn)），但它們一個數(shù)字問題。下面我們討論這些問題。我們定義兩個新函數(shù)NXIRR和NXNPV來解決這些問題。 新函數(shù)顯示在本書文檔的電子表中；如何在你的電子表插 入這些函數(shù)，參見本書光盤中 Getformula “添加”文檔。XNPV的問題：0折現(xiàn)率XNPV,如下所示,只有正的折現(xiàn)率才能工作ABC1XNPV與XIRR的問題2折現(xiàn)率22.00%3凈現(xiàn)值65.1637<-=XNPV(B2,B6:B12,A6:A12)45日

2、期現(xiàn)金流630-Ju n-07-500714-Feb-08100814-Feb-09300914-Feb-104001014-Feb-116001114-Feb-128001214-Feb-13-1,800但是，0或負數(shù)折現(xiàn)率，XNPV不工作： / / / / / / /*rdThis note, written by Simon Benninga () and Benjamin Czaczkes ( ) accompaniesFinancial Modeling , 3 edition.Although it is copyrighted, we authorize its replicat

3、ion, provided full name credit is given the authors.ABC1XNPV的問題0或負數(shù)折現(xiàn)率XNPV不工作2折現(xiàn)率0.00%3凈現(xiàn)值#NUM!<-=XNPV(B2,B6:B12,A6:A12)45日期現(xiàn)金流630-Ju n-07-500714-Feb-08100814-Feb-09300914-Feb-104001014-Feb-116001114-Feb-128001214-Feb-13-1,800快速解決折現(xiàn)率為0的問題針對折現(xiàn)率為0的問題,快速解決方法是增加IF函數(shù):AIBC1快速解決XNPV2折現(xiàn)率0.00%3凈現(xiàn)值-100.00&

4、lt;-=IF(B2>0,XNPV(B2,B6：B12,A6：A12),IF(B2=0,SUM(B6：B12),”neg rate")45日期現(xiàn)金流630-Jun-07-500714-Feb-08100814-Feb-09300914-Feb-10400：10 -14-Feb-116001114-Feb-128001214-Feb-13-1,800新函數(shù)NXNPV是更好的解決方法。問題：XIRR對兩個IRR不工作Guess打開XIRR不工作.見下面的問題:3百度文庫101112131415XIRR的問題折現(xiàn)率 凈現(xiàn)值IRR22.00%65.16366#NUM!#NUM!#NUM

5、!<-=XIRR(B9:B15,A9:A15,B17) , no Guess<-=XIRR(B9:B15,A9:A15,35%), Guess = 35%<-=XIRR(B9:B15,A9:A15,5%) , Guess = 5%日期現(xiàn)金流30-Jun-0714-Feb-0814-Feb-0914-Feb-1014-Feb-1114-Feb-1214-Feb-13-1,800-500100300400600800<-=XNPV(B2,B9:B15,A9:A15)模擬運算表:XNPV作為折現(xiàn)率的函數(shù)<-=B3,模擬運算表頭Rate0.1%2.5%4.9%7.3%65

6、.16366 -97.36235 -42.66612 -2.16664827.0191118192021222324252627282930313233341617-1209.7%12.1%14.5%16.9%19.3%47.188960.1793767.4624170.2202569.4043621.7%24.1%26.5%28.9%31.3%33.7%36.1%38.5%40.9%23.5144812.598131.37118-10.0290565.7816359.9709952.4723443.689633.94914模擬運算表顯示有兩個IRR（約為5%和39%）但XIRR函數(shù)沒有識別出

7、來（B4:B6）.XNPV和XIRR重新編程在解決XNPV與XIRR的問題的電子表中，我們定義了兩個新函數(shù) ，NXNPV和NXIRR 解決該問題。ABC1解決XNPV與XIRR的問題 新的函數(shù)稱為NXNPV和NXIRR2折現(xiàn)率0.00%3凈現(xiàn)值-100<-=n XNPV(B2,B9:B15,A9:A15)4IRR5.05%<-=n XIRR(B9:B15,A9:A15,B17) , no Guess538.79%<-=nXIRR(B9:B15,A9:A15,35%), Guess = 35%65.05%<-=n XIRR(B9:B15,A9:A15,5%) , Gues

8、s = 5%78日期/現(xiàn)金流930-Ju n-07-5001014-Feb-081001114-Feb-093001214-Feb-104001314-Feb-116001414-Feb-128001514-Feb-13-1,800注意B3,我們計算0折現(xiàn)率的NPV。在B4:B6中我們說明了 IRR的新的計算函數(shù) 我們的NXNPV函數(shù)甚至對負的折現(xiàn)率也工作：ABC1解決XNPV與XIRR的問題 新的函數(shù)稱為NXNPV和NXIRR2折現(xiàn)率-3.00%3凈現(xiàn)值-195.0258<-=n XNPV(B2,B9:B15,A9:A15)4IRR5.05%<-=nXIRR(B9:B15,A9:

9、A15,B17) , no Guess538.79%<-=n XIRR(B9:B15,A9:A15,35%), Guess = 35%65.05%<-=n XIRR(B9:B15,A9:A15,5%) , Guess = 5%78日期現(xiàn)金流930-Ju n-07-5001014-Feb-081001114-Feb-093001214-Feb-104001314-Feb-116001414-Feb-128001514-Feb-13-1,8008NXNP與NXIRR編程該 VBA 代碼(由 Benjamin Czaczkes開發(fā))是：NXNPVThe function NXNPV al

10、so forms the basis ofNXIRR below:'NXNPV solves some problems in the XNPV fun cti on'Writte n by Benjamin Czaczkes, summer 2007Fun cti on nxn pv(Rate As Double, Values As Ran ge, Dates As Ran ge)Dim Dsize As In tegerDim Vsize As In tegerDsize = = Dsize <> Vsize The nnxnpv = CVErr(xlErrN

11、um)Exit Fun cti onEnd IfDim aValuesDim aDatesDim tempsum As DoubleDim r As DoubleDim dd As LongDim i As In tegeraValues = ValuesaDates = Datesr = 1 + Ratetempsum = 0dd = aDates(1, 1)For i = 1 To Dsizetempsum = tempsum + aValues(i, 1) / r A (aDates(i, 1) - dd) / 365)Next inxnpv = tempsumEnd Fun cti o

12、nFor NXIRROur new function NXIRR uses a slightly different version of the time-dated NPV function(called ANNPV )Private Fun cti on ann pv(Rate As Double, aValues, aDates)Dim Dsize As In tegerDsize = UBou nd(aDates)Dim tempsum As DoubleDim r As DoubleDim dd As LongDim i As In tegerr = 1 + Ratetempsum

13、 = 0dd = aDates(1, 1)For i = 1 To Dsizetempsum = tempsum + aValues(i, 1) / r a (aDates(i, 1) - dd) / 365)Next iannpv = tempsumEnd Fun cti on'NXIRR solves some problems in the XIRR function'It uses the ANNPV function above'Writte n by Benjamin Czaczkes, summer 2007Function NXIRR(Values As

14、 Range, Dates As Range, Optional Guess As Double = Const epsil on As Double =Dim D As DoubleDim V As DoubleDim oldV As DoubleDim r As DoubleDim Change As Boolea nDim i As In tegerDim StopNow As Boolea nDim Dsize As In tegerDim Vsize As In tegerDsize = Vsize = If Dsize <> Vsize The nNXIRR = CVErr(xlErrNum)Exit Fun cti onEnd IfDim aValuesDim aDatesaValues = ValuesaDates = Datesr = GuessD =oldV = ann pv(r, aValues, aDates)StopNow = Abs(oldV) < epsilo ni = 1Do Un til