Four Js Development Tools Forum

Hi,

I've put in a request for a full set of Financial functions along the lines of the util.Math class. I don't expect a speedy implementation, if at all !

Can anybody help with the following in 4GL:
My immediate need is for a function xirr() which would calculate the internal rate of return for a series of variable cash flows over variable dates.

A popular and apparently robust function XIRR is implemented in MS Excel and two possible examples of relevant code can be found at http://stackoverflow.com/questions/5179866/xirr-calculation

To be most useful, the method would return a single DECIMAL(16) variable after having read its data from a 1-dimensional dynamic array a bit like:

FUNCTION xirr (x)
  DEFINE
    xirr_rate DECIMAL(16),
    x DYNAMIC ARRAY OF RECORD              # the cash flows, populated by the calling program and called by reference
      dd DATE,
      mm MONEY
  END RECORD

  # do it

  RETURN xirr_rate
END FUNCTION

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-calculation

It 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

Feel free to publish to a GitHub repository so that others can find, download and use.

What I would suggest is coding so that it can be called from an IMPORT FGL, and rather than relying on a global array to be populated, define a public type and pass the cashflows array as a parameter.

Also don't rely on other libraries, I notice you had a reference to an abs() from your own library so until we persuade our head of development to add an abs() function or operator :-), you would need to include it in your repository so that it was self-contained. 

Reuben

An alternative approach which I jus t thought of and tried is to use the formulas built-into the java Apache POI library

Using my Apache POI library available here ... https://github.com/FourjsGenero/fgl_apache_poi

The following code ...

IMPORT FGL fgl_excel
 
MAIN
DEFINE workbook     fgl_excel.workbookType 
DEFINE sheet        fgl_excel.sheetType  
DEFINE row          fgl_excel.rowType  
DEFINE cell         fgl_excel.cellType 
 
    -- create workbook
    CALL fgl_excel.workbook_create() RETURNING workbook
 
    -- create a worksheet
    CALL fgl_excel.workbook_createsheet(workbook) RETURNING sheet
 
    -- create row
    CALL fgl_excel.sheet_createrow(sheet, 0) RETURNING row
 
    CALL fgl_excel.row_createcell(row, column2row("A")) RETURNING cell
    CALL fgl_excel.cell_number_set(cell,-1000000 )
 
    CALL fgl_excel.row_createcell(row, column2row("B")) RETURNING cell
    CALL fgl_excel.cell_number_set(cell,550000 )
 
    CALL fgl_excel.row_createcell(row, column2row("C")) RETURNING cell
    CALL fgl_excel.cell_number_set(cell,550000 )
 
    CALL fgl_excel.row_createcell(row, column2row("D")) RETURNING cell
    CALL fgl_excel.cell_formula_set(cell,"IRR(A1:C1,0.1)")  
 
    DISPLAY SFMT("Value in cell D1 = %1",  workbook.getCreationHelper().createFormulaEvaluator().evaluate(cell).getNumberValue())
 
END MAIN

outputs Value in cell D1 = 0.06596460097781877 which is same value as if I had plugged into Excel directly.

Reuben,

While a built-in ABS(x) function or operator would make sense, for now one can use:

   IIF(x<0,-x,x)

To avoid to have a additional library for just for that function.

Seb

On the subject of IIF(), before this was added to the language we had our own equivalent with an extra parameter...

IIF( bool-expr, true-expr, false-expr, bool-treat_null_as)

The programmer could then explicitly decide how they wanted a NULL condition to be treated.

Hopefully one day we will get also function overloading in Genero as well so IIF() could be called as:-

IIF(var == "A", "Accepted", "Rejected")
IIF(var == "A", "Accepted", "Rejected",TRUE) --Treat NULL as Accepted

David

Technically IIF is an operator, not a function.

What you want can be coded as

IIF(NVL(var,"A")=="A", "Accepted","Rejected")

IIF(NVL(var=="A",TRUE),"Accepted","Rejected")

IIF(var!="A","Rejected","Accepted")

IIF(NOT(var=="A"),"Rejected","Accepted")


I'd be very surprised if we overloaded as per your suggestion, you could also implement yourself with the pre-processor

&define IIFN(p1,p2,p3,p4) IIF(NVL(p1,p4),p2,p3)

(and replace your existing IIF with IIFN  s/IIF/IIFN/g)

I suspect if you had raised that during the 2.40 EAP when IIF was introduced that was what would be suggested.  Someone who had an existing nvl function that operated slightly different to the nvl operator we introduced had to do something similar.

Reuben