《解决XIRR与XNPV的问题》由会员分享，可在线阅读，更多相关《解决XIRR与XNPV的问题（8页珍藏版）》请在金锄头文库上搜索。
1、百度文库解决XIRR与XNPV*的问题2007. 10. 15概述XNPV和XIRR如何工作，你可以参考本书第1和33章。这些函数解决了一个重要 问题（当现金流是日期时间时的折现），但它们一个数字问题。下面我们讨论这些问题。我们定义两个新函数NXIRR和NXNPV来解决这些问题。 新函数显示在本书文档的电子表中；如何在你的电子表插 入这些函数，参见本书光盘中 Getformula “添加”文档。XNPV的问题：0折现率XNPV,如下所示,只有正的折现率才能工作ABC1XNPV与XIRR的问题2折现率22.00%3净现值65.1637-=XNPV(B2,B6:B12,A6:A12)45日期现金流
2、630-Ju n-07-500714-Feb-08100814-Feb-09300914-Feb-104001014-Feb-116001114-Feb-128001214-Feb-13-1,800但是，0或负数折现率，XNPV不工作： / / / / / / /*rdThis note, written by Simon Benninga () and Benjamin Czaczkes ( ) accompaniesFinancial Modeling , 3 edition.Although it is copyrighted, we authorize its replication,
3、 provided full name credit is given the authors.ABC1XNPV的问题0或负数折现率XNPV不工作2折现率0.00%3净现值#NUM!-=XNPV(B2,B6:B12,A6:A12)45日期现金流630-Ju n-07-500714-Feb-08100814-Feb-09300914-Feb-104001014-Feb-116001114-Feb-128001214-Feb-13-1,800快速解决折现率为0的问题针对折现率为0的问题,快速解决方法是增加IF函数:AIBC1快速解决XNPV2折现率0.00%3净现值-100.000,XNPV(B2
4、,B6：B12,A6：A12),IF(B2=0,SUM(B6：B12),”neg rate)45日期现金流630-Jun-07-500714-Feb-08100814-Feb-09300914-Feb-10400：10 -14-Feb-116001114-Feb-128001214-Feb-13-1,800新函数NXNPV是更好的解决方法。问题：XIRR对两个IRR不工作Guess打开XIRR不工作.见下面的问题:#百度文库101112131415XIRR的问题折现率 净现值IRR22.00%65.16366#NUM!#NUM!#NUM!-=XIRR(B9:B15,A9:A15,B17) ,
5、no Guess-=XIRR(B9:B15,A9:A15,35%), Guess = 35%-=XIRR(B9:B15,A9:A15,5%) , Guess = 5%日期现金流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作为折现率的函数-=B3,模拟运算表头Rate0.1%2.5%4.9%7.3%65.16366 -97.36235 -42.66612 -2.16664827.0191118192
6、021222324252627282930313233341617-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函数没有识别出来（B4:B6）.XNPV和XIRR重新编程在解决XNPV与XIRR的问题的电子表中，我们定义了两
7、个新函数 ，NXNPV和NXIRR 解决该问题。ABC1解决XNPV与XIRR的问题 新的函数称为NXNPV和NXIRR2折现率0.00%3净现值-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%) , Guess = 5%78日期/现金流930-Ju n-07-5001014-Feb-081001114-Feb-093001214-Feb
8、-104001314-Feb-116001414-Feb-128001514-Feb-13-1,800注意B3,我们计算0折现率的NPV。在B4:B6中我们说明了 IRR的新的计算函数 我们的NXNPV函数甚至对负的折现率也工作：ABC1解决XNPV与XIRR的问题 新的函数称为NXNPV和NXIRR2折现率-3.00%3净现值-195.0258-=n XNPV(B2,B9:B15,A9:A15)4IRR5.05%-=nXIRR(B9:B15,A9:A15,B17) , no Guess538.79%-=n XIRR(B9:B15,A9:A15,35%), Guess = 35%65.05%-
9、=n XIRR(B9:B15,A9:A15,5%) , Guess = 5%78日期现金流930-Ju n-07-5001014-Feb-081001114-Feb-093001214-Feb-104001314-Feb-116001414-Feb-128001514-Feb-13-1,800#NXNP与NXIRR编程该 VBA 代码(由 Benjamin Czaczkes开发)是：NXNPVThe function NXNPV also forms the basis ofNXIRR below:NXNPV solves some problems in the XNPV fun cti o
10、nWritte 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(xlErrNum)Exit Fun cti onEnd IfDim aValuesDim aDatesDim tempsum As DoubleDim r As DoubleDim dd As LongDim i
11、 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 onFor NXIRROur new function NXIRR uses a slightly different version of the time-dated NPV function(ca
12、lled 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 = 0dd = aDates(1, 1)For i = 1 To Dsizetempsum = tempsum + aValues(i, 1) / r a (aDates(i, 1) - dd) /
13、 365)Next iannpv = tempsumEnd Fun cti onNXIRR solves some problems in the XIRR functionIt uses the ANNPV function aboveWritte n by Benjamin Czaczkes, summer 2007Function NXIRR(Values As 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 Boo
