 A formula for IRR - Printable Version +- HP Forums (https://www.hpmuseum.org/forum) +-- Forum: HP Calculators (and very old HP Computers) (/forum-3.html) +--- Forum: HP Prime (/forum-5.html) +--- Thread: A formula for IRR (/thread-3882.html) Pages: 1 2 3 A formula for IRR - salvomic - 05-17-2015 08:47 PM 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 RE: A formula for IRR - Gene - 05-17-2015 09:13 PM There is no algebraic solution / equation for an IRR formula with more than one cashflow. RE: A formula for IRR - salvomic - 05-17-2015 09:19 PM (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... RE: A formula for IRR - cyrille de brébisson - 05-18-2015 05:47 AM 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 RE: A formula for IRR - salvomic - 05-18-2015 08:03 AM (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 RE: A formula for IRR - akmon - 05-18-2015 09:20 AM 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%. RE: A formula for IRR - salvomic - 05-18-2015 09:33 AM (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 RE: A formula for IRR - salvomic - 05-18-2015 10:27 AM 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;``` RE: A formula for IRR - DrD - 05-18-2015 11:54 AM 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- RE: A formula for IRR - salvomic - 05-18-2015 12:31 PM (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 RE: A formula for IRR - DrD - 05-18-2015 01:47 PM 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;``` RE: A formula for IRR - salvomic - 05-18-2015 02:08 PM (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 RE: A formula for IRR - DrD - 05-18-2015 02:47 PM 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.... RE: A formula for IRR - salvomic - 05-18-2015 02:56 PM (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 :-) RE: A formula for IRR - DrD - 05-18-2015 03:32 PM 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 ... RE: A formula for IRR - salvomic - 05-18-2015 03:45 PM (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... RE: A formula for IRR - DrD - 05-18-2015 03:55 PM 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- RE: A formula for IRR - salvomic - 05-18-2015 03:57 PM (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 RE: A formula for IRR - salvomic - 05-18-2015 05:20 PM 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)... RE: A formula for IRR - salvomic - 05-18-2015 06:01 PM (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