Login     Register

        Contact Us     Search

Enter a negative rate into the XNPV function

Oct 18

Written by: Charles Flock
10/18/2011 8:15 PM  RssIcon

11 Financial Calculations that you can’t do in EXCEL - Part 2 of 11
2. Enter a negative rate into the XNPV function
Here’s some simple data to enter into a spreadsheet:



In CELL B6, enter =XNPV(-0.02,B2:B5,A2:A5) and the #NUM! error value is returned (we are going to get that a lot in this article).



Let’s enter the same data in SQL Server and calculate the net present value.
SELECT wct.XNPV(-.02, cf, date) as XNPV
FROM (VALUES
      ('2011-10-15', -11000),
      ('2012-04-15', 200),
      ('2012-10-15', 200),
      ('2013-04-15', 10200)
      ) n(date, cf)
This produces the following result
                  XNPV
----------------------
     -79.7473906766863
There’s no mathematical reason for not having negative rates. Negative rates exist in the real world. And the EXCEL XIRR calculation will return a negative rate. This means that it is not actually possible to use the results of the XIRR calculation as input into the XNPV calculation. Since the purpose of the XIRR calculation is to find a value for rate, such that the XNPV is pretty close to zero, this seems especially user unfriendly. 
If we want to enter the following formula in B7, = XIRR(B2:B5,A2:A5) and change the formula in B6 to XIRR(B2:B5,A2:A5), we still get the #NUM! value, even though the XIRR function returns a value in B7.



Here’s the same calculation in SQL Server
;WITH mycte(date, cf) as (
SELECT *
FROM (VALUES
      ('2011-10-15', -11000),
      ('2012-04-15', 200),
      ('2012-10-15', 200),
      ('2013-04-15', 10200)
      ) n(date, cf)
)     SELECT ROUND(wct.XNPV(n.r, cf, date), 8) as XNPV
      FROM (SELECT wct.XIRR(cf, date, NULL) as r from mycte) n, mycte
This produces the following result
                  XNPV
----------------------
                     0

Of course, the NPV function has the same limitation, but we will still only count this as one towards our total.

 
Previous: Enter a negative yield to calculate the price of a bond
Next: Rely on the results of the XIRR function
 

 

Tags:
Categories:
Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service