解决XIRR与 XNPV的问题
PROBLEM WITH XNPVXNPV does not work with zero or negative discount ratesDiscount rate 0.00%Net present value #NUM! 0,XNPV(B2,B6:B12,A6:A12),IF(B2=0,SUM(B6:B12),neg rate)QUICK FIX FOR XNPVC123456789101112快速解决XNPV的问题折现率 0.00%净现值 -100.00日期 现金流30-六月-07 -50014-二月-08 10014-二月-09 30014-二月-10 40014-二月-11 60014-二月-12 80014-二月-13 -1,800A B1234567891011120,XNPV(B2,B6:B12,A6:A12),IF(B2=0,SUM(B6:B12),neg rate)快速解决XNPV的问题C123456789101112PROBLEMS WITH XIRRDiscount rate 22.00%Net present value 65.16366 - =XNPV(B2,B9:B15,A9:A15)IRR #NUM! - =XIRR(B9:B15,A9:A15,B17) , no Guess#NUM! - =XIRR(B9:B15,A9:A15,35%), Guess = 35%#NUM! - =XIRR(B9:B15,A9:A15,5%) , Guess = 5%Date Cash flow Data table: XNPV as function of discount rate30-六月-07 -50014-二月-08 100 Rate14-二月-09 300 0.1%14-二月-10 400 2.5%14-二月-11 600 4.9%14-二月-12 800 7.3%14-二月-13 -1,800 9.7%12.1%14.5%16.9%19.3%21.7%24.1%26.5%28.9%31.3%33.7%36.1%38.5%40.9%X N P V a s F u n c tio n o f D is c o u n t r a te4 2 %3 9 %3 6 %3 3 %3 0 %2 7 %2 4 %2 1 %1 8 %1 5 %1 2 %9 %6 %3 %0 %- 1 2 0- 1 0 0- 8 0- 6 0- 4 0- 2 002 04 06 08 0A B C D1234567891011121314151617181920212223242526272829303132333465.16366 - =B3, data table header-97.3624-42.6661-2.1666527.0191147.188960.1793767.4624170.2202569.4043665.7816359.9709952.4723443.689633.9491423.5144812.598131.37118-10.029Data table: XNPV as function of discount rateE F12345678910111213141516171819202122232425262728293031323334XIRR的问题折现率 22.00%净现值 65.16366 - =XNPV(B2,B9:B15,A9:A15)IRR #NUM! - =XIRR(B9:B15,A9:A15,B17) , no Guess#NUM! - =XIRR(B9:B15,A9:A15,35%), Guess = 35%#NUM! - =XIRR(B9:B15,A9:A15,5%) , Guess = 5%日期 现金流 模拟运算表: 作为XNPV的函数30-六月-07 -50014-二月-08 100 Rate14-二月-09 300 0.1%14-二月-10 400 2.5%14-二月-11 600 4.9%14-二月-12 800 7.3%14-二月-13 -1,800 9.7%12.1%14.5%16.9%19.3%21.7%24.1%26.5%28.9%31.3%33.7%36.1%38.5%40.9%X N P V 作 为 折 现 率 函 数4 2 %3 9 %3 6 %3 3 %3 0 %2 7 %2 4 %2 1 %1 8 %1 5 %1 2 %9 %6 %3 %0 %- 1 2 0- 1 0 0- 8 0- 6 0- 4 0- 2 002 04 06 08 0A B C D1234567891011121314151617181920212223242526272829303132333465.16366 - =B3, 表头-97.3624-42.6661-2.1666527.0191147.188960.1793767.4624170.2202569.4043665.7816359.9709952.4723443.689633.9491423.5144812.598131.37118-10.029模拟运算表: 作为XNPV的函数E F12345678910111213141516171819202122232425262728293031323334FIXING PROBLEMS WITH XNPV AND XIRRThe new functions are called NXNPV and NXIRRDiscount rate -3.00%Net present value -195.026 - =nXNPV(B2,B9:B15,A9:A15)IRR 5.05% - =nXIRR(B9:B15,A9:A15,B17) , no Guess38.79% - =nXIRR(B9:B15,A9:A15,35%), Guess = 35%5.05% - =nXIRR(B9:B15,A9:A15,5%) , Guess = 5%Date Cash flow Data table: XNPV as function of discount rate30-六月-07 -50014-二月-08 100 Rate14-二月-09 300 0.0%14-二月-10 400 2.5%14-二月-11 600 4.9%14-二月-12 800 7.3%14-二月-13 -1,800 9.7%12.1%14.5%16.9%19.3%21.7%24.1%26.5%28.9%31.3%33.7%36.1%38.5%40.9%X N P V a s F u n c tio n o f D is c o u n t r a te4 2 %3 9 %3 6 %3 3 %3 0 %2 7 %2 4 %2 1 %1 8 %1 5 %1 2 %9 %6 %3 %0 %- 1 2 0- 1 0 0- 8 0- 6 0- 4 0- 2 002 04 06 08 0A B C D12345678910111213141516171819202122232425262728293031323334-195.026 - =B3, data table header-100-42.6661-2.1666527.0191147.188960.1793767.4624170.2202569.4043665.7816359.9709952.4723443.689633.9491423.5144812.598131.37118-10.029Data table: XNPV as function of discount rateE F12345678910111213141516171819202122232425262728293031323334解决XNPV与XIRR的问题新函数是NXNPV和NXIRR折现率 -3.00%净现值 -195.026 - =nXNPV(B2,B9:B15,A9:A15)IRR 5.05% - =nXIRR(B9:B15,A9:A15,B17) , no Guess38.79% - =nXIRR(B9:B15,A9:A15,35%), Guess = 35%5.05% - =nXIRR(B9:B15,A9:A15,5%) , Guess = 5%日期 现金流 模拟运算表: 作为XNPV的函数30-六月-07 -50014-二月-08 100 Rate14-二月-09 300 0.0%14-二月-10 400 2.5%14-二月-11 600 4.9%14-二月-12 800 7.3%14-二月-13 -1,800 9.7%12.1%14.5%16.9%19.3%21.7%24.1%26.5%28.9%31.3%33.7%36.1%38.5%40.9%X N P V 作 为 折 现 率 函 数4 2 %3 9 %3 6 %3 3 %3 0 %2 7 %2 4 %2 1 %1 8 %1 5 %1 2 %9 %6 %3 %0 %- 1 2 0- 1 0 0- 8 0- 6 0- 4 0- 2 002 04 06 08 0A B C D12345678910111213141516171819202122232425262728293031323334-195.026 - =B3, 表头-100-42.6661-2.1666527.0191147.188960.1793767.4624170.2202569.4043665.7816359.9709952.4723443.689633.9491423.5144812.598131.37118-10.029模拟运算表: 作为XNPV的函数E F12345678910111213141516171819202122232425262728293031323334
