software

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.=IF(A1-B1lt;0,quot;-quot;,quot;quot;)amp;TEXT(ABS(A1-B1),quot;hh:mmquot;)

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

quot;LAHMquot; gt; wrote in message oups.com...
gt; Hi,
gt;
gt; Does anybody know why XNPV appears not to accept negative rates?
gt;
gt; e.g. using the example in the Excel 2003 help: (i.e. amounts = -10000,
gt; 2750, 4250, 3250, 2750, dates = 01 Jan 2008, 01 Mar 2008, 30 Oct 2008,
gt; 15 Feb 2009, 01 Apr 2009) returns #NUM! for rates 0 or below. That is
gt; =XNPV(0,amounts, dates). However when I composed a VBA function to
gt; duplicate the formula for XNPV, as shown in the help, the value is 3000
gt; when the rate is zero.
gt;
gt; thanks,
gt; L.
gt;
Sorry, posted reply to another question!
--
Kind regards,

Niek Otten

quot;Niek Ottenquot; gt; wrote in message ...
gt; =IF(A1-B1lt;0,quot;-quot;,quot;quot;)amp;TEXT(ABS(A1-B1),quot;hh:mmquot;)
gt;
gt; If you want to calculate with it, use just =A1-B1. It will not show the way you want, but further calculations are correct.
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;LAHMquot; gt; wrote in message oups.com...
gt;gt; Hi,
gt;gt;
gt;gt; Does anybody know why XNPV appears not to accept negative rates?
gt;gt;
gt;gt; e.g. using the example in the Excel 2003 help: (i.e. amounts = -10000,
gt;gt; 2750, 4250, 3250, 2750, dates = 01 Jan 2008, 01 Mar 2008, 30 Oct 2008,
gt;gt; 15 Feb 2009, 01 Apr 2009) returns #NUM! for rates 0 or below. That is
gt;gt; =XNPV(0,amounts, dates). However when I composed a VBA function to
gt;gt; duplicate the formula for XNPV, as shown in the help, the value is 3000
gt;gt; when the rate is zero.
gt;gt;
gt;gt; thanks,
gt;gt; L.
gt;gt;
gt;
gt;
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

=================================
quot;LAHMquot; gt; wrote in message oups.com...
gt; Hi,
gt;
gt; Does anybody know why XNPV appears not to accept negative rates?
gt;
gt; e.g. using the example in the Excel 2003 help: (i.e. amounts = -10000,
gt; 2750, 4250, 3250, 2750, dates = 01 Jan 2008, 01 Mar 2008, 30 Oct 2008,
gt; 15 Feb 2009, 01 Apr 2009) returns #NUM! for rates 0 or below. That is
gt; =XNPV(0,amounts, dates). However when I composed a VBA function to
gt; duplicate the formula for XNPV, as shown in the help, the value is 3000
gt; when the rate is zero.
gt;
gt; thanks,
gt; L.
gt;
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.