## Excel’s XIRR Function

### January 13, 2012

We studied numerical integration in a previous exercise. In today’s exercise we will look at the inverse operation of numerically calculating a derivative.

The function that interests us in today’s exercise is the XIRR function from Excel, which computes the internal rate of return of a series of cash flows that are not necessarily periodic. The XIRR function calculates the value of *x* that makes the following equation go to 0, where *p _{i}* is the

*i*th cash flow,

*d*is the date of the

_{i}*i*th cash flow, and

*d*is the date of the first cash flow:

_{0}The method used to estimate *x* was devised by Sir Isaac Newton about three hundred years ago. If *x _{n}* is an approximation to a function, then a better approximation

*x*

_{n+1}is given by

where *f*'(*x _{n}*) is the derivative of

*f*at

*n*. Mathematically, the derivative of a function at a given point is the slope of the tangent line at that point. Arithmetically, we calculate the slope of the tangent line by knowing the value of the function at a point

*x*and a nearby point

*x*+ε, then using the equation

to determine the slope of the line. Thus, to find *x*, pick an initial guess (0.1 or 10% works well for most interest calculations) and iterate until the difference between two successive values is close enough. For example, with payments of -10000, 2750, 4250, 3250, and 2750 on dates 1 Jan 2008, 1 March 2008, 30 October 2008, 15 February 2009, and 1 April 2009, the internal rate of return is 37.3%.

Your task is to write a function that mimics Excel’s XIRR function. When you are finished, you are welcome to read or run a suggested solution, or to post your own solution or discuss the exercise in the comments below.

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?