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.

About these ads

Pages: 1 2

2 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 609 other followers

%d bloggers like this: