## 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.

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

Tags:
Categories:

## Search Blogs

 KeywordsPhrase

## Blog Archives

Archive
 < September 2024 >
SunMonTueWedThuFriSat
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345
Monthly
Go