A formula for IRR

05172015, 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 

05172015, 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.


05172015, 09:19 PM
(This post was last modified: 05172015 09:27 PM by salvomic.)
Post: #3




RE: A formula for IRR
(05172015 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... ∫aL√0mic (IT9CLU) :: HP Prime 50g 41CX 71b 42s 39s 35s 12C 15C  DM42, DM41X  WP34s Prime Soft. Lib 

05182015, 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^(I1)),I,1,SIZE(L1))),X) This shold solve your IRR problem if I am not mistaken (verify the /X^(I1), it might not be exactly what is needed) Cyrille 

05182015, 08:03 AM
(This post was last modified: 05182015 08:46 AM by salvomic.)
Post: #5




RE: A formula for IRR
(05182015 05:47 AM)cyrille de brébisson Wrote: Hello, 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 

05182015, 09:20 AM
(This post was last modified: 05182015 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^(I1)),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%. 

05182015, 09:33 AM
(This post was last modified: 05182015 10:08 AM by salvomic.)
Post: #7




RE: A formula for IRR
(05182015 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: 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 

05182015, 10:27 AM
(This post was last modified: 05182015 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^(I1)),I,1,SIZE(L1))),X)1 >> Code:
∫aL√0mic (IT9CLU) :: HP Prime 50g 41CX 71b 42s 39s 35s 12C 15C  DM42, DM41X  WP34s Prime Soft. Lib 

05182015, 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:
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:
Dale 

05182015, 12:31 PM
Post: #10




RE: A formula for IRR
(05182015 11:54 AM)DrD Wrote: A couple of things to say here: 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 

05182015, 01:47 PM
(This post was last modified: 05182015 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:


05182015, 02:08 PM
Post: #12




RE: A formula for IRR
(05182015 01:47 PM)DrD Wrote: Well... that's not good! 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 

05182015, 02:47 PM
Post: #13




RE: A formula for IRR
Ok, QRZ OM, would you buy this? (preset your flows in L1, L1:={123400, 36200, 54800, 48100}; then run this program):
Code:
I know, its closer but no cigar.... 

05182015, 02:56 PM
Post: #14




RE: A formula for IRR
(05182015 02:47 PM)DrD Wrote: Ok, QRZ OM, would you buy this? (preset your flows in L1, L1:={123400, 36200, 54800, 48100}; then run this program): 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 

05182015, 03:32 PM
(This post was last modified: 05182015 03:50 PM by DrD.)
Post: #15




RE: A formula for IRR
Here's a twist on the theme, requires NO predefinition, but will work with the list, if it is predefined:
Code:
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 ... 

05182015, 03:45 PM
Post: #16




RE: A formula for IRR
(05182015 03:32 PM)DrD Wrote: Here's a twist on the theme, requires NO predefinition, but will work with the list, if it is predefined: well! another approach, that maybe "purists" could also accept... Code:
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 

05182015, 03:55 PM
(This post was last modified: 05182015 04:08 PM by DrD.)
Post: #17




RE: A formula for IRR
Ahh, yes ... that's much better! Used with a reserved listvariable like L1, it works even better! (No error if user variable 'flows' doesn't exist). 73, SK.
Dale 

05182015, 03:57 PM
Post: #18




RE: A formula for IRR
(05182015 03:55 PM)DrD Wrote: Ahh, yes ... that's much better! 73, SK. 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 

05182015, 05:20 PM
Post: #19




RE: A formula for IRR
trying to change this line
Code: RETURN fsolve(ΣLIST(MAKELIST((flows(I)/X^(I1)),I,1,SIZE(flows))),X)1; Code:
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 

05182015, 06:01 PM
(This post was last modified: 05182015 06:27 PM by salvomic.)
Post: #20




RE: A formula for IRR
(05182015 09:20 AM)akmon Wrote: Anyway I made two little changes: 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:
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 

« Next Oldest  Next Newest »

User(s) browsing this thread: