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

### 3 Responses to “Excel’s XIRR Function”

1. ardnew said

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

2. Joe said

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?

3. Rajesh said

Hi

Has some one acheived calculating XIRR Function using c# or javascript – if so please guide me