Rely on the results of the XIRR function
Oct
18
Written by:
Charles Flock
10/18/2011 8:14 PM
11 Financial Calculations that you can’t do in EXCEL - Part 3 of 11
3. Rely on the results of the XIRR function
Here’s a very simple example. Enter the following dates and cash flows into a worksheet.
Then enter the following formula in B10: =XIRR(B2:B10,A2:A10). You should get the following result
EXCEL has calculated a value of 2.98023-09. Let’s reformat that. This should be close enough; 0.00000000298. In other words, approximately zero. Is that really what the internal rate of return is for these cash flows? Before answering, remember that if the value for discount rate is zero, than the discounted value of the cash flows will be equal to the sum of the cash flows. Is that the case here?
Absolutely not. But let’s use this result in XNPV, by putting this formula in B12. Here’s what we get.
The XIRR result certainly doesn’t satisfy the requirement of producing an XNPV of approximately zero. In this case, the XNPV result is actually approximately equal to the sum of the cash flows.
If we calculate the XIRR in SQL Server, we can see what the real result should be.
SELECT wct.XIRR(cf,date,NULL) as XIRR
FROM (VALUES
('2010-04-01',5290.5),
('2010-04-01',5290.5),
('2010-04-01',5290.5),
('2010-04-01',5290.5),
('2010-11-08',6273.2),
('2011-02-01',5417.2),
('2011-03-15',5492.05),
('2011-05-12',5486.15),
('2011-08-31',-20004)
) n(date, cf)
This produces the following result.
XIRR
----------------------
-0.592046674219327
Can we check that answer? Well, not in EXCEL (see item 2). Let’s do it in SQL Server.
;WITH mycte(date, cf) as (
SELECT *
FROM (VALUES
('2010-04-01',5290.5),
('2010-04-01',5290.5),
('2010-04-01',5290.5),
('2010-04-01',5290.5),
('2010-11-08',6273.2),
('2011-02-01',5417.2),
('2011-03-15',5492.05),
('2011-05-12',5486.15),
('2011-08-31',-20004)
) 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
----------------------
-1.736E-05
This is quite clearly a bug, and a bug that has been around for quite a while. Even if it returned #NUM! because it couldn’t find a solution it would be better than what it currently does. This is truly a disastrous result for anybody who is using the XIRR function to evaluate performance. There is a huge difference between having an internal rate of return of zero and -60%.