More on Internal Rates of Return
Dec
21
Written by:
Charles Flock
12/21/2008 2:31 PM
A graphical representation of the XNPV function mentioned in last week's blog. This illustrates the polynomial nature of the XNPV function illustrating why there can be more than one correct answer for XIRR.
We got several comments about last week’s blog on the different results in EXCEL when the order of the cash flows changed. This caused me to write a little piece of SQL, using CTE, which permitted me to plot all the XNPV values for these cash flows. If you read last week’s blog, you can download the spreadsheet with contains the cash flows we are talking about.
with cf as
(
select cast(-1 as float) as rate
,cast(0 as float) as NPV
union all
select round(rate + cast(.01 as float), 2)
,cast(0 as float)
from cf
where rate < 1
) select c.recno,cf.rate,
wct.XNPV(cf.rate,'XIRR_negt','cf_amt','recno',recno, 'cf_date') as [NPV]
from XIRR_negt c, cf
where c.recno = 16
group by c.recno, cf.rate
order by c.recno option(maxrecursion 200)
XIRR_negtis the table that I set up to store the cash flows. Use your own table name.
This will produce all the values for XNPV using the cash flows. It should return a result that looks like this (I have reproduced the first 10 values).
recno rate NPV
---------------------- ---------------------- ----------------------
16 -1 NULL
16 -0.99 2.24613909429993E+29
16 -0.98 1.34286125945879E+25
16 -0.97 4.53827262992528E+22
16 -0.96 7.99863623158625E+20
16 -0.95 3.48382270622601E+19
16 -0.94 2.68867430363179E+18
16 -0.93 3.0789060901001E+17
16 -0.92 4.70574588827045E+16
16 -0.91 8.9652091102536E+15
I then took the results and put them into EXCEL and created the following graph:
As you can see, the function crosses zero at some point between -0.5 and -0.45 and then again between 0.05 and 0.10. In fact the values that EXCEL returned were -0.48677491 and 0.06931016. The CTE shows the results in movements of .01, but it would be easy enough to change the increment to .001, but you would have to change maxrecursion from 201 to 2001.
I have limited the range in the query from -1 to 1, but you could simply change those values to look at a different interval or you could increase the range simply by testing for rate less than 2 or 3 or whatever you want the upper limit to be. Just make sure that you keep maxrecursion in sync with the range. Maxrecursion should be the range (which is 2 in this case; -1 to 1) divided by the increment (.01 in this case) plus 1.
I found this graph incredibly useful for analyzing the behavior of the XIRR function. I hope you do too.