Post Reply 
A formula for IRR
05-17-2015, 08:47 PM
Post: #1
A formula for IRR
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

∫aL√0mic (IT9CLU) :: HP Prime 50g 41CX 71b 42s 39s 35s 12C 15C - DM42, DM41X - WP34s Prime Soft. Lib
Visit this user's website Find all posts by this user
Quote this message in a reply
05-17-2015, 09:13 PM
Post: #2
RE: A formula for IRR
There is no algebraic solution / equation for an IRR formula with more than one cashflow.
Find all posts by this user
Quote this message in a reply
05-17-2015, 09:19 PM (This post was last modified: 05-17-2015 09:27 PM by salvomic.)
Post: #3
RE: A formula for IRR
(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...


Attached File(s) Thumbnail(s)
   

∫aL√0mic (IT9CLU) :: HP Prime 50g 41CX 71b 42s 39s 35s 12C 15C - DM42, DM41X - WP34s Prime Soft. Lib
Visit this user's website Find all posts by this user
Quote this message in a reply
05-18-2015, 05:47 AM
Post: #4
RE: A formula for IRR
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
Find all posts by this user
Quote this message in a reply
05-18-2015, 08:03 AM (This post was last modified: 05-18-2015 08:46 AM by salvomic.)
Post: #5
RE: A formula for IRR
(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

∫aL√0mic (IT9CLU) :: HP Prime 50g 41CX 71b 42s 39s 35s 12C 15C - DM42, DM41X - WP34s Prime Soft. Lib
Visit this user's website Find all posts by this user
Quote this message in a reply
05-18-2015, 09:20 AM (This post was last modified: 05-18-2015 09:23 AM by akmon.)
Post: #6
RE: A formula for IRR
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%.
Find all posts by this user
Quote this message in a reply
05-18-2015, 09:33 AM (This post was last modified: 05-18-2015 10:08 AM by salvomic.)
Post: #7
RE: A formula for IRR
(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

∫aL√0mic (IT9CLU) :: HP Prime 50g 41CX 71b 42s 39s 35s 12C 15C - DM42, DM41X - WP34s Prime Soft. Lib
Visit this user's website Find all posts by this user
Quote this message in a reply
05-18-2015, 10:27 AM (This post was last modified: 05-18-2015 10:29 AM by salvomic.)
Post: #8
RE: A formula for IRR
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;

∫aL√0mic (IT9CLU) :: HP Prime 50g 41CX 71b 42s 39s 35s 12C 15C - DM42, DM41X - WP34s Prime Soft. Lib
Visit this user's website Find all posts by this user
Quote this message in a reply
05-18-2015, 11:54 AM
Post: #9
RE: A formula for IRR
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-
Find all posts by this user
Quote this message in a reply
05-18-2015, 12:31 PM
Post: #10
RE: A formula for IRR
(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

∫aL√0mic (IT9CLU) :: HP Prime 50g 41CX 71b 42s 39s 35s 12C 15C - DM42, DM41X - WP34s Prime Soft. Lib
Visit this user's website Find all posts by this user
Quote this message in a reply
05-18-2015, 01:47 PM (This post was last modified: 05-18-2015 01:58 PM by DrD.)
Post: #11
RE: A formula for IRR
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;
Find all posts by this user
Quote this message in a reply
05-18-2015, 02:08 PM
Post: #12
RE: A formula for IRR
(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

∫aL√0mic (IT9CLU) :: HP Prime 50g 41CX 71b 42s 39s 35s 12C 15C - DM42, DM41X - WP34s Prime Soft. Lib
Visit this user's website Find all posts by this user
Quote this message in a reply
05-18-2015, 02:47 PM
Post: #13
RE: A formula for IRR
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....
Find all posts by this user
Quote this message in a reply
05-18-2015, 02:56 PM
Post: #14
RE: A formula for IRR
(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 :-)

∫aL√0mic (IT9CLU) :: HP Prime 50g 41CX 71b 42s 39s 35s 12C 15C - DM42, DM41X - WP34s Prime Soft. Lib
Visit this user's website Find all posts by this user
Quote this message in a reply
05-18-2015, 03:32 PM (This post was last modified: 05-18-2015 03:50 PM by DrD.)
Post: #15
RE: A formula for IRR
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 ...
Find all posts by this user
Quote this message in a reply
05-18-2015, 03:45 PM
Post: #16
RE: A formula for IRR
(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...

∫aL√0mic (IT9CLU) :: HP Prime 50g 41CX 71b 42s 39s 35s 12C 15C - DM42, DM41X - WP34s Prime Soft. Lib
Visit this user's website Find all posts by this user
Quote this message in a reply
05-18-2015, 03:55 PM (This post was last modified: 05-18-2015 04:08 PM by DrD.)
Post: #17
RE: A formula for IRR
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-
Find all posts by this user
Quote this message in a reply
05-18-2015, 03:57 PM
Post: #18
RE: A formula for IRR
(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

∫aL√0mic (IT9CLU) :: HP Prime 50g 41CX 71b 42s 39s 35s 12C 15C - DM42, DM41X - WP34s Prime Soft. Lib
Visit this user's website Find all posts by this user
Quote this message in a reply
05-18-2015, 05:20 PM
Post: #19
RE: A formula for IRR
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)...

∫aL√0mic (IT9CLU) :: HP Prime 50g 41CX 71b 42s 39s 35s 12C 15C - DM42, DM41X - WP34s Prime Soft. Lib
Visit this user's website Find all posts by this user
Quote this message in a reply
05-18-2015, 06:01 PM (This post was last modified: 05-18-2015 06:27 PM by salvomic.)
Post: #20
RE: A formula for IRR
(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

∫aL√0mic (IT9CLU) :: HP Prime 50g 41CX 71b 42s 39s 35s 12C 15C - DM42, DM41X - WP34s Prime Soft. Lib
Visit this user's website Find all posts by this user
Quote this message in a reply
Post Reply 




User(s) browsing this thread: 1 Guest(s)