Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XNPV vs. NPV(quarterly) different results | Excel Worksheet Functions |