Zero or negative rates and XNPV?
 

Hi,

Does anybody know why XNPV appears not to accept negative rates?

e.g. using the example in the Excel 2003 help: (i.e. amounts = -10000,
2750, 4250, 3250, 2750, dates = 01 Jan 2008, 01 Mar 2008, 30 Oct 2008,
15 Feb 2009, 01 Apr 2009) returns #NUM! for rates 0 or below. That is
=XNPV(0,amounts, dates). However when I composed a VBA function to
duplicate the formula for XNPV, as shown in the help, the value is 3000
when the rate is zero.

thanks,
L.

Zero or negative rates and XNPV?
 

=IF(A1-B1<0,"-","")&TEXT(ABS(A1-B1),"hh:mm")

If you want to calculate with it, use just =A1-B1. It will not show the way you want, but further calculations are correct.

--
Kind regards,

Niek Otten

"LAHM" wrote in message oups.com...
Hi,

Does anybody know why XNPV appears not to accept negative rates?

e.g. using the example in the Excel 2003 help: (i.e. amounts = -10000,
2750, 4250, 3250, 2750, dates = 01 Jan 2008, 01 Mar 2008, 30 Oct 2008,
15 Feb 2009, 01 Apr 2009) returns #NUM! for rates 0 or below. That is
=XNPV(0,amounts, dates). However when I composed a VBA function to
duplicate the formula for XNPV, as shown in the help, the value is 3000
when the rate is zero.

thanks,
L.

Zero or negative rates and XNPV?
 

Sorry, posted reply to another question!
--
Kind regards,

Niek Otten

"Niek Otten" wrote in message ...
=IF(A1-B1<0,"-","")&TEXT(ABS(A1-B1),"hh:mm")

If you want to calculate with it, use just =A1-B1. It will not show the way you want, but further calculations are correct.

--
Kind regards,

Niek Otten

"LAHM" wrote in message oups.com...
Hi,

Does anybody know why XNPV appears not to accept negative rates?

e.g. using the example in the Excel 2003 help: (i.e. amounts = -10000,
2750, 4250, 3250, 2750, dates = 01 Jan 2008, 01 Mar 2008, 30 Oct 2008,
15 Feb 2009, 01 Apr 2009) returns #NUM! for rates 0 or below. That is
=XNPV(0,amounts, dates). However when I composed a VBA function to
duplicate the formula for XNPV, as shown in the help, the value is 3000
when the rate is zero.

thanks,
L.

Zero or negative rates and XNPV?
 

Your VBA code is right.
If rate = 0, the NPV should be $3000.
If you use 0.0000001 as rate for XNPV function, you also get 3000.
Excel, or Microsoft makes XNPV function work only for positive rate - that's
normal in real world.

--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download

=================================
"LAHM" wrote in message
oups.com...
Hi,

Does anybody know why XNPV appears not to accept negative rates?

e.g. using the example in the Excel 2003 help: (i.e. amounts = -10000,
2750, 4250, 3250, 2750, dates = 01 Jan 2008, 01 Mar 2008, 30 Oct 2008,
15 Feb 2009, 01 Apr 2009) returns #NUM! for rates 0 or below. That is
=XNPV(0,amounts, dates). However when I composed a VBA function to
duplicate the formula for XNPV, as shown in the help, the value is 3000
when the rate is zero.

thanks,
L.

Zero or negative rates and XNPV?
 

Thanks for your reply, that's what I suspected, but it's surprising
that Excel doesn't mention this in the help. On a related matter, do
you think that the XNPV function would be used or called by the XIRR
function? The reason I ask is because it might explain why XIRR, in the
case of a cashflow which has two possible solutions, one of which is
negative, always appears to return the higher result?

Rgds,

Lachlan.