OK, no replies to this, so I worked up the following which works for me:

FUNCTION xirr (initial_guess, tolerance)

{

OBJECTIVE AND APPROACH:

Microsoft Excel's XIRR function generates the Internal Rate of Return for a variable series of

cash flows of irregular timings. It calculates XIRR using Newtons Method. There is discussion about

whether the Bisection method generates better results, but for our purposes Newtons Method does the

job in a small number of iterations and that's good enough !

In our application we are interested in investment growth rates, so the NPV code segments use the

growth formulation rather than the discounting formulation. This makes no difference to the end

result because the payments are just a series of positive or negative cash flows and the big -ve

one can be either as the first investment payment or the last closing valuation.

This implementation is based on C# code published at:

https://stackoverflow.com/questions/5179866/xirr-calculationIt assumes the following global array has been populated:

payments_schedule DYNAMIC ARRAY OF RECORD

date DATE,

value DECIMAL(24,2)

END RECORD,

The C code for this is:

# public static double Newtons_method(double guess, fx f, fx df) {

# double x0 = guess;

# double x1 = 0.0;

# double err = 1e+100;

#

# while (err > tol) {

# x1 = x0 - f(x0)/df(x0);

# err = Math.Abs(x1-x0);

# x0 = x1;

# }

#

# return x0;

# }

DEFINE

iterations SMALLINT,

initial_guess, # eg 0.1 = 10%; initial starting return value as a floating point not %; equivalent to "double" in C

tolerance, # eg 0.00001 = 0.001%

current_error, # current error

current_best_estimate, # current working return rate

revised_rate FLOAT # revised return rate

# set up default values:

IF initial_guess IS NULL OR NOT initial_guess THEN LET initial_guess = 0.10 END IF # 10%

IF tolerance IS NULL OR NOT tolerance THEN LET tolerance = 0.00001 END IF # 0.001%

# set up initial values:

LET current_best_estimate = initial_guess

LET revised_rate = 0 # this is redundant ?

LET current_error = 1000

LET iterations = 0

# this bit is the Newtons Method bit; we limit it to 100 iterations just in case:

WHILE current_error > tolerance AND iterations < 100

LET revised_rate = current_best_estimate -

(xirr_total_npv(current_best_estimate) / xirr_total_first_derivative_npv(current_best_estimate))

LET current_error = abs(revised_rate-current_best_estimate) # abs is in library2; it returns a DECIMAL(24) value

LET current_best_estimate = revised_rate

LET iterations = iterations + 1

END WHILE

RETURN 100 * revised_rate # this returns xirr as the % rate

END FUNCTION # xirr

FUNCTION xirr_total_npv (growth_rate)

{

This gets the aggregate of the elemental NPVs for all of the single payments. Normally the length

of the investment period would be (t1 - t0 + 1), we assume the cash flow is effective at the end of

the working day so we go along with the C code implementation of (t1 - t0). Also, the C code

reverses the dates to give the -ve exponent used for discounting; but we use the +ve variation.

The C code for this is:

# public static fx total_f_xirr(double[] payments, double[] days) {

# fx resf = (double x) => 0.0;

#

# for (int i = 0; i < payments.Length; i++) {

# resf = composeFunctions(resf,f_xirr(payments

*,days**,days[0]));*

# }

# return resf;

# }

# where f_xirr is given by:

# public static fx f_xirr(double p, double dt, double dt0) {

# return (double x) => p*Math.Pow((1.0+x),((dt0-dt)/365.0));

# }

DEFINE

start_date, end_date DATE,

growth_rate FLOAT, # this is the current best estimate of the growth rate

total_npv DECIMAL (24,3) # this is the total FINAL value

LET total_npv = 0

LET start_date = payments_schedule[1].date

LET end_date = payments_schedule[payments_schedule.getLength()].date

FOR i = 1 TO payments_schedule.getLength()

LET total_npv = total_npv +

# this is based on the classic NPV formula, but used as a growth formula:

payments_schedule*.value * util.math.pow ( (1+growth_rate), (end_date-payments_schedule**.date)/365 )*

END FOR

RETURN total_npv

END FUNCTION # xirr_total_npv

FUNCTION xirr_total_first_derivative_npv (growth_rate)

{

This gets the first derivatives of the aggregate of the elemental NPVs for all of the single payments

The C code for this is:

# public static fx total_df_xirr(double[] payments, double[] days) {

# fx resf = (double x) => 0.0;

#

# for (int i = 0; i < payments.Length; i++) {

# resf = composeFunctions(resf,df_xirr(payments*,days**,days[0]));*

# }

#

# return resf;

# }

# where df_xirr is given by:

# public static fx df_xirr(double p, double dt, double dt0) {

# return (double x) => (1.0/365.0)*(dt0-dt)*p*Math.Pow((x+1.0),(((dt0-dt)/365.0)-1.0));

# }

DEFINE

start_date, end_date DATE,

growth_rate FLOAT, # this is the current best estimate of the growth rate

total_first_derivative_npv DECIMAL (24,3)

LET total_first_derivative_npv = 0

LET start_date = payments_schedule[1].date

LET end_date = payments_schedule[payments_schedule.getLength()].date

FOR i = 1 TO payments_schedule.getLength()

LET total_first_derivative_npv = total_first_derivative_npv +

# this is based on the classic NPV formula, but used as a growth formula:

(1/365) * (end_date-payments_schedule*.date) * payments_schedule**.value **

util.math.pow ( (1+growth_rate), (((end_date-payments_schedule*.date)/365 ) - 1))*

END FOR

RETURN total_first_derivative_npv

END FUNCTION # xirr_total_first_derivative_npv