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.

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 642 other followers

%d bloggers like this: