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 pi is the ith cash flow, di is the date of the ith cash flow, and d0 is the date of the first cash flow:

\sum_i \frac{p_i}{(1+x)^{(d_i-d_0)/365}}

The method used to estimate x was devised by Sir Isaac Newton about three hundred years ago. If xn is an approximation to a function, then a better approximation xn+1 is given by

x_{n+1} = x_n - \frac{f(x_n)}{f'(x_n)}

where f'(xn) 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

\frac{f(x+\epsilon) - f(x)}{(x+\epsilon)-x}

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.

Advertisement

Pages: 1 2

Posted by programmingpraxis
Filed in Exercises
3 Comments »