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.