## Excel’s XIRR Function

### January 13, 2012

We begin with the calculus. We choose an arbitrary epsilon and write functions to calculate the derivative and Newton’s method:

`(define eps 0.0000001)`

`(define (deriv f x eps)`

(/ (- (f (+ x eps)) (f x)) eps))

`(define (newton f x)`

(- x (/ (f x) (deriv f x eps))))

We turn now to the desired function. The net present value of the series of cash flows at interest rate *x* is given by the following function, where we assume that the dates have previously been converted to julian numbers:

`(define (npv ps ds x)`

(let ((d (car ds)))

(let loop ((ps ps) (ds ds) (s 0))

(if (null? ps) s

(loop (cdr ps) (cdr ds)

(+ s (/ (car ps)

(expt (+ 1 x)

(/ (- (car ds) d)

365)))))))))

Now the XIRR function chooses an initial guess of the interest rate, calculates the net present value, and iterates until the result is close enough:

`(define (xirr ps dates . args)`

(let* ((guess (if (pair? args) (car args) 0.1))

(ds (map (lambda (d) (apply julian d)) dates))

(f (lambda (x) (npv ps ds x))))

(let loop ((x guess))

(let ((next (newton f x)))

(if (< (abs (- (f x) (f next))) eps) x

(loop next))))))

If you look closely, this isn’t very efficient, as some values of *f*(*x*) are needlessly recalculated. Nonetheless, it is reasonably clear to read, and the number of iterations is typically small (less than a half dozen), so we won’t bother to fix the problem.

Here are some examples:

`> (xirr '(10000 2000 -5500 3000 3500 -15000)`

'((2001 5 1) (2002 3 1) (2002 5 1)

(2002 9 1) (2003 2 1) (2003 5 1)))

0.09706406163330174

> (xirr '(-10000 2750 4250 3250 2750)

'((2008 1 1) (2008 3 1) (2008 10 30) (2009 2 15) (2009 4 1)))

0.37336253350981735

We used julian from the Standard Prelude. You can run the program at http://programmingpraxis.codepad.org/z12ny3O7.

Pages: 1 2

The XIRR function calculates the value of x which causes that particular summation to equal zero? I don’t see any equation other than Newton’s formula there, maybe I’m just being nit-picky

The XIRR has a constant of 365 built-in. So it forces the resulting rate to be annualized. Is there another function similar to XIRR but with a user difinable parameter instead of a constant 365?