Enter a negative rate into the XNPV function
Oct
18
Written by:
Charles Flock
10/18/2011 8:15 PM
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.