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
Sign in for full access
Help
