Friday, March 5, 2010

Calculating the IRR (Internal Rate of Return)

In a GAMS model where I try to select good investment projects (and find an optimal schedule when to implement them) at the end we wanted to show the IRR (Internal Rate of Return). There is no good closed formula for this, so my first approach to solve a series of one-variable, one-equation nonlinear models:

parameter cf(y) 'cashflows';
equation ecashflow;
variable irr;
irr.LO=0;
irr.UP=100;

ecashflow..
sum(y,cf(y)/[(1+irr)**(ord(y)-1)]) =e= 0;

model mirr /ecashflow/;

This can then be solved as follows:

loop(p$(cashflow(p,'cashflow','sum')>0),
   cf(y) = cashflow(p,
'cashflow',y);
   irr.L = 0.01;
  
solve mirr using cns;
   cashflow(p,
'irr','sum') = irr.L;
);

The overhead can be reduced somewhat by solving as a single larger model:

parameter cf(p,y);
equation ecashflow(p);
variable irr(p);
irr.LO(p)= 0;
irr.UP(p)= 100;

set psub(p) 'subset';

ecashflow(psub)..
   
sum(y,cf(psub,y)/[(1+irr(psub))**(ord(y)-1)]) =e= 0;

model mirr /ecashflow/;

psub(p)$(cashflow(p,
'cashflow','sum')>0) = yes;
cf(psub,y) = cashflow(psub,
'cashflow',y);
irr.L(psub) = 0.01;
solve mirr using cns;
cashflow(psub,
'irr','sum') = irr.L(psub);

Actually this was a little bit more stable than using the Excel function =IRR(). For the Excel function I needed different starting points to achieve convergence.

The last formulation is actually a square system of nonlinear equations, with only Jacobian entries on the diagonal. I.e. a solver like CONOPT can solve this completely in the presolver. In essence it will do internally exactly what I wrote in the first version: solving a series of 1 variable, 1 equation problems.

As the number of projects is relatively small for this model (<100), this nonlinear model fits in the demo size limits. So I don’t have to charge the client for an extra solver (the main model is a reasonably sized MIP).