A formula for IRR

[salvomic]

hi,
I need to set in the Prime a formula to calculate IRR (Internal Rate of Return, for Cashflow analysis of investment...).

Eddie W. hore made a nice program to calculate NPV; I need the inverse solution: given a list of cashflows and a rate or NPV calculate IRR...
The formula isn't simple, as I can see...

thank you

Salvo

[Gene]

There is no algebraic solution / equation for an IRR formula with more than one cashflow.

[salvomic]

(05-17-2015 09:13 PM)Gene Wrote:  There is no algebraic solution / equation for an IRR formula with more than one cashflow.

I know, Gene, in fact a need some tips to use what the Prime has to solve it in a program, or a routine to guess the solution for trials...

In the manual of HP 12C (that can solve IRR) the formula is that in attachment...

[cyrille de brébisson]

Hello,

Easiest way to do it is as follow:

Enter your cashflow in L1, following usual +/- signs
enter the SOLVE aplet, but do NOT use the SOLVE UI as it will try to set L1 to 0..
Instead, on the command line, type:

SOLVE(ΣLIST(MAKELIST((L1(I)/X^(I-1)),I,1,SIZE(L1))),X)

This shold solve your IRR problem if I am not mistaken (verify the /X^(I-1), it might not be exactly what is needed)

Cyrille

[salvomic]

(05-18-2015 05:47 AM)cyrille de brébisson Wrote:  Hello,

Easiest way to do it is as follow:

Enter your cashflow in L1, following usual +/- signs
enter the SOLVE aplet, but do NOT use the SOLVE UI as it will try to set L1 to 0..
Instead, on the command line, type:

SOLVE(ΣLIST(MAKELIST((L1(I)/X^(I-1)),I,1,SIZE(L1))),X)

This shold solve your IRR problem if I am not mistaken (verify the /X^(I-1), it might not be exactly what is needed)

Cyrille

thank you Cyrille, interesting.
However in the first try, I made some errors, so Solve has deleted the list then the Prime reset...
I'll make other tries...
maybe it should be:
SOLVE(ΣLIST(MAKELIST((L1(I)/(I+1)^X),I,1,SIZE(L1))),X)
but I get this warning ""argument is not an equation, solving 130000=0", then a void [].
130000 is the last item of the list...

Salvo

EDIT: for test I'm using the 12C example: {-80000, -500, 4500, 5500, 4500, 130000} CF0=-80000, then the CFj. Its IRR is about 13.06, at this rate the NPV of the list is about 0

[akmon]

Well it worked well for me in home mode Textbook format. Thank you Cyrille for the expression. Anyway I made two little changes:

fsolve(ΣLIST(MAKELIST((L1(I)/X^(I-1)),I,1,SIZE(L1))),X)-1

Example: L1= {-123400, 36200, 54800, 48100}

TIR = 0,0596, or 5,96%

(In spanish we call TIR -tasa interna de retorno- to IRR)

Very useful for me, cause I work with financial analysis in public works concessions.

I tried your example, Salvomic, and it nails it: 0,1306 or 13,06%.

[salvomic]

(05-18-2015 09:20 AM)akmon Wrote:  Well it worked well for me in home mode Textbook format. Thank you Cyrille for the expression. Anyway I made two little changes:

fsolve(ΣLIST(MAKELIST((L1(I)/X^(I-1)),I,1,SIZE(L1))),X)-1

Example: L1= {-123400, 36200, 54800, 48100}

TIR = 0,0596, or 5,96%

(In spanish we call TIR -tasa interna de retorno- to IRR)

Very useful for me, cause I work with financial analysis in public works concessions.

I tried your example, Salvomic, and it nails it: 0,1306 or 13,06%.

good for you, akmon!
HP 12C confirms your example: IRR (TIR) = 5.96%

But I get warning: "evaluating invariable expression(value) like if expression was a function. You should write subst(48100/X^-1,X,0)"
then -1

I'm searching where is my error...

EDIT: I was in CAS...
In Home it works well!

So, now we must find a way to be able to use it also in CAS...
Then Eddie could add this to his nice program for NPV.

However I'm trying to port formula in an independent program IRR(lista), but it gives for now "Bad argument type" in Home, and a lot of warnings in CAS. Working in progress...

Salvo

[salvomic]

why this code gives -1?
(the formula from akmon is ok in Home (not in CAS)...
<< fsolve(ΣLIST(MAKELIST((L1(I)/X^(I-1)),I,1,SIZE(L1))),X)-1 >>

Code:


EXPORT IRR(flows)
BEGIN
local I, irr, sz;
sz:= SIZE(flows);
irr:= fsolve(ΣLIST(MAKELIST((flows(I)/X^(I-1)),I,1,sz)), X)-1;
RETURN irr;
END;

[DrD]

A couple of things to say here:

1. You don't really need to declare a local variable I, since I is an available global already.
2. The dummy variable "flows" can not be typecast to a list directly that way. To demonstrate (using a predefined list variable):

Code:


EXPORT IRR(L1)
BEGIN
local irr, sz;
flows:=L1;
sz:= SIZE(flows);
irr:= fsolve(ΣLIST(MAKELIST((flows(I)/X^(I-1)),I,1,sz)), X)-1;
RETURN irr;
END;

I included the list variable "flows," only to demonstrate the need for a type: list variable. I know the code is redundant, instead you may prefer this:

Code:


EXPORT IRR(L1)
BEGIN
  RETURN fsolve(ΣLIST(MAKELIST((flows(I)/X^(I-1)),I,1,size(L1))), X)-1;
END;

-Dale-

[salvomic]

(05-18-2015 11:54 AM)DrD Wrote:  A couple of things to say here:

1. You don't really need to declare a local variable I, since I is an available global already.
2. The dummy variable "flows" can not be typecast to a list directly that way. To demonstrate (using a predefined list variable):
...
I included the list variable "flows," only to demonstrate the need for a type: list variable. I know the code is redundant, instead you may prefer this:

Code:


EXPORT IRR(L1)
BEGIN
  RETURN fsolve(ΣLIST(MAKELIST((L1(I)/X^(I-1)),I,1,size(L1))), X)-1;
END;

-Dale-

I thought also of this code, but here I get "bad argument type" in Home and CAS...

Thanks
Salvo

[DrD]

Well... that's not good!

I had a previously created User Variable 'flows' but I managed to copy the wrong program segment at the end in my previous post, oops. With the User Variable 'flows' predefined, this HOME program segment works for me: (predefine flows:={};)

Code:


EXPORT IRR(L1)
BEGIN
  flows:=L1;
  RETURN fsolve(ΣLIST(MAKELIST((flows(I)/X^(I-1)),I,1,size(flows))), X)-1;  
END;

[salvomic]

(05-18-2015 01:47 PM)DrD Wrote:  Well... that's not good!
... With the User Variable 'flows' predefined, this HOME program segment works for me: (predefine flows:={};)
...

ok, Dale, with predefined var it works also here (Home and CAS)!

But we need a program that doesn't require a predefined variable, it would be better.

Salvo

[DrD]

Ok, QRZ OM, would you buy this? (pre-set your flows in L1, L1:={-123400, 36200, 54800, 48100}; then run this program):

Code:


EXPORT IRR()
BEGIN    
   return fsolve(ΣLIST(MAKELIST((L1(I)/X^(I-1)),I,1,SIZE(L1))),X)-1; //  (Original command line from Akmon)
END;

I know, its closer but no cigar....

[salvomic]

(05-18-2015 02:47 PM)DrD Wrote:  Ok, QRZ OM, would you buy this? (pre-set your flows in L1, L1:={-123400, 36200, 54800, 48100}; then run this program):

Code:


EXPORT IRR()
BEGIN    
   return fsolve(ΣLIST(MAKELIST((L1(I)/X^(I-1)),I,1,SIZE(L1))),X)-1; //  (Original command line from Cyrille and Akmon)
END;

I know, its closer but no cigar....

QSO CQ
I would buy it, Sir, but... :-)
Now the program it's ok but only with L1, instead, as first, with flows it's good also for L3 ora a new list, but... flows must be predefined...

We need a new cigar: between those two :-)

[DrD]

Here's a twist on the theme, requires NO pre-definition, but will work with the list, if it is predefined:

Code:


EXPORT IRR()
BEGIN
  // test vals: {-123400, 36200, 54800, 48100} 
  IFTE(size(L1)==0,input({L1}),break); // purists will HATE this, some feel its wrong usage of this function!
  return fsolve(ΣLIST(MAKELIST((L1(I)/X^(I-1)),I,1,SIZE(L1))),X)-1; //  (Original command line from Akmon)      
END;

You would need to clear the input line if you input a brace enclosed set of values, of course.

* Feel free to change the IFTE(), to: if size(L1)==0 then input({L1}); end; ... for those so inclined!

There is an issue with IFTE() vs. the IF THEN ELSE END conditional block. I don't know the author's original design intention, but I see IFTE() as a convenient short hand for the other IF construct (in some cases). It has been attempted to explain that IFTE() is not intended to process a command as a T/F clause verb, only to process a value. It does process some commands, but reports syntax error on others. Stuff for another thread, perhaps ...

[salvomic]

(05-18-2015 03:32 PM)DrD Wrote:  Here's a twist on the theme, requires NO pre-definition, but will work with the list, if it is predefined:

Code:


EXPORT IRR()
...

You would need to clear the input line if you input a brace enclosed set of values, of course.

well!
another approach, that maybe "purists" could also accept...

Code:


EXPORT IRR()
// Thanks Cyrille, Akmon, Dale, salvomic
BEGIN
EDITLIST(flows);
RETURN fsolve(ΣLIST(MAKELIST((flows(I)/X^(I-1)),I,1,SIZE(flows))),X)-1;
END;

If flows is not set, user can add CF items, if it's present, he/she can edit the list, then calculates IRR...

[DrD]

Ahh, yes ... that's much better! Used with a reserved list-variable like L1, it works even better! (No error if user variable 'flows' doesn't exist). 73, SK.

-Dale-

[salvomic]

(05-18-2015 03:55 PM)DrD Wrote:  Ahh, yes ... that's much better! 73, SK.
-Dale-

I'm solved only a problem: if there is no var flows, program gives error, so we must create if it doesn't exist, then edit list and so on...
Staty tuned

73

[salvomic]

trying to change this line

Code:

RETURN fsolve(ΣLIST(MAKELIST((flows(I)/X^(I-1)),I,1,SIZE(flows))),X)-1;

into

Code:


IFERR RETURN "Error calc IRR"; THEN
RETURN fsolve(ΣLIST(MAKELIST((flows(I)/X^(I-1)),I,1,SIZE(flows))),X)-1;
END; // iferr

Prime reset :-(

why?

I would like to avoid the frequent "Error: Bad argument type" that program gives when there is no solution (and in this numeric calculation for IRR is frequent error)...

[salvomic]

(05-18-2015 09:20 AM)akmon Wrote:  Anyway I made two little changes:

fsolve(ΣLIST(MAKELIST((L1(I)/X^(I-1)),I,1,SIZE(L1))),X)-1

Example: L1= {-123400, 36200, 54800, 48100}

TIR = 0,0596, or 5,96%
...
I tried your example, Salvomic, and it nails it: 0,1306 or 13,06%.

this formula is quite good, but with another example of 12C I've troubles using it in the program:
{-79000, 14000, 11000, 10000, 10000, 10000, 9100, 9000, 4500, 100000}
12C gives IRR 13.72% (0.1372), Prime "Error: bad argument value" with the program...
With the formula directly I've two solution:
[-101369... 0.13719...], obviously only the second result is ok (positive)...

So, why then in the program it doesn't work?

Instead, with this code

Code:


EXPORT IRR()
// Thanks Cyrille, Akmon, Dale, salvomic
BEGIN
EDITLIST(flows);
irr2();
END;

#cas
irr2():=
BEGIN
fsolve(ΣLIST(MAKELIST((flows(I)/X^(I-1)),I,1,SIZE(flows))),X)-1;
END;
#end

the program works better with the new list (with 2 results), but always the output is a ugly
program({},{},[0.1306288...])
I'm searching to avoid the "program(..." part