topic Re: XIRR - what method is used? in QlikView

XIRR - what method is used?

Tue, 02 Sep 2014 13:54:43 GMT

Hi Folks,

Does anyone know what methodology Qlikview uses to solve XIRR or IRR? Newton's method? If it is a combined or custom method, can you give a brief description?

Any help is appreciated. Thanks.

Re: XIRR - what method is used?

JonnyPoole — Tue, 02 Sep 2014 14:12:41 GMT

Hi there - the online help has these definitions for the function as well as some short samples. If you have already read this and it doesn't provide what you need , please let me know.

Re: XIRR - what method is used?

Tue, 02 Sep 2014 14:35:12 GMT

Hi Jonathan,

Thanks for the prompt response. I have read through those before but they do not discuss the methology used for root finding.

To find the one interest rate that can discount all of the cashflows to a npv sum of zero requires an iterative root-finding technique, most commonly the Newton, or Newton-Raphson method, and some seeding methodology.

So I am curious about the internals of the Qlikview implementation, which are not discussed anywhere, as far as I can tell. It seems quite stable and very fast, but I would like to know the details. This is probably a question that only someone from inside Qlikview can answer, there may be an existing internal whitepaper that they could post......

Thanks,

Peter

Re: XIRR - what method is used?

juleshartley — Tue, 13 Jan 2015 14:46:56 GMT

Hi Peter,

I'm also interested in this as I'm finding discrepancies between results produced by Excel and Qlik...

Re: XIRR - what method is used?

Wed, 11 Feb 2015 19:49:35 GMT

Hi Julian

I got this in reply (below).

Cheers,

Peter

XIRR uses our own code - it is not fetched from the Cephes library which we use for many other functions. When it was implemented, we used Excel as norm for how it should work.

The only reason XIRR can't be done using the common functions is that the order of amounts and dates is important (load order only) and there is a Newton raphson iteration at the end.